I have a simple monthly budget with expense rows and a total at the bottom. I want to filter the table for certain expenses and see the totals revise. Right now my totals do not adjust when data rows are filtered out.

Question

Excel- I have a simple monthly budget across the columns and expenses down the left rows. I want to be able to filter this table for certain expenses and see the total change at the bottom. At present, when I filter out rows, the totals reflect all rows, not just those visible like I need.

Answer 1

To achieve the desired functionality of having your total adjust based on filtered expenses in Excel, you can use the SUBTOTAL function instead of the regular SUM function. The SUBTOTAL function can dynamically calculate totals based on the visible rows after filtering.

Here's how you can do it:

1. Replace your current total formula with =SUBTOTAL(109, range), where range is the range of cells you want to sum. The number 109 specifies that you want to sum only the visible cells (ignoring any that are hidden by filters).
2. After applying this formula, when you filter your table, the total at the bottom will automatically update to reflect only the visible expenses.

This way, you can filter your budget for certain expenses and see the totals change accordingly.