Excel summing named range

Question

I have a workbook with many sheets in it. I want to sum column C2:C50 on each sheet. However, in the future I anticipate having to sum the range C2:C?, and rather than have to go into each sheet to amend the formula, is a way where I can define the range of cells just once and use that reference in my individual worksheets? Something like myrange =C2:c50 and then on each sheet have the formula =sum(myrange). How do I do that, please?

Solution

You can do this with named ranges and the INDIRECT function. From the ribbon, select Formulas, Name Manager, New:

Enter a name for your range, and in the "Refers to" field, enter =INDIRECT("$C$2:$C$50"):

Now, when you refer to the range myrange in a formula, e.g. =SUM(myrange), Excel will sum that range of cells for the current sheet. If you refer to the range in a formula in Sheet1, it will sum that range of cells in Sheet1. If you do the same in Sheet2, the range then applies to Sheet2, and so on.

When you want to change it, you only need to open the name manager, highlight the range in the list, and click Edit. This will change the range and all formulas that use it will change accordingly.

---

Note that if you move cells around, the named range won't update automatically (as it normally does), because you've included the cell references inside a string passed as an argument to INDIRECT. Also, if you're using many of these ranges, you may see a minor performance hit in Excel.