Pivot Table in Excel: Turn Raw Sales Into a Monthly Report (2026)

Try this quick self-test to see how much a pivot table in Excel could help you. Your manager sends you a sheet with 1,240 rows of sales and asks three things: which product category earned the most, how the two branches compare, and which month was weakest. If your plan involves sorting, copying and adding with a calculator, this tool will save you most of that afternoon. If you already know how to drag fields into Rows and Values, skip ahead to the sections on grouping dates, percentages and slicers.

A pivot table reads a long list of records and turns it into a short summary you can rearrange with the mouse. No formulas are typed, and the source data stays untouched. This Excel pivot table for beginners walkthrough builds one report from start to finish for an example business, so every click has a reason behind it.

Here is what you'll be able to do after this walkthrough:

• Prepare a messy sales sheet so Excel can summarize it correctly
• Build a category-by-branch report in under five minutes
• Turn daily dates into months and quarters with one right-click
• Show each category as a share of total sales and rank the results
• Add slicers and a chart, and fix the errors that confuse most beginners

What Is a Pivot Table in Excel, in Plain Words?

Think of a pivot table in Excel as a question-answering machine for a list. You give it a table where each row is one record, such as one sale, one fee payment or one attendance entry. Then you tell it which column to group by and which column to add up. Excel does the grouping and the totals for you.

The word "pivot" means you can turn the report around. Put branches across the top and categories down the side, then swap them with one drag. The numbers recalculate instantly because Excel isn't editing your data. It only reads it.

In Pakistani offices, you'll find a pivot table in Excel behind school fee collection summaries, shop sales reports, stock movement sheets and HR attendance counts. Interviewers for office and data entry jobs often ask about them, which is why our list of Excel interview questions for office jobs touches on summarizing data.

The Example Business We'll Use

To keep things concrete, this guide follows one example: a stationery shop with two branches, one in Abbottabad and one in Mansehra. The business is invented for teaching, and so are its numbers. The owner keeps every sale from January to June 2026 in one sheet with 1,240 rows and six columns.

The owner wants a one-page summary every Monday. Doing it with filters and the SUM function takes about an hour each week. A pivot table in Excel does it in minutes, and next week it only needs a refresh.

Clean the Sheet First: Five Checks

Most problems with a pivot table in Excel start in the source data, not in the pivot table itself. Microsoft's guide to creating a PivotTable asks for clean, tabular data with one header row and unique column labels. Before you click Insert, run these five checks:

1. One header row. Delete title rows like "Sales Record 2026" above the headers, and unmerge any merged cells.
2. A name for every column. A blank header or two columns both called "Amount" will cause trouble later.
3. No subtotal rows inside the data. If someone typed "Total January" halfway down, delete that row. The pivot table will calculate totals itself.
4. Numbers stored as numbers. Amounts typed with "Rs." inside the cell are text. Keep the currency in the header instead.
5. Real dates in the Date column. A date typed as 14.03.2026 is often stored as text, and text can't be grouped into months.

Then click any cell in the data and press Ctrl + T to turn it into an Excel table, which Microsoft also recommends. A table grows when you add rows below it, so your pivot table picks up new sales after a refresh. If your data arrives from several branch files, our Power Query guide for beginners shows how to combine and clean them first.

Build the Pivot Table in Excel: The Core Clicks

With a clean table in place, the build itself is quick. These steps follow Microsoft 365 and Excel 2021 or 2024 on Windows. Older versions use the same idea with slightly different ribbon names.

1. Click any cell inside the sales table.
2. Go to Insert and choose PivotTable.
3. Confirm the table name in the source box, choose New Worksheet, and press OK.
4. In the field list on the right, tick Category. Because it's text, Excel places it in Rows.
5. Tick Amount (Rs.). Because it's a number, Excel places it in Values and adds it up.
6. Drag Branch into the Columns box.

That's it. You now have four category rows, two branch columns and grand totals on both sides. Rename the header "Sum of Amount (Rs.)" to "Sales" by clicking it and typing, and apply number formatting through Value Field Settings so the commas stay in place after every refresh.

For our example shop, the finished summary reads like this:

Two questions from the self-test are already answered. Notebooks earned the most, and Abbottabad sold more than Mansehra in every category.

Which Box Should Each Field Go In?

The PivotTable Fields pane has four boxes, and beginners often freeze here. A simple rule helps: put the things you want to compare in Rows or Columns, and put the thing you want to measure in Values. Filters is for narrowing the whole report down.

Keep Columns short. Two branches or six months fit nicely across a screen. Two hundred items across the top won't. If a field has many values, it belongs in Rows.

You can also place the same field twice in Values. Drop Amount in once as a Sum and again as a Count, and you'll see both total sales and the number of transactions side by side.

Group Daily Dates Into Months and Quarters

