How to Create a PivotTable in Excel for Beginners: Rows, Values, Count vs Sum and Report Filter (Blinkit Orders Example)

By Ravindra Bagale · Last updated: 15 October 2026

Practice file: excel-first-pivottable-practice.xlsx: Orders (the table named Orders) and Pivot answers (every pivot result in this chapter, worked out with formulas so you can check yours).

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 raw data to a dashboard

A dashboard is built in steps, and each step uses what you learned before:

1. Raw data: the orders as they arrive, in a plain range.
2. Excel Table: Ctrl+T, a name, filters (Table vs Range).
3. PivotTables: summaries by city, payment mode, status (this chapter).
4. Pivot Charts and KPI cards: charts and big single numbers built on the pivots.
5. Dashboard: all of it on one screen.

Before you build anything, decide what you want to know: how many orders, profit by city, how many were paid online, how many were delivered late. Each question becomes one pivot.

2. Insert a PivotTable

1. Click any cell inside the Orders table.
2. Insert > PivotTable (in Excel 365 choose From Table/Range), or Table Design > Summarize with PivotTable.
3. Table/Range already says Orders.
4. Choose New Worksheet and click OK.

A new sheet opens with an empty pivot area on the left and the PivotTable Fields pane on the right. The top of the pane lists the 13 column names; the bottom has four boxes:

1. Filters: limits the whole pivot to some values (a report filter).
2. Columns: values across the top.
3. Rows: values down the side.
4. Values: the numbers being counted or added.

Using the table name as the source means new orders added to the table are included the next time you click PivotTable Analyze > Refresh.

3. Values: Count or Sum?

1. Drag Order_ID into Values. The pivot shows Count of Order_ID: 24. Order IDs are text: they cannot be added, so Excel counts them.
2. Remove it (drag it out of the box) and drag Profit into Values: Sum of Profit: 327. Profit is a number, so Excel adds it.

The rule: text and dates go to Rows (or Columns, or Filters); numbers go to Values. A text column dropped in Values is counted; a number column is summed. To change it, click the field in Values > Value Field Settings and pick Sum, Count, Average, Max or Min.

4. City-wise profit

1. Drag City into Rows.
2. Keep Profit in Values.

1. Each city appears once, even though Pune has 6 orders: the pivot groups them.
2. The cities are sorted A to Z.
3. Grand Total is added for you.

When you tick a text field's box in the pane instead of dragging, Excel puts it in Rows by itself.

5. More pivots on the same sheet

You can build another pivot next to the first one without starting again:

1. Click inside the first pivot, then PivotTable Analyze > Select > Entire PivotTable.
2. Press Ctrl+C.
3. Click an empty cell a few columns to the right, for example D3, and press Ctrl+V.
4. Click inside the copy and change its fields: remove City and Profit, put Payment_Mode in Rows and Order_ID in Values.

Or insert a fresh one: Insert > PivotTable, choose Existing Worksheet, click in Location and click the cell where it should start (Excel writes something like Sheet2!$D$3), then OK.

Leave at least one empty column between pivots: a pivot that grows must not run into its neighbour.

Each Grand Total is 24. In pivot 3, (blank) is the 3 cancelled orders, which were never delivered and so have no Delivery_Status.

6. Report filter: profit of delivered orders only

The city-wise profit in section 4 includes cancelled and returned orders. Nagpur's 154 contains 100 from a cancelled order (ORD1006) that never earned anything.

1. Click inside the city-wise profit pivot.
2. Drag Status into Filters. A filter cell appears above the pivot: Status (All).
3. Open it, choose Delivered, click OK.

The best city changes from Nagpur to Sambhaji Nagar once only delivered orders count. This is the same answer as =SUMIFS(Orders[Profit],Orders[Status],"Delivered") from the SUMIFS chapter, built without a formula.

7. The whole chapter on one page

