Use Pivot Tables to Create Summary Reports and Dashboards

Excel Bad Habit #7: Using Formulas When Pivot Tables Are a Better Option

Let's say your boss gives you a big table of data and asks you to create a summary report of the revenue per region.

Watch on YouTube & Subscribe to our Channel

Create a Summary Report of Revenue

You might be tempted to create a list of unique values, write a complex SUMIF formula, and then format the results into a legible report.

Instead, you should use pivot tables! They're fast and easy.

Pivot Table

How do you create a pivot table?

Start by selecting any cell in the data range and go to the Insert tab. Click on the Pivot Table button.

Pivot Table Button on Insert Tab

Double-check that the range is correct. Then press OK.

Make sure your range or table is selected in the PivotTable window.

Drag the Region field into the Rows area and the Revenue field into the Values area.

Region to Rows area and Revenue to Values area

That's it! With just a few mouse clicks, you've created the entire report requested by your boss.

Pivot Table

Pivot tables don't require you to write any formulas, and they do all of the formatting work for you. You can also use them to create interactive reports, charts, and dashboards.


For a more in-depth look at pivot tables, check out this tutorial:

How Do Pivot Tables Work?

Be sure to leave questions or feedback in the comments section below. I hope this quick example of pivot tables helps you create summary reports that your boss will appreciate!


Your email address will not be published. Required fields are marked *

  • Thanks. I am stuck on how to create an aimline between two points in one series of a two series chart. There has to be an easier way than what I am doing.

  • Fantastic demonstration of the benefits of using a pivot table, I agree in that it can save so much time in writing formulas when it does it for you!

    Thank you,

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter