1 Creating Formulas and Charts in Excel - Basic Using Excel When you open up Excel, this is the first window you will see. This will open up a new Excel workbook and a spreadsheet will appear. You will notice that columns are labeled alphabetically and rows are labeled numerically. Choose Excel Workbook and then click “OK” 2 An active cell has a light blue border around it. Click in the cell you want to make active. Entering Values In excel, a value can be any text, number, date, or time you enter in a cell. Activate the cell in which you want to enter a value. As you type the data will appear in two places: the active cell and the formula bar. If the value you enter does not fit into the cell, it will overflow into the cell in the next column. In order to fit the value, place the cursor between the first two columns the value occupies. A double arrow with a line in the middle will appear. Double-Click. Excel will auto fit the column to the width of the cell. Formula Bar 3 Cell References In Excel, formulas use cell references to refer to other cells. If you change the content of the cells, then the result of the formula also change. The cell reference is found in the *Formula Bar or you put the cursor over the cell, Excel tells you the cell reference. *If you do not see the formula bar on your screen, then click View and select Formula Bar. Entering Formulas by Typing Using Cell References Type the values in the cell. Click on an empty cell you want to place the formula in. Once you click the active cell, it will have a blue border around it. Type a formula in the Formula Bar OR in the active cell. The cell reference for 21 is D3. 4 To write a formula, first, type an equal (=) sign. All formulas need to begin with an equal sign. Next, enter a cell reference, constant or operator (+ ,-. *, /,^) until finish entering the formula in the bar. Complete the formula by pressing return or the green check mark icon. You should now see the result of the formula in the cell. OR Click on the cell you want to enter a formula. Type an equal sign. Click on the cells you want to reference. The cell will have a gray fuzzy boarder around it. If you click on the cell references without operations, Excel assumes you want to add the references to the formula. When you have finished entering the formula, press return or click on the green check mark icon. Tip: Using arrow will change the cell reference. For example, if I click on C2 and then use my arrow key to move to C3, then the formula will read =B2+C3 instead of =B2+C2. Entering Functions Functions are predefined formulas Excel uses to make calculations. A common function used in Excel is the SUM Function. The SUM Function allows you to add a range of numbers. Click on the cell you want to type in the formula. Type an equal sign. Go to the Insert menu and select Function. The Paste Function dialogue box will appear. Select the function you want to use. Click “OK”. 5 After you click “OK”, another dialogue box will appear. In the dialogue box, Number 1 references the range of cells to be summated. Excel automatically selects the range of numbers that it thinks is most logical to summate. Check the cell references, edit if necessary. Click “OK” The result of the formula should be displayed in the active cell. . OR Cell references of numbers to summate. Box A 6 You can also use a button on the toolbar to create a formula. First, like the above method, click on the cell in which you want to use the formula. In the cell, enter an equal sign (=). Once you do this, the upper-left hand box next to the tool bar will turn into a function box. You will be able to decide what function you want to use by clicking on the downward- pointed arrow on the right of the box. Once you have decided your function, Box A (above) will appear. You may follow these directions to complete your function. OR Once you click on this arrow, a list of functions will appear. You simply need to highlight the function you want to use. In this example, the IF function is highlighted. 7 Another way to enter a formula is by using a different icon in the toolbar. First, click on the cell in which you want to place your formula. Next, click on an icon in the middle of the tool bar. Once you click on the function, the computer will automatically choose a range for your function. This will show up in the active cell, and a dotted line will surround the cells that are being calculated. Once you press enter, the value will appear. In this case, 148. Creating Charts If you click on the downward-pointed arrow on the right, it will give you options as to what you want your formula to be. Highlight the desired function. If for some reason the computer has not chosen the values that you want calculated, you can adjust the values by either changing them in the formula bar, or the active cell, or by adjusting what is inside the dotted-line box. For example, on the left, the dotted line box includes B2(4), B3(87), B4(3), and B5(54). I did this by first clicking on B2(4), then holding and dragging down to B5(54). Once I let go of the mouse button, the dotted line box comes out like on the left. 8 First, on your table, highlight what you want to be included in your chart. Above, the light blue line encompasses what you have highlighted. One way to easily highlight everything, is to click on the diamond icon on the upper left hand side. Another way to highlight just certain values is to click on cell A1, then hold and drag to where you want. In the above example, I clicked first on A1, then held and dragged to E6. After you have highlighted your values, click on the chart wizard icon. This is icon is in the middle of the top row of icons. 9 Once you click on this icon, a table will appear. On this table, you can choose what type of chart you want to make. There are many options, as there are even “chart sub-types” within each chart type. Some chart types are more appropriate than others, depending on the type of values you have selected. Once you have chosen, click on “Next>”. I have chosen the “Line” chart type, with the specific subtype highlighted in blue. 10 In this next table, you have a few options as to how your chart may look. First, the “Data range” box reiterates what you want to show in your graph. Generally, if you have correctly highlighted your values, the values will be fine. Next, the “Series in” option, between Rows and Columns, gives you the option of viewing the values based on how you arranged your information in your table. For example,