Microsoft Excel Tutorial: Pivoting Time Columns in Excel: Amazing Trick for Creating Pivot Tables.

Welcome back to the MrExcel netcast! In this video, we will be discussing an amazing trick for pivoting time columns in Excel. If you've ever tried to create a pivot table with time data going across your columns, you know it can be a disaster. But don't worry, I have a solution for you.

Let's take a look at what happens when we try to create a pivot table with time data in our columns. We choose our report criteria, but then we have to include separate fields for each quarter. This makes it difficult to total the data and requires us to use calculated fields. Not an ideal situation. But fear not, there is a better way.

I want to give a shout-out to my co-author, Mike Alexander, for showing me this amazing trick. We will be using the old pivot table interface for this, so press [alt D] and then [P]. From there, we will use the multiple consolidation ranges option and specify our data range. This will give us a pivot table that is identical to our original data set.

But here's where the magic happens. If we double-click on any cell in the pivot table, we will get all the records behind that data. And because we used multiple consolidation ranges, when we double-click on the grand total, we get a brand new spreadsheet with our data now going down the sheet instead of across. This makes it much easier to create a pivot table.

To finish off the process, we will insert a couple of columns and use the data and text to columns feature to split our data back out into separate fields. This will give us a perfect data set to create a pivot table in Excel 2007.

Thanks for watching this netcast from MrExcel. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time!

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

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

It is difficult to create a pivot table when you have a time component stretching across the columns in your data set. In today's episode, an amazing trick in Excel 2007 to change four quarterly columns into four rows. Episode 705 will show the multiple consolidation ranges trick.

Table of Contents:
(00:00) Introduction
(00:17) Data Set Issues
(00:29) Creating a Pivot Table
(00:50) Amazing Trick by Mike Alexander
(01:02) Concatenating Data
(01:24) Creating a Pivot Table with Old Interface
(02:05) Pivot Table Results
(02:15) Double Clicking for Records
(02:34) Excel Rearranges Data
(03:06) Splitting Data for Pivot Table
(03:21) 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:
Concatenating fields in Excel
Converting data from columns to rows in Excel
Creating a pivot table from split data in Excel
Double-clicking cells in pivot tables to view underlying data
Excel tutorial on pivot tables in Excel 2007
How to fix data sets for pivot tables
MrExcel netcast on pivot table techniques
Splitting delimited data in Excel using text to columns
Tips for creating effective pivot tables
Using multiple consolidation ranges in pivot tables
Using the old pivot table interface in Excel
YouTube video on creating pivot tables in Excel

Join the MrExcel Message Board discussion about this video at mrexcel.com/board/threads/1152199/

• Excel - Pivoting Time Columns in Excel: Amazing Trick for Creating Pivot Tables - Episode 705 ( Download)
• Excel - Pivot Tables in Excel: Step-by-Step Tutorial to Create Your First Pivot Table - Episode 706 ( Download)
• Excel - Master Pivot Tables in Excel: Customize Layout and Solve Annoyances! - Episode 707 ( Download)
• Excel Rev Up - Pivot Table 2010: Episode 1340 ( Download)
• Excel - Multiple Consolidation Ranges in Excel Pivot Tables - Episode 556 ( Download)
• Excel - Master Pivot Tables in Excel: New Features and Improved Functionality - Episode 652 ( Download)
• Excel - Excel Data Formatting: Splitting a 2-Field Column for Pivot Table Analysis - Episode 702 ( Download)
• Excel - Master Pivot Tables with Calculated Fields in Excel | Excel Tutorial - Episode 878 ( Download)
• Excel - Turn Numbers into Yes/No in Excel Pivot Table | Step-by-Step Tutorial - Episode 723 ( Download)
• Excel - Sorting a Pivot Table - Episode 708 ( Download)
• Excel - Mastering Pivot Table Formatting: Keep Your Excel Worksheets Consistent - Episode 887 ( Download)
• Excel - Grouping Dates in a Pivot Table in Excel - Episode 751 ( Download)
• Excel - Pivot Table Formatting: How to Make Your Formats Stick - Episode 865 ( Download)
• Excel - Unlocking Excel's Hidden Feature: Multiple Consolidation Ranges - Episode 928 ( Download)
• Excel - Using Filters in an Excel Pivot Table - Episode 710 ( Download)