Quick Tip: Add worksheet name to an excel printout

Have you ever printed an excel file with multiple tabs or worksheets, only to find you can’t tell which page came from which worksheet?  There are several ways to solve this one, but the easiest is to just put the worksheet name in the header or footer, so that when you print the file, each page indicates the sheet it came from.

To include the worksheet name on every sheet:

  • Start on the first worksheet, and highlight all the others by holding the control button and clicking each sheet name.  This will apply the footer to all the sheets at once so that you don’t have to set each sheet individually.
  • Go to the Page Layout, Page Setup menu and click the Header/Footer tab.
  • Choose Custom Header or Custom Footer, depending on where you want the sheet name to appear
  • Put the cursor in the Left, Center or Right sections and and select the icon that looks like a spreadsheet with multiple tabs at the bottom.  This will put the formula &[Tab] into the space.  Click OK.
  • Print preview the file to ensure that the worksheet name appears where you would like it.  If you need to adjust your margins so that there is enough space for the header/footer to not overlap data, you can.
  • When you go to print, be sure you are on the first tab, and select Print Entire Workbook so that all the pages print at once.

If you need help formatting your spreadsheet to print, see this previous post on tips for formatting your excel spreadsheet to print.

Need help with a print job?  Give us a call at 512-472-9911 or visit our printing services page to learn more about how we can help!

Quick Tip: How to format excel spreadsheets to print

LOCATED  in downtown Austin at 515 Congress Avenue, Suite F GIVE US A CALL AT   512.472.9911

We are pleased to offer  Relativity and a menu of leading legal technologies to support your goals. More than just a user name and password, we provide a customized, dynamic approach to your data.

  • UBEO Acquires Rainmaker Document Technologies
  • Eliminate Overhead, Outsource to Rainmaker
  • Focus on your law practice and let us handle the rest!

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:

print worksheet name in excel 2013

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:

print worksheet name in excel 2013

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.

4 thoughts 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.

Thank you for posting this. I was wondering why it wasn’t working 🙁

Will you help to edit vstack formula to include sheets names of source?

‎Feb 19 2024 08:14 PM I prefer to have Excel Cell Function on Excel Web; as-is, I have to switch to Desktop Edit but when blocked in public computers, do I carry a Windows Laptop (with Tablet, Smartphone for our digital AND also work-anywhere environment)? just to use Excel Desktop?

Sheet name function reduce creation of next Helper Sheet(s) if it indirectly reference Sheets that start with same name.

INDIRECT(TEXTAFTER(CELL(“filename”,A1),”]”)…..)

This is my stopgap measure of using functions that work across rows only like MMULT but Date are in columns.

I am adding a sheet Sheet_Local_Currency_Months-In-Columns Sheet_Local_Currency_Months-In-Columns_Transposed

Without Sheet Name function, I need to hand transpose every Sheet. vote https://techcommunity.microsoft.com/t5/excel-blog/what-s-new-in-excel-march-2023/bc-p/4061601#M4142

Leave a Comment Cancel reply

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

  • Kutools for Excel
  • Kutools for Outlook
  • Kutools for Word
  • Setup Made Simple
  • End User License Agreement
  • Get 4 Software Bundle
  • 60-Day Refund
  • 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
  • ExtendOffice GPT ExtendOffice GPT Assistant
  • About Us Our Team

Feature Tutorials

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

Explore the new, free version of Kutools for Outlook today!

How to print sheet name or a list of sheet names in Excel?

By default, it is hard for you to figure out which printout came from which worksheet as the sheet name will not be printed by default in Excel. In this article, we will show you how to print worksheet with the worksheet name in it, and how to print a list of worksheet names.

Print sheet name with changing the print page setup Print sheet name by inserting the sheet name in a cell with Kutools for Excel  Print a list of sheet names with VBA code Print a list of sheet names with Kutools for Excel 

Using sheet index in your workbook helps you manage your worksheets in ease. With the Create List of Sheet Names utility of Kutools for Excel , you can easily create a sheet index with all sheet names of current workbook listing inside. And you can quickly shift to a certain worksheet by just clicking the sheet name in the Index worksheet. See below screenshot:

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now

print worksheet name in excel 2013

Print sheet name with changing the print page setup

You can change the print page setup by adding the sheet name into the Header/Footer of the worksheet, and then print it.

1. In Excel 2010 and 2013, click File > Print > Print Setup . See screenshot:

print worksheet name in excel 2013

In Excel 2007, please click the Office button > Print > Print Preview . And then click Page Setup under the Print Preview tab.

print worksheet name in excel 2013

2. In the Page Setup dialog box, go to the Header/Footer tab. If you want to insert the sheet name to header, please click the Custom Header button, but if you want to insert the sheet name to footer, click the Custom Footer button. Here I click the Custom Header button. See screenshot:

print worksheet name in excel 2013

3. In the Header dialog box, click the Left section box, then click the Insert Sheet Name button, and finally click the OK button. See screenshot:

print worksheet name in excel 2013

Note : You can also insert the sheet name to the Center section or Right section of header as you need.

4. When it returns to the Page Setup dialog box, click the OK button. Then it will print out the worksheet name when printing the worksheet.

Print sheet name by inserting the sheet name in a cell with Kutools for Excel

The Insert Workbook Information utility of Kutools for Ecel can help you quickly insert active worksheet name into a cell, and then you can manually print the sheet name as you need. Please do as follows.

Before applying Kutools for Excel , please download and install it firstly .

1. Click Kutools Plus > Insert Workbook Information . 

print worksheet name in excel 2013

2. In the Insert Workbook Information dialog box, check the Worksheet name option in Information section, select the cell you want to insert the sheet name in the Range box, and then click the OK button.

print worksheet name in excel 2013

3. Print your sheet.

Note : you can insert the sheet name at worksheet header or footer by checking the Header or Footer option.

  If you want to have a free trial (30-day) of this utility, please click to download it , and then go to apply the operation according above steps.

Print a list of sheet names with VBA code

If you need to print a list of all sheet names of the whole workbook, you need to list all worksheet names in a list with following VBA code first. In this section, we guide you through how to use VBA to list all worksheet names in a list.

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module , then copy and paste the below VBA code into the editor.

VBA: List all worksheet names

4. Press F5 key to run the code. In a popping up KutoolsforExcel dialog box, select a cell for locating the sheet name list, and then click the OK button.

print worksheet name in excel 2013

5. Then all worksheet names are populated, and then you can print it now.

Print a list of sheet names with Kutools for Excel

If you want to print a list of sheet names in current workbook, the Create List of Sheet Names utility of Kutools for Excel can do you a favor.

1. Click Kutools Plus  > Worksheet > Create List of Sheet Names .

print worksheet name in excel 2013

2. In the Create List of Sheet Names dialog box, specify the settings as below screenshot shown, and then click the OK button.

print worksheet name in excel 2013

Then a new worksheet will be created to list all worksheet names in current workbook, and then you can print it.

print worksheet name in excel 2013

Related articles:

  • How to print worksheet with cell formulas displaying in Excel?

Best Office Productivity Tools

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

🤖 : Revolutionize data analysis based on:                   …
:                 ...
:                   ....
:              ....
:  ...
:                       (Auto Text)                                     (filter bold/italic/strikethrough...) ...
Tools ( , , ...)       Types ( , ...)       ( , ...)       Tools ( , , ...)      Tools ( , , ...)      Tools ( , , ...)      ... and more

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!

print worksheet name in excel 2013

ExcelTrick - Easy, Smart & Effective!

  • How To’s
  • Excel Functions

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

You have made a decision. You are, Excel-ly, not walking the manual route unless you must. Whether the file contains a single worksheet or a single hundred, putting together a list of all the worksheet names is not a situation demanding that you take the manual route so here we are. Welcome to Exceltrick's guide on getting all worksheet names in Excel so you can focus your skills and energy on other requirements.

Our tutorial comprises getting sheet names in Excel using a formula and a couple of VBA codes. Also, find out how to add hyperlinks to up your Excel game.

Let's get working!

List All Sheet Names In Excel

Table of Contents

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. We need a small background working to patch up our formula. A named function will be created so that the name can be used in the formula on the worksheet to return all the sheet names in the workbook.

The function will be named using the Name Manager and the detailed steps ahead will give you the rest of the story on how to get all the worksheet names in Excel:

  • Go to the Formulas tab and click on the Name Manager in the Defined Names group to launch the Name Manager .
  • Alternatively, you can open the Name Manager using the Ctrl + F3 keys.

