#Mac pivot chart how to#

We select the whole range including years and products in our example.

Use the select range icon to select a range.

Now select the ranges you want to consolidate. In the next step of the wizard, choose the Create a single page field for me then press the Next button. Open the PivotTable and PivotChart Wizard using the Alt + D + P keyboard shortcut, then choose Multiple consolidation ranges then press the Next button.

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.

Essentially we will use a pivot table to unpivot the data.

#Mac pivot chart how to#

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. Sometimes when you are sharing an Excel Pivot Table with your colleagues you do not want the other user(s) to mess with your Pivot Table layout and format.You’ve probably come across data that looks something like this before. Have a look at the following image and the tutorial below that to see how this is achieved using Excel 2013:Ħ. Lock The Excel Pivot Table But NOT The Slicer! Now as you select each Slicer’s items, both Pivot Tables will change! STEP 5: Right Click on Slicer #2 and go to Report Connections(Excel 2013)/PivotTable Connections (Excel 2010) > “check” the PivotTable1 box and press OK

STEP 4: Right Click on Slicer #1 and go to Report Connections(Excel 2013 & 2016)/PivotTable Connections (Excel 2010) > “check” the PivotTable2 box and press OK STEP 3: Click in Pivot Table #2 and insert a YEAR Slicer by going to PivotTable Tools > Analyze/Options > Insert Slicer > Year > OK STEP 2: Click in Pivot Table #1 and insert a MONTH Slicer by going to PivotTable Tools > Analyze/Options > Insert Slicer > Month > OK STEP 1: Create 2 Pivot Tables by clicking in your data set and selecting Insert > Pivot Table > New Worksheet/Existing Worksheet You can then use the scroll bar to increase and decrease the columns or manually type in the number of columns to show your Slicer. To do this you need to click on your Slicer and go to Slicer Tools > Options > Buttons > Columns. Say that your Slicer is showing months from January to December, you can change the layout of the buttons to show in 3 separate columns, turning it into a “Quarterly View”. When you insert an Excel Slicer with your Pivot Table it defaults to one column, showing all your items in a vertical layout. SHIFT KEYBOARD:You can select a range of items by holding down the SHIFT key on your keyboard! LEFT MOUSE CLICK:You can select items from the Slicer by using your left mouse button ĭRAG LEFT MOUSE BUTTON:You can select an array of items by clicking the left mouse button and doing a dragging motion downwards/upwards within the Slicer ĬTRL KEYBOARD:You can select multiple items by holding down the CTRL key on your keyboard