Consolidate and Unpivot Multiple Ranges with the PivotTable Wizard It can be found under the Commands Not in the Ribbon section and it’s labeled PivotTable and PivotChart Wizard. You can also access this by adding a command to the quick access toolbar for the wizard. This is a keyboard shortcut from an older version of Excel and unless you know about it, there’s no place to find it in the ribbon.
I very briefly touched on the PivotTable And PivotChart Wizard in my 101 pivot table tips post where I mentioned the keyboard shortcut Alt + D + P to open the wizard.
In this post, I’m going to show you how to combine these tables and unpivot them using the pivot table wizard. I’ve already shown you how to convert this type of data into a proper data set by using the unpivot feature found in power query. This type of data can be a pain to convert to a proper data set. As is is now, it’s really hard to do anything with it. If this data was in a proper tabular format, it would be easy to summarize it in a similar manner with pivot tables and pivot charts. Salesperson and product should also be in their own column. The year should feature as a piece of data too in its own column. In this example, the sales amounts are scattered over 4 different ranges when they should all be in one column with a descriptive column heading like Sales Amount. It’s usually created by someone who doesn’t know what data should look like and thinks that the data should live in a summarized format because that’s how they want to view it. The file is in xlsx format, and does not contain macros.You’ve probably come across data that looks something like this before.
To follow along with the video, you can download the sample file from my Contextures website, on the Pivot Table Running Totals page. Watch this short video to see how to set up the pivot table, add a running total percent, change the row field and adjust the running total percent.Ġ4:59 Get the Sample File Download the Sample File So, sales went better in the second half of the year – the remaining 56% of the sales occurred then. I verified that, buy using a formula in cell G9, to divide the June running total, by the grand total.
This is the total units sold, up to and including that month, divided by the grand total of units sold.īy June, a running total of 11426 units have been sold, which is 44% of the overall total units sold.
This is the number of units sold each month. In column C, the sum is shown, with no calculation.In the pivot table shown below, there are three Value fields in the pivot table, showing the Sum of Qty sold in each month. One of my favourite options is the % Running Total calculation, which shows the current running total amount, divided by the grand total. Just right-click a Value field, and click “Show Values As”, to see what’s available, such as % of Column Total, or Running Total In. There are lots of other options though, such as Average, Minimum and Maximum.Īlso, there are many ways to view those Sums or Counts. When you add a number field to a pivot table, it usually shows up in the Values area, summarized by Sum or Count.
And the raw statistics are interesting, but with a pivot table (or 12), you can dig even deeper into the results. I’m sure you do something similar for your month end, to see how things are going. And, seeing which sample files get downloaded most frequently gives me ideas for creating new examples. If a page is getting lots of hits, I might add a new section to it, to make the content even better. After the data is imported to Excel, I use pivot tables to get a quick overview of the activity. At the beginning of every month, I download the previous month’s statistics for my web site, to see which pages and files were the most popular.