Skip to content

5000+ Great Articles

Best Articles & Essays: Interesting Articles to Read Online

  • Set up Windows 7/10 to Login Automatically How-To
  • Fix Desktop Icons Missing or Disappeared in Windows Computer Tips
  • Best tax calculator 2020

    Best tax calculator 2020. 1 - Sales Tax Calculator Free Top Reviews If all you need is to be able to figure out what th

  • how to screenshot on roku tv 2021 How-To
  • The Best Utilities to Customize Windows 10 Windows 10
  • How To Add A Font To Photoshop How-To
  • How To Charge Your Apple Watch Hardware
  • Best credit card calculator 2020 Calculator

Why You Should Be Using Named Ranges in Excel

Posted on October 8, 2020 By blog_786 No Comments on Why You Should Be Using Named Ranges in Excel

Named ranges are a useful but often overlooked feature of Microsoft Excel. Named ranges can make formulas easier to understand (and debug), make complex spreadsheets easier to create, and simplify macros.

A named range is simply a range (either a single cell or a range of cells) that you assign a name to. You can then use this name in place of the usual cell references in formulas, macros, and to define the source for graphs or data validation.

Using a range name like TaxRate instead of a standard cell reference like Sheet2! $ C $ 11, can make spreadsheet easier to understand and debug / audit.

Using Named Ranges in Excel

For example, consider a simple order form. Our file includes a fillable order form with a drop-down list for choosing a shipping method, as well as a second sheet with a shipping cost and tax rate table.

Why You Should Be Using Named Ranges in Excel

– /

Why You Should Be Using Named Ranges in Excel

In version 1 (no named ranges), formulas use regular A1-style cell references (shown in the formula bar below).

Why You Should Be Using Named Ranges in Excel

Version 2 uses named ranges to make formulas easier to understand. Named ranges also make it easier to enter formulas because Excel will display a list of names, including function names, from which you can choose when you start typing a name into a formula. Double-click a name in the picklist to add it to the formula.

Why You Should Be Using Named Ranges in Excel

Opening the Name Manager window on the Formulas tab displays a list of the range names and cell ranges to which they refer.

Why You Should Be Using Named Ranges in Excel

But these ranges have other advantages as well. In our sample files, the shipping method is selected using the dropdown (data validation) in cell B13 on Sheet1. The selected method is then used to find the shipping cost on Sheet 2.

Without named ranges, the dropdown choices must be entered manually because data validation will prevent you from selecting the original list on another sheet. Therefore, all options must be entered twice: once in the drop-down list and again on Sheet2. Also, the two lists must match.

If a mistake is made in one of the entries in any list, then the shipping cost formula will give the # N / A error when choosing the wrong choice. Naming the list on Sheet 2 as ShippingMethods fixes both problems.

You can reference the named range when defining data validation for the dropdown, for example, simply by typing = ShippingMethods in the source field. This allows you to use a list of options found on another sheet.

And if the dropdown refers to the actual cells used in the search (for the shipping cost formula), then the dropdown options will always match the search list, avoiding # N / A errors.

Create a named range in Excel

To create a named range, simply select the cell or range of cells you want to name, then click in the name field (where the address of the selected cell usually appears, to the left of the Formula Bar), enter the name you want to use, and press Enter.

Why You Should Be Using Named Ranges in Excel

You can also create a named range by clicking the New button in the Name Manager window. The “New Name” window opens, where you can enter a new name.

By default, a named range is set to whatever range is selected when you click the New button, but you can edit the range before or after saving the new name.

Why You Should Be Using Named Ranges in Excel

Note that range names cannot contain spaces, but can include underscores and periods. Typically, names should begin with a letter and then only contain letters, numbers, periods, or underscores.

Names are not case sensitive, but using a headword string such as TaxRate or December2018Sales makes names easier to read and recognize. You cannot use a range name that mimics a valid cell reference such as Dog26.

You can edit the names of ranges or change the ranges to which they belong using the Name Manager window.

Also note that each named range has a specific scope. Typically, the default scope is Workbook, which means that the range name can be referenced from anywhere in the workbook. However, it is also possible to have two or more ranges with the same name on separate sheets, but in the same book.

