Exercise 3: Create a No-code, Data-bound Interactive Display in Excel

Exercise 2 demonstrated a powerful technique for displaying data in a dynamic fashion. For many scenarios, displaying data in a table such as this is sufficient. Other scenarios however, require more control over the presentation of the data. In this type of scenario, you can design the layout on a separate worksheet and create formulas to retrieve data from the query table as needed. In this exercise, you will create the forecast/budget report by mating the forecast worksheet to the table on the Data worksheet.

Task 1 – Add Formulas to the Forecast worksheet

1. Switch to the Forecast worksheet. Note that some basic setup work is complete.
   1. The income statement has been formatted as desired
   2. Sub-totals have been added where appropriate
   3. Unnecessary columns and rows have been hidden
   4. Account ID’s corresponding to the account used in a given row have been added to column A. This is to avoid having to hard-code these in formulas.
   5. Column offsets are located in row one. This is to avoid having to hard-code these in formulas.
2. Select cell G12 and enter the formula: =VLOOKUP($A12,Table_Query_from_Budget_Database[#All],Forecast!G$1,FALSE)
3. Select the range G12:R12 and press CTRL+R to fill the formula from cell G12 to the rest of the cells through December
4. With the range G12:R12 still selected, grab the drag handle in the lower right corner of cell R12 and drag fill the formula down to row 16
5. Select the range G12:R12 and press CTRL+C to copy the range
6. Select cell G20 and press CTRL+V to paste the formulas
7. With the range G20:R20 still selected, grab the drag handle in the lower right corner of cell R20 and drag fill the formula down to row 33
8. Select the range G12:R12 and press CTRL+C to copy the range
9. Select cell G37 and press CTRL+V to paste the formulas
10. With the range G37:R37 still selected, grab the drag handle in the lower right corner of cell R37 and drag fill the formula down to row 60
11. Test your work:
    1. Note the total Net Income in cell S63. This value should be $47,252 using the parameter values of Store=1, Year=2009, and Scenario=Actual.
    2. Switch back to the ForecastData worksheet and change the Year to 2008
    3. Observe that the data refreshes with data from 2008.
    4. Switch back to the Forecast worksheet
    5. Note the total Net Income value in cell S63. This value should be $33,284 using the parameter values of Store=1, Year=2008, and Scenario=Actual.

Task 2 – Add Interactivity to the worksheet

1. In cell E6, enter the value STORE
2. In cell E7, enter the value YEAR
3. In cell E8, enter the value SCENARIO
4. Add data validation to cell F6
   1. Select cell F6
   2. On the Data tab of the ribbon, click Data Validation
   3. Choose List from the Allow drop-down box
   4. Enter the values 1,2,3,4,5,6,7 in the Source text box
   5. Click OK
5. Similarly, add data validation to cell F7 using the values 2008,2009 in the Source text box
6. Add data validation to cell F8 using the values Actual, Budget in the Source text box
7. Set the values of the drop-downs to the following values:
   1. Store=1
   2. Year=2009
   3. Scenario=Actual
8. Switch to the ForecastData worksheet and enter the following formulas:
   1. In cell C1: =Forecast!F6
   2. In cell C2: =Forecast!F7
   3. In cell C3: =Forecast!F8
9. Add Sparklines to the Forecast worksheet
   1. Switch back to the Forecast worksheet
   2. Select cell F12
   3. From the Insert tab in the Sparklines group, select Line

   4. In the Create Sparklines dialog box, set the Data Range to G12:R12 and the Location Range to $F$12. Click OK.

      Figure 1

      Create Sparklines

   5. Fill cell F12 down to other rows containing data
10. Save the workbook
11. Test your work:
    1. Choose store 2 from the drop-down list in cell F6. Observe that Excel refreshes the value of the cells.
    2. Choose 2008 from the drop-down list in cell F7. Observe that Excel refreshes the value of the cells.
    3. Choose Budget from the drop-down list in cell F8. Observe that Excel refreshes the value of the cells.
12. (OPTIONAL) Add some polish to the worksheet:
    1. Right-click on the heading for row 1 and select Hide to hide the row
    2. Select columns A:C
    3. Right-click and select Hide to hide the columns
    4. Click on the View tab in the ribbon
    5. Uncheck Gridlines, Headings, and Formula Bar
    6. Press Ctrl + F1 to minimize the ribbon

Exercise 3 Verification

Assuming all of the “Test your work” steps in the exercise were successful, you should be looking at an interactive worksheet that resembles Figure 1. As you change the values for Store, Year, and Scenario you should see the entire worksheet refresh as Excel retrieves the values from the database on the Forecast Data worksheet and then recalculates to display the correct values on the Forecast worksheet.