Excel Formulas and Functions

Learn Basic Excel Formulas and Functions in 15 min

Excel Formulas and Functions

Formulas and functions are very popular topics in Microsoft Excel assessment test. They’re also used very frequently in Excel interview. If you look at the types of questions being asked, a lot of them are related to fundamentals of Excel formulas and functions. In this tutorial, I am going to introduce you to excel formulas and excel functions, and we'll do some basic operations with both learning about key functions that are used as part of excel interview in this part of Excel assessment test. Based on the job candidate applies for, there are typically three levels of Excel assessment.

There are:

  • Basic Excel Assessments
  • Intermediate Excel assessments
  • advanced assessment

In this tutorial, we're going to focus on the basic level assessment questions which is typically a screening process for entry level or even experienced candidates which includes administrative assistants, retail store managers, sales representative, marketing representative, accountants, entry level people and a lot of other categories of job interviews which require Excel skills. We'll look at some questions asked as part of Excel interview or Excel assessment test. And I'll show you a couple next steps how you can learn more.

One of the fastest ways to find information about excel topics is to use Microsoft Excel built-in help by just pressing f1 button on the keyboard and then search for the topics you're looking for. A lot of articles in Microsoft help even have built-in videos which help you learn the fastest way possible.

Excel Formula

Excel formula is used to perform basic calculations.

Let's perform simple Excel calculation:

Excel Formula

  • Put cursor into the Cell E4
  • Type equal sign
  • Select 2
  • Add +
  • Select 54
  • Hit Enter

The result is 56.

You can use Excel formula to perform basic mathematical functions, for example, addition, subtraction, division, multiplication and a lot more.

Let's perform Multiplication using Excel formula:

Multiplication using Excel formula

  • Put cursor into the Cell E5
  • Type equal sign
  • Select 2
  • Add *
  • Select 3
  • Hit Enter

The result of this mathematical operation is 6.

Excel Functions

Function is a little bit different from formulas but they have a lot of similarity. You still need to type equal sign then the name of the function. Typically, you have mandatory arguments and it could be either cell reference, could be value, could be arranged and then you have list of optional values.

SUM Function

SUM functions, you can trigger it in multiple ways. Let's start with the simple and fundamental ways to use SUM function.

Excel Functions

  • Type Equal sign
  • Type SUM
  • Open parenthesis
  • Select 2
  • Add Plus sign
  • Select 3
  • Close Parenthesis
  • Hit Enter

It shows me the value 5 which is exact result of summing 2 & 3. That’s one way to use SUM function.

Another way is;

Excel Functions

  • Type Equal sign
  • Type SUM
  • Open Parenthesis
  • Hold Ctrl button and select values B4, B6, B8
  • Close the parenthesis
  • Hit Enter and

Now we have the sum of three values and the end result of this is 17.

Another way is:

Excel Functions sum

  • Type Equal sign
  • Open The Parenthesis
  • Select by dragging the cursor to select the range of values B4 through B10
  • Close the Parenthesis
  • Hit Enter

Now, SUM of all values in the range B4 through B10 is 166.

So summing up values manually and typing everything manually is very cool but as you well aware, excel is all about productivity. Excel has couple of shortcuts, tips and tricks that it creators implemented in the tool. Because SUM is so popular, they created the whole autosum function on the Home tab.

Excel Functions

  • Select the Cell E6
  • Click Autosum
  • Highlight the range B4 through B10
  • Hit Enter button

Excel insert Functions

Another way as you probably well aware there is a formulas tab in Excel which specifically designed to work with functions. In the insert function button, you can research functions. A lot of times you don't know which function you would like to use. For example, if you type SUM and click go, it shows SUM and all related functions like SUMIF, SUMIFS and some other ones which you probably will be able to research, it shows the syntax and shows the descriptions. And you can also click Help on this function and if you do that, it will bring you to Microsoft's Office website where you can a lot of times, watch video, read a lot about the function. So basically, it's the way for you to learn more about particular function.

MIN Function

MIN Function

  • Type Equal sign
  • Type MIN
  • Open the Parenthesis
  • Select the range B4 through B10
  • Close the Parenthesis
  • Hit Enter

The minimum value is 2. That's the lowest value in this range.

MAX Function

MAX Function

  • Type Equal sign
  • Type MAX
  • Open the Parenthesis
  • Select the range B4 through B10
  • Close the Parenthesis
  • Hit Enter

We have 85 which is the largest value in this range.

COUNT Function

Count basically counts number of values in the range.

COUNT Function

  • Type Equal sign
  • Type COUNT
  • Open the Parenthesis
  • Select the range B4 through B10
  • Close the Parenthesis
  • Hit Enter

AVERAGE Function

AVERAGE Function

  • Type Equal sign
  • Type AVERAGE
  • Open the Parenthesis
  • Select the range B4 through B10
  • Close the Parenthesis
  • Hit Enter

3 Excel Interview Questions

Excel Interview Questions

  1. SUM is used to calculate total number of values in the range?

So it's a tricky question. They use the word total which is part of the description of the SUM function because it's a total number of values. The answer is false because SUM functions is used to calculate sum of values not number of values in the range.

  1. When using Excel functions you have to provide optional arguments?

The answer is false again. As you are well aware that we have two types of arguments; we have mandatory arguments which typically presented in the first part of the function and then we have optional arguments that are typically presented after. So you do not have to supply optional arguments but you have to supply mandatory arguments. So that's why the answer is false.

  1. SUM function supports mathematical operations, cell references and ranges as arguments?

The answer here is true. If you go back in this tutorial, you will see that you can type SUM and then cell reference B4+B6 or you can type SUM B4,B6 or you can type SUM B4:B6. That would represent all of these three conditions; mathematical operations, cell references and cell ranges as arguments.

 

Watch Excel Formulas and Functions Video Tutorial