Excel Guides

Unbreakable Formula References to Worksheets in Excel

When it comes to working with formulas in Excel, one of the most important things to keep in mind is how to reference other worksheets within your formulas. This is especially important when you have a large workbook with multiple worksheets, as it can be easy to lose track of which cell belongs to which worksheet. In this article, we'll take a look at how to reference other worksheets in your formulas, so that you can always be sure that your formulas are accurate.

The first thing to know is that there are two ways to reference another worksheet in a formula: by name or by index number. If you know the name of the worksheet you want to reference, you can simply use that name in your formula, enclosed in single quotes. For example, if you wanted to sum the values in cells A1 and B1 on a worksheet named "Sheet2", you would use the following formula:

=SUM('Sheet2'!A1:B1)

If you don't know the name of the worksheet you want to reference, or if you want to be able to change the worksheet name without having to update your formulas, you can reference the worksheet by its index number. The index number is the position of the worksheet within the workbook, with 1 being the first sheet and 2 being the second sheet, and so on. So, if we wanted to sum the values in cells A1 and B1 on the second sheet in our workbook, we would use this formula:

=SUM(Sheet2!A1:B1)

You can also use mixed references, where some parts of the reference are static (i.e., they don't change) and other parts are dynamic (i.e., they can change). For example, let's say that we have a workbook with three sheets: "Sales", "Returns", and "Total". We want our "Total" sheet to always show the sum of sales and returns for each month. To do this, we could use a mixed reference that looks like this:

=SUM('Sales'!A1:A10,'Returns'!B1:B10)

In this case, our static part is 'Sales'!A1:A10 (which will always refer to cells A1 through A10 on our "Sales" sheet) and our dynamic part is 'Returns'!B1:B10 (which will refer to cells B1 through B10 on whatever sheet is currently named "Returns"). This way, we can change the name of our "Returns" sheet from month to month (e.g., "January Returns", "February Returns", etc.) without having to update our formula.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.