How to Use Pivot Tables in Microsoft Excel

Updated on April 25, 2017
A full pivot report using the Microsoft Excel pivot table functionality.
A full pivot report using the Microsoft Excel pivot table functionality. | Source

What Is a Pivot Table?

I have twenty years of experience working within either a finance or accounting department. I first started working with spreadsheets over twenty years ago and have used them constantly since. One of the more powerful functions of Microsoft Excel is the pivot table.

These are interactive tables in Excel that group and summarize large amounts of data in an easy-to-read and concise tabular format that allows for complex analysis. This tool is very powerful as it allows you to sort, hide, and manipulate data quickly and easily. You can also get an instant update to your summarized data with a quick refresh as you amend your information.

How to Use Pivot Tables

Using pivot tables in Excel is far easier than most people realize. This step-by-step guide will take you through the process from setting up the initial data right through to formatting the final table.

Creating the Data

The data will usually be a simple table of information with the headings in the top row and the data in the rows below. There are no limits on how many rows there can be.

Imagine a scenario where you have to summarize the salary data for a small company by organizing elements such as total salary per department and splitting by active and terminated employees.

By collecting all the data and labeling them as Function, Manager, Employee Status (ActiveTerm), Salary, Bonus and Severance, I created a simple table of data listing each element by employee.

The data.
The data. | Source

Creating the Pivot Table

  • Highlight the data range.
  • Click in the top left cell (including headings) and hold down the left mouse button. Stretch the range to the bottom right cell of the data. Do not include the totals in the range.
  • On the Excel ribbon, choose the Insert menu and click on Pivot Table.

Select the Pivot Table option from the Insert menu on the Excel ribbon.
Select the Pivot Table option from the Insert menu on the Excel ribbon. | Source

You will be presented with a dialog box. As you highighted the range prior to clicking on pivot table, the Table/Range section is already filled in. You can click on the icon next to the selected range and change the range if you need to.

You can now choose where you want the table to be located. This option will default to New Worksheet but you can choose Existing Worksheet and select a cell within this worksheet if you want to. For the purposes of this example, I will choose New Worksheet.

Press OK to create a blank pivot table and use the data from the range you selected to populate the field.

The template wth the field list populated
The template wth the field list populated | Source

You will now see the template (titled PivotTable1); this is where you will build the report. You can pull fields into the report directly from the field list or drag the files into the following areas.

  • Report Filter: This allows you to filter what appears in the report. For example, you might want to filter by manager and show the data relating to Cook only.
  • Column Labels: This allows you to set up the headings in the report. For example, you could list each manager and produce a total by manager.
  • Row Labels: This allows you to set up the headings for the rows. For example, you could list each function and collate the date by the function.
  • Values: This controls what information is going to be summarized.

In the example below I have created a pivot table that summarizes the salary for each manager and breaks the totals down by the function. At this point I have not formatted it. I did this by simply dragging Manager into the column labels box, Function into the row labels box, and Sum of Salary into the values box.

The template with the field list items pulled from your data.
The template with the field list items pulled from your data. | Source

What I would actually like is a subheading for Salary, Bonus, Severance and total under each function.

  • Drag Bonus, Severance, and Total into the values box.
  • The pivot table defaults to Count of Bonus so click on each added element, select Value Field Settings and choose Sum.
  • The table also automatically adds these as column subheadings. Simply drag the element Values from the column labels box into the row labels box.
  • I’ve decided that I only want to see data for active employees. Therefore, I will filter the pivot table. Drag the element ActiveTerm into the Report Filter box.
  • In cell B1 there is now a drop down arrow. Click on this and click on Active. The table now shows only data relating to active. If you had multiple entries in ActiveTerm you can use the Select Multiple Items check box in the drop down to choose more than one element to report.

Adding extra sub-headings to the pivot table.
Adding extra sub-headings to the pivot table. | Source

You now have a functioning pivot table. If you go back and change the figures within the data you can update the table by right clicking anywhere within and selecting Refresh.

Adding New Data

Sometimes, as you are preparing a pivot table, you may find that your information changes or different data is required. In the example below, it has been decided that a new column, Incentive Compensation, should be added to the data.

Revised Pivot Table Data with added Incentive Compensation field
Revised Pivot Table Data with added Incentive Compensation field | Source
  • Update the data and recalculate accordingly.
  • Right click on the table and press refresh. You will notice that the new field has appeared in the field list.
  • Drag Incentive Comp down to the Values section just above the Sum of Total element.
  • Click on Incentive Comp in the Values section.
  • Select Value Field Settings.
  • Select Sum.

You have now added the new field to the pivot table.

Adding the Incentive Compensation column to the Pivot Table
Adding the Incentive Compensation column to the Pivot Table | Source

Formatting

Now that I have the pivot table with the correct data summarized, I want to format it before presenting it.

  • Updating Headings: Some of the main headings need changing. Simply click in the cell as if it were any document and change accordingly. I changed the column label to manager and the row label to function.
  • Updating Subheadings: I’d like to remove "Sum of" from the front of each subheading. In the values section, click on each element and select Value Fields Settings. Amend the Custom Name as required—I removed the "Sum of" from all elements. Note that the pivot table will not allow you to amend these values to the same as field names. I simply added a space to the end of each. You can also click on the cell to change the text and it would update the whole table.
  • Formatting the Numbers: Select the range of numbers you would like to format by clicking on the left most cell, holding down the mouse button, and clicking on the right most cell of the range. Use the standard formatting options to change the format of the numbers in this range.
  • Formatting the Pivot Table: Click on the pivot table. You will see two additional menus on the main Excel ribbon. Select Design and click on the bottom arrow next to Styles. You can now hover over any of the styles and see a preview. When you decide which one you want to use, simply click on it and the format will change.

Formatting the Pivot Table
Formatting the Pivot Table | Source

You have now completed a simple pivot table.

Have you used Microft Excel Pivot Tables?

See results

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • profile image

        AMAN GHEBREYESUS 

        17 months ago

        Thanks a lot, a learnt a great deal from this.

      • angela_michelle profile image

        Angela Michelle Schultz 

        6 years ago from United States

        Thanks for making this easier to understand with your visuals!

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)