Calculations across worksheets and workbooks

Apr 1, 2021 | Excel Hints and Tips

There are many times and many reasons your data will not all be stored on the same sheet.

But what if you need to get information from one sheet to another or perform a calculation.  Microsoft Excel is designed to handle this with ease! 

Calculations in general 

Calculations always start with an equals and then the cell reference points at the location of the data. This means that when the data changes the calculations automatically update. 

My advice, whenever you are doing calculations is to click on the cell you wish to use in the calculation.  This means you are less likely to pick up the wrong cell by mistyping or misreading the correct row or column the information is coming from. 

This same advice rings true when calculating across sheets or workbooks.  In fact, it becomes even more vital. 

Calculations across worksheets 

The same principles apply when calculating across sheets.  Instead of the calculation referencing only a cell it now also references the sheet that cell is on.  This is added automatically when the cell clicked on isn’t on the current sheet. 

e.g. =Sheet1!A1 

Once the calculation is complete the ENTER or RETURN key is pressed. 

Calculations across workbooks 

When calculating across workbooks the calculation becomes longer still as it needs to reference the file, the sheet and the cell reference. 

e.g. =[Book1.xlsx] Sheet1!$A$1 

Notice also the addition of $ signs.  This indicates that the cell reference is fixed.  This is often referred to as absolute referencing or absolute addressing.  It means that if the calculation is autofilled or copied it will not move which is different to the behaviour across sheets in the same book. 

These can be manually deleted or the calculation can be selected and the function key F4 pressed until they all disappear.  This is a great shortcut to either add or remove $ signs from any calculation. 

The reason they appear in this situation is the difficulty in keeping consistent layout and order of information across books which is often required for autofilling. 

NOTE: If the files the calculation is looking at are not open then the calculation will be much longer.  Before the filename you will get the file location which will vary depending on where you have stored it on your computer. 

NOTE: If you move the source files or rename them the calculation will not be able to update because the link to the underlying data will be broken.  This can be reset by selecting the Edit links option on the Data tab, choosing change source and navigating to the new location. 

Edit links in Microsoft Excel

NOTE: When you open a file that has external links you will normally be asked to update the links.  This will update the file with any changes in data that have been made since the file was last opened.  If the file locations are no longer correct it will ask you to specify where they have been moved to in much the same way as edit links works above.  If the links are not updated then the data will be correct at the last point of updating.  This option should be used if you do not have access to the underlying data files because they are owned by someone else and stored in a location you do not have access to. 

All standard calculations can be performed across sheets and books and many functions also allow this too. 

Further Reading

If you’ve enjoyed reading about how to use the calculations across worksheets and books in Excel, there’s some other blogs below that you might find useful:

Want to learn more about other Excel features? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Courses I run.

MEMBERSHIPS

FOLLOW LARA