Spreadsheet Planet

How to Get Sheet Names in Excel?

You may want to get the worksheet’s name while working with Excel.

For example, when creating a report that includes multiple worksheets, you may have the sheet name as a header or footer to help users navigate the report. 

Of course, you can manually type in the names, but the names will not automatically update if you rename the worksheets.

This tutorial shows four methods of getting the sheet name in Excel, and the name is automatically updated if it is changed. 

Table of Contents

Method #1: Using TEXTAFTER and CELL Functions to Get the Worksheet Name in Excel

The TEXTAFTER function, only available in Excel 365, returns text that occurs after a given character or string. The CELL function returns information about a cell’s formatting, location, or contents.

We can use a formula that combines the two functions to get a worksheet name in Excel.

Let’s consider the following workbook, “Employee Performance Appraisal Reports,” which has five worksheets with different names:

Excel file with various worksheets

We want to return the name of the current worksheet, “Qtr 1 2022”,  in a cell in the workbook using a formula that combines the TEXTAFTER and CELL functions.

We use the following steps:

  • Select any cell in the active worksheet; in this case, we select cell A1 and enter the below formula:

Note: This formula only works if the workbook has been saved at least once. Otherwise, it returns the #N/A error because it cannot locate the workbook.

TEXTAFTER formula to get the current worksheet name

  • Press Enter.

The name of the “Qtr 1 2022” worksheet is returned in cell A1 as shown below:

current worksheet name is shown in the cell

We get the respective worksheet names if we copy the formula to the other worksheets in the workbook.

For example, when we copy the formula to cell B2 of the “Qtr 2 2022” worksheet, the worksheet’s name is returned in the cell as seen below:

dynamic formula that changes based on the worksheet name

Explanation of the formula

The CELL function’s info_type   argument is set to “filename,” and reference to cell A1 to return the full path to the active worksheet, as seen below:

CELL part of the formula

The returned full path is then fed into the TEXTAFTER function as the text argument. The delimiter argument is set to “]” to extract only the text that is after the closing square bracket (“]”).

In our example, the result is “Qtr 1 2022”, the name of the active worksheet.

Method #2: Use a Formula Combining MID, CELL, and FIND Functions to Get Sheet Name in Excel

Another easy way to get sheet names in Excel is by using a combination of MID, CELL, and FIND functions.

  • The MID function returns the text string characters from inside a text string, given a starting position and length.
  • The CELL function returns information about a cell’s formatting, location, or contents.
  • The FIND function is case-sensitive and returns the starting position of one text string within another.

We can use a formula that combines the three functions to get a worksheet name in Excel.

We have the following workbook, “Employee Performance Appraisal Reports,” which has five worksheets with different names:

Excel file with various worksheets

We want to return the name of the current worksheet, “Qtr 1 2022”,  in a cell in the workbook using a formula that combines the MID, CELL, and FIND functions.

  • Select any cell in the current worksheet; in this example, we select cell A1 and enter the formula below:

Note: This formula will only work if the workbook has been saved at least once. Otherwise, the formula returns the #VALUE! error because it cannot locate the workbook.

MID formula to get the current worksheet name

The current worksheet’s name is returned in cell A1 as seen below:

current worksheet name is shown in the cell

If we copy the formula to any cell in the other worksheets, the worksheet’s respective name is displayed in the selected cell.

Let’s, for example, copy the formula to cell G1 of the “Qtr 1 2023” worksheet:

dynamic formula changes based on the worksheet name

The formula returns the name of the worksheet in cell G1.

Explanation of the formula 

=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,31)

  • CELL(“filename”,A1) – The first CELL function’s info_type argument is set to “filename” and reference argument to cell A1 to return the full path to the active worksheet as shown below:

how to name worksheet in excel

The full path to the worksheet is passed to the MID function as the text argument.

  • FIND(“]”,CELL(“filename”,A1))+1 – The FIND function returns the position of the closing square bracket in the full path. The position is increased by 1 to calculate the starting position of the worksheet name. The computed result is passed to the MID function as the start_num argument.
  • Finally, the value 31, the maximum number of characters allowed in a worksheet name, is passed to the MID function as the num_chars argument. The value ensures that the MID function extracts the full worksheet name to the right of the closing square bracket. The final result in our example is “Qtr 1 2022,” the name of the current worksheet. 

Method #3: Using RIGHT, CELL, LEN, and FIND Functions to Get the Worksheet Name in Excel

The RIGHT function returns the specified number of text string characters from the end of a text string. The CELL function returns information about a cell’s formatting, location, or contents. The LEN function returns the number of characters in a text string, and the FIND function, which is case-sensitive, returns the starting position of one text string within another. 

We can apply a formula combining the four functions to get the name of a worksheet in Excel.

Assume we are working on the following “Employee Performance Appraisal Reports” workbook that has five worksheets with different names:

Excel file with various worksheets

We want to use a formula combining the RIGHT, CELL, LEN, and FIND functions to return the name of the active worksheet, “Qtr 1 2022,” in a cell in the workbook.

We proceed as follows:

  • Select any cell in the active worksheet; in this example, we select cell A1 and enter the formula below:

Note: This formula only works if the workbook is saved at least once. Otherwise, the formula returns the #VALUE! error because it cannot find the workbook.

RIGHT formula to get current worksheet name

The name of the active worksheet is displayed in cell A1:

result of the formula that shows the current worksheet name

If we copy the formula to any cell in the other worksheets, the worksheet’s respective name is returned in the selected cell.

Let’s, for example, copy the formula to cell E2 of the “Qtr 3 2022” worksheet:

the result changes based on the worksheet name

The name of the active worksheet is shown in cell E2.

=RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND(“]”,CELL(“filename”)))

  • CELL(“filename”) The first CELL function returns the full path to the active worksheet as shown below:

how to name worksheet in excel

The worksheet’s path is then passed to the RIGHT function as the text argument.

  • LEN(CELL(“filename”) The LEN function returns the number of characters in the active worksheet’s full path text string.
  • FIND(“]”,CELL(“filename”)) The FIND function returns the position of the closing square bracket in the full path text string. 
  • LEN(CELL(“filename”))-FIND(“]”,CELL(“filename”)) The number returned by the FIND function is subtracted from the entire length of the full path text string returned by the LEN function. The result is the length of the name of the active worksheet. The result is passed to the RIGHT function as the num_chars argument.
  • Finally, the RIGHT function utilizes the text, and the num_chars values passed to it to extract the name of the current worksheet. 

Appending Text to the Worksheet Name

If printing a report that includes many worksheets, we could add more descriptive text to the worksheet name to help users quickly navigate the information. 

For example, if we have a worksheet name “Qtr 1 2022,” we may want to add the text “Employee Performance Appraisal Report for” to the name so that the report’s title reads “Employee Performance Appraisal Report for Qtr 1 2022.”

We can achieve this by joining the formulas we have already described to the additional text we want using the ampersand (&) operator.

For example, the formula below adds the text “Employee Performance Appraisal Report for” to the worksheet name:

appending a text before the worksheet name

We can also use the CONCAT function as in the example below:

concat Formula To append text before the worksheet name

How to List All Worksheet Names in a Workbook Using a Formula

We may want a list of all worksheet names in a workbook.

Suppose we have the following “Employee Performance Appraisal Reports” workbook with a Summary worksheet and five other worksheets.

Summary worksheet to get all sheet names

We want to use a formula to extract a list of the worksheets’ names in the “Summary” worksheet of the workbook. 

  • Select any cell in the Summary worksheet; we select cell A1 in this example.
  • On the Formulas tab, on the Defined Names group, click the Define Name button.
  • On the New Name dialog group that pops up, do the following:
  • On the Name box , type “ List_Worksheet_Names .” Remember, the name should not have spaces.
  • Open the Scope drop-down list and select Workbook .
  • Type the formula “ =REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””) ” on the Refers to box and click OK.

New name dialog box

Note: The GET.WORKBOOK function is an Excel 4.0 function that cannot be used directly in cells but works with named ranges.

  • Enter the values 1 to 7 in the cell range B1:B7 as shown below:

enter serial values in a column

  • Select cell C1 and enter the formula below:

Formula to fetch worksheet names by number

  • Drag the Fill Handle to copy the formula down the column to get the following list of names of worksheets in the workbook:

drag the formula to get all worksheet names

  • Save the workbook as a Macro-Enabled Workbook (*.xlsm), so you do not lose the list. Excel informs you accordingly if you attempt to save the workbook as a regular (*.xlsx) file.

save the file as macro enabled file

Explanation of the technique

  • The formula REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””) replaces all the characters in each worksheet’s full path text string up to and including the closing square bracket with empty strings leaving only the worksheet name. This formula effectively generates an array of the names of the worksheets in the workbook.
  • INDEX(List_Worksheet_Names,B1) The INDEX function uses the value in cell B1, in this case, one (1), to return the first worksheet name in the array. As the formula is copied to the other cells, it returns the second and third worksheet names, and so on.
  • The IFERROR function that wraps the formula returns an empty string after all the worksheet names in the array have been listed.

Some Use Cases where Getting Sheet Names Could Be Useful

Knowing how to get sheet names in Excel can be useful in many different situations.

Here are some use cases where I have found it useful to quickly know the name of the current sheet name or all sheet names in the file.

1. When Consolidating Data From Multiple Excel Files

When you have multiple Excel files with similar data structures, you may want to consolidate them into a single file.

In this case, knowing the sheet names can help you easily identify which sheets contain the data you need to consolidate.

You can then use formulas or VBA code to extract the data from multiple sheets and combine them into one single sheet or file.

2. When Automating Reports

If you regularly create reports that involve multiple sheets, knowing how to get sheet names can save you time and effort.

For example, you can use VBA code to loop through all the sheets in a workbook and extract data from all the sheets or specific sheets with specific sheet names (such as the year number of department name).

You can also use sheet names to dynamically reference cells or ranges in your formulas or VBA code.

3. Finding Missing Data/Sheet

If you’re collating data and combining different sheets into one Excel file, getting a list of all the sheet names can help you spot if there are any missing sheets that needs to be added.

Getting all the sheet names in a column then can be very useful in such a situation.

4. Data Validation

When creating data validation rules in Excel, you may want to restrict the input to a specific range of cells on a particular sheet.

Knowing the sheet name can help you easily specify the range of cells you want to restrict the input to. This can help prevent errors and ensure data consistency.

5. Collaborating with Others

If you are collaborating with others on an Excel workbook, knowing the sheet names can help you communicate more effectively.

For example, you can refer to specific sheets by name when discussing the data or formulas with your colleagues.

This can help ensure everyone is on the same page and minimize confusion.

This tutorial showed four techniques for getting worksheet names in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

  • Add New Sheet in Excel (Shortcut)
  • Switch Between Tabs/Worksheets in Excel (Shortcut)
  • 3 Easy Ways to Duplicate Sheet in Excel (Shortcuts + VBA)

' src=

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “How to Get Sheet Names in Excel?”

