Have you ever had the need to create Summary worksheets while working on your Excel data? Summary sheets basically pull together the totals of values that are available in other worksheets in your workbook or should I say they help to consolidate data from multiple worksheets. They act as the main sheet that can be viewed to know the sum performance of the whole. If you are in need of preparing one such sheet, this tutorial will show you the step by step process to create summary worksheets in Excel 2010.
For the purpose of this tutorial, I’ll take up a scenario where I have the expenses of a company detailed for 4 quarters. I want to create a summary sheet showing me the net expenses incurred for each individual item at the end of a year. You can download the Expenses data sheet I prepared for this purpose to test it out for yourself. (Please right-click and Save As in your folder.)
Downloadables:
How to Create a Summary Worksheet in Excel 2010
One of the most important factors to remember while preparing a summary worksheet is that having the data in the various worksheets in your Excel workbook in the same arrangement or format will make the consolidation less tedious. In fact, you should try and keep it that way to complete the job more efficiently. Now lets get started.
1. Insert a new worksheet before the current worksheet you have, in our case before Q1. Right-click Q1 and select Insert. In the Insert dialog box, ensure Worksheet is selected and click OK.
2. Name this worksheet Summary Total Expenses. Double click the worksheet tab named Sheet 5 and enter the name Summary Total Expenses.
3. In the newly created Summary Total Expenses worksheet, click cell A1 and enter the title Total Projected Expenditure of XYZ ltd. 2014
4. Now copy the row headings from any one of the worksheets Q1, Q2, Q3 or Q4 and paste it in the Summary sheet. To do this, select cell A2 in the Summary Total Expenses sheet and then click the Q1 tab. Select the cell range A2:A45 in the Q1 sheet; then press Ctrl+C, click the Summary Total Expenses tab again, and press Enter.
5. Similarly, in the Summary Total Expenses sheet, in cell B2 enter “Actual Figures”.
6. Now you have to create the summary totals to reflect the sum of each individual expense in sheets Q1:Q4.
7. In the Summary Total Expenses sheet, click cell B4, press Alt+= to select the AutoSum feature. Alternately, click cell B4, and on the Home tab, in the Editing group, click AutoSum. Excel will place the SUM formula in the cell with the insertion point between the 2 parentheses.
8. Click the Q1 sheet tab, then click its cell B4 to select the actual figures for the print expenses. The Formula bar will now read =SUM(‘Q1!B4)
9. In the Formula bar, type a comma after B4. Then click the Q2 sheet tab, click its cell B4 to select the actual figures for the print expenses in Q2. The Formula bar will now read =SUM(‘Q1’!B4,‘Q2’!B4).
10. Repeat the same procedure for worksheets Q3 and Q4. The final formula will look like this.
11. Press Enter to get the sum total of all 4 quarters expenses (44,000) in the Summary Total Expenses sheet.
12. Now you can Autofill the rest of the cells. Click cell B4 and drag the AutoFill handle in the lower-right corner right down to cell B45.
13. Now delete in zeroes in the cell headings B12, B17, B25, B31, and B39.
Downloadable:
PDF Copy of How to Create Summary Worksheets in Excel 2010
That’s it! You have just learned how to create a summary worksheet to reflect the totals of the additional worksheets you have in your workbook.
Leave a Reply