VIEWS: 3 PAGES: 15 POSTED ON: 1/2/2013 Public Domain
Lab 3 - Spreadsheets and Charts with Microsoft Excel A computer spreadsheet application can contain various types of information including text, numbers, dates, and formulas. Spreadsheets allow for efficient computation of formulas while providing organized storage for the data as well as the computed results. They are effective tools for many business and bookkeeping applications such as maintaining grade books, inventories, and account balances to name a few. Getting Started The spreadsheet application can be opened by double clicking on the Excel spreadsheet icon in the desktop window or by clicking the Start Menu > All Programs > Microsoft Office > Microsoft Excel. The spreadsheet user interface has a regular grid pattern, with numbered rows along the left side and columns across the top labeled with letters of the alphabet. The box at the intersection of a row and column is called a cell and is identified by its row and column designation. Each spreadsheet is called a worksheet. By default there are three blank worksheets in a new spreadsheet file. They can be accessed from tabs labeled: Sheet1, Sheet2, and Sheet3 at the bottom of the spreadsheet interface. Clicking on the tabs will shift from one active worksheet to another. The tabs can be given more meaningful names by highlighting the tab (Sheet1, Sheet2, or Sheet3) and typing a new name, or right-click and then choose Rename. More worksheets can be inserted. Each worksheet has 8 million cells. Figure 1 A Typical Spreadsheet Application Suppose that you wanted to include the following information about your next paycheck in a spreadsheet: Monthly Income (gross) $1500 Medical Insurance $65.00 Life Insurance $39.00 Income tax 15% Final Monthly Check The information can be entered in the worksheet cells as shown in Figure 2. Figure 2 1 In cell B4, we use a formula, which calculates withholding at 15 percent of the monthly income. All formulas start with an equal sign. The numbers used in the calculations are referred to by their cell addresses. So the formula inserted in cell B4 will be typed according to Figure 3 below. Formula Figure 3: To calculate the withholding amounts of the income tax, the formula is entered into cell B4. We can now calculate the final take-home pay, based on another formula inserted in cell B5. The final pay is equal to the monthly income less all the deductions. (Final Monthly Check = Monthly Income – Medical Insurance – Life Insurance – Income Tax) The cell addresses are used in the formula not the actual values. This allows the values in the cells to be changed and the values in the formula cells to be automatically recalculated. (See What-if analysis in your text and below.) When that formula is typed in B5, the value of the final monthly check will be calculated and shown in cell B5. The complete spreadsheet is shown in Figures 4 and 5 below. Figure 5 shows the calculated values in cells B4 and B5. Formula Box Formula Figure 4 Figure 5: Shows nShows The What-if analysis is the biggest advantage of using an electronic spreadsheet. All of the values can be changed and all of the calculations are recalculated automatically because the formulas refer to the values within the cell addresses not the actual values. Suppose, for example, that the next month, you get a raise to 1800 per month. You can use the same spreadsheet, since all the cells that contain numerical data are dynamic. This means that any formula, which refers to cell addresses, will use the current values in those cells in the calculations. In the formulas in B4 and B5, the monthly income is always referenced as cell B1, not the values 1500 or 1800. Given a new value of 1800 for the monthly income in cell B1, the formulas in B4 and B5 will pick it up and properly figure out new values for withholding (B4) and net monthly pay (B5). The revised spreadsheet is shown in Figure 6. Figure 6 2 Formatting the Data Formatting the data allows the cells to be represented in the appropriate context, including numbers, dates and text. To add formatting, highlight the desired cell or group of cells and choose Format > Cells on the menu bar. An attribute dialog box will open with a list of choices about formatting cell contents. You may alternatively right-click after you have highlighted the cells to get a pop-up menu with the option format cells. Add Notes Notes are character strings that are not part of the cells but are attached to them, a kind of annotation. To add a note to a cell, select the desired cell by clicking on it. Figure 7 Next, select Insert > Comment from the menu bar, and the program inserts a yellow box with a black border for inserting text. When you are finished inserting text, just click outside the note box and the note will no longer be visible. To activate the note, move the cursor over the cell near the tiny red spot in the upper right corner (signifying that a note is there), and the note appears temporarily. Figure 8 Formulas and Additional Spreadsheet Features Moving Around in a Spreadsheet Just click inside a cell to make it active. To highlight an entire row or column, click on the number of the row or letter of the column. All the cells will be activated. Dragging over a range of adjacent cells in any direction (over either row cells or column cells) will highlight all of them. Dragging the mouse down diagonally over several rows and columns will highlight all of them. Alternatively you can select cells with the arrow keys (up, down, left, and right) while holding down the Shift key. You can also select one cell and select another while holding all the Shift Key down to select all the cells in between. 3 Adjusting the Size of Rows or Columns For widening columns, move the cursor carefully between column letters without clicking the mouse. When the cursor turns into an arrow pointing left and right, you can drag it with the mouse button held down and widen or narrow the column to any desired width. Figure 9 Adding and Deleting Rows and Columns To add a row, click the mouse in the row where a new row is wanted and choose Insert > Rows from the menu bar. A new blank line will push down the row where you clicked the mouse. To add a column, choose Insert > Column from the menu bar. The new column will be inserted to the left of the selected column. To delete a row, highlight it, and choose Edit > Delete. A dialog box will open that offers Delete entire row as an option. See the figures below. Figure 10 – Dialog box to from selecting Insert > Cell Figure 11 – Dialog box to delete cell(s), row(s) and/or column(s) 4 Worksheets: Adding, Deleting and Renaming Remember by default a spreadsheet comes with three worksheets. To add or delete a worksheet, choose the commands Insert > Sheet or Edit > Delete Sheet from the menu bar. You can also right-click on the tab and choose insert > worksheet or choose rename to put a new name on the tab. More about Spreadsheet Formulas Creating Formulas In Excel there are three ways to enter a formula: by typing it directly into a cell, by typing it into the Formula box, and by using the Automatic Formula function to create it. In our earlier example, we could easily type the two formulas directly into cells B4 and B5. Excel always uses the equal sign to start a formula. It is the equal sign, which tells the spreadsheet program that a calculation is coming in that cell, instead of a number or label or date. When you press enter, the formula will be inserted into the cell. Later, to see the formula, just click on the cell and the formula will be displayed in this formula box. If it contains an error or needs to be changed, it can easily be edited here before pressing the Enter key again. Spreadsheet Functions Sometimes it is not convenient to create very long formulas. For instance, to enter the following formula for 100 figures: = (J1+J2+J3 … J100) / 100 For cases like this, the spreadsheet software has already stored functions for many common operations (mathematical, statistical, and logical). These functions can be activated with Insert > Function in the menu bar, which brings up a dialog box. Having chosen a function, the user then gets a function shell in the Formula Bar and must put in the proper cell addresses before the formula is carried out. In this case, the function needed is called AVERAGE, and the formula for averaging would look like the following after it is filled in with the cell addresses: = AVERAGE (J1:J100) Figure 12 – Dialog box after selecting Insert > Function on the menu bar Figure 13 – Dialog Box after selecting Average, and the appropriate cells to be used. 5 Figure 14. If the cell chosen for the result is at the bottom of a column, the cell range will be automatically selected as that column. You can also just enter the cells to be averaged or the range of cells. Built into the function is the summing of the numbers in cells J1 to J100 and dividing them by 100, the number of elements. In a case like this with a range of cells, you specify them with shorthand of the first and last separated by a colon. Without using the Insert menu, the AVERAGE function can also be typed directly into the Formula Window with the elements inserted within the parenthesis, using the colon as a separator. Figure 15 – Manually typing the AVERAGE formula into the formula box. The Automatic Formula Function A feature of Excel which allows you to use functions easily is the Automatic Formula function. Its icon, located next to the icon, resembles an Fx. Let us examine the Automatic Formula function in detail. Figure 16 - First click the cell where the formula goes, then click the Automatic Function Icon. 6 Figure 17. Within the dialog box, choose the category and appropriate formula. To select a formula, double click on its name, and another dialog box opens up. Figure 18 –Click on the button pointed to by the arrow and then highlight all cells that are part of the calculation. A description of the formula appears at the bottom of the dialog box. Above that, the dialog box shows the user how to fill in the arguments. After the correct arguments are inserted, the result is displayed below the range in the menu box. If it is correct, choose OK, and the formula will be inserted into the cell. Figure 19 – The red arrows point to the result of this Sum function. It is possible to put literal numbers instead of addresses in the argument of a function like AVERAGE or SUM, and the formula will average them. Doing this, the user is forcing the function to work on specific numerical data rather than the value typed into the addresses, such as A1, B2, etc. Excel has a great variety of prepared functions like AVERAGE or SUM already created for use. Perhaps you should open the Automatic Formula function dialog box and check some of them out before writing formulas for yourself. As you move through the list of functions, one click on any formula name in the dialog box will bring up a description above the Formula text box (remember that a double click brings up the formula for inserting arguments). Take a moment to look at some of these possibilities to see how they work. You can make your own formulas. For example, suppose cell A10 looks like this: = 2+3*4 7 Figure 20 In this case, the formula is calculating with numbers, not cell addresses. A question remains: is the answer 20 (2 plus 3 times 4) or 14 (3 times 4 plus 2)? The answer is 14, since the spreadsheet follows the rules of algebra, which carries out multiplication and division before addition and subtraction. To overrule this convention, use parenthesis liberally. In other words, to get 20, the formula in cell A10 should be = (2+3)*4 Figure 21 Changing the Formula A spreadsheet cell can also be used to make logical choices for the user with the IF function. As an example of its use, consider the following case: if the price of Stock A reaches $40, then I will sell; if not, I will buy more in the next month. A condition is checked with two alternative actions. The IF function allows the spreadsheet to examine a changing condition and carry out different actions based on the changes. The general construction of the logical IF function is as follows: =IF(logical-condition,true-value,false-value) To see how it works, consider the following IF function located in cell A12: =IF(A6>A7,A11-A10,0) The function will compare the values in cells A6 and A7. If the value in A6 is greater than the value in A7, then the true case applies and the value in A10 will be subtracted from the value in A11. The result will be placed in cell A12, where the IF function is located. If the value in A6 is not greater than the value in A7, then the value 0 will be placed in the A12 cell. Figure 22 – IF function with sample values 8 The three part IF function can be understood in logical terms: IF the value in A6 is greater than the value in A7 THEN subtract the value in A10 from the value in A11 and place the result in cell A12 ELSE put zero in A12 Figure 23 – Results of the Formula in the Spreadsheet IF function makes the spreadsheet a very powerful tool for making logical choices by having the ability to test and choose what to do next depending on the results of the test. In a large and complex spreadsheet, a good rule of thumb is to consider adding a note to a cell containing any complicated formula to remind yourself what you were intending when the formula was created. Several weeks and hundreds of cells later, you may look at this cell and forget what it is supposed to be calculating. The text note will be a reminder of the thinking which created it. Remember that we used a note in the take-home pay example which introduced the discussion of spreadsheets. Filling Adjacent Cells with Data or Formulas One of the most useful features yet to discuss with spreadsheets is the concept of filling adjacent cells with data of formulas rather than typing or cutting and pasting them manually. For instance, suppose that you have a cell, D6, with a number 10 in it. The next several cells from D7 to D10 should be increased by 10 each so that D7 will have 20 in it and D10 will have 50. A shortcut to typing all this involves filling. First put the formula =D6+10 in cell D7. That will take care of the 20 which goes there. With that cell highlighted, place the cursor on the bottom-right corner of the highlighted cell till you see a crossbar. Drag the mouse over all the cells through D10. When you release the mouse, the formula will be applied to all the cells, making each 10 larger than the one above. An even quicker solution to this example uses the automatic fill handle. First put a 10 in cell D6 and a 20 in D7. Then select both of these cells with the mouse. You can grab the fill handle on the bottom-right corner of D7 as explained above and drag it down through cell D10. When you have finished, all cells will have the correct numbers, each one being 10 greater than the one above it. The program recognizes the relationship between the first two cells (the increment of 10) and continues to apply it across the whole range. This will work with more complex relationships or with a single value. If you need a column with all of the values equal to 100, you can type 100 in the first cell an drag the fill handle down to fill the other cells. Formula inconsistency If a formula is inconsistent with other formulas in nearby cells the inconsistent formula will be flagged with a little triangle in the upper left corner of the cell. When the cell is selected the AutoCorrect Options button 9 appears. If you click the button, a menu opens with more options including copying the formula from other cells, correcting errors in formulas, and ignoring the inconsistency. Displaying all formulas Use Control ~ to display all of the formulas on the spreadsheet instead of the values. You can then edit the formulas. Press Control ~ again to return to the calculated values. Toolbars and Other Spreadsheet Concepts Let us look at some of the additional special features of Excel that make using spreadsheets easy and convenient. One is the icon on the formula toolbar next to the Auto Function icon. It is called the Sum icon. If you need to sum up a row or column of figures, you can do this with the shortcut involving one mouse click. For instance, let us assume that row 4 has four numbers in it, columns B through E. If you click cell F4 and then the Sum icon, the formula =SUM(B4:E4) will be created for your approval. The icon then turns into two icons, a checkmark to Accept or an X to cancel the formula before it is executed. If you accept the formula, the sum of the numbers in row 4 will be in cell F4. We shall assume cell A4 has no numbers in it; maybe it carried a text label. Nevertheless, the sum of the four legitimate numbers will be carried out. If A4 also contains a number, the sum will be for the five consecutive numbers in row 4. This Sum icon is useful for cases where a quick addition of consecutive cells, either in rows or columns, is needed. Formatting The formatting toolbar includes all the typical formatting features that were used in the word processor to be used in displaying the values in the cells. Further to the right of the formatting icon are a number of icons suitable for formatting numbers in cells. There are icons for displaying a cell number as a currency or percentages. There are also icons to align figures within a cell to the left, right or center of the cell, depending on the size of the cell. These options and many others can be found under Format > Cells on the menu bar. Filtering and Sorting AutoFilter can be accessed through the menu bar by selecting Data > Filter > Autofilter. Using the filter, you can split out individual sections of your spreadsheet for inspection. For instance, you might want to look at categories of expenses in a very large budget spreadsheet. The individual items for the same category could be rows or columns away from each other and not readily examined. However, with the filter, they will be collated and shown in their own window. AutoFilter places a small dropdown arrow over the selected column or columns if all are to be examined. Using the drop-down arrow choose the category of information to filter. The first choice, all, is the default. For example, you could look at all travel expenses among business items. When the data is filtered it is done over the existing worksheet, no new windows open. A second mouse click on the AutoFilter icon will return you to the full spreadsheet. AutoFilter > Show All is designed for looking at the filtered information. For sorting, use the dialog boxes under Data > Sort from the menu bar, which allows sorting on more than one field. It gives you the option to select what column to use as the sorting category and whether to sort in ascending or descending order. You may also select second and third sorting columns which will be used in case there are two cells with identical data in the first sort criteria column. Goal Seek Another powerful feature of Excel is Goal Seek, available under Tools > Goal Seek. Before seeing it in action, let us look at a situation where it is used. Suppose that you are trying to sell 100 computers, fifty of which cost you $400 apiece and the other fifty which cost $500 apiece, and want to make a profit of $50,000. What price should you sell them for? Goal Seek is the ideal for any situation in which you know the desired result, a profit of $50,000 in this case, before you known one important item that is needed to achieve the result, the sales price for each computer. The relationships between the elements are known: 10 50 * (sales price – 400) + 50 * (sales price – 500) = 50000 In the spreadsheet, the formula needs to be placed in one cell, and another needs to be selected for the unknown value, the sales price. For instance, cell A1 could contain the formula, with cell B1 chosen for the unknown sales price. The formula above is then typed in A1 as follows: =50 * (B1 – 400) + 50 * (B1 – 500) After choosing Goal Seek under Tools, a dialog box appears, containing the following three lines: Set Cell, To Value, By Changing Cell. In the Set Cell, we enter A1, this is our formula cell. In the To Value cell we enter 50000, our target value. Finally, B1 is entered in the By Changing Cell, where the unknown value will go. After all of these are inserted and the OK button clicked, the program returns a message that the Goal Seek worked. The unknown value is 950. In other words, if all 100 computers are sold at $950, the profit will be $50,000. This feature offers a powerful tool to someone who knows the bottom line of some process but is missing a key component of the process. After it is set up, Goal Seek also allows one to change the bottom line quickly and see the results. Making Charts and Graphs from Spreadsheets Charts and graphs can be made in Excel from data in a spreadsheet. To create a chart, select Insert > Chart on the menu bar or select the Chart Wizard icon, which resembles a multi-colored bar graph. The Chart Wizard will pop up on the screen as a series of four dialog boxes. In the first dialog box the type of chart and the orientation or sub- chart are selected. The second box gives the user the opportunity to select the items to be charted by selecting the data in the spreadsheet. If an area was selected before the Chart Wizard was opened that area will appear in the dialog box. You can change that area by highlighting another area of the spreadsheet with the mouse. The next dialog box allows you to insert titles, legends, and labels. The fourth dialog box gives the option to choose where the graph will be displayed, either in one of the existing worksheets or in a new worksheet. The graph can be dragged to a new location or copied to another document after if is displayed. There is help box in all of the Chart Wizard Figure 24 dialog boxes. 11 Figure 25 – Chart Wizard Step 2. You can click the button pointed to by the red arrow to roll the box up to allow you to highlight the desired cells. Figure 26 – The series tab. Add a new series by clicking the button pointed to by the red arrow, and highlighting the cells corresponding to the series. Re-click the button to return to the prompt. Presses add to add the series. You may also select a set of cells for the category X-axis label for each series. These cells will be used to label the intervals between tick marks on the X-axis. Note that you may either select your chart data using data range or series. Do not do both. 12 Figure 27 – Chart Wizard Step 3. You choose how you want the chart labeled. Moving and Resizing a Graph Once you have pressed Finish, the graph will be displayed on the worksheet where you have chosen to put it. If you click once on the graph, it will be selected as an object that can be moved around or resized. You will notice that at the edges there are little boxes. Grabbing them with the mouse allows stretching or shrinking the graph to fit your needs. Also you should be able to select the graph and move it around on the worksheet where it was created. It can also be cut or copied and pasted in a new location, in another worksheet, or in another document. Clicking on individual elements of the graph, such as the title, legend or labels, will also allow them to be edited. . Selecting Non-Adjacent Cells for Graphing You may need to graph cells that do not lie adjacent to each other in a spreadsheet. Click in the first cell, then hold down the Control key, and click on the other cells to select, wherever they are in the worksheet. When you have finished, you will have selected a group of nonadjacent cells that can be graphed. Editing a Chart or Graph Often you forget something or need to make changes once a graph has been created. Excel has options to allow further editing after the graph is created. If you move the cursor onto the chart and right-click, a shortcut menu appears that allows editing many of the features of the graph. A different menu will appear if you right-click on the legend or labels. Double-clicking on the chart will open a menu box that allows you to make many format changes including changing the colors in the chart. Macros If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task. For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps within the cell. 13 Recording a Macro 1. On the Tools menu, point to Macro, and then click Record New Macro. 2. In the Macro name box, enter a name for the Stop Relative Recording Reference Figure 28 Notes The first character of the macro name must be a letter Other characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator. Do not use a macro name that is also a cell reference or you can get an error message that the macro name is not valid. 3. If you want to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #. Note The shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open. 4. In the Store macro in box, click the location where you want to store the macro. If you want a macro to be available whenever you use Excel, select Personal Macro Workbook. 5. If you want to include a description of the macro, type it in the Description box. 6. Click OK. 7. If you want the macro to run relative to the position of the active cell, record it using relative cell references. On the Stop Recording toolbar, click Relative Reference so that it is selected. Excel will continue to record macros with relative references until you quit Excel or until you click Relative Reference again, so that it is not selected. 8. Carry out the actions you want to record. 9. Click the Stop Recording button once you are finished. Running a Macro 1. Open the workbook that contains the macro. 2. On the Tools menu, point to Macro, and then click Macros. 3. In the Macro name box, enter the name of the macro you want to run. 14 4. Click Run. If you want to interrupt, press ESC. Start a Macro from a Keyboard Shortcut 1. On the Tools menu, point to Macro, and then click Macros. 2. In the Macro name box, enter the name of the macro you want to assign to a keyboard shortcut key. 3. Click Options. Assign a shortcut key to the macro if you have not already done so as explained above. 4. Click OK. 5. You may now use the assigned shortcut key to start the macro. 15