FYI – effective today, Excel 365 no longer supports Excel 4.0 Macro Functions in a Name. So, Get.Worksheet is no longer supported, lol. Funny, how it came back with a #blocked error.

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Excel Dashboards

Excel Tutorial: How To Name Worksheet In Excel

Introduction.

When working with Excel, one of the essential skills to master is the ability to name worksheets effectively. This simple yet crucial task can make a world of difference in organizing and labeling your data. In this tutorial, we will explore the importance of naming worksheets in Excel and provide step-by-step instructions on how to do it.

Key Takeaways

  • Naming worksheets effectively in Excel is essential for organizing and labeling data.
  • Utilizing keyboard shortcuts, such as the F2 key, can save time when renaming worksheets.
  • Establishing a consistent naming convention is important for clarity and organization.
  • Renaming multiple worksheets simultaneously can streamline the process and improve efficiency.
  • Advanced techniques, such as using formulas and macros, can automate worksheet naming for increased efficiency.

Understanding the basics of naming worksheets

When working with Excel, it's important to understand how to effectively name your worksheets. By giving them meaningful and descriptive names, you can make it easier to organize and navigate your workbooks.

Explain how to right-click on a worksheet tab to access the option to rename

Renaming a worksheet is a simple process. To do this, simply right-click on the tab of the worksheet you want to rename. This will bring up a menu of options, including the ability to rename the worksheet.

Discuss the limitations and best practices for naming worksheets

While the process of naming a worksheet is straightforward, it's important to be aware of certain limitations and best practices. For example, worksheet names cannot exceed 31 characters and cannot contain special characters such as \, /, *, [, ], etc. It's also best to use clear and concise names that accurately reflect the contents of the worksheet. This will make it easier for you and others to understand the purpose of each worksheet within the workbook.

Utilizing keyboard shortcuts for efficiency

When working in Excel, it’s important to utilize keyboard shortcuts to improve efficiency and save time. One useful shortcut is the F2 key for renaming a worksheet.

Introduce the shortcut key (F2) for renaming a worksheet

The F2 key allows you to quickly rename a worksheet without having to use the mouse or navigate through the Excel menu.

Provide step-by-step instructions for using the shortcut key

To use the F2 key to rename a worksheet, simply select the worksheet tab you want to rename and press the F2 key. This will put the selected tab into edit mode, allowing you to rename it directly from the keyboard.

Highlight the time-saving benefits of using keyboard shortcuts

Using keyboard shortcuts like F2 for renaming a worksheet can save you valuable time by eliminating the need to navigate through menus and use the mouse. This allows you to stay focused on your work and achieve greater productivity in Excel.

Incorporating specific naming conventions

When it comes to working with multiple worksheets in Excel, having a consistent naming convention is crucial for staying organized and efficient. In this chapter, we will discuss the importance of establishing a naming convention, provide examples of effective naming conventions, and address the significance of clarity and organization in naming worksheets.

Having a consistent naming convention for your worksheets is important for several reasons. Firstly, it helps you and your team members easily identify and locate specific worksheets within a large workbook. It also makes it easier to reference and link between different worksheets, saving you time and reducing the risk of errors.

There are several ways to establish an effective naming convention for your worksheets. One common approach is to use a descriptive name that clearly indicates the contents or purpose of the worksheet. For example, if you have a worksheet containing sales data for Q1, you could name it "Sales_Q1_2022". Another approach is to use abbreviations or codes to categorize worksheets, such as "PR" for project management or "INV" for inventory tracking.

Clarity and organization in naming worksheets are essential for maintaining a structured and easily navigable workbook. This is particularly important when working with complex datasets or sharing workbooks with others. By using clear and consistent naming conventions, you can enhance the overall usability and effectiveness of your Excel workbook, ultimately leading to improved productivity and accuracy.

Renaming multiple worksheets simultaneously

Renaming multiple worksheets in Excel can be a time-consuming task, especially when you have a large number of worksheets to rename. However, Excel provides a simple and efficient way to rename multiple worksheets at once, saving you time and effort.

Explain how to select and rename multiple worksheets at once

To rename multiple worksheets simultaneously, first, hold down the Ctrl key on your keyboard and click on each worksheet tab that you want to rename. Once you have selected all the worksheets, right-click on any of the selected tabs and choose Rename . You can then enter the new name for the selected worksheets, and all the selected worksheets will be renamed with the same name.

Provide tips for efficiently renaming multiple worksheets

When renaming multiple worksheets, it can be helpful to use a systematic approach. For example, if you need to add a prefix or suffix to the worksheet names, you can do so by selecting the worksheets and then using the Rename option to add the prefix or suffix to all selected worksheets at once. Additionally, you can use keyboard shortcuts such as F2 to quickly rename a selected worksheet without having to right-click and choose Rename .

Emphasize the benefits of streamlining the renaming process

Streamlining the process of renaming multiple worksheets in Excel can save you a significant amount of time and effort, especially when working with large datasets or complex workbooks. By using the built-in functionality to rename multiple worksheets simultaneously, you can ensure consistency in the naming conventions of your worksheets and avoid manual errors that may occur when renaming each worksheet individually. This can lead to improved organization and productivity when working with Excel workbooks.

Advanced techniques for naming worksheets

In addition to the basic method of naming worksheets in Excel, there are advanced techniques that can be used to automate the process and increase efficiency.

Formulas and macros can be used to automatically name worksheets based on specific criteria or input. This can be particularly useful for large data sets or recurring tasks where naming worksheets manually would be time-consuming.

  • Formulas can be used in the worksheet tab name to dynamically update the name based on the values in the worksheet.
  • For example, a formula could concatenate the current date with a specific identifier to create a unique worksheet name.
  • Macros can be created to automate the process of naming worksheets according to predefined rules or user input.
  • By recording a macro or writing VBA code, users can streamline the naming process and reduce the likelihood of errors.

By implementing advanced techniques for naming worksheets, users have the potential to customize the naming conventions to suit their specific needs. This level of customization can lead to increased efficiency and accuracy in managing and organizing data within Excel workbooks.

Customization

  • Users can tailor formulas and macros to reflect their preferred naming conventions, such as including project codes or department names in the worksheet names.
  • This level of customization allows for greater clarity and organization within workbooks containing multiple worksheets.
  • Automating the worksheet naming process can save time and reduce the risk of human error associated with manual naming.
  • By streamlining the naming process, users can focus on the core tasks within Excel without being bogged down by administrative duties.

For those interested in learning more about advanced techniques for naming worksheets in Excel, there are various resources available to further develop these skills. These resources may include tutorials, online courses, and reference materials.

Online Tutorials

  • Websites such as Microsoft's official support page, Excel tutorials on YouTube, and blogs dedicated to Excel tips and tricks can be valuable sources of information.
  • These tutorials often provide step-by-step instructions and practical examples to help users grasp the concepts behind advanced naming techniques.

Online Courses

  • E-learning platforms like Udemy, Coursera, and LinkedIn Learning offer courses specifically focused on advanced Excel skills, including worksheet naming techniques.
  • These courses may be more comprehensive and in-depth, providing a deeper understanding of the topic and hands-on practice.

Reference Materials

  • Books, manuals, and official documentation from Microsoft can serve as valuable reference materials for those seeking a deeper understanding of advanced Excel techniques.
  • These resources may provide detailed explanations, best practices, and advanced tips for optimizing worksheet naming within Excel.

In conclusion, naming worksheets in Excel is a simple yet crucial task that can greatly improve organization and efficiency. By following the tips and techniques discussed in this tutorial, such as using clear and descriptive names and incorporating consistent naming conventions , users can easily navigate their workbooks and save time and effort in the long run. It is important to emphasize the significance of naming worksheets in Excel, as it can help users to avoid confusion and errors and enhance the overall usability of their spreadsheets. We encourage our readers to apply these best practices and witness the positive impact it can have on their Excel workflow.

Excel Dashboard

Immediate Download

MAC & PC Compatible

Free Email Support

Related aticles

Excel Tutorial: What Does #### Mean In Excel

Excel Tutorial: What Does #### Mean In Excel

Understanding Mathematical Functions: How To Call A Function In Vba

Understanding Mathematical Functions: How To Call A Function In Vba

Understanding Mathematical Functions: How To Add Function In Google Sheets

Understanding Mathematical Functions: How To Add Function In Google Sheets

Understanding Mathematical Functions: How To Fill In A Table Using A Function Rule

Understanding Mathematical Functions: How To Fill In A Table Using A Function Rule

Understanding Mathematical Functions: What Are The Basic Functions Of A Cell

Understanding Mathematical Functions: What Are The Basic Functions Of A Cell

Making Write 15 Minutes On A Timesheet

Making Write 15 Minutes On A Timesheet

Making Identify Sheet Sizes

Making Identify Sheet Sizes

Mastering Formulas In Excel: What Is The Formula For Standard Deviation

Mastering Formulas In Excel: What Is The Formula For Standard Deviation

Mastering Formulas In Excel: What Is The Formula Of Force

Mastering Formulas In Excel: What Is The Formula Of Force

Mastering Formulas In Excel: What Is Net Present Value Formula

Mastering Formulas In Excel: What Is Net Present Value Formula

Mastering Formulas In Excel: How To Write Formula In Google Docs

Mastering Formulas In Excel: How To Write Formula In Google Docs

Mastering Formulas In Excel: How To Do A Formula In Google Sheets

Mastering Formulas In Excel: How To Do A Formula In Google Sheets

  • Choosing a selection results in a full page refresh.

How to name and format worksheet tabs

how to name worksheet in excel

Abstract 

Transcript .

In this lesson, we'll look at how to name and rename worksheets. We'll also look at how to change the color of worksheet tabs.

Let's take a look.

When you open a new workbook, or when you add new worksheets to an existing workbook, Excel uses a generic name for each sheet: Sheet1, Sheet2, Sheet3, and so on.

As you build out a workbook to meet your needs, you'll probably want to rename these sheets to keep things organized.

The easiest way to rename a worksheet is to double-click its name. This will highlight the text of the name, and then you can type a new name.

Press enter to confirm the change.

You can also right-click on a worksheet and choose Rename from the menu.

As before, Excel will highlight the text and you can type a new name.

Once text is highlighted, you can click to move the cursor inside the text and edit as you like. You can also double-click to select individual words.

There are a few rules to keep in mind when you rename tabs.

Excel worksheet names need to be at least one character long, and they can't be longer than 31 characters.

You can't have two worksheets with the same name in the same workbook.

Certain characters are not allowed in worksheet names, including square brackets, asterisks, question marks, forward and backward slashes, periods, apostrophes, and colons. If you try to type these characters into a worksheet name, Excel will simply ignore the input.

In addition to changing a worksheet's name, you can also change the color of a worksheet tab. To change the color, just right-click and choose Tab Color from the menu. You can then choose a color of your choice.

Note that you won't see the color change until you click enter and select another worksheet.

Dave Bruns Profile Picture

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

  • Accept and apply

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Excel foundational video course

  • iPhone 15 Plus vs. Pro Max
  • 3 Key Tech Trends to Watch in 2024

