How to Cross Reference a Cell Range in a Function
Referencing a single cell is useful enough. But you might want to write a function (such as SUM) that references a range of cells on another worksheet or workbook.
Start the function as usual and then click on the sheet and the range of cells—the same way you did in the previous examples.
In the following example, a SUM function is summing the values from range B2:B6 on a worksheet named Sales.
=SUM(Sales!B2:B6)
How to Use Defined Names for Simple Cross References
In Excel, you can assign a name to a cell or range of cells. This is more meaningful than a cell or range address when you look back at them. If you use a lot of references in your spreadsheet, naming those references can make it much easier to see what you’ve done.
Even better, this name is unique for all the worksheets in that Excel file.
For example, we could name a cell ‘ChicagoTotal’ and then the cross-reference would read:
=ChicagoTotal
This is a more meaningful alternative to a standard reference like this:
=Sales!B2
It’s easy to create a defined name. Start by selecting the cell or range of cells that you want to name.
Click in the Name Box in the top left corner, type the name you want to assign, and then press Enter.
When creating defined names, you cannot use spaces. Therefore, in this example, the words have been joined in the name and separated by a capital letter. You could also separate words with characters like a hyphen (-) or underscore (_).
Excel also has a Name Manager that makes monitoring these names in the future easy. Click Formulas > Name Manager. In the Name Manager window, you can see a list of all of the defined names in the workbook, where they are, and what values they currently store.
You can then use the buttons along the top to edit and delete these defined names.
How to Format Data as a Table
When working with an extensive list of related data, using Excel’s Format as Table feature can simplify the way that you reference data in it.
Take the following simple table.
This could be formatted as a table.
Click on a cell in the list, switch to the “Home” tab, click the “Format as Table” button, and then select a style.
Confirm that the range of cells is correct and that your table has headers.
You can then assign a meaningful name to your table from the “Design” tab.
Then, if we needed to sum the sales of Chicago, we could refer to the table by its name (from any sheet), followed by a square bracket ([) to see a list of the table’s columns.
Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:
=SUM(Sales[Chicago])
You can see how tables can make referencing data for aggregation functions such as SUM and AVERAGE easier than standard sheet references.
This table is small for the purposes of demonstration. The larger the table and the more sheets you have in a workbook, the more benefits you’ll see.
No comments:
Post a Comment