Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

11.4 PivotTables/Charts

Workplace Software and Skills11.4 PivotTables/Charts

Learning Objectives

By the end of this section, you will be able to:

  • Manipulate and create calculated fields in a PivotTable
  • Add slicers to a PivotTable
  • Use timelines in a PivotTable

In business, you will sometimes hear references made to “big data.” In this context, “big data” represents the large, growing, diverse datasets used by businesses to analyze their performance and attract more customers. There are three components to big data: the velocity of growth in the amount of data, the massive volume of data collected, and the variety of data that is collected and used in businesses. This data is collected through a process called data mining, which involves using software to make sense of all the data collected by the company and from outside sources. The goal is to analyze and evaluate large quantities of information and discover trends that may be useful to the company. Those trends could show purchasing patterns for a specific demographic group or identify a new competitor that businesses need to combat. The possibilities are as endless as the data that is collected. Through programs such as Excel and sophisticated data analytic software packages, the data can be synthesized to aid in decision making in the business. The use of data for decision making is applicable to all types and sizes of businesses and organizations.

You learned about PivotTables in Advanced Excel Formulas, Functions, and Techniques. Here, you will focus on the advanced features of PivotTables that help with data analysis. The PivotTable Analyze tab provides several tools that make data analysis a bit easier. Recall that the PivotTable Analyze tab is available when you have data formatted as a PivotTable and you click inside the table (Figure 11.72).

PivotTable Analyze tab is selected. Command groups available include: PivotTable, Active Field, Group, Data, Actions, Calculations, Tools, and Show.
Figure 11.72 The PivotTable Analyze tab provides several tools that make data analysis streamlined. (Used with permission from Microsoft)

The Timeline and Slicer tools offer a more visual way to filter the data in the PivotTable. The Fields, Items, & Sets menu allows you to include more calculations in your analysis. There are additional options in this menu that can help facilitate data analysis in the PivotTable.

Insert Timeline

The Insert Timeline feature for a PivotTable allows you to quickly filter information based on a date field. You must have a field formatted as a date to use the Insert Timeline tool. Using the first four tabs in the Chapter 11 data file workbook ("timeline1," "timeline2," "timeline3," and "timeline4"), you can create a PivotTable using the skills learned in Advanced Excel Formulas, Functions, and Techniques. The PivotTable summarizes the enrollment English Index Score (EN Index Score) for various majors at a university over several years (Figure 11.73). You can use this data to see if there have been changes in the score over time. The Insert Timeline feature on the PivotTable Analyze tab allows you to add a date filter to the table.

PivotTable Analyze tab selected. Filter command group highlighted. Cell B7 selected (62.64); Formula bar reads: 62.361111111111. PivotTable Fields sidebar displays selected options: Major 1 and EN IndexSource.
Figure 11.73 A date filter can be applied to a dataset with the Insert Timeline tool. (Used with permission from Microsoft)

Click in the PivotTable to bring up the PivotTable Analyze tab. Choose Insert Timeline from the Filter command group. Click the checkbox next to the field title that contains the date. In this example, the field is called DATE. Click OK. The timeline scroll bar will appear (Figure 11.74). In this case, you only have annual data, so you can change the unit to years instead of quarters, months, or days.

Timeline tab selected. DATE is selected under Timeline Caption in Timeline toolbar. A DATE timeline scroll bar displays information and offers options for Years (selected), Quarters, Months, and Days.
Figure 11.74 The timeline scroll bar offers a quick way to filter data using time such as years. (Used with permission from Microsoft)

Currently, the PivotTable shows all years available in the dataset. You can deselect those years that you do not want and keep only those that you are interested in examining. Let’s select 2017 and 2018, which filters the table using those dates only. You can then use this filtered data for more in-depth analysis and calculations. To remove the timeline, right-click on the timeline scroll bar and choose Remove Timeline. If you had already filtered the data with the timeline, the data in the PivotTable will remain filtered to those dates. If you want the table to return to its original form with all of the dates included, be sure to click on the Remove Filter button prior to removing the timeline.

Insert Slicer

A slicer is a visual, interactive way to filter data in a PivotTable. When a PivotTable has slicers available, you can filter the data by clicking on buttons in the slicer as opposed to using the filter options in the column headings of a PivotTable. They are particularly useful when there are multiple users of a PivotTable who need to view the information that is relevant to their needs. You can add slicers through the PivotTable Analyze tab in the Filter command group.