The third self-test question asked for the weakest month. Your sheet has a date for every sale, and if you don't group dates in a pivot table, a raw Date field would give you around 150 separate rows. Grouping fixes that. The steps below follow Microsoft's page on grouping data in a PivotTable.

1. Drag Date into Rows and remove Category for now.
2. Right-click any date in the pivot table and choose Group.
3. Check the Starting at and Ending at boxes, then select Months under By. Add Quarters too if you want a two-level view.
4. Press OK.

Newer Excel versions often group time fields automatically when you add them. If Excel refuses to group your dates, the usual cause is a blank cell or a text entry hiding somewhere in the Date column. Filter that column in the source table, look for anything that isn't a real date, fix it, and refresh.

Grouping works on numbers as well. You can group order amounts into bands such as Rs. 0 to 999, Rs. 1,000 to 1,999 and so on by setting a step value in the same dialog.

Show Values as Percentages, Ranks and Running Totals

Totals in rupees tell the owner how much was sold. Percentages tell him where the money really comes from. Right-click any number in the Values area, choose Show Values As, and pick a calculation from Microsoft's list of Show Values As options. You don't need a single formula.

In our example, switching to % of Grand Total shows notebooks at 36.6%, school bags at 32.0%, pens and art supplies at 17.8% and printing at 13.5%. The owner now knows that two categories bring in more than two-thirds of the revenue.

A good habit is to add Amount to Values a second time and apply the percentage only to the copy. Then the report shows rupees and share next to each other, which is far easier to read in a meeting.

Add Slicers and a PivotChart for the Weekly Meeting

Filters in the field list work, but they're hidden in small drop-down arrows. A slicer in Excel is a panel of big clickable buttons that anyone can use, even someone who has never touched a pivot table.

1. Click inside the pivot table.
2. Go to Insert and choose Slicer.
3. Tick Branch and Category, then press OK.
4. Click a button to filter. Hold Ctrl to choose more than one, and use the Clear Filter icon to reset.

If you build a second pivot table from the same source, such as a month-by-month view, select the slicer and use Report Connections on the Slicer tab. One click on "Mansehra" then filters both reports together. Microsoft's slicer guide notes that this only works for pivot tables sharing the same data source.

For a visual, click inside the pivot table, go to Insert, choose PivotChart and pick a clustered column chart, as described in Microsoft's PivotChart steps. The chart follows the pivot table, so when you click a slicer, the chart changes too. Print the sheet or share a screenshot, and the Monday report is done.

Why Is My Pivot Table Showing Wrong Numbers?

When totals look strange, the cause is almost always one of a handful of things. Work through them in this order before you rebuild anything.

• New sales are missing. Pivot tables don't update on their own, so you need to refresh a pivot table after every change to the source. Right-click inside and choose Refresh, or use Refresh All on the PivotTable Analyze tab to update every pivot table in the file.
• It says "Count of Amount" instead of "Sum". Excel counts a value field when it treats it as text, often because a few cells contain spaces, dashes or "Rs.". Fix those cells, then change the field to Sum in Value Field Settings.
• A "(blank)" row appears. Some rows have an empty Category or Branch. Fill them in at the source.
• The same item shows twice. "Notebooks" and "notebooks " with a trailing space are different values to Excel. Use TRIM or Find and Replace on the source column.
• The range stopped short. If you selected cells instead of an Excel table, new rows below the old range are ignored. Convert the data to a table and change the source once.

Pivot Table vs SUMIFS vs Power BI: When to Use Which

A pivot table in Excel isn't always the right tool. Here's how I'd decide for typical office work in Pakistan:

If lookups are your weak spot, read our comparison of VLOOKUP vs XLOOKUP. If you find yourself building the same pivot tables every week for a whole team, that's the point where the Power BI course becomes worth your time.

Practicing Excel Reports in PSTA's Office Automation Course

Reading about a pivot table in Excel helps. Building ten of them on real-looking data is what makes it stick. PSTA's Certified Office Automation Course runs for 12 weeks at beginner level, and its Excel module covers tables, formulas, formatting and charts. Those are exactly the foundations a clean pivot table depends on. One of the course projects is a monthly expense or sales sheet with totals, formatting and a trend chart.

The current fee is Rs. 12,000 (regular Rs. 15,000), payable in two installments. You can study at the Mandian campus on Old College Road in Abbottabad, join live classes online from any city, or mix the two. If you'd like to go further into reporting, ask about pivot table practice when you contact us, or look at the one-year DIT diploma for a broader qualification. Admission details are on the admission page, and all programs are listed on the courses page. You can also message the team on WhatsApp at +92 314 5424399.

A 30-Minute Exercise for This Evening

Open any list you already have: a household expense record, a class fee register or an old sales sheet. Turn it into an Excel table, insert a pivot table, and answer three questions with it: which group is largest, which month is lowest, and what share each group holds. Then add one slicer. If you can do that without looking back at this page, you already have the pivot table skills needed for most office reporting tasks.