Look Who it Was Random video

Excel - Unpivoting Dates That Extend Across Column Headings - Episode 1105

714 views · Published 21 September 2009 · 4:13 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Make a pivot table from data with dates across the columns.

Welcome back to another episode of the MrExcel netcast! In today's video, we will be tackling a common issue faced by many Excel users - unpivoting dates. Our data set for this demonstration was sent in by Fabien from France, and we will be using it to create a PivotTable with Users as the filter field, Flow across the columns, and Dates down the side.

While it is possible to achieve this using the traditional method of putting each individual date in the Values field, I am not entirely satisfied with the process. It can be quite tedious and time-consuming, especially if you have a large number of dates. So, let's explore some alternative methods that might make this task a little easier.

First, we will try the method of creating a new column called "Key" which combines the User's name and the direction (IN or OUT) using the ampersand and comma symbols. Then, we will use the multiple consolidation ranges feature to create a PivotTable with all the dates going down the side. This method may seem a bit complicated, but it can be a great solution for larger data sets with multiple dates.

Another approach we can try is using the Data, Text to Columns feature to split the data into separate columns. This will allow us to create a PivotTable with Users as the filter field, Dates down the side, and Flow across the top. This method may require a bit more effort, but it can be worth it if you have a significant number of dates in your data set.

In conclusion, there are a few different ways to unpivot dates in Excel, and it ultimately depends on the size and complexity of your data set. I hope you found these methods helpful, and a big thank you to Fabien for sending in this question. Don't forget to subscribe to our channel for more Excel tips and tricks, and I'll see you in the next netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction and data set from Fabien
(00:21) Creating a PivotTable with Users as filter and Flow and Dates as fields
(00:37) Alternative method using individual dates in Values field
(01:27) Another method using a Key column and multiple consolidation ranges
(02:28) Comparison of the two methods
(03:48) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Adding individual dates to Values field in PivotTable
Arranging flow across columns and dates down the side in PivotTable
Building a PivotTable from formatted data
Choosing Report Filter, Row Labels, Column Labels, and Values in PivotTable
Creating a Key column for data manipulation
Creating a PivotTable in Excel
Efficiently handling large number of dates in PivotTable
Excluding Grand Total in PivotTable
Moving sum Values field to Row Labels in PivotTable
Using multiple consolidation ranges for PivotTable
Using Text to Columns for data formatting
Using Users as a filter field in PivotTable

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152628/

Creating a pivot table from dates where daily dates stretch across the top of your data set. This episode compares the pain of adding multiple value fields to a pivot table versus unpivoting using multiple consolidation range pivot tables.

Fabien sends in an intriguing pivot table question. I show one mildly acceptable way to solve the problem using the existing data and then a way to spin the data to make the problem easier to solve. Episode 1105 shows you how.

More from this channel