Скачать или смотреть Excel - Microsoft Excel - Cleaning Data Before Pivot Table - Episode 493

Microsoft Excel Tutorial - Microsoft Excel - Cleaning Data Before Pivot Table.

Welcome back to the MrExcel podcast! In today's episode, we have a question from David about creating a pivot table with data that violates the rules. As we all know, pivot tables require transactional data, not summary data. However, David's data has months listed across the headings, making it unsuitable for a pivot table. But don't worry, I have a solution for you!

I learned this trick from my co-author on the pivot table data crunching books, Mike Alexander. It's a little-known trick, but it's incredibly useful for situations like this. We'll be using this trick to take David's formatted data and turn it into transactional data that can be used for a pivot table. So, let's get started!

First, we'll create a pivot table by going to Data ＞ PivotTable and PivotChart Report. But instead of choosing the usual options, we'll select "Multiple consolidation ranges" in the first step. This is a feature that is often overlooked, but it's perfect for our situation. We'll then click "Next" and allow Excel to create a single page field for us. In the next step, we'll specify where our data is located and click "Add". Finally, we'll click "Next" and "Finish" to create a basic pivot table.

Now, here comes the amazing trick. If you double-click any cell in a pivot table, you get a new worksheet showing all of the data that makes up that cell. So, we'll go to the grand total for rows and columns, double-click that cell, and voila! We now have our original data set, but it's been transformed into one row per intersection of rows and columns. This is exactly what we need for a pivot table. It's a simple yet effective way to turn formatted data into transactional data suitable for pivot tables.

Thanks for tuning in to this episode of the MrExcel podcast. I hope this trick helps you with your pivot table woes. Don't forget to subscribe to our channel for more helpful tips and tricks. See you next time for another 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-...

Table of Contents:
(00:00) Introduction to the topic of creating a pivot table with non-traditional data
(00:24) Explanation of the importance of transactional data for pivot tables
(00:36) Introduction to the obscure trick for creating transactional data from formatted data
(00:50) Step-by-step instructions for using the trick to create a pivot table
(01:19) The amazing result of the trick and its usefulness for creating pivot tables
(01:51) 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:
Converting formatted data with months as headings to a suitable format for pivot tables
Converting summary data to transactional data for pivot tables
How to access the original data set from a pivot table
Mike Alexander's trick for creating transactional data from formatted data
MrExcel netcast on pivot table techniques
Pivot table rules and guidelines
Step-by-step guide to creating a pivot table using multiple consolidation ranges
Tips for working with pivot tables in Excel
Understanding the grand total feature in pivot tables
Using double-click to view underlying data in a pivot table
Using the pivot table wizard in Excel
YouTube video on creating pivot tables

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



In just about every pivot table tutorial, the writer tells you to start with a nice clean transactional data set. No blank rows, no blank columns, and no months going across the columns. David from Pennsylvania writes in to ask what to do if your dataset already is in a table with headings in the rows and columns. In Episode 493 I borrow a trick from Mike Alexander to solve this problem and make your dataset appropriate for use in a pivot table.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!