how to create a name range in mac excel 2013

A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed. Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.įor these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.Ī solution to this problem is to use a dynamic range name as the source for the dropdown choices. Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range). To view named ranges: Under the Insert menu, select Name > Define.This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic. Then we've entered the following data in Excel:īased on the Excel spreadsheet above, the following DAVERAGE examples would return: =DAVERAGE(orders, 'Total Cost', A1:B2) If you are unsure of how to setup a named range in your spreadsheet, read our tutorial on Adding a Named Range.įor example, we've created a named range called orders that refers to Sheet1!$A$4:$D$8.

A named range is a descriptive name for a collection of cells or range in a worksheet. You can also use a named range in the DAVERAGE function. Let's look at some Excel DAVERAGE function examples and explore how to use the DAVERAGE function as a worksheet function in Microsoft Excel:īased on the Excel spreadsheet above, the following DAVERAGE examples would return: =DAVERAGE(A4:D8, 'Unit Cost', A1:B2)

How to create a name range in mac excel 2013