Nov
19

How to use pivot tables in Excel - tutorial for beginners

In this Excel pivot table tutorial you will learn what a PivotTable is, find a number of pivot table examples to get started quickly and see how to create and use pivot tables in Excel 2013, 2010 and 2007.

If you are working with large data sets in Excel, pivot tables come in really handy as a quick way to make an interactive summary from many records. Among other features, an Excel pivot table can automatically sort and filter different subsets of data, count totals, calculate average as well as create cross tabulations.

Another benefit of using pivot tables is that you can set up and change the structure of your summary table simply by dragging and dropping the source table's columns. This rotation or pivoting gave the feature its name.

What is a pivot table in Excel?

An Excel pivot table, aka PivotTable, is a tool to explore and summarize large amounts of data, analyze related totals and present summary reports. Pivot table reports are essentially designed to:

  • Present large amounts of data in a user-friendly way.
  • Summarize data by categories and subcategories.
  • Filter, group, sort and conditionally format different subsets of data so that you can focus on the most relevant information.
  • Rotate rows to columns or columns to rows (which is called "pivoting") to view different summaries of the source data.
  • Subtotal and aggregate numeric data in the spreadsheet.
  • Expand or collapse the levels of data and drill down to see the details behind any total.
  • Present concise and attractive online of your Excel data or printed reports.

For example, you may have hundreds of entries in your Excel worksheet with sales figures of local resellers:
The source data for a summary table

One possible way to sum this long list of numbers by one or several conditions is to use Excel formulas as demonstrated in SUMIF and SUMIFS tutorials. However, if you want to compare several facts about each figure, using a pivot table is a far more efficient way. In just a few mouse clicks, you can get a resilient and easily customizable summary table that totals the numbers by any field you want.
Excel pivot table examples

The screenshots above demonstrate just a few of many possible pivot table layouts. And the steps below show how you can quickly create your own pivot table in Excel 2013, 2010 and 2007.

How to make a pivot table in Excel: quick start

Many people think that creating an Excel pivot table is burdensome and time-consuming. But this is not true! Microsoft has been refining the PivotTable technology for many years, and in the modern versions of Excel, pivot tables are user-friendly are incredibly fast. In fact, you can build your own summary table in just a couple of minutes. And here's how:

1. Organize your source data in an Excel Table

Before creating a pivot table, organize your data into rows and columns, and then convert your data range in to an Excel Table. To do this, select all of the data, go to the Insert tab and click Table.

Using an Excel Table for the source data gives you a very nice benefit - your data range becomes "dynamic". In this context, a dynamic range means that your table will automatically expand and shrink as you add or remove entries, so won't have to worry that your pivot table is missing the latest data.

Useful tips:

  • Add unique, meaningful headings to your columns, they will turn into the pivot table's field names later.
  • Make sure your source table contains no blank rows or columns, and no subtotals.
  • To make it easier to maintain your pivot table, you can name your source table by switching to the Design tab and typing the name in the Table Name box the upper right corner of your worksheet.

2. Create a pivot table

Select any cell in the source data table (if you are building a pivot table based on a range of cells, select all cells with the data that you want to include), and then go to the Insert tab > Tables group > PivotTable.
Creating a pivot table in Excel

This will open the Create PivotTable window. Make sure the correct table or range of cells is highlighted in the Table/Range field. Then choose the target location for your Excel pivot table:

  • Selecting New Worksheet will place a pivot table in a new worksheet starting at cell A1.
  • Selecting Existing Worksheet will place your pivot table at the specified location in an existing worksheet. In the Location box, click the Collapse Dialog button The Collapse Dialog button to choose the first cell where you want to position your pivot table.

Choose the target location for your Excel pivot table.

Clicking OK creates a blank pivot table in the target location, which will look similar to this:
A blank pivot table is created.

Useful tips:

  • In most cases, it's convenient to have a pivot table in a separate worksheet, this is especially recommended for beginners.
  • If you are creating a pivot table from the data in another worksheet or workbook, include the workbook and worksheet names using the following syntax [workbook_name]sheet_name!range, for example, [Book1.xlsx]Sheet1!$A$1:$E$20. Alternatively, you can click the Collapse Dialog button The Collapse Dialog button and select a table or range of cells in another workbook using the mouse.
  • It might be useful to create a pivot table and pivot chart at the same time. To do this, in Excel 2013, go to the Insert tab > Charts group, click the arrow below the PivotChart button, and then click PivotChart & PivotTable. In Excel 2010 and 2007, click the arrow below PivotTable, and then click PivotChart.

