• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

TheAppTimes

App Reviews, Tutorials, & Gadget News Hub

  • Gadgets
  • How To
  • Apps
  • Software
  • IT
  • Guides
  • Gaming
  • Show Search
Hide Search
Home » How To » How to Create Summary Worksheets in Excel 2010

How to Create Summary Worksheets in Excel 2010

November 6, 2014

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:

Expenses Sheet. xlsx

Expenses Sheet_solution. xlsx

 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.

insert new worksheet

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

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.

ALSO READ:  The Excel R1C1 Reference Explained

copy paste row headings

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).

formula bar - creating summary worksheets

10. Repeat the same procedure for worksheets Q3 and Q4. The final formula will look like this.

final formula

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.

delete zeros  - create summary worksheets

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.

Tweet
Share
WhatsApp
Pin
Buffer
0 Shares

Filed Under: How To Tagged With: excel

You May Also Like

Ways to Use Excel Mobile - TATFI
10 Creative Ways to Use Excel Mobile in your Business
How to Delete Blank Rows in Excel 2016
How to Delete Blank Rows in Excel 2016
excel 2010 tutorials
Remove Duplicate Rows in Excel 2010 in 4 Super Quick Ways

About Adeline Gear

Adeline is a tech enthusiast who loves exploring the latest tools and applications in the marketplace. She also loves her Android and spends time reviewing apps and playing games when she has the time.

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Hottest New Gadgets!

Withings ScanWatch 2 - TAT

Everything You Need to Know About the Withings ScanWatch 2

Starfield Streaming gear - TAT

Elgato Unveils Limited-Edition Starfield Streaming Gear

Samsung PRO Ultimate Memory Cards - TAT

The New Samsung PRO Ultimate Memory Cards Offer Unmatched Speed for Professionals

Galaxy Watch 6 Classic Astro Edition - TAT

Galaxy Watch 6 Classic Astro Edition Launched as a Tribute to MENA’s Innovators and Explorers

Satechi Dual Dock Stand - TAT

Satechi Dual Dock Stand is a Great Mac Companion for Your Workstation

Popular Tags

android android apps android games android how to apple casino games chrome extensions excel excel how to facebook fitness wearable gadgets gaming gmail google ios apps ios games ios how to ios tips ipad iphone kids apps learn excel management microsoft new releases outlook how to photo apps powerpoint how to productivity Samsung security smartphone smartphones smartwatches software tablet tablets technology twitter web browsers windows 10 word how to xbox youtube

Disclosure

TheAppTimes is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.
Best Controllers for Xbox Cloud Gaming - TAT

Best Controllers for Xbox Cloud Gaming

Starfield - TAT

Starfield: Get Ready to Experience a New Frontier in Gaming

Razer Cobra Pro - TAT

Razer Cobra Pro: A Great Gaming Mouse for Competitive Gamers

Best gaming monitors 2023 - TAT

The Best Gaming Monitors for a Truly Immersive Experience

Stormcloud Vapor Special Edition Wireless Xbox Controller - TAT

Microsoft Launches the Stormcloud Vapor Special Edition Wireless Xbox Controller

Hot on Amazon

TheAppTimes © 2023 // Runs on StudioPress

  • About
  • Contact
  • Privacy Policy
  • Template Gallery