Getting Sheet Names in Excel Using Formula

  • Select the New button in the Name Manager .

Getting Sheet Names in Excel Using Formula

  • Enter the details to create the named function.

Add a name of choice in the Name field as we have added SheetNames.

Keep Workbook as the Scope .

Copy and paste the following formula in the Refers to field:

In the formula, =GET.WORKBOOK(1) returns all the sheet names of a workbook prefixed with the workbook name and file extension enclosed in square brackets. E.g., if the workbook name was MyWorkbook, =GET.WORKBOOK(1) would return [MyWorkbook.xlsx]Sheet1 as the first sheet name.

The rest of the formula works to replace the file name with empty text using the REPLACE function so that we are left with just the sheet name. 1 in the second argument indicates the starting point of the replacement text. The endpoint for the replacement is given in the third argument through the FIND function . REPLACE is to replace the text starting from the first character of the workbook name (i.e. the opening square bracket) to where the FIND function finds the closing square bracket in the workbook name.

Replacing the text from the opening to the closing square bracket will eliminate the prefixed file name and leave just the sheet name as Sheet1.

Getting Sheet Names in Excel Using Formula

  • After entering the formula, hit the OK command to create the named function.
  • Now you will see the new named function in the Name Manager , ready to be used as a name in a worksheet formula.

Getting Sheet Names in Excel Using Formula

  • Close the Name Manager .
  • In a separate column, before the column intended for the sheet names, add serial numbering. This numbering represents the number of sheets in the workbook.
  • Next, add the following formula to the target cell, where you want the sheet names to begin.

Use the named function in the INDEX formula to get all the sheet names, minus the prefix of the file name. When typing out the formula, the named function will appear in the Formula AutoComplete :

Getting Sheet Names in Excel Using Formula

With the formula applied using the created name, we have all the names of the workbook's worksheets listed below:

Getting Sheet Names in Excel Using Formula

Method #2 – Getting Worksheet Names Using VBA

Other functions can be used as names in formulas to create a list of the worksheet names and you can also employ VBA for the same job. Use a code in VBA that will itemize all the worksheet names from the workbook to the target location on the active worksheet. Read the steps given ahead to learn how to get sheet names in Excel using VBA :

  • Use the Visual Basic button in the Developer tab's Code group to access the Visual Basic You can directly use the Alt + F11 keys for the Visual Basic editor if the Developer tab is disabled on your Excel.

Getting Sheet Names in Excel Using VBA

  • With the Visual Basic editor now open, click on the Insert tab above the toolbar and select Module .

Getting Sheet Names in Excel Using VBA

  • In the Module window that has opened, copy and paste this code for getting the worksheet names:

Getting Sheet Names in Excel Using VBA

  • When done setting the code, use the Run button from the toolbar or the F5 key to run the code.
  • In the background of the Visual Basic editor, the worksheet names will be enlisted as soon as you run the code.
  • Close the Visual Basic

Getting Sheet Names in Excel Using VBA

We got you the list. You add the serial numbers. If you must.

Creating Worksheet Hyperlinks

Aren't we fans of all things that are just a click away? Yes, we are and that is the promise that comes with hyperlinks. Instead of clicking through the worksheet tabs before you find what you're looking for, you can use a clickable link with all the worksheet names to land you on the chosen worksheet, courtesy of the HYPERLINK function. The HYPERLINK function creates a shortcut, opening a document on your computer or the internet.

Since hyperlinks are an added feature, we'll expand on our example workbook from above and create worksheet hyperlinks with the extracted worksheet names. Here's what you need to do after extracting the sheet names:

In a free column, add the following formula against the first worksheet name:

It might look like this formula is housing a lot but it's just two arguments: the link location and the hyperlink name. Therefore, "Go to Sheet" after the comma in the formula is the hyperlink name or the anchor text that will appear once we've applied the formula. Its appearance will be that of a regular hyperlink: the underlined name in blue font.

Now for the part in the formula that is before the comma; this is the link location, the point where the created link will take you. In our case, we need it to take us to the selected worksheet in the same workbook. The hash sign, exclamation mark, and double quotes are a way of adding the cell and sheet references as the location.

A pair of double quotes enclose the hash sign and also !B2 while single quotation marks enclose &C3&. The hash can be joined to the cell reference C3 but C3 needs to be separated and enclosed in single quotes when there are spaces in the worksheet name. The cell reference C3 is used since we have the sheet names listed in column C. C3 is wrapped in ampersands (&) to keep the cell reference relative so that as the formula is copied down, the next sheet name from C4 will be taken.

B2 is the cell on the destination sheet that the hyperlink will land us on. And that is how the formula is pieced together, folks. See the hyperlinks we've created using this formula:

Creating Worksheet Hyperlinks

Create your worksheet hyperlinks and use them. You know you want to.

Bonus: Creating Dynamic Sheet Names List

With hyperlinks. That's right! In this section, you'll find an easy way of creating a dynamic list of all the worksheet names in the workbook. The new list will come with another perk, or two; the list will be created in hyperlinks, also hyperlinking back to the sheet with the list.

Since that can require some attention to detail, we'll just dump all the responsibility on a VBA code and get going. The following steps are on making a hyperlinked list of all the worksheet names in Excel:

Note: You can choose any sheet for housing the list but bear in mind that the name of the target sheet will be skipped from the list without any gaps; the name of the sheet before and after the target sheet will be listed one after the other. In our case example, we are using the first sheet (Sheet1) as the target sheet for the list.

  • Set the target sheet for creating the sheet names list.
  • Right-click the tab of the target sheet and select View Code from the menu.

Creating Dynamic Sheet Names List

  • You will be redirected to a Code window in the Visual Basic editor.
  • Copy the code provided below and paste it into the Code

Creating Dynamic Sheet Names List

  • Run the code using the F5 key or the Run

And that's the hyperlinked list of all the workbook's worksheet names.

Creating Dynamic Sheet Names List

Clicking on the last link, we are directed to the selected sheet with the return link in cell A1:

Creating Dynamic Sheet Names List

Let's test the main purpose of this exercise, the list's dynamic quality. We're adding a new sheet to see if the list responds.

Creating Dynamic Sheet Names List

It does! The return link will not be instantly created in the new sheet but it will, once you visit the main sheet and let the list update automatically. The list will respond to additions, deletions, and position changes of the worksheets.

So that was how to get all the worksheet names listed in Excel. Now you know 2 easy ways to do that, involving built-in and segregated hyperlinks. We'll catch you with another Excel situation rounding the corner!

Subscribe and be a part of our 15,000+ member family!

Now subscribe to Excel Trick and get a free copy of our ebook "200+ Excel Shortcuts" (printable format) to catapult your productivity.

ExcelTick - 200+ Excel Shortcuts ebook

You May Also Like

Excel-HYPERLINK-Function

Mehwish, an ACCA-qualified professional, transitioned from an audit trainee to an Excel specialist. With a foundation in financial auditing, her 4+ years of Excel expertise, showcased as a Content Specialist at ExcelTrick, bridges her auditing background with advanced spreadsheet skills. Read more...

How to Find Circular References in Excel Flash Fill in Excel - Complete Guide How to Enable Macros in Excel Sum Cells based on Background Color How to Spell Check in Excel Counting Unique Values In Excel How to Insert a Checkbox in Excel Excel Function Keys and Shortcuts How to Insert Multiple Rows in Excel Use an Image as a Background in Excel How to Find External Refrences in Excel How to Concatenate in Excel

Excel IF Statement - How to Use Excel FILTER Function - How to Use Excel VLOOKUP Function - How to Use Excel HLOOKUP Function - How to Use Excel ROUND Function - Complete Guide Excel SUMIF and SUMIFS - Complete Guide Excel COUNTIF Function - How to Use Excel OFFSET Function - How to Use Excel TEXT Function - How to Use Excel MATCH Function - How to Use Excel IFERROR Function - How to Use

VBA Wait and Sleep Functions VBA On Error Statement – Handling Errors VBA Substring – How to Substring in Excel VBA VBA VLOOKUP – With Examples VBA MsgBox - How to Use VBA For, For Each, Do While & Do Until Loops VBA Split Function - How to Use VBA DIR Function - How to Use VBA IF Function - How to Use VBA InStr and InStrRev - How to Use VBA REPLACE Function - How to Use

ExcelTrick Logo

Subscribe to ExcelTrick

logo, professor, excel

Professor Excel

Let's excel in Excel

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

print worksheet name in excel 2013

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!

print worksheet name in excel 2013

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

ExcelDemy

How to List Sheet Name in Excel (5 Methods + VBA)

