A worksheet in Excel can contain thousands of columns and more than a million rows. This advantage is also a disadvantage, because if you combine a lot of data on one worksheet, it quickly becomes a cluttered mess. For processing all kinds of data, formulas and charts, it is better to use separate worksheets within the same file.
Tip 01: Naming
Every workbook in Excel consists of one or more worksheets. When you're working with a large amount of data, it's best to put them in different worksheets of the same workbook. In this way you keep all data together, while the different data groups are still clearly divided. If you create several worksheets, Excel will number them as follows: Sheet 1, Sheet 2, Sheet 3, ... But in order to manage them better, it is better to give them a distinctive name. This can be years, cities or months, as long as the name indicates what exactly is in the worksheet. Keep in mind that each worksheet must have a unique name. Right click on the sheet tab you want to rename and select Renaming. Type the desired name and then click anywhere outside the worksheet. You can also double-click a sheet tab to rename it.
Show sheet tabs
If you receive an Excel file from someone, but you don't see any worksheet tabs, the option is Show sheet tabs most likely disabled for this document. Go to File / Options / Advanced. Check below Display options for this workbook or the checkbox for Show sheet tabs is enabled.
There is little easier than moving a worksheet within the same workbook Tip 02: Insert Sheet
Next to the tabs of the worksheets you will find a button with a plus sign. This is the button with which you create a new worksheet. You can also go to the tab in the ribbon Start to go. There you choose in the group Cells the assignment Insert / Insert Sheet. To delete a worksheet, right-click the sheet tab and choose remove. Or go back to the tab Start where you Delete / Delete Sheet selects.
Suppose you already have four worksheets and you want to add three new worksheets, the fastest way to do so is to press the Shift key and select the number of existing sheet tabs you need at the bottom of the worksheet. Then right click on the selected sheet tabs and choose Insert. Excel adds the selected number of worksheets at once.
Tip 03: Copy worksheets
There is little as easy as moving a worksheet. Click on the sheet tab with your mouse and drag the sheet icon to the desired position in the row of sheet tabs. You can also copy a worksheet. To do this, press Ctrl and then drag the sheet tab to the correct location in the row of sheet tabs. A plus sign will appear in the sheet icon. Release the mouse button before releasing the Ctrl key so that Excel places a copy of the selected worksheet in that location. In the macOS version of Excel, you must use the Alt key instead of the Ctrl key to make a copy of the worksheet.
Tip 04: To other workbook
We have talked so far about moving and copying worksheets within the same workbook. But you can also move a worksheet to another workbook. Make sure both workbooks are open. For clarity, we will call the workbook from which we want to copy a worksheet as WorkbookSource.xlsx. The workbook into which we want to paste the worksheet is called WorkbookTarget.xlsx. In WorkbookSource.xlsx, right click on the sheet tab you want to copy and choose the command Move or copy. A small window will open, where in the To folder box you can select WorkbookTarget.xlsx selects. below Cover Sheet determine in which position that worksheet should be placed. And at the bottom you can indicate whether Excel should make a copy of the selected worksheet. If you do not check this box, Excel will not copy the worksheet, but will move it from one folder to another.
Just checking
When moving a worksheet to another workbook, pay attention to any formulas that refer to cells in the current workbook. As long as the formulas are built with data from the same worksheet, there is nothing to worry about. However, if formulas are used that refer to data on other worksheets, those formulas will no longer be correct after moving the worksheet.
Adjustments can be applied to several worksheets at once by grouping themTip 05: Colors
To keep it clear, you can give the tabs not only a suitable name but also a color. Right click on the worksheet tab and select the option tab color. This opens the palette with the theme colors and the default colors. With the option More colors opens the Windows color picker where you can select any color you want. If the sheet tab has a slight gradient to that color, that means the sheet is selected. Click on a tab of another worksheet to see the actual color change.
Tip 06: Group
You can apply adjustments to several worksheets at once by grouping them temporarily. To group the worksheets, press the Ctrl key while clicking the different sheet tabs one by one. When you then format the data of cells in one sheet of the group in a certain color or font, the same cells in the other grouped worksheets will adopt the same format. You can also type data on that one tab, after which the same data will also appear on the other tabs. Don't forget to ungroup, or you'll keep modifying data on all grouped worksheets without realizing it. To ungroup, right click on one of the tabs and choose Ungroup sheets.
Tip 07: Switching
If you work with many worksheets, the tabs may not all fit in the Excel window. You can browse the worksheets in a number of ways. In Windows, you'll see three horizontal dots at one or both ends of the taskbar. Click the three dots to cycle through the sheet tabs in that direction. You can also use the right and left arrows of the tab bar. If you click on the left arrow while holding down Ctrl, you will flash to the first sheet.
Shortcut keys
Lovers of shortcuts, we give another trick to quickly switch between the worksheets. The keyboard shortcut to select the next sheet is Ctrl+Page Down. Logically, the keyboard shortcut to select the previous sheet is Ctrl+Page Up. You can also use the Ctrl and Shift keys to select multiple sheets. We have already explained the method with Ctrl in tip 6. Hold down Shift to select a contiguous set of sheets while clicking the first and last tabs in the range.
Tip 08: Extra space
In Windows, there is a scroll bar at the bottom of the Excel window that takes up quite a bit of space. If you work with many worksheets, you will not have enough space to see all the sheet tabs. Then it's nice to know that you can make the tab bar longer. To do this, click with your mouse pointer on the three vertical dots to the left of the scroll bar. Then drag the three dots to the right to trim the scroll bar.
With a simple trick it is possible to have multiple worksheets in view at the same time Tip 09: More sheets in window
When you work with multiple worksheets, you normally only see one worksheet on the screen. But with a simple trick it is possible to have multiple worksheets in view at the same time. We show this with the worksheets: Copenhagen, Brussels, Amsterdam. Make sure that the first worksheet (Copenhagen) is in view and go via the ribbon to the tab Image. Click on the button in this tab New window. This will allow you to see the same worksheet in a second window. In that second window, click on the Brussels sheet tab, so that it appears. Because you want to display a third worksheet, use the button New window again. And in this third window, click on the Amsterdam sheet tab. The third window can be recognized by the indication 3 after the file name in the title bar. Now you can combine the three open windows by using the ribbon in the tab Image on the button All windows to click. A small dialog box will appear asking how you want to combine the windows. Choose Next to each other and confirm with OK. All open windows are now neatly arranged next to and below each other.
Tip 10: Hide
Do you want to hide certain worksheets? Which can. Right-click on the tab you want to disappear from view and choose Hide in the context menu. To bring a hidden worksheet back to the surface, right-click on one of the other tabs and select the command Visible. If several worksheets were hidden, Excel will ask in a pop-up window which worksheet you want to bring up.
Tip 11: Sheet List
Where we discussed how to switch between the different tabs in tip 7, there is another way, namely the sheet list. That is a list of all visible sheets. How do you open the sheet list? Right-click on the tab navigation buttons, that's a mouthful for the two navigation arrows you see at the bottom left of the sheet tabs. A list of the worksheets from the workbook will appear where you can just double click on the name of the sheet you want to make active.
Number of sheets
How many worksheets does Excel show when you start a new workbook? That depends on the settings. Click on the ribbon tab File. Then select the Options. In the window Options for Excel choose in the left column General and then you search in the section When new workbooks are created the assignment Number of sheets to be picked up. Use the counter to indicate how many worksheets a new workbook should contain.
Tip 12: Template
When you save an Excel file as an .xltx file, it becomes a template. This will turn all other worksheets in this file into templates as well. The opposite is also possible. You can insert a template into an existing workbook as a new worksheet. To do this, right click on an existing tab and choose the command Insert. This opens a window where you select what you want to insert. From here you can also consult and select the online Office templates. A template you insert in this way is automatically placed in front of the sheet tab you selected.
Tip 13: Secure
You protect a worksheet to prevent yourself or someone else from accidentally making changes to it. To do this, go to the tab in the ribbon Check and choose in the group To secure in front of Protect sheet. In the pop-up window you indicate what the user is still allowed to do. If the user is not allowed to do anything in it, uncheck all the boxes. Then enter a password to ensure that the security cannot be lifted just like that. For confirmation, Excel will ask to repeat the password.