Let’s look again at the dataset of enrollment ACT scores. A PivotTable can be created to include the years, major, average high school grade point average (HS GPA), EN Index Score, and average ACT Math. You can insert a slicer to delve deeper into the data. Assume you want to look at the HS GPA for biology and chemistry majors compared with that of accounting and business majors. You can use a slicer to make this process easier. First, click in the PivotTable to access the PivotTable Analyze tab. Then, choose Insert Slicer. A selection window will appear (Figure 11.75). Click the checkbox next to Major 1. Click OK.

Insert Slicers pane options: DATE, Major 1 (selected), Count, EN IndexScore, HS GPA, ACTEnglish, ACTMath, ACTReading, ACTScience, ACTComp., Quarters, and Years.
Figure 11.75 The Insert Slicer tool allows you to choose which variable to filter. (Used with permission from Microsoft)

The slicer will appear on the spreadsheet with selection buttons for each of the majors in the list. You can resize and format the slicer to meet your needs. You can also choose various slicer styles under the Slicer tab that becomes accessible when you click inside the slicer (Figure 11.76).

Slicer tab selected and Slicer Styles command group offers various options for selection (includes various colors and styles).
Figure 11.76 The Slicer tab appears when you have inserted a slicer into a PivotTable. (Used with permission from Microsoft)

When you insert the slicer, the default is for all majors to be selected, as indicated by the blue highlighted buttons (Figure 11.77). You can click on the Multi-Select option to remove all the majors and start building your table for analysis. For this example, you want to look at the differences in HS GPA for Biology, Chemistry, Accounting, and Business Administration. You will choose only those majors to be displayed in your PivotTable (Figure 11.78).

Command Group displays Major 1 selected under Slicer Caption. Blue colors selected in Slicer Styles command group. Major 1 slicer displays categories in blue. Filter button is highlighted in top corner.
Figure 11.77 By inserting a slicer, a user can quickly filter data by simply clicking on the major of interest in this example. (Used with permission from Microsoft)
Major 1 slicer displays some categories in blue, some in white. Remove Filter button is highlighted in top corner.
Figure 11.78 To remove a filter, click on the Remove Filter button. (Used with permission from Microsoft)

Choosing only these majors filters the table for an easier and more direct comparison. Remember, you could simply use the filter option in the column heading, but slicers are a convenient and interactive way to filter data. To remove the filter, click on the Remove Filter button in the top right of the slicer window. To remove the slicer entirely, right-click on the slicer and choose Remove Slicer. Be sure to remove the filters first if you want the data to return to its original form. Simply removing the slicer window will not also remove the filters you set in place.

Slicers are also available when you have data in a Data Table. With the data formatted as a Data Table, click inside the Data Table to bring up the Table Design tab. The Insert Slicer option is in the Tools command group (Figure 11.79). The process to insert, format, use, and remove slicers is the same as it is for PivotTables.

Table Design tab selected. Insert Slicer button in Tools command group is highlighted. Cell A1 is selected in the spreadsheet and DATE is in the formula bar.
Figure 11.79 Slicers can be used with Data Tables as well through the Table Design tab. (Used with permission from Microsoft)

Calculated Field

The Calculated Field tool allows you to add columns of calculations to your PivotTable. The Calculated Field tool is accessed through the Fields, Items, & Sets menu on the PivotTable Analyze tab (Figure 11.80).

PivotTable Analyze tab selected. Fields, Items, & Sets options: Calculated Fields (highlighted), Calculated Item, Solve Order, List Formulas, Create Set Based on Row Items, Create Set Based on Column Items, Manage Sets.
Figure 11.80 Calculated fields are added to a PivotTable through the PivotTable Analyze tab. (Used with permission from Microsoft)

These calculated field columns cannot be used with slicers, but they allow you to analyze the data in other ways. Using the dataset of enrollments and ACT scores, assume you want to get the average ACT scores across the various content areas (English, Math, etc.). You will need to get an average of the four ACT scores as a calculated field column. Click inside the PivotTable to access the PivotTable Analyze tab; choose Fields, Items, & Sets; and click on Calculated Field. An input window will appear (Figure 11.81).

