1. Arithmetic operators-used to perform basic mathematical operations such as adding, subtracting, multiplying, and dividing.

       

2. Comparison operators-used to compare values.

       

3. Text operators-used to join two distinct text strings into a single string. For instance, "global" & "warming."

       

4. Reference operators-used to combine ranges of cells for calculations.

The table below contains a list of recognized operators.

Operator	Meaning
Arithmetic operators
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Percent
^	Exponentiation
Comparison operators
=	Equal to
>	Greater than
<	Less than
> =	Greater than or equal to
< =	Less than or equal to
< >	Not equal to
Text operator
&	Connects two values to produce one continuous text value
Reference operators
:	Range operator
,	Union operator, combines multiple references into one reference.
(space)	Intersection operator

 

Creating a Formula

As we said earlier, a formula must start with an equals sign: =. This might seem strange at first since ordinarily an equals sign comes at the end of an equation, but this lets Excel know right away that you want to perform a calculation.

Now let's say we wanted to create a weekly expense report. We stopped for gas twice on Tuesday and have two receipts, one for $29.92 and the other for $32.51. We'd click on the correct cell (in this case C4) and type =29.92+32.51.

The equation appears in the formula bar as well as in the individual cell as you type it. Hit Enter and the equation disappears, leaving only the sum of the two numbers: . If you want to see the formula again, click on the cell. The formula will be visible in the formula bar. To see the equation inside the cell, double-click on it.

You can also perform a calculation based on the values within a series of cells. Let's add up the figures in column D. The individual cell coordinates are D4, D5, and D6. We want the total to appear in cell D7, in the row called Daily Total. Select that cell and verify your selection by checking the Name Box.

Now we'll type =SUM(D4:D6). The equals sign tells MS Excel that you are going to enter a formula. The SUM tells it to add the numbers in a range of cells and the colon tells it the range (D4 to D6. )

You'll notice that as you enter the range, the text changes color (in this case blue) and the cells it represents become outlined in that color as well. This gives you a visual idea of the selected range.

To quickly change the selected range, you can position your mouse pointer over the blue border around cells D4 through D6, then click and hold the left mouse button to drag it over the cells to the left or right. Doing this would automatically change the data range in our formula. You can also position your mouse over one of the blue boxes in the corners to expand or restrict your data range.

When you are finished writing your formula, hit enter to perform the calculation.

Because the sum depends upon the numbers in cells D4 thru D6, MS Excel will automatically recalculate the sum whenever the values of those cells change. In the example on the right, we have the original value. In the example on the left, we've changed the entry in cell D6 to 25. Note the different totals at the bottom of the column.

               

The Role of Functions

By now you've probably noticed the Insert Function button next to the formula bar:

Functions are predefined formulas that perform calculations based upon specific values, called arguments. They can perform simple tasks, such as rounding off a number, or complex tasks like returning the inverse hyperbolic cosine of a number. (If your palms just got sweaty, don't worry, inverse hyperbolic cosines will not be mentioned again in this course.)

Let's look at a simple task. The following function will round the figure in cell G11 up two digits:

=ROUNDUP(G11,2)

A function, like a formula, always begins with an equals sign: =. This is followed by the function name. In this case, it's ROUNDUP. Next is the argument (G11,2). The argument is always enclosed in parenthesis. An argument gives the formula specific parameters. For instance, in our example, we're telling Excel we want to round up the value in cell G11 by two digits.

An argument can be a number, text, a logical value, an array or a cell reference.

When you enter a function, you'll see the tool tip box appear under it. If you need an explanation for any part of a function, just click on any word in the tool tip.

Note: Tool tips are only available for built-in functions.

In the above example, we clicked the word "ROUNDUP" in the tool tip.

Creating Functions

There are two ways to enter a function. If you know the function name and the syntax, you can select a cell and start typing. If you're not sure of its name, select a cell then click the function button .

This is what you will see:

If you have a pretty good idea of what you want to do, select the "Search for a function" box and start typing. In our case, we want to round a number up. So we'll type "Round a number up" and click Go.

Excel searches the description for each function and comes up with a list of close matches. You can see what each function does, by selecting it. In the above example, we've selected ROUNDUP. The function and its syntax appears below the "Select function" box, as well as its description. Click OK.

The Function Arguments dialog will open. Type the number or reference you want to round up in the number box. Since we want to round up the value of cell G11, we'll enter that into the Number box. If you're not sure what value to enter, just click the box. An explanation will appear below. In this example, the Num_digits box is selected. We can see its explanation in the lower middle of the box:

We want to round the number up two digits, so we enter that figure into the Num_digits box and click OK. The formula result appears in the selected cell in our worksheet.

If you know what kind of calculation you want Excel to perform, but you're not quite sure how to articulate it, you can browse through the functions in the Insert Function dialog box. Just scroll through the list and select the one that seems the most appropriate. You can narrow the search results by choosing a category in the "Or select a category" box.

Another way to search for and insert a function, is by navigating to the Formula tab. Use each of the buttons in the Function Library group to easily browse through all of the available function.

The Insert Function button launches the Insert Function window, which we've already talked about.

References

Now let's take a closer look at references. A reference simply tells MS Excel where to find the information you want to use in a formula. As you've already learned, by default MS Excel uses the A1 reference style, or coordinate system, to identify cells. You can use these coordinates for references (as in the example above) or you can use labels and names.

Using labels and names makes it easier to understand the information you are entering into the formula. For instance, the formula "=SUM(WednesdayTotal)" is easier to understand than "=SUM(D4:D6)

Using Labels

As you learned in lesson 3, a label is used to identify a range of cells. For example, the word "Thursday" in cell E4 can be used to represent all the cells in column E. Likewise, the word "Gas" can be used to represent all of the cells in row 4.

A drawback to using labels, is that they can only be used to represent a range on the same worksheet. If you want to represent a range across multiple worksheets or even in another workbook, you must use a name.

Note: When a name for a formula or range is used in another workbook, it is called a "link."

Using Names

Unlike labels, names can represent more than just a range of cells. They can represent an unchanging number (called a constant) or even a formula. For instance, let's say sales tax is 8 percent. Since this number will not change between worksheets or workbooks, we can give it a name. In this case, we'll call it SalesTax.

First, we'll want to define our name. We'll do this by navigating to the Formulas Tab and clicking the Define Name button. This will launch the Define Name dialog box.

In the Define Name dialog box, we'll type Sales_Tax in the name window and .08 to represent 8 percent in the Refers to box. Then click OK.

We now have a reference for sales tax, one that we can use in a formula. In the example below, we'll apply sales tax to the sub total (which we've already given a name: Sub_Total).

