Spreadsheets

Show each category as a percentage of total in a pivot table

Show each expense category as a percentage of total in an Excel or Google Sheets pivot table using Show Values As, and learn which percent option to pick.

Editorial illustration for Show each category as a percentage of total in a pivot table

In Excel, add Amount to Values a second time, right-click it, choose Show Values As, then % of Column Total. In Google Sheets, open the value in the editor and set Show as to % of column. Each category now shows its share of total spend, and the shares add up to 100 percent.

How do I do it in Excel?

  1. Build the pivot with Category in Rows and Amount in Values, so you see Sum of Amount.
  2. Drag Amount into Values a second time. A new column called Sum of Amount2 appears.
  3. Right-click any number in that second column and choose Show Values As.
  4. Pick % of Column Total, or % of Grand Total if you have months in Columns and want each cell as a share of the whole.
  5. Rename the heading to Share by typing over it, and keep the first Amount column beside it for the rupee value.

How do I do it in Google Sheets?

  1. Create the pivot with Category in Rows and Amount in Values, summarised by SUM.
  2. Click Add next to Values again and pick Amount a second time.
  3. On that second Amount entry, find Show as and open the drop-down.
  4. Choose % of column. Other choices are Default, % of row and % of grand total.
  5. Type a clearer name in the Values heading, such as Share of spend.

Which percent option should I pick?

Layout% of Column Total% of Row Total% of Grand Total
Only Category in RowsShare of all spendAlways 100%Same as column total
Category by MonthShare within each monthShare of the category over monthsShare of the entire period
Category by Payment modeMix within each modeHow each category is paidShare of the overall total

The question you ask decides the option. If you want to know what fraction of the year went to rent, use grand total. If you want to know what share of each month went to rent, use column total with months in Columns.

What does a worked example look like?

Say a hypothetical month totals ₹60,000: Rent 18,000, Groceries 12,000, Transport 6,000, Dining 7,500, Shopping 9,000 and Other 7,500. Rent shows 30 percent, Groceries 20, Transport 10, Dining 12.5, Shopping 15 and Other 12.5. Added together they make exactly 100. If the shares add up to something else, a filter is hiding a category or the percent was set against a different base.

Why do my percentages look odd?

  • Negative amounts such as refunds reduce a category, and a share can be below zero. Net them against spending before you read the result.
  • Hidden rows or a filtered month change the base. Shares are always of what is currently shown.
  • Both Income and Expense in the same pivot make the total the net figure, so shares can exceed 100 percent. Filter to expenses only.
  • Decimals clutter. Format the share column with one decimal place, for example 12.5%.
  • Duplicate category names split one slice into two. Clean the labels in the source.

How can I use the shares?

A share table is a quick way to see where the month went. It does not tell you what is right for you, as that depends on your household. A useful habit is to note the top three shares every month and look for changes, not to compare against an outside figure. Place the percent column next to a bar chart for a one-glance view.

If you want these shares without rebuilding the pivot each month, SpendixAI offers a library of ready reports grouped by question, plus a Report builder where you filter and group transactions yourself. A pivot remains the quickest way to try an idea.

  • Add Amount twice, once as a sum and once as a percent.
  • Choose the percent base that matches your question.
  • Confirm the shares add to 100 percent.
  • Filter out income and refunds before reading.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports