Spreadsheet reports make for ineffective data models

Spreadsheet reports display highly formatted, summarized data and are often designed as presentation tools for management or executive users. A typical spreadsheet report makes judicious use of empty space for formatting, repeats data for aesthetic purposes, and presents only high-level analysis. Figure 2-3 illustrates a spreadsheet report.

Although a spreadsheet report may look nice, it doesn’t make for an effective data model. Why? The primary reason is that these reports offer you no separation of data, analysis, and presentation. You’re essentially locked into one analysis.

Although you could make charts from the report shown in Figure 2-3, it’d be impractical to apply any analysis outside what’s already there. For instance, how would you calculate and present the average of all bike sales using this particular report? How would you calculate a list of the top ten best-performing markets?

With this setup, you’re forced into very manual processes that are difficult to maintain month after month. Any analysis outside the high-level ones already in the report is basic at best — even with fancy formulas. Furthermore, what happens when you’re required to show bike sales by month? When your data model requires analysis with data that isn’t in the spreadsheet report, you’re forced to search for another dataset.

Flat data files lend themselves nicely to data models

Another type of file format is a flat file. Flat files are data repositories organized by row and column. Each row corresponds to a set of data elements, or a record. Each column is a field. A field corresponds to a unique data element in a record. Figure 2-4 contains the same data as the report in Figure 2-3 but expressed in a flat data file format.

Notice that every data field has a column, and every column corresponds to one data element. Furthermore, there’s no extra spacing, and each row (or record) corresponds to a unique set of information. But the key attribute that makes this a flat file is that no single field uniquely identifies a record. In fact, you’d have to specify four separate fields (Region, Market, Business Segment, and a month’s sales amount) before you could uniquely identify the record.

Flat files lend themselves nicely to data modeling in Excel because they can be detailed enough to hold the data you need and still be conducive to a wide array of analysis with simple formulas — SUM, AVERAGE, VLOOKUP, and SUMIF, just to name a few. Later in this chapter, you explore formulas that come in handy in a reporting data model.

Tabular datasets are perfect for pivot table–driven data models

Many effective data models are driven primarily by pivot tables. Pivot tables (which I cover in Chapter 6) are Excel’s premier analysis tools. For those of you who have used pivot tables, you know they offer an excellent way to summarize and shape data for use by reporting components, such as charts and tables.

Tabular datasets are ideal for pivot table–driven data models. Figure 2-5 illustrates a tabular dataset. Note that the primary difference between a tabular dataset, as shown in Figure 2-5, and a flat data file is that in tabular datasets the column labels don’t double as actual data. For instance, in Figure 2-4, the month identifiers are integrated into the column labels. In Figure 2-5, the Sales Period column contains the month identifier. This subtle difference in structure is what makes tabular datasets optimal data sources for pivot tables. This structure ensures that key pivot table functions, such as sorting and grouping, work the way they should.

The attributes of a tabular dataset are as follows:

• The first row of the dataset contains field labels that describe the information in each column.
• The column labels don’t pull double duty as data items that can be used as filters or query criteria (such as months, dates, years, regions, or markets).
• There are no blank rows or columns — every column has a heading, and a value is in every row.
• Each column represents a unique category of data.
• Each row represents individual items in each column.

VLOOKUP basics

Take a look at Figure 2-7 to get the general idea. The table on the left shows sales by month and product number. The bottom table translates those product numbers to actual product names. The VLOOKUP function can help in associating the appropriate name to each respective product number.

To understand how VLOOKUP formulas work, take a moment to review the basic syntax. A VLOOKUP formula requires four arguments:

VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)

• Lookup_value: The Lookup_value argument identifies the value being looked up. This is the value that needs to be matched to the lookup table. In the example in Figure 2-7, the Lookup_value is the product number. Therefore, the first argument for all the formulas shown in Figure 2-7 references column C (the column that contains the product number).
• Table_array: The Table_array argument specifies the range that contains the lookup values. In Figure 2-7, that range is D16:E22. Here are a couple of points to keep in mind with this argument. First, for a VLOOKUP to work, the leftmost column of the table must be the matching value. For instance, if you’re trying to match product numbers, the leftmost column of the lookup table must contain product numbers. Second, notice that the reference used for this argument is an absolute reference. This means the column and row references are prefixed with dollar ($) signs — as in $D$16:$E$22. This ensures that the references don’t shift while you copy the formulas down or across.
• Col_index_num: The Col_index_num argument identifies the column number in the lookup table that contains the value to be returned. In the example in Figure 2-7, the second column contains the product name (the value being looked up), so the formula uses the number 2. If the product name column were the fourth column in the lookup table, the number 4 would be used.
• Range_lookup: The Range_lookup argument specifies whether you’re looking for an exact match or an approximate match. If an exact match is needed, you’d enter FALSE for this argument. If the closest match will do, you’d enter TRUE or leave the argument blank.

