Save yourself from hours of manual calculations: 3D equations

3D equations just sounds like James Cameron created them during the filming of Avatar. However, these handy equations are how any individual is able to EASILY summarize information across tabs .

3D Equations

Here is how they are formatted and how they work.

Many may know that in the fun language of Excel you are able to reference different tabs when typing out functions, these look like: =SUM( Sheet2!A1:A67)

Where you are not adding the cells on your current tab, but rather the cells on the tab Sheet2.

3D equations do something very similar, but rather than only calculating the cells on another tab, you are calculating the cells in the same location across tabs within the same workbook.

If you had four sheets with information in the cells A1:A67 and you wanted them added together, you would list the first tab and the last tab in the same format you would with the first cell and last cell in an otherwise normal equation. Here is an example>> =SUM(Sheet1:Sheet6!A1:A67).

This 3D equation will summarize all of the information in A1 through A67 on all of the tabs that are placed between Sheet1 and Sheet6.

Do you see how helpful this is!!?? You can just insert more and more sheets between these two tabs and this equation will just add them up over and over.

To help explain this concept lets start with a scenario. It is 4:15 on Friday afternoon and you have just been emailed a number of regional sales projections all formatted the exact same way that need to be summarized and sent out to the SVP by end of day…how do you add them all together without losing the detail and still leave before 5?

First Step:

combine all of the files into one excel sheet. You can just drag and drop them or right click the tab and move or copy them into a single sheet with one tab at the beginning labeled “Summary” with the same format as all of the others.

Second Step:

Enter two blank tabs before and after the tabs you would like to summarize and name them “first” and “last.”

Third Step:

Go to the “Summary” tab and for each cell you would like to summarize create an equation that looks something like =SUM(first:last!A1)
And the thing is if you need to repeat that equation in different cells, just copy, paste, and let relative cell reference change the cells for you!

 

From there just copy the equation to all of the cells you need to summarize, do a few quick spot checks to make sure nothing looks fishy, and boom send it out to the SVP and enjoy your weekend.

No more manual entries as sales reps submit forecasts, no more hand keying budgets from different departments, you will be able to create a format that works for everyone and then make it easy for you to add them all together by dragging and dropping their submission into this sheet between the first and last tab in the equation.

 

 

 

Leave a Reply

Your email address will not be published.