Avatar photo

In some cases, you may have dozens of sheets in your workbook. However, if you need the list of sheet names, you can use Excel formulas. In this article, I’ll discuss 5 conventional methods which are mainly formulas and also VBA code to find the sheet name list in Excel.

Here, we’ll show the process of retrieving the list of sheet names in a single column. In the following figure, you see that we have 12 sheets which are mainly prices in months along a year. Also, the names of the sheets are Price_Jan, Price_Feb , and so on.

Dataset

Unfortunately, there is no dedicated function to get the list of sheet names in Excel. So we have to use the combination of several functions or the 2-step process of using Name Manager & formula.

Let’s dive into the methods.

1. Using the Combination of INDEX & FIND Functions to Get Sheet Name List

If you want to find the list of sheet names using several functions, you may use the formula containing the INDEX , LEFT , MID , and FIND functions.

Follow the steps below.

⏩ Click on the Formulas tab > Select the Name Manager option from the Defined Names ribbon.

⏩ Then you’ll see a dialog box named Name Manager .

⏩ Press on the New option.

Opening Name Manager

⏩ Insert the Name (here the name is Worksheets ) and insert the below formula in the Refers to section.

Creating a New Name

Note: GET.WORKBOOK is a macro-enabled function that stores the sheet names in the workbook.

⏩ After that enter the following formula in cell B5 where you want to get the sheet names.

Sheet Name List Using INDEX & FIND Functions

⏩ Press Enter and use the Fill Handle tool for the below cells and you’ll get the list of sheet names like the following.

Sheet Name List Using INDEX & FIND Functions

2. Utilizing the INDEX Function along with the REPLACE Function (Automated List)

⏩ Again, you can insert the below formula in the Refers to section and the Name as SheetNames after clicking the New option from the Name Manager dialog box.

Sheet Name List Utilizing The INDEX Function

⏩ Now, insert the formula on cell C5 .

Here, B5 is the starting cell of the serial number ( S/N ).

Sheet Name List Utilizing The INDEX Function

3. Applying the TRANSPOSE Function to Find Sheet Name List

Furthermore, you can apply the TRANSPOSE function which returns a horizontal cell range as a vertical cell range, or vice versa.

⏩ Before doing this make sure the Name is GetSheetNames and insert the below formula.

Applying TRANSPOSE Function to Find The Sheet Name List

⏩ Then insert the following formula and you’ll get the following output.

Applying TRANSPOSE Function to Find The Sheet Name List

4. Using the LOOKUP Function to Get the Sheet Name List

⏩ Before using the LOOKUP function , create a new name where the Name may be Sheets and the formula in the Refers to section.

Using the LOOKUP Function to Get the Sheet Name List

Note: The same macro-enable formula is used in the first method ( Name : Worksheets ), you can also use that if you want.

⏩ Then insert the formula.

Excel Sheet Name List Using the LOOKUP Function

⏩ If you press Enter and use the FIl Handle tool, you’ll get the following output.

Using the LOOKUP Function to Get the Sheet Name List

5. Creating a Dynamic List of Sheet Names Using SUBSTITUTE Function

Moreover, you may create a dynamic list of sheet names using the SUBSTITUTE function .

⏩ Set the name as Dynamic_List_of_SheetNames and put the below formula.

Creating a Dynamic List of Sheet Name Using SUBSTITUTE Function

⏩ Afterward, insert the following formula.

Creating a Dynamic List of Sheet Name Using SUBSTITUTE Function

6. Generating Sheet Name List Using VBA Code

Now, let’s see how you can apply the VBA code to get the list of sheet names in Excel.

⏩ Open a module by clicking Developer > Visual Basic .

How to Insert VBA Code

⏩ Go to Insert > Module .

How to Insert VBA Code

⏩ Then, copy and paste the following code into your module.

Sheet Name List Using VBA Code

⏩ Finally, run the code and the output will be as follows.

Sheet Name List Using VBA Code

Notes: Be careful about the following facts while using the VBA code.

  • Worksheet name: Here, the worksheet name is Price_Jun .
  • Cells(i, 2) means the cell location of row i (here, i=5 ) and column 2 .

Things to Remember 

I. As the GET.WORKBOOK is a macro-enabled function, save the Excel file in .xlsm format (designated extension format for macro-enabled Excel file).

II. Be careful about the #BLOCKED error which may be found instead of the output if the workbook is unable to update.

Download Practice Workbook

Sheet Name List.xlsm

In the above article, I tried to cover the methods to get the list of sheet names. Obviously, I believe these methods might be beneficial for you. Anyway, if you have any queries or suggestions, please don’t forget to share them in the following comments section.

Related Articles

  • How to Get Excel Sheet Name
  • How to Insert Excel Sheet Name from Cell Value
  • How to Rename Sheet in Excel
  • How to Use Sheet Name Code in Excel
  • How to Apply Sheet Name Code in Footer in Excel
  • How to Search by Sheet Name in Excel Workbook

<< Go Back to Excel Sheet Name  | Excel Worksheets  | Learn Excel

What is ExcelDemy?

Tags: Excel Sheet Name

Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

  • User Reviews
  • List of Services
  • Service Pricing

trustpilot review

  • Create Basic Excel Pivot Tables
  • Excel Formulas and Functions
  • Excel Charts and SmartArt Graphics
  • Advanced Excel Training
  • Data Analysis Excel for Beginners

DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

ExcelDemy

How To Generate A List Of Sheet Names From A Workbook Without VBA

In this post we’ll find out how to get a list of all the sheet names in the current workbook without using VBA.

This can be pretty handy if you have a large workbook with hundreds of sheets and you want to create a table of contents. This method uses the little known and often forgotten Excel 4 macro functions.

These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick we’re going to use one of these in a named function.

step-001-how-to-generate-a-list-of-sheet-names-from-a-workbook-without-vba

In this example, I’ve created a workbook with a lot of sheets. There are 50 sheets in this example so I was lazy and didn’t rename them from the default names.

step-002-how-to-generate-a-list-of-sheet-names-from-a-workbook-without-vba

Now we will create our named function.

  • Go to the Formulas tab.
  • Press the Define Name button.
  • Enter SheetNames into the name field.
  • Enter the following formula into the Refers to field. =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
  • Hit the OK button.

step-003-how-to-generate-a-list-of-sheet-names-from-a-workbook-without-vba

In a sheet within the workbook enter the numbers 1,2,3,etc… into column A starting at row 2 and then in cell B2 enter the following formula and copy and paste it down the column until you have a list of all your sheet names.

As a bonus, we can also create a hyperlink so that if you click on the link it will take you to that sheet. This can be handy for navigating through a spreadsheet with lots of sheets. To do this add this formula into the column C.

Note, to use this method you will need to save the file as a macro enabled workbook (.xls, .xlsm or .xlsb). Not too difficult and no VBA needed.

Video Tutorial

This video will show you two methods to list all the sheet names in a workbook.

  • The first method uses a VBA procedure from this post .
  • The second (skip to 3:15 in the video) uses the method in the above post.

About the Author

John MacDougall

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

  • 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

print worksheet name in excel 2013

Related Posts

37 Awesome Excel Mouse Tips & Tricks You Should Know

37 Awesome Excel Mouse Tips & Tricks You Should Know

Apr 15, 2019

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

25 Amazing Power Query Tips and Tricks

25 Amazing Power Query Tips and Tricks

Jun 11, 2018

Power query is amazing tool that allows you to import and transform data with ease and helps to create repeatable and robust procedures with your data. Here are some tips and tricks to help you get the most out Power Query.

Amazing Excel Tips and Tricks

Amazing Excel Tips and Tricks

May 12, 2018

A compilation of the best Excel tips and tricks ranging from beginner to pro that will save you time and make you more productive.

53 Comments

Kinsen

Hi, this is very helpful, but it would be great if you mention how to do this only for visible sheets?

John

Unfortunately, with this method it’s not possible.

andrew

The hyperlink doesn’t work when there are spaces in the worksheet names

Rajid ALi

Exactly…. any solutions ????

When there is a space you need to add single quotes around the sheet name. Just after the # and just before the !

=HYPERLINK("#'"&B2&"'!A1","Go To Sheet")

Ed

Or you can simply rightClick either the left or right tab-horizontal-scroll arrow at the bottomLeft, and then click the worksheet name (from the simple vertical list) that you want to jump to.

That’s a great tip if you want to navigate between sheets, but won’t help if you want to list out all the sheet names.

Dea Mengers