3. Arranging the layout of your pivot table report

The Excel Pivot Table user interface is very intuitive and easy-to-use. The area where you work with the fields of your pivot tables is called PivotTable Field List. Itis located in the right-hand part of the worksheet and divided into the header and body sections:

  • The Field Section contains the names of the fields that you can add to your pivot table. The filed names correspond to the column names of your source table.
  • The Layout Section contains the Report Filter area, Column Labels, Row Labels area, and the Values area. Here you can arrange and re-arrange the fields of your pivot table.

The PivotTable Field List

The changes that you make in the PivotTable Field List are immediately reflected to your pivot table.

How to add a field to the pivot table

To add a field to the Layout section, select the check box next to the field name in the Field section.

Adding a field to the pivot table

By default, Microsoft Excel adds the fields to the Layout section in the following way:

  • Non-numeric fields are added to the Row Labels area;
  • Numeric fields are added to the Values area;
  • Online Analytical Processing (OLAP) date and time hierarchies are added to the Column Labels area.

How to remove a field from the pivot table

To delete a certain field from your Excel pivot table, you can either:

  • Uncheck the box nest to the field's name in the Field section of the PivotTable pane.
  • Right-click on the field in your pivot table, and then click "Remove Field_Name".

Removing a field from the pivot table

How to arrange pivot table fields

You can arrange the fields in the Layout section in three ways:

  1. Drag and drop fields between the 4 areas of the Layout section using the mouse. Alternatively, click and hold the field name in the Field section, and then drag it to an area in the Layout section - this will remove the field from the current area in the Layout section and place it in the new area.

    Drag and drop fields between the 4 areas of the Layout section using the mouse.

  2. Right-click the field name in the Field section, and then select the area where you want to add it:

    Right-click the field name in the Field section, and then select the area where you want to add the field.

  3. Click on the filed in the Layout section to select it. This will also display the options available for that particular field.

    Click on the filed in the Layout section to display the options available for that field.

4. Choose the function for the Values field (optional)

By default, Microsoft Excel uses the Sum function for numeric value fields that you place in the Values area of the PivotTable Field List. When you place non-numeric data (text, date, or Boolean) or blank values in the Values area, the Count function is applied.

But of course, you can choose a different summary function if you want to. In Excel 2013, right-click the value field you want to change in the pivot table, click Summarize Values By, and choose the summary function you want.

In Excel 2010 and lower, the Summarize Values By option is also available on the ribbon - on theOptions tab, in the Calculations group.

The screenshot below demonstrates an example of the pivot table with the Average function:

Choosing the function for the Values field.

The functions' names are mostly self-explanatory:

  • Sum - calculates the sum of the values.
  • Count - counts the number of non-empty values (works as the COUNTA function).
  • Average - calculates the average of the values.
  • Max - finds the largest value.
  • Min - finds the smallest value.
  • Product - calculates the product of the values.

To get more specific functions, click Summarize Values By > More Options… You can find the full list of available summary functions and their detailed descriptions here.

5. Show different calculations in Pivot Table value fields (optional)

Excel pivot tables provide one more useful feature that enables you to present values in different ways, for example show totals as percentage or rank values from smallest to largest and vice versa. The full list of calculation options is available here.

This feature is called Show Values As and it's accessible by right-clicking the field in the table in Excel 2013. In Excel 2010 and lower, you can also find this option on the Options tab, in the Calculations group.
Show totals as percentage rather than numbers

Tip. The Show Values As feature may prove especially useful if you add the same field to a pivot table more than once and show, for example, total sales and sales as a percent of total at the same time. See an example of such a table.

This is how you create pivot tables in Excel. And now it's time for you to experiment with your pivot table fields a bit to choose the layout best suited for your data set.

Working with PivotTable Field List

The pivot table pane, which is formally called PivotTable Field List, is the main tool that you use to arrange your summary table exactly the way you want. To make your work with the fields more comfortable, you may want to customize the pane to your liking.

Changing the Field List view

If you want to change how the sections are displayed in the Field List, click the Tools button, and choose your preferred layout.

Changing the Field List view

You can also resize the pane horizontally by dragging the bar (splitter) that separates the PivotTable pane from the worksheet.

Closing and opening the PivotTable pane

Closing thePivotTableField List is as easy as clicking the Close button (X) in the top right corner of the pane.Making it to show up again is not so obvious :)

To display the Field List again, right-click anywhere in the pivot table, and then select Show Field List from the context menu.

Re-opening the PivotTable Field List
You can also click the Field List button on the Ribbon, which resides on the Analyze / Options tab, in the Show group.

