10 Essential Excel Functions for Beginners
Spreadsheets are an invaluable tool for organizing and analyzing data. Excel is one of the most popular spreadsheet apps, and it can significantly boost your productivity once you get the hang of it. To maximize your productivity with Excel, you can use functions to automate time-consuming calculations.
Excel has an arsenal of built-in functions, including simple functions that you could quickly learn and utilize in your spreadsheet. These ten functions will give you a better understanding of how functions work, and boost your journey to Excel mastery.
The SUM function sums the given values together and outputs the result.
The values can be numbers, cells, or ranges of cells. If you input non-numerical values, SUM will return an error, as it can’t sum text strings. If you refer to a range that contains text, SUM will ignore the texts.
To use the SUM function, you need to include it in your formula and give it the values you want to sum.
Example: The formula below sums the values inB2toB13.
2. AVERAGE
The AVERAGE function calculates the average of a group of numbers. You can feed the numbers directly, or refer to cells and ranges that contain them.
AVERAGE works exclusively with numbers. It will ignore the text cells if you feed it a range containing numbers and texts. To use the AVERAGE function, initiate your formula, call AVERAGE and then feed it the values.
Example: The formula below returns the average value of cellsB2toB13.
MIN is a simple function that serves only one purpose: Return the smallest value in a series. Despite being single-purpose, it can be useful in many scenarios.
you could refer to cells and ranges or input the values directly. MIN works with numbers only and will ignore cells that include text.
Example: The formula below returns the smallest value inB2toB11.
This function is the yin to MIN’s yang. Where MIN returns the smallest value, MAX returns the largest value.
MAX deals exclusively with numbers as well. If the range that you input contains text, MAX will ignore the text cells.
Example: The formula below returns the largest value inB2toB11.
Since you’re familiar with both MIN and MAX, you can combine them to calculate the difference between the largest and the smallest value in a range:
TheCOUNT function in Exceltakes in a range of values and returns the count of numeral values.
You can feed COUNT any sort of value, but it will only return the count of numeral values. If you refer to a range of cells, COUNT will return the count of cells that have numbers in them. COUNT will ignore cells that have text or other non-numerical data in them.
Example: The formula below counts cells with numbers inB2toB11.
COUNTA is an expansion of COUNT that counts cells with any value. COUNTA counts numbers, text, errors, and any cell that’s not blank.
You can feed COUNTA any sort of value, including cells and ranges.COUNTA counts non-blank cellsand will only omit the cell if it’s truly blank.
In Excel, putting a blank space in a cell adds content. As a result, although the cell seems blank, COUNTA will still count it. This is also true for a formula in your cell that returns a blank space (" “) or if you make a cell look blank through custom formatting.
Example: The formula below counts the number of non-blank cells inA1toB11.
7. COUNTBLANK
As the name implies, COUNTBLANK only counts cells that are completely blank, not cells that contain spaces or other non-visible characters.
This function is complementary to COUNTA. If you sum up the results from COUNTA (number of non-blank cells) with COUNTBLANK (number of blank cells), it will equal the total number of cells.
Excel’s standards for a blank cell are relatable here, too. COUNTBLANK only counts cells that are absolutely blank, and it omits cells that have blank spaces in them or look blank throughcustom formatting.
Example: The formula below counts the number of blank cells inB2toB11.
8. SUBSTITUTE
Excel’s SUBSTITUTE functionallows you to search for a specified string in cells and replace it with another.
In this syntax,stringwill be the target cell,old_stringwill be the part you want to replace, andnew_stringwill be the replacement. The last argument,instance_num, is optional.
If you leaveinstance_numblank, SUBSTITUTE will replace all instances ofold_stringwithnew_string. If you input2forinstance_num, SUBSTITUTE will replace the second instance ofold_stringwithnew_string. This function works with both numeral and text strings.
Example: The formula below substitutes+in cellsB2toB7withand. Since the last argument is left blank, all instances are replaced (C3).
The IF function in Excel takes in a logical expression, checks whether the expression is true, then returns custom outputs for the two outcomes.
The logical expression can be a simple statement such as1>2or a condition such asB2=1. If the expression is true, IF will returnoutput_if_true. Otherwise, it will returnoutput_if_false.
IF is a key function in creating sophisticated formulas. For instance, you cannest multiple IF functions inside each otherto create multiple conditions.
Example: The formula below calls IF to check whether the sum ofB2toB11is greater than40,000. If the condition is met, IF returnsYes. Otherwise, it returnsNo.
10. COUNTIF
This function counts the cells that meet a specified condition. Think of it as a combination of COUNT and IF.
In this syntax,rangeis the cells you want COUNTIF to look into, andconditionis the criteria COUNTIF will consider when counting them.
To use the COUNTIF function in Excel, you must first input the range of cells you want to count. Then you must specify the criteria for the cells you want to count.
Example: The formula below counts the number of values greater than5,000inB2toB11.
Make the Most of Your Spreadsheets With Excel Functions
Excel is a data analysis and organization tool that can drastically improve your productivity. If you’ve done your research on Excel, then you know that you’ll need to use Excel functions to maximize its potential.
Functions help you save time and increase the accuracy of your spreadsheets. There are tons of functions that satisfy different needs, but the essential Excel functions are useful for everyone. Now that you’ve added ten essential functions to your toolkit, you’re well-equipped to start organizing your data with Excel!
Excel’s conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks.
I found my TV was always listening—so I shut it down.
Sometimes the smallest cleaning habit makes the biggest mess.
Some subscriptions are worth the recurring cost, but not these ones.
Who asked for these upgrades?
Windows is great, but adding this makes it unstoppable.