This is exactly what I was looking for! It’s a major time saver when my client provides a workbook with 80 sheets and wanted the data from each combined into one sheet.

Good to hear it’s of use to you!

Trevor

Very useful and helpful. I have modified the index part though for my use. Others might also find this helpful: You can generate the list of sheet names directly without having to first create an index column by using the ROW() function. I used: =INDEX(SheetNames,ROW()-“row offset”+”sheet offset”) where the “row offset” is the number of rows down the sheet you intend to start the numbering from and “sheet offset” is the number of sheets in from the start of the workbook you intend to start the list from. An example is =INDEX(SheetNames,ROW()-2+2) from what I’ve done. I start my sheet list on row 3 of a summary page and allowed the first two rows for headings etc. , and I skipped the first two sheets in the workbook. In this case ROW()-2+2 will be evaluated to be 3. Maybe have a play with the numbers used in the row and sheet offsets and you will see what i mean.

Thanks Trevor, good tip!

Julie

Thanks! I had to switch the commas by semi-colons, but otherwise it’s fine! 🙂

I believe that’s due to regional version of Excel, maybe French?

Pancho

Pliz, put the result. Can’t get it to work

YEOW

my excel end up with #N/A , please help me !! thanks

When the index is past the number of sheets in the workbook the formula will produce an #N/A error. You can wrap the formula in an IFERROR to avoid this.

daniel styslinger

Very helpful thank you.

Nanda

Thank you so much John. You’re a life saver.

Bob Smiley

Wow. Just wow.

Trygve

My list does not automatically update if the sheet is renamed. Is there a way to get around this?

Use the keyboard shortcut Ctrl + Alt + F9 to recalculate.

Zo

Hello, John,

My indexing and redirecting works fine until I invoke Ctrl + Alt + F9 to recalculate; then it shows #NAME errors!?

Same happens when I add a sheet to the workbook.

Can you, please help with this?

zo

I fixed it by applying the names in the list through the “Define names” function as shown above here.

Simply typing the same that this formula produces didn’t work for me?!

Lisa

Hello, What would the formula be to then retrieve a ‘named cell’ value from one of the worksheets?

GOAL – list all names of sheets, then retrieve the value of a ‘Total’ cell from each sheet. Then I have a summary sheet where I can calculate a grand total of expenses from each worksheet.

Thanks so much!

You could use the INDIRECT function to create a reference to your total on each sheet, by referring to the sheet name and location.

But honestly, it sounds like you should reorganize your data so that you have one proper data set instead of data across many sheets. Then use a pivot table to do any analysis.

Claudius

Thanks a lot that’s awesome! How can I use this to reference cells in the other sheets?

Thanks a lot!

If your sheet name is in A1 and you want to reference a cell on that sheet in C2, use this formula:

=INDIRECT("'"&A1&"'!C2")

Thanks a lot John! Much appreciated!

Thank you!! This worked perfectly for me.

Andrew Herbert

Try this to combine the Hyperlink as part of the name… Also gets rid of any that aren’t used so you can make it a full page and add tabs later if needed.