How to Rename a Worksheet in Excel

Make your worksheets names more descriptive

What to Know

  • Press and release Alt + H + O + R , type a new name, and press Enter to rename the active worksheet.
  • Double-click the current worksheet's name, type a new name, and press the Enter key.
  • Right-click the current worksheet's name, select Rename , type a new name, and press the Enter key.

Two changes that make it easier to organize and identify worksheets and the data they contain are to rename the worksheet and to change the color of the worksheet tab at the bottom of the work area. These instructions apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Microsoft 365, and Excel Online.

Using Keyboard Hotkeys

In Excel 2019, Excel 2016, Excel 2013, or Excel 2010, you can use a keyboard shortcut to rename a worksheet tab. 

This set of keystrokes activates the ribbon commands. Once the last key in the sequence, the R, is pressed and released, the current name on the sheet tab of the current or active sheet is highlighted.

  • Press and release in sequence the following key combination to highlight the name of the active sheet: Alt + H + O + R .
  • Type the new name for the worksheet.
  • Press the Enter key on the keyboard to complete renaming the worksheet.

As with renaming a worksheet using hotkeys , you don't have to hold down the Alt key while you press the other keys, as with some keyboard shortcuts. Each key is pressed and released in succession.

Double-Click the Sheet Tab

This method works in all versions of Excel for Windows, Mac, Microsoft 365, and Online.

  • Double-click on the current name in the worksheet tab to highlight the current name in the tab.
  • The new name should be visible on the worksheet tab.

Right-Click the Sheet Tab

  • Right-click on the tab of the worksheet you want to rename to open the context menu.
  • Click on Rename in the menu list to highlight the current worksheet name.

Access the Ribbon Option With the Mouse

  • Click on the tab of the worksheet to be renamed to make it the active sheet.
  • Click on the Home tab of the ribbon.
  • Click Format in the Cells group to open the drop-down menu.
  • Click on Rename Sheet to highlight the sheet tab at the bottom of the screen.

View All Sheet Tabs in a Workbook

If a workbook has a lot of worksheets or the horizontal scroll bar is extended, not all the sheet tabs will necessarily be visible at once. The longer the sheet names, the longer the tabs.

To correct this situation:

  • Place the mouse pointer over the vertical ellipsis (three vertical dots) next to the horizontal scrollbar.
  • The mouse pointer will change to a double-headed arrow, as shown in the image above.
  • Press and hold down the left mouse button and drag the pointer to the right to enlarge the area for sheet tabs to be displayed, or to the left to widen the scrollbar.

Excel Worksheet Name Restrictions

There are a few restrictions when it comes to renaming an Excel worksheet:

  • A name cannot exceed 31 characters in length
  • A worksheet name cannot be left blank
  • The following characters cannot be in a name:  \ / ? : * [ ]

Using Worksheet Names in Excel Formulas

Renaming a worksheet not only makes it easier to keep track of individual sheets in a large workbook, but it has the added benefit of making it easier to understand formulas that span multiple worksheets.

When a formula includes a cell reference from a different worksheet, the worksheet name must be in the formula.

If the default worksheet names are used — such as Sheet2, Sheet3 — the formula will look something like this:

Giving the worksheets a descriptive name — such as May Expenses and June Expenses — can make the formula easier to decipher. For example:

Get the Latest Tech News Delivered Every Day

  • How to Change Worksheet Tab Colors in Excel
  • How to Hide and Unhide Worksheets in Excel
  • How to Use Excel Shortcuts to Add Worksheets
  • Worksheets and Workbooks in Excel
  • How to Move Around and Between Worksheet Tabs in Excel
  • Understand the Basic Excel Screen Elements
  • How to Define and Edit a Named Range in Excel
  • How to Select Rows, Columns, or Worksheets in Excel
  • How to Copy a Sheet in Excel
  • The 23 Best Excel Shortcuts
  • Active Cell/Active Sheet
  • What Is the Name Box in Excel?
  • Double Click the Fill Handle to Copy Formulas in Excel
  • Excel Step by Step Basic Tutorial
  • Quickly Sum Columns or Rows of Numbers in Excel
  • Adding Excel Hyperlinks, Bookmarks, and Mailto Links

TechRepublic

Account information.

how to name worksheet in excel

Share with Your Friends

5 ways to rename a sheet in Microsoft Excel

Your email has been sent

Image of Susan Harkins

When working with more than one sheet, it’s a good idea to give those sheets meaningful names. Doing so improves efficiency, from the simple task of clicking the right sheet to referencing sheets in VBA procedures—those names help us get things done easier and quicker. Most of us pick up a naming method and stick with it, never realizing there’s more than one way to name Excel sheets. In this article, we’ll discuss the rules for naming sheets. Then, I’ll show you five ways to rename sheets—depending on what you’re doing, using a little-known method that can save you a bit of time.

SEE: Software Installation Policy (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use an earlier version. There’s no demonstration file because you won’t need one. Excel for the web supports all five methods for renaming sheets.

Rules for naming sheets in Excel

By default, Excel names sheets using the Sheet x format where x is a consecutive number, as shown in Figure A .

You can rename Excel sheets.

As you add more sheets, x increases accordingly. I recommend that you give sheets a meaningful name if you’re working with more than a few sheets. Even if you don’t use VBA, a meaningful name helps guide you to right sheet instantly. Meaningful names also help when referencing sheets in functions and formula, VBA, and more.

Like most object names, sheet naming has a few rules:

  • You can’t use a reserved word. If you try to use one of Microsoft Excel’s reserved words, Excel will reject the name and tell you why. Find a new name; there’s no way around this.
  • A sheet must have a name, even if you leave the default name.
  • Sheet names are limited to 31 characters.
  • There are a few special characters—[ ] / \ ? * :—that you can’t include in a sheet name.
  • Don’t start or end a sheet name with the apostrophe character (‘).

These rules aren’t so bad; you’ll find them easy to respect. Now, let’s look at a few ways to rename sheets.

1. Double-click

Perhaps the easiest way is also the most intuitive. Simply double-click the sheet name right on the tab. Doing so will highlight the name in edit mode. At this point, you simply write over the name and press Enter. You’re probably already familiar with this routine because Windows lets you rename files this way in File Explorer.

2. Right-click

Another quick method for putting the sheet name in edit mode is to right-click it. In the resulting submenu, choose Rename, as shown in Figure B . Excel will highlight the current name in edit mode at which time you can overwrite the current name. The right-click is about as intuitive as the double-click, but it requires more clicks.

Right-click the sheet name in the tab.

3. The Excel Home tab

The Home tab offers a way into the sheet tab, but most users will never find it on their own. First, click the Home tab and then click Format in the Cell group. Select Rename Sheet, shown in Figure C , from the resulting menu. Doing so will highlight the current sheet name in edit mode at which time you can overwrite the sheet name.

Choose Rename Sheet to rename the current sheet.

Knowing the Rename Sheet command is available on the Format dropdown is the key—most users don’t know about it and won’t go looking for it there. Using one of the first two methods, I can’t think of a good reason for them to bother.

4. Accessibility check

Users requiring an accessibility reader won’t get much from Sheet1, Sheet2 and so on. If you don’t rename sheets for yourself, that’s fine. However, if you don’t know who might end up using your workbook, run the Check Accessibility feature in the Accessibility group on the Review tab. When Excel returns the results, look for the Default Sheet Names section. Click the dropdown arrow and choose Rename Sheet, as shown in Figure D , which will highlight the sheet name in edit mode. If a sheet has already been renamed from the default, Check Accessibility won’t list the sheet.

Find sheets that need to be renamed using Check Accessibility.

This method isn’t particularly intuitive, and you won’t want to use it for renaming sheets specifically. However, it’s good to know that the checker will catch unnamed sheets for you. Hidden sheets won’t show up in the list either, but there’s less of a need to rename hidden sheets than the sheets users interact with.

SEE: Why Microsoft Lists is the new Excel (TechRepublic)

5. Excel’s new Navigation pane

Some Microsoft 365 users have the new Navigation pane. To display it, click the View tab and check the Navigation pane option in the Show group. If you don’t see that option, you don’t have it yet, but should soon.

Excel’s Navigation pane shows a list of objects, sheet names, PivotTables, and more. Click an item to go straight to it. Or right-click a sheet name to highlight it in edit mode so you can rename it. This isn’t a great feature for renaming a sheet; there are easier ways. But if you happen to already be in the Navigation pane, it’s quicker than closing the pane and using one of the easier methods.

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

  • Windows 11 cheat sheet: Everything you need to know
  • Windows PowerToys: A cheat sheet
  • Best encryption software 2022
  • EDR Software: Choosing the Best EDR Tools for Your Business
  • Best software for businesses and end users

Image of Susan Harkins

Create a TechRepublic Account

Get the web's best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let's start with the basics.

* - indicates required fields

Sign in to TechRepublic

Lost your password? Request a new password

Reset Password

Please enter your email adress. You will receive an email message with instructions on how to reset your password.

Check your email for a password reset link. If you didn't receive an email don't forgot to check your spam folder, otherwise contact support .

Welcome. Tell us a little bit about you.

This will help us provide you with customized content.

Want to receive more TechRepublic news?

You're all set.

Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.

How-To Geek

How to rename worksheet tabs in excel.

By default, worksheet tabs in Excel are given generic names, such as Sheet1, Sheet2, and so on.

By default, worksheet tabs in Excel are given generic names, such as Sheet1, Sheet2, and so on. If you have a lot of worksheets in your workbook, finding specific sheets can get tricky.

However, you can assign a name to each tab in your workbook so you can easily find the tab you want. It’s quite simple, and we’ll show you how.

To rename a tab, right-click on the tab and select “Rename” from the popup menu. You can also double-click on a tab.

01_selecting_rename

Type a new name for the tab and press Enter. Tab names can contain up to 30 characters.

02_typing_tab_name

Repeat this procedure for each tab you want to rename.

03_sheets_renamed

If your custom tab names are longer than the default names, they may not all fit on the worksheet tab bar. If they don’t, you will see three dots on the right end, initially. Click the dots to go to the first hidden tab to the right. Every time you click the three dots, the next hidden tab displays and is selected until you reach the last tab. As you click the three dots on the right, you will notice that three dots appear on the left end of the worksheet tab bar also. Click the three dots on the let to move to and select the next hidden tab on the left side of the worksheet tab bar, until you reach the first tab.

04_additional_tabs_indicator

Related: How to Change the Color of the Worksheet Tabs in Excel

Try to be concise with your tab names. The longer your tab names, the fewer tabs will fit on the visible part of the worksheet tab bar.

You can also change the color of your worksheet tabs to distinguish among them.

logo, professor, excel

Professor Excel

Let's excel in Excel

Insert Sheet Name In Cell: Easy! 3 Methods to Return the Worksheet Name

how to name worksheet in excel

Often, you need to insert and work with the sheet name in an Excel sheet, for example if you are working with the ‘INDIRECT’-formula. Or, if you want to dynamically change headlines depending on the sheet name. If you don’t want to type the sheet name manually – which is very unstable – there are three ways to get a sheet name

sheet name, worksheet, name, excel

Before we start : If you just have to insert the sheet name for a small amount of worksheets, please consider doing it manually. It usually is the fastest way.

Method 1: Insert the sheet name using built-in Excel functions

The easiest way is to copy the following function and paste it into your Excel cell:

This formula obtains the filename “=CELL(“filename”,A1)” and separates it after the character “]”. If you want to get the name of another Excel sheet, you have to change the cell reference from “A1” to any cell of the other worksheet. And depending on your version and language of Excel, you might have to translate the function names and maybe replace “,” by “;”.

