Microsoft Excel Tutorial

Microsoft Excel Tutorial Prepared by Carla Garrison, Formula sheet by Joan Palmer Microsoft Excel is a spreadsheet program that allows users to organize data, complete calculations, graph data, and create reports. The Worksheet When Excel starts, it creates a blank workbook, called Book 1. The workbook is like a notebook. Inside the notebook are sheets, these are known as worksheets. The name of the worksheet below is Sheet1. You can add more worksheets to your workbook if necessary. The worksheet is organized into a rectangular grid containing vertical columns and horizontal rows. A column letter above the grid, called the column heading, identifies each column. A row number on the left side of the grid, called the row heading, identifies each row. The intersection of each column and row is a cell. The cell is the basic unit of a worksheet into which you enter data. You will refer to each cell by using its cell reference. The cell reference is the coordinates of the intersection of a column and a row. For example, the cell that is the intersection of column D and row 8 would be called cell D8. In the worksheet shown below, notice that cell A1 has a heavy border surrounding it. This indicates that it is the active cell. Also note that the column heading for column A is highlighted and the row heading for column 1 is highlighted. There is also a reference to cell A1 in the Name Box. Name box with active cell reference Highlighted row and column headings indicate cell A1 is active Heavy border surrounds active cell – A1 Columns G, H and J Rows 8, 10 and 12 Sheet tabs 1 The Standard toolbar and the Formatting toolbar contain buttons and boxes that allow you to perform task quickly. You probable recognize many of these tools if you are familiar with any of the Microsoft Office suite of programs. The diagrams below show what each tool is for. Standard Toolbar Formatting Toolbar Formula Bar As you type information into cells, Excel displays the information in the formula bar. Excel also displays the Active Cell Reference in the Name Box on the left side of the formula Bar. Name Box Insert Function Box 2 Select a Cell When you are ready to enter data into a cell, you must first select the cell. The easiest way to select the cell is to click on it with your mouse. You can also use the arrow keys on your keyboard to navigate the worksheet and select the intended cell. You know that a cell is selected, or active, when a heavy border surrounds the cell and the active cell reference appears in the Name box on the left side of the formula bar. Excel also highlights the appropriate column and row headings. Let’s Create and Format a Worksheet For this example, let’s create a worksheet that shows what college expenses might be incurred during a four year period for a typical student at a local college. First select cell A1 and enter the Title of my chart – COLLEGE EXPENSES Then select cell A2 and enter the SubTitle of my chart – West Liberty State College The following diagrams show these entries: Notice that “COLLEGE EXPENSES” is bold and the font has been changed to 16. This formatting works the same way it does in Word. Even though the words are larger than the A1 box, the whole title will show when printed. Remember though, that when you are editing or formatting the words “COLLEGE EXPENSES” you must have the cell A1 selected. See that “West Liberty State College” underlined. Remember that you can format the text in your worksheet in much the same way that you can in other programs –just be sure that you have the correct cell selected when you begin. You may be wondering at this point if we can make these headings look better by centering them over our data. We can and we will by using the Merge and Center button on the toolbar. We will wait until we have our data in place so that we know what we are centering over. 3 Now we will enter our row and column titles: Our row titles are much longer that the actual cell size. We can increase the width of column A by clicking on the bar between column header A and column header B and dragging toward the left. We can also do this to our Freshman, Sophomore, Junior and Senior columns to give them some more space. Column widths can also be modified by clicking on Format, Column, Width (on the menu bar) and entering a value. 4 Now we can enter some values into our cells. To calculate the total expenses for Freshman year, we would first select the cell that would hold the total. In this case, that is cell C12. After selecting C12 with the mouse. Click on the AutoSum button on the Standard toolbar. Excel will respond by displaying “=Sum(C5:C11)” in the formula bar. Excel also surrounds the proposed cells to sum with a moving border called a marquee. Excel is waiting for you to verify that it has chosen the proper values to AutoSum. These are the proper values so now click the AutoSum button again and value will show in C12. Now we will want to add up the totals for the rest of the columns (Sophomore, Junior and Senior). Excel makes it easy to carry formulas and calculations over to similar columns by using 5 the Fill Handle. Notice below that the cell C12 is active. Place the mouse on the box in the bottom right hand corner of the cell. Click and drag the box across the columns that need added. When you let go of the mouse, the columns will be added automatically. In order to get the total for Room and Board for all 4 years, select column G5 and click on the AutoSum button. 6 After verifying to Excel that it has selected the proper numbers to add by clicking again on the AutoSum key, you can then use the Fill Handle to get the totals for the rest of the items. Now we can put a heading on our Totals column and row. Also, since these are money amounts, we should format the columns for currency by selecting the column and then selecting the Currency Style (looks like a dollar sign) button on the Formatting Toolbar We can format our headings to be bold and centered by selecting each one and choosing Bold and Center Alignment from the Formatting Toolbar 7 We could format our currency data in a more detailed way by selecting the currency data and choosing Format, Style from the Menu Bar. This screen will appear: We can choose Modify to make specific changes. Currently we are formatted using “Accounting” type of numbers. If we didn’t want the dollar signs to show, we could change this to “Number”. 8 Now that our data has been inserted, we can improve the look of our worksheet by centering and merging the title lines. To do this, select the words “COLLEGE EXPENSES” and drag your mouse across the worksheet to include column G. 9 Now choose the Merge and Center button on the Formatting Toolbar. The Title line will now be centered across our worksheet. Do the same for the SubTitle line. The next thing we will do is create a Pie Chart showing the costs for the Freshman year. In order to create a chart, you must select the data that will be in the chart. As you will notice, we have an empty column (B). We left this column empty simply because it made our data look more visually appealing. But, when you are going to create a chart or graph, you do not to select any empty data columns. We need to delete column B. We can increase the width of column A to get the desired effect of having some space between our Row Titles and our data. To delete column B you should click in the Column Header for column B. This action will select the entire column. Now right click on your mouse button to reveal the short menu and choose Delete (for this action you could have also pressed the Delete key on your keyboard.) 10 After deleting column B, increase the width of column A so there is some white space between the Row Titles and the actual data. Now choose the data that you want to chart by selecting it. Start at Cell A5 and drag across and down to include Cell B11. (Do not include the Total row in your selection) Click on the Chart Wizard button on the Standard Toolbar. 11 Step 1 of the Chart Wizard asks you to choose the type of chart you want to create. Choose Pie Chart. (Since a Pie Chart show the contribution of each value to a total, we did not want the Total row to be included in our selected data to chart.) Sometimes it is difficult to decide which type of chart you want. Notice the “Press and Hold to View Sample” button. When you press this you will get a picture of what your chart will look like. If you don’t think it is the correct way to present your data, just choose a different chart type or sub-type and view the sample again. When you are happy with the preview of your data, click on Next. 12 Step 2 of the Chart Wizard shows you what the Data Range is for your chart. You will notice that your worksheet is showing a Marquee around the selected data. You could change your selected data now if you needed to. You could do this by changing the references in the Data Range line or by dragging the Marquee around the new data on the worksheet. We don’t need to do anything here for this example. Step 3 of the Chart Wizard allows you to enter a Title for your chart. We will call ours “Freshman Expenses” 13 Your title will show be displayed immediately. If you didn’t want to show a legend or you wanted to change it’s location, you could click on the Legend tab now. To remove the Legend unclick the Show Legend checkbox. To move the Legend, click on one of the Placement options given. Another option in Step 3 is Data Labels. You can choose to show the Series name, Category name, Value or Percentage with the Pie Chart. If you choose to not have a Legend, you would probable want to use one of these options to explain your chart. The next page shows some examples. 14 This is an example of using the Category Name label. It looks a little messy in this particular case, but you could increase the size of the end chart to make it look better. This example shows the actual values as Data Labels. Once again, increasing the size of the end chart will help the appearance of this option. You could also choose to show the actual percentage as a data label. For this example, we will leave the Category Name and Percentage as the data labels. 15 Step 4 of the Chart Wizard asks you where you want your new chart to be located. Do you want it to be a part of the same worksheet you are working on or do you want it to be a new sheet in the current workbook. For this example, lets just put it in our current worksheet. Now click Finish. Your new chart will show up as a part of your worksheet. Notice the “handles on the corners and sides, these handles allow you to resize the chart and move it to a different location on your worksheet. To resize the chart, “grab” one of the corner handles by pressing and holding your mouse button and dragging it outward to make the chart larger, or inward to make the chard smaller. To move the chart, just hold you mouse button down inside the white space of the chart and drag the chart to the location you desire. Now move your chart so that it is not covering up your data in the worksheet. As you notice, our Data Labels look better in the finished chart than they did in the Chart Wizard Preview. 16 You can do a lot of formatting within your chart if you wish. It is a little tricky though. You must have the appropriate area of the chart selected in order to format it. Here are some examples. In order to change the formatting of the Data Labels you must click on the data labels with your mouse to create this selection effect. Then right click you mouse button to show the short menu and choose Format Data Labels, you will receive the Format Data Labels dialog shown here and you can make the changes you wish. If you had chosen “Clear” from the short menu, the labels would have been removed completely. You can double-click on a specific Data Label to enter it’s textbox and edit the text. 17 If you click on the Legend area and then right click to show the short menu, you can select Format Legend. You will receive the Format Legend dialog shown on the right and you can make formatting changes to the legend. Many other formatting and editing options are available by selecting the chart, right clicking and choosing one of the options on the short menu. Saving a Workbook Don’t forget to save your work as you progress. Click on the Save button on the Standard Toolbar or click File, Save from the Menu Bar to save your work. Some Other Things You Can Do in Excel Excel is incredibly powerful and has hundreds of features. There is no way you could cover them all in any tutorial. This tutorial will continue with some of the most used features. Don’t forget that you have extensive help files available to you by clicking Help, Microsoft Excel Help on the Menu Bar. Hiding and Unhiding Columns 18 There may be times when you want to “hide” a column from view. You may not want the column to print in your current report, but you don’t want to delete it entirely. You can select the column by clicking on the Column Heading and then right clicking to reveal the Short Menu, choose Hide. Now the column cannot be seen. In order to Unhide the column, you must first select the column before the hidden column and the column after the hidden column, right click to reveal the Short Menu and then choose Unhide. You column will be visible again. To Hide – Right click column, choose Hide To Unhide – Right click columns, choose Unhide Use AutoCalculate to Determine an Average If we wanted to calculate the Average Cost Per Year for the data in our worksheet, we could first enter an appropriate title for the number and then select the cell that will hold the number. Now look at the AutoSum button on the Standard Toolbar. Click on the down arrow shown at the right of the AutoSum button. 19 The chart below shows the options you are given and what they do. We will choose Average for our example. Command Function Sum Displays the sum of the numbers in the selected range Average Displays the average of the numbers in the selected range Count Displays the number of nonblank cells in the selected range Max Displays the highest value in the selected range Min Displays the lowest value in the selected range More Functions: Provides a way to access the many other functions available in Excel After choosing Average, Excel will try to “guess” which values you want to average. In this case Excel is wrong. What we want to do is move the marquee to the yearly totals values so that we can average them to find the Average Cost Per Year. Now click the AutoSum key again to accept the range of values. The Average Cost Per Year is now shown in cell B14. Also notice that the formula used is shown in the Formula Bar. Correcting Errors After Entering Data into a Cell 20 If you find an error after entering data into your worksheet, you can correct the error by selecting the cell and retyping it within the cell itself. If there is a lot of information in the cell, it may be easier to select the cell and then notice that the cell contents are being displayed in the Formula Bar. You can click in the Formula Bar and edit the contents of the cell there. Print Preview and/or Print a Worksheet To print preview your worksheet, click on the Print Preview button on the Standard Toolbar. If the preview looks OK, then just click Print, if not, then choose Close and move your chart or edit your worksheet as you would like. A dotted line will be displayed on your worksheet. This 21 line indicates where the printed page will be. In this example, notice that the Total column and the right edge of the chart will not be printed on the first page. We will probably want to resize our columns or change to Landscape view to allow for all of our data to print on the same page. If you click on the Chart itself before entering Print Preview, the Chart is the only thing that will show in Print Preview. This allows you to print just the chart if you like. Insert a Row to the Worksheet To insert a row to your worksheet, select the row that is below where you want your new row to be. Right click and reveal the Short Menu and then click Insert. A blank row will be inserted above your selected row. Delete a Row from a Worksheet To delete a row from your worksheet, select the row you want to delete. Right click to reveal the Short Menu and then click Delete. The selected row will be deleted. Clear the Contents of a Row If you don’t want to delete the row, but you do want the contents to go away, select the row you want to clear. Right click to reveal the Short Menu and click Clear. The row will stay but the contents will disappear. 22 Insert a Column to the Worksheet To insert a column to your worksheet, select the column that is to the right of where you want your new column to be. Right click and reveal the Short Menu and then click Insert. A blank column will be inserted before your selected column. Delete a Column from a Worksheet To delete a column from your worksheet, select the column you want to delete. Right click to reveal the Short Menu and then click Delete. The selected column will be deleted. Clear the Contents of a Column If you don’t want to delete the column, but you do want the contents to go away, select the column you want to clear. Right click to reveal the Short Menu and click Clear. The column will stay but the contents will disappear. Excel will not clear the contents of a merged cell. If your column contains Merged Cells, Unmerge those cells before trying to clear the whole column. Don’t Forget About the Undo Button Excel provides the Undo command via the Undo button on the Standard Toolbar, clicking this button will undo your last action. This button can be a lifesaver, it will allow you to try different types of formatting without fear of ruining your worksheet. Just try the change, if you don’t like the look of it, just click the Undo button. Web Sources for Other Excel Tutorials: http://www.baycongroup.com/el0.htm http://www.functionx.com/excel/ http://isds.bus.lsu.edu/cvoc/learn/introit/excel/ http://www.chem.tamu.edu/class/majors/commonfiles/exceltutor.htm http://www.chemistry.usna.edu/plebechem/excel_tutor/homepage.html 23 Addition Summation Key: Subtraction Division Absolutes Median Maximum Minimum Mode Average Algorithm Brackets Counting Item Round Advanced Rounding Ceiling Floor Odd Even Square Root =(A1+B1) =sum(A1:F1) {The : represents through} =sum (A4,B12,E17) {Allows addition of nonadjacent cells} Click onto the first number and drag to the first empty cell beyond your list. Press the summation key and the formula will appear in the empty cell. =(A2-A8) =(A2/A8) To set an absolute cell reference, place a $ sign in front of the cell referenced in the formula. E.g. =sum($B$2/b5) This formula would always refer to cell B2, even when the formula is copied into another cell. =median(B1:B15) =max(B5:B15) =min(B5:B17) =mode(B5:B15) The mode is the number most often found in a series. =average (b5,c10,d12) or =average(b2:b10) =(C2-D2)-(E2+F2) or =(C2*(D2-E2)) The operation that appears between the two sets of brackets will be calculated first. If there are brackets contained within brackets, the set that is in the interior will be calculated first, and the outside set of brackets will be calculated last. =count (select the items to be counted, close the parenthesis and hit enter) This will count how many items ore entries are in a long list of numbers. =round(A6,1) This formula would round the designated cell to a 1/10 position after the decimal. =round(A6,3) would round to a 1/1000 position after the decimal. =Ceiling(B5,0.25) This example would round a dollar figure upward to the nearest quarter. =Floor(B5,0.05) This example would round a dollar figure downward to the nearest nickel. =odd(B6:B14) This example would round all numbers upward to the nearest odd number in the designated range =even(1.23) This example would round this reference to the next even number of 2. =SQRT(C8) **If you put an apostrophe in front of the equal marks in a formula, it will force the formula to appear on the spreadsheet rather than the calculation of the formula. 24