How to Use PivotTables in Excel

PivotTables help you quickly summarize large datasets into useful business insights without writing complex formulas. In this lesson, you’ll learn how to use PivotTables to analyze HVAC service data by technician, job type, city, and date, while also exploring interactive tools like Slicers and Timelines to filter the results and uncover trends faster.

Download the Excel file used in this tutorial:

Download

How to Use PivotTables in Excel

1. Insert a PivotTable from your dataset

  • Click anywhere inside your table or dataset
  • Go to Insert and choose PivotTable
  • Create it on a new worksheet so you have a clean space to work in
  • Once inserted, Excel will open the PivotTable area and the field list

2. Turn the field list back on if it disappears

  • If you click away and no longer see the PivotTable fields, click back into the PivotTable
  • Go to PivotTable Analyze
  • Select Field List to bring the panel back

3. Start with a simple revenue summary

  • Drag Revenue into the Values area
  • Excel will automatically summarize the full revenue in the dataset
  • This gives you your first quick KPI total

4. Break revenue out by technician

  • Drag Technician into the Rows area
  • Excel will instantly show revenue by technician
  • This is the fastest way to see how performance is distributed across your team

5. Add a second dimension like job type

  • Drag Job Type into the Rows area underneath Technician
  • This nests job type under each technician so you can see a more detailed breakdown
  • To simplify the view, remove Technician and leave only Job Type if you want a cleaner summary

6. Show revenue as a percentage of the total

  • Right click any value in the PivotTable
  • Choose Show Values As
  • Select % of Grand Total
  • This changes the view from dollar totals to contribution percentages

7. Change the calculation from Sum to Average

  • Open the dropdown for the Revenue field inside the Values area
  • Choose Value Field Settings
  • Change the summary from Sum to Average
  • This lets you compare average revenue by job type, technician, or any other category you place in the PivotTable

8. Switch the layout to answer different questions

  • Move fields in and out of the Rows area to change the analysis
  • Try:
    • Revenue by Technician
    • Revenue by Job Type
    • Average Revenue by Technician and Job Type
    • Revenue by City
  • This is one of the main advantages of PivotTables since you can re-slice the same data quickly

9. Add slicers for interactive filtering

  • Click anywhere inside the PivotTable
  • Go to Insert and choose Slicer
  • Select fields like Job Type, City, or Technician
  • Position the slicers next to the PivotTable so users can click through filters visually
  • Use the clear filter option when you want to reset the view

10. Add a timeline for date filtering

  • Click inside the PivotTable
  • Go to Insert and choose Timeline
  • Select your Service Date field
  • Use the timeline to filter the PivotTable by month, quarter, or other time groupings
  • Drag across the timeline to compare periods like Q1 or Q2

11. Adjust the timeline level when needed

  • Change the timeline from Months to Quarters if you want a higher-level summary
  • This makes it easier to compare broader time periods without rebuilding the PivotTable

12. Drill into any number to see the underlying records

  • Double click any number inside the PivotTable
  • Excel will open a new sheet showing only the rows behind that total
  • This is useful when you want to validate a result or inspect the exact transactions included in a summary

13. Use PivotTables as a fast exploration tool

  • Build the first summary with one value field and one row field
  • Then keep adjusting:
    • Add or remove row fields
    • Change the value calculation
    • Add slicers
    • Add a timeline
    • Drill into results
  • This gives you a fast way to explore service data and uncover patterns without writing formulas first

PivotTables in Excel

Q1. What is a PivotTable in Excel?
 A PivotTable is a built-in Excel tool that lets you summarize and analyze large amounts of data quickly. It can group information into categories and calculate totals, averages, counts, and percentages without requiring complex formulas.

Q2. Why are PivotTables useful for business analysis?
 PivotTables make it easy to turn raw data into meaningful insights. For example, you can quickly see revenue by technician, job type, or city, helping you spot trends and compare performance across different parts of the business.

Q3. What can I analyze with a PivotTable?
 You can analyze almost any structured dataset, including sales, revenue, service calls, inventory, or performance metrics. In this video, the PivotTable is used to summarize HVAC service records into insights by technician, job type, location, and time period.

Q4. What’s the difference between rows, values, and filters in a PivotTable?

  • Rows organize how categories are displayed
  • Values calculate the metric you want to analyze, such as sum or average revenue
  • Filters let you narrow the data to specific segments for deeper analysis

Together, these fields allow you to reshape the same dataset in multiple ways.

Q5. What are Slicers and Timelines in PivotTables?
 Slicers are visual filter buttons that let you quickly filter a PivotTable by categories like technician, city, or job type. Timelines do the same for dates, making it easy to view results by month, quarter, or other time periods.

Q6. Can PivotTables show averages and percentages, not just totals?
 Yes. PivotTables can display sum, average, count, percentage of total, and other summary calculations. This makes them useful not only for total revenue analysis, but also for comparing average performance and contribution by category.

Q7. Can I drill into the numbers in a PivotTable?
 Yes. One useful feature of PivotTables is that you can often double-click a summary number to see the underlying rows of data behind it. This makes it much easier to investigate exactly what is driving a result.

Q8. Should I use PivotTables or formulas?
 PivotTables are great for quick summarization and exploration, especially when you want fast insights from large datasets. Formula-based analysis offers more flexibility and customization, but PivotTables are often the easiest starting point for interactive reporting.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Join Now

Menu

Databoards

Analysis & Development

© 2025 All Rights Reserved.

Facebook Twitter Youtube