Insert a sheet name with the cell function.

The big advantages of this method is that it doesn’t require any programming in VBA or a third-party Excel add-in.

On the downside, please note the following comments:

  • If you open a new file and paste this function, it won’t work before saving it.
  • The cell function is volatile . That means, it always calculates no matter if you’ve changed anything. This is a disadvantage for large Excel files where the performance of calculation is crucial.
  • Also, the cell function doesn’t translate to other languages. If your Excel is set to German, Spanish etc., you have to replace the “filename” part with the respective word in your language.

Do you want to boost your productivity in Excel ?

Get the Professor Excel ribbon!

how to name worksheet in excel

Add more than 120 great features to Excel!

Method 2: Return the sheet name using VBA

The second way to get a worksheet name uses VBA. Once set-up, it might be easier to use. You have to open the developer tools and add a module. Next, you can use this code:

Now, you can use the function “=PROFEXSheetName(A1)” in your Excel cell and the worksheet name will be shown.

VBA macro for inserting a sheet name into an Excel cell.

As the result of both ways is (or should be…) the same, it is up to you which way to choose. If you feel comfortable using an easy macro, you probably go for the second way. If not, just paste the formula from the first way to your cell.

Method 3: Use ‘Professor Excel Tools’

Because returning the sheet name is a common problem in Excel, it’s included in the Excel add-in ‘Professor Excel Tools’. Just type =PROFEXSheetName(A1) and it’ll return the sheet name. Download the free trial below.

Please note: for using this feature of Professor Excel Tools, you don’t have to buy a license. The built-in Excel functions are free to use in order to guarantee compatibility.

Professor Excel Tools Box

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Example: Take it a step further: Insert and clean a worksheet name

I usually recommend not using space characters in worksheet names. It has some disadvantages, especially when linking to other worksheets (read more about worksheet names here ).

So, I usually use underscore “_”-characters to separate words in sheet names. Let’s assume the following example: The worksheet name should at the same time be displayed in a heading. Here is how to do it, using the method one from above.

The approach: Wrap the substitute function around the sheet name function:

Cleaning and inserting the worksheet name from underscores.

So, the function is:

Download example sheet and further reading

Please download the example file here . It contains examples for the methods two and three from above.

You don’t want to insert the worksheet name but rather the whole path, file name etc.? Check this article . It has everything, you need!

' src=

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment Cancel reply

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

Save my name, email, and website in this browser for the next time I comment.

Privacy Overview

How to Get the Sheet Name in Excel? Easy Formula

When working with Excel spreadsheets, sometimes you may have a need to get the name of the worksheet.

While you can always manually enter the sheet name, it won’t update in case the sheet name is changed.

So if you want to get the sheet name, so that it automatically updates when the name is changed, you can use a simple formula in Excel.

In this tutorial, I will show you how to get the sheet name in Excel using a simple formula.

This Tutorial Covers:

Get Sheet Name Using the CELL Function

CELL function in Excel allows you to quickly get information about the cell in which the function is used.

This function also allows us to get the entire file name as a result of the formula.

Suppose I have an Excel workbook with the sheet name ‘Sales Data’

Below is the formula that I have used in any cells in the ‘Sales Data’ worksheet:

As you can see, it gave me the whole address of the file in which I am using this formula.

But I needed only the sheet name, not the whole file address,

Well, to get the sheet name only, we will have to use this formula along with some other text formulas, so that it can extract only the sheet name.

Below is the formula that will give you only the sheet name when you use it in any cell in that sheet:

The above formula will give us the sheet name in all scenarios. And the best part is that it would automatically update in case you change the sheet name or the file name.

Note that the CELL formula only works if you have saved the workbook. If you haven’t, then it would return a blank (as it has no idea what the workbook path is)

Wondering how this formula works? Let me explain!

The CELL formula gives us the whole workbook address along with the sheet name at the end.

One rule it would always follow is to have the sheet name after the square bracket (]).

Knowing this, we can find out the position of the square bracket, and then extract everything after it (which would be the sheet name)

And that’s exactly what this formula does.

The FIND part of the formula looks for ‘]’ and return it’s position (which is a number that denotes the number of characters after which the square bracket is found)

We use this position of the square bracket within the RIGHT formula to extract everything after that square bracket

One major issue with the CELL formula is that it’s dynamic. So if you use it in Sheet1 and then go to Sheet2, the formula in Sheet1 would update and show you the name as Sheet2 (despite the formula being on Sheet1). This happens as the CELL formula considers the cell in the active sheet and gives the name for that sheet, no matter where it is in the workbook. A workaround would be to hit the F9 key when you want to update the CELL formula in the active sheet. This will force a recalculation.

Alternative Formula to Get Sheet Name (MID formula)

There are many different ways to do the same thing in Excel. And in this case, there is another formula that works just as well.

Instead of the RIGHT function, it uses the MID function .

Below is the formula:

This formula works similarly to the RIGHT formula, where it first finds the position of the square bracket (using the FIND function).

It then uses the MID function to extract everything after the square bracket.

Fetching Sheet Name and Adding Text to it

If you’re building a dashboard, you may want to not just get the name of the worksheet, but also append a text before or after it.

For example, if you have a sheet name 2021, you may want to get the result as ‘Summary of 2021’ (and not just the sheet name).

This can easily be done by combining the formula we saw above with the text we want before it using the ampersand operator.

Below is the formula that will add the text ‘Summary of ‘ before the sheet name:

The ampersand operator (&) simply combines the text before the formula with the result of the formula. You can also use the CONCAT or CONCATENATE function instead of an ampersand.

Similarly, if you want to add any text after the formula, you can use the same ampersand logic (i.e., have the ampersand after the formula followed by the text that you want to append).

So these are two simple formulas that you can use to get the sheet name in Excel.

I hope you found this tutorial useful.

Other Excel tutorials you may also like:

  • How to Rename a Sheet in Excel (4 Easy Ways + Shortcut)
  • How to Insert New Worksheet in Excel (Easy Shortcuts)
  • How to Unhide Sheets in Excel (All In One Go)
  • How to Sort Worksheets in Excel using VBA (alphabetically)
  • Combine Data From Multiple Worksheets into a Single Worksheet in Excel
  • How to Compare Two Excel Sheets
  • How to Group Worksheets in Excel

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Leave a comment cancel reply.

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

how to name worksheet in excel

Insert the current Excel file name, path, or worksheet in a cell

Let's say you want to add information to a spreadsheet report that confirms the location of a workbook and worksheet so you can quickly track and identify it. There are several ways you can do this task.

Insert the current file name, its full path, and the name of the active worksheet

Type or paste the following formula in the cell in which you want to display the current file name with its full path and the name of the current worksheet:

=CELL("filename")

Insert the current file name and the name of the active worksheet

Type or paste the following formula as an array formula to display the current file name and active worksheet name:

=RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))

To enter a formula as an array formula, press CTRL+SHIFT+ENTER.

The formula returns the name of the worksheet as long as the worksheet has been saved at least once. If you use this formula on an unsaved worksheet, the formula cell will remain blank until you save the worksheet.

Insert the current file name only

Type or paste the following formula to insert the name of the current file in a cell:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Note:  If you use this formula in an unsaved worksheet, you will see the error #VALUE! in the cell. When you save the worksheet, the error is replaced by the file name.

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Facebook

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

how to name worksheet in excel

Microsoft 365 subscription benefits

how to name worksheet in excel

Microsoft 365 training

how to name worksheet in excel

Microsoft security

how to name worksheet in excel

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

how to name worksheet in excel

Ask the Microsoft Community

how to name worksheet in excel

Microsoft Tech Community

how to name worksheet in excel

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

6 Ways to Find Sheet Names in Microsoft Excel

Suppose you want to display the sheet name in the header and footer, create a dynamic formula referencing multiple worksheets, or automate Excel tasks that involve different worksheets. For all these tasks, you must learn how to find the sheet name in Excel.

Microsoft Excel worksheets enable you to create different calculations and data analytics in the same workbook. Using worksheets, you can segregate the data analytics and visualization tasks of a specific project.

Though all the datasets, their calculations, and visualizations are in the same workbook, the Excel file still stays organized due to the fact that you can create hundreds of worksheets to segregate your data.

When the workbook becomes large and it consists of hundreds of worksheets, it can be truly challenging to remember the names of these worksheets. Here comes the sheet name feature of Excel to ease your work.

Keep reading as I show you several ways of finding sheet names in Excel using manual and automatic methods.

Reasons to Find Sheet Name in Excel

You can create dynamic worksheet name views using the sheet name feature in Excel. Here’s an example to help you understand:

Suppose, you created an index of the Excel workbook manually. However, after a few weeks, you had to change the names of a few worksheets. The index won’t update itself automatically. Anyone using the workbook referencing the outdated index will surely make navigational and programmatic errors in the workbook.

To avoid this issue, you can use sheet names in Excel to create dynamic worksheet name views. When someone changes the worksheet name, the index will update automatically.

Find below other important justifications for learning this basic Excel skill:

  • You might want to find the sheet name to automate processes, such as creating customized reports or macros that perform actions on specific worksheets.
  • If you’re setting up custom headers or footers for printing, you can use the sheet name to dynamically display the sheet name on each sheet.
  • In data validation rules or conditional formatting, you can reference the sheet name to apply specific rules or formatting based on the sheet’s name.
  • If you create a dashboard or summary sheets that consolidate data from other sheets, the sheet name can help you reference and pull data from the relevant sheets.
  • For auditing and traceability purposes, you can use the sheet name to track changes and actions performed on specific sheets.
  • When importing or exporting data to and from Excel, the sheet name can be used to specify the target sheet.

So far, you’ve found out what’s sheet name in Excel and why you must learn how to find it. Great! Let’s now explore several methods to get the sheet name below:

Add Sheet Names Using the Header & Footer Command in Excel

The Header & Footer command is the built-in user interface function that allows you to add the workbook name and worksheet name when printing or exporting Excel spreadsheets as PDFs. Find below the steps to follow:

Header and footer function

  • Go to the Excel worksheet in which you want to add the sheet name and file name as header and footer elements.
  • Click the Insert tab and find the Header & Footer command inside the Text block.
  • Click the Header & Footer command.

