Step 3 :: Customise an Excel Pivot Table Report |
Step 3 :: Customise an Excel Pivot Table Report |
An Excel Pivot Table report can summarize large amounts of data in a table. Excel Pivot Tables can sum, average, count and use percentages of ‘n’ without entering a single formula.
In the previous sections we created an Excel Pivot Table. It is possible to interrogate the raw data using the Excel Pivot Table report and instantly change the whole emphasis of the original Excel Pivot Table report.
We can;
- Add, change or remove data.
- Change the layout and format.
- Apply filters to quickly narrow the criteria of your report.
- Add depth to your reports by including multiple categories.
- Display the underlying source data for a specific data cell.
The original raw data and Excel Pivot Table report we created in the ‘Create an Excel Pivot Table Report’ section is shown below.
Source Data:
 |
Excel Pivot Table report.
 |
Step 3 :: Customise an Excel Pivot Table Report
It only takes a few seconds or drags of the mouse to change the emphasis of the Excel Pivot Table above.
For example, if we want to change the emphasis of the original analysis towards the ‘Manager’ and the no. of Units sold we can do so very simply.
Add, Change or Remove Data
Step 1. Select the ‘Region’ Excel Pivot Table field within the Excel Pivot Table itself with our mouse and drag it out of the Excel Pivot Table area
Step 2. Select the Excel Pivot Table field named ‘Sum of Units’ and drag this out of the Excel Pivot Table area.
You should be left with a much emptier Excel Pivot Table.
Step 3. We now need to select and drag the ‘Sales’ field from the Excel Pivot Table field list into the Excel Pivot Table area named ‘Drop Data Items Here’.
Step 4. We now need to select and drag the ‘Manager’ field from the Excel Pivot Table field list onto the Excel Pivot Table field named ‘Total’ (Under the ‘Sum of Sales’ field)
You should be left with an Excel Pivot Table report which looks similar to the one below.
 |
Change the layout and format
If you want to locate change the look of your Excel Pivot Table report by having the ‘Manager’ field down the left column and the ‘Month’ field across the top, then these are easily swapped in seconds to alter the layout of your report.
We can also add depth to our Excel Pivot Table report by including multiple categories. For example, adding the ‘Region’ field to the column of your Excel Pivot Table will transform the emphasis of your report.
Step 1: Select the ‘Manager’ Excel Pivot Table field label from within the Excel Pivot Table and drag it onto the ‘Month’ label.
Step 2: Select the ‘Month’ Pivot Table field label from within the Pivot Table and drag and drop onto the ‘Total’ label (Below the ‘Sum of Sales’ field.)
Step 3: We will now add depth to the report by adding the ‘Region’ to our Pivot Table. Select the ‘Region’ field from the Pivot Table field list and drag and drop to the left hand side of the ‘Manager’ field label in your Pivot Table.
You should be left with a Pivot Table report which looks similar to the one below.
 |
Apply filters to quickly narrow the criteria of your report.
Using data tables with a large variety of values can mean your Pivot Table soon grows in size.
It is easy to filter these values to make the Pivot Tables easier for the intended audience to digest.
This has already been done in our original example by adding the 2003 Year to the Page Field at the top of the Pivot Table. We can use the drop down menu to change this for a different Year such as 2010, or even a combination of years.
We can also filter other elements of the Pivot Table such as the ‘Manager’
Step 1: Select the 
button on the ‘Manager’ label in the Pivot Table to reveal a list of Managers.
 |
Step 2: Click the tick box for the ‘Khan’ manager to remove the tick. Click OK.
The Khan manager will be removed from the Pivot Table report.
 |
You can add Khan back to the report by repeating Step 1 and Step 2.
Show or hide details for an item
Sometimes we would like to drill-down and view the underlying raw data that a specific item is based upon within a Pivot Table.
For example, Say we want to know what ‘Units’ data the manager ‘Taylor’ consisted of.
Step 1: Select the ‘Taylor’ field and right click. This will reveal the contextual menu as seen below. Select the ‘Group and Show Detail’ option to reveal the next sub –menu and select the ‘Show Detail’ option.
 |
Step 1: the ‘After Step 1 you should have the ‘Show Detail’ menu open. Select the ‘Units’ option from the Show Detail list and click OK.
 |
The Pivot Table will now show the ‘Units’ information in the Excel Pivot Table.
 |
Display the underlying raw data that makes up the summary elements of the Excel Pivot Table
If you would like to see the underlying raw data for the summary elements (Sub Totals, Totals, Grand Totals etc) of an Excel Pivot Table simply double click the summary element of an Excel Pivot Table.
For example, say we want to view the raw data that makes up the Taylor part of the ‘Central’ ‘Region’. Simply double click the summary element (Highlighted below) of the Excel Pivot Table under the ‘Taylor Total’.
 |
This will open a new worksheet and dump the raw data that makes up this sub-total into this sheet.
 |
| <<< Previous Section :: Step 2 : Create an Excel Pivot Table Report | Next Section :: Refresh Pivot Table >>> |