Learn to work across multiple sheets if you need to apply formatting or summarize calculations across multiple sheets.
Excel tricks, including working quickly across multiple sheets, are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.
Working Across Sheets
In this short lesson, we'll take a look at how you can work across multiple sheets if you need to apply formatting or summarize calculations across multiple sheets. So we'll start off with Task 1.
Formatting Data
We need to format data across sheets Quarter 1, Quarter 2, Quarter 3 and Quarter 4, each named with Q and the number of the quarter. So let's take a look at Quarter 1.
And I'm going to make a slight adjustment to each of these sheets just to make it easier for you to understand what's going on. I'm going to change the size of each of the sheets so you'll know that there is actually a difference between them as we click through. Because all the cells are in the same location in the sheet, it might look like we're just moving across one sheet. All right, so now if I click on Quarter 1, Quarter 2, Quarter 3 and Quarter 4, you'll see that there is a difference.
What I would like to do is apply three types of formatting to the values for revenue, cost of sales, rent, other expenses and profit. Those changes are going to be changing the font to Verdana, then I want to change the size to 14. And I want to change the formatting to Accounting format. I've made that change in Quarter 1.
If you look at Quarter 2, you'll see that the values are still the same. So now I'm going to undo, and now I'm going to apply those changes across all four sheets. All I need to do is hold the Shift key and click on Quarter 2, 3 and 4. I'll apply the same formatting adjustments I just made: Verdana, going to change the font size to 14, and I'm going to choose Accounting formatting.
So now I'm just going to click on a sheet that is not any of the quarter sheets, and now individually click on each sheet. Here's Quarter 1, here's Quarter 2, here's Quarter 3 and here's Quarter 4. All of them have the formatting that's been applied to Quarter 1.
So that's how you can work across sheets when it comes to formatting.
Summarizing Information
Let's take a look at working across sheets when you need to summarize information to a single sheet. So I'm interested in getting the total revenue for Quarters 1, 2, 3 and 4, so I'll start out by using the SUM function. That's the appropriate function to use. I'll go over to Quarter 1, and let me select the first amount, which is 25,000.
Now, I don't have to navigate and actually see the other sheets to select the other values. All I need to do is hold on to the Shift key and then click on Quarter 2, Quarter 3, Quarter 4.
As you can see, I'm not seeing anything change. I'm still on the Quarter 1 sheet, but if I look at the formula bar, I see SUM Q1:Q4 and then B4. B4 is the common cell. I'll put in a closing parenthesis and press Enter. That will return me back to the original sheet because I'm still there. I'm just visiting Quarter 1.
When I press Enter, I've now summed all the values for Quarters, 1, 2, 3 and 4. Now, if I wanted to do the same for cost of sales, rent and other expenses as well as profit, I don't even need to do what I just did for revenue. I can simply use the Autofill handle and Relative Reference will make sure that I select all the other values in the positions relevant to revenue. I'll choose Fill without Formatting, I'll keep that underline there.
Recap
That's how I was able to summarize all the information across four different sheets in a summary sheet.