Added file name and sheet name as header

  • Select the File Name and Sheet Name items in the Header & Footer Elements block.
  • These will be added in the header as the &[Tab] and &[File] codes.

Adding sheet name code in footer

  • Click anywhere on the Excel spreadsheet except the header section.
  • Now, scroll down to the footer section.
  • Click the Footer and add the items you want to add here from the Header & Footer Elements block.
  • Repeat the steps for all other worksheets, if needed.
  • Print the workbook.

The header and footer elements added this way are dynamic content. When you change the worksheet name, the changes will also be reflected in the header and footer sections.

Find Sheet Name with the CELL Formula in Excel

Suppose you need to show the current worksheet name in any cell on the active sheet. What you can quickly do is manually write down the name by copying it from the worksheet tab and pasting the name as a text string in any cell.

However, when you change the worksheet name in the future, Excel won’t be able to update the name automatically. Instead, you can use the following formula that adds a worksheet name in any cell in the active worksheet dynamically.

Getting sheet name code using CELL formula

When you change the active worksheet name, Excel will be able to update the same name in the designated cell.

Here’s how you can create a dynamic worksheet index in your Excel workbook by using the above formula:

Dynamic views of worksheet name

  • Populate dynamic views of worksheet names for all the worksheets in cell A1 except for the one where you want to create the sheet index by using the above formula.

Face worksheet

  • Now, go to the target worksheet and create a column named Worksheet Index .
  • In the first cell below the column header, copy and paste the above formula to populate the name for the target worksheet.

Referencing to worksheets in Excel

  • Now, use the following formula to add the rest of the worksheet names:

A worksheet index using sheet name codes

  • Replace Operations with the actual worksheet names on your workbook in the above formula.

Get Sheet Name in an Excel Cell Using the MID Function in Excel

If the above formula doesn’t work for you or you’re looking for an alternative, you can use the following formula:

How to use MID function to find sheet code name

This function will also view the active worksheet name in the selected cell dynamically.

Create a User-Defined Function to Find Sheet Names in Excel

Since Excel doesn’t offer any dedicated function to find sheet names, you can create one yourself. Excel’s ability to accept user-defined functions or UDFs will help you here. Here’s how:

Creating a UDF

  • Press Alt + F11 to bring up the Excel VBA tool.
  • There, click on the Insert button and choose the Module option.
  • Inside the blank module, copy and paste the following script:
  • Click the Save button.
  • On the warning message that pops up, press No .

Saving workbook as XLSM

  • Choose the XLSM file in the Save as type drop-down menu of the Save As dialog that pops up.
  • Now, close the Excel VBA Editor .

When working in the same workbook in the future, in any cell, just type this formula and Excel will populate the worksheet name instantly:

Executing an UDF

The formula syntax depends on the function name you use in the Excel VBA script.

Find Sheet Names in Excel Using Excel VBA

The best way to find sheet names the way you want using various types of Excel VBA scripting. Find below three different scripts and scenarios that you should know:

Display Sheet Names in a Message Box Using VBA

Suppose you want to create a VBA macro button and place it on the Excel worksheets to view sheet names when needed. You can do so by following the instructions mentioned below.

Create the DisplaySheetName VBA macro by using the following script:

Excel VBA macro to display sheet name code

You can follow the steps mentioned in the previous section to learn how to create a VBA macro in Excel.

Once you’ve successfully created the macro, follow these steps:

Creating a button on excel

  • Go to the Developer tab on the Excel ribbon menu.
  • Click the Insert command inside the Controls block and choose Form Controls > Button .
  • The mouse cursor will turn into a crosshair so you can draw a button.
  • Draw the button on a cell of your choice.

Assigning a macro to a button

  • Once you’re done, the Assign Macro dialog will pop up.
  • There, choose the DisplaySheetName and click OK .

Using an Excel VBA button to learn sheet code name

That’s it. You can now copy and paste this VBA macro button on other worksheets. Whenevre you or another user need to know the current name of the active worksheet, just click the button.

Fetch All Sheet Names in an Excel Workbook Using VBA

You can use the following Excel VBA script to generate a worksheet index in your workbook. Write the CreateSheetIndex macro in your workbook and execute it.

Create a sheet index using VBA

The code will add a new worksheet named SheetIndex where you’ll find all the spreadsheets of the Excel file.

Running sheet index macro

The worksheet index will update automatically if you change the sheet names. You just need to run the macro each time you open the workbook to fetch the latest worksheet names.

Find Sheet Names in Excel Using Office Scripts

While Excel VBA is only available on Excel desktop apps, Office Scripts is available on both Excel for Microsoft 365 desktop and Excel for the web apps.

Also, you can use Office Scripts to further automate your tasks on Microsoft Power Automate.

Therefore, when you need to create an automated way to get sheet names in Excel, you must try out Office Scripts as well. Find below the script and the steps to implement the script:

how to name worksheet in excel

  • Go to the Automate tab.
  • Click on the New Script button inside the Scripting Tools command block.
  • Now, copy and paste the following Office Scripts code inside the Code Editor pane:
  • Click the Save script button to save it.
  • Click on the Run button to execute the script.
  • You’ll see the sheet name in A1 .

Note: Microsoft has limited the access to Office Scripts feature only to Microsoft 365 subscribers who own Microsoft 365 Business Standard or higher plans. If you don’t see the Automate tab, you aren’t eligible to use this feature.

Conclusions

I’ve mentioned 6 different methods to find sheet names in Excel with minimum effort. You can also remember the steps of these methods easily.

You can use the Header & Footer command, the CELL function, and the MID function if you occasionally need to get sheet names. These are the easiest ones you can begin with.

If you like to create automatic programs in Excel using coding, you can choose two VBA-based methods, one is the user-defined function and another using Excel VBA subroutines.

Finally, if you need to use advanced automation in Excel using other Microsoft tools like Microsoft Power Automate, you can use the Office Scripts-based method to get sheet names.

Did this article help?

Do you have a tip you want to share?

Let me know by writing few lines in the comment box below.

About the Author

Tamal Das

Subscribe for awesome Microsoft Excel videos 😃

how to name worksheet in excel

John MacDougall

I’m John , and my goal is to help you Excel!

You’ll find a ton of awesome tips , tricks , tutorials , and templates here to help you save time and effort in your work.

  • Pivot Table Tips and Tricks You Need to Know
  • Everything You Need to Know About Excel Tables
  • The Complete Guide to Power Query
  • Introduction To Power Query M Code
  • The Complete List of Keyboard Shortcuts in Microsoft Excel
  • The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

how to name worksheet in excel

Related Posts

10 Ways to Convert Number to Text in Excel

10 Ways to Convert Number to Text in Excel

Feb 23, 2024

Do you want to learn how to convert number to text in Excel to appropriately...

8 Ways to Find External Links in Microsoft Excel

8 Ways to Find External Links in Microsoft Excel

Professional and collaborative Microsoft Excel workbooks can accumulate...

9 Ways to Reduce Excel File Size

9 Ways to Reduce Excel File Size

Read this Excel tutorial until the end to learn how to reduce Excel file size...

Submit a Comment Cancel reply

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

Save my name, email, and website in this browser for the next time I comment.

Submit Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Get the Latest Microsoft Excel Tips

how to name worksheet in excel

Follow us to stay up to date with the latest in Microsoft Excel!

  • Kutools for Excel
  • Kutools for Outlook
  • Kutools for Word
  • EULA End User License Agreement
  • Setup Made Simple Easily Install or Uninstall
  • Get 4 Sotware Package
  • 60-Day Refund 60-Day Risk-Free Guarantee
  • Tips & Tricks for Excel (3000+)
  • Tips & Tricks for Outlook (1200+)
  • Tips & Tricks for Word (300+)
  • Excel Functions (498)
  • Excel Formulas (350)
  • Excel Charts
  • Outlook Tutorials
  • About Us Our Team

Feature Tutorials

  • Search Search more
  • Retrieve License Lost License?
  • Report a Bug Bug Report
  • Forum Post in Forum

How to rename sheets based on cell values (from list) in Excel?

Renaming a single worksheet in Excel is typically done by right-clicking on the tab of the sheet and choosing "Rename" from the context menu. However, if you need to rename multiple worksheets using values from specified cells, or want to dynamically name a worksheet based on a specific cell's value, there are several methods you can use. This article will guide you through these techniques, making the process straightforward and efficient.

  • Dynamically rename a sheet based on cell value with VBA

Rename multiple sheets based on cell values in corresponding sheets

Rename multiple sheets from the specified list, rename multiple sheets based on cell values in corresponding sheets in excel.

ad rename multiple sheets cell value

Dynamically rename a sheet from cell value with VBA

We can dynamically rename a worksheet from a certain cell’s value with VBA in Excel, and you can do as follows:

how to name worksheet in excel

Step 2: In the opening Microsoft Visual Basic for Applications window, paste the following VBA code into the module window.

Tip: This VBA dynamically renames the worksheet based on the value in cell A1, you can change "A1" to other cells based on your needs.

Step 3: Save the VBA code and close the Microsoft Visual Basic for Application window.

Then you will see the sheet name is dynamically changed based on the specified cell value.

  • If the specified cell is blank, the worksheet won’t be renamed.
  • The worksheet will be renamed each time you change the value in the specified cell.
  • If you type special characters into the specified cell, such as *, an error warning will pop up.

The method described above allows for renaming one sheet at a time. However, by using the Rename Multiple Worksheets feature of Kutools for Excel , you can swiftly rename all or multiple sheets based on the value of a specified cell in each corresponding sheet. For instance, you can rename each sheet to match the value found in cell A1 of that particular sheet.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

how to name worksheet in excel

  • In the Worksheets list, check the worksheets you will rename.
  • In the Rename Options section, check the Replace original sheet name option.
  • In the New Worksheet Name section, please check the Rename worksheets with specific cell option, and specify the cell whose content you will rename corresponding sheet with.
  • Click the Ok button.

And now all checked sheets are renamed based on the specified cell of each sheet. See screenshot:

how to name worksheet in excel

Note: Want to access the Rename Multiple Worksheets feature? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!

Kutools for Excel’s Rename Multiple Worksheets feature also supports to rename multiple sheets based on cell values in a specified list.

  • In the Worksheets list, select the worksheets you will rename.

how to name worksheet in excel

  • Click the Ok button to apply the renaming.

how to name worksheet in excel

Demo: name sheets based on cell values (from list) in Excel

Related articles:

Rename multiple worksheets

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.   Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

how to name worksheet in excel

how to name worksheet in excel

Retrieving Worksheet Names In Excel

Key takeaway:.

  • Knowing how to retrieve worksheet names in Excel is essential for organizing and quickly accessing data. Two methods to retrieve worksheet names include using the Sheets property and the Name property.
  • To retrieve multiple worksheet names, using a For Each loop is useful as it allows for repetitive actions ensuring all desired worksheet names are retrieved.
  • Retrieving hidden worksheet names is also possible by using the Visible property, it is important to retrieve these sheets as they can contain important data and formulas for your spreadsheet.

