Charts with Names and More Controls 0) Introduction: 1) Names for Charting: scope review 2) Special Ranges/Counting and 2 examples of Names in Charts 3) Checkboxes and Radio Buttons in Charts (2 more examples): 4) Validating Data Entry: 5) IS Information Functions: 6) Summary: 0) Introduction: In this lecture, we find that defined names, along with reference functions, are very useful in producint charts. Also, we will learn how to use check boxes and radio buttons to control such general charts. Finally, as a small added bonus we will learn how to use Excel's built-in ability for validating data (disallowing the user from entering bad data into cells), which goes beyond our use of conditional formats to indicate bad data entry. 1) Names for Charting: scope review When we create charts, we cannot use formulas directly to specify where to get information about a data series. But, we can define a name for those formulas (given what we learned in the previous lecture), and use those names in the charts. Before we see examples, first lets review some information about the scope of names, and learn some new information. When we define a name with the Workbook scope, it is available in cells on all worksheets just by typing the name. If we define a name with some worksheet name as its scope (lets say the actual worksheet name is ws), then we can type the name by itself just on that worksheet; but we can also type ws!name on any other worksheets to refer to that name. The Use in formula icon knows the scopes of all the names, and allows using just the ones that are scoped to the current worksheet or the entire Workbook. Typically it is a good idea to use the worksheet scope when defining names, if the name is to be used on just one sheet. When entering information into cells, the general form worksheet!name (and worksheet!cell or worksheet!range) allows us to refer to information scoped to a different sheet than the one whose cells we are filling in. The Filter pull-down list in the Name Manager pop-up window allows us to see just all names that are scoped to the Workbook, or see just all names that are scoped to any individual worksheets (too bad it doesn't allow us to see all the names scoped to the worksheet we are on, which is the most likely thing we want to know). The Defined Names selection means all names scoped to the workbook or any worksheet. On worksheets I1, I2, and I3, I have defined names scoped to these worksheets. -->Open the workbook for this lecture and click the Name Manager icon; notice --> how the names are displayed -->Sort them by Scope and then sort them back by Names. So, for worksheet I1 it shows 2 names, each with I1 at their right. -->Select worksheet I1 -->In cell D1 enter the formula =SUM(AllTotals) and observe that the value 267 --> appears there. AllTotals is scoped to worksheet I1, and it effectively --> specifies the range B2:B13 (we will study its details later). -->Also note, when start type the formula, say =SUM(All it will pop up a box --> with the names available for use on this worksheet -->Remove the formula from D1 -->Select worksheet I2 -->In cell K1 enter the formula =SUM(AllTotals) and observe that #NAME? --> appears there, because AllTotals is scoped to worksheet I1. -->Also note, when start type the formula, say =SUM(All no pop up box appears, --> because no name scoped to this worksheet starts with All -->Edit the formula to be =SUM(I2!AllTotals) and observe that the value 267 --> appears there. Here we are specifying the AllTotals name that is scoped --> scoped to worksheet I1, which Excel allows -->Remove the formula from K1 Note that if we change the name of a worksheet, all the defined names will change appropriately as well, to conform to the new worksheet name. -->Change the name of worksheet I1 to Foo, then use the Name Manager to examine --> the name, which are rescoped to worksheet Foo -->Rename the worksheet back to I1 2) Special Ranges/Counting and 2 examples of Names in Charts The COUNT function, when a applied to range, counts how many NUMBERS are in that range. A variant, the COUNTA function, counts the number of NON-BLANK cells in a range. In addition, the COUNTBLANK function, counts the number of BLANK cells in a range. Finally, if we specify a range like A:A (specifying a column but no rows, the range specifies the entire column. So, if we write COUNTA($A:$A) this function counts the number of NON-BLANK cells in the entire A column. Likewise we could write COUNTA($5:$5) to compute the number of non-blank cells in the 5th row. Finally, we could write COUNTA($A:$C) to count the number of non-blank cells in the rectangular region containing all of columns A, B, and C. Of course, we could also write these formulas using relative references, e.g., COUNTA(A:A) or COUNTA(5:5): the formulas calcuate the same result, the only difference is what happens when you copy and paste them (as with all relative vs. absolute references). Now we will examine two worksheets (I1-I2) that use names (computing with these functions) to create more interesting charts. -->Open the workbook for this lecture and examine the Count worksheet -->Examine the various COUNT functions in formulas in column B (labeled with --> text in column A) to see how they count the items in column E (and row --> 1, which includes information in A1, B1, E1, and G1:L1, and N1. -->Add aything in cell E16 and notice that count in cell B4 changes; same for --> adding something in cell D1 and notice the change in cell B5 Example 1: using COUNTA, OFFSET, and Names for Charting We would like to specify a chart that plots all the values in a column, even if we add values AFTER specifying this chart. We can do this by creating the names AllDates and AllTotals scoped to the I1 worksheet. In the workbook for this lecture, I have already defined the names AllDates as =OFFSET($A$2, 0,0, COUNT($A$A),1) AllTotals as =OFFSET($B$2, 0,0, COUNT($B$B),1) Note that Excel will add an 'I1'! prefix to each of the cell names refered to. In the AllDates OFFSET function we use $A$2 as the first date (using $A$2 as the reference, offset by 0,0: 0 rows and 0 columns); the number of rows (height) is COUNT($A$A), which counts the numeric cells in column A (we could also use COUNTA, but then we would have to subtract 1 because the text "Month" at the top would also be counted); and the number of columns (width) is just 1. Likewise in AllTotals we use $B$2 as the first total; the number of rows is COUNT($BA$B), which counts the numeric cells in column B. When we create the chart, we specify the Series values (in the Edit Series pop-up windows) as I1!AllTotals (which Excel translates to 'I1'!AllTotals). Actually if we type a =, we can then click the Use in Formula icon, we can choose/click either AllDates or AllTotals, the names scoped to worksheet I1. IMPORTANT: If we try to enter this value as just AllTotals, Excel will report an error and not use this name. WE MUST TYPE IT as I1!AllTotals or select it with the Use in Formula icon. In fact, if the name was scoped to the workbook, we would have to enter it as lecture9.xlsm!AllTotals (the Use in Formula icon doesn't even do the right thing here). -->Define the name Temp scoped to the entire workbook and make it refers to --> the name AllTotals; then right click on one of the bars in the chart, --> select Select Data... and then click Edit; in the Series_values box --> enter Temp (which won't work) and then enter either I1!Temp or --> lecture9.xlsm!Temp both which will work. Likewise, if we click Edit for the Horizontal (Category) Axis Labels (in the the Select Data Source pop-up window) we can enter I1!AllDates (in the ways just mentioned). Whenever we look at these entries again, we see that Excel has put singe quotes around the worksheet names. For example, the first appears as 'I1'!AllDates, the second as 'I1'!AllTotals. IMPORTANT: We cannot put Excel formulas in these charts, but we can refer to names that refer to such formulas. We will learn more about Bar Charts soon, which are almost as useful as Scatter Charts: their Y values are like in Scatter Charts, but their X values are just a list of names (here the months of a year). Now, if we add new rows (starting at row 14) they will automatically appear in the chart, because the COUNTA functions will count 1 more value. -->Open the workbook for this lecture and examine the I1 worksheet -->Add the date 1/1/2010 and 20 (on row 14) and 2/1/2010 and 30 (on row --> 15) and watch the chart automatically expand to include these values. -->Note that until a value is entered in both columns A and B, the chart --> remains unchanged Finally, note that cell E4 is used as the name for this series in the legend. It uses COUNT($A:$A) to compute the number of numeric values that appear in the column and chart. It increases from 12 to 14 after you make the changes above -->Note what would happen if we now enter the date 3/1/2010 and 35 on row --> 17 (skipping row 16): those values are not plotted. Why? Because although --> cell E4 count would show 15, the AllData OFFSET would be the columns --> A2:A16 and the AllTotal OFFSET would be the columns A2:A16. -->So, if we now enter the date 4/1/2010 and 25 on row 18, the information for --> Row 17 is charted (not displaying the empty row) IMPORTANT: Note that we CANNOT put the formula =OFFSET... in the text boxes when edit a data series; but we put in very simple formulas the cells, ranges, and names -and names refer to an arbitrarily complicated formulas. Example 2: using OFFSET and Names for Charting We would like to specify a chart (that can expand as above) that plots some number of values selected by the user (specified by a scrollbar). In this "simple" example, we always start plotting at the first value. We can do this by creating the names NDates and NTotals scoped to this worksheet. We define NDates as =OFFSET($A$2, 0,0, $E$2,1) NTotals as =OFFSET($B$2, 0,0, $E$2,1) Note that Excel will add an 'I2'! prefix to each of the cell names refered to. In NDates we use $A$2 as the first date; the number of rows is $E$2, which is the real value computed from (the same as the raw value of) the scrollbar. Likewise in NTotals we use $B$2 as the first total; the number of rows is $E$2. The raw value for this scroll bar has a minimum of 1 and a maximum of 100. This real value has a minimum of 1 and a maximum of COUNT($A:$A) (see its formula) in cell E2. So, although the scrollbar itself has 100 as its maximum value, the real value won't go beyond the amount of data on the worksheet; if, we add points in the A and B columns, the real value of the scrollbar can get bigger to accomodate all the new data. Unfortunately, we must specify actual numbers (not cells or formulas) when we set the maximum property of the scrollbar, so that is why it is set so high (100). If we put more than 100 rows of data on this worksheet, we would have to change the maximum raw value for the scroll bar. When we create the chart, we specify the Series values (in the Edit Series pop-up windows) as I2!NTotals (which Excel translates to 'I2'!) and Horizontal (Category) Axis Labels as I2!NDates. Now, if we move the scrollbar, the selected number of values will automatically appear in the chart. -->Open the workbook for this lecture and examine the I2 worksheet -->Move the scrollbar and watch the chart automatically display the correct --> amount of data. Finally, note that cell E4 is used as the name for this series in the legend. It uses E2 to compute the number of values that appear in the chart, with the root work Month following this number. Note one clever use of an IF: when we scroll to 1, this cell's value is "1 Month" not "1 Months" (but it is "2 Months", "3 Months", etc.) We use an IF in the formula to decides whether to catenate "s" or "" (nothing) after the word "Month" depending on the value in E2). 3) Checkboxes and Radio Buttons in Charts (2 more examples): We will now learn how to include checkboxes and radio buttons on a worksheet to allow us to specify whether or not to display a specific data series on a chart (or which one series of many to display). We will link the former to multiple cells and the later to a single cell, and use the values in these cells. Example 3: Checkboxes and Names for Charting We can create checkboxes to accomplish this task: click the Insert icon in the Controls group on the Developer tab, then click the checkbox icon from the Forms Control group (it is the third icon on the first line). Each checkbox is linked to a cell whose value is a BOOLEAN: whether or not the box is checked. Then we can right-click a checkbox to edit the name or click Format Control..., click the Control tab and specify a Cell link. Similarly to scroll bars, if we enter a TRUE or FALSE in the linked cell, the box will be checked or unchecked to match. On worksheet I3 I have inserted 3 checkboxes with a green background. I positioned them so they look like one big control with three checkboxes. Finally, we will use 3 named formulas (one for each series) to use these boolean value to decide whether to display their associated series or a special "blank" series. We can do this by creating the names Series1, Series2, Series3 in the scope of the I3 tab. Series1 as =IF($F$5, $B$2:$B$13, $E$2:$E$13) Series2 as =IF($F$6, $C$2:$C$13, $E$2:$E$13) Series3 as =IF($F$7, $D$2:$D$13, $E$2:$E$13) Note that Excel will add an 'I3'! prefix to each of the cell names refered to. In Series1 we use the boolean value in $F$5 to contol selecting either a real data series (Domestic, B2:B13) or a blank data series (E2:E13). Likewise in Series2 we use $F$6 to contol a real data series (C2:C13) or a blank one. Likewise for Series3. When we create the chart, we specify the Series values (in the Edit Series pop-up windows) as I3!Series1, I3!Series2, and I3!Series3 and Horizontal (Category) Axis Labels directly, as $A$2:$A$13 Now, if we check (or remove the check from) boxes, the selected series appear (or disappear) automatically in the chart. -->Open the workbook for this lecture and examine the I3 worksheet -->Check and remove the check from these boxes and watch the chart --> automatically display the requested series; enter a BOOLEAN value in F5:F7 --> and notice what changes occur to the checkboxes Of course, we could use the OFFSET function to allow these data series to grow too, as in Examples 2 and 3, but I wanted to focus on the checkbox aspect here, so I made the data series fixed. Example 4: Radio Buttons and Names for Charting We will now learn to use radio buttons on a worksheet, which allow us to specify which one data series is displayed on a chart (a choice of one among many). We can create radio buttons to accomplish this task: click the Insert icon in the Controls group on the Developer tab, then click the radio button icon from the Forms Control group (it is the sixth/last icon on the first line). Note that while we can check (and uncheck) any arbitrary combination of checkboxes, only one radio button can be active at a time: when we push a radio button (to activate it), the previous radio button we have pushed is deactivated. After creating a radio button, we can right-click to edit its name or click Format Control, and select the Control tab and specify a Cell link. Typically we link all radio buttons on a worksheet to the same cell. The first radio button that we create on a page, when pushed, will put the value 1 in that cell; the second radio button that we create on a page, when pushed, will put the value 2 in that cell; etc. Notice that each page "restarts" the numbering at 1 for radio buttons on that page. Similarly to checkboxes, if we enter a value in the linked cell, the radio button corrsponding to that number will be activated (and all others deactivated) It is often useful to create one radio button on a page and then copy/paste it on that page for as many radio buttons that are needed. Because of the copy operations, each will automatically link to the same cell; then we can change the text on each to whatever is appropriate. In fact, whenever we have a radio button defined on the worksheet, a newly created radio button on that worksheet will link to the same cell. For this example, we will use a named formula to choose the data to display, based on the current value of the linked cell. We can do so by creating the name Chosen in the scope of the I4 worksheet. We define Chosen as =OFFSET($A$2, 0, $F$5, 12, 1) Note that Excel will add an 'I4'! prefix to each of the cell names refered to. In Chosen we use the integer value in $F$5 (1, 2, or 3, depending on which radio button is active) to control how many columns over from the A column we display: if we select the Domestic radio button, we offset column A by 1 column (referring to column B, which contains the Domestic data series); if we select the Foreign radio button, we offset column A by 2 columns (referring to column C, which contains the Foriegn data series); if we select the Total radio button, we offset column A by 3 columns (referring to column D, which sums columns B and C). In each case, we select 12 rows (starting with row 2) and 1 column. Of course, we could generalize this as in Examples 1 and 2, but I wanted to focus on the radio button aspect here, so I made the data series a fixed size of 12. When we create the chart, we specify the Series values (in the Edit Series pop-up windows) as I4!Chosen and Horizontal (Category) Axis Labels directly, as $A$2:$A$13 When we create the chart, we specify for series 1 the Values as I4!Chosen and the Category (X) axis lables as just A2:A13. Now, if we activate any of the radio buttons, the selected series appears automatically in the chart. Note that the label in the legend changes as we activate different radio buttons. See cell G3, which contains the function =CHOOSE(F5,"Domestic","Foreign","Total") Recall that the CHOOSE function uses the first argument as an index, to choose a value among the rest of the arguments. If the first argument is 1, the value of the function is the 2nd argument; if the first argument is 2, the value of the function is the 3rd argument; if the first argument is 3, the value of the function is the 4ht argument. If the first argument is any othe number, the value of the function is #VALUE. CHOOSE is a compact way to write the following equivalent IF function =IF(F5=1,"Domestic", IF(F5=2,"Foreign", IF(F5=3, "Total", #VALUE))) In fact, we can also use the CHOOSE function in a name to specify which series to display. We can do so by creating the name Chosen2 in the I4 scope. We define Chosen2 as =CHOOSE($F$5, $B$2:$B$13, $C$2:$C$13, $D$2:$D$13) Note that Excel will add an 'I4'! prefix to each of the cell names refered to. Here we use the value in F5 (either 1, 2, or 3, set by the radio button) to choose whether the values in column B, C, or D are chosen. -->Open the workbook for this lecture and examine the I4 worksheet -->Activate different radio buttons and watch the chart automatically display --> the requested series (enter a value, 1-3, in cell F5 and notice what --> changes occur to the radio buttons) -->Define the name Chosen2 as specified above, and change the Series values --> be Chosen2 and notice the same behavior 4) Validating Data Entry: We have seen how to use Conditional Formatting to "validate" data: showing cells in a background color that indicates a bad value. In this section we will learn how to really validate data in Excel: not even allowing a bad value to be entered in a cell). Excel includes a mechanism that describes the legal data values that the user must enter. If we select a cell and click the Dava Validation icon in the Data Tools group of the Data tab, we can fill in three tabs in the pop-up window named Data Validation. (1) Settings: First we specify the condition for legal data. For most Allow options (Whole, Decimal, Date, Time, Text Length) we get to specify some single relational operator (Data options) on the value in the selected cell: <, <=, <>, = >=, or >. When Data is "between" or "not between" we can select' the Minimum and Maximum value. We can also select Allow to be "custom", which allows us to enter an arbitrary boolean formula (similar to one of the options in Conditional Formatting). Note that it is always legal to put nothing in a cell. (2) Input Message: Second, we can specify a message to show the user when the cell receiving the data is selected. The title (if entered) appears first in bold; the Message appears next in a normal font. Both appear in a yellow-rectangle, similar to a comment. (3) Output Message: Third, we can specify a message to show the user if he/she enters bad data (the condition in part 1 is FALSE). The title appears at the title of a pop-up windows (if it is not entered, the title becomes "Microsoft Excel"; the Message appears inside the pop-up window. We can turn on/off data validation by checking/unchecking the box "Show error alert after invalid data is entered". Sometimes we want to just disable checking, but then enable it later, withouth losing all the information associated with that cell. There are three Styles (in a list box): Stop, Warning, and Information (a) If we select Stop, and enter bad data, Excel will ring an "error tone", show us the output message with a white X in a red circle, and allow the user the options of Retry (edit the entered value) or Cancel (remove the entered value). With this Style, once you click on a cell, you cannot do anything else until you enter a legal value there (or revert back to an old legal value there, or put nothing in the cell)! (b) If we select Warning, and enter bad data, Excel will ring a "warning tone"(not quite as loud as the "error tone"), show us the output message with a black ! in a yellow triangle, and allow the user the options of leaving the bad value entered (Yes), editing the bad data (No), or removing the entered value and reverting to the previous value there (Cancel). (c) If we select Information, and enter bad data, Excel will not produce any noise, and will show us the output message with a blue i in a speech bubble, and allow the user the options of (a)leaving the bad value entered (OK), or removing the entered value (Cancel). -->Open the workbook for this lecture and examine the Validation worksheet -->Click cell A1 and follow the directions there, trying to enter both legal --> and illegal value -->Click this cell and click the Data Validation icon; notice all the --> settings -->Experiment with changing the settings and entering legal and illegal data -->Create numeric data validation on cell A2 and experiment with changing the --> settings and entering legal and illegal data Note that the Custom Formula is =NOT(ISERROR(MATCH(A1, D1:D4, 0))) First, MATCH is called to find the index of the value in cell A1 in the range D1:D4. MATCH works for text as well as it works for numbers. If this value is NOT an error, then the value is allowed. We check this by the ISERROR function, which returns FALSE if it finds A1 in D1:D4 and #N/A (an error value) if A1 is not in D1:D4; then we negate this VALUE with not, calculating whether the entered values does not cause an error. ISERROR is one Information function, others appear in the next section. 5) IS Information Functions: The IS... functions all calculate a boolean result, as follows. We have seen many uses of boolean results, including for data validation discussed above. ISBLANK value refers to an empty cell ISERR value refers to any error value except #N/A ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!) ISLOGICAL value refers to a logical/BOOLEAN value ISNA value refers to the #N/A (value not available) error value ISNONTEXT value refers to any item that is not text; note that this function returns TRUE if the value refers to a blank cell ISNUMBER value refers to a number ISREF value refers to a reference ISTEXT value refers to text We have seen how to use ISERROR in data validation. These functions can likewise be used in conditional formating and IF functions. -->Open the workbook for this lecture and examine the IS worksheet -->Enter information in cell A15 and see the results in that cell and in --> column B --> 12 --> Bob --> TRUE --> C1:C10 --> =SUM(C1:C10) 5) Summary: Here is a quick summary of skills to acquire from working on this lecture. You should be able to discuss each of these topics a bit, but more importantly know HOW TO DO something in Excel, Know the difference between named defined for an entire workbook and names defined on a specific worksheet. Know how to use the COUNT, COUNTA, and COUNTBLANKS functions for entire rows or columns (e.g., =COUNT($A:$A) Know how to build interactive charts with names, the OFFSET, COUNTA and CHOOSE functions, scrollbars, checkboxes, and radio buttons (understanding the use of the linked cells for checkboxes and radio buttons). Know how to use Excel's data validation to perform stronger checks than those we learned how to do with conditional formatting. Know that there are a variety of IS function to test the contents of cells, useful in data validation, conditional formatting, and IF functions.