How to make a pivot table in Excel: An example 

Many financial professionals spend a long time putting together tables that summarize and aggregate data. Typically, these tables draw information from different columns and rows within the spreadsheet. However, as the underlying information changes on a daily, weekly, or monthly basis many hours can be spent updating the spreadsheet.

Pivot tables can help minimize the effort of updating the spreadsheets by automatically collating and aggregating data. They make data aggregation much faster and much more flexible. Pivot tables allow users to easily change the way the data is summarized depending on their needs.

For example, if one manager is interested in seeing sales figures by salesperson, but another manager is interested in seeing sales figures by region, a pivot table can be used to summarize the data so that it can be easily read by both managers. 

[Using Excel for finance: 5 essential functions (with videos)]

Pivot tables: Example 

Let us assume we’ve been asked to put together an analysis on the fees charged for each service (product) by our consultants for the first quarter of the year.

The data on the fees has been exported from the sales system into an Excel spreadsheet, but we need to present the information in a clear and user-friendly format. It has been suggested that pivot tables should be used to organize the data so that the various stakeholders can manipulate the data afterward to their liking.

In the video below, we construct a pivot table in Excel, showing which services brought in the most revenue, which consultants billed the most work, and the total values for each.

Note that the video makes use of keyboard navigation and shortcuts, but a computer mouse can also be used as an alternative for much of this. 
Write your awesome label here.

Cleaning the data 

The key is to do any housekeeping on the (imported) data before setting up the pivot table. In our example, the imported data has been cleaned in that every row and column is completed, that is, all data is consistent and has been spell checked.

There are many functions within Excel that are useful to identify duplicate information or incorrect data. It is best practice to use these tools to check the data thoroughly before setting up a pivot table.

For example, Excel includes functions such as conditional formatting which has a setting that can quickly format duplicated information. By formatting these data automatically, users can scan the information and check if everything seems correct before setting up the table. 

We use cookies to provide you with an optimal experience and relevant communication. Learn more or accept individual cookies.

Necessary

Necessary cookies (First Party Cookies) are sometimes called "strictly necessary" as without them we cannot provide the functionality that you need to use this website. For example, essential cookies help remember your preferences as you navigate through the online school.

Functional

During your interaction with this website, cookies are used to remember information you have entered or choices you make (such as your username or preferences for personalizing your account). These preferences are remembered, through the use of the persistent cookies, and the next time you use the Website you will not have to set them again.

Analytics

These cookies track information about visits to this Website so that we can make improvements and report our performance. For example: analyze User behavior so as to provide additional functionality or improve course contents. These cookies collect information about how visitors use the Website, which site or page they came from, the number of visits and how long a user stays on the Website.

Marketing

These cookies are used to deliver advertising materials relevant to you and your interests. They are also used to limit the number of times you see an advertisement as well as help measure the effectiveness of campaigns. They are usually placed by advertising networks we work with with our permission. They remember that you have visited a website and this information is shared with other organizations such as advertisers.