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.
Combining Worksheets from Many Workbooks
Do you need to pull a particular worksheet out of a group of workbooks and combine those worksheets into a different workbook? You can make the task a breeze with the macros presented in this tip.
Condensing Multiple Worksheets Into One
Want a quick way to combine your worksheets? Excel provides a tool to make the task easier, but you could also bypass the tool and create a simple macro to do the same 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.
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.
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.
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.
Forcing a Worksheet to be Protected Again
Excel allows you to protect your worksheets so they can only be changed as you want to have happen. If you unprotect a worksheet and then save the workbook, that status (unprotected) is saved as well. This tip explains how you can make sure that a worksheet remains protected when saving.
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.
Generating Unique Numbers for Worksheets
You may need to automatically generate unique numbers when you create new worksheets in a workbook. Here's a couple of easy ways to do it.
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, an 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.
Independent Radio Buttons
Radio buttons are great for some data collection purposes. They may not be that great for some purposes, however, for the reasons described in this tip.
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’s some handy ways to jump to the worksheet you want, alphabetically.
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.
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.
Protecting a Worksheet's Format
You can protect various parts of your worksheets by using the tools built into Excel. One thing you can protect is the format of the worksheet, as described in this tip.
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.
Relative Worksheet References
Copy a formula from one place to another and Excel helpfully adjusts the cell references within the formula. That is, it adjusts everything except the names of any worksheets that may be referenced in the formula. Here's how you can get around that lack of change.
Some easy steps to rename the worksheets in your Excel workbook.
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.
If you need to save your Excel data at different benchmarks, you might want to use some sort of "versioning" system. Such does not exist in Excel, but you can use the ideas presented here to make saving your data a snap.
Setting the Number of Default Worksheets
Excel allows the user to determine how many default worksheets are in a new workbook.
Shortcut to Move between Two Worksheets
Moving between to adjacent worksheets is easy; Excel provides a shortcut key to do the trick. If you want to move between two non-adjacent worksheets, you'll need to get creative.
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.
Synchronized Workbook and Worksheet Names
When you work on older workbooks in Excel, you may notice that the name of the worksheet tab and the workbook itself are the same. This is by design, and something you can correct.
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.
Viewing Same Cells on Different Worksheets
When switching from one worksheet to another, you might want to view the same portion of the new worksheet that you were viewing on the old. Here's how you can add this capability by using a macro.
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.