Simon Hurst draws on queries and answers from the ExcelZone archives to offer a collection of time-saving worksheet handling tips.
Managing worksheets is such a vital part of working with spreadsheets that even relatively trivial time saving tips and techniques can lead to substantial improvements in productivity. Going a little further, knowing how best to use Excel formulae to work with values across worksheets can improve efficiency and reduce errors. Finally, more advanced techniques can help to consolidate data spread across many different sheets.
Which keyboard shortcuts are available for working with worksheets?
- CTRL+PAGE UP/DOWN - move forwards/backwards through worksheets
- SHIFT+F11 – Insert a new worksheet
How do I find the sheet I want to look at?
If you can see the tab at the bottom of the screen you can just click it to make that sheet active. If you can’t see any sheet tabs, try maximising your workbook window (Alt+, or click the middle of the three square icons at the top right of the Excel window). If you have more sheets than fit in the available area at the bottom then you can use the sheet navigation button to scroll along the list of sheets or jump to the beginning or end of the list. Alternatively, right click on any of the sheet navigation buttons to see a full list of all your sheets.
What are the best ways to manage worksheets?
Right-click on any worksheet tab to perform the following actions:
- Insert – unlike keyboards shortcut or menu/ribbon option allows you to choose from worksheet and workbook templates. In Excel 2007/2010 you can also click on the Insert Worksheet tab to insert a worksheet at the end of the existing worksheets:
- Delete – deletes sheet after warning if the sheet contains data. Delete sheet even if cells are locked and sheet is protected
- Rename – double-clicking the sheet tab is a quicker way to rename the sheet
- Move or Copy – including moving and copying between open workbooks or the current workbook and a new workbook. Be careful to select ‘Create a copy’ unless you really want to remove the sheet from the current workbook and insert it in the destination workbook:
- You can also move by dragging the sheet tab with the mouse or copy by dragging the sheet tab with the mouse while holding down the Control key.
- Select All Sheets – multiple sheets can also be selected by using CTRL+mouse click to select individual sheets and SHIFT+ mouse click to select a block of contiguous sheets. Use this method to perform the same operation or make the same entries on several sheets at time. Also use this method to rapidly destroy the contents of the selected sheets that you can’t see if you forget to turn it off when you’ve finished, or employ it accidentally.
- Tab Colour – Group sheets visually by applying similar tab colours. Not available in versions of Excel before Excel XP.
- View Code – see or create code attached to worksheet events.
- Hide/Unhide sheets – these options are only included in the right click menu in Excel 2007/2010. There are equivalent menu/ribbon options available.
How can I use sheet templates to save effort?
When you insert a new sheet using the Insert option on the sheet tab right-click menu, you can choose to base the new sheet or sheets on your existing templates. If you want your sheets to start with particular settings – such as headers and footers or a special custom number format – you can save a workbook with a single sheet as a template. You could also opt for a modular approach to spreadsheets by creating templates containing different groups of sheet, such as a documentation block, a data entry block and various report blocks. New workbooks can be created or existing workbooks enhanced by using Insert-Sheet to include the relevant blocks.
How can I view more than one sheet of a workbook at the same time?
The option Window-New Window (Excel 2007/2010: View tab, Window Group, New Window) creates an additional window on the current workbook. The title bar of the window will indicate it is a second or subsequent window:
When you have more than one window available, you can use the Arrange All option to display the windows side by side.
From Excel 2003 you can also choose to synchronize the scrolling of two windows shown side-by-side. In Excel 2003 Window-ompare Side by Side with and in Excel 2007/2010 View tab-Window group-View Side by Side will display a list of other open windows, including additional windows on the same workbook, to allow two windows to be shown side by side. A Synchronous scrolling button is then available.
Which Excel formulae can help me to refer to other sheets?
Formulae that refer from one sheet to another include the name of the sheet referred to and an exclamation mark:
The sheet name is enclosed in apostrophes when it contains a space or starts with a number:
The INDIRECT() function can change all the formula on a page to change to refer to a different sheet, simply by changing the contents of a single cell.
What is a 3D formula?
Usually references to a range of cells in a formula refer to a flat “rectangle” of cells on a single sheet. A 3D formula adds an extra dimension by extending the rectangle through several adjacent sheets.
To create a 3D Sum formula, click on the AutoSum button and then click on the tab of the first sheet to be included in the formula. SHIFT+Click on the last sheet to be included, then select the required cell or range of cells on this last sheet. The formula should look something like this:
It refers to the range A1:A3 on all the sheets positioned between Sheet2 and Sheet3, including the end sheets themselves. Moving sheets into or out of the range will change the result.
How can I consolidate multiple sheets?
Judging by the number of questions on the issue, this is a problem many ExcelZone members have:
- How do I combine worksheets in Excel?
- Tutorial: Consolidating multiple worksheets in a Pivot Table
- Excel 3-D spreadsheets: is there a way of manipulating them?
- Excel - Combining lots of spreadsheets into one
- How to combine three worksheets into one for a pivot table
Apart from a bit of copying and pasting, there is no simple solution in many cases. Options suggested include:
- Use a macro
- For Excel 2007/2010 files saved in the default, xml-based file format, manipulate the xml directly
- For very simple tables of data, for example simple lists of description and value, use a PivotTable based on Multiple Consolidation ranges
- Use a database such as Microsoft Access to import the data in the worksheets, consolidate it and then export it to, or link to it from, Excel.
- For Excel 2010 users, consider the use of the free PowerPivot add-in and the use of SQL Union queries. Debra Dalgleish has an excellent example on her Contextures blog.
How do I find out more about managing worksheets?
For further information and ideas have a look at:
- Excel Zone Compendium: Working with worksheets (Excel 2007 update)
- Gail Perry's Tips for Working With Multiple Sheets
About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base at [email protected].