Applying VLOOKUP formulas in a data model

As you can imagine, there are countless ways to apply a VLOOKUP in all kinds of analyses. Let’s take a moment to walk through a scenario where using a VLOOKUP can help enhance your dashboard model.

With a few VLOOKUP formulas and a simple drop-down list, you can create a data model that not only delivers data to the appropriate staging table, but also allows you to dynamically change data views based on a selection you make. Figure 2-8 illustrates the setup.

To see this effect in action, get the Chapter 2 Samples.xlsx workbook from this book’s companion website. Open that workbook to see a VLOOKUP1 tab.

The data layer in the model shown in Figure 2-8 resides in the range A9:F209. The analysis layer is held in range E2:F6. The data layer consists of all formulas that extract and shape the data as needed. As you can see, the VLOOKUP formulas use the Customer Name value in cell C3 to look up the appropriate data from the data layer. So if you entered Chevron in cell C3, the VLOOKUP formulas would extract the data for Chevron.

You may have noticed that the VLOOKUP formulas in Figure 2-8 specify a Table_array argument of $C$9:$F$5000. This means that the lookup table they’re pointing to stretches from C9 to F5000. That seems strange because the table ends at F209. Why would you force your VLOOKUP formulas to look at a range far past the end of the data table?

Well, remember that the idea behind separating the data layer and the analysis layer is so that the analysis layer can be automatically updated when the data is refreshed. When you get new data next month, you should be able to simply replace the data layer in the model without having to rework the analysis layer. Allowing for more rows than necessary in your VLOOKUP formulas ensures that if the data layer grows, records won’t fall outside the lookup range of the formulas.

Later in this chapter, I show you how to automatically keep up with growing data tables by using smart tables.

Using data validation drop-down lists in the data model

In the example illustrated in Figure 2-8, the data model allows you to select customer names 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 analytical layers in which each shows a different set of analyses. Although each analysis layer is different, they often need to revolve around a shared dimension, such as the same customer name, the same market, or the same region. For instance, when you have a data model that reports on Financials, Labor Statistics, and Operational Volumes, you want to make certain 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 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 relatively easy with Excel’s Data Validation functionality. To add a drop-down list, follow these steps:

1. Select the Data tab on the Ribbon.
2. Click the Data Validation button.
3. Select the Settings tab in the newly activated Data Validation dialog box. (See Figure 2-9.)
4. In the Allow drop-down list, choose List.

5. In the Source input box, reference the range of cells that contain your predefined selection list.

   In our example, this would be the list of customers you want exposed through the dashboard.

6. Click OK.

HLOOKUP basics

Figure 2-10 demonstrates a typical scenario in which HLOOKUP formulas are used. The table in C5 requires quarter-end numbers (March and June) for 2011. The HLOOKUP formulas use the column labels to find the correct month columns and then locate the 2011 data by moving down the appropriate number of rows. In this case, 2011 data is in row 4, so the number 4 is used in the formulas.

To get your mind around how this works, take a look at the basic syntax of the HLOOKUP function.

HLOOKUP(Lookup_value, Table_array, Row_index_num, Range_lookup)

• Lookup_value: The Lookup_value argument identifies the value being looked up. In most cases, these values are column names. In the example in Figure 2-10, the column labels are being referenced for the Lookup_value. This points the HLOOKUP function to the appropriate column in the lookup table.
• Table_array: The Table_array argument identifies the range that contains the lookup table. In Figure 2-10, that range is B9:H12. Like the VLOOKUP examples earlier in this chapter, notice that the references used for this argument are absolute. This means the column and row references are prefixed with dollar ($) signs — as in $B$9:$H$12. This ensures that the reference doesn’t shift while you copy the formula down or across.
• Row_index_num: The Row_index_num argument identifies the row number that contains the value you’re looking for. In the example in Figure 2-10, the 2011 data is located in row 4 of the lookup table. Therefore, the formulas use the number 4.
• Range_lookup: The Range_lookup argument specifies whether you’re looking for an exact match or an approximate match. If an exact match is needed, you’d enter FALSE for this argument. If the closest match will do, you’d enter TRUE or leave the argument blank.

Applying HLOOKUP formulas in a data model

HLOOKUPs are especially handy for shaping data into structures appropriate for charting or other types of reporting. A simple example is demonstrated in Figure 2-11. With HLOOKUPs, the data shown in the raw data table at the bottom of the figure is reoriented in a staging table at the top. When the raw data is changed or refreshed, the staging table captures the changes.

SUMPRODUCT basics

The SUMPRODUCT function is designed to multiply values from two or more ranges of data and then add the results together to return the sum of the products. Take a look at Figure 2-12 to see a typical scenario in which the SUMPRODUCT is useful.

