• About
  • Contact
  • Privacy Policy
  • Submit For Review
  • Template Gallery
  • Excel 2016 Udemy Course
  • Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

TheAppTimes

App Reviews, Tutorials, & Gadget News Hub

  • Gadgets
  • How To
  • Mobile Apps
  • Software and Web Apps
  • Buying Guides
  • 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:  How to Create a Drop Down List in Excel 2010

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!

Amazfit Falcon Smartwatch - TATFI

The Amazfit Falcon is a Military Grade Smartwatch with Premium Looks and Great Features

Couchmaster Cyboss Ergonomic Couch Desk - TATFI

Couchmaster Cyboss is an Ergonomic Desk to Keep Off Neck Pain

Philips Hue Festavia String Lights - TATFI

Add a Bit of Sparkle to Your Christmas Tree with Philips Hue Festavia String Lights

Samsung Galaxy Tab A7 Lite Kids Edition - TATFI

This Kid Friendly Tablet from AT&T is a Thoughtful Gift for Your Little One This Christmas

Is An iPhone Really Worth It - TATFI

Is an iPhone is Really Worth It?

Devices You Might Want to Try - TATFI

4 Fantastic Devices You Might Want to Try

Amazon Kindle 2022 - TATFI

Meet the Amazon Kindle 2022 – Packed with New Features

Popular Tags

android android apps android games android how to apple casino games chrome extensions excel excel how to facebook gadgets gaming gmail google instagram ios apps ios games ios how to ios tips ipad iphone kids apps learn excel management microsoft music apps new releases outlook how to photo apps powerpoint powerpoint how to productivity Samsung security smartphone smartphones software tablet tablets 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.
Tips to Boost Your Ranking and Sales on Etsy - TATFI

5 Ways to Boost Your Ranking and Sales on Etsy

PunchBee Time Management App - TATFI

Know How You Spend Your Time with PunchBee

DevOps Model Explained - TATFI

The Complete Guide to DevOps: How the Model Works and Who It Benefits

Approaches to Digital Marketing - TATFI

Blending Technical and Content-Centric Approaches to Digital Marketing

Tech Tips For Startups - TATFI

Useful Tech Tips For Startups

Boox Tab X Android eReader - TATFI

Boox Tab X is an Android eReader That Helps You Do More Than Just Read

Best e Readers to Buy in 2023 - TATFI

5 Best eReaders to Buy in 2023

Best Translation Apps for Android - TATFI

5 Great Translation Apps for Android

Dynamic Island Feature on Your Android - TATFI

How to Get the Dynamic Island Feature on Android

Connected TV Advertising In 2023 - TATFI

5 Things You Need To Know About Connected TV Advertising In 2023

Smart Cost Cutting Strategies for SMEs - TATFI

5 Smart Cost Cutting Strategies for SMEs

Technologies behind the online casino world - TATFI

The Latest Technologies Behind the Online Casino World

TheAppTimes © 2023 // Runs on StudioPress