At the heart of Excel's success lie two very important features: formulas and functions. These are the building blocks that enable users to create massive, complex spreadsheets with intricate calculations. 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.
Key Takeaways
- Excel formulas start with
=and can combine numbers, operators, cell references, ranges, and functions. - The main categories of Excel formulas include maths, logic, text, dates, lookups, financial calculations, and statistics.
- Useful beginner formulas include
SUM,IF,TEXTJOIN,TODAY,XLOOKUP,PMT, andCOUNT. - Strong formula writing depends on clear cell references, simple ranges, and absolute references when copied formulas must keep pointing to the same cell.
| Formula Category | Key Formulas | What They Help You Do | Example |
|---|---|---|---|
| Mathematical and Trigonometric | SUM, ROUND, PRODUCT, SUMIF | Add, multiply, round, and total numbers conditionally | !ERROR! A1 does not contain a number or expression |
| Logical | IF, AND, OR, NOT, IFS | Test conditions and return different results | !ERROR! D1 does not contain a number or expression |
| Text | CONCAT, TEXTJOIN, LEFT, RIGHT, PROPER | Join, extract, and format text | !ERROR! A1 does not contain a number or expression |
| Date and Time | TODAY, NOW, DATEDIF, DATE, DAYS | Work with dates, times, and deadlines | !ERROR! unexpected closing bracket |
| Lookup and Reference | XLOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH | Find values in tables and ranges | !ERROR! A2 does not contain a number or expression |
| Financial | PMT, FV, NPV, PV, RATE | Calculate payments, investment value, and rates | !ERROR! illegal character '%' |
| Statistical | COUNT, COUNTA, AVERAGE, MEDIAN, COUNTIF | Count, summarise, and analyse data | !ERROR! Cell R1 does not exist |
Understanding Excel Formulas
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 formulas, 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!
Sometimes you might want to see your function or formula in your spreadsheet rather than its results. You can easily toggle formulas in Excel by going to the Formulas tab > Formula Auditing group and clicking the Show Formulas button.
What Are Excel Formulas?
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 expressions with many variables. For example, a formula might look like "=1+2+3" or "=B1-B2"
A formula is the full calculation you type into an Excel cell, usually beginning with =. A function is a built-in command that performs a specific calculation inside a formula, such as SUM, IF, or XLOOKUP. For example, =SUM(A1:A10) is a formula that uses the SUM function. Understanding this difference makes it much easier to read and write Excel formulas correctly.
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 parentheses.
How to Use Excel Formulas
To enter a formula or function in Excel, you'll first need to select a cell that can display its result. 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 execute the instructions you entered in the selected cell. 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!
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. To enter cell references, you can either type the cell reference manually, click the cell, or click and drag to select a range, making it even easier to create the formulas you want!
Categories of Excel Formulas with Examples
Excel formulas can help you do a lot. To make it clearer, we've grouped them by what they help you do. Whether you're adding numbers, checking conditions, joining text, working with dates, or finding information in tables, you'll likely need a formula. Since Microsoft organises Excel functions by category, it's easier to choose the right formula for each task.¹ 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.
Excel includes many functions, but most users rely on a smaller set of formulas for everyday work. Start with common formulas like SUM, IF, COUNT, TEXTJOIN, TODAY, and XLOOKUP, then build from there as your tasks become more specific. Learning by category is usually easier than memorising a long alphabetical list.
Mathematical and Trigonometric Functions
SUMadds numbers together, such as=SUM(A1:A10).³ROUNDrounds a number to a set number of decimal places, such as=ROUND(C1,2).³PRODUCTmultiplies numbers, such as=PRODUCT(A1:A5).³SUMIFadds values only when they meet one condition, such as=SUMIF(A1:A10,">100").³MAXandMINcan be used together to calculate a data range, such as=MAX(A1:A10)-MIN(A1:A10).¹
Logical Functions
IFreturns one result if a condition is true and another if it is false, such as=IF(D1>100,"Over Budget","Within Budget").⁴ANDreturns TRUE only when every condition is true, such as=AND(E1>0,E1<100).⁴ORreturns TRUE when at least one condition is true, such as=OR(F1="Yes",F1="Maybe").⁴NOTreverses a TRUE or FALSE result, which is useful when checking whether something is not the case.⁴IFStests several conditions in order, which can make long nestedIFformulas easier to read.⁴
Text Functions
CONCATENATEjoins text together, butCONCATis the more modern version to teach first.⁵TEXTJOINjoins text with a chosen separator, such as=TEXTJOIN(" ",TRUE,A1:B1).⁵LEFTextracts characters from the start of a text string, such as=LEFT(I1,5).⁵RIGHTextracts characters from the end of a text string, such as=RIGHT(J1,3).⁵UPPER,LOWER, andPROPERchange capitalisation in text.⁵
Date and Time Functions
TODAYreturns the current date, such as=TODAY().⁶NOWreturns the current date and time, such as=NOW().⁶DATEDIFcalculates the difference between two dates, such as=DATEDIF(K1,K2,"d").⁶DATEcreates a date from separate year, month, and day values.⁶DAYSreturns the number of days between two dates.⁶
Lookup and Reference Functions
VLOOKUPsearches down the first column of a table and returns a value from another column in the same row.⁷HLOOKUPsearches across the first row of a table and returns a value from another row in the same column.⁷INDEXreturns the value at a chosen row and column inside a range.⁷MATCHreturns the position of a value inside a row, column, or range.⁷XLOOKUPis the cleaner modern option for many lookup tasks because it can search in different directions.⁷
Financial Functions
PMTcalculates a regular loan payment, such as=PMT(5%/12,60,-10000).⁸FVcalculates the future value of an investment based on payments and interest.⁸NPVcalculates the present value of future cash flows using a discount rate.⁸PVcalculates what a future amount is worth today.⁸RATEcalculates the interest rate needed for a loan or investment calculation.⁸
Statistical Functions
COUNTcounts cells that contain numbers, such as=COUNT(R1:R10).⁹COUNTAcounts cells that are not empty, such as=COUNTA(S1:S10).⁹AVERAGEcalculates the mean of a group of numbers, such as=AVERAGE(B1:B10).⁹MEDIANreturns the middle value in a set of numbers, such as=MEDIAN(T1:T10).⁹COUNTIFcounts cells that meet one condition, such as=COUNTIF(A1:A10,"Yes").⁹
Tips for Mastering Excel Formulas
Learning Excel can seem daunting when you know there are hundreds of ways to use the default options and millions of ways to create new ones. 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! The best way to improve is to start with simple formulas, then gradually combine functions, cell references, and ranges as your spreadsheets become more complex.²
Excel allows users to create User-Defined Functions (UDFs) using the built-in programming language, Visual Basic. And if not, you can create your own functions, called User-Defined Functions (UDFs), using the programming language Visual Basic for Applications (VBA) in Excel.
Best Practices for Writing Formulas
- Start every formula with
=so Excel recognises it as a calculation.² - Use cell references instead of fixed numbers when values may change later.²
- Use ranges like
A1:A10to keep formulas shorter and easier to check.² - Use absolute references like
$B$10when a copied formula must always refer to the same cell.² - Leave empty cells beside formulas that return multiple results, because dynamic array formulas can spill into neighbouring cells.¹⁰
References
- Microsoft Support. “Excel Functions (by Category).” Microsoft, https://support.microsoft.com/en-us/excel/excel-functions-by-category. Accessed 2 Jul. 2026.
- Microsoft Support. “Overview of Formulas in Excel.” Microsoft, https://support.microsoft.com/en-us/excel/get-started/overview-of-formulas-in-excel. Accessed 2 Jul. 2026.
- Microsoft Support. “Math and Trigonometry Functions (Reference).” Microsoft, https://support.microsoft.com/en-us/excel/math-and-trigonometry-functions-reference. Accessed 2 Jul. 2026.
- Microsoft Support. “Logical Functions (Reference).” Microsoft, https://support.microsoft.com/en-us/excel/logical-functions-reference. Accessed 2 Jul. 2026.
- Microsoft Support. “Text Functions (Reference).” Microsoft, https://support.microsoft.com/en-us/excel/text-functions-reference. Accessed 2 Jul. 2026.
- Microsoft Support. “Date and Time Functions (Reference).” Microsoft, https://support.microsoft.com/en-us/excel/date-and-time-functions-reference. Accessed 2 Jul. 2026.
- Microsoft Support. “Lookup and Reference Functions (Reference).” Microsoft, https://support.microsoft.com/en-us/excel/lookup-and-reference-functions-reference. Accessed 2 Jul. 2026.
- Microsoft Support. “Financial Functions (Reference).” Microsoft, https://support.microsoft.com/en-us/excel/financial-functions-reference. Accessed 2 Jul. 2026.
- Microsoft Support. “Statistical Functions (Reference).” Microsoft, https://support.microsoft.com/en-us/excel/statistical-functions-reference. Accessed 2 Jul. 2026.
- Microsoft Support. “Dynamic Array Formulas and Spilled Array Behavior.” Microsoft, https://support.microsoft.com/en-us/excel/dynamic-array-formulas-and-spilled-array-behavior. Accessed 2 Jul. 2026.
Summarise with AI:
Did you like this article? Leave a rating!
Loading...
