You can name parts of a spreadsheet and then use the name in formulas and other Excel features. Using a naming convention make things much easier to follow and adapt in the long run. I will share some suggestions for naming ranges.
Tag Archives: range names
Yesterday and Tomorrow in Excel
I recently saw a post about using a LAMBDA function for the dates for yesterday and tomorrow. You don’t need to use LAMBDA, you can just use range names.
A Cell Reference Custom Function
The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
One Minute to Excel #29 – Nine Names in a Minute
Shortcut to speed up name creation
In this short video I demonstrate how to create range names quickly based on labels. Range names are a powerful formula feature. I also demonstrate their use.
Putting a Message on All Sheets in Excel
A text box solution
Let’s say you have a validation check in your file and you want to display a message based on the validation status. Here is one way to do that.
Retrofit a Factor to an Excel Budget
Range name technique
It is common to have a Factor in a cell or cells in a budget to allow you to easily tweak the numbers by a percentage. If you want to add a Factor to an existing budget model here is how you can do it.
Zeroing an input range in multiple cells
Range names make it easy
In some cases you may have to make manual inputs across multiple cells that are spread across a sheet. Before making the entries you need to clear the existing entries. Creating a range name can make that process much quicker.
Range Names and Macros
Range names rule
When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
Deleting Duplicated Names in Excel
Names scoped at Worksheet level are often duplicates
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
Some Observations on Structure in Excel
Tables rule
My consulting work recently highlighted a stark contrast in different Excel models and the effort it takes to create or change them. I make some recommendations to make things easier for yourself at the end of this post.
Taking Control of Excel shortcuts- Part One
Save time with shortcuts
Keyboard shortcuts can really speed up your work in Excel. Here are some of my favourites that use the Ctrl key. I’ll share some more in later blog posts.
Delete range names with #REF errors
A macro to the rescue
Here is an example of a simple macro that solves a problem in Excel 2003 and earlier versions.
Range names can be corrupted if a cell that they refer to gets deleted. This doesn’t mean that the cell value gets deleted, but the cell itself is removed from the sheet.