Insights

Occasionally when you are working with a lot of tabs it helps to have a reference to the tab name on the worksheet. An example might be a workbook containing financials for 100 restaurants.

Tips for working with data across multiple worksheets in the same Excel workbook.
The single biggest requirement to make maximum use of these tricks is to have all of your data laid out in identical format across tabs.

The =INDIRECT function permits referencing information in a workbook using strings of text. This can be a very handy tool when you want to create dynamic references in formulas without changing the formulas themselves. It is also a terrific way to reference data on different worksheets.

This video explains how to use SUMIF and the INDEX + MATCH function combination in Excel to dynamically reference columns in a financial model. This has been particularly useful to me when I am organizing a company’s data by location or product category.

This video provides a quick introduction to the =SUMIF() function in Microsoft Excel. The video also provides a quick demonstration of what is likely the most common use of =SUMIF() in a financial model: organizing monthly information into quarters and annual periods (skip to 1:15 for this example).