Learnerix
Learnerix Blog

Excel PivotTables for Beginners: Build and Check a Report

Learnerix Blogger · · 6 min read

To build your first Excel PivotTable, begin with a clean table and a question that needs a summary. Put a category in Rows, a numeric amount in Values and an appropriate condition in Filters. Then check the result against the source records. The important skill is producing an answer you can explain and refresh, not only creating a table that looks finished.

TL;DR

  • Use one header row and one record per source row, with numbers stored as numbers.
  • Decide which records belong in the answer before arranging the PivotTable fields.
  • Check that Values uses the calculation you intend, such as Sum rather than Count.
  • Add a test record, refresh and reconcile the result before treating the report as ready.

Define one question before opening the field list

Use a small fictional example: a stationery supplier wants the confirmed order value by product category. This is a learning exercise, not a real business report. The question tells you that Category, Value and Status must be available in the source data.

Write down your rule: include Confirmed records and exclude Cancelled records. Keep the rule visible on a Notes sheet. If another person later asks why your total differs from theirs, you can compare the definition instead of guessing which spreadsheet is wrong.

Choose a period only if your data contains meaningful dates. For a first exercise, you can use one month of fictional records and state that scope explicitly. Do not label a report monthly if the source includes several months and you have not applied the intended condition.

Build a source table you can inspect by eye

Create columns named Order ID, Category, Value and Status. Enter six practice records: A101, Pens, 120, Confirmed; A102, Paper, 200, Confirmed; A103, Pens, 80, Cancelled; A104, Notebooks, 150, Confirmed; A105, Paper, 50, Confirmed; and A106, Pens, 30, Confirmed.

Put each record on its own row and each value in the appropriate column. The semicolons above separate records; they are not instructions to paste the entire list into one cell. Confirm that the Value cells are numeric and that the status spelling is consistent.

Convert the range to an Excel table if that fits your version and workflow, and give it a useful name such as PracticeOrders. Keep the manual totals outside the source table. Merged title cells, decorative blank rows and subtotal lines inside the records can make a simple analysis unnecessarily difficult.

Create the PivotTable using the appropriate platform instructions

Menu labels and some behaviour vary between Excel platforms. Microsoft's official PivotTable guide provides the current platform-specific instructions. For a typical desktop workflow, select the source and use Insert, then PivotTable, choosing a separate worksheet for the report.

Check the selected source name or range in the dialog. Do not assume that the visible selection includes every record you intended. A report can produce plausible totals even when it is based on only part of the data. Keep the original source available so that you can investigate the answer.

Name the report sheet clearly, such as Category Summary. Place a short description of the question above or beside the PivotTable. The reader should not have to infer the meaning of the report only from technical field names such as Sum of Value.

Put the fields where they answer the question

Place Category in Rows and Value in Values. You should see an amount for each product category. Add Status to Filters and choose Confirmed for this exercise. Check the calculation shown for Value: Sum answers a total-value question, while Count answers a record-count question.

If you unexpectedly get Count, inspect the source rather than immediately disguising the result. Some entries that look like numbers may be text, or you may have chosen the wrong field. Correct the source appropriately, refresh and confirm the intended calculation again.

Apply a readable number format to the values. You may label this fictional example in AED, but formatting does not convert currencies or determine whether an amount should be included. Keep the arithmetic and the reporting definition separate from the visual appearance.

Reconcile the first result manually

For the six records above, the confirmed total is 550: Pens is 150, Paper is 250 and Notebooks is 150. The cancelled Pens record of 80 is excluded. These figures are the expected answers for this specific fictional dataset, not a benchmark for a real company.

Select the corresponding source records and check the arithmetic yourself. If your grand total is 630, investigate whether the Cancelled record is included. If a category is missing, inspect its spelling and the source boundaries before assuming that Excel lost the record.

Write down the check beside the report. A note such as “Confirmed source values reconcile to 550” explains what you verified. It is more useful than a general statement that the workbook has been checked, because another learner can repeat the same test.

Test the refresh behaviour with a deliberate change

Add A107, Notebooks, 40, Confirmed to the source. Ensure the record belongs to the table or range used by the PivotTable, then refresh the report. The new expected confirmed total is 590, with Notebooks increasing to 190. Check both the category and grand totals.

If the result stays at 550, investigate whether you refreshed and whether the new row belongs to the source. Avoid manually typing the expected answer into the report to make it look correct. The point of the exercise is to understand how the report responds to changes.

Keep a note that identifies the test row. If you remove it afterwards, refresh again and confirm the original 550 result. A useful handover explains how to update the source and repeat this check, rather than relying on the next person remembering your demonstration.

Add detail only when it serves another question

You could add a second field to Columns to compare periods, but do that after your basic total reconciles. Each added field changes how the answer is grouped. Ask what decision the extra detail supports before turning a small report into a crowded dashboard.

A chart can help a reader compare categories, but the table should remain understandable without it. Use a clear title and label the scope. Avoid presenting a fictional practice report as evidence of actual sales performance, a client project or a financial recommendation.

Once the first example works, change the dataset and rebuild the report without following every instruction. Introduce one deliberate exception, such as a blank status, and explain how you will handle it. This tests whether you understand the reporting choices rather than only remembering which fields to drag.

Continue from summary tables to a complete workflow

The broader Excel skills roadmap connects tables, formulas, lookups and reporting checks. The Excel for Work course includes a workbook capstone and a clearly labelled AI course tutor, Rami. Use a fictional example when asking for help, and bring your question, source definition and reconciliation to the conversation.