=IFERROR(HYPERLINK(“#’”&INDEX(SheetName,A3)&”‘!A1″,INDEX(SheetName,A3)),””)

Narender Reddy S

Thank you, It was very helpful.

Bob

I tried this earlier on another sheet and it worked, but today i’m trying it again and i’m getting a “#REF!” error. When I step through the calculations, it appears to resolve the SheetName function to an array, follows by “(A2)”, which resolves to 1. Either I’ve got an elusive type-o or Excel has changed how it does this since the last time I tried. Any suggestions? I’m on office 365 so updates come whenever they feel like it.

Nevermind… I found my elusive Type-o! I had done SheetNames(A2) rather than Index(SheetNames,A2). Doh!

Iain

John – am using a Mac with the latest version of Office. When I try to save the file with your REPLACE formula, I get a message saying that Excel 4.0 formula’s can’t be saved in a macro-free workbook.

Is there a workaround for this?

Try saving as a xlsm file.

Isn’t that a macro enabled file type? Thought the idea of this formula was to stay away from that.

They are a type of old legacy macro from before VBA, so an xls file will also work.

Here’s the modern way of doing this.

Abhay Antala

I used this formula for both Hyperlink and Sheet Name

=HYPERLINK(CONCATENATE(“#”,”‘”,INDEX(SheetNames,P1),”‘”,”!A1″),IFERROR(INDEX(SheetNames,P1),””))

Thanks! Good tip on combining them into one.

jc

Would you share the file with the example? my excel is in spanish and GET.WORKBOOK doesn´t work because of language,

Sorry for the delay, here’s the link .

zain

Thanks John for a valuable post!

sarat

i want all sheets single client name in one sheet, please tell me

SuperSingh

I don’t want the name of the 1st sheet . I want to get the index start with 2nd sheet onwards. How do I do that ?

richard waddington

This is genius!! Thanks vm.

Jon

Will this work in Google Sheets?

I highly doubt it.

Samuel

HI John, It worked well before and suddenly it does not work anymore and has a #Name? error. any idea about the problem?

Not sure. Maybe try deleting everything in the name manager and start over.

Thomas

I’m probably too late to help you, but for others, I had a similar error of a spreadsheet working for years, then suddenly getting a bunch of #NAME? errors. Turned out macros were automatically being disabled, and the GET.WORKBOOK function relies on macros.

bhaskar B Khandke

This is one of the best tip I have seen … Not only it lists the workbooks in one sheet but also provides hyperlink… such a saviour..!!!

John

Works like a charm. Thanks!

Love Rai

Note :- Formula Work only if your Sheet Name in Numeric =”=’E:\Contacts\”&”[“&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”‘!$B$1048576″ Use this Formula To Get Value from other Excel Sheet ________________

Define Path = “=’E:\Contacts\”&”[” Give Sheet Name [if Sheet Name in Numericl] = SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1&”.csv”&”]”&SUM(MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256))-1 Cell No. = !$B$1048576

Get the Latest Microsoft Excel Tips

print worksheet name in excel 2013

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

List sheet names with formula

Related functions .

Excel formula: List sheet names with formula

To list worksheets in an Excel workbook with a formula, you can use a 2-step approach: (1) define a named range called "sheetnames" with an old macro command and (2) use the TEXTAFTER function and the TRANSPOSE function  to retrieve sheet names using the name. In the example shown, the formula in B5 is:

Notes: (1) In older versions of Excel without the TEXTAFTER function you can use a formula based on INDEX. See below for details (2) This article is about using Excel formulas to return a list of sheet names, but I've included a Power Query solution below as well.

Generic formula

Explanation .

In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula. Unfortunately, there is no simple way to do this with a formula in Excel. However, it can be done with a two-step approach:

  • Define a name called "sheetnames" with an old macro command and the Name Manager.
  • Use the defined name in a formula that extracts the names into the workbook

The article below explains how to follow these steps. It includes a newer dynamic array formula that will return all sheets at once and a more traditional formula that will work in older versions of Excel.

Step 1: Define the name

The first step is to define a new name. Navigate to the "Formulas" tab and click the "Define Name" button. In the "Name" field, enter "sheetnames". For the "Refers to" field, enter the formula below:

GET.WORKBOOK is part of a set of commands referred to as "Excel 4.0 macros" or "XLM macros," which were introduced with Excel 4 in the early 1990s. These macros provided basic automation in Excel before the introduction of Visual Basic for Applications (VBA) in Excel 5. This is essentially a hack that still works in later versions of Excel. In Excel 365, you might need to enable Excel 4.0 macros in the Trust Center, as explained below.

In the first part of the formula, GET.WORKBOOK(1), retrieves an array of sheet names in the current workbook. In a generic workbook called "workbook.xlsx" with five sheets, the resulting array looks like this:

Next, we concatenate a strange expression to the result:

I ran into this approach formula many years ago on the MrExcel message board in a post by T. Valko .

The purpose of this cryptic code is to trigger a recalculation when the worksheet changes. Since  NOW is a volatile function , it will recalculate with each workbook change, like, for example, when a sheet is renamed. NOW returns a number indicating time in Excel, and the T function translates the number into an empty string (""). As a result, this part of the formula does not affect the output from GET.WORKBOOK; it is simply used to trigger recalculation.

Notes: (1) Because this step relies on a macro command, you'll need to save the file as a macro-enabled workbook (.xlsm) to allow the formula to update sheet names after the file is closed and re-opened. If you save the file as a normal worksheet (.xlsx), the sheetname code will be removed. (2) In newer versions of Excel, you may encounter a #BLOCKED error even after taking these steps. See below for a fix . 

Step 2: enter a formula to retrieve sheet names

The second step in the process is to enter a formula that will retrieve the individual sheet names from the previously defined "sheetnames". In a modern version of Excel that provides dynamic array formulas and offers the TEXTAFTER function, you can use a formula like this to retrieve all sheet names in one step:

The TEXTAFTER function extracts just the sheet name from the full workbook path. The TRANSPOSE function converts the original horizontal array into a vertical array . The individual sheet names land in cell B5 and spill onto the worksheet. Done.

If you are using an older version of Excel without TEXTAFTER, see the next section.

Legacy formula

If you have an older version of Excel that does not support dynamic arrays, you will need to use a more traditional formula like this:

Working from the inside out, the MID function is configured to extract each sheet name from the array returned by GET.WORKBOOK like this:

The text argument is provided as sheetnames , which is the defined name created above. The value in sheetnames is an array like this:

The start_num argument is determined with the FIND function here:

The FIND function locates the closing square bracket "]" in sheetnames, which appears right after the workbook name. The result is a number that indicates the position of this bracket. We add 1 because we want to start extracting the sheet name after the closing bracket. The value for num_chars is hardcoded as 31 to keep things simple. When num_chars exceeds the length of the text following the start number, MID returns all remaining text . Since sheet names cannot be longer than 31 characters, this ensures that we get the entire sheet name. The result from MID is an array like this:

At this point, we have the sheet names ready to go, we just need to get them onto the worksheet. We do this with the INDEX function. The array is delivered to INDEX as the array argument. The value for row_num is provided by the ROW function like this:

The ROW function uses an expanding reference to generate an incrementing row number — as the formula is copied down, ROWS will return 1, then 2, then 3, etc. This allows INDEX to retrieve the next sheet name from the array at each new row. Copy the formula down until all sheet names are listed. When there are no more sheet names to output, the formula will return a #REF error. 

Note: close observers will notice that we are asking INDEX for specific rows, even though the array is horizontal and has one row only. INDEX is clever this way — it knows what you want :)

Clearing a #BLOCKED error

In newer versions of Excel, you may run into a #BLOCKED error even after saving the file in .xlsm format and enabling VBA Macros. The error looks like this:

#BLOCKED error with XLM macros

The problem occurs because a Trust Center setting called "Enable Excel 4.0 macros when VBA macros are enabled" in Excel 365 is disabled by default:

Enable Excel 4.0 macros in Trust Center

To open the Trust Center, navigate to File > Options > Trust Center, then click the "Trust Center Settings" button. To clear the blocked error, you will need to enable this setting. Check the checkbox and reopen the file. This time, when you are prompted to enable VBA macros the #BLOCKED error should be cleared.

List sheet names with Power Query

If you find the Macro-based solution above complex and underwhelming, there is another way to list sheet names in Excel: Power Query. Power Query is a heavy-duty data transformation tool, so this is a little like using a sledgehammer to crack a nut, but it works well. Below is a summary of the steps to list all Sheet names with Power Query. Don't be discouraged by the number of steps, you can do this in less than 2 minutes:

  • Place your cursor in the cell where you want to list sheet names.
  • Navigate to Data > Get Data > From File > From Excel Workbook.
  • Select the current Excel workbook.
  • Select any sheet name, then click the "Transform Data" button.
  • Under Properties > Name, enter "List sheet names"
  • Under Applied Steps, remove all steps except Source
  • In the Kind column, filter to select "Sheet" only.
  • Right-click the Name column and select "Remove other columns".
  • Click "Close & Load" then select "Close & Load to"
  • Select "Existing worksheet" and click "OK"

The sheet names will be delivered to the cell you selected in step #1 in an Excel table .

One disadvantage to the Power Query solution is that you must manually refresh the query if sheet names change — the list of sheet names won't automatically update. To refresh results, first save the workbook. Then Right-click the table and select "Refresh". One nice benefit of the Power Query approach is that you can easily exclude a sheet from the list. To exclude a specific sheet, click the filter button in the Name column after step #7 above then deselect the sheet(s) you want to exclude.

Notes: Power Query is built-in to Excel 2016 and later on Windows and is available as a free add-in for Excel 2010 and 2013. On the Mac, Power Query is available in Excel 365 but the feature set is more limited. The example above does work in a current version of Excel 365 on a Mac.

Related formulas

Excel formula: Get workbook name only

  • Get workbook name only

Excel formula: Get sheet name only

  • Get sheet name only

Excel formula: Count errors in all sheets

  • Count errors in all sheets

Related functions

Excel TEXTAFTER function

  • TEXTAFTER Function

The Excel TEXTAFTER function returns the text that occurs after a given substring or delimiter. In cases where multiple delimiters appear in the text, TEXTAFTER can return text after the nth occurrence of a delimiter....

Excel TRANSPOSE function

  • TRANSPOSE Function

The Excel TRANSPOSE function "flips" the orientation of a given range or array: TRANSPOSE flips a vertical range to a horizontal range, and flips a horizontal range to a vertical range.

Excel INDEX function

  • INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....

Excel MID function

  • MID Function

The Excel MID function extracts a given number of characters from the middle of a supplied text string based on the provided starting location. For example, =MID("apple",2,3) returns "ppl".

Excel ROW function

  • ROW Function

The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.

Excel NOW function

  • NOW Function

The Excel NOW function returns the current date and time, updated continuously when a worksheet is changed or opened. The NOW function takes no arguments. You can format the value returned by NOW as a date, or as a date with time by applying a number format ...

Excel T function

The Excel T function returns text when given a text value and an empty string ("") for numbers, dates, and the logical values TRUE and FALSE. You can use the T function to remove values that are not text.

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

  • Mr. Excel forum post by T. Valko

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

Help us improve Exceljet

Your email address is private and not shared.

TrumpExcel Logo - Online Excel Tips & Tricks

How to Get the Sheet Name in Excel? Easy Formula

Picture of Sumit Bansal

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:

CELL formula to get workbook name and address

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:

formula to get the sheet name

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:

Formula to append text before the sheet name in Excel

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

Excel Ebook Subscribe

FREE EXCEL BOOK

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

Picture of Sumit Bansal

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

DMCA.com Protection Status

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Free Excel Tips EBook Sumit Bansal

AutomateExcel Logo

Return to Excel Formulas List

Excel Formula – Get Worksheet Name

print worksheet name in excel 2013

Editorial Team

print worksheet name in excel 2013

Reviewed by

Steve Rynearson

Download the example workbook

Excel formula to get the worksheet name. Uses the Excel functions: MID, CELL, and FIND.

Use this Excel formula to get the worksheet name

Get Worksheet Name – Excel Formula

To calculate the worksheet name in one Excel Formula, use the following formula:

Notice in the image above this formula returns sheet names GetWorksheetName and Sheet3.

This code may look intimidating at first, but it’s less confusing if you split it out into separate formulas:

Use 3 Excel Functions to get the work sheet name: CELL Function, FIND Function, MID Function.

Excel Functions – Worksheet Name

The CELL Function:

The Cell Function returns information about a cell . Use the criteria “filename” to return the file location, name, and current sheet.

The FIND Function:

The CELL Function returns [workbook.xlsx]sheet , but we only want the sheet name, so we need to extract it from the result. First though, we need to use the FIND Function to identify the location of the sheet name from the result.

The MID Function

Next, we will extract the desired text using the MID Function with the result of the FIND Function (+1) as the start_num.

Why did choose 999 for the num_characters input in the MID Function?  999 is a large number that will return all remaining characters. You could have chosen any other significantly large number instead.

Get Sheet Name in VBA

If you want to use VBA instead of an Excel Formula, you have many options.  This is just one example:

Retrieve the current worksheet name using VBA. Enter the current worksheet name in cell A1.

Enter the current worksheet name in cell A1 using VBA.

Return to Formula Examples

AI Formula Generator

print worksheet name in excel 2013

Try for Free

  • Excel Help Bot
  • Formula Generator
  • Formula Explainer
  • VBA Code Generator
  • VBA Code Commenter
  • Excel Template Generator

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

print worksheet name in excel 2013

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.

print worksheet name in excel 2013

Microsoft 365 subscription benefits

print worksheet name in excel 2013

Microsoft 365 training

print worksheet name in excel 2013

Microsoft security

print worksheet name in excel 2013

Accessibility center

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

print worksheet name in excel 2013

Ask the Microsoft Community

print worksheet name in excel 2013

Microsoft Tech Community

print worksheet name in excel 2013

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

tips.net

  •  >  Excel Tips
  •  >  Printing

print worksheet name in excel 2013

Printing a List of Named Ranges

print worksheet name in excel 2013

Written by Allen Wyatt (last updated March 4, 2023) This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021

Anyone who has created a large workbook knows that it is very easy to create a large list of named ranges. Managing those ranges, particularly if you inherit the workbook from someone else, is a much harder task. Part of the problem is that you may lose track of all your ranges and what they refer to.

Having a list of the names in your workbook could be helpful. To get a list, follow these steps:

  • Select the cell where you want the list to start. Since a name list can occupy a good deal of space, you may want to select a cell in a blank worksheet.
  • Display the Formulas tab of the ribbon.
  • In the Defined Names group, click the Use In Formula tool. (This tool is not available if there are no named ranges in your workbook.) Excel displays a list of options.
  • Choose Paste Names from the submenu. Excel displays the Paste Name dialog box.
  • Click on Paste List. The two-column list of names and their ranges is inserted.

There's also a shortcut you can use to accomplish these steps: Simply press F3 , and Excel displays the Paste Name dialog box. (If you press F3 and nothing happens, it means that the workbook has no named ranges defined within it.) In other words, pressing F3 accomplishes steps 1-4. You can then perform step 5 to get your list of named ranges.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7731) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Printing a List of Named Ranges .

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates , a computer and publishing services company. Learn more about Allen ...

MORE FROM ALLEN

Turning Off Automatic Numbered Lists

Type what Word thinks is a numbered list, and it will helpfully format the text to match what it thinks your numbered ...

Editing the Same Cell in Multiple Sheets

When creating a workbook, you may need to make changes on one worksheet and have those edits appear on the same cells in ...

Automatically Copying Formatting

It's easy to automatically set the contents of one cell to be equal to another cell. But what if you want to copy the ...

print worksheet name in excel 2013

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Conditional Printing

If you need to make what Excel prints be based upon conditions in a worksheet, you'll love the information in this tip. ...

Specifying Print Quantity in a Cell

When you print a worksheet, you can specify in the Print dialog box how many copies you want printed. If you want the ...

Printing Row Numbers

Excel displays row numbers on-screen that help you easily see what is in each row. If you want to print these row ...

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

(Your e-mail address is not shared with anyone, ever.)

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

(gif, jpeg or png only, 5MB maximum file size)

Notify me about new comments ONLY FOR THIS TIP

Notify me about new comments ANYWHERE ON THIS SITE

Hide my email address

What is 3 + 8?

2023-03-06 18:33:01

A couple notes from testing this (in 2016 version): If you open a blank worksheet to paste into, then this tip will give you a list of Global named ranges. If you do it within a sheet with data, then it will give you a list of named ranges specific to that sheet. This would be really cool if it would produce a list of all named ranges in the workbook, regardless of scope.

2023-03-04 10:14:10

My Excel Toolbox includes the following dynamic array function to list defined names (named ranges) with workbook, worksheet, or any scope, including names that are normally hidden: =ListNames([Scope],[SkipHidden],[SkipHeader]) The list includes the following columns: Scope, Name, Visible, Refers To, Value, Comment. When using pre-2021 versions of Excel without support for dynamic arrays, consider UseSpillArray.pdf. My Excel Toolbox's NamesInFormulas macro lists hyperlinks to formula cells referencing each visible defined name (named range) in the active workbook. (Hidden names are ignored.) The cell's formula will be included in a comment attached to its hyperlink. The following details are also provided for each name: Scope, Name, Refers To, Value, and Comment. Results are recorded in the active workbook's 'NamesIn...' worksheet. See https://sites.google.com/view/MyExcelToolbox

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface .

Newest Tips

View the most recent newsletter.

Links and Sharing

  • ExcelTips FAQ
  • Questions or Comments
  • Great Branding Ideas
  • Free Calendars

OUR COMPANY

  • Sharon Parq Associates, Inc.
  • About Tips.Net
  • Advertise with Us
  • Our Privacy Policy
  • Author Index

OUR PRODUCTS

  • Helpful E-books
  • Newsletter Archives
  • Video Courses
  • Excel Products
  • Windows Products
  • Word Products
  • Beauty and Style
  • ExcelTips (Excel 97–2003)
  • ExcelTips (Excel 2007–2021; 365)
  • Home Improvement
  • Money and Finances
  • Pests and Bugs
  • Pets and Animals
  • WindowsTips (Microsoft Windows)
  • WordTips (Word 97–2003)
  • WordTips (Word 2007–2021; 365)

Copyright © 2024 Sharon Parq Associates, Inc.

How to Print Excel Spreadsheet: Step-by-Step Guide for Perfect Prints

Printing an Excel spreadsheet might seem tricky, but it’s actually pretty simple once you get the hang of it. By following a few straightforward steps, you can get your spreadsheet looking just right on paper. You’ll want to adjust settings like page orientation and margins to ensure everything fits well. Here’s a step-by-step guide that will help you through the process.

How to Print Excel Spreadsheet

By following these steps, you’ll be able to print an Excel spreadsheet with all the necessary settings adjusted for a perfect printout.

Step 1: Open the Excel Spreadsheet You Want to Print

First, open the Excel file that contains the spreadsheet you need to print.

Make sure you have the correct workbook and worksheet selected. This is important because you don’t want to print the wrong data.

Step 2: Go to the ‘File’ Menu

Next, click on the ‘File’ menu located at the top-left corner of the screen.

This will bring up a menu with various options. Here, you’ll find all the settings you need for printing.

Step 3: Select ‘Print’ from the Menu

Then, choose the ‘Print’ option from the ‘File’ menu.

Selecting ‘Print’ will open the print preview window. This is where you can see how your spreadsheet will look on paper.

Step 4: Adjust Print Settings

After that, configure your print settings. You can set the number of copies, page orientation, and more.

Adjust these settings to match your preferences. For example, if your spreadsheet is wide, consider switching to landscape orientation.

Step 5: Click ‘Print’

Finally, click the ‘Print’ button to send your spreadsheet to the printer.

Make sure your printer is connected and has enough paper. Also, double-check that you’ve selected the correct printer from the list.

Once you click ‘Print,’ your spreadsheet will be printed according to the settings you specified.

Tips for Printing Excel Spreadsheet

Check Print Area : Before printing, set the print area to ensure only the data you need gets printed. You can find this option under the ‘Page Layout’ tab.

Fit to One Page : Use the ‘Fit to One Page’ option to make sure your entire spreadsheet fits on a single sheet of paper. This is particularly useful for large spreadsheets.

Print Titles : If your spreadsheet spans multiple pages, print titles on each page. This way, column headers will appear at the top of every printed page.

Margins and Scaling : Adjust the margins and scaling options to better fit your data onto the paper. You can find these settings in the print preview window.

Print Preview : Always check the print preview before printing. This helps you catch any formatting issues that might need fixing.

Frequently Asked Questions

How do i set the print area in excel.

To set the print area, select the cells you want to print, go to the ‘Page Layout’ tab, and click ‘Print Area’ followed by ‘Set Print Area.’

Why does my Excel spreadsheet print on multiple pages?

Your spreadsheet prints on multiple pages if it doesn’t fit within the default page size. Use the ‘Fit to One Page’ option to adjust this.

How can I print gridlines in Excel?

Go to the ‘Page Layout’ tab, under ‘Gridlines,’ check the ‘Print’ option to include gridlines in your printed spreadsheet.

How do I print a specific worksheet in an Excel workbook?

Select the worksheet you want to print, and then follow the same steps for printing. Only the active worksheet will be printed.

Can I save my print settings for future use?

Yes, Excel allows you to save your print settings as part of the workbook. Simply save the workbook after configuring your print settings.

  • Open the Excel spreadsheet you want to print.
  • Go to the ‘File’ menu.
  • Select ‘Print’ from the menu.
  • Adjust print settings.
  • Click ‘Print’.

Printing an Excel spreadsheet doesn’t have to be a daunting task. By following these straightforward steps, you can ensure that your data looks exactly the way you want it on paper. Remember to set your print area and check the print preview to avoid any surprises. Utilize the tips provided to fine-tune your printout and handle common issues like scaling and gridlines.

So, the next time you need to print a spreadsheet, just follow this guide, and you’ll be good to go. Whether you’re printing for a meeting or just need a hard copy for your records, mastering these steps will save you both time and frustration.

If you found this article helpful, feel free to check out other tutorials on working with Excel. Happy printing!

Matt Jacobs Support Your Tech

Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.

His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)

Related Posts

  • How to Print an Excel Spreadsheet on A4 Paper: A Step-by-Step Guide
  • How to Print Empty Gridlines in Excel (An Easy 5 Step Guide)
  • How to Print Each Worksheet of an Excel 2013 Workbook on One Page
  • How to Print on Legal Paper in Excel 2010: A Step-by-Step Guide
  • What is the Difference Between a Workbook and Worksheet in Excel?
  • Excel 2013 – Set Print Area: A Step-by-Step Guide
  • How to Print All Columns in Excel 2010 on One Page: A Step-by-Step Guide
  • How to View the Print Area in Excel 2010: A Step-by-Step Guide
  • How to Clear the Print Area in Excel 2010: A Step-by-Step Guide
  • How to Add a New Worksheet in Excel 2010: A Step-by-Step Guide
  • How to Print Excel with Lines: A Step-by-Step Guide
  • How to Fit All Columns on One Page in Excel 2013 (A Simple 6 Step Guide)
  • How to Center Worksheet Horizontally and Vertically in Excel Guide
  • Why Isn’t My Entire Spreadsheet Printing in Excel 2010? Solutions
  • How to Use Print View in Excel 2013: A Step-by-Step Guide
  • How to Put the Worksheet Name in Excel’s Footer: A Step-by-Step Guide
  • How to Change Paper Size in Google Docs Quickly [5 Steps + Infographic]
  • How to Print More Than One Worksheet on a Page in Excel: A Step-by-Step Guide
  • How to Make Page Layout the Default View in Excel 2010: A Step-by-Step Guide
  • How to Make a Blank Excel Sheet Printable: Step-by-Step Guide

Get Our Free Newsletter

How-to guides and tech deals

You may opt out at any time. Read our Privacy Policy

print worksheet name in excel 2013

#1 Excel tutorial on the net

Print a Worksheet | What to Print | Multiple Copies | Orientation | Page Margins | Scaling

This chapter teaches you how to print a worksheet and how to change some important print settings in Excel.

Print a Worksheet

To print a worksheet in Excel , execute the following steps.

1. On the File tab, click Print.

2. To preview the other pages that will be printed, click 'Next Page' or 'Previous Page' at the bottom of the window.

Excel Print Preview

3. To print the worksheet, click the big Print button.

Click the big Print Button

What to Print

Instead of printing the entire worksheet, you can also print the current selection.

1. First, select the range of cells you want to print.

2. Next, under Settings, select Print Selection.

Print Selection

3. To print the selection, click the big Print button.

Note: you can also print the active sheets (first select the sheets by holding down CTRL and clicking the sheet tabs) or print the entire workbook. Use the boxes next to Pages (see first screenshot) to only print a few pages of your document. For example, 2 to 2 only prints the second page.

Multiple Copies

To print multiple copies, execute the following steps.

1. Use the arrows next to the Copies box.

2. If one copy contains multiple pages, you can switch between Collated and Uncollated. For example, if you print 6 copies, Collated prints the entire first copy, then the entire second copy, etc. Uncollated prints 6 copies of page 1, 6 copies of page 2, etc.

Multiple Copies

Orientation

You can switch between Portrait Orientation (more rows but fewer columns) and Landscape Orientation (more columns but fewer rows).

Landscape Orientation

Page Margins

To adjust the page margins, execute the following steps.

1. Select one of the predefined margins (Normal, Wide or Narrow) from the Margins drop-down list.

2. Or click the 'Show Margins' icon at the bottom right of the window. Now you can drag the lines to manually change the page margins.

Adjust Page Margins

If you want to fit more data on one page, you can fit the sheet on one page. To achieve this, execute the following steps.

1. Select 'Fit Sheet on One Page' from the Scaling drop-down list.

Fit Sheet on One Page

Note: you can also shrink the printout to one page wide or one page high. Click Custom Scaling Options to manually enter a scaling percentage or to fit the printout to a specific number of pages wide and tall. Be careful, Excel doesn't warn you when your printout becomes unreadable.

1/9 Completed! Learn much more about printing > Go to Next Chapter: Share

Learn more, it's easy

  • Workbook Views
  • Page Breaks
  • Headers and Footers
  • Page Numbers
  • Print Titles
  • Center on Page
  • Print Gridlines & Headings

Next Chapter

Follow excel easy.

Excel Easy on Facebook

Become an Excel Pro

  • 300 Examples

Print • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA

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

How to Enter Different Names in cell when printing

  • Thread starter Chatfim
  • Start date Apr 6, 2012
  • Apr 6, 2012

I have a Excel spreadsheet I use as a time sheet for each of my employees. I change the time sheet each month and print it for each of my employees with their name on their time sheet in certain cell. ie I have 35 employees and have to print 35 seperate time sheets with their name in a certain cell in the spreadsheet. How can I print each my employees names in a cell without doing it manually? It takes a long time to enter enter each name in the cell and then print it. There has to be an eaiser way. Thanks Mark  

Excel Facts

Well-known member.

Here are a couple alternatives: 1. Easiest Way, but Name will be at bottom of time sheet : Assume your timesheet is in cells A1 thru K30 - so in the print page setup, make "Rows to repeat at top" to be $1:$30 and "Columns to repeat at top" to be $A:$K. Enter all 35 employee names in A31 thru A65. Then "Insert a page break in A31, A32, A33... thru A65. Your print range would be A1:K65 and a separate sheet will automatically print for each employee. The only "disadvantage" is possibly an aesthetic one where the employee's name is printed at the bottom of the sheet instead of the top - for me I would not have a problem with this, but I understand why people may prefer the more conventional "Name at the Top of the Page" format. 2. Use Data Validation in the cell for the employees name : Create a list of employees' names elsewhere in the worksheet. Set up data validation on the Employee Name cell in the time sheet referencing this list. Then to print, click on the 1st person's name in the drop down and print, then the 2nd person and print and so forth for all employees. Saves having to type in names each time and the timecard is kept in its original format, but it's not as efficient and the 1st approach I suggested. In case you're not familiar with Data Validation, here's a link describing it: http://www.contextures.com/xldataval01.html  

Board Regular

A simple approach might be to set up all employee names in column A in a seperate worksheet (in my example I created "Employees"). Then, assuming the Name cell is D1, you can try the following code, which will print all employees at once. Code: Sub Macro1() Dim lastRow As Integer ' Dim r As Integer lastRow = Sheets("Employees").Cells(Rows.Count, "A").End(xlUp).Row For r = 1 To lastRow Sheets("Sheet1").Range("D1").Value = Sheets("Employees").Range("A" & r).Value ActiveWindow.SelectedSheets.PrintOut Next r End Sub  

  • Apr 9, 2012
jeffmb said: A simple approach might be to set up all employee names in column A in a seperate worksheet (in my example I created "Employees"). Then, assuming the Name cell is D1, you can try the following code, which will print all employees at once. Code: Sub Macro1() Dim lastRow As Integer ' Dim r As Integer [INDENT]lastRow = Sheets("Employees").Cells(Rows.Count, "A").End(xlUp).Row For r = 1 To lastRow [INDENT]Sheets("Sheet1").Range("D1").Value = Sheets("Employees").Range("A" & r).Value ActiveWindow.SelectedSheets.PrintOut [/INDENT]Next r [/INDENT]End Sub Click to expand...
  • Click Insert on the Developer tab on the ribbon.
  • Click on the command button icon under Forms Control
  • Draw your command button on your worksheet
  • In the dialog box that will appear Enter a descriptive macro name
  • Copy and paste the code above into the Sub in Visual Basic
  • Change the command button label text (right click on button, if necessary
  • Click outside the comand box to exit edit mode for the button
  • Mar 30, 2022
jeffmb said: 1. Enter the Visual Basic Editor by entering Alt F11. 2. Right click ThisWorkbook in the left pane (if you have more than 1 workbook open, make sure you chose the one under the workbook name you want to update). 3. Click Insert >> Module 4. Copy and paste the code. Be sure to modify the code for the proper sheet names and cells you are using. You can also create a command button and enter the code directly from your worksheet. The instructions for the command button are: If you are using Office 2010 (I believe 2007 also) make sure you have the Developer tab on your ribbon. If you do not, go to Excel Options, select Popular in the left column, and check Show Developer tab in the Ribbon checkbox. Exit Excel Options. Click Insert on the Developer tab on the ribbon. Click on the command button icon under Forms Control Draw your command button on your worksheet In the dialog box that will appear Enter a descriptive macro name Click New Copy and paste the code above into the Sub in Visual Basic Change the command button label text (right click on button, if necessary Click outside the comand box to exit edit mode for the button You are ready to execute the macro via the command button <!-- / message --> Click to expand...

print worksheet name in excel 2013

MrExcel MVP, Moderator

Try copying the code from post#3 again. I have removed the old formatting.  

  • Mar 31, 2022

print worksheet name in excel 2013

Similar threads

  • Mar 8, 2024

jolivanes

  • Jun 4, 2024

mumps

  • Jun 12, 2024
  • Jun 20, 2024
  • Macdougall91
  • Mar 16, 2024

Peter_SSs

Forum statistics

Share this page.

print worksheet name in excel 2013

We've detected that you are using an adblocker.

Which adblocker are you using.

AdBlock

Disable AdBlock

print worksheet name in excel 2013

Disable AdBlock Plus

print worksheet name in excel 2013

Disable uBlock Origin

print worksheet name in excel 2013

Disable uBlock

print worksheet name in excel 2013

IMAGES

  1. How To Get Worksheet Name In Excel

    print worksheet name in excel 2013

  2. How To Use Sheet Name In Excel Formula

    print worksheet name in excel 2013

  3. Can I Name a Worksheet Something Other Than Sheet1, Sheet2, etc in

    print worksheet name in excel 2013

  4. How To Print Sheet Name Or A List Of Sheet Names In Excel Images

    print worksheet name in excel 2013

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

    print worksheet name in excel 2013

  6. How to Reference Worksheet Name in Formula in Excel

    print worksheet name in excel 2013

VIDEO

  1. How to Rename Worksheet in Microsoft Excel

  2. How to Print File Name Path and Sheet Name in Excel

  3. Get Excel Worksheet Name (the easiest way)

  4. How to change worksheet name in Excel #Shorts #excel #exceltamil

  5. Excel print: Printing Excel workbooks

  6. All About Worksheets in MS Excel

COMMENTS

  1. Quick Tip: Add worksheet name to an excel printout

    Put the cursor in the Left, Center or Right sections and and select the icon that looks like a spreadsheet with multiple tabs at the bottom. This will put the formula & [Tab] into the space. Click OK. Print preview the file to ensure that the worksheet name appears where you would like it. If you need to adjust your margins so that there is ...

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

    Method #1: Using TEXTAFTER and CELL Functions. Method #2: Use a Formula Combining MID, CELL, and FIND Functions. Method #3: Using RIGHT, CELL, LEN, and FIND Functions. Appending Text to the Worksheet Name. How to List All Worksheet Names in a Workbook Using a Formula.

  3. How to print sheet name or a list of sheet names in Excel?

    1. In Excel 2010 and 2013, click File > Print > Print Setup. See screenshot: In Excel 2007, please click the Office button > Print > Print Preview. And then click Page Setup under the Print Preview tab. 2. In the Page Setup dialog box, go to the Header/Footer tab. If you want to insert the sheet name to header, please click the Custom Header ...

  4. 6 Ways to Find Sheet Names in Microsoft Excel

    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.

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

    Next, add the following formula to the target cell, where you want the sheet names to begin. =INDEX(SheetNames,B3) Use the named function in the INDEX formula to get all the sheet names, minus the prefix of the file name. When typing out the formula, the named function will appear in the Formula AutoComplete:

  6. 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: 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 ...

  7. How to List Sheet Name in Excel (5 Methods + VBA)

    Steps: ⏩ Click on the Formulas tab > Select the Name Manager option from the Defined Names ribbon. ⏩ Then you'll see a dialog box named Name Manager. ⏩ Press on the New option. ⏩ Insert the Name (here the name is Worksheets) and insert the below formula in the Refers to section. =GET.WORKBOOK(1)&T(NOW())

  8. How To Generate A List Of Sheet Names From A Workbook ...

    Press the Define Name button. Enter SheetNames into the name field. Enter the following formula into the Refers to field. =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") Hit the OK button. In a sheet within the workbook enter the numbers 1,2,3,etc… into column A starting at row 2 and then in cell B2 enter the following formula and ...

  9. List sheet names with formula

    Navigate to Data > Get Data > From File > From Excel Workbook. Select the current Excel workbook. Select any sheet name, then click the "Transform Data" button. Under Properties > Name, enter "List sheet names". Under Applied Steps, remove all steps except Source. In the Kind column, filter to select "Sheet" only.

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

    Below is the formula that will give you only the sheet name when you use it in any cell in that sheet: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) 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.

  11. List Sheet Names with Formula

    Create Name Range for Sheet Names. To create a Named Range for the sheet names, in the Excel Ribbon: Formulas > Name Manager > New. Type "Worksheets" in the Name Box: In the "Refers to" section of the dialog box, we will need to write the formula. =GET.WORKBOOK(1) & T(NOW())" This formula stores the names of all sheets (as an array in ...

  12. Get Sheet Name in Excel (In Simple Steps)

    1. The CELL function below returns the complete path, workbook name and current worksheet name. Note: instead of using A1, you can refer to any cell on the first worksheet to get the name of this worksheet. 2. Use the FIND function to find the position of the right bracket. Add 1 to return the start position of the sheet name.

  13. Excel Formula

    The FIND Function: The CELL Function returns [workbook.xlsx]sheet , but we only want the sheet name, so we need to extract it from the result. First though, we need to use the FIND Function to identify the location of the sheet name from the result. =find("]",E5)

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

    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

  15. Printing a List of Named Ranges (Microsoft Excel)

    The two-column list of names and their ranges is inserted. There's also a shortcut you can use to accomplish these steps: Simply press F3, and Excel displays the Paste Name dialog box. (If you press F3 and nothing happens, it means that the workbook has no named ranges defined within it.) In other words, pressing F3 accomplishes steps 1-4.

  16. How to Print Excel Spreadsheet: Step-by-Step Guide for Perfect Prints

    Step 5: Click 'Print'. Finally, click the 'Print' button to send your spreadsheet to the printer. Make sure your printer is connected and has enough paper. Also, double-check that you've selected the correct printer from the list. Once you click 'Print,' your spreadsheet will be printed according to the settings you specified.

  17. how can i print file name by default for all excel sheets?

    Click on print setup --> Header/Footer --> Custom Footer --> Click file name. Aug 02 2019 02:54 AM. You can also make an excel template and define file name in the footer. Next time when you make new file open that template and save as with any desired name , it will automatically show new file name. Thanks.

  18. How do you print a list of Worksheet Names?

    You'll need some VBA code... • ALT+F11.....to open the Visual Basic Editor. • Right-click on your workbook name (listed in the "Project-VBA Project" window) • Select: Insert Module. • Copy the below code and paste it into that module. Sub ListWkshtsOfWkbk () Dim wks As Worksheet. Dim rngStart As Range. Dim intCtr As Integer.

  19. How to Print Sheets in Excel (In Easy Steps)

    2. Next, under Settings, select Print Selection. 3. To print the selection, click the big Print button. Note: you can also print the active sheets (first select the sheets by holding down CTRL and clicking the sheet tabs) or print the entire workbook. Use the boxes next to Pages (see first screenshot) to only print a few pages of your document.

  20. How can I print a list of excel names?

    If you are referring to a list of names you have in a worksheet then Select File then Select and/or Edit any of the prompts on the resulting dialog and then click the Print button at the top. Regards, OssieMac. ER. EricWix. Replied on November 27, 2021. Report abuse. In reply to OssieMac's post on November 27, 2021. Thanks for your quick response.

  21. Include The Sheet Name In Printing A Worksheet And Print A Workbook

    In this demo, we will learn how to include the sheet name in printing a worksheet and print a workbook in Excel 2007.

  22. print name of sheet in excel

    Hi ajsnagpal, I am Vijay, an Independent Advisor. I am here to work with you on this problem. Page Layout tab > Click downward pointing arrow in Page Setup group to open Page Set up > Header / Footer tab > Under Header, you can choose Sheet Name. Do let me know if you require any further help on this.

  23. How to Enter Different Names in cell when printing

    Create a list of employees' names elsewhere in the worksheet. Set up data validation on the Employee Name cell in the time sheet referencing this list. Then to print, click on the 1st person's name in the drop down and print, then the 2nd person and print and so forth for all employees. Saves having to type in names each time and the timecard ...

  24. Excel Named Range includes Worksheet name, even when worksheet

    You can create names in the Name Manager using notation like. rng : =!A1:A20 Only an exclamation mark before the address! worksheet function - Excel 2010: Any non-volatile solution to create named range without a sheet reference in the formula - Super User