Excel Pivot Table Guide
Step by step tutorial
Step by step tutorial
A pivot table allows you to organize, sort, manage, and analyze large data sets in a dynamic way. Pivot tables are one of Excel’s most powerful data analysis tools, used extensively by financial analysts around the world. In a pivot table Excel essentially runs a database behind the scenes, allowing you to easily manipulate large amounts of information.
Below is a step by step guide of how to insert a pivot table in Excel:
The first step is to ensure you have well-organized data that can easily be turned into a dynamic table. This means ensuring that all data is in the proper rows and columns. If data is not properly organized the table will not work properly. Ensure that the categories (category names) are located in the top row of the dataset, as shown in the screen shot below.
In step two you select the data you want to include in the table and then on the Insert Tab on the Excel ribbon locate the tables Group and select Pivot Table, as shown in the screenshot below.
When the dialog box comes up, ensure the right data are selected and the decide if you want the table to be inserted as a new worksheet, or located somewhere on the current worksheet. This is entirely up to you and your personal preference.
Once you’ve completed step two, the “PivotTable Fields” box will appear. This is where you set the fields by dragging and dropping the options that are listed as available fields. You can also use the tick boxes next to the fields to select the items you want to see in the table.
Now that the basic pivot table is in place, you can sort the information by multiple criteria, such as name, value, count or others.
To sort the date, click on the autosort button (highlighted in the image below) and then click “more sort options” to pick the various criteria you can sort by.
Another option is to right click anywhere in the table and then select Sort, and then “more sort options”.
Adding a filter is a great way of sorting the data very easily. In the above example we how to sort, but now with the filter function, we can see the data for specific sub-sections with the click of a button.
In the image below you can see how, by dragging the “channel” category from the list of options down to the Filters section, all of a sudden an extra box appears at the top of the pivot table that says “channel” indicating the filter has been added.
Next, we can click on the filter button and select the filters we want to apply (as shown below).
After this step is completed, we can see the revenue, shipping, and marketing spend for all products that were sold via the Instagram channel, for example.
More filters can be added to the pivot table as required.
The default in Excel pivot tables is that all data is shown as the sum of whatever is being displayed in the table. For example, in this table we see the sum of all revenue by category, the sum of all shipping expenses by category, and the sum of all marketing expenses by category.
To change from showing the sum or all revenue to the “count” of all revenue we can determine how many items were sold. This may be useful for reporting purposes. To do this, right click on the data you wish the change the value of and select “Value field settings” which will open the box you see in the screenshot below.
In accounting and financial analysis, this is a very important feature, as it’s often necessary to move back and forth between units/volume (the count function) and total cost ore revenue (the sum function).
At this point we only have on category in the rows, and one in the columns (the values). It may be necessary, however to add an extra dimension. A brief warning, however, that this could significantly increase the size of your table.
In order to do this, click on the table so the “fields” box pops up and drag an extra category, such as “dates” into the columns box. This will sub divided each column heading into additional columns for each date contained in the data set.
In the example below you can see how the extra dates dimension has been added to the columns to provide much more data in the PivotTable.
To learn more about PivotTables in Excel, check out our Advanced Excel Course which covers all the main functions a financial analyst needs to perform an analysis.
In addition to that course, we also highly recommend these free resources:
To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!