VIEWS: 8 PAGES: 12 POSTED ON: 12/22/2009
QUICK GUIDE TO BASIC Microsoft Excel functions Below is an Excel spreadsheet of violent crime statistics for 2000, published by Statistics Canada. To total up a column of numbers: Using the autosum button: 1. Place mouse in a cell below the last figure in the column. 2. Click on the autosum button on the toolbar. 3. If it picks the correct range to add up, push ENTER to accept it. Otherwise, edit the range and then push enter. Using a standard formula: 1. Determine the range of cells you wish to add up. 2. Enter the formula in the cell where you would like the answer to appear, beginning with an equal sign. 3. When you have entered the formula, push ENTER. Examples: To add up a range of cells from B2 to B28: =SUM(B2:B28) To add up a discontinuous range of cells from B2 to B28 and B34 to B39: =SUM(B2:B28,B34:B39) To add up a series of discontinuous cells: Example: =SUM(B2,B8,B24,B66) To add just a couple of cells, example, B2 and B3: =B2+B3 To multiply two cells: Eg: =B2*C2 To subtract one cell from another: Eg: =B2-C2 OTHER FUNCTIONS There are many many more spreadsheet functions. For example, AVERAGE will calculate the average among a range of values and MEDIAN will calculate the median. ( EG =AVERAGE(A2:A26) calculates the average value in the range of cells from A2 through A26). There are dozens more, including some mighty impressive statistical stuff that you should steer clear of unless you know what you are doing. Complete details on these functions are in the online help in Excel but they all follow the same pattern as SUM so you will probably be able to guess. PERCENTAGES: To calculate percentage change, subtract the old value from the new value, and divide by the old value. The formula is (New-Old)/Old. Eg: =(E2-D2)/D2 To calculate what one number is as a percentage of another, divide the first number by the second and format the formula cell using the percentage button on the toolbar. In the next illustration, a formula is being entered in cell G3 to determine what cell F3 is as a percentage of cell B3. The formula entered in cell G3 is: =F3/B3 This is the result: One you have calculated a percentage, or done any other calculation, you can copy the formula into adjacent cells by placing the mouse in the lower right hand corner of the cell where you entered the first formula. When the cursor turns into a small plus sign, drag it to the bottom or end of the range of cells you want to fill, or if you are copying the formula downward, simply double click on the corner of the cell. The formula will be copied automatically, as you can see from the illustration on the next page: You can continue to copy formulas downward or to the right. As you copy the formula into new cells, the cells the formula adds up, divides, etc, will move the same number of cells up or down or to the left or right as you move the formula itself. The following illustration shows how a formula changes as it is moved from cell to cell: The one way to alter this behavior is to place a dollar sign before one or both of the coordinates in a cell reference. If you do so, that coordinate will be fixed in place. So, if you enter a cell reference as $D8, the cell reference can move up and down, through the range of numbers, but not left to right through the range of letters. If you enter a cell reference as D$8, the opposite is true. The cell can move left to right through the letters, but cannot move up and down through the numbers. If you write it as $D$8, it is frozen in place and cannot move in either direction. The dollar sign is typically used to freeze totals and other such entries in formulas. You can also copy and paste formulas from one cell to another, using standard copy and paste commands. SORTING: To sort a spreadsheet, according to one or more columms, first highlight the entire block that will be sorted, as in the following illustration. Do not include header rows, totals and other rows you do not want to sort: Click on Data, Sort, as in this illustration: This will bring up the following dialogue box: Now, pick the column or columns you want to sort on, choose ascending or descending order in each case, and click OK to do the sort. Excel has a couple of shortcuts for sorting, but both can cause problems. You can place a cursor in the first row to be sorted, and choose data/sort. Excel will choose a range to be sorted. As long as there are no gaps in the range, either empty rows or columns, Excel will generally get it right, but not always. So, be careful. A partially-sorted spreadsheet is a useless spreadsheet. You can also place your cursor in the column you want so sort on, and click on one of the quick sort buttons Excel will automatically sort, but again, it can make mistakes. I do not like to leave this much up to a computer program and would advise you be similarly cautious. A good practice is to save your sheet under a different name before sorting so you can recover the original if you make a mistake. FILTERING Excel has the ability to exclude certain rows from what you see on the screen through a trick called “autofilter.” This is a rudimentary database function and you will use it most often when you have a column of information you want to narrow down. To activate autofilter, choose FILTER/AUTOFILTER from the drop-down DATA menu. As soon as you do, you should see a series of arrows appear in the cells across the top of the worksheet. These arrows allow you to select from among all of the unique entries in a particular column and have the spreadsheet narrow what you see down to only rows including that entry. In this file of Hamilton building permits issued in Jan. 2001, you can use autofilter to show only permits issued in Hamilton: You also have the choice “top 10” which will give you the 10 largest entries. You could try that with the construction value column. Choosing “custom” allows you to set more complex filter conditions. In the next illustration it is being used to choose projects whose value is between $100,000 and $200,000: Here is the result. The rows included are shown at the left: To restore all of the rows after using autofilter, choose data/filter/show all on the menu bar. GRAPHING One of the most useful functions of Excel is its ability to create graphs and charts. These allow you to “see” your data. To quickly create a chart, highlight the area you wish to chart, and push f11. This will create a quick and dirty chart using a default chart type. To exert more control, use the chart wizard. Begin by highlighting an area that includes the columns/rows you would like to graph, as in this illustration: Next, click on the chart wizard button the toolbar. That will bring up the chart wizard, as on the next page: Pick the type of graph or chart you would like to create, and click next. Click on the series tab, and remove the series that refer(s) to column(s) that you do not want represented on your graph or chart. In this case, we remove series 1, which is a range of cells in column B, the actual incidents column. We only want to chart crime rates: In this case, this leaves only series 2, the one that contains the crime rate. The box marked “Category (X) axis labels” contains the reference for what will appear along the X axis of the graph, in this case the city names. Once you have narrowed down to the series you want to chart/graph, click next. At this point you can give your chart a title, and label the X and Y axes if you so choose. Click next, and choose whether to display your chart as a separate worksheet, or place it in your existing worksheet, along with your numbers. Click Finish to create your graph: In a bar graph such as this, you can force it to display the values from largest to smallest or vice versa by sorting the main worksheet on the appropriate column. The result is on the next page: Charting is an extensive topic. To find out more about the techniques, and the different charts available, look in Excel help under Create a Chart.
Pages to are hidden for
"QUICK GUIDE TO BASIC Microsoft Excel functions"Please download to view full document