Login
Copyright

How to Use Formulas & Functions in Excel

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Cell References in Excel: Relative, Absolute & Mixed

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 Formulas
  • 0:46 Benefits
  • 2:12 The Basics of Formulas
  • 4:55 More on Functions
  • 6:06 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Login or Sign up

Timeline
Autoplay
Autoplay
Create an account to start this course today
Try it free for 5 days!
Create An Account

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

When you need Excel to do the math for you and run the calculations for totals and subtotals, formulas are your go-to feature. This lesson will introduce you to formulas, how they work and the different elements that make up a formula in Excel.

Formulas

When you think of MS Excel, a spreadsheet of numbers and calculations usually comes to mind. A very common feature of the program is to add numbers together, creating totals and subtotals. But how does Excel do that? If you want Excel to do the math for you, adding let's say a list of ten numbers together and display the total at the bottom of the list, what do you do?

Formulas are the answer! A formula is a set of instructions you write that tells Excel what and how to calculate numerical data. This lesson will introduce you to formulas. We will briefly take a look at how they work and the different elements to a formula in Excel.

Benefits

Formulas have several benefits beyond performing calculations. Probably one that sits at the top of the list is the fact that a formula saves you time. Imagine you have a complicated worksheet, displaying revenue numbers for your company. There are totals and subtotals for several different lists of numbers. Then imagine you entered the totals, performing the calculations manually with your trusty calculator. The worksheet is complete and ready to share at the next quarterly business review. But wait! You just received an email that changes many of your numbers, which means your calculations must all be re-calculated. Ugh, really? Yes, really.

When you use formulas to perform your calculations and do the math, each time you change a number, the formula updates the calculated result, such as your totals. No re-calculating, no additional math. Excel does all the work and keeps the totals up to date.

I am sure I could go on and on about benefits to using formulas, but let me share just one more. It's about accuracy! Formulas (assuming you wrote them correctly) increase the accuracy of your data and reports. Excel can't transpose the numbers or fat-finger the 10-key pad like human input can and does so often.

The Basics of Formulas

A formula is written using a mix of functions, operators, cell references and characters. Let's take a look at these four elements. We will use an example formula, =SUM(A1:A10).

And we'll start with functions. In our example, the function is SUM. This is likely the most common function, but you will find COUNT and AVERAGE popular functions as well. The function is the part of the formula that tells Excel how to perform the calculation. For instance, if you wanted Excel to add a list of numbers together and give you a total, you would want Excel to SUM the numbers together.

Next are operators. The plus (+), minus (-), divide (/) and multiplication (*) are common operators. In our example, the equals sign (=) is also considered an operator. In this case, it signifies the start of a formula. The equals sign tells Excel to pay attention, because a set of instructions to calculate some numbers are next.

Alright, moving on to cell references. Let's take a look at our example formula, =SUM(A1:A10). Now the cell reference is A1:A10, which tells Excel what numbers to SUM or total. In other words, where are the numbers that you want Excel to add? In our formula, the numbers are located in cell A1, A2, A3 and so on, through A10.

And then finally, characters. All formulas need structure. Operators are a bit like using the correct grammar when writing a paper. Grammar helps the reader know when to pause (like commas) or stop (periods). Without the right grammar, you would end up with one, very long, run-on sentence. Formulas need the same structure and characters, such as parenthesis. They chunk things together, telling Excel when to start and stop doing the math. Make sense?

You may hear the term 'syntax' when referring to a formula. The syntax is the language or rules of how the formula is structured. For instance, which element goes first, second, third and so forth.

To unlock this lesson you must be a Study.com Member.
Create your account

Register for a free trial

Are you a student or a teacher?
I am a teacher
What is your educational goal?
 Back

Unlock Your Education

See for yourself why 10 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back

Earning College Credit

Did you know… We have over 95 college courses that prepare you to earn credit by exam that is accepted by over 2,000 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it free for 5 days!
Create An Account
Support