Extend cell ranges in multiple spreadsheet formulas

Question

Level 1

6 points

Extend cell ranges in multiple spreadsheet formulas

I have a budget program I created, it uses daily spend data that i input into one sheet under 10 different categories (dining, groceries, hobbies, fuel, etc.). This is the format:

Date, Expense Description, $$, and Category

Then on the next sheet I read that data and organize and sum them in new buckets by month for each of the 10 different categories, eg:

Jan Feb ….

category 1

category 2

...

I use many formulas for this that look at the appropriate cell ranges to make the calculations. I want to extend the cell ranges for all of the formulas from x to y.

How can I do this easily without having to modify each and every one of the 120 formulas? I was hoping to use a “find and replace” action but I doesn’t seem like that exists for a formula?

Any help is appreciated!! Thank you!

Posted on Aug 6, 2026 11:36 AM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

60,801 points

Aug 7, 2026 1:00 PM in response to mawk1

This sounds like a pivot table to me.

Pivot Tables are designed to automatically sort and categorize data. If I'm reading your post correctly, you're trying to get a sum of expenses for each category, broken down by month, and have it automatically extend to cover all months and categories. That's exactly what what pivot tables do.

I created a dummy table based on your headings:

From here, with any cell in the table selected, choose Organize -> Create Pivot Table -> On Current Sheet.

From here, in the inspector, just tell Numbers what data you want in the columns and rows:

Drag the column,n headers from the 'Fields' section into the relevant sections (Columns, Rows, or Values:

Here I've assigned the Date (in Year-Month breakdown) to the columns, the categories to the Rows, and the $$ to the values (which will be summed by default. This is what I get:

As the dates extend, so will the columns. Likewise for the rows (categories).

Note that Pivot Tables aren't dynamic, so they won't constantly update as you add new expenses. Instead, in the Inspector, click the refresh icon to update the table.

Reply