Automatically Expanding Ranges in Excel 2013

Today I found myself wanting to sum up a bunch of values in a column whose length expands as I add more values. One option is to write a range that is really tall (like D4:D200) but there's the possibility of values eventually being entered after the range, and I'm not really a fan of arbitrary limits, even if they're easy to adjust. I think Excel has a type of range definition that selects the entire column, but my worksheet has unrelated data above where I need to start summing.

I did some research, and it turns out that there are things called dynamic named ranges that can change their selection depending on the value of one or more cells. I'm sure you can do all sorts of amazing stuff with them, but all I needed was the ability to sum as long as there are values.

Named ranges can only be managed with Name Manager, but the new Ribbon UI makes it really hard to access. In fact, the only way to get to it is to open Excel Options and add Name Manager (under All Commands) to the Quick Access Toolbar or a new custom group in the Ribbon. Once you have the button, click it to open the Name Manager, then click the New button in the resulting window.

Write an appropriate name (without spaces) in the Name field, then paste the following into the "Refers to" box:

=OFFSET(Sheet1!$D$4,0,0,COUNT(Sheet1!$D:$D),1)

I underlined the parts you'll need to change. Change "Sheet1" to the name of the sheet containing the range. Change "$D$4" to the starting cell of the data column you want to sum. Change the other "$D" instances to the name of the column in which your data resides.

Be aware that editing that string in the Name Manager may be really inconvenient because Excel tries to "helpfully" shove cell names into the text field whenever you use the arrow keys. You may want to paste into and edit in a Notepad window.

Once you have your custom range, you can use it's name in place of the usual rectangular definitions (your standard F5:J22 stuff). I named my range "Times" and replaced the total cell's formula with =SUM(Times).