While other spreadsheet software like Microsoft Excel and LibreOffice Calc allow for easy calculating across sheets, Google Sheets is a little more work. Let’s dive into how you can calculate across sheets in Google Sheets.
Monthly Expenses Example in Google Sheets
A great example of calculating across sheets might be when calculating monthly expenses. Here we have a Google Sheet open with two sheets, one for fixed expenses, where we see a list of some fixed monthly costs like rent, cable, internet, phone and auto insurance. These are typical monthly expenses where we will know the exact dollar amount as they are fixed from month to month.
Now we can calculate these fixed costs and come up with a total fixed cost to use across other sheets, where we will also add in variable costs to calculate the total monthly expense. Whether you do that calculation here or on the monthly sheet, the use of SUM() is typically what works for a straight forward totally of a range of numbers. So, if we do this on the fixed costs sheet we would use:
=SUM(C4:C8)Then on the monthly sheet, in this example November, for the fixed cost we would enter:
='Fixed Costs'!C10
Alternatively, we can use the sum() function directly on the monthly sheet. In this example that’s the November sheet. However we cannot select the range on the Fixed Costs sheet, so we will need to manually add in the range. So in this example it will look like this:
=SUM('Fixed Costs'!C4:'Fixed Costs'!C8)Note: The use of the exclamation mark between the sheet name in quotes and the cell simply says to look at that cell on that sheet. It’s a reference pointer.
So in conclusion, while not as straight forward as other office setups, you can in fact calculate across sheets or reference cells from another sheet in Google Sheets.