Stuck with a delicate Excel task? You don’t need to worry, this article will help you quickly retrieve all the sheet names of your Excel file. With just a few simple steps, you can make life easier when it comes to managing your spreadsheets.

Retrieving Worksheet Names in Excel

Retrieve worksheet names in Excel without difficulty! Learn how with this guide. Sub-sections will provide you with the info you need. Get an introduction to the topic , and you’ll be equipped to do it quickly and easily.

Introduction to the topic

Retrieving Worksheet Names in Excel can be crucial for handling large datasets. Accessing worksheet names is important for identifying the database’s contents, and categorizing it. By understanding the worksheet names, one can quickly navigate through average sized data, without having to waste time scrolling.

Moreover, In Excel, one could use a few simple methods to retrieve a list of worksheet names from the current workbook. The ‘Worksheets’ property returns a collection of ‘Worksheet’ objects with each sheet’s information that includes their position and name. Additionally, using the ‘For Each
Next’ loop could display all worksheet names without any interruptions quickly.

In addition to this procedure, users must ensure that they rename worksheet names by keeping signal clarity consistent or related to content enclosed in it. Different worksheets must not carry similar or identical titles lest confusion arises during navigation.

As an example scenario where finding and using Worksheet Names in Excel helped – Mr. Johnston was tasked with analyzing data collected from customers who purchased products worldwide. Instead of scrolling through pages looking for specific forms filled out by certain countries, he accessed the list of worksheet names accurately categorized under regions/countries which made his work more comfortable overall; reducing frustration considerably.

Finding worksheet names in Excel is like a treasure hunt, except the treasure is just a bunch of data and the map is hidden in a dropdown menu.

Methods to retrieve worksheet names

Retrieving worksheet names in Excel? It’s easy! Know the right methods. Here are two solutions: ‘Using the Sheets property’ and ‘Using the Name property’ . Learn about them in this section.

Using the Sheets property

One of the effective approaches to retrieve worksheet names in Excel is by using the Sheets property . This method allows users to access all the worksheets within a workbook and their respective names.

Here is a 5-Step guide on how to use this property efficiently:

  • Open Excel and go to the VBA editor.
  • Select the worksheet you want to retrieve the name for.
  • Type “ Sheets("worksheet_name").Name" in the immediate window.
  • Press Enter , and it will return the name of that particular sheet.
  • Repeat Step 2 – Step 4 for other sheets as well.

It’s essential to note that when using this method, one must understand that it’s case-sensitive and will only return results if typed accurately.

Another crucial point is that this approach is relatively simple and can be used by anyone familiar with basic programming concepts in VBA.

A study conducted by Microsoft revealed that over 800 million people worldwide use Excel on a regular basis , making it one of the most popular software applications globally.

Finding your worksheet’s identity crisis? The Name property has got you covered, bringing clarity to your Excel chaos.

Using the Name property

The Excel Name property can be used to retrieve the names of all worksheets in a workbook. By accessing this property, you can obtain a collection of all worksheet names . This method is particularly useful when working with multiple sheets, as it enables easy management and organization.

To use this technique, you must first define the workbook object using VBA code. Once the workbook object is established, use the Name property to access the collection of worksheet names. Iterate over each sheet in the collection and perform any desired operations.

A major advantage of this method is that it allows for programmatic control over worksheet management. For instance, if you need to rename or delete a specific worksheet within a workbook, using the Name property makes it simple to do so. Additionally, this approach is highly efficient and requires minimal code.

It’s worth noting that while this method can be incredibly useful, it may not be appropriate for all scenarios. If you only need to work with a single worksheet within a workbook or are only interested in certain worksheets, there may be more targeted methods available.

Consider using other VBA properties such as ActiveSheet or Worksheets(index) if such targeting is necessary. However, for situations where general worksheet name retrieval is required, utilizing the Name property is an excellent choice due to its simplicity and ease of use.

Why settle for one worksheet name when you can have them all? Retrieving multiple worksheet names in Excel just got easier.

Retrieving multiple worksheet names

Retrieving multiple worksheet names in Excel with the For Each loop is one way to organize and manage data better. This technique cycles quickly through each worksheet in the file. It makes it easier to spot and analyze the required info. We will now look at the advantages of this approach and how it can make your Excel workflow smoother.

Using the For Each loop

One way to retrieve multiple worksheet names in Excel is by using the For Each loop, which is a powerful tool that can iterate through collections such as worksheets in a workbook.

A 5-step guide to using the For Each loop:

  • Declare and instantiate a variable for the worksheets collection.
  • Set the variable to the active workbook’s Worksheets property.
  • Iterate through each worksheet in the collection using the For Each loop.
  • Access each worksheet’s name property within the loop.
  • Do something with the worksheet name, such as add it to a list or display it on-screen.

This method allows you to automate tasks that require processing multiple worksheets without hardcoding their names, which saves time and minimizes error-prone manual effort.

It’s worth noting that there are alternative ways of retrieving worksheet names, such as using built-in functions like =SHEETNAME() or VBA macros. However, these may not be as flexible or customizable as using code-based approaches like For Each loops.

In previous projects, I’ve used this technique to extract data from named ranges across multiple worksheets and consolidate them into a single output file. The ability to dynamically reference sheets by name without having to rely on static cell references has been invaluable in streamlining my workflows and reducing maintenance overhead.

Why hide worksheets when you can retrieve their names and expose their secrets?

Retrieving hidden worksheet names

Retrieving hidden worksheets in Excel? Employ the “Visible property” technique. Access the hidden ones and make use of their contents. Doing so can help you speed up your workflow !

Using the Visible property

When attempting to retrieve hidden worksheet names in Excel, one potential solution is utilizing the Visible property. By checking this property for each individual worksheet, you can determine whether it is visible or hidden and therefore locate any hidden sheets within your document.

To use this method, first access the Visual Basic Editor within Excel by pressing Alt + F11. From here, navigate to the Immediate Window and enter the following command:

?Sheets("Sheet1").Visible

You should replace “Sheet1” with the name of whichever worksheet you would like to check. This command will output either “True” or “False”, indicating whether or not the specified sheet is currently visible.

One unique advantage of utilizing the Visible property method is that it allows you to both find and unhide any hidden worksheets all at once. By looping through each sheet in your workbook and modifying their Visible properties as needed, you can easily reveal any necessary data without having to manually search for and unhide individual sheets.

Don’t miss out on valuable data hidden away in your Excel documents – try using the Visible property method to retrieve even those pesky hidden worksheets.

Some Facts About Retrieving Worksheet Names in Excel:

  • ✅ You can retrieve worksheet names in Excel using VBA code. (Source: Excel VBA Tutorial)
  • ✅ The most common method for retrieving worksheet names is through the use of the Sheets collection. (Source: Excel Campus)
  • ✅ Worksheet names can also be retrieved using formulas like CELL and MID. (Source: Ablebits)
  • ✅ Retaining the original worksheet name without modification is important for preserving the integrity of the data. (Source: Spreadsheet Journal)
  • ✅ Retrieving worksheet names is essential for managing and analyzing large data sets in Excel. (Source: Investopedia)

FAQs about Retrieving Worksheet Names In Excel

What is the method for retrieving worksheet names in excel.

The easiest way to retrieve worksheet names in Excel is by using the VBA code. You can use this code to display a list of all the worksheet names in a message box.

Can I retrieve worksheet names in Excel without using VBA code?

Yes, there are a few ways you can retrieve worksheet names in Excel without using VBA code. One way is to go to the “Home” tab, click on the “Find & Select” button, and select “Go To Special.” Then, select “Workbook” and click “OK” to see a list of all the worksheet names. Another way is to right-click on the navigation arrows to the left of the worksheet tabs and select “Activate More Sheets.”

Is it possible to retrieve only specific worksheet names in Excel?

Yes, you can retrieve only specific worksheet names in Excel using VBA code. You can use the “Sheets” collection to loop through all the worksheets and check if the name matches your desired name.

Can I retrieve worksheet names in Excel from another workbook?

Yes, you can retrieve worksheet names in Excel from another workbook by opening the workbook and using the VBA code to retrieve the worksheet names. You can also use the “Worksheets” function to retrieve specific worksheet names from another workbook.

How do I retrieve worksheet names in Excel for a large number of worksheets?

If you have a large number of worksheets, it can be tedious to manually retrieve their names. In this case, it’s best to use VBA code to retrieve the worksheet names. You can use a loop to go through all the worksheets and retrieve their names automatically.

Can I retrieve worksheet names in Excel for hidden worksheets?

Yes, you can retrieve worksheet names in Excel for hidden worksheets by using VBA code. You can use the “Sheets” collection to loop through all the worksheets, even if they are hidden, and retrieve their names.

  • Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer

ExcelMojo

VBA Name Worksheet

ExcelMojo Team

What is Excel VBA Name Worksheet?

The Name property is used to get or set the name of an object. When you refer to a worksheet in VBA, you can use the Name property to retrieve or change the name of that worksheet.

You can see how to use the “Name” property to change the name of the Worksheet with VBA with a simple example.

VBA Name Worksheet - Definition Example - 1

Here, the active sheet or the current sheet in the Excel Workbook is “Sheet1.” You can either call it by name or use the ActiveSheet keyword, as shown below.

VBA Name Worksheet - Definition Example - 2

A subroutine is created to change the currently used sheet into any string form given. The ActiveSheet, which is “Sheet1”, is changed to “Example_name” instead once the code is run. In this way, the VBA Name worksheet function can be utilized effortlessly.

VBA Name Worksheet - Definition Example - 3

Table of contents

How to use excl vba name worksheet, examples to name worksheet using vba, important things to note, frequently asked questions (faqs), download template, recommended articles, key takeaways.

  • In VBA (Visual Basic for Applications), the Name property is used to get or set the name of an object.
  • To rename a worksheet in VBA, you use the Name property of a Worksheet object.
  • The Name property is applied to a specific worksheet, and you can use it to change the name of that worksheet. Worksheet names in VBA must be unique within a workbook. Example: Worksheets("Sheet1").Name = "NewSheetName" renames "Sheet1" to "NewSheetName."
  • Ensure uniqueness and handle errors when renaming sheets to avoid issues.

To learn how to name Worksheets in VBA follow the steps below.

How to use Excel VBA Name WorkSheet - Step 1a

You can view the different ways to name worksheets in Excel VBA by going through some examples below.

In this example, you can copy worksheets in VBA and rename them subsequently. You can use the VBA name worksheet today’s date to store the current day’s quota in a worksheet. Follow the steps below.

  • Step 1: Name the subroutine to change the copied worksheet’s name.

Example 1 - Step 1

  • Step 2: Using the VBA Copy function, copy the worksheet of your choice with its name mentioned and set it near the end using the “After” keyword. ‘Sheets.Count’ returns the number of Worksheets; it will place the copied worksheet at the end of the worksheet index.

Example 1 - Step 2

The copied worksheet will have the same name as the sheet that it is copied from, except since it is a copy and to avoid naming conflicts, Excel adds a numerical value after the sheet name as seen below.

