Spreadsheet applications are used to fulfill many requirements. Especially in accounting and mathematical fileds. Today, we are going to learn a powerful feature in Excel called ‘Pivot Tables’, a quick and easy way to prepare valuable information using a data set.
- Microsoft Excel 2007 or later version.
- A set of data (Figure 01)
Let’s assume we need to prepare a report to analyze sales (Items sold) by each salesmen as against each sales items. It should be something like below as in Figure 02.
Let’s begin. It is assumed that you already have a datasheet as in Figure 01 above.
- Highlight the data-set and select ‘Pivot Table’ from ‘Table’ section under Insert ribbon as in Figure 03. Highlighting the data-set at this stage is not mandatory as this can be done now or later.
- When the following dialog box appears, click ‘Ok’. As we have already selected the data range, there is no need to select the data range now. Also we will leave ‘new worksheet’ option to place the Pivot table in a new worksheet.
- You will get the following screen.
See the Left and Right sides of the screen carefully. The Pivot table fields are on the right side. Below that you will notice placeholders for Rows, Columns, Filters and Values. You can drag and drop these fields to any of the place holders below.
Rows – refers to report row fields where the values are grouped in to.
Columns – refers to report column fields where the values are grouped in to.
Filter – refers to fields where you can apply filters on the report data.
Values – refers to fields you can used as calculated fields/ values.
In this instance, we will use ‘Sales Person’ field as Rows, ‘Item’ field as Column and ‘Sold’ fields as Values. Let’s avoid any filtering at this stage to understand the basic functions of Pivot Tables first.
- The following Figure 06 shows when the ‘Sales Person’ fields is selected as Row.
- The figure 07 shows the report layout after selecting the Row field. You can see how each Sales Person’s name is displayed as ‘Rows’.
- Let’s now add the ‘Item’ field as ‘Column’. The following Figure 08 shows how it will look like once it is done’
- The Figure 09 shows how the report layout will look like when the ‘Item’ field is added as ‘Column’.
- Finally, let’s add ‘Sold’ field under ‘Values’ placeholder. The Figure 10 illustrates this as below.
- The report layout should be as follows in Figure 11.
This is not the end. You can always change the report layout to make a new report. This is the most important feature of Pivot Tables. One data-set allows you to make various reports with minimum efforts. You can keep updating the data-set regularly and refresh the report to view the updated report, or you just need to drag and drop fields to each placeholder based on the expected report to change the layout. Also you can add multiple fields for each place holder as well. But it is a best practice to limit to two fields to keep the report simple, and meaningful.
Hope you learned something new today. Try it out yourself and post your comments or questions here.