Ravindra BagaleCourses & study guides मराठी Track your progress

Change Sum to Average in an Excel PivotTable and Create a Pivot Chart (Clustered Column) for a Dashboard

By Last updated:

← All Excel dashboards

A pivot that adds profit is a good start, but the question is often "what is the average profit per order in each city?". And a dashboard rarely shows a pivot at all: it shows a chart built on it. In this chapter you change Sum to Average, tidy the pivot so it is easy to read, and turn it into a Pivot Chart that changes when the pivot changes.

What you'll learn in this chapter

  • Value Field Settings: Sum, Count, Average, Max, Min
  • Number format and All Borders on a pivot
  • Insert a Pivot Chart (Clustered Column)
  • A chart that follows the report filter
  • Where pivots, Pivot Charts and KPI cards fit in a dashboard
  • A quick reminder about hidden rows before you summarize

Practice file: excel-pivot-average-pivotchart-practice.xlsx: Orders (the table named Orders) and Pivot answers (Average of Profit by city, and delivered profit by city with a column chart).

Made-up data: the 24 orders are made up for practice. They imitate a quick-commerce app such as Blinkit or Amazon Now in Pune, Nashik, Solapur, Kolhapur, Nagpur and Sambhaji Nagar.

1. From Sum to Average

Build the city-wise profit pivot from the first PivotTable chapter: City in Rows, Profit in Values (Sum of Profit). Then:

  1. In the Values area, click Sum of Profit and choose Value Field Settings (or right-click a number in the pivot > Value Field Settings).
  2. On Summarize value field by, choose Average.
  3. Click OK.

The heading becomes Average of Profit:

Row Labels Average of Profit
Kolhapur 16.00
Nagpur 38.50
Nashik 9.00
Pune 1.00
Sambhaji Nagar 20.33
Solapur 2.00
Grand Total 13.63

Each city's average is its profit divided by its number of orders: Nagpur 154 ÷ 4 = 38.50, Sambhaji Nagar 61 ÷ 3 = 20.33. The Grand Total is not the average of the six cities: it is 327 ÷ 24 = 13.63, the average of all 24 orders. The same box offers Count, Max, Min and Product.

Classroom line

If you don't want the sum but the average, select Average and click OK.

Value Field Settings > Average Value Field Settings Summarize value field by Sum Count Average Max Min Product Number Format Row Labels Average of Profit Kolhapur 16.00 Nagpur 38.50 Nashik 9.00 Pune 1.00 Sambhaji Nagar 20.33 Solapur 2.00 Grand Total 13.63 Nagpur: 154 ÷ 4 = 38.50 Grand Total: 327 ÷ 24 = 13.63 (all orders)

Figure 1. Value Field Settings > Summarize value field by: Average. Sum of Profit becomes Average of Profit: Nagpur 154 ÷ 4 = 38.50; Grand Total 327 ÷ 24 = 13.63.

2. Make the pivot readable

  1. Number format: in Value Field Settings, click Number Format (bottom left), choose Number with 2 decimal places, OK. Unlike formatting the cells directly, this format stays when the pivot refreshes or changes shape.
  2. Borders: select the pivot, then Home > Borders > All Borders.
  3. Style: the Design tab of the pivot has ready-made PivotTable Styles if you prefer colour bands.
  4. Heading: click the header cell "Average of Profit" and type a shorter one, such as Avg profit per order.

3. Insert a Pivot Chart

Switch the value back to Sum (Value Field Settings > Sum) for this part. Then:

  1. Click any cell of the pivot.
  2. PivotTable Analyze > PivotChart.
  3. Choose Column > Clustered Column, click OK.

A column chart appears with one bar per city, in the same A to Z order as the pivot. It has field buttons (City, Sum of Profit) on it; you can hide them from PivotChart Analyze > Field Buttons for a cleaner look.

The chart follows the pivot. Drag Status into Filters and choose Delivered: the bars change at once to 60, 54, 20, 14, 65 and 14, and the tallest bar moves from Nagpur to Sambhaji Nagar. Nothing on the chart had to be edited.

Pivot Chart (Clustered Column) follows the report filter Status (All): Sum of Profit 64 Kolhapur 154 Nagpur 36 Nashik 6 Pune 61 Sambhaji 6 Solapur Status = Delivered: Sum of Profit 60 Kolhapur 54 Nagpur 20 Nashik 14 Pune 65 Sambhaji 14 Solapur PivotTable Analyze > PivotChart > Column > Clustered Column. Bars follow the pivot's A-Z order.

