Microsoft Excel has been the leading spreadsheet-making software and data-processing application since its debut in 1985, where it quickly defeated competitors. Since its initial release, Excel has evolved to not just be a data organizing tool, but a comprehensive business intelligence and data analytic software, among many other things.
At the heart of Excel’s success lies two very important features: formulas and functions. These are the building blocks that allow users to create massive and complex spreadsheets full of intricate computations. Formulas and functions save countless hours of manual work for Excel users and help people wrap their heads around lengthy mathematical concepts, transforming confusing data into usable information.
Whether you're a novice seeking to grasp the basics or an advanced user aiming to refine your skills, this article serves as a comprehensive guide to the best Excel formulas and functions, so you can start configuring your data in new, groundbreaking ways.
What Are Excel Formulas & Functions?
If you’re not using formulas and functions in Excel, you’re hardly using Excel at all. Many beginners start using Excel for basic grid-like projects like making a calendar and feel they don’t need to use math equations, preferring to fill each cell in by hand instead.
While you certainly can do quite a lot this way, learning how to implement formulas and functions in Excel is the best way to rapidly improve your understanding of the software and your speed and efficiency when working with it. You can even use formulas and functions to help you create that calendar!
What is a Formula?
Simply put, formulas in Excel are mathematical expressions that operate based on the input you give them.
They deal mostly with simple math concepts, like addition, subtraction, multiplication, and division. They are simple to learn and implement and you can create very complex formulas to solve for expressions with lots of variables.
For example, a formula might look like "=1+2+3" or "=B1-B2"
What is a Function?
Functions in Excel can be a few different things. First, a function can act as a stored formula. For example, the functions SUM and AVERAGE allow you to quickly tell Excel to execute an addition or average-finding formula without having to type in every detail of the formula, which is especially helpful when you want to find the SUM of many different sections.
They can also be instructions for Excel, like to display the time and date, retrieve information, and perform more complicated functions specific to engineering, logistics, statistics, finances, and more.
How to Use Excel Formulas and Functions
To enter a formula or function in Excel, you’ll first need to select a cell that can display the result of the formula or function.
Then, in the formula bar in Excel, simply type “=” followed by the formula or function you want to use. Confirm the formula or function by pressing Enter/Return on the keyboard and Excel will automatically perform the instructions you entered in the cell you selected.
Formulas and functions have endless applications, so no matter what you need to solve for or prompt Excel to do, there’s likely a function for it!
And if not, you can create your own functions, called User Defined Functions (UDFs) using the programming language called Visual Basic Application (VBA) inside Excel.
Sometimes you might want to see your function or formula in your spreadsheet rather than their results. You can toggle to show formulas in Excel easily by going to the Formulas tab > Formula Auditing group and clicking the Show Formulas button.
How to Create Formulas in Excel
Making an Excel formula is one of the simplest tasks in Excel. Essentially, you just need to know how to do basic math!
There are two ways to enter variables into your formulaic equation:
- Enter the specific numbers you want to calculate with
- Use Cell references/addresses for the values in the cells you want to calculate with
You can mix these two methods, for example, you can write “=((A2*B2)/2)” so that even if the values of A2 and B2 change, they will always be divided by 2.
How to Enter a Formula in Excel
As previously discussed, you must enter the formula in the formula bar in the top center of the Excel worksheet. Start the formula with a “=” and go on to type in your formula in parentheses after.
To enter cell references, you can either manually type the cell reference or you can simply click on the cell, or click and drag to select a range, making it even easier for you to create the formulas you want!
You can enter the same formula in many cells within a row or column with the AutoFill feature. Simply click the lower right corner of the cell with the formula you want to repeat, and drag your cursor down the rows or across the columns you want the formula to get copied to. Unless you use absolute references in your formula text, Excel with automatically change the cell references.
How to Remove Formulas in Excel
Formulas can easily be removed by simply clicking on the cell that houses them and hitting the delete key. If you removed a formula by mistake, simply hit ctrl+z to undo your action and bring the formula back! Also note that if you reference the cell housing the formula elsewhere in your sheet as part of another formula, deleting the first formula will cause the second one to break.
If you have a formula that uses "relative references," those references will change every time you move it to another cell, which can change the values. To keep a reference the same no matter what, turn it into an "absolute reference" by using a "$" before the reference, for example, $B$10.
How to Create Functions in Excel
Excel functions are a slightly more advanced topic, but there are many formulas you can easily learn and understand even if you’re a beginner.
To enter a function, you perform the same action as when entering a formula: in the Excel formula bar, start with a “=” and type the name of your function followed by the arguments in a set of parentheses.
For example, the simple function SUM is written like this: “=SUM(D1+D2)”
You can add up multiple cells by adding a “:” between the first and last cell reference.
For example: SUM=(D1:D50)
And, if you want to reference multiple columns, you can separate the references with a comma.
Example: SUM=(D1:D50,H3:H25)
How to Create Custom Functions in Excel
Excel comes with over 450 included functions, but if you find that you need a function that doesn’t yet exist, you can make your own.
Excel allows users to create User Defined Functions (UDFs) using the built-in programming language called Visual Basic.
It’s an intermediate-to-advanced skill to make your own functions.
Here’s how to enter your own function:
- Hit Alt+F11 (Windows) or Fn + Opt + F11 (Mac) to open the VBA editor in Excel. Alternatively, you can go to the "Developer" tab and click on "Visual Basic.”
- Right-click on any item in the Project Explorer window, then choose Insert > Module to add a new module.
- Write the UDF code using VBA syntax.
- Close the editor.
You’ll need to learn more about VBA to be able to write your custom code, which is another topic in and of itself, but this is how you’ll enter your own code when you want to!
Excel Formula List: The Most Helpful Formulas
While Excel boasts over 450 pre-installed functions and an infinite amount of formulas, there are a handful that are useful to know as soon as you start using Excel.
The Most Useful Excel Spreadsheet Formulas
SUM
Purpose: Adds up numbers in an identified range of cells or a list of values.
Syntax: SUM(number1, number2, ...)
AVERAGE
Purpose: Calculates the average (mean) of a range or a list of values.
Syntax: AVERAGE(number1, number2, ...)
COUNT
Purpose: Counts the number of cells in a range that contain numbers.
Syntax: COUNT(value1, [value2], ...)
IF
Purpose: Performs conditional logic based on a given condition.
Syntax: IF(logical_test, value_if_true, value_if_false)
SUBTOTAL
Purpose: Performs various aggregate functions (e.g., SUM, AVERAGE) on a range, ignoring other SUBTOTAL functions within that range.
Syntax: SUBTOTAL(function_num, ref1, [ref2], ...)
CONCATENATE (or CONCAT)
Purpose: Combines two or more text strings into one.
Syntax: CONCATENATE(text1, [text2], ...)
UPPER
Purpose: Converts text to uppercase.
Syntax: UPPER(text)
LOWER
Purpose: Converts text to lowercase.
Syntax: LOWER(text)
PROPER
Purpose: Capitalizes the first letter of each word in a text string.
Syntax: PROPER(text)
NOW
Purpose: Returns the current date and time.
Syntax: NOW()
TODAY
Purpose: Returns the current date.
Syntax: TODAY()
DATEDIF
Purpose: Calculates the difference between two dates in terms of years, months, or days.
Syntax: DATEDIF(start_date, end_date, unit)
RANGE
Purpose: Calculates the difference between the lowest and highest values in a given range of cells with data.
Syntax: RANGE(cell_range)
Other popular functions are VLOOKUP and HLOOKUP, which replace the older function from previous Excel versions called LOOKUP. The difference between LOOKUP vs VLOOKUP and HLOOKUP is slight but impactful. The original LOOKUP function is simply too broad, so it has been deprecated and Microsoft recommends you use the more specific V and H LOOKUP functions instead.
VLOOKUP (Vertical Lookup):
- Functionality: VLOOKUP searches for a value in the first column of a table and returns a corresponding value in the same row from another column.
- Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Use Case: Commonly used for vertical lookups, such as retrieving information from a table based on a specific identifier.
HLOOKUP functions the same as VLOOKUP, but horizontally (H) instead.
How to Use the IF Function in Excel
IF on its own is a very useful function, but it can be combined with other functions to modify the original function meaning. The IF function is used for conditional logic, allowing you to perform different actions based on whether a specified condition is true or false.
A simple IF function might look like this:
=IF(A1>10, "Greater than 10", "Less than or equal to 10")
You can combine the IF function with many other functions to create such functions as SUMIF, COUNTIF, AVERAGEIF, and many others.
This way, you can instruct Excel to perform the main function as long as the variables fit within a certain parameter.
For example:
=COUNTIF(C1:C10, "Category1")
This formula counts the number of occurrences in the range C1:C10 where the value is "Category1". A typical COUNT function would count all instances of an entry between C1 and C10 that matches the specified value or text.
Here’s a handy Excel functions list of other helpful functions!
[table “us_101069” not found /]Learning Excel can seem like a daunting task when you know there are hundreds of ways to use the default options and millions of ways to create new ones as well. However, with patience and practice, you can learn new things in Excel easily over time. Before you know it, you’ll be an Excel expert!
You can supplement your learning with lessons from skilled Superprof tutors who are ready to help you boost your Excel skills in the most convenient way that works with your schedule!
Ready to start making amazing data systems in Excel?
Enjoyed this article? Leave a rating!
Loading...
