Create a PivotTable
In Excel, we can view data in different ways, for this we use a PivotTable. (Pivot table)
We have our worksheet with sales figures from 1997 and 1998 for PC’s and Printers:
- Place the cursor in an empty cell of the sheet.
- From the Data menu, select the ‘PivotTable’ (Pivot table).
- In step 1 of 4, you will be asked from what type of data you want to make the pivot table, select “Microsoft Excel List or Database” (Microsoft Excel list or database).
- Click ‘Next’.
- In step 2 of 4, the range is automatically selected.
- Click ‘Next’.
- In step 3 of 4, determine the layout (format) of the PivotTable by dragging the the field buttons from the right side of the dialog box to the desired location.
- Drag the buttons “Product” and the ‘Year’ from the right side, to the ‘ROW’ area which means that every product for every year gets a separate row in the PivotTable.
- Drag the ‘Province’ from the right to the ‘COLUMN’ area, this means that each region will receive a separate column in the PivotTable.
- Drag the “Sale” button from the right side to the ‘DATA’ area . In this area you can only enter numeric values, because it includes a numerical process.
(When the nature of the proposed operation needs to change, double-click the field button and select another operation. In this dialog you can also customize the number format). - Drag the “Seller” on the right side to the ‘PAGE’ area.
This means that for every seller a separate page is created in the PivotTable .
- Click ‘Next’
- Here you decide where you want to place the PivotTable
- If you leave this box blank, Excel will automatically place the PivotTable on a new worksheet, which is certainly recommended
- Click ‘Finish’ (Finish)
You can go over the sales figures per representative or representatives for any view in a new worksheet.
Editing Pivot Tables
You get a special toolbar with the PivotTable that lets you display data in a very simple way. You can modify PivotTable.
- select any data cell in your PivotTable
- Click the PivotTable Wizard.
- and you will immediately go to Step 3 of 4, here you can see the structure of the PivotTable for adding, deleting or moving the fields
- when you finish making changes, click ‘Finish’
Also, you can make changes in the structure in the worksheet itself:
- Eg: drag the field button “Seller” to the Page area just below the ‘Year’ button
You see the result above
- Drag the page field to restore the original PivotTable
You can easily add row fields:
- Click on the PivotTable Wizard.
- When you come to the steps 3 of 4 in the PivotTable Wizard
- Drag ‘month’ above ‘product’ in the ‘ROW’
- Click on “finish”
You can see that the months are added.
To hide certain parts in a PivotTable select the field name of the fields that you want to hide.
- Click on the’PivotTable Field’
button and in the dialog box, choose the items you want to hide in the “Hide items” (Hide items) and click OK.
We have hidden the E-Flanders and W-Flanders provinces,
To make them visible again, you need to repeat the operation.
To adjust the format of numbers:
- Select athe data
- Click on the ‘PivotTable’
button - In the dialog choose the ‘Number’ (Number) button
- Choose the category in the ‘Cells’ dialog . eg the currency notation
- Click OK
- Click OK again
The currency notation is used
Percentage representation
- Select the data
- Click on the ‘PivotTable Field’ button
- Click the ‘Options’ button
- In the selection “Show data as” (Show data as), click on the downward ponting arrow
- Eg: choose “% of row”
- Click OK
Result:
If this is not what you want, you can undo this operation
.
Hide Grand Totals
- Select the data
- Click on the “PivotTable” wizard
button - You will then come to step 3 of 4
- Click ‘Next’
- Click on ‘Options’
- Uncheck the options for the boxes marked “Grand totals for columns“ and “Grand totals for rows”
- Click OK
- Click ‘Finish’
At the bottom of the list you will see that the grand totals for rows have disappeared.
If you want to see the totals later, then repeat the steps above and then check the options “Grand Totals for Rows” and “Grand Totals for Coloumns” again.
Group Data in a PivotTable
- Select the months January, February and March
- Click on the ‘Group ‘ in the “Pivot table” (Pivot table) toolbar
- A new section called ‘group1’ is added
- We repeat this for every set of 3-months (April, May, June and July, August, September and October, November, December)
Now we give the group a new name:
- Select the group
- Place the cursor in the formula bar and type a new name: Quarter 1
- Repeat this for all groups
Also you can change the field name in the same way.
Sorting column fields and row fields in a PivotTable
- Select the ‘province’ column
- Click on the “Sort Ascending and Descending”
toolbar button
You notice that in the column field sorting is done from left to right. Sorting of quarters will be done from top to bottom
Formatting a PivotTable report
- Select any cell
- Choose the “Auto Format“ option from the “Format” menu
- Choose a style that you like
- Click Ok
The selected format is applied
Of course you can also create your own formatting.
Pivot tables based on merge tables
In our workbook, here are four worksheets that can be merged into a single PivotTable :
- In the ‘Data’ menu, choose the ‘PivotTable’ (Pivot table) option
- Choose the option “Multiple consolidation ranges” as a source for your PivotTable
- Click ‘Next’
- In step 2A of 4 you see that data with identical labels are merged
- Click here for 1-page field
- Press next
- In step 2B of 4, you should indicate the worksheet ranges:
- In the first worksheet ‘Antw. ’95’ select the range (A2 to E5)
Make sure that the totals are NOT included in the range - Click on the Add button in the Wizard (Add)
- Select the next worksheet Limb’95 and select the range (A2 to E5)
- Click on the Add button in the Wizard
- Repeat this for the worksheets ‘Antw. ’96’ and ‘Limb. ’96’
- Click the Next button
- Step 3 of the Wizard is shown
- The layout is automatically displayed
- Click ‘Next’
- Step 4 is shown
- The “start cell of the PivotTable” box is empty again
- Enter a description in the “PivotTable”
- Click ‘Finish’
- The PivotTable is shown
Refresh data and add charts
If the source data of a PivotTable is changed and you want to see the changes in the Pivot Table, then you have to Refresh the PivotTable :
- In your workbook ‘Sales 97 98’ workbook, change the “laptop” to “Printer”. (Edit-find-replace)
- Open the PivotTable worksheet, you see that the changes have not been implemented
- Make the “Pivot Table” toolbar visible again
- Place the cursor in any data cell
- Click on the “Refresh Data”
button - The data in the PivotTable is refreshed
Also, you can create charts in PivotTable
- Select the range
- Press the “Wizard charts” button
- You can make the chart step by step
- Click ‘Finish’