Insert Calculated Field pane displays options: Name, Formula, Add and Delete buttons, Fields (DATE), Insert Field button. Other options in Field include Major 1, Count, EN IndexScore, HS GPA, eng, math, reading.
Figure 11.81 Select the variable for the calculated field in the Insert Calculated Field window. (Used with permission from Microsoft)

You will need to name the calculated field—use “ACT average” for this example—and then you can input the formula. Remember that all formulas in Excel need to begin with an equals sign. You can use functions such as AVERAGE, SUM, and IF, as appropriate for your needs. For this example, you want the average of the English, Math, Reading, and Science fields. To accomplish this, type in the Formula input box “=AVERAGE(” to begin. Then, choose “eng” from the Fields list and select Insert Field. Then, type a comma and insert the next field, “math.” At the end of the formula, make sure to close your parentheses and then check for extra spaces in the formula. Then, click Add to create the calculated field. Finally, click OK (Figure 11.82a). You will now see the calculated field column in your PivotTable (Figure 11.82b).

(a) Insert Calculated Field pane indicates Add button and option for Formula (=AVERAGE(eng,math,reading,science). Insert Field button selected after Field (Major1). (b) Column H (Sum of ACT avg) added to spreadsheet.
Figure 11.82 Using the (a) Calculated Field feature will add (b) a new column to the PivotTable. (Used with permission from Microsoft)

The default setting for the column headings in PivotTables is “Sum of” even though you are calculating an average. When you created the calculated field, you named the field ACT average, but that is not the column heading Excel will use. Excel will automatically add on “Sum of” to the column heading. You can change that by simply clicking on the column heading and deleting the words “Sum of.” Notice the data in the first cell in the ACT average column for each major is much larger than the other numbers in the major. This cell represents the sum of all the averages for the subgroup. For accounting, 131.49 is the sum of the average ACT numbers for the years 2015 to 2020. Excel automatically adds the subtotal to the first field in the Rows list. This number is probably not as valuable as the averages per year under each major. You can delete this value by choosing the field settings for Major 1 and under Subtotals, select None (Figure 11.83).

Field Settings pane displays Custom Name (Major 1) and Subtotals (None selected) PivotTable visible in background.
Figure 11.83 The default setting is “Automatic.” This adds the subtotal for the first field in Rows in the PivotTable. (Used with permission from Microsoft)

Adding this calculated field for the accounting majors, you can see that the average ACT was at its lowest in 2019 and at its highest in 2018. Calculated fields can be used with sales and expense information to determine your profit, profit margin, and other similar calculations.

There are other options in the Insert Calculated Field input window that may also be useful. You can select the calculated field from the list and modify it to make changes. You can also select that calculated field and delete it entirely. Click OK after making your changes or deleting the calculated field. If you delete the field, the column will be removed from the PivotTable.

List Formulas

If there are multiple users of a data file, it can be important to ensure everyone is aware of the calculations behind the columns. This will assist the users in understanding the dataset and the information contained within. The List Formulas option in the Fields, Items, & Sets menu creates a separate tab that lists all the formulas (calculated fields) used in a PivotTable (Figure 11.84). This option is accessed when you are in the PivotTable in the PivotTable Analyze tab. Then, go to Field, Items, & Sets to get to List Formulas. It is good practice to include this with all data files that have multiple calculated fields. The list is not dynamic. If you create additional fields after the list is created, you will need to delete the original list and create a new one.

A spreadsheet displays Calculated Field (Field and Formula columns) and Calculated Item (Item and Formula columns) headers. A Note displays at the bottom.
Figure 11.84 If there are multiple users for a spreadsheet, use the List Formulas tool to make sure all users are aware of the calculated fields and solve order in a PivotTable. (Used with permission from Microsoft)

Real-World Application

Tracking Spending

PivotTables can be a powerful tool to manage finances in a business or for your personal retirement planning. They allow you to compare data, as well as identify trends and patterns in the data. They are especially useful when you have a large amount of data. PivotTables can summarize this data, as well as provide interactive capabilities that allow you to adjust the different variables and investigate their influence. For instance, perhaps you would like to investigate spending and determine different considerations, such as how much is being spent, by whom, and on what. The same scenario can be applied to your own personal budget. Consider how you can use a PivotTable to track your own spending:

  • How might this process be adjusted for a small business?
  • What are some other uses of PivotTables in small businesses that you can think of?

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.