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.
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. 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.
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 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 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 now 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 sheet 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. So I'm interested in getting the total revenue for quarters one, two, three and four. So I'll start out by using the sum function.
That's the appropriate function to use. I'll go over to quarter one 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 two, quarter three, quarter four. As you can see, I'm not seeing anything change. I'm still on the quarter one sheet.
If I look at the formula bar, I see some Q1 colon Q4 and then B4. B4 is the common cell. I'll put in a closing parentheses and press ENTER.
That will return 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 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 underlined there. And that's how I was able to summarize all the information across four different sheets in a summary sheet.