Calculations

Mathematical symbols

Math symbols on computers are a little different. The addition and subtraction symbols look pretty much the same, + and -. The symbol for multiplication looks like *, which is an asterisk. The symbol for division looks like /, which is a slash. For example, if I wrote four times two equals eight, it would look like, 4*2=8. If I wrote eight divided by four equals two, it would look like, 8/4=2.

AutoSum
To add values (value is another name for numbers) in a column, you should use the AutoSum toolbar button. To total values in a column, highlight the cells you want to add up and then press the AutoSum toolbar button. A number will appear beneath the last cell in the column. This number is the total. Since the totaled value is placed underneath the last cell make sure you have a blank cell before you press the AutoSum button. You can also click on a blank cell beneath the last cell in the column and choose the AutoSum button. When you do, you will see "dancing ants" around the cells Excel thinks you want to add together. If the cells are correct, then hit the ENTER key and the value is placed in the cell. If the cells are not correct, you may want to select the cells yourself and then hit the AutoSum button. You can also select a blank cell to add up all the cells to the left of the cell, thereby totaling a row.

Copying a calculation using the fill handle

Previously, we have copied information from one cell to another by using the fill handle. You can also copy mathematical formulas by clicking and dragging on the fill handle or by cut, copy, and pasting a cell with a formula. For example, if you have two adjacent columns (adjacent means two things right next to each other or touching) you are interested in totaling, use the AutoSum button to add the first column. You can then click and drag on the fill handle of the cell that contains the total of the first column to the blank cell of the second column. In the example below, you would click on the fill handle on cell C6 and drag to the left so cell D6 is highlighted and release your mouse button. As you will see, Excel automatically adjusts the mathematical calculation to add the column directly above it. You can view the mathematical formula by clicking on the cell and clicking in at the formula bar.

The formula in cell C6 in the example below is C3+C4+C5. Using formulas is helpful because if the values change in those cells, C6 will automatically recalculate a new total as the numbers in those cells change.
    

When you copy a formula to another cell, Excel adjusts the formula by naming the cells in the formula according to their location to the cell with the formula in it. For example, the formula in C6 is C3+C4+C5. However, to Excel C3 is really thought of as "the third cell above C6." C4 is thought of as "the second cell aboveC6." C5 is thought of as "the cell directly aboveC6." Therefore, to Excel the formula in C6 is really, "the third cell above C6+the second cell above C6+the cell above C6." So when the formula is pasted to D6, the formula becomes D3+D4+D5 because the third cell above D6 is D3, the second cell above D6 is D4, and the first cell above D6 is D5. If the formula was pasted into cell F10, the formula would automatically be F7+F8+F9 because F7 is the third cell above F10 and F8 is the second cell above F10 and F9 is the cell above F10.

Manually entering in a formula

If we wanted to create the same formula in C6 without using the AutoSum button, we could do it a couple of different ways. One way would be to click in cell C6 and hit the equal sign (=) on our keyboard. By doing this, we are telling Excel that we are about to type in a formula. After we hit the equal sign, we can then click on cell C3. When we do, we will see C3’s name appear in our cell and in the formula bar. Next, we hit the + sign on our keyboard. Next, we click on cell C4, hit the plus sign, click on C5, and now that we are done typing in the formula we can hit the ENTER key and the sum is shown in C6.

We could have also clicked on C6 and then clicked in the formula bar. When we do, we see a flashing straight line, which is called a cursor. The cursor is important because it tells us two things. That the computer is ready for us to start using our keyboard to type and the cursor shows us where the numbers or letters will appear on our screen once they are typed in. Once our cursor is inside the formula bar, we hit the = sign on our keyboard. We can then type in the cell name C3 or click on C3 (so the cell name is typed in for us). Then we repeat the same steps as above and hit the plus sign, then we select or type C4, hit the plus sign, and then type or select C5. Now, we can either hit the ENTER key to tell Excel we are done with the formula or hit the green check mark next to the formula bar.

If we do not like our formula and want to delete it. We can hit on the red X in the formula bar, select the cell with the formula in it and hit the DELETE key on the keyboard, or select the cell and choose clear or delete from the Edit menu.

Status Bar

If you want to quickly calculate the average, sum, min, max, or number of values, then select the cells you are interested in. Remember if you want to select cells that are not adjacent to each other hold the CTRL (control) key on the keyboard while you select the other cells. Then right-click (press the right button on the mouse) on top of the Status Bar and choose what you want to calculate. You can do that by putting your mouse on top of the function you are interested in and clicking. The result is displayed in the status bar.

Edit Formula

To use even more mathematical functions, click in the cell you want to put a formula into and then press the equal sign on your keyboard or click on the equal sign next to the formula bar. Notice, the name box turns into a functions drop down list (meaning a list will appear if you click on the tiny triangle to the right). To select one of the functions place your mouse on top of it and click. A box will appear on your screen with a cell range that Excel thinks you are interested in calculating. Also notice that the calculated answer is also displayed in the box along with a description of what the mathematical function means and unless you are mathematically advanced most of the functions will not mean much to you.

