Sunday, April 18, 2010

Create Range Name in Excel

Today I am sharing quickest way to create Range Name in Excel. Just 3 easy steps

1. Select the cell(s) you want to name
2. Type Range name in the name box
3. Press Enter

Create Range Name in Excel

The following is a list of syntax rules that you need to be aware of when you create and edit names.

Valid characters The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Cell references disallowed Names cannot be the same as a cell reference, such as Z$100 or R1C1. Spaces are not valid Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.

Name length A name can contain up to 255 characters.

Case sensitivity Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name

Check out screen cast below to see it happening.
Create Range Name in Excel

Named ranges is part of defined names in excel. It is just not limited to a cell on the worksheet or range of cells, it can also be a constant or a formula in your workbook.

It has many uses in Excel, some of them are :-

1. Make your formulas much easier to understand.
2. Refer to dynamic ranges in excel by creating dynamic range names
3. Can be used in data validation based on contents on another worksheet
4. Can be used for conditional formatting based on contents on another worksheet.
5. These are best used in Excel Macros. It makes your macros more robust
6. Use them as constant value without referring to contents on your worksheet.

Will be covering some of the topics in my upcoming posts, so stay tuned to learn more uses of defined names in excel.


  1. You can also create lots of range names in one go with a keyboard shortcut if you've already typed in row and column headings in a table:

  2. Is it possible to use OFFSET, to use more then 1 range, hence multiple ranges? If not, what formula can you suggest? Sample workbook to edit

    For example a workbook with 3 spreadsheets:
    DATA1 (With name range 1).
    DATA2 (With name range 2).
    COMBINED (OFFSET formula combining all non-blank rows of DATA1 & DATA2, in that order).

    To elaborate:
    defined name range's per DATA1 spreadsheet is:
    This code expands down as many rows as there are Numeric and Text entries.

    Then to combine/merge all of the spreadsheets into 1 spreadsheet, copying row by row, of each DATA spreadsheets, TO EDIT :


    What is the right code to combine the 2 formulas above into a 1 cell formula?

  3. check out this tutorial - it was really helpful

  4. Shalom, Om swastiastu, Namo buddhaya, Salam kebajikan, pada artikel kali ini kami akan memberikan kepada kamu Panduan Cara Bet 2D Togel Klik4D Online yang tersedia di S128cash. Berhubung permainan togel adalah game legendaris di Indonesia, tahukah kamu kalau game togel sekarang bisa dimainkan secara online ? (Baca Selengkapnya Disini...)

  5. This is a fantastic website, thanks for sharing.Street View Online

  6. Our Black Leather L Krasnov Jacket is traditional leather, the style now also commonly appears in nylon and other materials. Available at

  7. While traditional black asymmetrical leather jacket are leather, the style now also commonly appears in nylon and other materials.

  8. fashion industry have many big brands. here is another brand named by who provide best Petite saskia trench coat get the best leather jackets and be happy. buy now.



    Adopt full aluminium alloy panel structure, anodized surface process, ultra-narrow frame, rounded corner design, fashion and beautiful appearance
    4mm tempered glass with anti glare 3.Advanced IR 20 touching technology.
    The OPS host module adopts plug structure design, internal Inter standard 80pin interface, without any external power line and signal line, which is convenient for
    inspection, maintenance and upgrade, and achieves the aesthetic effect of the machine

  10. This is the first time I visit here and I found so much interesting stuff in your blog especially its discussion, thank you. Please also read mine at Fun Livin' and let me know where I can improve.

  11. Is this a paid topic or do you change it yourself?
    However, stopping by with great quality writing, it's hard to see any good blog today.
    Ashampoo Burning Studio
    PDF XChange Editor

  12. How To Download For Fortnite On Android Phones This Is The Most Easiest Most Efficient And The Fastest Way To Download Fortnite To Your Android Phones Click Here fortnite

  13. Thanks for sharing the tip. MS Excel has extensive use in every business. For any SEO and data analyst, it is necessary to be an expert in excel. Excel provides many data filters and validation processes that help get the best data for business. This is the main reason it is being used by the researchers of top dissertation writing services in the UK for data analysis.

  14. A name must be at least 3 characters long. You can't use any of the following characters: a colon (:), a semicolon (;), an equals sign (=), or a tilde (~). You can't use any of the following characters buy coursework uk to prefix a name: an asterisk (*), a comma (,), or a space.The first letter of each name must be capitalized. You can't use a name that is already in use by another person, organization, or website.

  15. Proficient exposition essayists for recruits are accessible for understudies from one side of the planet to the other. Guides give help with making a wide range of scholastic Write my Paper for me - EssayEmpire.Co.UK Writing Service tasks, for example, expositions, research projects, research papers, and different errands. Our writers have advanced education, composing experience, and the longing to help understudies.

  16. Creating a Range name in an excel sheet was very difficult for me but after knowing this method it will be very easy for me. Thanks for sharing this with us. I really appreciate this. Now it's time to avail Logo Design usa for more information.

  17. A very good information in this article about Excel range. I hope you will share more good updates. Now it's time to avail luxury airport transfer for more information.

  18. Partner specializations replicate the depth of a partner organization’s expertise. They embrace required exams and really helpful trainings for workers in numerous roles. Explore the ability of the Cisco Learning Network's vibrant neighborhood to jump-start your certifications and lifelong studying goals. # Permanently disabled menu factor, solely required attribute is 'type'. This characteristic permits one reduce back} repetitive definitions in display_data sections. One could use the builtin Sweater Dress render() operate in display_data sections to gauge a template.


Learn how to create Excel dashboards.
Yogesh Gupta's Excel Tips