3.2 Use names in Excel - Video Tutorials & Practice Problems

<v Instructor>When building complex Excel models</v> with various inputs, outputs, and transitionary data, it often gets cumbersome to track and maintain explicit cell references that we reviewed in the previous sub lesson. To avoid such challenges, Excel provides a few ways to structure and manage our data references. We can use named ranges for individual cells or an array of cells, which would typically be referenced using absolute cell references. For example, input variables to a complex calculation, or maybe a list of values for a dropdown selection. For scenarios where we have tabular data sets that may need to be referenced for look-ups, calculations, etc., we typically use structured references within Excel tables. And finally, with the latest releases for Microsoft 365, dynamic arrays and the corresponding spill reference notation have been made available for more advanced reference capabilities. During this sub lesson, we will review Excel capabilities related to creating, using, updating, and reviewing named ranges. We will discuss the other two topics later in this course. Using our previous example for relative and absolute cell references, we will now create a conditional sum column where we will sum the values in Column1 with values in Column2, or the alternate Column2, based on the selection we make in cell I6. I have entered the corresponding formula using explicit cell references here. Note how we have used a mix of absolute and relative cell references to achieve our desired results. The specific reference we will focus on for this example is cell I6. Note how we have prefixed the dollar sign for both the column and row reference. This will make sure that this reference is not updated as we copy this formula down the conditional sum column or potentially into another column. We will now remove the tilde at the start of this formula and press Enter to review the result. We can then copy this formula into the other cells in this column and confirm that the results match the values in the appropriate column, based on the current selection. We can also modify our selection to confirm that the implemented logic works as expected. Now, typically such variables will be located on an input sheet within our models, and we will define names for them. This will allow us to easily reference them within formulas throughout the model. In our case, we will just use the Name box to enter the name as displayed in the header for cell I6. We can then revise the formula in our conditional sum column to use the new name that we just defined. We can also copy the formula into the other cells within this column and confirm that the results have not changed. Finally, we can modify our selection again and confirm that the results still update as expected. Now, in case we have already written some formulas with explicit cell references and would like to update them with new names that we have defined, we will select the cells with the explicit cell references, navigate to the Formulas tab, click on the arrow next to the Define button, and click on the Apply Names button. We will then select the names that we want to apply, and then click Okay. Entering any cell in the range we just edited, we can confirm that, where applicable, our explicit cell references have now been updated with the names that we just selected. Also note that by using appropriate names, our formulas are now much more readable when compared to their explicit cell reference versions. Using names thus allows us to author efficient and consistent formulas that are easy to review and support. When using names in our models, at some point we want to review or edit the defined names, or maybe we need to change the cells or ranges these defined names refer to. For this activity, we can use the Name Manager under the Formulas tab, Using this dialog box, we can create new names by clicking on the New button and then providing our desired name, the scope for this name, a comment for future reference, and the cell or range this name refers to. Similarly, we can edit or delete existing names using the corresponding buttons within the dialog box. This dialog box also provides the option to filter the names within this workbook by scope, editor status, or by type.