Formula Link Multiple Worksheets to within an Excel Summary Workbook

This post I’ll share with you how to create a summary worksheet that pulls data from within other Excel worksheets. Say you have multiple monthly worksheets that track expenses like my screenshot examples below.

January

April

August

December

Ok, the main thing to pick up here in the screenshot examples is that all the worksheets are in the same structure of rows and columns. In other words, all the food costs are in cell D5. All of the housing costs are in cell D6. And so on. Also make sure that all the worksheets are in order for the calculations. If you had a checking account worksheet, move it to the left or right of the worksheets you want to calculate.

Once all your worksheets are in order we are ready to build the formula.

Ready set  go!

Build the formula in phases.

  1. Click on Cell D5 of the Summary worksheet
  2. Type =SUM(
  3. Left click on the Jan worksheet
  4. Click on D5 cell (food expense for Jan!)
  5. Hold down the Shift key and left click Dec worksheet
  6. Press Enter
  7. The formula should read =SUM(Jan:Dec!D5)
  8. Verify the calculation added all the D5 cells from Jan through Dec

sum jan dec D5

Now use the autofill button to copy the formula down. Look for a tiny black cross in the lower right cell and drag down.

AutoFill Button Excel

 

Want to try this on your own. Download my .xlsx workbook here. Multiple Worksheets for Excel Formula

 

 

Advertisements

About Steve Chase

I want to help you work in your business, so you can work on your business. I want to help you achieve your goals and help you have a successful small business journey. I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business. But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers. My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant. Please contact me on my new website at www.sequentiasolutions.com
This entry was posted in Budget, Excel and tagged . Bookmark the permalink.

2 Responses to Formula Link Multiple Worksheets to within an Excel Summary Workbook

  1. roel says:

    Thanks for sharing. It helped me a lot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s