Most people use Excel for the purpose of “data management”.
Data management includes data ‘processing’ and ‘analyzing’ (creating tables and calculating values…), and is a crucial skill in the business world.
When managing data, the method or way of ‘processing’ data becomes important.
There are many ways to process data in Excel and we can achieve the “accurate value” in many different ways, but the problem is that the bigger the data, the more difficult it will become to calculate values or just to “show” it in a simple way.
The ‘pivot table‘ can help us manage big data and make it visible and simple.
Knowing this skill will save a lot of our time, so this is a useful and important skill to remember.
- 1 Process data using the pivot table
- 2 Change the range of the data in the pivot table
- 3 Sorting of graph data from the pivot table
- 4 Grouping data with the pivot table
- 5 In what cases will the date grouping not work?
Process data using the pivot table
Below is an April~June sales report of a computer shop.
Our boss wants to “know the sales amount of each product for each branch”. This sounds like a painstaking task.
In order to use time efficiently, we will use the pivot table to extract and process necessary data.
Example of use 1) Create a pivot table
Follow the steps below to create a pivot table.
(1) Select any cell within the table.
(2) Click on the following:
① Click the [Insert] tab.
② Click ‘PivotTable’ on the left.
(3) When the dialog box opens, insert the following:
① Check in the ‘Table/Range:’ if the automatically selected range is correct.
→ ※If incorrect, use the mouse and highlight the correct range.
② Select ‘New Worksheet’.
→ ※To create the pivot table in the same sheet, select ‘Existing Worksheet’.
③ Click [OK] to complete.
(4) A new sheet will be created automatically, and a pivot table sheet will appear.
Example of use 2) The system of the pivot table
Let us now learn the system of the pivot table and how to use it.
An automatically processed table will appear according to the settings on the right.
②【Field list area】
The subjects used as titles in the original table (※such as ‘Date sold’ or ‘Branch’) will appear as “items” here. By dropping (dragging down) the ‘items’ to the areas, or by clicking ON/OFF, we can create the table we want.
③【Report filter area】
The pivot table will be process according to the item dropped here.
The content of the item dropped here will be the title of each column in the pivot table.
The content of the item dropped here will be the title of each row in the pivot table.
The content of the item dropped here will be the actual data that will be processed in the pivot table.
(2) How to use the pivot table
From the field list area, drop (drag) each item to each of the area below and decide what type of column, row and values to process.
Now that we have revised on how to use the pivot table, we can move on to our main task.
Example of use 3) Processing data using the pivot table
Now, let us actually create a pivot table that shows “the sales amount of each product for each branch”.
(1) Select ‘Branch’ from the field area and drag it to the row area.
(2) The names of the ‘branch’ appears in each row of the pivot table.
(3) Drop the other items like above.
① Drop ‘Product name’ to the columns area.
② Drop ‘Price’ to the values area.
(4) In each column the ‘product name’ appears and the values of the ‘price’ are calculated automatically.
If we select an wrong item by accident, we can undue it in 2 ways.
Change the range of the data in the pivot table
After we created the pivot table, we added 2 more information in the original table. We would like to add these 2 data in the pivot table as well.
However, even though we changed the original table, it is not reflected in the pivot table.
The pivot table
In this case, we must change the settings of the pivot table itself.
Example of use) Change the data range
We will change the data range and show it in the pivot table.
(1) Follow these steps to begin:
① Select ‘PivotTable Tools’→[Analyze].
② Click ‘Change Data Source’ (click on the image).
(2) When the dialog box appears, change the settings.
① Select the correct range in ‘Table/Range:’ by either typing or highlighting.
② Click [OK] to complete.
(3) The data is correctly reflected in the pivot table.
Click anywhere on the pivot table to display the ‘PivotTable Tools’.
Sorting of graph data from the pivot table
Other than creating a table that we want, the pivot table function also has the “pivot graph“, in which we can create a graph based on the pivot table.
Next we will look at how to create a pivot graph and how to sort it.
Example 1) Create a pivot graph
Follow the steps below to create a graph out of the pivot table.
(1) Select the following:
① Select ‘PivotTable Tools’→[Analyze].
② Select ‘PivotChart’.
(2) When the dialog box appears, select Clustered Column and click [OK].
(3) A clustered column graph will be automatically created.
Example 2) Change the order of data
The branch names in the graph created is in the same order as the original pivot table, ‘California, Florida, New York, Texas’.
To change the order to for example the grand total (=sales price), we do the following:
(1) Right click on one of the values of the grand total in the pivot table, and select ‘Sort’ → ‘Sort Largest to Smallest’.
(2) The order in the pivot table and the graph changed from largest to smallest.
Like this, if we change the pivot table, the graph will also change.
Grouping data with the pivot table
The table below is the result of dropping ‘sales person’ into the column area and the ‘date sold’ into the row area.
In this case, organizing data not according to date but by month is called ‘grouping‘ of pivot tables.
Example of use) Grouping dates
Let us group the dates so that it will show the monthly total.
(1) Right click on the cell with date, and select ‘Group’ from the menu.
(2) When the dialog box appears, select the following:
① Select ‘Months’ from ‘By’.
② Click [OK] to complete.
(3) The pivot table has been reorganized with monthly results (e.g. 4=April).
In what cases will the date grouping not work?
There are cases where an error will appear when trying to group dates. Below is one example of such.
The table below is the same pivot table as above, but the date display is different.
Let us try to group this and create a monthly sales table.
Original pivot table
When we tried to group the dates in the same way as we did before, this error message popped up.
Error when trying to group
Only dates and numbers can be grouped in pivot tables, so let us look at what field the selected cell is in.
As we can see above, the cell field is in ‘General’, meaning that it is identified as a text.
When grouping in pivot tables, the above must be in ‘Date’. Even though it looks like a date in the table, the field also must be in date in order to correctly group them in the pivot table.