Here’s a neat little trick that is really useful: create a named range
CellAbove that dynamically links to the cell above the formula – and then use it in formulas where you need to refer to the above cell. E.g. if you want to have a list of years, starting with a (dynamic) start year stored in cell
A1, instead of writing
=A1+1 in the cell below, use
=CellAbove+1. Now copy this formula down. The advantage? If you insert another row somewhere in this range of years, the new formula will still refer to the row above – while the normal formula will refer now two rows above.
So, in order to set up this name, do the following:
- Select cell
- In the “Formula” tab of the ribbon, click on the “Name manager”, select “New”
- Enter the name
CellAboveas name, select the current worksheet as scope
- In the “Refers to field”, select the cell above, i.e. cell
A1. Important: Make sure to remove the
$(use F4 to toggle them)
- Press “OK” and close the name manager
Small limitation: This name will always refer to the cell above on Sheet1! If you want to use this approach on multiple sheets, repeat the steps above, but replace Sheet1 with the target sheet.
Pro tip: As I use this really often, I create a small macro in my personal macro workbook that does the job – and assigned the macro to Ctrl–Shift–A:
- Open the Visual Basic editor (Alt–F11)
- In the tree panel on the left, expand “VBAPRoject (personal.xlsm)”.
- Right click “Modules”->”Insert->”Module”
- Double click on the new module
- Paste the code below in the main code window
- Save (Ctrl–S) the macro and close the editor
- In Excel, open the Macro dialog (kbd>Alt–F8, select the macro Personal.xlsm!subAddCellAboveName, click on “Options” and assign a shortcut of your choice.
Public Sub subAddCellAboveName() On Error Resume Next ActiveSheet.Names.Add Name:="CellAbove", RefersToR1C1:="=R[-1]C" End Sub