Excel Tips

Tips from The Accounting Trenches: The Power of Pivot Tables

Ever stare at worksheets containing very large data sets of data and have no idea where to start? How do you break down your data to extract what you need in a meaningful way? Welcome to the power of the pivot table. A pivot table allows you to take the systems data dump and quickly organize it for meaningful analysis. And even with the grandest of accounting and finance software systems — and the fanciest standard reports — pivot tables are easy to create and invaluable to your financial reporting and accounting.

What are the benefits?

Before we dive in to the steps requires to create your table, here’s a quick list of the benefits pivot tables provide:

  • Easy to use
  • Flexible
  • Gives the ability to sort and re-sort information in a summarized format
  • Provides data analyses that can be identified and updated easily
  • Efficient in creation of reports
  • Can be used as a tool to help management make key decisions quickly

Now let’s get started!

Step 1: Organize Data in a Tabular Format

As an example, let’s use data that was extracted from an accounting system for an ice cream shop. The spreadsheet below shows gross sales by quarter, by product, across two locations. To start, make sure your data is organized in a tabular format and does not have any blank rows or columns, as follows.

Step 2: Select Your Range

Next, click any single cell in the data sheet. Click on the “Insert” tab, then click “Pivot Table.”

A dialog box will appear called “Create Pivot Table.” You will see that Excel has automatically selected the range for you to now create the pivot table. Select “OK.”

Step 3: Select Your Fields

The below pivot table field list will then appear. Select and drag the different fields into the filter, column, rows and values in order to achieve your objective with your pivot table.

Another Example to Follow

In the below example, let’s say we need to get the Gross Sales of each product, by quarter, for two locations. You would do the following:

  • Drag Quarter to the Columns areas, Product to the Row areas, Gross Sales to the Values area and State to Report Filter.
    • In the “Values” area you have the option to sum, count, etc.  You might have to edit this (mine doesn’t always show up as sum).
    • The “Filters” gives you a drop-down menu so you can toggle between all or a selection of states.

So there you have it! You have nothing to lose and everything to gain by learning how to create and use pivot tables. I hope you find this everyday tip useful.

For more quick and easy Excel solutions “from the trenches,” be sure to check out the following:

We also invite you to subscribe to Insights for up-to-date perspectives on finance best practices!

subscribe to CFO insights

Would you like to leverage more advanced capabilities in Excel that can make an immediate impact on your organization? Keep in mind that 8020 Consulting is here to help. Just click on the contact button below to ask us a question or learn more.

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

M&A Due Diligence & Transaction Support

3 Tips for Responding to Requests in the Sell-Side Due Diligence Process

Entering a sell-side due diligence process? Here are 3 things to consider when responding to diligence requests. #1 – Understand the bigger picture. It is easy to get swept up in a sell-side process (especially one with multiple buyers) and be overwhelmed by the volume or difficulty of various data requests. We can avoid some… View Article

January 23, 2020Mitch Browne

Financial Planning & Analysis

BI & EPM Planning and Budgeting: Automation and Acceleration Tips

Budget season for companies can be extremely challenging if the budget process is manual, inefficient or not-scalable. An Enterprise Performance Management (EPM) / Business Intelligence (BI) software tool is needed in order to negate those process issues. Best practices also exist that can be adopted in the budgeting process, while using the selected tool. Let’s… View Article

January 21, 2020Vikram Mehta

Financial Reporting & Accounting

Startup Finance: Building Operations Through Two Rounds of Funding

After several years as Director of Finance at a major entertainment studio for domestic TV production and distribution, I was excited to be hired for a startup finance position – as the first finance/accounting/back-office person and the company’s 12th employee. The company eventually became a multi-platform media destination offering Olympic and lifestyle sports programming on… View Article

January 16, 2020Yasuko Furuya

See All