Working with MS Excel is hard enough as it is with one worksheet. But when you work with more than one, the difficulty in dealing with that data increases tenfold with every single worksheet you add on.
To help, here are a few basic tips, handy tricks and informative tutorials to make working with multiple worksheets in Excel ten times easier for you.
1. Viewing Multiple Worksheets At Once
A simple tip to start—customizing your view. It can be easy to lose track of which sheet you’re working on, especially when you have several worksheets to deal with. To put your worksheets front and center,
a) Open your workbook in Excel.
b) Click on the New Window button for every worksheet you want to view in that workbook.
c) In each new window, click on the tab of the worksheet you want to view.
d) Once the worksheets are displayed, click on the Arrange All button in the View window
e) In the following dialog, select your arrangement view. Ensure that you put a check in the Windows of active workbook option.
From there, you’ll be able to get a bird’s eye view of every worksheet in the workbook you need.
2. Viewing More Worksheet Tabs
This is another simple tip that may go unnoticed. Because the scrollbar at the bottom hogs all the space at the bottom of your window, the number of worksheet tabs you can see is limited. An annoyance when you have more than 3 worksheets.
To view more, simply hover over the 3 dots at the left of the scrollbar. Then click and drag it to the right. You’ll instantly start seeing more of your worksheet tabs displayed.
3. Grouping Your Worksheets Together
In Excel, working with your worksheets as a single group is a quicker way for applying formulas and formatting across worksheets with the same formatting. For instance, let’s say you need to change your monthly rent costs and regular bills. If you’re moving in September and need to update your budget worksheets, grouping them together will let you input the data once to update the entire group.
a) Click on the first worksheet tab you wish to edit.
b) Then Click + CTRL (for non-adjacent worksheet tabs) or Click + Shift (for adjacent worksheets) on the other worksheet tabs to add it to the group.
c) Modify the cell as needed. Click on enter.
To ungroup your worksheets, you can right click and select Ungroup Sheets option. This may seem like a basic tip, but it’s a powerful one to have!
4. Copying Formulas Between Worksheets
One handy tip for working with data across worksheets is to copy formulas that call for repetitive calculations. We’ve covered this tip before in our post, Work With Excel Data Like A Pro With 9 Simple Tips, but is a great tip worth mentioning again:
a) With the worksheet containing the formula you wish to copy opened, CTRL + clickon the tab of the worksheet you want to copy it into.
b) Click on or navigate to the cell with the formula or data you need (in the opened worksheet).
c) Press F2to activate the cell.
d) Press Enter. This will re-enter the formula or data, and it will also enter it into the same corresponding cell in the other selected worksheet as well.
5. Using Formulas to Link Worksheets
Despite what you may think, data in multiple worksheets can be more efficient. When you need to add cell values from different worksheets, it’s possible to create and use a formula that can pull data from other worksheets that are in the same workbook and formatted similarly.
This tutorial from ExcelFormulas gives you a solid easy-to-understand explanation of how this trick can make your task easier. It goes the extra mile and even shows you how the same methods can be applied to multiple workbooks, as well.
6. Consolidating Data From Multiple Worksheets
If you need to combine data from different worksheets, this tutorial from Tuts+ Computer Skills will show you step-by-step how to combine your data from multiple sheets into one pivot table.
This is perfect if you’re keeping track of different types of information that are related but are separated in individual worksheets. You can dynamically connect them into one single worksheet so you can update them all at once. Note that this tutorial uses Excel 2013, but is virtually the same for Excel 2016.
7. Importing Multiple Excel Sheets from Multiple Excel Workbooks
This following trick from ExcellsFun is a bit more advanced, but helpful for getting your Excel data from different worksheets and workbooks into an individual worksheet using PowerQuery.
Using the example of track sheets being used to analyze sales rep performance, this tutorial also links out to a resource where you can access those practice files for a good hands on learning experience to master this skill.
What tips do you have for working with multiple Excel worksheets?