Click the Field List button on the ribbon to make the pivot table pane to show up.

Using Recommended PivotTables in Excel 2013

As you have just seen, creating a pivot table in Excel is easy. However, Microsoft Excel 2013 takes even a step further and proposes to automatically make a pivot table most suited for your source data. All you have to do is 4 mouse clicks:

  1. Click any cell in your source range of cells or table.
  2. On the Insert tab, click Recommended PivotTables. Microsoft Excel will immediately display a few layouts, based on your data.
  3. In the Recommended PivotTables dialog box, click a pivot table layout to see its preview.
  4. If you are happy with the preview, click the OK button, and get a pivot table added to a new worksheet.
    Using Recommended PivotTables in Excel 2013

As you see in the screenshot above, Excel 2013 was able to suggest just a couple of basic layouts for my source data, which are far inferior to the pivot tables we created manually a moment ago. Of course, this is only my opinion and I am biased, you know : )

Overall, using the Recommended PivotTable in Excel 2013 is a quick way to get started, especially when you have a lot of data and are not sure where to start.

How to use pivot table in Excel

Now that you know the pivot table basics, you can navigate to the Analyze and Design tabs of the PivotTable Tools in Excel 2013 (Options and Design tabs in Excel 2010 and 2007) to explore the groups and options provided there. These tabs become available as soon as you click anywhere within your pivot table.

the Analyze and Design tabs in Excel 2013

You can also access options and features that are available for a specific pivot table element by right-clicking that element.

How to design and improve an Excel pivot table

Once you have created a pivot table based on your source data, you may want to refine it further to make powerful data analysis.

To improve the pivot table's design, head over to the Design tab where you will find plenty of pre-defined pivot table styles. To create your own style, click the More button in the PivotTable Styles gallery, and then click "New PivotTable Style...".

To customize the layout of a certain field, click on that field, then click the Field Settings button on the Analyze tab in Excel 2013 (Options tab in Excel 2010 and 2007). Alternatively, you can right click the field and choose Field Settings from the context menu.

The screenshot below demonstrate a new design and layout for our pivot table in Excel 2013.
Improving the pivot table's deign and layout

How to get rid of "Row Labels" and "Column Labels" headings

When you are creating a pivot table, Excel applies the Compact layout by default. This layout displays "Row Labels" and "Column Labels" as headings in the pivot table. Agree, these aren't very meaningful headings, especially for novices.

An easy way to get rid of these ridiculous headings is to switch the pivot table layout from Compact to Outline or Tabular. To do this, go to the Design ribbon tab, click the Report Layout dropdown, and choose Show in Outline Form or Show in Tabular Form.
Switching to the Outline Form or Tabular Form

This will cause your Excel pivot table to display the actual field names, as you see in the pivot table on the right, which makes much more sense.
'Row Labels' and 'Column Labels' headings are gone.

Another solution is to go to the Analyze (Options) tab, click the Options button, switch to the Display tab and uncheck the "Display Field Captions and Filter Dropdowns" box. However, this will remove all field captions as well as filter dropdowns in your pivot table.

How to refresh a pivot table in Excel

Although a pivot table report is connected to your source data, you might be surprised to know that Excel does not refresh it automatically. You can get any data updates by performing a refresh operation manually, or have it refresh automatically when you open the workbook.

Refresh the pivot table data manually

  1. Click anywhere in your pivot table.
  2. On the Analyze tab in Excel 2013 (Options tab in earlier versions), in the Data group, click the Refresh button, or press ALT+F5.

    Alternatively, you can right-click the pivot table, and choose Refresh from the context menu.
    Refreshing the pivot table manually

To refresh all pivot tables in your Excel workbook, click the Refresh button arrow, and then click Refresh All.

Note. If the format of your pivot table gets changed after refreshing, make sure the "Autofit column width on update" and "Preserve cell formatting on update" options are selected. To check this, click the Analyze (Options) tab > PivotTable group > Options button. In the PivotTable Options dialog box, switch to the Layout & Format tab and you will find these check boxes there.

After starting a refresh, you can review the status or cancel it if you've changed your mind. Just click on the Refresh button arrow, and then click either Refresh Status or Cancel Refresh.

Refreshing a pivot table automatically when opening the workbook

  1. On the Analyze / Options tab, in the PivotTable group, click Options > Options.
  2. In the PivotTable Options dialog box, go to the Data tab, and select the Refresh data when opening the file check box.
    Refresh a pivot table automatically when opening the workbook.

How to move a pivot table to a new location