Example 1 - Step 2b

  • Step 3: Print the name of the copied worksheet using the MsgBox function .

Example 1 - Step 3

  • Step 4: To prevent an error interrupting the running of the subroutine, you can use Resume Next along with error handling.

Example 1 - Step 4

  • Step 5: Once the worksheet is copied, the copied worksheet becomes the active sheet. Hence, you can change its name to CopiedSheets . by using the ActiveSheet keyword as shown.

Example 1 - Step 5

  • Step 6: If errors persist, you can disable error handling by returning to its default value once any error comes through. This is to ensure that the code runs continuously.

Example 1 - Step 6

  • Step 7: Print the worksheet name of the sheet that was copied.

Example 1 - Step 7

  • Step 8: Now, you can assign the name of the current worksheet to the day’s date using the VBA name worksheet today’s date.

Example 1 - Step 8

The Date function in Excel VBA returns the current date or today’s date that is set in your Excel workbook.

  • Step 9: With the MsgBox function, print the changed name of the worksheet.

Example 1 - Step 9

Sub Change_Copied_Sheet() Worksheets(“NewWorksheet”).Copy After:=Sheets(Sheets.Count) MsgBox ActiveSheet.name, vbInformation On Error Resume Next ActiveSheet.name = “CopiedSheets” On Error GoTo 0 MsgBox “Changed to ” & ActiveSheet.name ActiveSheet.name = Date MsgBox “Changed to ” & ActiveSheet.name End Sub

  • Step 10: Press “F5” to run the subroutine. First, the name of the Worksheet that is copied is printed in a Message box.

Example 1 - Step 10a

Then, the name of the worksheet changes. The new name is printed in a message box.

Example 1 - Step 10c

It changes afterwards because the code implements the VBA Name Worksheet today’s date.

Example 1 - Step 10e

Suppose you have a list of worksheets in your Excel workbook, and you want to change them all to different names. You can enter the name that you want to change it to in any cell and run all the worksheets through a FOR-loop. You can use the VBA Name Worksheet in a FOR loop.

Excel - VBA Name Worksheet in Excel - Example 2

For example, these are the worksheets you have in your workbook. You have a name selected in cell “A1” in each worksheet as shown.

Example 2 - 1

You can now start building the code to change the names of the Worksheets by following the steps below.

  • Step 1: Initialize a subroutine to perform the VBA Name Worksheet from cell value.

Example 2 - Step 1

  • Step 2: Declare a worksheet variable, w in this case. It will be used as an iterative variable in a FOR-loop to run through every sheet in the workbook.

Example 2 - Step 2

  • Step 3: Define a string variable in VBA to store the cell value to later perform the VBA Name Worksheet with variable.

Example 2 - Step 3

  • Step 4: Start a FOR-loop with the Worksheet variable defined to run through all the sheets of the Workbook using the “ThisWorkbook.Worksheets” function to select all the worksheets.

Example 2 - Step 4

  • Step 5: Initialize the string variable with the value of cell “A1” in the FOR-loop.

Example 2 - Step 5

This means that in each worksheet, the cell value of “A1” is taken in and used for changing the name of the worksheets.

  • Step 6: Define an If-Else statement to check if the values aren’t empty to prevent any errors.

Example 2 - Step 6

If the cell value isn’t empty, using the Name property, change the name of the worksheet by equating it to the string value. Then, end the conditional statement.

  • Step 7: Continue the FOR-loop for subsequent worksheets.

Example 2 - Step 7

Sub Rename_Sheets_with_cell_values() Dim w As Worksheet Dim val As String For Each w In ThisWorkbook.Worksheets val = w.Range(“A1”).Value If val <> “” Then w.name = val End If Next w End Sub

  • Step 8: Run the subroutine by clicking on the green button on the VBA Toolbar.

Excel - VBA Name Worksheet in Excel - Example 2 - Step 8a

After running the code, you can see that the code name of the sheets remains unchanged.

Excel - VBA Name Worksheet in Excel - Example 2 - Step 8b

Go to the worksheet to view the changes.

Excel - VBA Name Worksheet in Excel - Example 2 - Step 8c

  • Ensure that the new worksheet name is unique within the workbook to avoid conflicts.
  • Validate that the new name doesn’t contain characters not allowed in sheet names.
  • Don’t set worksheet names to blank values. Ensure that the new name is not an empty string.
  • Neglecting error handling can lead to runtime errors, especially if the chosen name already exists or contains invalid characters.

Yes, you can use a variable to dynamically name a Worksheet in VBA. Declare the variable and assign it to the value you want your sheet to be changed to. See the example below:   SheetName = "NewWorksheet" Worksheets(2).name = SheetName   This code snippet utilizes the variable to store the string value and assign it to the Worksheet afterward.

You can check if a worksheet name already exists before renaming it in VBA using this line of code:   If WorksheetExists(newName) Then   After this, write alternative statements. In this way, you can check if a worksheet name already exists.

Yes, you can use the VBA Name worksheet from cell value to rename worksheet names in VBA. You can either use variables or assign them directly.   Suppose the value of cell “A1” is “TEST”. The name changes can be done in 2 ways.   With variables: Sub eg()     v = Worksheets(1).Range("A1").Value     Worksheets(1).name = v End Sub   Without variables: Sub eg2()     Worksheets(2).name = Worksheets(2).Range("A1").Value End Sub   With these two ways, you can rename a worksheet in VBA using cell values.

Yes, you can rename multiple worksheets at once using FOR-loops with the Worksheet variable running through ThisWorkbook.Sheets. For Example: For Each w In ThisWorkbook.Worksheets         val = w.Range("A1").Value         w.name = val   In this way, you can rename multiple worksheets at once.

This article must be helpful to understand the  VBA Name Worksheet , with it features and examples. You can download the template here to use it instantly.

This has been a guide to VBA Name Worksheet . Here we learn how to name Excel Worksheets using VBA coding, with examples & points to remember. You can learn more from the following articles –

  • VBA Named Range
  • VBA Worksheet Function
  • VBA Copy Worksheet

Leave a Reply Cancel reply

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

Save my name, email, and website in this browser for the next time I comment.

  • Privacy Policy
  • Terms of Service
  • Excel Guides
  • Power BI Guides

MrExcel Message Board

  • Search forums
  • Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

  • If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
  • Question Forums
  • Excel Questions

Vba Code to Remove #Name? error in all cells entire worksheet

  • Thread starter intellitechdataservices
  • Start date Wednesday at 2:10 AM

intellitechdataservices

  • Wednesday at 2:10 AM

how to name worksheet in excel

Excel Facts

CephasOz

Active Member

  • Wednesday at 2:30 AM

You don't need a macro to see that. Just press Ctrl-~ to show the cell contents, and then press it again when you're finished.  

  • Wednesday at 2:45 AM
CephasOz said: You don't need a macro to see that. Just press Ctrl-~ to show the cell contents, and then press it again when you're finished. Click to expand...
  • Wednesday at 3:00 AM
  • Press F5 to bring up the dialog box,
  • click the "Special" button,
  • click the "Formulas" radio button
  • under "Formulas" take the ticks out of "Numbers", "Text", and "Logicals" (so now only "Errors" has a tick in that group), then
  • click the "OK" button.
  • Wednesday at 3:21 AM

... or would this code suit you better? VBA Code: Sub DeleteNameErrors() Dim wksToCheck As Worksheet Dim rngCell As Range Dim xlcPrev As XlCalculation ' xlcPrev = Application.Calculation Application.Calculation = xlCalculationManual For Each wksToCheck In ThisWorkbook.Worksheets For Each rngCell In wksToCheck.UsedRange If rngCell.HasFormula Then If (rngCell.Text = "#NAME?") Then If (rngCell.Formula2R1C1 = "=yes") Then rngCell.ClearContents End If End If End If Next Next Application.Calculation = xlcPrev MsgBox "Finished!", vbInformation + vbOKOnly, "Delete Name Errors" End Sub  

  • Wednesday at 3:38 AM

I dont want to delete #Name? error , I want to show actual text Ctrl~ worked! but this vba not working Ctrl~ worked . Go to also worked  

  • Wednesday at 3:53 AM

Okay, if you just want to show the actual text, rather than the #NAME? error, this code will do that. VBA Code: Sub ShowNameErrors() Dim wksToCheck As Worksheet Dim rngCell As Range Dim xlcPrev As XlCalculation ' xlcPrev = Application.Calculation Application.Calculation = xlCalculationManual For Each wksToCheck In ThisWorkbook.Worksheets For Each rngCell In wksToCheck.UsedRange If rngCell.HasFormula Then If (rngCell.Text = "#NAME?") Then rngCell.Value = "'" & rngCell.Formula2R1C1 End If End If Next Next Application.Calculation = xlcPrev MsgBox "Finished!", vbInformation + vbOKOnly, "Show Name Errors" End Sub  

  • Wednesday at 3:55 AM

Will test this and tell  

  • Wednesday at 3:58 AM

It didnt worked vba  

Attachments

Error2.jpg

  • Wednesday at 4:02 AM

Finally it worked with some modifications Sub ShowNameErrors() Dim wksToCheck As Worksheet Dim rngCell As Range Dim xlcPrev As XlCalculation ' Store the previous calculation mode xlcPrev = Application.Calculation Application.Calculation = xlCalculationManual ' Loop through each worksheet in the workbook For Each wksToCheck In ThisWorkbook.Worksheets ' Loop through each cell in the used range of the worksheet For Each rngCell In wksToCheck.UsedRange ' Check if the cell contains a formula and if its text is "#NAME?" If rngCell.HasFormula Then If rngCell.Text = "#NAME?" Then ' Replace the cell value with the formula preceded by an apostrophe rngCell.Value = "'" & rngCell.Formula End If End If Next rngCell Next wksToCheck ' Restore the previous calculation mode Application.Calculation = xlcPrev ' Display a message box to indicate completion MsgBox "Finished!", vbInformation + vbOKOnly, "Show Name Errors" End Sub  

Similar threads

  • Feb 10, 2024

rollis13

  • Oct 17, 2023

Fluff

  • Jan 25, 2024
  • LearningExcel2024
  • Jan 15, 2024

RoryA

  • Jan 1, 2024

Forum statistics

Share this page.

how to name worksheet in excel

We've detected that you are using an adblocker.

Which adblocker are you using.

AdBlock

Disable AdBlock

how to name worksheet in excel

Disable AdBlock Plus

how to name worksheet in excel

Disable uBlock Origin

how to name worksheet in excel

Disable uBlock

how to name worksheet in excel

how to name worksheet in excel

Top Contributors in Excel: HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Rory Archibald   ✅

February 13, 2024

Top Contributors in Excel:

HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Rory Archibald   ✅

  • Search the community and support articles
  • Microsoft 365 and Office
  • Search Community member

Ask a new question

How to auto name worksheets tab

I have a worksheet that I want auto named based on the data in multiple cells. I need the tab name to be the combination of what is in these cells.