In Figure 2-12, you see a common analysis in which you need the total sales for the years 2011 and 2012. As you can see, to get the total sales for each year, you first have to multiply Price by the number of Units to get the total for each Region. Then you have to sum those results to get the total sales for each year.

With the SUMPRODUCT function, you can perform the two-step analysis with just one formula. Figure 2-13 shows the same analysis with SUMPRODUCT formulas. Rather than use 11 formulas, you can accomplish the same analysis with just 3!

The syntax of the SUMPRODUCT function is fairly simple:

SUMPRODUCT(Array1, Array2, …)

Array: Array represents a range of data. You can use anywhere from 2 to 255 arrays in a SUMPRODUCT formula. The arrays are multiplied together and then added. The only hard-and-fast rule you have to remember is that all arrays must have the same number of values. That is to say, you can’t use the SUMPRODUCT if range X has 10 values and range Y has 11 values. Otherwise, you get the #VALUE! error.

A twist on the SUMPRODUCT function

The interesting thing about the SUMPRODUCT function is that it can be used to filter out values. Take a look at Figure 2-14 to see what I mean.

The formula in cell E12 is pulling the sum of total units for just the North region. Meanwhile, cell E13 is pulling the units logged for the North region in the year 2011.

To understand how this works, take a look at the formula in cell E12, shown in Figure 2-14. That formula reads SUMPRODUCT((C3:C10="North")*(E3:E10)).

In Excel, TRUE evaluates to 1 and FALSE evaluates to 0. Every value in column C that equals North evaluates to TRUE or 1. Where the value is not North, it evaluates to FALSE or 0. The part of the formula that reads (C3:C10="North") enumerates through each value in the range C3:C10, assigning a 1 or 0 to each value. Then internally, the SUMPRODUCT formula translates to

(1*E3)+(0*E4)+(0*E5)+(0*E6)+(1*E7)+(0*E8)+(0*E9)+(0*E10).

This gives you the answer of 1628 because

(1*751)+(0*483)+(0*789)+(0*932)+(1*877)+(0*162)+(0*258)+(0*517)

equals 1628.

Applying SUMPRODUCT formulas in a data model

As always in Excel, you don’t have to hard-code the criteria in your formulas. Rather than explicitly use "North" in the SUMPRODUCT formula, you could reference a cell that contains the filter value. You can imagine that cell A3 contains the word North, in which case you can use (C3:C10=A3) instead of (C3:C10="North"). This way, you can dynamically change your filter criteria, and your formula keeps up.

Figure 2-15 demonstrates how you can use this concept to pull data into a staging table based on multiple criteria. Note that each of the SUMPRODUCT formulas shown here references cells B3 and C3 to filter on Account and Product Line. Again, you can add data validation drop-down lists to cells B3 and C3, allowing you to easily change criteria.

CHOOSE basics

Figure 2-16 illustrates how CHOOSE formulas can help pinpoint and extract numbers from a range of cells. Note that instead of using hard-coded values, like Red, Green, and so on, you can use cell references to list the choices.

Take a moment to review the basic syntax of the CHOOSE function:

CHOOSE(Index_num, Value1, Value2, …)

• Index_num: The Index_num argument specifies the position number of the chosen value in the list of values. If the third value in the list is needed, the Index_num is 3. The Index_num argument must be an integer between one and the maximum number of values in the defined list of values. That is to say, if there are ten choices defined in the CHOOSE formula, the Index_num argument can’t be more than ten.
• Value: Each Value argument represents a choice in the defined list of choices for that CHOOSE formula. The Value> arguments can be hard-coded values, cell references, defined names, formulas, or functions. You can have up to 255 choices listed in your CHOOSE formulas.

Applying CHOOSE formulas in a data model

The CHOOSE function is especially valuable in data models in which multiple layers of data need to be brought together. Figure 2-17 illustrates an example in which CHOOSE formulas help pull data together.

In this example, you have two data tables: one for Revenues and one for Net Income. Each contains numbers for separate regions. The idea is to create a staging table that pulls data from both tables so that the data corresponds to a selected region.

To understand what’s going on, focus on the formula in cell F3, shown in Figure 2-17. The formula is CHOOSE($C$2,F7,F8,F9,F10). The Index_num argument is actually a cell reference that looks at the value in cell C2, which happens to be the number 2. As you can see, cell C2 is actually a VLOOKUP formula that pulls the appropriate index number for the selected region. The list of defined choices in the CHOOSE formula is essentially the cell references that make up the revenue values for each region: F7, F8, F9, and F10. So the formula in cell F3 translates to CHOOSE(2, 27474, 41767, 18911, 10590). The answer is 41,767.