Figure 2. A Clustered Column Pivot Chart on the city-wise profit pivot. All orders: Nagpur tallest at 154. With the report filter Status = Delivered: Sambhaji Nagar tallest at 65.

Ravindra Bagale's Tip

Before you share a Pivot Chart, check its first bar against the pivot cell beside it, and the pivot's Grand Total against a formula on the table. One check takes ten seconds and catches a wrong filter before your boss does.

4. Where this fits in a dashboard

A dashboard is one screen that answers the main questions at a glance. It is built in this order:

  1. Raw data as it arrives.
  2. An Excel Table (Ctrl+T, a name).
  3. PivotTables, one per question: profit by city, orders by payment mode, orders by status.
  4. Pivot Charts on those pivots (column, bar, line, pie), plus KPI cards: single big numbers such as Total Orders 24 or Total Profit 327.
  5. The dashboard sheet that puts the charts and cards together.

The pivots do the work, but they usually sit on a separate sheet. The dashboard shows the charts and cards, because a reader sees a tall bar faster than a number in a table.

Classroom line

We don't show the pivot table on the dashboard. On the dashboard we show the chart.

A dashboard shows cards and charts; pivots work behind it Total Orders 24 Total Profit 327 Delivered 18 Profit by city (column) Orders by payment (pie) Orders by status (bar) Pivots sheet Pivot 1: profit by city Pivot 2: by payment mode Pivot 3: by status feeds the charts → Raw data → Excel Table → PivotTables → Pivot Charts + KPI cards → Dashboard

Figure 3. A dashboard sheet: KPI cards on top (Total Orders 24, Total Profit 327, Delivered 18), Pivot Charts below. The pivots that feed them sit on another sheet.

5. Before you summarize: hidden rows

A pivot uses every row of its source, hidden or not. Formulas next to the data can also surprise you: a COUNT or SUM includes rows someone hid by hand. Two quick checks from the SUBTOTAL vs SUM chapter:

  1. Look at the row numbers: a jump (4, then 6) with a double line means row 5 is hidden. Select rows 4 to 6, right-click, Unhide, or drag the double line down to open the row again.
  2. Compare =COUNT(B2:B16) with =SUBTOTAL(102,B2:B16). If COUNT says 15 and SUBTOTAL 102 says 14, one row with a number is hidden.

6. The whole chapter on one page

Task How
Sum to Average Values > Sum of Profit > Value Field Settings > Average
Number format that sticks Value Field Settings > Number Format
Borders Select the pivot > Home > Borders > All Borders
Pivot Chart PivotTable Analyze > PivotChart > Clustered Column
Chart for delivered orders Status in Filters > Delivered
  1. Value Field Settings changes Sum to Average, Count, Max or Min.
  2. The Grand Total average is over all orders, not over the city averages.
  3. Format numbers through Value Field Settings so they survive a refresh.
  4. A Pivot Chart follows every change of its pivot, including filters.
  5. Dashboards show charts and KPI cards; the pivots work behind them.

7. Try at home

Try at home

Task 1

  1. Set the report filter to Delivered and the value to Average. What is the average delivered profit in Sambhaji Nagar? (32.50.)

Task 2

  1. Change the value to Max. Which city has the single most profitable order, and how much? (Nagpur, 100.)

Task 3

  1. Make a Clustered Column Pivot Chart of Count of Order_ID by Payment_Mode. How tall are the two bars? (COD 9, Online 15.)

Recap

In short

  1. Value Field Settings switches Sum to Average (or Count, Max, Min).
  2. Number Format inside Value Field Settings keeps the format.
  3. PivotTable Analyze > PivotChart > Clustered Column draws the pivot.
  4. The chart follows the pivot's filters at once.
  5. Raw data → Table → Pivots → Charts and KPI cards → Dashboard.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: PivotTable average and Pivot Chart. Example data table with Average profit per city: Pune ₹150, Nashik ₹90; Keep the ₹ format: ₹150; Pivot Chart: 2 bars.
Card 1 of 2: PivotTable average and Pivot Chart · Download PNG
Revision card: Dashboard flow. Covers 1; 2; 3; 4; 5. With a photo of Ravindra Bagale and a short tip.
Card 2 of 2: Dashboard flow · Download PNG

More cards for every chapter: Ravi Sir's Cards.