Excel Tips for Private Equity: Sheet References

What are Sheet References?

Sheet References are when you reference a singular cell or a range of cells in another worksheet in the same workbook or a different workbook. When you want to reference one specific cell, you would use the following formula : 

Sheet_name!Cell_address

For example, if we wanted to reference cell C4 in Sheet 6, the formula would be Sheet6!C4.

For a range of cells, you would use this formula : 

Sheet_name!First_cell:Last_cell

Let’s say you wanted to reference cells B1 to B10 in Sheet 4. The formula would be Sheet4!B1:B10. 

For a worksheet name that has non-alphabetical characters or spaces, you must include singular quotation marks around the workbook name. For example, a reference to cell B1 in a worksheet named Hotel Development Model should read: 'Hotel Development Model'!B1. For a range of cells such as B1 to B10, the formula would be ‘Hotel Development Model’!B1:B10. 

If you are writing a formula and click into a different tab and then back into your current tab, Excel will show the sheet reference of the current tab.  A best practice is to not have a sheet reference for the active tab, so you would need to delete the reference if that happens.

Sheet Reference vs. Cell Reference 

Sheet references exclusively reference another cell or range of cells in another sheet and are considered external references. Cell references are located within the sheet and can be absolute (designated with dollar signs) or relative (changes relative to position). 

Sheet Reference in Real Estate

In our financial models, the formulas often reference cells from other workbooks from within the model. This ensures the calculations are consistent and accurate throughout the model.  

Conclusion

In conclusion, Sheet References are when you reference cells or a range of cells in another worksheet. This can be useful in Real Estate, when working with financial models that require multiple references to other worksheets or in everyday use, when you want to keep formulas constant and exact. 

---

About the Author

Brittany Martin is TSM’s Vice President who has developed real estate financial models for an extensive range of property types. She specializes in land, hotel, and storage models. Please reach out to her if you have any questions about Unlevered Cash Flow or if she can help you with your modeling needs.