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?
- Build the pivot with Category in Rows and Amount in Values, so you see Sum of Amount.
- Drag Amount into Values a second time. A new column called Sum of Amount2 appears.
- Right-click any number in that second column and choose Show Values As.
- Pick % of Column Total, or % of Grand Total if you have months in Columns and want each cell as a share of the whole.
- 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?
- Create the pivot with Category in Rows and Amount in Values, summarised by SUM.
- Click Add next to Values again and pick Amount a second time.
- On that second Amount entry, find Show as and open the drop-down.
- Choose % of column. Other choices are Default, % of row and % of grand total.
- 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 Rows | Share of all spend | Always 100% | Same as column total |
| Category by Month | Share within each month | Share of the category over months | Share of the entire period |
| Category by Payment mode | Mix within each mode | How each category is paid | Share 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.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


