Pivot Table in Excel for Accountants: Step by Step with Example

What is a pivot table in Excel?

Microsoft describes a PivotTable as a tool to calculate, summarise and analyse data so you can see comparisons, patterns and trends. In plain words: you give Excel a list with column headings, then you choose which heading should become the rows, which the columns, and which numbers to add up. Excel builds the summary.

Your original data is not changed, so you can rearrange the pivot as many times as you like. Accountants use it on exports from Tally, bank statements, sales registers and expense sheets, where the same ledger or party appears many times. A month-end expense summary that takes an hour with formulas often takes a minute with a pivot.

It works best when the data is clean and has one heading per column, which the next section explains. You can add or remove fields whenever the question changes, and the totals update at once.

How should you prepare accounts data before making a pivot table?

Most pivot problems are really data problems. Before you insert anything, check these points on your data sheet:

• One header row, with a different name in every column, and no blank header cells.
• No blank rows or columns in the middle of the data, and no merged cells.
• One fact per cell: the date in one column, the ledger in another, the amount in another.
• Real dates and real numbers. A date typed as text, or an amount with a stray space, will not group or add up properly. Our guide to Excel formulas for accountants shows the TRIM and VALUE habits that clean this.
• No total row inside the data, because the pivot would count it twice.

It also helps to turn the list into an Excel table with Ctrl+T, so the pivot picks up new rows when you refresh it. If your data comes from Tally, the export option in TallyPrime (Alt+E, then choose Excel as the format) gives you a list in this shape; see Tally’s export guide.

How do you create a pivot table in Excel, step by step?

Here is a small expense list with 12 entries. It is an illustration, but the steps are the same for 12,000 rows.

1. Click any cell inside the list.
2. Go to Insert, then PivotTable. Excel selects the data range. Choose New Worksheet and click OK. If you are unsure how to lay it out, Insert, then Recommended PivotTables, shows a few ready-made options.
3. Use the PivotTable Fields pane. Drag Expense head into Rows and Amount into Values. Excel adds the amounts up and labels the field “Sum of Amount”.
4. Add the months. Drag Date into Columns. Depending on your Excel version, dates may group into months on their own. If they don’t, right-click a date in the pivot, choose Group and select Months (add Years if your data covers more than one year, or January of two years will merge).
5. Check the total. The grand total must equal the total of the data. Here, it is ₹2,86,650.
6. Format the numbers. Right-click a number, choose Value Field Settings, then Number Format, and pick the number style you use in your reports.

The result should look like this:

Read it as an accountant: rent is a steady ₹25,000 every month, salaries step up in March, and electricity rises each month. Stationery appears only in February. The grand total, ₹2,86,650, is the same as the sum of the 12 entries, which is the check that your pivot is complete. To change the question, drag fields around: put Party in Rows instead, or move Date to Rows for a month-wise list.

How do you refresh, filter and change a pivot table?

• Refresh: a pivot does not update when you add data. Right-click inside it and choose Refresh. For several pivots, use PivotTable Analyze, then Refresh All.
• Change the calculation: in Value Field Settings under Summarize Values By, choose Sum, Count or Average. Use Count to see how many vouchers, Sum to see the money.
• Filter: drag a field such as Party into Filters to see one party at a time, or use a slicer to filter with a click.
• Show percentages: Value Field Settings also offers Show Values As, for example the percentage of the grand total, which tells you where the money goes.

Which pivot tables do accountants build most often?

These are the same reports an MIS role produces every month. If you are heading that way, our MIS course syllabus and jobs guide shows where pivots fit.

What mistakes make a pivot table wrong?

• Forgetting to refresh after the data changes, so the pivot shows old numbers.
• Numbers stored as text, which makes the pivot count instead of add. If the field says “Count of Amount”, check the data.
• Blank cells in the data, which show up as “(blank)” rows. Fill or delete them at the source.
• Typing over the pivot. Never edit the values inside it; change the source data and refresh.
• A total row inside the source, which doubles every total.
• Not tying out. Compare the grand total with your books or your Tally trial balance; our trial balance format with an example explains the check.

Pivot table or VLOOKUP and XLOOKUP: which one should an accountant use?

Use a pivot table to summarise many rows, and use VLOOKUP or XLOOKUP to fetch one matching value for each row. They solve different problems, and accountants usually need both. A pivot answers “how much did we spend on each expense head each month?”. A lookup answers “what is the party name for this ledger code?” or “which GST rate applies to this item?”.

A common order is to bring the missing columns in with a lookup first, then build the pivot on the completed data. The Excel formulas guide for accountants explains VLOOKUP, XLOOKUP and SUMIFS step by step, and the Advanced Excel course in Delhi practises all of them on accounts data.

When should you use a pivot table instead of SUMIFS?

Use a pivot when you want to look at a list from different angles quickly and the layout will change. Use SUMIFS when you need one fixed number in a fixed cell, such as the rent total in a monthly report that is built once. Many accountants use both: a pivot to explore and a formula to feed a final report. Pivots refresh with a click, but formulas update on their own. The formulas are explained in our guide to Excel formulas for accountants, linked above.

How we teach this at IPA

At IPA, the Institute of Professional Accountants (est. 2003), pivot tables are part of the data analysis block of the Advanced Excel course, alongside Power Query and Power Pivot, and the course also covers lookups, charts, dashboards, data validation, macros and VBA basics, and an Excel for finance module. It runs for 2 months, in the classroom or in live online batches, with weekend batches for working professionals. The course carries placement support, and a free demo class is available for every course. If your data starts in Tally, the Tally course teaches you to produce the reports that you then analyse in Excel.

Frequently asked questions