If you want to move your pivot table to a new workbook, worksheet are some other area in the current sheet, head over to the Analyze tab in Excel 2013 (Options tab in Excel 2010 and earlier) and click the Move PivotTable button in the Actions group. Select a new destination and click OK.
Moving a pivot table to a new location

How to delete an Excel pivot table

If you no longer need a certain pivot table, you can delete it in a number of ways.

  • If your pivot table resides in a separate worksheet, simply delete that sheet.
  • If your pivot table is located along with some other data on a sheet, select the entire pivot table using the mouse and press the Delete key.
  • Click anywhere in the pivot table that you want to delete, go to the Analyze tab in Excel 2013 (Options tab in Excel 2010 and earlier) > Actions group, click the little arrow below the Select button, choose Entire PivotTable, and then press Delete.

Deleting an Excel pivot table

Note. If any PivotTable chart is associated with your pivot table, deleting the pivot table will turn it into a standard chart can no longer be pivoted or updated.

Pivot table examples

The screenshots below demonstrate a few possible pivot table layouts for the same source data that might help you to get started on the right path. Feel free to download them get a hands-on experience.

Pivot table example 1: Two-dimensional table

  • No Filter
  • Rows: Product, Reseller
  • Columns: Months
  • Values: Sales

An example of the two-dimensional pivot table

Pivot table example 2: Three-dimensional table

  • Filter: Month
  • Rows: Reseller
  • Columns: Product
  • Values: Sales

This pivot table lets you filter the report by month.

An example of the three-dimensional pivot table

Pivot table example 3: One field is displayed twice - as total and % of total

  • No Filter
  • Rows: Product, Reseller
  • Values: SUM of Sales, % of Sales

This pivot table shows total sales and sales as a percent of total at the same time.
An example of the pivot table that shows total sales and sales as a percent of total

Hopefully, this pivot table tutorial has been a good starting point for you. If you want to learn advanced features and capabilities of Excel pivot tables, check out the links below. And thank you for reading!

Available downloads:

Pivot table examples

You may also be interested in:

13 Responses to "How to use pivot tables in Excel - tutorial for beginners"

  1. vaibhav says:

    Wow. What a wonderful explanation. Thank you very much.

  2. Deb says:

    Hi- I am trying to create a pivot for survey responses.... the answers to one of the questions is actual text responses such as "Excellent, Good, ect." Is there an easy way to sort these responses in a pivot? The remaining questions on the survey are numeric responses ranging 1-5, those are working great. It's the text one I am struggling with.
    Thanks,
    Deb

  3. kupci says:

    As usual, _great_ instructions. I've been out of the loop a bit with Excel, and they've really added some powerful and cool features to the product.

    One minor typo in the instructions - it should be 'Insert' instead of 'Inset' in the line below.
    To do this, select all of the data, go to the {Inset} tab and click Table.

    Anyway, impressive work, thanks very much!!

  4. prince says:

    Im trying pivot table but (Range&source) create a problem how to fix this problem please help me

  5. Vikas Solanki says:

    Perfect exmaple. I have been using this site from quite a long time and i have learned a lot.....

    Thanks

  6. Kumar says:

    I have a list of data having as many 10 rows and two columns. This may be termed as a reference table.
    Now I have a task by datas used in first table I have to update a very large table containing 25000 and more rows .
    It took too much time for me to do the task.
    Please suggest and efficient way to do the same

  7. Stephen says:

    Great instructions thank you, really clear and easy to understand.
    Having now got my pivot tables working i would like to use one of the pivot table columns as the source data for a separate drop down menu. Getting the drop down to use the cells as the source is simple but when the pivot table updates and the number of rows changes the drop down does not dynamically update to match so you either end up with blank drop down options or not all options available. Can you help?

  8. Venkat says:

    How to draw pivot tables from 3 different workbooks.Pl. help me.

  9. Bijay Kumar Dash says:

    Hi I want to know the short key return pivot table to excel page. If possible please let me know. Thanks.

  10. Mateus says:

    Hi!,

    Could you help me find a solution for formatting a pivot chart? I did a dash board that contain one chart with primary and secondary axis, and this chart it's attached to a slicer. The problem is: Every time a choose a blank series in the slicer, the chart looses the secondary axies configuration.

    Can you Help me? Sorry if it does not sound clear.

    Tks

    Matt

  11. YOGESH KUMAT TANDON says:

    Hi,

    I need to put the count of row and sum of person for same data in single pivot chart..... please suggest how can apply this in single pivot for showing two different different count in single pivot. please help me to resolve the same ASAP.

    Warm Regards,
    Yogesh Tandon

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard