Skip to content

5000+ Great Articles

Best Articles & Essays: Interesting Articles to Read Online

  • Freeware Versus Shareware – What’s The Difference? Reviews
  • Fix “Windows cannot access the specified device path or file” Error Help Desk
  • Is there a Classic View in Windows 7? Windows 7
  • How To Conference Call On An iPhone iPhone
  • what is mcm client on android Smartphones
  • How can I know the type of RAM in your computer? How-To
  • How To FIX: Apple TV Won’t Connect to Wi-Fi Hardware
  • How To Fix Google Drive Not Syncing On Mac OS X

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 Google Docs Chat Helps You Collaborate on Documents Office Tips
  • Using Excel’s What-If Analysis Goal Seek Tool Office Tips
  • Change the Default Margins Used in New Word Documents Office Tips
  • How to Change the Slide Size in PowerPoint for Better Presentations Office Tips
  • How to create and run a macro in MS Word Office Tips
  • When to Use Index-Match Instead of VLOOKUP in Excel Office Tips

Leave a Reply Cancel reply

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

Archives

  • 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
  • How to go Back to the old YouTube Layout (2013)
  • How to Bypass Chromecast DNS and Circumvent Geo Blocking
  • 5 Cool Websites to Find Good Movies and TV Shows on Netflix
  • SmartDNS vs VPN – What’s the Difference?
  • How to go Back to the old YouTube Layout (2017)
DMCA.com Protection Status

Recent Posts

  • How to Bypass Chromecast DNS and Circumvent Geo Blocking
  • 5 Cool Websites to Find Good Movies and TV Shows on Netflix
  • SmartDNS vs VPN “What’s the Difference?”
  • How to go Back to the old YouTube Layout (2013)
  • A Simple Trick to Get 50% Discount on Audible for Three Months

Recent Comments

  1. A Simple Trick to Get 50% Discount on Audible for Three Months on Private Browsing: What is it and What it is not
  2. 6 Best PayPal Alternatives (2017) on How to Save Money While Shopping Online in India
  3. Automatically Transcribe YouTube Video/Audio with Google Docs on 5 Best Team Management Apps (For Small and Large Teams)
  4. 6 Things You Need Know About Email Encryption on Delete All Emails from Gmail With Once Click
  5. Looking For YouTube Alternative? Try These 7 Video Sharing Sites on Ten Best YouTube Video Editing Software 2023
  • How to Transfer WhatsApp Chat History from iPhone to Android iPhone
  • How to Combine or Merge Multiple Text Files Free Software Downloads
  • How To Block Distracting Websites To Beat Procrastination Computer Tips
  • Can’t Play MP4 Files on Your XBox 360? Computer Tips
  • Ryzen 3900X vs Intel i9-9900K – Which CPU Is Truly Better? Reviews
  • The best way to add a slide number to PowerPoint Office Tips
  • Best ti34 calculator 2020

    Best ti34 calculator 2020. 1 - Selected TI34II Scientific Explorer + By Texas Instruments Top Reviews Works fine and in

  • Top 5 Free Online Image Editor Like Photoshop How-To

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version