5 basic Excel Functions every beginner should learn

Microsoft Excel is a fantastic program allowing you to collate, analyse and make sense of data quickly. If you’re just starting on your Excel journey, then it is worth learning a few basic Excel functions to get you started. Don’t grab a calculator, let Excel do the work for you.

Before using any Excel functions, it is helpful to understand a few basics. You should understand the difference between the terms function and formula, how BIDMAS works in calculations, how to use the formula bar, and recommendations for typing a formula or function. I recommend you go through my post on Formula basics in Excel before attempting the following exercises.

In this post, I would like to introduce you to five (5) of the most popular Excel functions. These include AutoSum/SUM, AVERAGE, COUNT, MIN and MAX.

What are Excel functions?

Excel functions are built-in formulas that calculate values in your worksheet. You can use Excel functions to add, subtract, multiply, and divide, plus calculate the average, minimum and maximum values. There are over 450 Excel functions to choose from and in reality you will probably only need to know a dozen or so depending on your job role and needs.

A common issue when creating Excel functions

Many new users run into problems when they type numbers directly into a formula instead of storing values in cells. While typing numbers into the calculation might seem faster, it prevents Excel from updating the result when the data changes.

Here is an example of good versus bad ways to enter data:

In the example above you can see the way in which the total sales for January and February have been calculated.

In the calculation for January, this shows the correct way to create a calculation. The value for each consultant is displayed in individual cells and the formula created is referencing the cell names (E.g. B4) and then adds each cell value together. E.g. =B4+B5+B6. You can easily adjust any of the values as they are visible in their indivdual cells and the value displayed in the total will automatically update.

In the calculation for February, this shows that the values for each consultant have been displayed in individual cells (which is great) however for the total to be calculated the user has manually typed the actual values directly into the calculation E.g. =61+66+38. If any of the values change, we can change the individual cells manually however the calculation would not automatically update to reflect the difference.

AutoSum or SUM function

The SUM function is one of the most commonly used Excel functions. AutoSum will automatically locate the closest adjacent cells containing numbers. AutoSum then inserts the SUM function and cell references to allow you to add groups of numbers together quickly.

To use AutoSum:

1. Open Microsoft Excel.
2. Create a new blank workbook and type the following content into the worksheet. You do not need to worry about formatting the content if you do not want to:

3. We will now use the AutoSum feature to calculate the total for each person’s sales figures.
4. Place your cursor in cell F4.
5. You will now use AutoSum to automatically total the data from B4, C4, D4 and E4.
6. From the Home tab select the AutoSum button from the Editing group (located on the right side of the Ribbon), alternatively you can use the Alt + = keyboard shortcut combination.

7. You can either click directly on the AutoSum button or click the drop-down arrow to the right of the button to view all options and click Sum.
8. You will notice that Excel has automatically inserted the SUM function, including cell references, into cell F4:

9. Read through the Excel function:

=SUM(B4:E4)

The equals sign tells Excel to perform a calculation.

The word SUM identifies which Excel function is being used (e.g. Sum the values together).

The cell reference B4:E4 tells Excel which cell range to add together.

10. If you are happy that the function has the correct range of cells selected, press Enter once.
11. The total will now be displayed.
12. Repeat this for the remaining totals or if you are familiar with how to use AutoFill, then you can use this method.
13. As a little exercise, place your cursor in cell B6 and change the value to 180. When you press Enter on the keyboard you will see the total at the end of the row is updated to use the new value.
14. Now repeat the process and change the value back to 57.

Understand the Formula bar

The formula bar in Excel allows you to view or edit the contents of the active cell. The formula bar can be used to manually enter a formula into a cell, edit an existing formula or function and view a formula or function.

It is important to remember that the values you see displayed in a cell can be information that has been manually typed or can be the result of a formula or function which is active within a cell. If you want to see where the data is coming from, select the cell and check the formula bar.

1. Place your cursor in cell F4.
2. Look at the formula bar to view the formula contained within the cell:

AVERAGE function

The AVERAGE function allows you to calculate the average of the values set within a range of cells. The values can be numbers and names or references that contain numbers.

To use the AVERAGE function:

1. Place your cursor in cell E8.
2. Type the heading AVERAGE RESULT.

Note: You may need to adjust the width of the columns to ensure the text fits into the column nicely.

3. Place your cursor in cell F8.
4. From the Home tab select the drop down button adjacent to the AutoSum button and then select the Average function:

5. Microsoft Excel will now try to identify which range of cells you wish to calculate the average for, we want to calculate the average total for the sales consultants, you will, therefore, need to ensure that cells F4:F7 are selected:

6. Click the Enter key to complete the formula.
7. The average of the values will now be displayed.

COUNT Function

The COUNT function allows you to count the number of cells that contain numbers or numbers within a list of arguments. The COUNT function often gets people a little stuck because users assume that the function can count any type of data.

Let’s say you have a spreadsheet with all your customers listed and you want to know how many customers you have, a user will use the COUNT function and try to have Excel count the customers’ names, they then receive an answer of zero (0) and are not sure why.

The COUNT function only works with number based values such as numbers, currency and ates. You cannot use this function to count text based content. The best way around this is to use a column of data such as a customer’s phone number or postcode and use the count function to count this data instead.

To use the COUNT function:

1. Place your cursor in cell E10.
2. Type the heading COUNT RESULT.
3. Place your cursor in cell F10.
4. From the Home tab select the drop down button adjacent to the AutoSum button.
5. Select the Count Numbers function from the list.
6. You will see that Excel tries to select the two cells directly above the current cell:

7. We want to count how many consultants we have so use the left mouse button to highlight cells F4 to F6:

7. Now press the Enter key on the keyboard.
8. Microsoft Excel will now count the number of numeric values within the specified cell range and will display the answer of 3.

MIN Function

The MIN function enables users to find the minimum value within a range of values. You do not need to sort the data to see the lowest value, Excel can find it for you. If any values in your worksheet change, which would affect the answer, the function will update automatically to give you the most up to date answer.

The MIN function is useful for: determining the lowest cost of an item; the lowest quantity; lowest percentage or dollar amount.

To use the MIN function:

1. Place your cursor in cell E12.
2. Type the heading MINIMUM RESULT.
3. Place your cursor in cell F12.
4. Repeat the process used previously and locate the MIN function within the AutoSum drop-down.
5. Again Excel will choose the cells directly above the current cell.
6. Use the mouse to highlight the correct cells in F4:F6 and press Enter:

7. Excel will now display the smallest value from the cell range specified which is 206.

MAX Function

The MAX function is used when trying to determine the maximum or highest value from a range of cells or values.

To use the MAXfunction:

1. Place your cursor in cell E14.
2. Type the heading MAXIMUM RESULT.
3. Place your cursor in cell F14.
4. Repeat the process used previously and select the MAX function from the AutoSum drop-down menu.
5. Again Excel will choose the cells directly above the current cell.
6. Use the mouse to highlight the correct cells in F4:F6 and press Enter.
7. Microsoft Excel will now display the maximum value from the cell range specified being 224.

These five Excel functions are the perfect starting point for anyone learning to use Microsoft Excel. Once you are confident using SUM, AVERAGE, COUNT, MIN and MAX, you will have a strong foundation for working with data.

For more posts relating to formulas and functions be sure to take a look at:

• Formula Basics in Excel
• Create a basic Lookup function in Excel
• Use Concatenate to join text together in Excel