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.
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.
- Click on Cell D5 of the Summary worksheet
- Type =SUM(
- Left click on the Jan worksheet
- Click on D5 cell (food expense for Jan!)
- Hold down the Shift key and left click Dec worksheet
- Press Enter
- The formula should read =SUM(Jan:Dec!D5)
- Verify the calculation added all the D5 cells from Jan through Dec
Now use the autofill button to copy the formula down. Look for a tiny black cross in the lower right cell and drag down.
Want to try this on your own. Download my .xlsx workbook here. Multiple Worksheets for Excel Formula