How many characters can a worksheet name have?

Quicklinks

Download

• Community Hub
• Blog
• Forum
• Educators
• Events
• Solutions
• Careers
• Contact

Main navigation

• Software
• Pricing
• Community
• Learning
• Partners
• About

• Software
• Solutions
• Learning
• Partners
• Community
• About
• Download
• Search

Hi,

I have a question on sheet name been truncated in ODS EXCEL output. I saw the ODS Sheet_name has a limitation of 28 characters but Excel sheet name allow more. For example, if I use proc export, the sheet name will output fine. But because the percentage format is not kept in proc export, I had to rely on ODS EXCEL. 

Is there a way to get around it, meaning keeping the full sheet name? 

Here is my simple code: 

ods excel file=&outputexcel; 
ods excel options(sheet_label = "XXXXXXXXX Tracking Data Summary"); run; 
proc report data=mydata;
run;
ods excel close;

 and the output Excel Tab will be "XXXXXXXXX Tracking Data Summ"

Thanks in advance!!

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010

Excel workbooks contain individual worksheets and the number of worksheets in a workbook is limited only by the system memory of your computer. This article is all about the names you can give to a worksheet.

Naming Syntax

By default, a new workbook contains 3 worksheets and they are named; Sheet1, Sheet2, Sheet3. You can add new worksheets and clone existing ones and all the worksheets can be renamed. There are however a few rules when naming worksheets.

1. The name must be unique within a single workbook.
2. A worksheet name cannot exceed 31 characters.
3. You can use all alphanumeric characters but not the following special characters:
   \ , / , * , ? , : , [ , ].
4. You can use spaces, underscores (_) and periods (.) in the name as word separators.

However, just because Excel lets you use certain characters, I would strongly recommend against using most special characters including spaces. Why? Because, when you use characters such as Spaces, Dollars ($), Exclamation Marks (!) etc, Excel will wrap the worksheet name in single quotes. E.g.

=’Hello World’!A5

Many people use spaces as a word separator in Excel but a Space will also cause Excel to add the single quotes. Compare the worksheet name above with the following worksheet name:

=Hello_World!A5

It is also possible to use a space as the first or last character of a worksheet name. This is dangerous as users do not expect it and often can’t see it. For this reason, I recommend avoiding all spaces in when naming worksheets. Furthermore, as other special characters also cause Excel to add the single quotes, it is best that these are also avoided and then, if you ever see single quotes in formulas referring to worksheets, then you will immediately see it as a warning sign.

Why are the quotes a problem? Well, it makes it hard to type the names of worksheets in Excel, they make the formulas longer and harder to read and lend themselves to errors. It is cleaner and more reliable to avoid them.

Numbers. It is alright to use numbers in the name of a worksheet as long as the name doesn’t start or end with a number as this will also cause Excel to add the single quotes. For example:

Note: there are no quote marks in the last formula listed above.There are exceptions to this illustrated by the example below where the name actually ends with a number ans still has no quote marks.

BalanceSheet2011 → =BalanceSheet2011!A5

This is caused because of the way Excel validates names. For some reason, with certain characters such as numbers, after a certain number of characters, it stops checking to see if it needs to add single quotes. This is a bizarre phenomenon which may not continue in future versions of Excel. Hence it is best to stick to the rules to avoid problems in the future.

In summary, it is safe to use all alphanumeric characters and underscores (_) or periods (.) as word separators.

Use of Capitals

I recommend that all worksheets are named with at least one capital letter. This is because Excel does not differentiate between lowercase and uppercase but will convert the characters of any name you type in a formula to match the case that the actual worksheet name uses. The following table will illustrate this.

The benefit is this: when you type a name in lowercase, if Excel recognises it, the name will be automatically changed to match the capitalisation used by the worksheet. This helps to validate the name as you type. If you misspell it or type in a name that does not exist, Excel will not match it and the letters will not be capitalised.

Word Separators

This warrants some further comments which are mirrored in my post Defined Names: Naming Conventions (Part 1).

When creating descriptive names that contain more than one word, a separator should be used. For reasons given above, Spaces should be avoided.

1. First letter capitalisation: Join the words together but Capitalise the first letter of each word e.g. BalanceSheet. This is clean and easy to read and is my preferred method. It does not add unnecessary extra length and is clear to read.
2. Use of an underscore (_): Separating words with an underscore works well as shown by these examples: balance_sheet, Balance_Sheet or BALANCE_SHEET. However, the underscore does add unnecessary length to the name and makes it hard to differentiate the main part of the name from the Prefix (see section below).
3. Use of a period (.): Separating words with a period is clean as shown by these examples: total.revenue, Total.Revenue or TOTAL.REVENUE. However, periods are used in many places in Excel especially in numbers, web addresses and general text so I prefer to avoid them in Worksheet names.

Prefixes

If I have more than around 9 worksheets in my workbook, I tend to use a grouping code in the worksheet name. E.g. I may have a workbook that is a company financial model. It will have an Income Statement, Balance Sheet, Cash Flow and Summary, but it will probably have a bunch of other sheets that have been used to generate the data used in these financial sheets. In this case, I will probably prefix the names of the four financial worksheets with something like FS ie.

FS_IncomeStatement
FS_BalanceSheet
FS_CashFlow
FS_Summary

Other sets of worksheets would have their own grouping codes so, for consistency, all worksheets would be prefixed by a two letter code followed by an underscore.

Note: Named Ranges scoped to the workbook will take the prefix from the worksheet that they reference so when using a grouping code for the worksheet, the workbook name would look something like this:

AA_FS_INS_TotalRevenue

This will result in a fully descriptive Named Range. Full details are given in my post Defined Names: Naming Conventions (Part 1).

Can sheet tab names exceed 15 characters?

How many characters can an Excel worksheet be?

What is the maximum number of worksheets?

How is a worksheet named?