Manage Excel data with ease by organising them with the use of excel range formulas. Here are 5 Excel range name tips to keep in mind as you rearrange your worksheet data. When executed correctly, you will be able to achieve better results for future analysing. The Excel range name tips are:
- Syntax
Microsoft Excel has a few unspoken rules about range names that users should keep in mind. Name strings are only accepted if they begin with text characters and consists of adjacent characters instead of numbers. Words can be interconnected with underscores and there are no limits on how many names the user can define. However, names that are easily confused with cell references are not accepted.
- Updating Range Name References Automatically
The first step in updating reference in any formula that uses range names would be to update the size of the source data range. Locate your ‘Define Name’ dialog box and search for the ‘Refers to’ section. Update it with this formula (replace underlines with values): [=OFFSET(Ref,__,__,COUNT_(Col_),COUNT_(Row_)) ]
The offset formula return the data table’s measurement size, and reference would be based on the offset.
- Defining Range Names
Locate and select the ‘Formulas tab’. Choose ‘Defined Names Groups’ and select ‘Name Manager’. This should open up the name manager dialog box. Select new and delegate the range name of your choice. Under the ‘Refers to’ section, choose the range for the names to be defined and authorise the notion by hitting ‘OK’.
- Saving Commonly Used Formulas In Define Name Dialog Box
Locate your ‘Formulas tab’ and find the ‘Defined name’ dialog box. Once done, search for the ‘Names’ section and type in the desired range name. Under the ‘Refers to’ section this time, type in whichever formula that is commonly and frequently used. Upon finishing, authorise the change by choosing ‘Ok’. Now whenever you wish to inset the formula into a cell, simply type =F3, select the name presented and OK it.
- Deleting Range Names
The more range names are present, the more difficult it could be to locate specific range names, and sift through unwanted links. For safety purposes, create a new sheet and paste the full list with range names and references into it using the F3 key. Once done, delete the unwanted range names and check to see if the data is seamless and accurate.
Using these 5 Excel range name tips can assist users in being better able to organise their Excel data. Apply these practices to your Excel worksheet file today and look at the changes it brings!
