In this post, you'll discover some starter Excel functions for beginners.
Functions allow you to simplify your formulas and create magic within your spreadsheets. With over 500 functions to choose from and mix together in any combinations you like, let's get started by exploring some basic Excel functions.
1. What is an Excel function?
A function (or a combination of functions) is used within a formula to simplify it and do the things that you need to do.
For example, the formula =A1+B1+C1+D1+E1+F1+G1+H1 could be replaced with =SUM(A1:H1) which offers the advantage of being easier to read and easier to maintain.
SUM is the function. Its sole purpose is to add up a series of numbers.
There are over 500 functions in Excel. Many of these are specialised high-end functions which would only be used by engineers, scientists and mathematicians. However, there are still many Excel functions for beginners to use. You know, ordinary’ folk!
Formulas and functions are the engine of every decent spreadsheet The more functions you can master, the more you’ll be able to achieve in Excel.
But first things first. Let's master the baby steps.
In this post you’ll learn the 5 simplest functions that Excel offers – SUM, AVERAGE, MAX, MIN and COUNT.
2. AUTOSUM
AutoSum is a button/feature that gives you quick access to the 5 aforementioned functions. It is the perfect tool to use if you are just starting out and want a smarter and easier way to work with your data.
The quickest and easiest place to find the AutoSum button on the Home ribbon. You’ll also find it on the Formulas ribbon.
Here are some different ways to use AutoSum to calculate a total for a series of numbers.
METHOD 1: Select just a column of numbers
1. Select a vertical range of numbers
2. Click the AutoSum button on the Home tab.
The total is added to the cell beneath the range you selected.
METHOD 2: Select a column of numbers and an extra cell
1. Select a vertical range of numbers and an extra blank cell for the total.
2. Click the AutoSum button on the Home tab.
The total is inserted into the blank cell.
METHOD 3: Select just a blank total cell
1. Select a blank cell at the bottom of a column of figures
2. Click the AutoSum button on the Home tab.
Excel looks up and left to find some data. If it discovers some, it displays the cell range for you.
3. If the cell range is correct, press Enter. The total is calculated.
4. If the cell range is incorrect or empty, use your mouse to directly select the range you wish to use then press Enter to calculate the total.
METHOD 4: Select just a row of numbers
1. Select a horizontal range of numbers.
2. Click the AutoSum button on the Home tab.
The total is added to the cell to the right of the range you selected.
METHOD 5: Select a row of numbers and an extra cell
1. Select a horizontal range of numbers and an extra blank cell for the total.
2. Click the AutoSum button on the Home tab.
The total is inserted into the blank cell.
METHOD 6: Select a row of blank total cells
1. Select a horizontal range of blank cells directly beneath two or more columns of data.
2. Click the AutoSum button on the Home tab.
The column totals are inserted into the blank cells you selected.
METHOD 7: Select a column of blank total cells
1. Select a vertical range of blank cells directly to the right of two or more rows of data.
2. Click the AutoSum button on the Home tab.
The row totals are inserted into the blank cells you selected.
METHOD 8: All in One
1. Select a 2-dimensional range of data plus a blank total row and a blank total column
2. Click the AutoSum button on the Home tab.
The column totals and the row totals are inserted into the blank cells you selected.
3. Anatomy of a SUM function
By default, the AutoSum, creates a SUM function, which adds together the values in the selected cells.
To see what the SUM function looks like, click on any total cell and study the formula bar.
You don’t have to use AutoSum. As you get more experienced, you will begin to just type the SUM function directly and use your mouse to select the cell or cell range.
The following formulas are valid examples of the SUM function.
4. The AVERAGE Function
The AVERAGE function divides the total by the number of items. It can be selected from the AutoSum drop down menu, or by typing it directly into the cell.
The structure of the Average function is =AVERAGE (number1, number 2 …)
5. The COUNT Function
The COUNT function counts how many cells in the specified range that contain a numerical value.
The structure of the COUNT function is =COUNT (number1, number 2 …)
6. The MAX Function
The MAX function returns the largest value within the specified range. This can be used to find the longest, tallest, deepest, widest, biggest etc.
The structure of the MAX function is =MAX (number1, number 2 …)
7. The MIN Function
The MIN function returns the smallest value within the specified range. This can be used to find the shortest, shallowest, smallest, quickest etc.
The structure of the MIN function is =MIN (number1, number 2 …)
8. More functions for beginners
Excel contains over 500 different functions. If you know what the function is called, you can just start typing it into a cell. But to search for a suitable function:
1. Click the fx icon next to the formula bar.
2. Typing a related search word or explore the categories.
3. Choose a function.
4. Each argument is given a box. Complete the boxes.
9. Using AutoFill to copy a formula
Autofill allows a formula in one cell to be copied into adjacent cells quickly and efficiently.
1. Select a cell or range.
2. Place the mouse pointer over the small solid square at the bottom right of the cell. This is called the Autofill handle. The pointer will change to a solid cross.
3. Click and hold the left mouse button and select adjacent cells beneath (or to the right).
10. Using AutoFill to copy just the formula (without the formatting)
1. AutoFill a formula cell as described above.
2. When the button is released a small icon appears at the bottom right of the selected range. Click this icon to display the AutoFill options.
3. Choose Fill Without Formatting.
10. Functions for beginners: Key takeaways
Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item!
ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.
Enjoy the rest of your day!
About the author
Jason Morrell
Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!
SHARE
