Tips, Tricks, and Answers
The following articles are available for the 'Worksheets' topic. Click the article''s title (shown in bold) to see the associated article.
Alphabetizing Worksheet Tabs
As you get more and more worksheets into a workbook, you'll find yourself moving them around into different sequences. You may even want to put the worksheets into alphabetic order. This tip provides two macros that can handle sorting for you.
Changing the Color of Worksheet Gridlines
Want the gridlines in your worksheet to be a different color? You aren't limited to stodgy black; Excel lets you make them just about any color you want. Here's how to make the change.
Changing the Height of Worksheet Tabs
Do you need your worksheet tabs to be taller than what they are? You can't make the adjustment in Excel, but you can make it in Windows itself.
Colors and Fonts for Worksheet Tabs
Changing the color used on a worksheet tab is easy. Just follow the three steps in this tip.
Comparing Formulas on Two Worksheets
As you develop worksheets, it is not unusual to end up with two that are essentially the same. At some point you may want to compare those two to determine if they really are the same. This tip explores several ways you can approach comparing two worksheets, cell by cell, to ensure that the formulas on each of them are identical.
Condensing Multiple Worksheets Into One
Want a quick way to combine your worksheets? This tip features a simple macro to do the task for you.
Copying a Worksheet
Need to make a copy of one of your worksheets? Excel provides a few different ways you can accomplish the task.
Creating a Copy without Formulas
Excel makes copying worksheets (duplicating them) rather easy. However, you may want a worksheet copy that differs from the original in some way, such as it contains only the results of formulas. Here's how you can get what you want.
Creating a Worksheet Copy by Default
Excel makes it relatively easy to copy worksheets to a different workbook. That doesn't mean it couldn't be made simpler through the use of a macro. The key is to identify the steps you want to take and then start with those.
Creating Worksheets from a List of Names
Need to create a large number of worksheets using specific names? If so, you'll love the ideas presented in this tip.
Detecting Types of Sheets in VBA
When processing workbook information in a macro, you may need to step through each worksheet to make some sort of changes. It is helpful to know what type of worksheets you are working with so that you don't try to make changes on an inappropriate worksheet.
Determining a Worksheet's Number
When you add a new worksheet to a workbook, it receives a meaningful name such as "Sheet4" or "Sheet17." If you want to determine a worksheet's number—even after you change the worksheet's name—here's how to do it.
Determining the Complexity of a Worksheet
If you have multiple worksheets that each provide different ways to arrive at the same results, you may be wondering how you can determine which worksheet is the most complex approach. Finding an objective measure of complexity can be tricky and elusive, for reasons detailed in this tip.
Disabling Moving Between Worksheets
If you want someone to not be able to move from one worksheet to another in a workbook, you've got your work cut out for you. Here are some things you can try to disable this form of navigation.
Dynamic Worksheet Tab Names
Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but it the task is rather easy as you discover in this tip.
Dynamically Changing Worksheet Tab Color
Excel makes it easy to change the color of a worksheet's tab. If you want that color change to be dynamic, one way to do it is to use a macro that examines the contents of a cell and modifies the color based on what is there.
Finding a Worksheet with a Specific Value in a Specific Cell
If you have a lot of worksheets in workbook, finding the exact one you want can be a bit tricky. This tip looks at various ways you can locate one worksheet out of many based on what is stored in a particular cell of each worksheet.
Finding the Size of Individual Worksheets
Your workbooks can contain many, many worksheets. Which of those worksheets are the largest, however? Here’s some ideas on how to find out.
Freezing Top Rows and Bottom Rows
Freezing the top rows in a worksheet so that they are always visible is easy to do. Freezing the bottom rows is not so easy. Here's a workaround you can use.
Freezing Worksheet Tabs
If you have a lot of worksheets in a workbook, you may wonder if you can "freeze" the position of some of those worksheet tabs. Here's some different ways to tackle the problem.
Getting the Name of the Worksheet Into a Cell
Excel allows you to change the names assigned to the worksheets in a workbook. If you want to have those names appear in a cell on the worksheet, there are a couple of ways you can approach the problem. Here's an assortment of techniques.
Hiding and Unhiding Worksheets
Worksheets are easily accessible in a workbook, but you may not want them to be so open. You can hide worksheets so they aren't immediately visible, and then make them unhidden only when you need to work on them.
Identifying the Last Cell Changed in a Worksheet
When someone changes a cell in a worksheet, Excel normally goes along its merry way of keeping everything up to date. It does not, however, keep track of where that last change occurred. Here's some ideas on how you can determine the location of the last change by the user.
Jumping to a Specific Worksheet
Want to make fast work of moving from one worksheet to another? Here's how to do the task when you have a lot of worksheets in your workbook.
Jumping to Alphabetic Worksheets
Got a workbook with a lot of worksheets in it? Here are some handy ways to jump to the worksheet you want, alphabetically.
Lotus Grouped Worksheets
Not all spreadsheet programs are created equal; there are some things that can be done in others that can’t be done in Excel. One such feature is “grouped” worksheets. However, Excel still provides the ability to adjust the formatting of multiple worksheets all at the same time.
Moving from Sheet to Sheet
Need to move quickly through the worksheets in a workbook? Learn the keyboard shortcuts and you can make short work of this navigational task.
Naming Tabs for Weeks
Need to set up a workbook that includes a worksheet for each week of the year? Here's a couple of quick macros that can do the hard work for you.
Ordering Worksheets Based on a Cell Value
Need to sort your worksheets so that they appear in an order determined by the value of a cell on each worksheet? Using a macro you can make this ordering change relatively easily.
Picking Worksheets Quickly
If your workbook contains a multitude of worksheets, the worksheet tabs at the bottom of the program window start to loose a bit of their utility value. If you want a quick way to choose which worksheet to display, apply the technique described in this tip.
Printing a Worksheet List
Want a list of all the worksheets in your workbook? Here's a short, handy macro that will place all the worksheet names into a single worksheet where you can work with them.
Protecting a Single Worksheet
Excel allows you to protect your worksheets easily—and that includes if you need to protect only a single worksheet out of an entire workbook. Here's a high-level discussion of how to achieve your protection needs.
Quickly Copying Worksheets
Excel provides a little-known way to copy worksheets simply by clicking and dragging. Here's how to do it.
Quickly Inserting a New Worksheet
Want a quick way to insert a worksheet? There's nothing faster than using the handy shortcut.
Referencing a Worksheet Name
Excel provides ways to reference the column or row number of a cell, but it doesn't provide a built-in way to reference a worksheet name. This tip examines how you can determine the name of a worksheet in any given position within the workbook.
Referencing Worksheet Tabs
Ever want to use the name of a worksheet tab within a cell? Here's how you can access that information using the CELL function.
Retrieving Worksheet Names
Want to grab the names of all the worksheets in a workbook? Here's how you can stuff all those names into the cells of a worksheet.
Returning a Worksheet Name
Need to know the name of the current worksheet? You can use the CELL function as the basis for finding this information and putting it in a cell.
Running a Macro when a Worksheet is Activated
Want to run a macro when you first select a worksheet? You can do so by using one of the event handlers built into Excel, as described in this tip.
Selecting All Visible Worksheets in a Macro
Do you need your macro to select all the visible worksheets (and just the visible ones)? It's not as easy as it sounds, but this tip uncovers the best way to do it.
Sheets for Days
Need a quick way to have a worksheet for each day in a month? Here's a macro that makes the worksheet creation a snap.
Shifting Objects Off a Sheet
One day you are just editing your worksheet like you normally do, then you see an error that says "Cannot shift object off this sheet." That can throw your editing for a loop—unless you know the information in this tip, which can help to solve the problem.
Switching Headers in a Frozen Row
Excel allows you to "freeze" rows in your worksheet. What if you want the rows that are frozen to change as you scroll through the worksheet? There is no way to do that directly in Excel, but you can use a macro to approach a solution.
Testing for an Empty Worksheet
If you are using a macro to process a number of worksheets, you may have a need to know if the worksheet is empty or not. After all, it makes no sense to process an empty worksheet; you can move to the next one. Here are some techniques to determine whether there is data on a worksheet.
Turning Off Worksheet Tabs
Look at the bottom of a worksheet and chances are you will see tabs for all the worksheets in the current workbook. Want to hide those tabs? You can do so by using the steps described here.
Unbreakable Formula References to Worksheets
Excel allows you, in your formulas, to include references to cells on other worksheets. Those references include the name of that other worksheet, which may cause questions about what happens when you change the name of the other worksheet. Here’s the scoop.
Unhiding Multiple Worksheets
You can hide a bunch of worksheets at the same time, but Excel makes it impossible to unhide a bunch at once. You can, however, create your own method for unhiding a group of worksheets all at one time.
Using Very Long Worksheet Tab Names
Excel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides some easy workarounds you could try if you need to use longer names.
Viewing Two Worksheets At Once
If you need to work on two worksheets in the same workbook at the same time, Excel makes this rather easy to do. All you need to do is display two windows and arrange them to both be visible.
Workaround for Multiple Rows of Worksheet Tabs
If you've got a lot of worksheets in your workbook, you may want to display their tabs in to rows at the bottom of the Excel window. There is no way to do this, but you might like one of the other helpful suggestions in this tip.