1. Raw data → Table → PivotTable → Pivot Charts and KPI cards → Dashboard.
2. Insert > PivotTable from the table, New Worksheet.
3. Text in Values = Count; numbers in Values = Sum.
4. Pivots group, sort A to Z and add a Grand Total.
5. Copy a pivot with Select > Entire PivotTable, Ctrl+C, Ctrl+V; or Existing Worksheet + Location.
6. A report filter answers "for delivered orders only".

8. Try at home

9. Going further: a second practice table

Sections 1 to 8 used the 24 grocery orders. The next sections need a Revenue and a Cost column, so we use a small shop sales table instead. It has 12 rows, one row per sale, and is saved as an Excel Table named Sales (Ctrl+T, then type the name in Table Design > Table Name).

Made-up data for practice: every name, city figure and amount is made up. Download it: pivottable-going-further-practice.xlsx (sheet Sales = the table above; sheet Answers = every result below worked out with formulas, so you can check your pivots).

Before you build a pivot on any data of your own, check the source once:

1. Row 1 has a heading for every column, and no two headings are the same.
2. There are no fully blank rows or columns inside the data.
3. Revenue and Cost are real numbers, not text (numbers sit on the right of the cell).
4. Dates are real Excel dates, not text, or they cannot be grouped into months later.
5. The data is an Excel Table with a clear name such as Sales. Ctrl+T makes a Table, not a PivotTable: the Table is the source, the PivotTable is the summary.

A PivotTable never changes this source data. It only reads it and builds a summary you can rearrange.

10. Each city's share of total revenue (% of Grand Total)

Question: Pune sells the most, but what share of all revenue does it bring? You do not need a formula for this.

1. Click inside the Sales table, then Insert > PivotTable > New Worksheet > OK.
2. Drag City into Rows.
3. Drag Revenue into Values. Drag Revenue into Values a second time. You now have Sum of Revenue and Sum of Revenue2.
4. Right-click any number in the Sum of Revenue2 column.
5. Choose Show Values As > % of Grand Total.
6. Click the heading cell of that column and type a clear name: Revenue Share %.

Pune's share is 92,000 ÷ 2,40,000 = 38.33%. The shares always add up to 100%. The same menu has Running Total In: once dates are grouped by month (see Group dates by month), it shows January 75,000, February 1,50,000, March 2,40,000.

11. Show only the top 3 cities (Value Filters)

1. Use the pivot from section 10 (City in Rows, Sum of Revenue in Values).
2. Click the drop-down arrow on the Row Labels cell.
3. Choose Value Filters > Top 10.
4. Change 10 to 3, keep Items and Sum of Revenue, click OK.

Result: Pune 92,000, Nashik 54,000 and Kolhapur 49,000. Nagpur is hidden, and the Grand Total now shows only the visible cities: 1,95,000. On a big table, use the same steps with 5 for a "Top 5 products" report. To remove it: drop-down arrow > Clear Filter From "City".

12. Profit and margin with a Calculated Field

The Sales table has Revenue and Cost but no Profit column. A Calculated Field is a new field you create inside the PivotTable with a formula that uses other fields.

1. Build a pivot: Category in Rows, Revenue and Cost in Values.
2. Click inside the pivot, then PivotTable Analyze > Fields, Items & Sets > Calculated Field.
3. In Name type Profit.
4. In Formula type =Revenue-Cost (double-click a field in the list to insert its name).
5. Click Add.
6. Now type Margin in Name and =(Revenue-Cost)/Revenue in Formula, click Add, then OK.
7. Right-click a Margin number > Number Format > Percentage, 1 decimal place.

A calculated field always works on the totals of each row of the pivot. Grocery's margin is 12,500 ÷ 49,000 = 25.5%: total profit divided by total revenue. That is the correct margin.

The wrong way is to work out a margin % on every sale and then average those percentages. Grocery's five sales have margins 25%, 22.2%, 25%, 28.6% and 25%; their average is 25.2%, not 25.5%. The small sales count as much as the big ones, so the average is off. Always divide total profit by total revenue.

