Intermediate Worksheets, Cells, and Data Tables 1) Introduction: 2) The Form of Worksheets: 3) Comments/Styles (multiple formats) for Cells 4) Input Scrollbars: 5) Computing Data Tables: 6) Conditional Formatting: 7) Protecting Cells in Worksheets: 8) Selecting Many Cells at Once/Selecting Cells by Contents: 9) Summary: 1) Introduction: This lecture discusses a few simple but disparate topics. We will learn a) about the general form of worksheets b) how to associate comments with cells c) how to use scroll-bars for input d) how to create 1-d and 2-d data tables e) how to conditionally format cells (related to their and other cells) f) how to protect cells on a worksheet (so users cannot modify them) This is a long lecture that we will cover over two days. On the first We'll cover sections 1-4; on the second we'll cover sections 5-9. 2) The Form of Worksheets: Some books on using Excel in Science and Engineering present a general form for all worksheets. Going from top to bottom they present information in the following order: 1) Title & Explanations 2) Constants 3) Inputs 4) Calculations or 4) Outputs (cells/charts) 5) Outputs(cells/charts) 5) Calculations The title might document what this worksheet is about, who originally developed and who has changed it (an audit of who has worked on the worksheet and how they changed it), when it was written (last changed), etc. The explanation could be a short paragragh that explains the problem in more depth and how to solve it using the tools on the worksheet. Constants are values that never change. At the end of this lecture, we will learn how to lock the values of cells so that they cannot be changed. So, we can lock constant cells to ensure the user of a worksheet cannot accidentally -or maliciously- change their values. Typically we label such constants so that the worksheet user knows the meaning of them, and we might also label their units. Inputs are values that the user can change. Often a worksheet calculates some output values based on some input values; we want to see what outputs are produced for different inputs. Some worksheets adopt special formatting for cells that we enter inputs into: e.g., formatting them with a special background color or border (e.g., double boxed). Typically we label such inputs so that the person using the worksheet knows the meaning of every possible input. Calculations are cells (maybe one, maybe hundreds) that transform inputs into outputs. Typically a model is just a collection of rules for computing outputs from inputs. Calculations might include many cells holding "temporary" results as well as real outputs. Often a model that simulates outputs changing over time have one row per time step (with the last row holding the final output values: we have seen this approach in previous lectures and labs). Outputs are interesting values that are calculated by formulas. We can display outputs as text values, or graphically show these values in some kinds of charts. We enter models in worksheets to calculate outputs for specific inputs. By choosing a good form to display the output, we may also be able to understand a model better: make the qualitative or quantitative relationships between the model's inputs and outputs clearer. One common task with a model is to try to find inputs that "optimize" one of the outputs. Typically we want to emphasize the outputs: sometimes by putting them right after the inputs, sometimes by placing them at the end of the worksheet. In fact, sometimes we might put just the title, input, and outputs on one worksheet, and put the constansts and calculation on a different worksheet (to be out of the way). 3) Comments/Styles (multiple formats) for Cells We can put comments into any cells, for describing the value or fomulas in that cell in an unintrusive way. These comments are indicated by a small red triangle in the upper-right hand corner of a cell. By hovering over the cell (moving the mouse over the cell), we can read the comment. By right-clicking a cell we will be presented with options that include the ability to Insert a comment in that cell (if none is there) or Edit the comment in that cell or Delete the comment from that cell (if a comment is already there). When we Insert or Edit the contents of a cell, we can also change the size of the rectangle containing the comments -->Open the the workbook distributed with this lecture. -->On the Form worksheet; hover over cells A6 and A9 (see the difference in --> their backgrounds) -->Practice manipulating the comments: edit one of these comments, change the --> size of its rectangular background, and hover over it again; then delete --> the comment; insert a comment in a cell that doesn't already contain one Now we move onto looking at cell formats -and how to change them- in more details. -->Select some cell and then right-click it and select Format Cells... Notice tha a pop-up window named Format Cells appears, with 6 tabs showing. The Number, Alignment, and Font tabs refer to how information inside the cell is displayed. The Border and Fill tabs refer to how the cell itself (even with no information inside it) is displayed. -->Explore the Fill and Border tabs (trying to duplicate in cells A35, A37, --> and A39 the formats in cells A25, A27, and A29) -->In the Boarder tab, click a Line Style (the last one on the right is a --> double line); click the Color pull-down list and click a color; either --> click the Outline (from Presets, which puts line on the top/bottom and --> left/right of a cell), or click individual positions in the cell (or the --> icons around the border, which toggles whether horizontal, vertical, or --> diagonal lines appear -->When you see the pattern you want click OK; notice the result for that cell If we are going to reuse the same formats for many cells, we can put those formats in a library of Cell Styles and easily apply any of these style to cells we select. First, we will see how to use the styles already in this library; then we will see how to add, modify, and delete our own styles. -->Select a cell that contains some information -->Click the Cell Styles icon in the Styles group on the Home tab; notice --> a matrix of styles -->Hover over any style; notice that the selected cell will take on that style, --> so you can see what it would look like with that style (all its formats) -->Double click a style (or right-click one and click Apply) and Excel applies --> that style (all its formats) to the selected cell -->Select a cell that contains a number and hover over the Number Format styles --> (at the bottom of the matrix) We can examine the format details of these cell styles by right clicking a cell and clicking Modify. A pop-up window named Style appears. The checkboxes include those formats that will be applied; so if a box is not checked, when this style is applied, it won't affect that kind of format. In fact the checked boxes are followed by some information detailing that attribute's value for this style. We can click the Format... button to see all the details of the formats for that style. When looking at the matrix of styles, we have 2 options; the most important is New Cell Style... (second from the bottom on the left). If we click this option, a pop-up window named Style appears, with all its boxes checked. We can first enter the the name of the style in the Style Name text box, then we can uncheck the boxes that are not part of the new style that we are creating; then we click Format... and set the Number, Alignment, Font, Border, Fill, or Protection formats for that style. Then we click OK for the formats and click OK again for creating the new cell. Now when we click the Cell Styles icon, at the top (under the heading Custom) is the name we gave the cell style that we created (and it "looks like" that style). Of course we can create more styles (the styles are displayed in alphabetical order, using more than one line if there are many custom cells), and we can modify/delete any style from this matrix by right-clicking it and clicking modify (and making the modifications) or clicking delete. If we want to make a slight variant of some style that already exist (whether it is standard or custom) we can right click that style, select Duplicate... and then name the new style and change whatever formats we want for that style. Excel will add it as a new custom style -->Create a custom style, apply it to some cells, and then finally delete that --> style 4) Input Scrollbars: It is sometimes useful to enter information into input cells with some kind of graphic control, rather than type values into the cells. For example, such a control makes it easier to quickly sequence through a series of values for an input cell, and observe the results (most easily through watching a chart change, as we have seen in the Interest Rate Comparisons chart). In Excel, we can put scrollbars into worksheets and use them to control the values appearing in cells. To create a scrollbar, we must first click the Developer tab. If you do not see the Developer tab... -->Click the Office Button and then click Excel Options -->The third checkbox from the top should not be checked; click it to check it -->Click OK; notice the Developer tab appear between the View and Add-Ins tab -->Click the Developer Tab The Controls group (second on that tab) has two leftmost icons: the toolbox icon on the far left and directly to its right an icon that looks like a pencil (to the right of a triangle, above a ruler). Clicking the first icon will allow us to Insert controls (like scrollbars) into our worksheet; clicking the second icon toggles "Design Mode" on/off. If we are in "Design Mode" the "Design Mode" icon will show with an orange background; if we are NOT IN "Design Mode", the "Design Mode" icon will have the regular blue backgound. If this icon is gray, and not selectable, it is because we have not yet inserted any scrollbars on the worksheet, or any other controls that "Design Mode" can affect. Clicking the "Design Mode" icon toggles between these two modes. When we are working with scrollbars, we will frequently find ourself toggling this icon, so it is an excellent idea to put it (maybe, along with the toolbox icon labeled Insert) on the QAT. When we are in "Design Mode", we can select a scrollbar, reposition it, and change its properties; when are NOT in this mode, we can actually use the scrollbar to scroll through different values. The standard way to lay out a scrollbar includes seven cells, arranged in two rows and three columns. For example A B C D 1 raw ulsb ursb 2 real llsb lrsb [a,b] by c The top of the first column (A1) contains one cell for the "raw" value of the control (which is always a non-negative integer), and underneath it (A2) one cell for the "real" value of the control (which can be anything: it is computed from the raw value). To the right of these cells is a rectangle of four cells (B1:C2; e.g., where I wrote Upper-Left-Scrollbar, Lower-Left-Scrollbar, Upper-Right-Scrollbar, and Lower-Right-Scrollbar) that contains the graphic that we use to control the scrollbar. Finally, in the column after the scroller, in the lower cell shows (D2, filled with text) the values the scroller can scroll through, and what the increment is: here it means all values between a and b inclusive, with steps of size c. For real examples, see the "Scrollbars+Conditional Format" worksheet, and examine the top three scrollbars. Once a scrollbar is set up (and we are NOT in "Design Mode"), we can control the scrollbar so that it will change the top cell directly to its left (the raw value), which should change the bottom cell, directly to its left too (the real value). The cell with the real value should contain a formula that refers to the cell with the raw value: so it too will change whent he scrollbar is changed. The real value scales the raw value and should be used by the other cells in the calculation. Below, we will talk about how to set up the correct formula in the real cell. To control the scrollbar we can either 1) click on the left-/right-pointing triangle on either end of the scrollbar 2) click in the space between the left-/right-pointing triangle and the slider 3) click the slider and drag it to the left or right -->Examine the Scrollbar+Conditional Format worksheet -->Look at the top scrollbar: examine the formula in B3 (it is =B2, so the --> real value is the same as the raw value); cell E3 indicates that this --> scrollbar allows values 0 to 1,000 (inclusive) in steps of -1 or +1. -->Try each of the three methods above to control the scrollbar, and observe --> the result in the real (B2) and raw (B3) cells: the first two kinds of --> clicking change the value by -1 or +1. In the second scrollbar on the "Scrollbar+Conditional Format" worksheet, we want the real value of the scrollbar to refer to a percentage, in the range [0, 1] in .01 increments (1% increments). This scrollbar is set to allow raw values between 0 and 100, and the real value will be a formula that calculates .01 times the raw value: the smallest real value will be .01*0 = 0 and the largest real value will be .01*100 = 1.0, just as specified by cell E6. -->Look at the 2nd from the top scrollbar: examine the formula in B6 (it is --> =.01*B5); cell E6 indicates that this scrollbar allows values 0 to 1 --> (inclusive) in steps of -1 or +1. -->Try each of the three methods above to control the scrollbar, and observe --> the result in the real (B5) and raw (B6) cells: the first kind of --> clicking changes the raw/real value by -5/-.05 or +5/+.05; the second kind --> of clicking changes the raw/real value by -10/-.1 or +10/+.1 To create a scrollbar and place it in on a worksheet, click the Insert icon and then click the scrollbar icon (the 6th -last on the first line- under ActiveX Controls -in the bottom half of the choices). Move the cursor, which will look like a +, to the upper-left hand corner of the four cells that will contain the scrollbar. Press down of the left button, then while holding it down, drag the rectangle that appears on the screen down and to the right, until it is at the lower-right hand corner of the four cells that will contain the scrollbar, and then remove your finger from the button. An icon for the scrollbar will now appear on the worksheet. Excel will then automatically switch to "Design Mode" (we will see the orange bacground for this icon). While in "Design Mode" we can left-click this scrollbar (or any other control on the worksheet) and change its size, or reposition it. When the scrollbar is in the right location and is the right size, we can right-click it and select Properties to edit its properties. While still in "Design Mode", right-click the scrollbar and select Properties. A pop-up window named Properites appears. There are three very important properties to update (by filling in/replacing values on the right in this window; of course, you can explore the others to see what they do as well). These three are Min, Max, and LinkedCell. Min is the minimum real value for the scrollbar (when its slider is all the way to the left). Max is the maximum real value for the scrollbar (when its slider is all the way to the right). THESE MUST BE INTEGER VALUES. LinkedCell is the name of the raw value cell (e.g., B5 in the second scrollbar) into which Excel will always put the current value of the scrollbar. That is just the raw cell we discussed earlier. Once we have entered these values in the properties we can terminate (X) the Properties pop-up window. Again note that the Min and Max values must be integers and they cannot be negative. In fact, almost all the time we will specify the Min as 0, and calculate by hand the value we need for Max. To operate the scrollbar, and put different values in the raw cell, we must first EXIT "Design Mode". Once we do so, we can control the scrollbar as described by the three options above. When we do so, the raw value in the LinkedCell changes. The real value cell, which contains a formula that refers to the raw value cell, also changes when we operate the scrollbar. For example, in the second scrollbar, the real value is .01 times the raw value (because we are producing percentages). There are two other useful Properties of scrollbars that we will discuss now. If we enter "Design Mode", right-click the scrollbar and select Properties, we will see a pop-up window named Properites. Notice the properties LargeChange and SmallChange (which default to 1). If we click the left/right triangles on the scrollbar, Excel changes the real value (down or up) by SmallChange; if we click the space between each triangle and the moveable scroller, Excel changes the real value (down or up) by LargeChange. So that is what these values control. -->Enter "Design Mode" and right click the second scroller and select --> Properties; notice that SmallChange is 5 and LargeChange is 10. -->Try the first two methods above to control the scrollbar, and observe --> the result in the real (B5) and raw (B6) cells: the first kind of --> clicking changes the raw/real value by SmallChange (-5/-.05 or +5/+.05); --> the second kind of clicking changes the raw/real value by LargeChange --> (-10/-.1 or +10/+.1) Note that the "change" goes BOTH WAYS: if we enter a value directly into the raw cell (i.e., type it in), the scrollbar(s) linked to that cell will update themselves by moving the scroller to the correct position. In fact, we can enter any value into a raw cell (even a non-integer) and the scrollbar will move to the most reasonable position for that value. So when a raw cell is linked to a scrollbar, changing either will change the other. -->Examine the Scrollbar+Conditional Format worksheet -->Observe what happens when you operate the 5 scrollbars on the top, either --> clicking the triangles on the end of the scrollbars, clicking to the --> left/right of the slider, or dragging/dropping the slider; make sure you --> are NOT IN "Design Mode", or none of these operations will work -->Examine the formulas in the real value cells (B3, B6, an B9) to see how --> these real values are calculated from the raw values. -->Enter the values -1, 500.7, and 2000 into cell B2: the raw cell linked to --> the first scrollbar. -->Enter "Design Mode" and move and resize the scrollbar icons; right-click any --> and select properties to see the Min, Max, and LinkedCell values: see --> expecially the LinkedCell values -->Experiment with these values by changing them and observing the effects --> after Exiting "Design Mode" -->Create scrollbars, and raw/real values cells to the right of the top 5 --> scrollbars that duplicate their behavior. Generally, if we want to have values in the range [a,b] with steps of s, we can easily specify the integer min/max values and a formula that translates a raw value into a real value. It is similar to the way in which we generated random numbers in an arbitrary range. First, specify the Min Property as 0 and the Max Property as (b-a)/s. In the real cell, write the formula of s*raw + a. So, for example, in the second scroller, we want [0,1] in steps of .01, so we specify Min as 0 and specify Max as (1-0)/.01 (which is 100). In the real cell we enter the formula =.01*B5+0, which I simplified to just .01*B5. If we want to have real values in the range [-2,5] with a step of .1, we specify Min as 0 and specify Max as (5- (-2) )/ .1, which is 70. In the real cell we enter the formula .1*raw - 2: note when raw is 0, the real value is 0-2 = -2 (the minimum value); when raw is 70, the real value is 70*.1 - 2 = 7-2 = 5 (the maximum value). 5) Computing Data Tables: A worksheet might have many cells working together to perform some calculation. For example, all the cells in columns B and C on the Interest worksheet, to calculate the amount available in period 29; or all the cells in columns A-D and F-H (at the top) on the Pi worksheet to calculate an approximation to pi (the final approximation is in cell H2). In the Pi worksheet, as part of assignmnment #2, I made you redo the calculation 5 times, and then copy and paste-special the result from cell H2 to make a table of data to average. We will examine a simple to use but powerful Excel command to "doing the same calculation multiple times with different specified input values, and recording all the specified output values." The is called the Data Table operation. There are two versions: 1-dimensional and 2-dimensional. The 1-d version is simpler and more useful than the 2-d version. We apply either by setting-up and selecting a rectangular range, clicking the Data tab, the What-If-Analsysis icon in the Data Tools groups, and click Data Table, and filling in either 1 cell (1-d form) in the pop-up window or 2 cells (2-d form). So, it is a bit difficult to get to the Data Table icon; it might be a good idea to put its icon on the QAT. Let's first take a look at how we set up the rectangular range, to calculate F(x) for a variety of xs. We build a two-column table (for simplicity assume columns A-B and rows 1-5). The inputs to be used are in the range A2:A5 A B +----------------------------- 1 | anything formula for F(x), where x is stored, say, in cell Z1. 2 | x1 blank We will use Z1 later in this command 3 | x2 blank 4 | x3 blank So, on the Interest worksheet, x is 5 | x4 blank stored in cell I4 We then select the rectangular range of cells A1:B5 and issue the Data Table... command. It asks us to fill in a pop-window that contains two possible values. Since we are using 1 column of values (x1 through x4) we specify the "Column input cell" as Z1 (see above, that is where F finds its input to compute on). The Data Table operation replaces cell Z1 with the value in x1 and then takes the result calculated in B1 and places it into B2; then it replaces cell Z1 with the value in x2 and then takes the result calculated in B1 and places it into B3; ...; finally it replaces cell Z1 with the value in x4 and then takes the result calculated in B1 and places it into B5. At this point it has filled in the entire table. The result looks as follows A B +----------------------------- 1 | anything formula for F(x), where x is stored in, say, cell Z1 2 | x1 F(x1) 3 | x2 F(x2) 4 | x3 F(x3) 5 | x4 F(x4) If we audit the formulas in B2:B5 we will see {=TABLE(,Z1)}. We will talk more about formulas in braces {} called "array formulas" later in the quarter. If we change any input in A2:A5 its corresponding value in the B column will auotmatically recompute itself. Likewise, if we change the formula in B1, all the values in the B column will be recalcuated. -->Examine the Interest worksheet -->Delete all the values in B41:B55 so we can construct a fresh Data Table -->Notice the formula in B40 is =B37, the result of calculating the accumulated compount interest for the 29th time period in column B. -->Selecting the rectangular range of cells B40:B55 apply the Data Table --> operation specifying the Column input cell as B4, which is the interest --> rate that affects what value is calculated in B37, which is the result use --> in this Data Table -->Notice how the rest of the table is filled in (compare cells B46 through B48 --> with the columns, D, E, and F: both calculate 6%, 7%, and 8% interest on --> $1,000 The worksheet also has chart that plots this Data Table: the amount of money available on the 29th period, if we start with $1,000, for each interest rate. -->Change the Rate cells from .01, .02, ... to .05, .1, .15, ... -->Notice the values B41:B55 change, as does the XY Chart illustrating these --> values (you'll need to reformat the X Axis to not be fixed, to allow --> all the X values up to .75 and adjust the width of the B column to see all --> the digits in these much bigger numbers.) -->Change the Rate cells .05, .1, .15, ... to .11, .12, .13, ... -->Finally, change the Rate cells back to .01, .02, ... -->Change the formula in B40 is =C37, the result of calculating how much --> interest will be added for the final time period -->Notice the values B41:B55 change, as does the XY Chart illustrating these --> values Now let's look at how we can use Data Table to run multiple experiments that compute PI by throwing darts, and averaging their results. -->Examine the Pi worksheet (increased to 1,000 darts), with a chart plotting --> all the dart's X,Y coordinates in a circle of radius 1: press F9 multiple --> times to recalculate all 1,000 dart throws -->Delete all the values in H23:H42 so we can construct a fresh Data Table -->Notice the formula in H22 is =H2, the result of calculating an approximation --> to pi after throwing 1,000 darts. -->Selecting the rectangular range of cells G22:H42 apply the Data Table --> operation specifying the Column input cell as ANY CELL (because there is --> NO value input to this calculation, it just generated 1000 random numbers) -->Of course, each time one of the try values is put in that ANY CELL, Excel --> recomputes all the random numbers -->Notice how the rest of the table is filled in. In the 2-d version of the Data Table operation, we calculate a rectangular table (instead of just a column) where the function we calculate depends on 2 different variables, as in F(x,y): it gets one sequence of inputs from Column A and the other sequence of inputs from Row 1. We build a rectangular table (for simplicity assume columns A-D and rows 1-5). A B C D +------------------------------------- 1 | F(x,y) y1 y2 y3 Where x is stored in, say, Z1 2 | x1 blank blank blank and y is stored in, say Z2 3 | x2 blank blank blank 4 | x3 blank blank blank 5 | x4 blank blank blank So, we assume that the function F gets its first input (x) from Z1 and its second input (y) from Z2. We then select the rectangular range of cells A1:D5 and issue the Data Table... command. It asks us to fill in a pop-window that contains two possible values. We specify the "Column input cell" to be Z1 (see above) and the "Row input cell" to be Z2. The Data Table operations replaces cell Z1 with the value in x1 and Z2 with the value in y1 and then takes the result calculated in A1 and places it into B2: this is the value of F(x1,y1); then it repeats this process for every pair of x and y inputs. At this point it has filled in the entire table. The result is A B C D +------------------------------------- 1 | F(x,y) y1 y2 y3 2 | x1 F(x1,y1) F(x1,y2) F(x1,y3) 3 | x2 F(x2,y1) F(x2,y2) F(x2,y3) 4 | x3 F(x3,y1) F(x3,y2) F(x3,y3) 5 | x4 F(x4,y1) F(x4,y2) F(x4,y3) If audit the formulas in B2:D5 we will see {=TABLE(Z2,Z1)}. We will talk more about formulas in braces {} called "array formulas" later in the quarter. -->Examine the Interest worksheet -->Delete all the values in B67:D71 so we can construct a fresh Data Table -->Notice the formula in A66 is =B37, the result of calcuating interest for the --> 29th time period in column B. --> It will use the values in Column A (.06 to .10) as interest rates nad the --> values in Row 668 (1000 to 3000) as initial amounts -->Selecting the rectangular range of cells A66:D71 issue the Data Table... --> command specifying the Row input cell as B5 (the starting amount) and --> the Column input cell as B4 (the interest rate), both of which affects --> what value is calculated in B37 and this Data Table. -->Notice how the rest of the table is filled in (compare cells B67, B68, and --> B69 with the columns DB, D, and F: both calculate 6%, 7% and 8% interest --> on $1,000; cell C69 shows the amount of money in the 29th period for an --> interest rate of 8% and an initial amount of 2,000 Finally, we can use the first form of the Data Table to compute a different kind of 2-dimensional table, using one function per column. Suppose FF means "Formula For", then by putting multiple formulas in row 1 (in cells B1, C1, D1 in this example), we can create a Data Table for each column/function simultaneously, by selecting A1:D5 (for the table below), clicking the Data Table icon, and in the pop-up window specifying where where x is stored for each (each function must be recalculable by changing the same cell; the one where the x for all of them is stored). A B C D +-------------------------------------------------- 1 | anything FF F1(x) FF F2(x) FF F3(X) 2 | x1 F1(x1) F2(x1) F3(x1) 3 | x2 F1(x2) F2(x2) F3(x2) 4 | x3 F1(x3) F2(x3) F3(x3) 5 | x4 F1(x4) F2(x4) F3(x4) 6) Conditional Formatting: Next we will discuss conditional formatting. As we have seen, all cells have formats. We can instruct Excel sometimes to use a different format for a cell (controlling its Font, Border, and Fill)) conditionally, depending on the value that the cell contains. This is called conditionally formatting a cell. We conditionally format a selection of cells by clicking the icon named Conditional Formating in the Styles group on the Home tab. There are five kinds of highlighting that are easily available for use. Let's look briefly at each in the context percentages from a grade sheet (cells B33:B71). -->Select cells B33:B71 and click the Conditional Formating icon -->Hover over Highlight Cell Rules; notice the many options for deciding --> whether to highlight a cell -->Click Between... -->Enter the left value of 90 a right value of =MAX(B33:B71); generally these --> entries can be any formula; here we identify A scores, those >= 90 -->Click the pull-down list on the right and select Custom Format, then click --> the Fill tab, and the green color in the bottom list, then click OK twice; --> notice all scores 90 and above are highlighted in green. -->Select cells B33:B71 and click the Conditional Formating icon -->Hover over Highlight Cell Rules and click Between... -->Enter the left value of 80 a right value of 89; here we identify B scores, --> those between between 80 and 89 -->Click the pull-down list on the right and select Custom Format, then click --> the yellow color in the bottom list, then click OK twice; notice all --> scores 80 and above are now colored either yellow or green We could continue this process for for Cs, Ds, and Fs, color coding each one. -->Select cells B33:B71 and click the Conditional Formating icon -->Click Manage Rules...; notice that there are 2 rules for the current --> selection (by changing the information in the top pull-down list, we can --> access all rules for the current worksheet -->We can double-click either rule to change the range of values or color, and --> edit the Applies to text book to change the cell range that that rule --> applies to: clicking Apply makes the new rule take immediate effect while --> still allowing us to make further changes; clicking OK makes the new rule --> take affect while terminating the rule manager -->Select cells B33:B71 and click the Conditional Formating icon -->Hover over Clear Rules and then click Clear Rules from Selected Cells; --> notice the cells lose their color; all the conditional formatting rules --> rules are gone (check that via Manage Rules, which shows none) Explore the Top/Bottom Rules: Highlight variations of high/low values Data Bars : Put a bar in each cell whose length is proportional to its value Color Scales : Put a color in each cell, spanning some colors/darknesses Icon Sets : Put an icon in each cell, based on the values there -->Select cells B33:B71 and click the Conditional Formating icon -->Hover over Icon Sets and click the line with a black/white circle going --> from filled in, to 3/4 to 1/2 to 1/4 filled in, to empty -->Click Conditional Formating, click Manage Rules, double-click this rule -->In each Type pull-down list, select Number (its Value entry will change to --> 0); then in the Value cells enter the values 90, 80, 70, and 60 (from the --> top down) and click OK; notice how the As, Bs, Cs, Ds, and Fs are marked -->Click the Conditional Formating icon and click Manage Rules... -->Double-click the the one rule there, then at the bottom pull-down list --> named Icon Style, select 5 Arrows (Colored), then click OK and click --> Apply; observe how the markings for these ranges change We can use conditional formatting to easily find values that differ between two columns. -->Select cells G33:H71 -->Hover over "Highlight Cell Rules" and slect "Duplicate Values" -->In the pull-down menu on the left select Unique and in the pull-down --> menu on the right select "Light Red Fill with Dark Red Text"; the --> pairs of different numbers in the column are easily highighted Clear all these rules. -->Select the single cell cells B33 and click the Conditional Formating icon -->Click Conditional Formating and then New Rule... A pop-up window named New Formatting Rule appears. In the Select a Rule type box (the top one), there are six categories of rules, most of which we've seen. Format all cells based on their values Format only cells that contain Format only top or bottom ranked values Format only cells that are above or below average Format only unique or duplicate cells Use a formula to determine which cells to format When we choose one of these, the Edit the Rule Description box (the bottom one) allows us to select all the details for that category of rule. In fact we reach this same pop-up window if we hover over any of the 5 icons after clicking Conditional Formatting, and choose More Rules... at the bottom. -->Choose the Use a formula to determine which cells to format This is the most general and useful option -if we want to do something special- because we can enter our own BOOLEAN formula. Let's find all the scores that are bigger than both the average and the median (there is no special icon for this complicated requirement). -->Enter the formula --> =AND(B33>AVERAGE($B$33:$B$71),B33>MEDIAN($B$33:$B$71)) -->Click Format... then click the green color on the bottom row, and click OK --> three more times; notice the value (94) appears in green -->Note that we also could have use the formula --> =AND(B33>$B$73,B33>$B$74) -->Because those worksheet cells calculate the average and median respectively. Notice that this rule uses a mixture of RELATIVE addresses (for the cell to format: B33) and ABSOLUTE addresses (for the range of cells used to compute the average and median). This choice will become important soon, when we want to apply this conditional formatting to other cells. Also, once we specify the condition we can click Format... and use the Font, Border, and Fill tabs to describe the details of the format to use if the condition is TRUE. There are a at least two ways to make this same conditional format apply to all the cells in this column. Method 1: -->Select cell B33, click Conditional Formatting, and then Manage Rules... -->Click in the box under "Applies To", and either type in $B$33:$B$71 or --> select cells B33:B71 on the worksheet (which will put$B$33:$B$71 in --> that box). -->Click Apply to see the result of applying this rule to all these cells -->Click the Undo icon to remove conditional formatting from these cells Method 2: -->Copy the cell B33; select cells B33:B71; click the disclosure triangle for --> Paste (in the Clipboard group on the Home tab); click Paste Special...; --> click the Formats radio button (in the first group, named Paste); click OK -->Notice that all the values greater than the Average (about 84) and the --> Median (about 87) are highlighted in green In the first method (when the formula is applied to other cells) and the second method (when the formula above is pasted), its relative references change, so that the rule is checking whether each of the new cell is bigger than both the average and median of the absolute range. Generally, we use conditionally formatting to highlight cells that contain "special" values that we want to stand out. We can also use conditional formatting to highlight data entry errors. -->Examine the Scrollbar+Conditional Format worksheet -->Manage the rule specifying the conditional format of cell B22, which appears --> with a reddish fill if the value entered there is not in the inclusive --> range specified by cells B20 and B21; change the value in B22 to be --> inside/outside this range (of course you can also change the values in B20 --> and B21 to change the range that the cell checks too) -->Manage the rule specifying the conditional formats of the cells B27:B28 -> These are the same, and both use the "Use a formula..." option. They both --> turn reddish if B27 > B28; either enter values into these cells or use the --> scrollbar to change these values and watch the color change when the --> inputs are "illegal": don't respect the order Min Value <= Max Value. So, this second example links scrollbars to cells that are conditionally formatted. But let's examine one more wrinkle. Finally, if you look closely at the formula in the conditional formatting of B27:B28, you will see AND( $B$25 , $B$27>$B$28 ). So this formula is TRUE (meaing the color turns reddish) only when both of the boolean values in the AND function are true. The reference to B25 refers to a cell containing TRUE, so the color change will occur when $B$27>$B$28. If we change B25 to FALSE, the AND will never be TRUE, because AND(FALSE, anything) is always FALSE. So, by changing B25 to FALSE, we have "turned off" error checking: even if B27 and B28 contain illegal values, their pattern will stay green. -->Change B25 to FALSE and observe the difference when the values in cells B27 --> and B28 are illegal. As a final note, we can add MULTIPLE conditional formats to a cell. In one case, different conditional formats might control different aspects of the format: one might control the font, another might control the fill, etc.; in another case, each conditional format might control the same aspects. In this later case, the order of these rules is important: Excel checks these rules in the order they appear, so if a later rule is true, it might override the format of a previous rule. Sometimes that is the behavior we want, sometimes not. We can change the order of the rules in Manage Rules... by selecting a rule and then pressing the up/down arrow (after Delete Rule) to move it. We also have the option of putting a check in the "Stop if True" box at the end of each rule; if we check this box, and the rule's condition is true, no other rules will be checked. In fact, sometimes when we are experimenting with adding conditional formats to cells, we might accidentally add multiple rules to a cell. We can use the "Delete Rule" option in Manage Rules... to selectively delete any rule. We are now finished with our quick tour of what we can do with conditional formatting. This features allows us to highlight selected cells (based on their values), looking at "important" cells in worksheet that contains a large amount of data. Once again, BOOLEAN values are important here, to determin, TRUE or FALSE (the condition), whether a cells should be formatted 7) Protecting Cells in Worksheets: We have already seen protected worksheets in which we cannot see the formulas in cells, but can see only the values in cells. This feature is useful, because it allows me to let you examine the behavior of my workheets without allowing you to see how I built them: you have to figure out the formulas in the cells and the formatting of charts by yourself (i.e., learn the material that this class is teaching). We will learn how to use protection for this task -to hide the contents of cells- and also how to protect cells from being changed. Typically if we give a worksheet to some user (who may or may not know Excel well), we would like to be able to protect (from mistakes and/or malicious intent) all the cells except the ones we designate as cells into which the user can input values. Below, we will first learn how to set individual cells in a worksheet to be locked/open and/or hidden/visible. Then we will learn how to make these choices take affect, which happens when we protect the entire worksheet. So, we acheive the result by performing two related actions. An open cell allows a user to change its contents (type in a new value or formula); a locked cell disallows changes. If the user tries to change a locked cell, the computer will beep and pop up an error message. A visible cell shows it contents (again, value or formula) for a user to see; a hidden cell hides its contents from the a user (the user cannot see its contents, only its value is visible -not how the value was computed). So, both visible and hidden cells show the value of their contents, but not the formulas that computed the contents. First, every cell in a worsheet is automatically formatted, by default, to be locked and visible. We say "format" here because to specify these properties we right-click a cell and select Format Cells... and then choose the Protection tab (the last one). For individual cells, we can check or uncheck the Locked and Hidden options, which show their current settings on a white backround (if we select multiple cells and some -but not all- cells have these options checked, the background in gray). If we want to change the protection formats, we can check/uncheck these boxes and then click OK. Once we have the protection format set correctly for every cell in the worksheet, we activate them by protecting the worksheet. To protect a worksheet, we click the Review tab, and then click the Protect Sheet icon (the leftmost one) in the Changes group. At this point all the Protection formats take effect. Once a worksheet is protected, the icon's name becomes Unprotect Sheet. So this icon toggles between protected and unprotected worksheets. If we click the Protect Sheet icon, a pop-up window named Protect Sheet appears and contains a Password to unprotect sheet text box and many options in the form of checkboxes of what a user is allowed/prohibited from doing on a protected sheet. We will explore these details later in the quarter (for now use just the default, the selections of Select locked/unlocked cells). The creator of the workbook is also given the option of placing a password on the locked spreadsheet. If no password is entered, the user of the worksheet can unprotect it as described above. If the creator does specify a password, he/she will be prompted to again enter the same password (to ensure it was typed correctly). Then, if the creator or user of the worksheet applies the command to unprotect it he/she will be prompted for the password and must supply it to unprotect the worksheet. SO, BE CAREFUL IF YOU USE A PASSWORD TO PROTECT A WORKSHEET. IF YOU FORGET IT YOU WILL NOT BE ABLE TO UNPROTECT THE WORKSHEET! -------------------------------------------------- Not applicable for Excel 2007: Finally, we can select objects on a worksheet (charts, for example, are a special kind of object) by clicking them while pressing the Ctrl key, we can then right-click an object and choose the Format Object option, whose Protection tab allows us to lock (but not hide) that object. In the case of a locked chart, the user of the spreadsheet cannot change its appearance (nor see how the chart format was achieved) -------------------------------------------------- 8) Selecting Many Cells at Once/Selecting Cells by Contents: In Excel, we sometimes need to select an irregular group of cells (not a column, row, nor rectangle range), such as for changing their formats (e.g., the protection formats just discussed). It is easy to select a regular group of cells: we just press the first cell and then drag the cursor to cover the range. It is easier for us to select a group of cells and specify the format for them once, than to specify the same format, over and over again, after selecting each cell. Below we briefly discuss how to select such irregular groups of cells. If we want to select a group of cells, we can click each cell (or a regular range of cells) while holding down the Ctrl key (called ctrl/click). Excel will highligh each cell that is chosen. If we make a mistake and choose a cell by accident, we have to start over; so be careful. Once we have selected all the necessary cells, we can right-click any one of them and choose Format Cells... Excel also provides a quick way to select all cells on a worksheet. If we select a cell and press ctrl/a once, Excel will sellect all the cells in the rectangular region that surrounds our selected cell. If we press ctrl/a again, Excel will typically select all cells on the worksheet. If that doesn't work, click the triangle above row 1 and to the left of column A. Excel also provides a quick way to select cells on a worksheet by the type of information that they contain. Using this mechanism, for example, we can quickly select all cells that contain numbers (not formulas); these cells are probably input cells; for example, we might want to unlock them before protecting a worksheet. For simple items, we can click the binocular icon labeled Find & Select in the Editing group on the Home tab. There we can click Formulas, Comments, Conditional Formating, Constants, and Data Validation, which select and highlight cells having the clicked attributes. We can start to find more complicated items by either clicking Find & Select and then clicking Go To Special ... or just by pressing ctrl/g and then clicking the Special... button. In both cases a pop-up window named Go To Special appears and we can choose the kinds of cells we want to select. The most useful options here are selecting constants or fomulas that contain Numbers, Text, Logicals (boolean values), or Errors. We can click either radio button and then check the requested type. Experiment by selecting various combinations. Once those cells are selected, we can use ctrl/click to add more cells to the group (but NOT remove some cells from the group). To select all input cells to unlock them, we might specify only Constants and Numbers. This brings up a small but interesting distinction: what is the difference between a cell that contains 0 and a cell that contains =0. Both cells will always have the same value, zero, but the first is a constant and the second is a formula. Here is a strangely worded statement. "In the future, I would advise entering input values as contants (not formulas) but entering constant cells as formulas." By doing so, selecting Constants and Numbers will choose only input cells. For example, in the Interest worksheet try selecting Constants and Numbers. It will select many input cells, but also A8, which contains just 0. But this cell is not an input: the user shouldn't be able to change its value, as periods always start at 0. It is just a constant 0 that cell A9 increments. So if we write cell A8 as =0, it will not be selected by when we go to Constants and Numbers. Note that if we select some group of cells and then use the commands described above, it will find the types of cells we specify, only among the ones that we have already selected. -->Examine the Scrollbar+Conditional Format worksheet -->Practice protecting and unprotecting this worksheet, after setting the --> protection formats (mostly concentrate on locked/open, but examine hidden/ --> visible formulas in the real input cells too) for cells that contain --> inputs: the raw cells for the scroller (B2, B5, B8, and B14) and --> the other input cells in blue (B22, B27, and B28). -->First lock some of these formats, protect the worksheet, then see what --> happens when you try to enter information into locked cells, and finally --> unprotect the worksheet and try again. -->Select these group of cells by using the mechanisms described above. -->Practice protecting/unprotecting the worksheet with and without a password. -->Examine the Interest worksheet and practice locking/hiding both cells and --> the charts. -->Select the two columns of numbers G33:H71 -->Summon the pop-up window named Go To Special and click the Row Differences --> radio button; notice that H46 and H59 are highlighted, because these rows --> are different between the G and H columns 9) 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 general form for worksheets. Know how insert, delete, and edit comments in cells. Know how to construct scrollbars and set their properties to vary inputs easily: know about the Label, raw and real values, and how to specify the range of values and step (and how put a formula in the real value that scales the raw value appropriately). Know about design mode and how to toggle it to design/use scrollbars. Know how to construct 1-d and 2-d data tables. Know how to conditionally format a cell or range of cells, using the standard forms and simple to arbitrarily complicated rules. Know how to protect individual cells (locked/open and hidden/visible) and protect worksheets (both with and without passwords) to make the protection of individual cells take effect. Know how to use ctrl/click to select many cells at once. Know how to select cells by their contents (e.g., constant, formulas, etc.) with the ctrl/g or Find & Select | Go To Special commands.