For example, you might have a sales data file with separate sheets for January, February, March, and so on.Each sheet might have a cell (named range) named MonthlySales, but usually each of these names would only be scoped to the sheet containing this is.

So the formula = ROUND (MonthlySales, 0) will give February sales rounded to the nearest whole dollar if the formula is on a February sheet, but March sales if on a March sheet, etc.

To avoid confusion in workbooks with multiple ranges on separate sheets of the same name, or simply in complex workbooks with tens or hundreds of named ranges, it can be helpful to include the sheet name as part of each range name.

This also makes each range name unique so that all names can have a Workbook scope. For example, January_Month Sale, February_Month Sale, Budget_Date, Order_Date, etc.

Two caveats regarding the scope of a named range: (1) you cannot edit the scope of a named range after it has been created, and (2) you can only scope a new named range if you create it using the ” New ”in the Name Manager window.

If you create a new name for a range by entering it in the Name field, the default scope will be the workbook (unless another range with the same name exists) or the sheet on which the name is created. Therefore, to create a new named range that is scoped to a specific sheet, use the New button in the Name Manager.

Finally, for those who write macros, range names can be easily referenced in VBA code by simply placing the range name in parentheses. For example, instead of ThisWorkbook.Sheets (1) .Cells (2,3), you can simply use [SalesTotal] if the name refers to that cell.

Start using named ranges in your Excel spreadsheets and you will quickly see the benefits! Enjoy!

–

Share this:

  • Twitter
  • Facebook
Office Tips

Post navigation

Previous Post: Google Sheets vs Microsoft Excel – What are the Differences?
Next Post: Play Any PC Game with a Gamepad Using JoyToKey

Related Posts

  • How To Recover a Word Document Office Tips
  • How To Move Columns In Excel Office Tips
  • How To Clear Outlook Cache Office Tips
  • Fix Outlook Spell Checker Not Working Office Tips
  • How to Customize Word’s Paste Function Office Tips
  • How to Customize the Ribbon in MS Word 2010 Office Tips

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Archives

  • October 2023
  • September 2023
  • August 2023
  • July 2023
  • June 2023
  • May 2023
  • April 2023
  • November 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • March 2021
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • December 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018

Categories

  • AI Tools & Guides
  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Featured Posts
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • islamic Books
  • Linux
  • Linux Tips
  • Mac OS X
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Windows XP Tips
  • Wordpress
  • Flixable Helps You Find the Best Movies and TV shows on Netflix
  • How to Watch Winter Olympics 2018 from Anywhere
  • Why does Instagram say this story is unavailable?
  • How to turn off Do Not Disturb mode on Android
  • 3 Sites like YouTube to Earn Money With Your Videos
DMCA.com Protection Status

Recent Posts

  • 15 Best Legal Torrenting Sites to Download Content Safely
  • Review of the ReMarkable 2.0 tablet with Type Folio keyboard
  • Flixable Helps You Find the Best Movies and TV shows on Netflix
  • How to Watch Winter Olympics 2018 from Anywhere
  • 5 Sites That Are Like Audible, But Free

Recent Comments

  1. Flixable Helps You Find the Best Movies and TV shows on Netflix on SmartDNS vs VPN “What’s the Difference?”
  2. 5 Sites That Are Like Audible, But Free on Top 4 Amazon Price Tracker Tools
  3. 5 Sites That Are Like Audible, But Free on 14 Best Free Audiobooks on Audible – 2022
  4. How to Reduce PNG File Size of a Photo on How to Convert a Screenshot to a Jpeg on a Mac
  5. 3 Sites like YouTube to Earn Money With Your Videos on Here are the Top 10 highest paid YouTubers of 2013
  • How To Become An Expert At Using Google Alerts Google Software/Tips
  • Quickly delete an image from iCloud Tutorials
  • 3 ways to Rotate Video on iPhone (2018) How-To
  • The 8 Best Online Sources For Green Screen Special Effects Reviews
  • How to cast Android screen on PC using Wi-Fi or USB step by step How-To
  • 5 Ways iPhone/Android Can Cast Screen to TV Without Chromecast How-To
  • how to calculate vat in maths

    The Gross to Net formula might include the operators provided in the table below. If you wish to find out which portion of the total a couple of uniqu

  • Third-Party Tools Which Do Better Than Some Of MacOS’ Default Apps OS X

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version