How to Select All Tabs in Excel: 4 Effortless Methods

Are you tired of manually selecting each sheet in your Excel workbook every time you must make a change or update? It’s time to streamline your workflow and save yourself some valuable time! Knowing how to select all tabs in Excel is a powerful tool that can make working with multiple sheets a breeze. 

In this guide, I’ll walk you through the simple steps to select all sheets in Excel in different ways. So you can work smarter, not harder. You’ll find different methods useful for a varying range of situations. So, let’s get started to take your Excel skills to the next level!

Why Should You Learn to Select All Sheets in Excel?

Wondering how knowing to select all sheets in Excel could benefit you? Find below the most popular reasons:

1. You can apply one sheet formatting style to all the worksheets by selecting them altogether.
2. If you’re creating a data entry workbook with many sheets and those sheets must contain similar default columns and rows, selecting all sheets will benefit you.
3. Suppose you need to edit or delete specific data from your Excel workbook manually. These data are in the same spot in different worksheets. Select all the sheets and delete data from one worksheet. Excel will automatically erase the same data from the same location in other sheets.
4. You’re importing formulas from a different source or workbook. Also, you must distribute the same formulas in different sheets but same locations. Then selecting all tabs will reduce the copy-and-paste effort. 

Now, it’s the right time to explore various Excel tricks to highlight all worksheets at once in Excel below: 

Select All Tabs Using Keyboard Shortcuts

You may work with many keyboard shortcuts while analyzing data in Excel to reduce clicks and increase productivity. By using keyboards, you can actually use the robust calculation and navigation powers of Microsoft Excel. Find below some additional hotkeys that you can remember to select all worksheets in Excel in a flash:

1. Ctrl + Shift + Page Down

Hit Ctrl + Shift + Page Down to select all worksheets to the right side of the currently selected sheets. After holding Ctrl + Shift keys, you can press Page Down once every time to select the next sheet. Or, you can simply press all keys at once and hold until Excel highlights all the Worksheets. 

2. Ctrl + Shift + Page Up

This one is just the opposite of the previous hotkey. If you use Ctrl + Shift + Page Up, you can select all the sheets to the left of the currently selected tab on an Excel workbook.

3. Using Sheet Tabs Hotkey F6

F6 is a useful hotkey to cycle the selections between the Excel ribbon menu, cells, worksheet tabs, and the Excel Status Bar. So, here’s how you can use F6 along with other keyboard shortcuts to select all sheets in Excel: 

1. Open any Excel workbook and hit the F6 key.
2. Excel will highlight the first worksheet.
3. You can now highlight other worksheets by going left or right using the arrow keys.

4. To select all or some of the sheets, use the arrow key to highlight that particular sheet and then hit Ctrl + Space keys together.
5. Repeat the same steps until you’ve selected all of the desired numbers of worksheets in Excel.  

4. Alt + H + O + I

Are you using Excel 2007 or earlier versions? Then, you can press Alt + H + O + I to instantly select all the worksheets in your Excel workbook.

Right-Click Context Menu on Tab Toolbar

On Excel 2010 or newer desktop app editions, you can also use the right-mouse button click to select all tabs in Excel. Here’s how it’s done:

1. Open your Excel workbook and right-click on any of the worksheets.
2. A context menu will show up. 

3. There, click on Select All Sheets.
4. An alternative to this is to press the S key on the keyboard after right-clicking on any sheet. 

Select All Tabs Using Mouse Clicks

Not just the right-click of a mouse but using the left mouse button click, you can select all sheets in Excel at the same convenience as the previous method. Here are the steps you must follow:

1. Select the first worksheet of your Excel workbook using the left-click of the mouse.
2. Now, hold the Shift key.
3. Then, go to the end of the tab list and left-click the last worksheet.
4. You’ll see that all sheets have been selected instantly.
5. Similarly, you can select the first tab and then press and hold the Ctrl key on the keyboard.
6. Now, you can select all other sheets of the same workbook by clicking the left mouse button one after another.
7. If there are sheets that don’t show up on the sheets toolbar, you can use the left and right Scrolling buttons at the lower-left corner of the Excel app.

Highlight All Tabs Using a VBA Script

Are you looking for a fully automated method to select all sheets in Excel? Do you find VBA coding for Excel interesting? Then you’ll definitely like this method. Here are the steps you can try:

Create an Excel Macro-Enabled Workbook

1. Select the File tab from the Excel ribbon and choose Save As.
2. Now, click This PC and then choose Browse.
3. Then, you can choose a preferred folder to save the Excel file.
4. However, before saving, click the Save as type drop-down list on the Save As dialog box and choose Excel Macro-Enabled Workbook.
5. Finally, hit the Save button to complete the process.

Now that you’ve created an XLSM file of the existing Excel workbook, you can write and save the VBA script in this Excel file. 

Write and Save the VBA Script

1. Press the Alt + F11 keys altogether to access the VBA Editor tool.
2. There, click the Insert button on the VBA Editor toolbar and select Module.
3. A new Module window will open on the grey field of the VBA Editing tool.
4. Now, copy and paste the following VBA script into the new Module you just created:

Sub SelectAllSheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Select (False)
    Next ws
End Sub

5. Click the Save button and close the VBA Editor. 

Great! Your VBA macro to select all sheets in Excel is ready. In the next section, learn how to run it.  

Execute the VBA Script

1. Hit the Alt + F8 keys on your keyboard to launch the Macro dialog box.
2. There, you should see all existing VBA macros including the new one you created.
3. Therefore, select the SelectAllSheets VBA macro and click the Run button.
4. Excel will instantly highlight all the tabs in your Excel workbook.

The code uses a For Each loop to select each worksheet in the workbook. Also, the macro uses the Select command to select each sheet after looping through all the worksheet tabs. And finally, the False argument ensures that the sheets aren’t activated before you select a cell on the first selected worksheet.

Conclusion

Selecting all sheets in Excel can be a time-saving technique when you need to apply the same formatting, formula, or data to multiple sheets at once. Whether you prefer using keyboard shortcuts, VBA code, or Excel’s built-in features, this article outlines several ways to select all tabs in Excel. 

Give any of the above methods a try. You can choose a method depending on your workbook and the need for automation or simplicity. For simple or occasional use, you can go for the mouse-click-based method. If you need more speed and automation while analyzing data in Excel, go for keyboard shortcuts or the VBA macro.