Getting Started With Excel Formulas
Excel formulas are just instructions you give the program to perform calculations on your data. They start with an equals sign and reference cells, ranges, or values. When you type =A1+B1 into a cell, Excel reads that as "look at A1, look at B1, add them together, and show me the result." That is it. Nothing complicated about the basic concept. What makes it useful is how quickly you can chain these together to automate tasks that would otherwise take hours of manual work. I am going to walk through the formulas I actually use every day, with real examples. Skip past the ones you already know. These are the foundation. You probably already know them, but they deserve a quick mention because people often overcomplicate things that should stay simple.
Addition and Subtraction: =A1+B1 subtracts or adds any two cells. For a range, use SUM instead: =SUM(A1:A100). This is faster than typing out A1+A2+A3 and so on. The SUM function also ignores blank cells automatically, which saves you from getting errors when your data has gaps. Multiplication and Division:
=A1*B1 multiplies. =A1/B1 divides. Simple enough. When working with percentages, remember that Excel stores 100% as the number 1. So if your price is in A1 and your tax rate is 8%, the formula is =A1*(1+0.08) or =A1*1.08. I have seen people multiply by 0.08 and then add it separately in another column, which works but creates unnecessary clutter in your spreadsheet. Power and Square Root: =POWER(A1,2) squares a value. =SQRT(A1) finds the square root. For most people, the caret operator works fine too: =A1^2 does the same thing as the POWER function. Use whichever reads clearer to you.
Statistical Functions
Mean, median, mode, standard deviation. These show up constantly in reporting work. AVERAGE: =AVERAGE(A1:A50) calculates the arithmetic mean. It skips text and empty cells. If your range has zero values, those count toward the average, which matters if you are computing something like average transaction size where zero represents a cancelled order. In that case, AVERAGEIF is the better tool.
Median and Mode: =MEDIAN(A1:A50) finds the middle value. =MODE.SNGL(A1:A50) returns the most frequent value. There is also MODE.MULT if your dataset has multiple modes, though that array formula requires pressing Ctrl+Shift+Enter in older Excel versions. Standard Deviation:
Get the Full Details
=STDEV.S(A1:A50) calculates sample standard deviation. =STDEV.P(A1:A50) does population standard deviation. The difference matters when you are working with a subset of data versus your entire dataset. Using the wrong one skews your confidence intervals. I once built a forecast model using STDEV.P on a sample of 30 data points and it understated the variability significantly. Switched to STDEV.S and the projections aligned with actual results much better.
Lookup and Reference Functions
This is where most people get stuck, so I will go into some detail here. VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches down the first column of a range and returns a value from a column you specify. The last argument being FALSE or 0 forces an exact match. Omitting it or setting it to TRUE uses approximate match, which requires your data to be sorted ascending. I cannot stress this enough: if your data is not sorted and you leave out the fourth argument, VLOOKUP returns garbage without warning you.
I ran into a problem once where a VLOOKUP was returning wrong values across an entire dataset. Turns out someone had merged cells in the lookup column, which split the data into separate rows that VLOOKUP could not match properly. The workaround was copying the column, pasting as values, and unmerging everything before running the lookup again. XLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) replaced VLOOKUP in Excel 365 and is genuinely better. It searches in any direction, does not break when you insert columns, and has built-in error handling through the if_not_found argument. If you are on a newer version of Excel, use XLOOKUP whenever possible. If you share files with people on older versions, stick with VLOOKUP or INDEX/MATCH.
INDEX and MATCH: Before XLOOKUP existed, this was the standard pairing. =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). INDEX returns the value at a given position. MATCH finds that position. Together they do what VLOOKUP does but with more flexibility: you can look left, look right, look up, look down. I still use this combination when building templates for clients who might be on Excel 2016 or earlier.
Text Functions
Cleaning and manipulating text strings is a regular part of data prep work. LEFT, RIGHT, MID: =LEFT(A1,3) pulls the first three characters. =RIGHT(A1,5) pulls the last five. =MID(A1, start_number, num_chars) extracts from the middle. These are useful when you have codes like INV-2024-001 and need to extract the year or the invoice number separately.
TEXTJOIN and CONCAT: =TEXTJOIN("-", TRUE, A1:A10) joins text with a delimiter and skips blanks. =CONCAT(A1, " ", B1) combines cells without a delimiter. TEXTJOIN replaced CONCATENATE and is much more flexible because it handles ranges directly. FIND and SEARCH:
=FIND("a", A1) locates a character and is case-sensitive. =SEARCH("a", A1) is not case-sensitive. The difference matters when you are parsing strings where uppercase and lowercase serve different purposes, like product SKUs. TRIM and CLEAN: =TRIM(A1) removes extra spaces. =CLEAN(A1) removes non-printable characters. When importing data from external systems, you will almost always need both. I typically wrap them together: =TRIM(CLEAN(A1)). This handles the two most common text contamination issues in one formula.
Date and Time Functions
Dates in Excel are stored as serial numbers, which means you can do arithmetic on them directly. TODAY and NOW: =TODAY() returns today's date. =NOW() returns the current date and time. These recalculate automatically every time the workbook opens or changes. Do not use them if you need a static timestamp. For that, use Ctrl+; for date and Ctrl+Shift+; for time.
DATEDIF: =DATEDIF(start_date, end_date, "unit") calculates the difference between two dates. The unit argument can be "Y" for years, "M" for months, "D" for days, "YM" for months excluding years, "YD" for days excluding years. This function is hidden, meaning it does not appear in autocomplete, but it works reliably. I use it constantly for tenure calculations. WORKDAY and NETWORKDAYS:
=WORKDAY(start_date, days, [holidays]) adds business days to a date. =NETWORKDAYS(start_date, end_date, [holidays]) counts business days between two dates. Both accept an optional holidays argument that references a range of date cells. This is essential for project scheduling. I had a case where a team used simple date subtraction for a delivery estimate and got 45 days, but the actual business days were only 32 because weekends were included in the count. The fix was wrapping the calculation in NETWORKDAYS.
Logical Functions
These let you build conditional logic directly inside formulas. IF: =IF(condition, value_if_true, value_if_false). Nested IFs are possible but get messy fast. If you find yourself nesting more than three levels deep, SWITCH or IFS is usually cleaner.
IFS: =IFS(A1>90, "A", A1>80, "B", A1>70, "C"). Each pair is a condition and a result. It stops at the first true condition. This replaced the old nested IF pattern for grading scales and tiered categorizations. AND, OR, NOT:
These are logical operators used inside IF statements. =IF(AND(A1>0, B1
100), "valid", "invalid"). AND requires all conditions to be true. OR requires at least one. NOT flips a condition. I combine these regularly when validating imported data before running downstream calculations. IFERROR: =IFERROR(formula, value_if_error). This catches #N/A, #DIV/0!, #VALUE!, and other errors and replaces them with whatever you specify. Use it sparingly. Hiding errors can mask real problems in your data. I usually apply it only to lookup functions where a missing match is expected, not to hide calculation mistakes.
Financial Functions
These are less commonly needed outside of accounting work, but they are worth knowing if you ever handle loan calculations or depreciation schedules. PMT: =PMT(rate, nper, pv) calculates the periodic payment for a loan. Rate is the interest rate per period. Nper is total number of periods. Pv is the present value or loan amount. The result is negative because it represents cash outflow. I add a negative sign in front if I want the payment displayed as a positive number: =-PMT(rate, nper, pv).
NPV and XNPV: =NPV(rate, values) assumes cash flows happen at regular intervals. =XNPV(rate, values, dates) handles irregular timing. Real-world cash flows are rarely perfectly regular, so XNPV is usually the more accurate choice for investment analysis.
Dynamic Array Functions
These are available in Excel 365 and Excel 2021 and change how you approach many problems. SORT and SORTBY: =SORT(A1:C100, 2, 1) sorts a range by the second column in ascending order. SORTBY lets you sort by a different range than the one being displayed, which is useful for ranked lists.
FILTER: =FILTER(A1:C100, B1:B100>"1000") returns only rows where column B exceeds 1000. This replaced the old approach of adding a helper column with an IF formula and then filtering manually. FILTER spills results automatically into adjacent cells, so make sure you have empty space around your formula or it will return a #SPILL! error. UNIQUE:
=UNIQUE(A1:A100) extracts distinct values from a range. Before this function, extracting unique values required a pivot table or a complex array formula. Now it is one function. SEQUENCE: =SEQUENCE(10) generates numbers 1 through 10 vertically. =SEQUENCE(5,3) creates a 5 by 3 grid. This is handy for generating test data or creating indexed reference tables without typing anything out.
Common Pitfalls to Avoid
Hardcoding values inside formulas. =A1*0.08 looks fine until tax rate changes and you have to hunt down every instance. Put the rate in a cell and reference it: =A1*$G$1. Locking the reference with dollar signs prevents it from shifting when you copy the formula down. Using entire column references in SUM or AVERAGE. =SUM(A:A) works but slows down large files because Excel evaluates over a million rows even if you only have data in the first hundred. Use =SUM(A1:A1000) or better yet, convert your range to a Table so the formula auto-expands: =SUM(Table1[Amount]). Mixing regional settings. Some versions of Excel use commas as decimal separators and others use periods. If you are sharing workbooks across regions, this can silently corrupt your calculations. Keep your locale consistent.
Referencing closed workbooks. =SUM('[other_file.xlsx]Sheet1'!A1) works but breaks if the source file is moved or renamed. I prefer using Power Query for cross-file data pulls because it tracks dependencies and refreshes cleanly. The initial setup takes longer, but it pays off quickly.
When Excel Formulas Are Not the Right Tool
Formulas become unreliable when your dataset grows past roughly 100,000 rows and you are doing repeated lookups across multiple large tables. Excel recalculates the entire sheet on every change, which gets slow. At that point, Power Pivot with DAX measures or Power Query for data transformation handles the workload much better. I transition projects to those tools once I hit memory warnings or calculate that manual optimization is taking longer than rebuilding the data model from scratch. For simple reporting with under 50,000 rows, formulas are still the fastest route. You can build a working dashboard in under an hour with basic functions. The same dashboard in Power BI might take half a day of setup. Choose the tool that matches the scale of your problem rather than the one that sounds most impressive.
