tips-and-tricks

Use range names to create meaningful Excel formulas

July 19, 2012 Liz Todd

Understanding what formulas actually show can be a challenge particularly if you are working with an Excel workbook that someone else has created. Formulas that use range names rather that cell references are much easier to understand. For example, =Income–Expenses is much more meaningful than =D9 D25.

Creating range names is really easy!

Simply select the cell or cells that you want to name, click the Name Box at the left of the Formula bar, type the name (eg Sales) and tap Enter. A few pointers: you’ll find the Name Box above column A; range names can’t include spaces or start with numbers; make your range names meaningful, eg Sales, Products, Prices, etc; range names are not case sensitive.

Making use of range names

Once you have assigned names to cells or ranges, you can use the names in a number of ways. You can quickly go to the named range using the Go To command (press Ctrl+G or F5) – even if the range name is in another worksheet. You can set a function’s argument to the range name, eg =SUM(Sales), =COUNTIF(Sales, A4), =VLOOKUP(A4,Sales,3). You can also use range names rather than cell references when you are creating macros.


Tags



Older Post Newer Post



Leave a comment

Please note, comments must be approved before they are published