I would need the worksheet tab to be auto named - Excel Help Needed.

Is this possible?

Report abuse

Replies (4) .

AnastasiosGR

right click on sheet tab, select view code and paste in...

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("A1:A3")) Is Nothing And Application.CountA(Range("A1:A3")) = 3 Then ActiveSheet.Name = Range("A1") & " " & Range("A2") & " " & Range("A3") End If End Sub

each time you change a value in range a1:a3

(a1:a3 non empty cells)

2 people found this reply helpful

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

Mike H..

You need VB code for that and this one-liner will do it.

ActiveSheet.Name = Join(Application.Transpose(Range("A1:A3").Value), " ")

1 person found this reply helpful

That doesn't seem to work for what I am trying to do. Let me be more specific.

I have a sheet where cell D12 and L12 are currently blank. I need the worksheet to be named what is in these two cells plus additional text. Lets say I enter the word need into D12 and the word excel into L12, but I want the worksheet tab to be named need excel help . How do I get the code to put the data from both cells together and add the word help ?

(always the first word is in cell D12 and the second in cell L12)

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("D12, L12")) Is Nothing And _

Application.CountA(Range("D12, L12")) = 2  Then ActiveSheet.Name = Range("D12") & " " & Range("L12") & " help" End If End Sub

Question Info

  • Windows other
  • Office 2010
  • Norsk BokmĂ„l
  • Ελληνικά
  • Русский
  • עברית
  • العربية
  • ไทย
  • 한국어
  • 中文(简体)
  • 中文(繁體)
  • 日本語

IMAGES

  1. 3 Quick Ways to Get a List of All Worksheet Names in an Excel Workbook

    how to name worksheet in excel

  2. Excel names and named ranges: how to define and use in formulas

    how to name worksheet in excel

  3. How to Name Worksheet Name with Specified Cell Value in Excel

    how to name worksheet in excel

  4. Worksheet Name In Excel

    how to name worksheet in excel

  5. Microsoft Excel: Create an automated list of worksheet names

    how to name worksheet in excel

  6. How To Using Sheet Names In Formulas Excel

    how to name worksheet in excel

VIDEO

  1. Separate Name & Numbers in MS Excel 😎

  2. Get the Worksheet Name Dynamically In Excel

  3. How to Automatically Change Worksheet Names Based on Cell Values (Simple)

  4. Excel

  5. Create Custom Excel Formula from Names

  6. How To Give Months Name In Worksheets In Ms Excel Very Simple Method

COMMENTS

  1. Rename a worksheet

    Get started Basics Rename a worksheet Rename a worksheet Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 More... By default, Excel names worksheets Sheet1, Sheet2, Sheet3 and so on, but you can easily rename them. 3 ways to rename a worksheet Double-click the sheet tab, and type the new name.

  2. How to Get Excel Sheet Names? (3 Easy Ways)

    Method #1: Using TEXTAFTER and CELL Functions to Get the Worksheet Name in Excel The TEXTAFTER function, only available in Excel 365, returns text that occurs after a given character or string. The CELL function returns information about a cell's formatting, location, or contents.

  3. How to Rename a Sheet in Excel (4 Easy Ways + Shortcut)

    Using a mouse is the easiest way to change the name of a sheet in Excel. Below are the steps to rename a sheet using the mouse double-click: Place your cursor over the sheet tab that you want to rename Double-click on it. This will put the sheet name in the edit mode Enter the name of the sheet that you want

  4. 7 Ways to Rename a Sheet in Microsoft Excel

    Rename a Sheet Using a Keyboard Shortcut. Unfortunately, there isn't a dedicated keyboard shortcut for renaming a sheet. But we can take advantage of the Alt hotkey shortcuts. Alt + H + O + R will get you to the Rename Sheet command in the Home tab. Pressing the Alt key will activate the hotkeys in the ribbon.

  5. How to Rename Sheet in Excel

    In this video tutorial, you'll see a superquick way how to easily rename a worksheet in Excel. Watch this excel video tutorial to learn how to do it.Watch ne...

  6. Excel Tutorial: How To Name Worksheet In Excel

    Explain how to select and rename multiple worksheets at once. To rename multiple worksheets simultaneously, first, hold down the Ctrl key on your keyboard and click on each worksheet tab that you want to rename. Once you have selected all the worksheets, right-click on any of the selected tabs and choose Rename.

  7. How to name and format worksheet tabs (video)

    When you open a new workbook, or when you add new worksheets to an existing workbook, Excel uses a generic name for each sheet: Sheet1, Sheet2, Sheet3, and so on. As you build out a workbook to meet your needs, you'll probably want to rename these sheets to keep things organized. The easiest way to rename a worksheet is to double-click its name.

  8. How to Rename a Worksheet in Excel

    This method works in all versions of Excel for Windows, Mac, Microsoft 365, and Online. Right-click on the tab of the worksheet you want to rename to open the context menu. Click on Rename in the menu list to highlight the current worksheet name. Type the new name for the worksheet. Press the Enter key on the keyboard to complete renaming the ...

  9. 5 ways to rename a sheet in Microsoft Excel

    Now, let's look at a few ways to rename sheets. 1. Double-click. Perhaps the easiest way is also the most intuitive. Simply double-click the sheet name right on the tab. Doing so will highlight ...

  10. Rename Excel Worksheets: Methods, Rules, Tips and Tricks

    Rename worksheets with your mouse. Rename worksheets with Alt -> h -> o -> r. Changing a worksheet name in Excel is quite straight forward. There are actually three ways: Double click on the sheet name and type a new name. Right click and the sheet name and click on 'Rename'. For Mac users: Click on 'Format' in the Mac menu bar.

  11. How to Rename Worksheet Tabs in Excel

    To rename a tab, right-click on the tab and select "Rename" from the popup menu. You can also double-click on a tab. Type a new name for the tab and press Enter. Tab names can contain up to 30 characters. Repeat this procedure for each tab you want to rename. If your custom tab names are longer than the default names, they may not all fit ...

  12. Dynamic Worksheet Tab Names In Excel

    To modify the name of a worksheet tab, follow these steps: Select the sheet that you want to rename. Right-click on the tab at the bottom of the workbook window. The Rename command will appear in the context menu, click it. Type in a new name for the worksheet. Press Enter or click on any other cell to confirm your new name.

  13. Insert Sheet Name In Cell: Easy! 3 Methods to Return the Worksheet Name

    Method 1: Insert the sheet name using built-in Excel functions The easiest way is to copy the following function and paste it into your Excel cell: =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))) This formula obtains the filename "=CELL ("filename",A1)" and separates it after the character "]".

  14. How do i put the name of a worksheet into a worksheet so that it can

    How do i put the name of a worksheet into a worksheet so that it can be identified ... In this forum, we are Microsoft consumers just like yourself. In Excel, you can use the `CELL` function to display the name of the current worksheet. Enter the following formula in a cell where you want to display the worksheet name: =RIGHT(CELL("filename ...

  15. How to Get the Sheet Name in Excel? Easy Formula

    When working with Excel spreadsheets, sometimes you may have a need to get the name of the worksheet. While you can always manually enter the sheet name, it won't update in case the sheet name is changed. So if you want to get the sheet name, so that it automatically updates when the name is changed, you can use a simple formula in Excel.

  16. Insert the current Excel file name, path, or worksheet in a cell

    Insert the current file name and the name of the active worksheet Type or paste the following formula as an array formula to display the current file name and active worksheet name: =RIGHT (CELL ("filename"),LEN (CELL ("filename"))- MAX (IF (NOT (ISERR (SEARCH ("\",CELL ("filename"), ROW (1:255)))),SEARCH ("\",CELL ("filename"),ROW (1:255)))))

  17. 6 Ways to Find Sheet Names in Microsoft Excel

    Go to the Excel worksheet in which you want to add the sheet name and file name as header and footer elements. Click the Insert tab and find the Header & Footer command inside the Text block. Click the Header & Footer command. Added file name and sheet name as header Select the File Name and Sheet Name items in the Header & Footer Elements block.

  18. How to rename sheets based on cell values (from list) in Excel?

    Step 1: Right click on the sheet name of the worksheet that you will dynamically name it by a cell value, and select View Code from the right-clicking menu. Step 2: In the opening Microsoft Visual Basic for Applications window, paste the following VBA code into the module window.

  19. How to Get All Worksheet Names in Excel (2 Easy Ways)

    Method #1 - Getting Sheet Names Using Formula Method #2 - Getting Worksheet Names Using VBA Creating Worksheet Hyperlinks Bonus: Creating Dynamic Sheet Names List Method #1 - Getting Sheet Names Using Formula We'll be getting all our sheet names with a formula but the formula isn't being applied conventionally, straightaway in the target cell.

  20. Automatically name a new worksheet with a date sequence

    Automatically name a new worksheet with a date sequence. Hello, I am trying to create a workbook in which new worksheet names are automatically showing the current date and month. For example, i have a worksheet named May 2019. I would like to insert a new worksheet that is automatically renamed to Jun 2019. When i create one in July, it would ...

  21. Retrieving Worksheet Names In Excel

    Yes, there are a few ways you can retrieve worksheet names in Excel without using VBA code. One way is to go to the "Home" tab, click on the "Find & Select" button, and select "Go To Special.". Then, select "Workbook" and click "OK" to see a list of all the worksheet names. Another way is to right-click on the navigation ...

  22. How to Reference Worksheet Name in Formula in Excel

    📌 Steps: At the very beginning, go to the C5 cell >> enter the formula given below. =January!C5+February!C5 Here, "January and February" refer to the worksheet names, and the C5 cell corresponds to the "Desktop Sales" in these two months. 2. Reference Worksheet Name with Spaces or Punctuation Characters

  23. VBA Name Worksheet in Excel

    You have a name selected in cell "A1" in each worksheet as shown. You can now start building the code to change the names of the Worksheets by following the steps below. Step 1: Initialize a subroutine to perform the VBA Name Worksheet from cell value. Step 2: Declare a worksheet variable, w in this case.

  24. Vba Code to Remove #Name? error in all cells entire worksheet

    Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

  25. How to auto name worksheets tab

    I have a worksheet that I want auto named based on the data in multiple cells. I need the tab name to be the combination of what is in these cells. Example: Cells: A1=Excel. A2=Help. A3=Needed . I would need the worksheet tab to be auto named - Excel Help Needed. Is this possible?

  26. Homework #4- Excel Illustration #1.pdf

    First and Last Name: _____ Date: _____ Homework #4: Excel Illustration #1 Instructions You will turn in only one Excel document. The Excel document should be entitled as "Last Name, First Name_Homework #4".For questions 2-14, you should use a different worksheet within one Excel document and name each worksheet according to each question.

  27. Mubashshir Imam on Instagram: "To perform Excel data entry effectively

    Open Excel: Launch Microsoft Exc..." Mubashshir Imam on Instagram: "To perform Excel data entry effectively, follow these steps: 1. Open Excel: Launch Microsoft Excel on your computer.