Learn how to efficiently format and summarize data across multiple Excel sheets. This step-by-step guide demonstrates easy techniques for applying consistent formatting and performing swift calculations across several worksheets.
Key Insights
- Apply consistent formatting across multiple Excel sheets simultaneously by selecting all sheets using the Shift key, then applying desired changes such as font Verdana, size 14, and accounting number format.
- Perform quick calculations across multiple sheets by using Excel's SUM function combined with the Shift key to select relevant cells (e.g., summing revenue in cell B4 from sheets Q1 through Q4).
- Use Excel's autofill handle with relative referencing to easily extend summary calculations (e.g., cost of sales, rent, other expenses, and profit) across multiple sheet positions without manual input.
This lesson is a preview from our Advanced Excel Course Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.
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. We'll start off with Task One.
We need to format data across sheets Quarter One, Quarter Two, Quarter Three, and Quarter Four, each named with Q and the number of the quarter. So let's take a look at Quarter One. 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.
Now, if I click on Quarter One, Quarter Two, Quarter Three, and Quarter Four, you'll see that there's 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 One. If you look at Quarter Two, you'll see that the values are still the same.
Now I'm going to undo, and then apply those changes across all four sheets. All I need to do is hold the Shift key and click on Quarter Two, Three, and Four. I'll apply the same formatting adjustments I just made.
Verdana. I'm going to change the font size to 14, and I'm going to choose Accounting formatting. Now I'm just going to click on a sheet that is not any of the Quarter sheets, and then individually click on each sheet.
Here's Quarter One, here's Quarter Two, here's Quarter Three, and here's Quarter Four. All of them have the formatting that's been applied to Quarter One. So that's how you can work across sheets when it comes to formatting.
Let's take a look at working across sheets when you need to summarize information to a single sheet. I'm interested in getting the total revenue for Quarters One, Two, Three, and Four. I'll start out by using the SUM function.
That's the appropriate function to use. I'll go over to Quarter One and 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 the Shift key and then click on Quarter Two, Quarter Three, and Quarter Four. As you can see, nothing changes. I'm still on the Quarter One sheet.
If I look at the formula bar, I see some Q1:Q4 and then B4. B4 is the common cell. I'll put in a closing parenthesis and press ENTER.
That returns me back to the original sheet because I'm still there. I'm just visiting Quarter One. When I press ENTER, I've now summed all the values for Quarters One, Two, Three, and Four.
If I want to do the same for Cost of Sales, Rent, Other Expenses, as well as Profit, I don't need to repeat what I just did for Revenue. I can simply use the autofill handle, and relative references will ensure that I select all other values in the positions corresponding to Revenue. I'll choose Fill Without Formatting.
And that's how I was able to summarize all the information across four different sheets in a summary sheet.