Updated: Spring 2020 Introduction to Spreadsheets and Excel Outline 1) Spreadsheets: History and Learing Expectations 2) Workbooks and Worksheets: 3) Cell Basics: Entering, Editing, Deleting, Undoing/Redoing, Formatting: 4) Cells: Copying/Cutting and Pasting of Relative and Absolute References 5) Filling: 6) Computations on Cell Ranges: 7) Summary: 1) History: Before computers, business students constructed spreadsheets using paper and pencil (and more recently calculators). In the late 1970s (about the time the Apple II computers first became popular) Dan Bricklin (in the MBA program at Harvard) prototyped a first version of an Electronic Spreadsheet and along with his friend Bob Frankston (a recent graduate student at MIT) wrote the VisiCalc spreadsheet program and co-founded Software Arts to sell their product. Their software was so useful that it became the "killer-app(lication)" for the Apple II: i.e., people wanted to use the program so much (because they found it so powerful and so time-saving) that they bought a computer just to run VisiCalc. For more information about VisiCalc, see http://www.bricklin.com/visicalc.htm Also, the following was written on the 35th anniversary of VisiCalc: https://www.wired.com/2014/10/a-spreadsheet-way-of-knowledge/ Since these days, spreadsheets have been enhanced greatly and have become powerful general purpose calculators, useful in all sorts of fields. Problems are often solved very simply with spreadsheets, and by using them we can solve a wide variety of problems on computers without resorting to learning to use traditional programming langauges. That is the genesis of this course, ICS-7. In their simplest form, spreadsheet programs are like 2-dimensional calculators: some cells represent labels, other represent inputs (data that we enter), and others represent the results of calculations on those inputs (or other calculated cells: calculations often result in numbers, but sometimes text). We can also use the graphical elements in spreadsheets to enter inputs and chart these inputs and the results of our calculations easily. Often we have a mathematical model for a problem that we need to either better understand or solve; and, we use a spreadsheet to simulate the model or solve the problem. Later we might change the inputs to see the solution to other similar problems. The labels and charts help us (or others who will use our spreadsheet) to understand what are the inputs and what the results mean in a spreadsheet. So, the spreadsheets that we produce should be documents that solve a specific problem clearly and elegantly. We should be able to use the same spreadsheet again, to solve the same kind of problem but on different data, or enhance it to solve a similar but more complicated problem. The most widely used spreadsheet is Microsoft Excel, which is part of the Microsoft Office Suite. We will use Excel 2019 in this course. There are other spreadsheet products (including freeware) that perform similar functions, but not all the functionality that we will explore in Excel 2019. The main goal of this class is for you to see and learn a variety of Excel commands and features, and practice using them to build and solve mathematical models, so you will feel comfortable using Excel to explore problems that you come across in your life or other UCI classes. I'm hoping in the future you'll think, "I remember something that I saw in ICS-7 that would be useful here." By practicing using thse commands, multiple times, you will build up both mental and muscle memory of the commands. Unless you use an Excel feature daily, you will forgot some of its details. But, once you learn a feature, you will be able to re-learn it much more quickly. (I frequently refer to my own notes to re-learn features I use infrequently). Also, you will have an archive of spreadsheets, some of which use that feature in a context that you might remember. Having such examples can also help you quickly re-learn a feature. During this entire course, feel free to explore Excel by clicking anything that interests you and observing (and trying to understand) the results. Most changes you make can be easily undone. My goal is to not only for you to get comfortable using Excel, but to get comfortable extending your knowledge of Excel while exploring it. That includes googling questions on the web; the more you know about Excel, the easier it will be understand the answers to such questions. ------------------------------------------------------------------------------ 2) Workbooks and Worksheets: In lecture, we learned how to start an Excel "workbook" from the Start button Click Start (lower left corner of bottom line)| Excel There are many other ways to start Excel, including double-clicking existing Excel file icons (if we have already created and saved an Excel spreadsheet), or creating/clicking a shortcut to EXCEL.EXE. On my computer the path is: C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE The middle of the Title Bar, appearing at the top of the window indicates the name of the workbook being edited: the name of the file corresponding to this workbook. Most likely the name indicated will be Book1, when starting a new spreadsheet. We save an Excel workbook, storing it in a file ending in .xlsx (and later .xlsm), by clicking the "floppy" icon on the Quick Access Toolbar (QAT): the first icon on the top row of icons (to its right are the undo and redo icons, or by clicking the File tab and then clicking the Save or Save As icon. In the next lecture we will learn how to add (and organize) special shortcut-icons to to the QAT, to more quickly access Excel features that we frequently use. In class, I may already have my QAT set up for giving me quick access to the common operations I perform. Finally, we terminate Excel by clicking the X, the rightmost icon on the title bar. When we do so, Excel will prompt us about whether or not to save our changes, if we have not already saved our work to a file. There are actually three options: Save (and then terminate), Don't Save (and then terminate), or Cancel the termination command itself, returning to Excel. Once we have saved a workbook, we can double click its icon to start Excel using that workbook. We can also drag-and-drop any Excel file into an Excel Window to edit that workbook. Each workbook will appear in in its own Excel window. A new Excel workbook contains one "worksheet" named Sheet1 (see the tab at the bottom of the Excel window; clicking the "plus in a circle" icon to its right (hover over it and it will show "New Sheet") is an easy way to add a new worksheet to the workbook). Worksheets help us organize our work: think of a Workbook as a book, and a Worksheet as a chapter. Here are four simple but useful things you can do with worksheets (even if they are empty). 1) To delete an existing worksheet, right-click its tab and either press d or select Delete. To delete many worksheets, ctrl/click on multiple tabs, then right-click on one of these tabs and either press d or select Delete. If there is any information on the worksheet, you get a pop-up warning for this operation, because IT CANNOT BE UNDONE: you can proceed with the Delete or Cancel it: so proceed carefully if you have created a complicated worksheet! You can also click a tab then shift/click on another tab, selecting all the tabs between them (inclusive), and delete them all by deleting one. 2) To change the name of a worksheet, double-click the name on its tab and edit the tab's name as you wish (it will be displayed in reverse: white on black). Or, right-click its tab and either press r or click Rename. If in the middle of renaming you decide to keep the original name, press the "Esc" (Escape) key and the original name will reappear. The "escape key" often undoes a change (e.g., when editing a cell) while you are in the middle of changing something 3) To move a worksheet's tab, press the tab (click down on it but don't let up with your finger) and then drag the tab to the position (among the other tabs) you want the worksheet to occupy, and then drop it (release the mouse button). Note that when you press the tab, a small black triangle appears, and as you move the tab it shows where the tab will be positioned when you release the mouse button. 4) To Hide a worksheet, right-click its tab and select Hide. To unhide a workseet, right-click any worksheet tab and select Unhide: you will be shown a list of hidden worksheets (by the name on their tabs) that you can unhide. Excel will not allow you to hide all worksheets: if you try to hide the last visible worksheet, it will not allow it. Sometimes you might hide entire worksheets if users don't need to see/use anything on that worksheet: it would just distract them. PRACTICAL HINT: If you do delete a worksheet by mistake, you can always terminate Excel without saving the new workbook. Of course, you will also lose any other changes that you have made to the workbook since the last time you saved it. So save workbooks often and delete worksheets carefully! -->Practice starting, saving, and stopping Excel and deleting, creating, --> renaming, moving, hiding, and unhiding, worksheets. -->Ultimately, your Workbook should contain three worksheets: the first named --> Name(s),the second named Lecture 1, and the last named Scratch ------------------------------------------------------------------------------ 3) Cell Basics: Entering, Editing, Deleting, Undoing/Redoing, Formatting: Every location inside a worksheet is known as a "cell". Each cell has a unique column and row "address", given by a letter (its column) and number (its row). Cell A1 is the cell in the upper-left corner. Columns go A, B, ... Z, then AA, AB, ... AZ, BA, BB, ... BZ, ..., ZA, ZB, ...ZZ, AAA, etc. Rows just increase from 1 upward. There is a limit to the number of columns and rows in a worksheet: Excel 2019 can store over 16,000 columns (2^16) and over 1,000,000 rows (2^20): note the assymmetry: many more rows than columns are possible. We can put many things inside cells, including text, numbers, and formulas that typically refer to the values stored in other cells; formulas all start with the equal character (=). A spreadsheet normally displays the "value" of each cell (i.e., if a formula is in a cell, the value calculated by the formula is displayed, not the formula itself -although we will soon learn how to audit cells: view their formulas instead of their values). We will also soon learn the kinds of information that we can put into formulas. When we click on a cell to enter information into it, the address of the cell we are editing appears directly above the A column, and the contents we are entering into the cell appears after the symbol "fx" as well as in the cell itself. We can edit (change the contents of) the cell in either location. Normally I edit the cell itself, unless the formula is very big, in which case I use the bigger cell at the top: The size of this bigger cell can be controlled (multiple lies or 1 line) with the ^ anv v icons to the right on its top. When changing the contents of a cell, we make the complete change and press the "enter" key or make the complete change and then use the mouse to select another cell. In either case, the cell we were editing is changed. But, if in the middle of making a change we want to cancel the change, and keep the original contents of that cell, we can press the "Esc" key to "escape" from the change (as we described above,when editing the worksheet tab name). In addition, after the contents are changed, you can undo the change by pressing ctrl/z or clicking the Undo icon (the curving counter-clockwise icon to the right of the Save icon on the Quick Access Toolbar (QAT)). You can undo any number of changes, by repeated pressing ctrl/z of clicking the undo icon. If you undo too much, you can redo (anything that you undid) by pressing ctrl/y or the Redo icon (the curving clockwise icon to the right of the Undo icon on the Quick Access Toolbar (QAT)). So, I encourage you to experiment with Excel, because we can easily undo/redo the effect of any experimental change. Don't be afraid of experimenting: most experiments can be easily undone! Although, we have already lerned one example, deleting a worksheet, that cannot be undone. Typically when doing an operation that cannot be easily undone, Excel makes you confirma the operation before it performs the operation. To delete the contents of a cell, select the cell and press the "Del" key (located at the bottom-right of the keyboard; make sure the Num Lock is off). Note that this action deletes the contents of the cell, but the cell retains its formatting information (discussed below). Of course, you can use Undo to undo the effect of the delete, restoring the cell to the contents it had before you deleted its information. By typing ctrl/` (holding down the ctrl key while pressing the ` key -called the grave accent, which is under the ~ on the top left of the keyboard), Excel toggles from "value" display mode to "formula auditing" display mode. In formula auditing display mode the cells are wider and text and numbers still show as themselves, but cells that contain formulas display the formulas, not the values they compute (since formulas often take up more space than values, the width of each cell increased in formula auditing display mode). Typing the ctrl/` command again toggles back to value display mode. Formula auditing display mode is very useful for understanding and debugging worksheets. We will examine this mode further, when we explore formulas in more detail in the next lecture. We can also toggle between value display mode and formula auditing display mode by clicking the Formulas tab and then clicking the Show Formulas icon in the Formula Auditing section of the ribbon (top of last column: it looks like a square with 15 in the top left and fx in the bottom right). Click it again to toggle back to value display mode. We can also format the values displayed by cells. Although we will discuss this option in much more detail later, we will look at some simple and useful examples of formating now. To change the format of a cell, first select that cell (we will learn how to select multiple cells and format them all at once) and then do any of the follow 1) Right-click a cell and either press f or click Format Cells 2) On the Home tab, click Format above the Cells label on the ribbon (press e or click Format Cells) 3) Click the downward-right arrow to the right of the Font, Alignment, or Number labels 4) Press ctrl/1 (the number one, not the letter l) You can bet if there are 4 ways to do something, it is important. To me the first way is the easiest to remember. And you often "right-click" things in Excel to see a menu of the things you can do to/with the clicked item. Later we will see how to put frequently used commands on the QAT. In each case a pop-up window labelled Format Cells appears, with the tabs: Number, Alignment, Font, Border, Fill, and Protection. We will learn the details of all these tabs in subsequent lectures. For now, we'll just discuss some of the basics; feel free to experiment and undo your choices. On the Number tab, the most important format is Number (2nd on the Category list, under General) which allows us to specify (a) the number of decimal places to display in a value, (b) whether to use commas to separate every 3 digits, and (c) how to display negative values (most of the negative options are relevant only for accounting, the original use of spreadsheets). Also simple and useful is the Alignment tab (the choices for Horizontal are most useful: see left, center, and right) and the Font tab, which allows us to specify the Font, Font Style, Size and other options relating to the value displayed. We can also change common Alignment and Font options by clicking the icons above the Font and Alignment labels on the ribbon of the Home tab, which is easier than doing this operation while formatting cells. Most cells are initially formatted by the General category, which along with the Text category allows us to specify Font, Font Style, etc. It is very useful to be able to toggle between normal and boldface fonts for displaying the contents of a cell. If you select a cell and press ctrl/b it will toggle from normal to boldface and from boldface to normal. Using this shortcut, if you can remember it, is much simpler than using the pop-up window or the B icon on Font label on the ribbon of the Home tab. -->Enter the names of each of the students working through this assignment --> in cells A1 and A2 on the Name(s) worksheet. -->Remember to press the Enter key when you are done typing in a cell -->Format these names to be boldface For all other work (from here on) use the Scratch worksheet. You can see what your worksheet should ultimatel look like in the Lecture 1 worksheet. Remember the purpose of this course is know how to get the right answers on the scratch worksheet, even though I provide them in the Lecture 1 worksheet. I won't provide answers for the labs or the in-lab exams. -->Enter some text (e.g., Hello), a number (e.g., 3.14) and a formula --> (e.g., =SQRT(2)+6) into the cells A1, A2, and A3 and experiment by --> changing their formats. You can always undo any changes; practice undoing --> some of the changes. -->Remember to press the Enter key when you are done typing in a cell -->Toggle between value display mode and formula auditing display mode to see --> the difference in the cell in which you entered a formula. Something we will explore more later: If you try to put -X into a cell, Excel will instead put #NAME? there. It thinks you are trying to negate the value in the variable X (more about variables later). Here, and later, if you run into problems entering text, preface the text by an = and put the text in quotes: Put ="-X" in the cell. The cell will display -X (without quotes). Also, if you preface characters with a single quote (') then whatver follows (without the single quote) appears in the cell. Try '-X. -->How do you think you can a cell to display "-X"? compared to ="-X" -------------------------------------------------------------------------------- 4) Cells: Copying/Cutting and Pasting of Relative and Absolute References One of the most important operations you can perform on cells in a spreadsheet is "copying and pasting" (and to lesser extent "cutting and pasting"). We often construct spreadsheets by filling in a few cells with values and formulas to start a calculation, then verify that they are correct, and finally copy and paste these cells into other cells to extend the spreadsheet and continue the calculation, until its conclusion. Sometimes we copy one cell and paste it into one other cell (duplicating its contents); sometimes we copy one cell and paste it into many other cells (duplicating its contents into each); sometimes we copy multiple cells into the same number of multiple cells or more (e.g., copying cells C1 and D1 simultaneously into C2 and D2, C3 and D3, C4 and D4 etc.) So, there are many possibilities for "copying/pasting" and we will show lots of examples below. To copy and paste a cell, we (a) click on the "source" cell(s), (b) press ctrl/c or click the Copy icon above the Clipboard label on the ribbon (the middle icon -double pages- between the scissors and brush) (c) click on the "destination" cell(s), (d) press ctrl/v or click the Paste icon above the Clipboard label on the ribbon (the brush) After we complete this operation, the destination cell(s) should show their new values. Cutting and pasting is like copying and pasting, except that the second step is (b) press ctrl/x or click the Cut icon above the Clipboard label on the ribbon (the scissors) After cutting and pasting, the source cell(s) should be empty (their contents are "cut out/removed") and the destination cell(s) should show their new values: a copy of the ones cut out/removed. As a side effect, formulas that refer to the old/now-empty cell will generally refer to the new/pasted-into cell. After we discuss absolute vs relative cells, we will return to copying and pasting with a few interesting examples. When a formula refers to another cell, the reference to that other cell can be described as "absolute", "relative", or "mixed". Each kind of reference computes the same value, but each is copied a bit differently from the others. And, since copying is such an important operation, we will discuss the meanings of these address while discussing the meaning of copying each of them. Each reference specifies a row and column. We say a reference is absolute: if a $ appears before both its column and its row relative: if no $ appears before either its column and row mixed : if one $ appears before its column or its row (but not both) -->Enter the number 1 in cell A6 -->Enter the formula =A6+1 into cell A7; note this reference is relative: there --> is no $ in front of either its column or row -->You can do this by typing A6+1 after typing =, or by clicking on cell A6 --> after typing = and then typing +1: when entering a formula (after the =), --> clicking a cell enters the cell's address into the formula. Do this the --> first way, then undo it, then enter it the second way -->Now copy cell A7; then select cells A8, A9, and A10; and then paste into --> these cells with one paste operation. These cells display 3, 4, and 5 --> respectively -->Use ctrl/` to switch to formula auditing display mode -->Notice that cell A8 has the formula =A7+1, cell A9 has the formula =A8+1, and cell A9 has the formula =A8+1 So, the original formula copied from cell A7 was pasted differently into each of these cells: it has the same general form, but the references are different. Pasting cells with relative references changes the references according to the following rule (then we'll cover absolute references, which are much easier; but relative references are more common). When a copy of cell A7 is pasted into cell A8, Excel computes that there was an increment of 0 columns (still A) and 1 row (8 from 7). Therefore, Excel modifies the original relative reference by adding 0 to the column (it stays the same: A) and adding 1 to the row (it changes the reference A6 in the formula to A7). Likewise, when a copy of cell A7 is pasted into cell A9, Excel computes that there was an increment of 0 columns (still A) and 2 rows (9 from 7). Therefore, Excel modifies the original relative reference by adding 0 to the column (it stays the same: A) and adding 2 to the row (it changes the reference A6 in the formula to A8). Likewise when a copy of cell A7 is pasted into cell A10... -->Use ctrl/` to switch to value display mode -->Enter the formula =A6+1 into cell B6 -->Now copy cell B6; then select cells C6, D6, and E6; and then paste into --> these cells with one paste operation. These cells display 3, 4, and 5 --> respectively -->Use ctrl/` to switch to formula auditing dispaly mode -->Notice that cell C6 has the formula =B6+1, cell D6 has the formula =C6+1, and cell E6 has the formula =D6+1 Again, the original formula copied from cell B6 was pasted differently into each of these cells because it had a relative reference. When a copy of cell B6 is pasted into cell C6, Excel computes that there was an increment of 1 column (C from B) and 0 rows (still 6). Therefore, Excel modifies the orignal relative reference by adding 1 to the column (it goes from A to B) and adding 0 to the row (it stays the same). This changes the reference A6 in the formula to B6. Likewise, when a copy of cell B6 is pasted into cell D6 and E6... So, generally copying and pasting a relative reference changes either the column address, row address, or both (whichever doesn't have the $ in front of it). The amount of the change is computed by Excel to be the relative change in the column and the relative change in the row. Now let's examine absolute references and how they are copied and pasted. Recall a reference is absolute if a $ appears before both its column and row. -->Use ctrl/` to switch to value display mode -->Enter the number 1 in cell A16 -->Enter the formula =$A$16+1 into cell A17 (note the $ prefacing the column --> letter and row number) -->You can do this by entering A16 either way (specified above); then you can --> edit in the $ in both places or press the F4 key, which includes both $s. --> If you press F4 multiple times it will sequence through all combinations --> of $: both column and row, only row, only column, back to neither column --> nor row; just keeping pressing F4 until you are back to both $s -->Now copy cell A17 and paste it into cells A18, A19, and A20 with one paste --> operation. These cells all display 2 (not different values!) -->Use ctrl/` to switch to formula auditing dispaly mode -->Notice that cells A18, A19, and A20 all have the formula =$A$16+1 So, when copying absolute references, the address of the reference does not change. The original formula copied from cell A17 was pasted identically into each of these cells. Pasting cells with absolute references leaves the address of that reference unchanged. -->Use ctrl/` to switch to value display mode -->Enter the formula =$A$16+1 into cell B16 (note the $ prefacing the column --> and row) -->Now copy cell B16 and paste it into cells C16, D16, and E16 with one paste --> operation. These cells all display 2 (not different values) -->Use ctrl/` to switch to formula auditing dispaly mode -->Notice that cell C16, D16, and E16 all have the formula =$A$16+1 Finally, if the column in a mixed reference is relative, then the column will change if it is pasted to a different column; if it is absolute the column will stay the same. And, if the row in a mixed reference is relative, then the row will change if it is pasted to a different row; if it is aboslute the row will stay the same. So, these are all the rules for copying and pasting relative, absolute, and mixed references. Sometimes the column and/or row addresses change; sometimes they do not. While these rules might seem strange at first, the more we use Excel, the more we will use copying and pasting, and the more intuitive these rules will become. Practice will help us make sense of them. PRACTICAL HINT: Often Excel mistakes arise from choosing references incorrectly (i.e., relative when should be aboslute or vice versa). Often we discover such mistakes when pasting: the pasted values look wrong. Typically we will "debug" the reference, then re-copy/-paste to solve the problem. So whenever you paste, take a few seconds to see if the new values meet your expectations. The pasting rule after CUTTING is different. If the formula in a cell is CUT and then pasted, the formula in the source cell is copied to the destination cell UNCHANGED, regardless of whether the references are absolute, relative, or mixed! In this way, the value calculated by the formula in the original cell will always be the SAME VALUE calculated by the formula in the cell it was pasted into, because it will refer to exactly the same other cells. In addition, any cell that referred to the cut cell (which is now empty) will automatically now refer to the cell that its formula was pasted into (which we know will have the same value in it): so all the cells except the one cut and the one pasted into have the same values. -->Now CUT cell B6 and paste it into cell F6 (in formula auditing dispaly mode) -->Notice that cell B6 becomes empty and cell F6 has same formulas as B6 had: --> =A6+1 -->Also notice that cell C6, which had the formula =B6+1 now has the formula --> =F6+1 (it refers to the cell that now has this formula) -->Use ctrl/` to switch to value display mode and notice all cells have the --> same values (except the cut and pasted cells) -->Undo this cut and paste and notice cell C6 changes back to =B6+1 -->Use ctrl/` to switch to formula auditing dispaly mode -->Now CUT cell A16 and paste it into cell F16 (still in formula auditing --> dispaly mode) -->Notice that cell A16 becomes empty and cell F16 now stores 1 -->Also notice that cells A17, A18, A19, and A20 now have the formula =$F$16+1 --> (each refers to the cell that now has this value); in addition, cell B16 --> now has the formula =$F$16+1 -->Use ctrl/` to switch to value display mode and notice all cells have the --> same values (except the cut and pasted cells) -->Undo this cut and paste and notice cells A17, A18, A19, A20, and B16 now --> have the formula =$A$16+1 -->Use ctrl/` to switch to formula auditing dispaly mode This is quite detailed information for a first lecture, but the use of different kinds of references will become simpler and simpler as you work more in Excel. Generally, relative references are the most common, then absolute references, and finally mixed references are least common; but all are still sometimes very useful; I have used all in spreadsheets that I have created. Let's generalize the idea of generating a sequence of values that uses both relative and absolute references. Using this more complicated scheme, we will be able to easily change the sequence generated without changing all the formulas. -->Use ctrl/` to switch to value display mode -->Enter the number 1 in cell A25; format it (see above) to be in boldface --> this will be the INCREMENT value for our sequence of values -->Enter the number 1 in cell A26 (but don't boldface it); this will be the --> START value for our sequence of values -->Enter the formula =A26+$A$25 into cell A27; notice A26 is a relative --> reference and A25 (really $A$25) is an absolute reference -->Now copy cell A27 and paste it into cells A28, A29, and A30 with one --> paste operation. These cells display 3, 4, and 5 respectively -->Use ctrl/` to switch to formula auditing dispaly mode -->Notice that cells A28, A29, and A20 all have the formulas in which the --> relative reference changes but the absolute one does not; each --> increments the cell above it by the same value: the value in cell A25 -->Use ctrl/` to switch to value display mode -->Change the value in A25 a few times (remember to press Enter after each --> change) and see what happens to the values that come after it. The values --> in A27 through A30 start at whatever value is in A26 and count up by --> whatever new value is in A25. These values are recalculated every time a --> new value is put in A25 -->Enter the value 1 in cell A25 -->Change the value in A26 a few times (remember to press Enter after each --> change) and see what happens to the values that come after it. The values --> in A27 through A30 start at whatever new value is in A26 and count up by 1. --> These values are recalculated every time a new value is put in A26. How does Excell know what cells to recalculate when we change the value in cell A25 or A26? When we enter the formula in cell A27, Excel remembers that this formula depends on the values in A25 and A26, so when we change the value in either, Excel knows to recalculate the value in A27. Likewise, for the formula cell A28, Excel remembers that this formula depends on the values in A25 and A27, so when we (or Excel) change the value in either, Excel knows to recalculate the value in A28. The same reasoning applies to cells A29 and A30. So, for example, if we change the value in A25, Excel knows to recompute the value in A27; when Excel recomputes the value in A27, it knows to recompute the value in A28. Excel continues to recompute values until it recomputes the value in A30; nothing in the spreadsheet depends on the value in this cell, so Excel stops recomputing values. The method to determine which cells to recalculate when any cell changes is fundamentally important to how Excel works (see a more complicated spreadshet described below) and we will explore this rule and it ramifications throughout the quarter. Later in the quarter we will learn how to instruct Excel to show both (a) precedent cells: what cells a specified cell depdends on, and (b) dependent cells: which cells depend on a specified cell. Precedent and depdendent tracing helps us understand the relationships between cells in a complicated spreadsheet, and might help us debug our spreadsheets. For now, let's extend this example a bit more. -->Change the value in A25 and A26 back to 1 -->Enter the formula =A26^2 into cell B26; notice A26 is a relative reference. ^2 means square the value in A26; that cell displays 1 -->Now copy cell B26 and paste it into cells B27, B28, B29, and B30 with one --> paste operation. Because this formula uses a relative reference, the --> reference will change when pasted (the values in column B will always be --> the squares of the values in column A, directly to their left). These cells --> display 4, 9, 16, and 25 respectively -->Use ctrl/` to switch to formula auditing dispaly mode and observe the --> formulas -->Use ctrl/` to switch to value display mode -->Change the value in A25 and/or A26 a few times (remember to press Enter --> after each change) and notice what happens to the values that come after --> it (as demonstrated above), AND now the changes to values in column B as --> well. Likewise, Excel knows that formula in cell B27 depends on the value in cell A27, so it knows that when cell A27 changes (because cell A25, which it depends on, changes), it recomputes the value in cell B27; same for cells B28, B29, and B30. Again, the method that determines which cells Excel recomputes when any cell changes is fundamentally important to how Excel works, but generally something that we don't have to worry about when building our spreadsheets. In fact, Excel uses this information when cutting/pasting formulas: it knows all cells refering to the cut one, and changes them to refer to the pasted one. Excel just does the "right" thing in all these cases, using the minimum amount of work to update everything. When we start doing more complicated work in Excel, we will be writing formulas that contain combinations of relative and absolute addresses, and it might take a bit of experimenting before we correctly identify which addresses should be relative and which ones absolute. To do this we can experiment, undo choices, and change them, until we get them right. This process is called DEBUGGING a spreadsheet. We will soon learn how we can delete entire rows or columns. Excel will change all the remaining formulas (in other rows and columns, as it did when we cut cells) to still be correct (computing the same values, keeping the same absolute and relative references), so long as the fomulas did not refer to any information in the deleted row/column: in that case Excel will indicate an error. Finally, frequently we will want to paste a VALUE (not a complete formula) into one cell from another. To do so we first copy the cell (as done above), but then click the down arrow underneath the Paste icon on the Home tab, and then click the first icon in the "Paste Values" section: the icon including 123. By doing so, Python pastes just the value from a cell (not the cell's formula) from the copied cell into the selected cell. This operation is useful more than you might first expect. We will see more examples later. -------------------------------------------------------------------------------- 5) Filling: For some common copy and paste operations, there is a shortcut called "filling". We can select a cell and then press on the small square in its lower-right corner (called the "fill handle"). After we press on this square, holding down that button, we can drag it down on its column (or to the right on its row), and fill the new cell(s) with information copied from the original cell(s). The copy and paste operation is more general and has more uses, but filling is a quick way to do a simple copy and paste, continuing in the row or column. -->Enter the value 1 in cell A35 -->Enter the formula =A35+1 in cell A36 -->Select cell A36 and use fill to replicate this formula in cells A37 to A39 -->Use ctrl/` to switch to value display mode -->Notice the formulas filled into cells A37 to A39 -->Use ctrl/` to switch to formula audit dispaly mode -->Delete the contents of A37 to A39 -->Enter the formula =A35^2 in cell B35 -->Select cell B35 and use fill to replicate this formula just in cell B36 -->Select the range of cells A36 to B36 and use one fill to replicate both --> formulas in cells down to A39 and B39 -->Delete the contents of B36 to B39 -->(the formula =A35^2 should still remain in cell B35) -->Select cell B35, and double-click its fill-handle. -->Excel willl automatically fill cells down to B39; it fills cells downward if --> (a) the cell contains contentss --> (b) the cell contains no contents, but the cell to its left has conents, --> which is why Excel stops at cell B39 and doesn't fill B40: there are --> no contents in in A40 There are some advanced uses of filling, in which Excel finds a pattern and automatically continues it. -->Enter the value 1 in cell C35 -->Enter the value 2 in cell C36 -->Select the range of cells C35 to C36 and use fill to cell C39. -->Notice that Excel guesses from the pattern 1 followed by 2 that each --> cell should have a value bigger than the one before it. -->Use ctrl/` to switch to formula auditing display mode and observe there are --> no formulas in these cells: Excel just puts the required numbers there -->Use ctrl/` to switch to value display mode Now, an even fancier use of Fill -->Enter the value 1 in cell D35 -->Enter the value 3 in cell D36 -->Select the range of cells D35 to D36 and use fill to cell D39. -->Notice that Excel guesses from the pattern 1 followed by 3 that each --> cell should have a value two bigger than the one before it. -->Enter the text value Project 1 in cell E35 -->Enter the text value Project 2 in cell E36 -->Select the range of cells E35 to E36 and use fill to cell E39. -->Notice that Excel guesses from the pattern to keep the word Project but --> follows it by in the pattern Excel infers: numbers going up by 1. -->Enter the text value Project 1 in cell F35 -->Enter the text value Project 3 in cell F36 -->Select the range of cells F35 to F36 and use fill to cell F39. -->Notice that Excel guess from the pattern to keep the word Project but --> follows it by in the pattern Excel infers: numbers going up by 2. Finally, note that while you are filling, the value stored in the last cell you are currently filling is displayed. So, for example, if you want to fill the numbers 1 to 100, you can enter 1, 2, and then select both cells and fill until you reach the cell that displays 100 next to it. -------------------------------------------------------------------------------- 6) Computations on Cell Ranges: The final topic of this first lecture is an introduction to "ranges". A simple range in Excel typically specifies a column or row of cells (later we will see ranges that can include multiple rows and columns: a rectangle of values). To specify a range, we specify the first and last cell in the range, separated by a colon. So, if we specify A26:A30 we are specifying a column range containing the cells A26, A27, A28, A29, and A30. Some Excel functions are designed to work with a ranges. For example, one form of the SUM function adds up the values of all the cells in the range. Let us examine its use. -->Enter the formula =SUM(A26:A30) into cell A31 -->You can do this by first typing =SUM( and then either typing A26:A30) or by --> pressing on cell A26 and dragging down to cell A30 and then releasing the --> mouse button and typing ). Try entering this formula both ways -->Format it (see above) to be in boldface -->Now copy cell A31 and paste it into cell B31 (or use horizontal Filling). --> Because both references in the range in this formula are relative, both --> are changed (from A26:A30 to B26:B30). So this new cell computes the sum --> of the B column. Notice the formating is copied as well: it is also bold. -->Use ctrl/` to switch to formula auditing dispaly mode and observe the --> formulas If we start cell A31 not with = but with SUM( then trying to select A26 through A30 will not work, because Excel doesn't think we are entering a formula. -->Try type SUM( into cell A31, then try pressing on cell A26 and dragging down --> to cell A30 and then releasing the mouse button. What happens? Undo the --> attempted change to cell A31. Note that if we wanted to format a range of cells, we would first select the range by pressing on the first cell in the range and dragging to the last cell in the range. Then we would specify that we wanted to format all those cells (see the methods above), typically by right-clicking one of the cells in the range. We will examine one final example of using Excel functions, ranges, and mixing absolute and relative references together. This pattern occurs in many Excel spreadsheet and is a good test of your understanding of many important topics covered in this lecture. -->Use ctrl/` to switch to value display mode -->Enter the text Month 1 into cell B44 and Month 2 into cell C44; use --> horizontal Filling on these two to enter the text Month 1 through Month 6 --> in cells B44 to G44 -->Enter the text $/Month in cell A45 and $/Total in cell A46 -->Enter the values 10, 20, 15, 12, 18, and 20 in cells B45 through G45 -->Enter the formula =SUM($B$45:B45) into cell B46 Note that the range $B$45:B45 has one absolute and one relative reference. This is a legal range, comprising just the cell B45 (summing the value in a range with one cell is fine: it is the value of that one cell). When we copy this cell into other columns, we will see why that range is used and examine why the absolute and relative reference is used. -->Copy cell B46 and paste it into cells C46 through G46 (or use horizontal --> Filling). Each reference will be copied appropriately (the first remains --> the same because it is absolute, the second is incremented by one column --> every time because it is relative -->Use ctrl/` to switch to formula auditing dispaly mode and observe the --> formulas --> in C46 it is $B$45:C45; in D46 it is $B$45:D45; in E46 it is $B$45:E45; --> in F46 it is $F$45:F45; and in G46 it is $B$45:G45 -->Use ctrl/` to switch to value display mode So, B46 displays how much was spent during the first month, C46 displays how much was spent during the first two months, D46 displays how much was spent during the first three months, .... and G46 displays how much was spent for all six months. Each copied range starts with the same absolute address ($B:$45), but ends with a different relative address. This is exactly the form we need so that copying and pasting (or Filling) creates the correct formulas in each cell, giving us a "running total" of the amount of money we spent from the first month, up to the current month. -->Select all the cells in the range A46:G46 and format them in boldface --> collectively. -->Enter the formula =SUM(B45:G45) into cell H45 and verify that it displays --> the same value as G46 (both should display 95). Boldface it too. Your should read this lecture and perform the actions; then reread the lecture and reperform the operations, until you have memorized these fundamental and useful commands and their meanings. Exercize both your mental and muscle memory. To become proficient in using Excel it is not enough to just be AWARE of these commands: you must master them through familiarity gained via repeated use, so when your brain wants something done in Excel, your fingers automatically do the job (much like learning touch-typing) 7) 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 with them in Excel, Know how to start and stop Excel, saving your workbook Know how to create, delete, move, rename, hide, and unhide worksheets in a workbook Know how to identify cells by their column(letter)/row(number) Know how to enter text, numbers, and simple formulas (with references to other cells) in cells; know how to use the Esc key and the undo and redo commands; know how to remove the contents of a cell with the Del key Know how to switch between value display mode and formula auditing display mode Begin to know how to format the values displayed in cells (digits for numbers and boldface for all values) Know how to Copy and Paste (and Cut and Paste) the contents of cells; also know how to copy/paste (cut/paste) the values in cells (not the formulas) Know how to identify absolute, relative, and mixed reference in formulas, how to change among them (using the F4 key), and how each is treated when a formula is pasted (after being copied or cut); have a few simple experiences copying and pasting formulas with all three types of references. Know how to Fill cells (a fast way to do special kinds of copy and paste) Know how to recognize and enter (in formulas) ranges of cells, including copying one cell to a range of cells; know how to apply a simple range function (e.g., SUM) to sum a range of cells, possibly using absolute and relative references Begin to understand how changing the value in a cell causes Excel to recalculate the values in other cells that depend on it, until no more cells need to recalculated. Bottom Line: The purpose of reading these notes is not just to follow the instructions and see the results; it is to understand what you did and the connection between what you can do and what the results will be, in general. So, in new situations where you need to apply the skill, you'll know what to do because you actually learned the skill while following the instructions in these lectures and thinking about what you did.