Note: this section is for more advanced users: If the function you want is not listed, you can click on the more functions option to select a preset mathematical formula. If the cell range is not what you are interested in then click on the button circled in the picture to the left and the formula window will be collapsed. Once that has happened, you can then click and drag on the cell range you want. Then, click on that same button again to reopen the formula window and click OK. Once you do, you will see the formula typed into the formula bar and the result typed into the cell. This is complicated stuff and most of us will not be responsible for creating complex formulas, but you should be familiar with basic adding, multiplying, dividing, and subtracting formulas.

Order of operations

Sometimes when we write mathematical formulas, we need to tell Excel, which order we want our formula to be calculated in. For example, if we type the formula 3*2+5, Excel will perform the calculation from left to right, giving us 11 as the answer. If we want Excel to multiply 3 times the sum of 2+5 we need to put parenthesis in the formula so Excel knows which steps to do first. Excel always does the calculations in the parenthesis first and then calculates from left to right. We would type the formula as 3*(2+5). Excel would then know to add 2+5 to give us 7. Our formula would then be 3*7. The answer then would be 21.

Combining words from different cells into one cell

Lastly, not only can we use formulas to add numbers together, but we can also use formulas to combine words together. For example, if we had column A with people’s last names and then column B with a person’s first names. We could create another cell that would add B1+A1 together, giving us a cell that listed the first and last name together. Type =A1 & "_" & B1 in the formula bar of a blank cell.

Charting

Charting Wizard

If you have a table that you wish to convert to a chart, then select the table, and click on the chart wizard toolbar button. When you do, a window will pop up on your screen. On the left-hand side of the window, you can choose to make a pie chart, line chart, and more. To select the type of chart you want. Simply click on one of the options in the list. If you choose on pie chart, then on the right hand side, you can choose more specific options of what type of pie chart you want. Similarly, you can click on the option you want to choose. Notice: there is also a press and hold to view sample option. If you click and hold on the button a preview of what your table will look like with the options you chose will appear. When you find what you like, click on the Next button.

In step 2, you can change the cell range for the chart and can also change whether the information you are interested in charting is listed row by row or column by column, otherwise known as series. If you click on the word series at the top, you can choose even more specific options regarding the series. Let’s say for example, you do not want to include one of the records in your table. To remove it, click on the name of that record in the series list and then hit the remove button. Do not worry if it does not make sense to you, when you click on all the different options, the sample of your chart changes accordingly and you can see how the option changes your chart. After seeing what each option does, next time you will know what all the options are for.

Step 3 allows you to change the chart title, axes, gridlines, labels and more. It would be silly to talk about each of the options here. They are all pretty self-explanatory. If you are not sure, click on the option and see how your chart changes. What you do need to remember is, for options that require you to type in information, you need to first click your mouse inside the area (usually a white box next to the option’s name) and once you see your cursor, then you can start typing. Hit the TAB key to remove the cursor from the box and to see the changes in the chart.

The fourth option lets you decide where you want the chart to be placed. You can have it placed on a separate worksheet (therefore when you print the chart, it will be on one page) or you can place the chart inside any worksheet. Once in a worksheet you can resize or move the chart. If during any step you decide to go back and change your options, hit the Back button and you will move back to the previous step.

Selecting objects in a chart

There are two ways to select a chart. One, you can use the drop down list in the chart toolbar. If your chart toolbar is not showing, click on toolbars in the View menu and then click on chart. Another way to select objects in a chart is to simply click on the object your interested in selecting. When you do, boxes appear around the selected object and the name of the selected area appears in the chart drop-down list.

Customizing a Chart

After you select on an object in the chart, you may change its color, shape, style, and many other options. To change the object, either double click on the object, select the object and choose format from the chart toolbar, or select the object and choose format from the menu bar. When you do you will find many options to customize your chart. The best way to learn about all the different options is to click on each of them and see what happens.

Resizing and moving a chart

To resize a chart, first select the chart. To select the chart or choose plot area from the drop down list in the chart toolbar. When you do, you should see a box appear around your chart with eight tiny squares on the box. There are four squares at each corner of the box and another four squares on each side of the box. If you click and drag on any of the four squares on the sides of the box, the height or the width of the chart will decrease or increase, depending on which square you clicked on, and which direction you dragged the mouse. If you click and drag on any of the four squares at the corners of the box you will decrease or enlarge both the height and width of the chart proportionally. Before you click on the tiny squares make sure your mouse pointer has turned into a black double-sided arrow. The mouse pointer changes to this only when you place your mouse on top of the tiny squares. Only when your mouse pointer changes should you then click and drag.

You can also, easily change the dimensions of the chart by selecting or clicking on the walls of the chart and clicking and dragging on the squares. Note your mouse should change to a plus sign before you click and drag on the squares. To move the chart click and drag on the box that appears around the chart when it is selected.

Printing a chart on a whole page

There are two ways to print a chart so that it appears on a whole page. One, place the chart in a separate worksheet. You can do that by choosing that option in step four of the chart wizard or right clicking the chart and choose the location option on the pop up menu, which opens up step four of the charting wizard. Two, you can click on the chart and choose print preview from the File menu. Once in print preview you can then hit the print option.

Back to Excel Syllabus