Ravindra BagaleCourses & study guides Track your progress

Guides

How to Create a PivotTable in Excel (From Scratch)

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Read this guide in: English · मराठी · हिंदी

A PivotTable summarises rows into totals — for example sales by City — without writing SUMIF formulas by hand.

Chala mitrano! Long sales sheet, boss asks “Mumbai kitna, Pune kitna?” PivotTable answers in clicks. Why? Fast grouping. How? Insert → PivotTable, drag fields. Tiny shop sheet first. Stay with me.

Quick answer

Vertical steps:

  1. Clean headers (one header row).
  2. Click inside the data (or select the Excel Table).
  3. Insert → PivotTable → New Worksheet → OK.
  4. Drag City to Rows.
  5. Drag Amount to Values (Sum).
  6. Read city totals; Refresh after new data.
Data → PivotTable → City in Rows, Amount in Values

Language: English · मराठी · हिंदी

Before and after (look at the tables first)

Before — raw sales rows

City Product Amount
Mumbai Milk 100
Pune Bread 40
Mumbai Eggs 50

After — PivotTable summary

City Sum of Amount
Mumbai 150
Pune 40
Grand Total 190
Before PivotTable Three raw sales rows.

Before

After PivotTable Pivot summarises Mumbai 150 and Pune 40.

After

Why create a PivotTable?

  1. Manual SUMIF for every city gets tiring.
  2. PivotTable lets you slice by City, Product, month — by dragging.
  3. Same engine later powers PivotCharts.

What is a PivotTable?

It is a report layout that groups and aggregates your flat list. You do not destroy the source sheet; the Pivot sits on another sheet (usually).

What happens when you build it

  1. Excel reads the source range (or Table).
  2. Field List shows column names.
  3. City in Rows → one label per city.
  4. Amount in Values → Sum by default for numbers.
  5. Mumbai 100 + 50 = 150, Pune 40, Grand Total 190.

Vertical practice steps

  1. Type the three-row table above (plus headers).
  2. Select A1:C4 (or Ctrl+T first).
  3. Insert → PivotTable → New Worksheet.
  4. Tick City → Rows; Amount → Values.
  5. Confirm Mumbai 150, Pune 40.
  6. Add a fourth sales row, then right-click Pivot → Refresh.

Tips that save pain

  1. Prefer Excel Table (Ctrl+T) so new rows are included after refresh + change source if needed.
  2. Numbers stored as text will not Sum correctly — fix types first.
  3. Blank header cells break field names — fill headers.

Ravindra Bagale's Tip

Students drag Amount into Rows by mistake and wonder why there is no total. Values area = numbers to summarise. Rows area = labels to group. Ekda clear zala ki Pivot “click-click” vatato.

Practice task

  1. Add Channel (Online/Store) to the tiny sheet.
  2. Put Channel in Columns or Filters.
  3. Get Mumbai Online vs Store totals.
  4. Screenshot Field List for your notes.

Learn it properly

Course lessons:

Related guides: SUMIF and SUMIFS · Remove duplicates · FILTER function

Samajla ka? PivotTable = group + total with drag-and-drop. Source sheet stays; summary sheet talks. Aata pudhe IF function for High/Low.

Frequently asked questions

What is a PivotTable?

A summary tool that groups and totals rows without writing formulas. You drag fields into Rows, Columns, Values and Filters.

Do I need formulas?

Not for a basic Sum by City. PivotTable builds the totals for you.

Why is my PivotTable empty?

Fields are not placed yet, or the selected range missed headers. Drag at least one field to Values.

Source data changed — now what?

Right-click inside the PivotTable → Refresh. If you added rows outside the old range, change the data source.

Excel Table vs plain range?

Ctrl+T makes refresh and structured names easier when you add new months of sales.

Course chapter?

Chapter 7 PivotTables — Creating a PivotTable. Linked at the end.