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

Table of contents
  1. Preface
  2. 1 Technology in Everyday Life and Business
    1. Chapter Scenario
    2. 1.1 Computing from Inception to Today
    3. 1.2 Computer Hardware and Networks
    4. 1.3 The Internet, Cloud Computing, and the Internet of Things
    5. 1.4 Safety, Security, Privacy, and the Ethical Use of Technology
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  3. 2 Essentials of Software Applications for Business
    1. Chapter Scenario
    2. 2.1 Software Basics
    3. 2.2 Files and Folders
    4. 2.3 Communication and Calendar Applications
    5. 2.4 Essentials of Microsoft 365
    6. 2.5 Essentials of Google Workspace
    7. 2.6 Collaboration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  4. 3 Creating and Working in Documents
    1. Chapter Scenario
    2. 3.1 Navigating Microsoft Word
    3. 3.2 Formatting Document Layout in Microsoft Word
    4. 3.3 Formatting Document Content in Microsoft Word
    5. 3.4 Collaborative Editing and Reviewing in Microsoft Word
    6. 3.5 Document Design
    7. 3.6 Navigating Google Docs
    8. 3.7 Formatting Layout and Content in Google Docs
    9. 3.8 Collaborative Editing and Reviewing in Google Docs
    10. 3.9 Versions and Version History
    11. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  5. 4 Document Preparation
    1. Chapter Scenario
    2. 4.1 Microsoft Word: Advanced Formatting Features
    3. 4.2 Working with Graphics and Text Tools in Microsoft Word
    4. 4.3 Managing Long Documents in Microsoft Word
    5. 4.4 Google Docs: Enhanced Formatting Features
    6. 4.5 Working with Graphics and Text Tools in Google Docs
    7. 4.6 Managing Long Documents in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  6. 5 Advanced Document Preparation
    1. Chapter Scenario
    2. 5.1 Creating Different Document Types in Microsoft Word
    3. 5.2 Mail Merge in Microsoft Word
    4. 5.3 Creating Forms in Microsoft Word
    5. 5.4 Creating Different Document Types in Google Docs
    6. 5.5 Creating Forms in Google Docs
    7. 5.6 Advanced Collaboration in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  7. 6 Preparing Presentations
    1. Chapter Scenario
    2. 6.1 Presentation and Design Essentials
    3. 6.2 Designing a Presentation in Microsoft PowerPoint
    4. 6.3 Formatting Microsoft PowerPoint Slides: Layout and Design Principles
    5. 6.4 Adding Visuals and Features to Microsoft PowerPoint Slides
    6. 6.5 Designing a Presentation in Google Slides
    7. 6.6 Creating Google Slides: Layout and Text
    8. 6.7 Adding Visuals and Features to Google Slides
    9. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  8. 7 Advanced Presentation Skills
    1. Chapter Scenario
    2. 7.1 Effective Presentation Skills
    3. 7.2 Finalizing a Slide Collection
    4. 7.3 Preparing a Microsoft PowerPoint Collection for Presentation
    5. 7.4 Preparing a Google Slides Collection for Presentation
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  9. 8 Content Management Systems and Social Media in Business
    1. Chapter Scenario
    2. 8.1 What Are Content Management Systems?
    3. 8.2 Common Content Management Systems
    4. 8.3 Creating Content with a Content Management System
    5. 8.4 Search Engine Optimization
    6. 8.5 Social Media in Business
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  10. 9 Working with Spreadsheets
    1. Chapter Scenario
    2. 9.1 Microsoft Excel Basics
    3. 9.2 Text and Numbers in Microsoft Excel
    4. 9.3 Calculations and Basic Formulas in Microsoft Excel
    5. 9.4 Formatting and Templates in Microsoft Excel
    6. 9.5 Google Sheets Basics
    7. 9.6 Text and Numbers in Google Sheets
    8. 9.7 Calculations and Basic Formulas in Google Sheets
    9. 9.8 Formatting and Templates in Google Sheets
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  11. 10 Advanced Excel Formulas, Functions, and Techniques
    1. Chapter Scenario
    2. 10.1 Data Tables and Ranges
    3. 10.2 More About Formulas
    4. 10.3 Using Arithmetic, Statistical, and Logical Functions
    5. 10.4 PivotTables
    6. 10.5 Auditing Formulas and Fixing Errors
    7. 10.6 Advanced Formatting Techniques
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  12. 11 Advanced Excel Spreadsheets: Statistical and Data Analysis
    1. Chapter Scenario
    2. 11.1 Understanding Data, Data Validation, and Data Tables
    3. 11.2 Statistical Functions
    4. 11.3 What-If Analysis
    5. 11.4 PivotTables/Charts
    6. 11.5 Data Analysis Charts
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  13. 12 Using Excel in Accounting and Financial Reporting
    1. Chapter Scenario
    2. 12.1 Basic Accounting
    3. 12.2 Financial Functions in Microsoft Excel
    4. 12.3 Integrating Microsoft Excel and Accounting Programs
    5. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  14. 13 Understanding and Using Databases
    1. Chapter Scenario
    2. 13.1 What Is a Database?
    3. 13.2 Microsoft Access: Main Features and Navigation
    4. 13.3 Querying a Database
    5. 13.4 Maintaining Records in a Database
    6. 13.5 Creating Reports in Microsoft Access
    7. 13.6 Creating Forms in Microsoft Access
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  15. 14 Advanced Database Use
    1. Chapter Scenario
    2. 14.1 Advanced Queries in Microsoft Access
    3. 14.2 Multiple Table Forms
    4. 14.3 Customizing Forms
    5. 14.4 Customizing Reports
    6. 14.5 Using Macros
    7. 14.6 Data Analysis and Integration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  16. 15 Integrating Applications
    1. Chapter Scenario
    2. 15.1 Microsoft 365: Collaboration and Integration
    3. 15.2 Microsoft Word: Integration with Microsoft Excel and Microsoft Access
    4. 15.3 Microsoft Word and Microsoft PowerPoint Integration
    5. 15.4 Microsoft Excel and Microsoft PowerPoint Integration
    6. 15.5 Microsoft Excel and Microsoft Access Integration
    7. 15.6 Integrating Data from Other Programs into Google Workspace
    8. 15.7 New Developments: The Role of Artificial Intelligence
    9. 15.8 Mastering Workplace Software Skills: A Project
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
  17. Index

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?
Citation/Attribution

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 https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • 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 https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
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.