Worksheets

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
Excel provides a consolidation tool that allows you to easily combine the data from a bunch of worksheets into a single worksheet. This tip explains how you can use this tool.

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.

Freezing Top Rows and Bottom Rows
It is not unusual to need to see both your column headers and column totals in your data. How to display all this information at one time can be a frustrating aspect of worksheet design and usage. This tip provides some ideas that can help make the desired information accessible.

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
When you've got a workbook that has a huge number of worksheets in it, jumping to a specific worksheet can be a challenge. Excel provides some tools to select from a large number of worksheets, but you can also create your own tool that will do the job even better.

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.

Putting Addresses on State-Specific Worksheets
Got a bunch of data on a worksheet that you need to divide onto other worksheets, based on a specific piece of information in the data? You can do this manually or you can do it using a macro that examines your data and makes the move. Here's how.

Quickly Inserting a New Worksheet
Want a quick way to insert a worksheet? There's nothing faster than using the handy shortcut.

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.

Renaming Worksheets
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.

Saving Versions
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
It is not uncommon to have workbook where two of the worksheets get more use than any of the others. If you want to easily switch between these two worksheets, you'll love the shortcuts discussed in this tip. Implement them, and you'll be quickly switching worksheets in no time!

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.

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.

 

More Information

The following are additional topics related to the subject of 'Worksheets'. A bracketed number after the topic indicates how many articles are related to that subject.