Since we've already attributed a name to cell G9, we will use it in our formula: "=(Sub_Total*Sales_Tax)". As you can see in the above example, the word "Sub_Total" and the cell it refers to (G9) are highlighted in blue while the word "Sales_Tax" is not. This is to give you a visual reminder of what the name refers to. Hit enter, and the product of 299.78 x .08 will appear in cell G10.

Let's say we wanted to add the sub total to the sales tax for a grand total and use the grand total as reference on worksheet 2. First we'd create a cell for the grand total and enter our formula:

When we push Enter the sum of the Sub Total and Sales Tax will appear in cell G11:

There are two ways to use value on another worksheet. First, we can simply select the appropriate cell on the second worksheet and enter this into our formula:

"=Sheet1!$G$11".

As you've no doubt deduced, "Sheet1" in our formula tells Excel from which worksheet you want to select cell G11. Since each worksheet uses the A1 reference style, if you didn't use this, MS Excel would simply use the information in cell G11 in the current worksheet.

So if we wanted to add the grand total in worksheet1 to the grand total in worksheet2, the formula might look something like this:

Because we're referring to a cell on the current worksheet, that cell and its reference are highlighted.

The dollar sign symbol ( $ ) represents an Absolute Reference, which we'll talk about in the next section.

But first, we'll use names to perform the same calculation. So let's go back and rename the value in cell G11 on worksheet1 "Grand_Total_Week1" and the value in cell G11 on worksheet2 "Grand_Total_Week2".

We can then just enter the names into our formula:

We could also give our formula a name:

In this case, we've decided to call the formula "Biweekly_Total" and told MS Excel that the formula for "Biweekly_Total" is "Grand_Total_Week1+Grand_Total_Week2".

We can now select cell G15 on worksheet2 and type "=Biweekly_Total":

Press Enter and the MS Excel performs the calculations for you:

Name Manager

The Name Manager can help you keep the names you created organized. This is especially helpful if you've created a lot of them. You can find the Name Manager
 

button on the Formulas Tab. From the Name Manager, you can create new names, delete them or edit them. Use the Filter button to sort your names.

Absolute, Relative and Mixed Cell References

There are two different cell references in Excel: absolute and relative. A relative reference in a formula depends upon its position in a worksheet. All Excel worksheets show relative references by default. For example, A1 is a relative cell reference.

Let's learn what it means when we say we have an absolute cell reference for a cell.

In the snapshot below, we've added a formula for cell D1. The formula is the sum of rows A1 through C1.

Now, let's say we want to copy and paste the contents of cell D1 into cell E1. We want 55 to show up in E1 instead.

When we copy then paste that cell, we notice that 55 doesn't show up. 103 does instead.

Why is that?

This is because it's a relative cell reference. The formula will be calculated based on the location. The range of the cells that you're adding together will increase by increments of one cell each time. The formula you entered in D1 originally does not copy and paste over to E1 exactly as you've entered it.

Excel makes adjustments based on the location and increases the range by one. So, when we paste into E1, Excel calculates B1 plus C1 plus D1 to come up with 103. The same thing happens when we try to paste into F1 from E1.

The formula stays the same (adding three cell locations to get a sum), but the range of the cells it adds up within the formula increases in increments of one cell.

Whenever you work with relative cell references, the range of the formula (A1 through C1 is a range) will increase in increments. In our snapshots, the increments increased by one cell each time. In D1, our formula started with A1. In E1, it started with B1. You can use the AutoFill feature to do what we've done in D1, E1, and F1 with the increments.

Now, in an absolute cell reference, when you copy and paste a cell, the range remains the same. This means if you pasted D1 (or 55) into E1, the range would stay the same and you'd see 55 in the cell.

Absolute references use $ signs.

To see what we mean, take a look at the snapshot below. Pay attention to the Formula Bar.

Notice that the rows and columns in the Formula Bar all have $ before them. This is an absolute cell reference. If you copy and paste D1 into E1 (or anywhere else), the range of the formula will remain the same.

Let's try it and copy (then paste) cell D1 into E1.

As you can see, the range stayed the same.

In addition to absolute and relative cell references, you can also have mixed references. Take a look at the list below. Since you already know what absolute and relative cell references are, mixed cell references will be easy to understand.

$A1: Only the column is fixed. It will not change during an AutoFill or copy, but the row will change.

A$1: Only the row reference is fixed (or absolute). It will not change, but the column will.

To make a default relative cell reference an absolute cell reference, select the formula that appears in the Formula Bar and press F4 until you see the $ in front of either the rows, columns, or both.