Hi friend! This is for my Excel enthusiasts who have grown weary of my diversification. This is also for entrepreneurs with multiple side hustles who use Excel to track business information.
For my TL;DR (too long; didn't read) folks:
You track sales in an Excel workbook and you place each month's sales in a different sheet.
At the end of each month, you add up the sales to get Total revenue which you place at the top of each respective sheet
You create a summary sheet that calculates the overall sales using the SUM function:
=SUM(Jan_revenue,Feb_revenue_March_revenue,...)Do this instead: Make sure the Total Revenue in each month is placed in the same cell, use the SUM function to calculate overall sales, click in the Total Revenue cell in the sheet of the first month, hold Shift and click on the last month; press ENTER. Done!
If you are still confused, take a seat:
Scenario
You have a massive spreadsheet with a sheet for each month tracking the total income for that month. You also have a summary sheet at the beginning of your workbook that shows the overall income. I want to show you how you can add up the total income across the months faster than the average Joe (I am sorry for this stray bullet, Joe).
Get the data
Here is the file we will use for this exercise so you can follow along (I made up the numbers for the sake of illustration, so imagine the workbook has more data):
If you like reading without doing the work, the workbook comprises 4 sheets: Summary, Jan, Feb and March. Each month has the total revenue for that month above a table collecting the Date, Invoice Number and Amount. The Summary sheet captures only the overall sales (Total Sales).
How does regular Joe compute Total Sales?
Regular Joe sets up his workbook similarly to ours and enters sales information daily every month before computing Total Revenue using the SUM function at the end of each month. Joe computes overall sales by using the SUM function to add the values in Jan, Feb and March by typing this formula in the cell near Total Sales in the Summary sheet:
=SUM(Jan!B3,Feb!B3,MarchB3!)
How YOU should compute Total Sales
While Joe's way is fast enough (especially since we are just in March), it could get tedious doing this in December. A couple of things we should change are:
Convert all the sales tables in each month to special Excel tables by clicking anywhere in the table and pressing Ctrl + T (⌘ + T on Mac) and clicking OK in the "Create Table" window that pops up
2. Compute the Total revenue each month by using the SUM function and taking advantage of the features of the special table. This way the Total Revenue updates automatically when more rows are added to the table. I wrote about Tables here if you are wondering what I am talking about
3. Change the way the formula is entered in the Summary sheet:
We change the formula by initiating the SUM function in the cell near Total Sales, clicking in the Total Revenue cell in the sheet of the first month (Jan), holding Shift and clicking on the last sheet (March) and pressing ENTER (return for Mac). Done!
Important to note 💡
In order for the sum across sheets to work well, all the amounts (Total Revenue in this case) from the separate sheets that are used to calculate the grand total (Total Sales in this case) must be entered in the same cell(s). Notice all Total Revenue is placed in cell B3 in all the sheets.
Always use special Excel Tables; they will make your life easier
While this feature is not available in Google Sheets, entering the formula manually works:
=SUM('Jan:March'!B3)Conclusion
Calculating the sum across multiple sheets in this way is not only more efficient but also a testament to the importance of formatting your workbooks well and ensuring they are uniform.
I hope your side hustle gained from this.
Have a great week!
Comments