Iâm sure many of you have built a spreadsheet like this before: youâve got a table of data; one of the columns in this table contains a formula which references a span of columns in the same table. The number of columns you need to reference may change over time as requirements change. How do you you build this table in such a way that the number of columns can be changed without breaking the formula that references them?
Letâs walk through a somewhat contrived but simple example to demonstrate the issue. Say Iâm a real estate investor and Iâm tracking a list of houses Iâm interested in purchasing. Iâm a demanding and detail oriented buyer so I want to know the square footage of the individual rooms in the dwelling. Based on this information, I want to calculate the count of rooms in the dwelling as well as the total square feet of the rooms combined. The table might look something like this:
The formula for the â#Roomsâ column looks something like this:
=COUNT( Table1[[#This Row],[SqFt-Room1]:[SqFt-Room3]] )
Now, letâs say I want to add a new house, but this new house has four rooms. Simple, just add a column, right? Not so fast; depending on how we add that column our formulas for â#Roomsâ and âTtl Sq. ft.â may not update as expected.
Or letâs say Iâve changed focus to small condominiums and no longer have a need for the âRoom3â column. If I attempt to delete it, then my formulas will break. How do we avoid this?
Here’s one trick I use in such situations. It may not be the best answer, but Iâll share it with you here and if others have suggestions to offer you can add them to the comments.
Solution: add an extra âdummyâ column before and after the span as end caps, and refer to those columns in your formulas. Using our real estate example, I would add a column before âSqFt-Room1â called âRoomsStartâ and one after âSqFt-Room3â called âRoomsEndâ, like so:
I would keep these columns empty and never put data in them. This is important because otherwise my COUNT and SUM formulas may return the wrong results. Then Iâd highlight the columns a different shade (this is totally optional but I personally like the visual effect of marking off the start and end of the span) and resize them to something very small so they are out of the way for the most part, like so:
Alternatively, you can hide the columns if you so desire. Then Iâd update my formulas so that they referenced these columns instead, like so:
Now, when I want to add a fourth room, I select the âRoomsEndâ column and right-click Insert and I get a new column which I can name âSqFt-Room4â. This new column will automatically be included in my COUNT and SUM calculations. Similarly, if I ever get rid of âSqFt-Room4â, I donât have to worry about breaking any of my formulas. It may not be the most elegant solution, but it gives me a virtually worry-free way to reference a changing number of columns in my table.