Use formulas and functions

Create formulas

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Get started on how to create formulas and use built-in functions to perform calculations and solve problems.

Formula bar
  • When a formula is entered into a cell, it also appears in the Formula bar.
    Formula Bar

Create a formula that refers to values in other cells
  1. Select a cell.
    Select a cell

  2. Type the equal sign =.

    Note: Formulas in Excel always begin with the equal sign.

  3. Select a cell or type its address in the selected cell.
    Its address

  4. Enter an operator. For example, – for subtraction.

  5. Select the next cell, or type its address in the selected cell.
    Operator

  6. Press Enter. The result of the calculation appears in the cell with the formula.

To see a formula
  1. Select a cell, and see the formula in the formula bar.
    See formula bar

Enter a formula that contains a built-in function
  1. Select an empty cell.

  2. Type an equal sign = and then type a function. For example, =SUM for getting the total sales.

  3. Type an opening parenthesis (.

  4. Select the range of cells, and then type a closing parenthesis).
    Range of cells

  5. Press Enter to get the result.

Relative references
  • A relative cell reference in a formula, such as B2:G2, is based on the relative position of the cell that contains the formula, such as H2. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill a formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if the formula in H2 is copied to H3, it automatically adjusts from =SUM (B2:G2) to =SUM (B3:G3).

Absolute references
  • An absolute cell reference, like $B$2, in a formula always refers to a cell in a specific location. The formula in H2 with absolute references would be =SUM ($B$2: $G$2). If the position of the cell that contains the formula changes, the absolute reference remains the same. For example, if the formula in H2 with absolute reference is copied to H3, H2 and H3 will have the same formula =SUM ($B$2: $G$2).

Want more?

Discover more courses like this at LinkedIn Learning

Overview of formulas in Excel 2016 for Windows

Switch between relative, absolute, and mixed references

Formulas, many times, are straightforward, simple, easy to use, but they start with a different concept than you might be familiar with.

In cell B4, we want to subtract the two numbers above it.

Now, if that's all you were told and you never worked with Excel, you might be typing something like 120 minus 100.

But in Excel, typically, we write formulas based on location and that implies content.

One way to state this is, we are about to say that cell B4 equals whatever is B2 minus whatever is in B3.

Now, we don't need to type B4, we're already at that cell.

We type =. Formulas in Excel begin with =. Now, there are a few different ways to enter formulas.

The most common way, at least initially, is to type addresses, B2.

And what we're saying, in effect, is whatever is in B2 minus whatever is in B3.

We'll type B3. This opens the door for the possibility that later, if we need to adjust those sales or overhead numbers, our formula will react automatically.

So we complete the entry with Enter or Tab or any of the arrow keys.

That's it, we've got a formula.

Now, get used to the idea that when you click on a cell, keep your eye on the formula bar.

And it really starts to become important as you work with formulas.

Built into Excel are what we call functions. There are over 450 of them.

A capsule description of what a function is, it's a shortcut for a formula.

That doesn't apply in every single case, but that's a general definition of what a function is.

On the Formulas tab in the ribbon, you'll see all kinds of functions listed here in various categories.

Now, the most common function in Excel is SUM for nearly everybody.

I'm going to start typing in cell H2, =SUM. Now, notice what happens when I type the letter S.

There are lots of other functions that begin with the letter S.

Sometimes you see one here and there, and you know what it is.

Other times, you get a little curious. We're not, say, at that point just yet.

And we don't have to necessarily scroll up and down and see these.

And this is so short, we're not gonna take advantage of any way to type this in any faster.

Let's type =SUM(. All functions are followed by an open and close parentheses.

Now, you wouldn't know what to do next necessarily, but with the mouse, let's highlight cells B2 through G2.

And you can either drag leftward or rightward.

Wherever you start, for example, I'm gonna start in G2, I'm pointing in here, hold down the left mouse button, drag leftward into B2.

I said that all functions are followed by open and close paren, but Excel does this for you, if you're only using a function by itself.

So I'll simply press Enter. Now we've got an answer there.

The average here, we simply need to take that value, H2, now, you can type it, or in this case, possibly =, click this cell, /6.

If you are typing, you don't need to capitalize the H. If you click on it, it's automatically capitalized. / is the division symbol.

Now, this is the slash that, on most keyboards, is in the bottom row, usually a few keys to the right of the letter M.

You cannot use the other slash, the one that goes in the opposite direction. =H2/6. So in our examples here, we just used division here, we used a SUM function here, our original formula over here in cell B4 used subtraction.

We haven't used the multiplication symbol, it's an asterisk.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

Benefits
  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×