Microsoft Office Tutorials and References
In Depth Information
Excel Functions for Your Data Model
Using drop-down lists
In the example illustrated in Figure 11-8, the data model allows you to select customer names (that
is, the AccountName field) from a drop-down list when you click cell C3. The customer name serves
as the lookup value for the VLOOKUP formulas. Changing the customer name extracts a new set of
data from the data layer. This allows you to quickly switch from one customer to another without
having to remember and type the customer name.
Now, as cool as this seems, the reasons for this setup aren’t all cosmetic. There are practical reasons
for adding drop-down lists to your data models.
Many of your models consist of multiple analysis layers. Although each analysis layer is different, the
layers often need to revolve around a shared dimension, such as the same customer name, the
market, or the region. For instance, when you have a data model that reports on Financials, Labor
Statistics, and Operational Volumes, you want to ensure that when the model is reporting Financials
for the South region, the Labor Statistics are for the South region as well.
An effective way to ensure that this happens is to force your formulas to use the same dimension
references. If cell C3 is where you switch customers, every analysis that is customer-dependent should
reference cell C3. Drop-down lists allow you to have a predefined list of valid variables located in a
single cell. With a drop-down list, you can easily switch dimensions while building and testing
multiple analysis layers.
Adding a drop-down list is a relatively easy thing to do with Excel’s Data Validation functionality. To
add a drop-down list:
1. Click the Data tab on the Ribbon.
2. Click the Data Validation button.
3. In the Data Validation dialog box, click the Settings tab (see Figure 11-9).
4. In the Allow drop-down list, select List.
5. In the Source box, specify the range of cells that contain your predefined selection list.
6. Click OK.
Figure 11-9: You can use data validation to create a predefined list of valid variables for your data