A calculated field cannot use cell references like A2, and it always adds (Sum) first. Keep its formulas to simple field maths like the two above.

Same margin with the Data Model (DAX measure). If you tick Add this data to the Data Model in the Create PivotTable box, Calculated Field is not available. Instead you add a measure: right-click the table name Sales in the PivotTable Fields pane > Add Measure. A measure is a formula written in DAX, the formula language of the Data Model. If your table has a Profit column, the margin measure is:

Profit Margin := DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]))

In our Sales table there is no Profit column, so write it as Profit Margin := DIVIDE(SUM(Sales[Revenue]) - SUM(Sales[Cost]), SUM(Sales[Revenue])). DIVIDE divides the first number by the second and shows a blank instead of an error when revenue is 0. Format the measure as a percentage. The result is the same: Grocery 25.5%. More on this in Power Pivot and the Data Model.

13. Count unique salespeople (Distinct Count)

Question: how many different salespeople sold in each city? Normal Count counts rows, so Pune would show 4 (four sales), but only 3 people made them.

1. Click inside the Sales table, Insert > PivotTable.
2. Tick Add this data to the Data Model, choose New Worksheet, click OK.
3. Drag City into Rows and Salesperson into Values.
4. Click Count of Salesperson in Values > Value Field Settings > choose Distinct Count > OK.

Result: Kolhapur 3, Nagpur 1, Nashik 3, Pune 3. The Grand Total is 4, not 10, because only four different people (Shahrukh, Zoya, Raja, Rani) appear in the whole table. Distinct Count appears in the list only when the pivot uses the Data Model.

14. Pull one pivot number into a cell (GETPIVOTDATA)

GETPIVOTDATA reads one value out of a PivotTable, for example to show it in a report cell. Say the city pivot from section 10 starts at cell A3 (Row Labels is in A3):

=GETPIVOTDATA("Revenue",$A$3,"City","Pune")

1. "Revenue" = the value field to read (Sum of Revenue).
2. $A$3 = any cell inside the pivot, so Excel knows which pivot.
3. "City","Pune" = the row to read: City is Pune.

Result: 92000. You rarely type it: in an empty cell type = and click Pune's revenue in the pivot, and Excel writes the GETPIVOTDATA formula for you. Unlike a plain =B7, it still finds Pune after the pivot is sorted or filtered. The KPI card chapter uses it for dashboard cards.

15. Common PivotTable mistakes and fixes

16. 20 pivot questions, from beginner to expert

Every question below can be answered on the Sales table by moving fields, without SUMIFS or COUNTIFS. Try them in order.

17. PivotTable interview questions

What is the difference between an Excel Table and a PivotTable?
An Excel Table holds and organises the source rows (Ctrl+T). A PivotTable reads those rows and shows a summary you can rearrange.

What is the difference between a report filter and a slicer?
Both filter the pivot. A slicer shows clickable buttons, shows what is selected at a glance, and one slicer can control several pivots built on the same source (Slicer > Report Connections).

Can one PivotTable show several Values fields?
Yes. Section 12 shows Sum of Revenue, Sum of Cost, Profit and Margin in one pivot.

What does GETPIVOTDATA do?
It returns one value from a pivot by field and item names, for example =GETPIVOTDATA("Revenue",$A$3,"City","Pune") = 92000.

Calculated field or DAX measure?
A calculated field works in a normal PivotTable and uses simple field maths on totals. A DAX measure works in a Data Model pivot and can do more, such as Distinct Count style logic, relationships between tables and safe division with DIVIDE.

Does a PivotTable update by itself when the data changes?
No. Click PivotTable Analyze > Refresh (or right-click > Refresh).

18. Practice on the Sales table

Recap

Quick revision cards

Englishमराठीहिंदी

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.

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