Excel Functions You Actually Use
Most people open Excel and stare at a blank grid like it owes them money. They have no idea where to start. The tool is overwhelming until you understand that functions are just shortcuts for things you would otherwise do by hand. That is all they are. Not magic, not some kind of secret knowledge. Just automation for repetitive calculations. I spent the first few years of my career doing manual pivots on data that was already three months old. I would manually count rows with VLOOKUP, cross-reference them, then do it again because someone changed a column. Every single time. It took about four hours for what a single INDEX-MATCH combo does in twelve seconds. Once I learned how to use the right function for the job, my Tuesday afternoons stopped being a waste of my life.
Functions Of Ms Excel With Example
Let me walk through the functions that actually matter in a real workplace, with examples you can copy and use immediately. Skip the ones I am not listing unless you have a reason to care about them. VLOOKUP is probably the first function anyone learns, and it is also the first function that gets people in trouble. It searches down the first column of a range and returns a value from a column you specify by index number. Simple enough until your source data changes shape. Here is a practical example. Say you have a product list in columns A through C. Column A has product IDs, column B has names, and column C has prices. You want to pull the price for a specific ID into another sheet. The formula looks like this: =VLOOKUP(E2, A:C, 3, FALSE). Cell E2 contains the ID you are searching for, A:C is your data range, 3 is the column number for price, and FALSE means exact match only.
The problem with VLOOKUP is that it breaks when you insert or delete columns in your source data. If someone adds a column between A and C, your index number of 3 now points to the wrong data. I encountered this once on a financial model where a colleague added a dummy column for formatting purposes, and suddenly every price in the entire report was showing the wrong value. The model had been running for six months without anyone noticing because the numbers were in the same ballpark. It cost us about three weeks of rework. The workaround I use now is XLOOKUP when it is available, or pairing VLOOKUP with MATCH so the column index is dynamic instead of hardcoded. Something like =VLOOKUP(E2, A:C, MATCH("Price", A1:C1, 0), FALSE). The MATCH part finds the column header automatically, so inserting a column does not break anything.
Get the Full Details
INDEX and MATCH
INDEX and MATCH together replace most VLOOKUP use cases and handle things VLOOKUP simply cannot do. INDEX pulls a value from a specific row and column within a range. MATCH finds the position of a lookup value within a range. Put them together and you get a lookup that is faster, more flexible, and does not break when columns move. Example: =INDEX(C:C, MATCH(E2, A:A, 0)). This returns the value from column C where column A equals the value in E2. The difference from VLOOKUP is that you reference entire columns instead of a fixed range, and the lookup column can be anywhere in your data, not just the leftmost one. I used this pattern on a dataset with over two hundred thousand rows where VLOOKUP was choking the workbook. Switching to INDEX-MATCH cut the recalculation time from about forty-five seconds down to under three. The difference was noticeable enough that my manager asked if I had upgraded my computer. I had not. The formula was just better.
IF and Nested IF Statements
The IF function evaluates a condition and returns one value if true and another if false. It sounds basic, but most people do not realize you can nest multiple IFs inside each other for multi-condition logic, or use IFS in newer versions of Excel to avoid nesting nine levels deep and losing your mind. Example: =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "F"))). This grades a score in A2 as A, B, C, or F depending on the range. The older nested approach works fine for three or four conditions. After that, it becomes unreadable and harder to maintain. A common mistake I see constantly is people using IF with text comparisons without wrapping the text in quotes or using proper cell references. =IF(A2="Yes", 1, 0) works. =IF(A2=Yes, 1, 0) throws a formula error because Excel thinks Yes is a named range that does not exist. It is a silly error but it stops dead people who are new to the software.
SUMIFS and COUNTIFS
These two functions are where Excel starts becoming useful for actual business work instead of just calculator replacements. SUMIFS adds values that meet multiple criteria. COUNTIFS counts rows that meet multiple criteria. Both accept wildcards and handle dates, text, and numbers. Example: =SUMIFS(C:C, A:A, "North", B:B, ">="&TEXT(DATE(2024,1,1), "0"), B:B, "="&TEXT(DATE(2024,12,31), "0")). This sums column C where column A equals "North" and column B falls within the year 2024. The ampersand concatenates the operator with the date value, which is a pattern you need to remember or the formula returns zero. I built a quarterly revenue tracker using SUMIFS that replaced what used to be a manual process of filtering, highlighting, and counting rows by hand. The manual version took about ninety minutes per quarter. The formula version takes about four minutes to set up and then recalculates in under a second whenever the source data changes. That is the kind of time savings that makes people take you seriously in a meeting.
MATCH and SEARCH for Text Extraction
Sometimes you do not need to look up a value. You need to find a substring within a larger string and extract it. SEARCH finds the position of text within text, and when combined with LEFT, MID, or RIGHT, it becomes a powerful extraction tool. Example: =MID(A2, SEARCH("@", A2)+1, SEARCH(".", A2, SEARCH("@", A2))-SEARCH("@", A2)-1). This pulls the domain name out of an email address in A2. It is ugly to look at, but it works reliably on a clean email format. I used this on a list of about fifteen thousand customer email addresses to extract domains for a marketing segmentation project. Manual extraction would have taken me two full days. This formula did it in about eight minutes across all the sheets.
DATEDIF for Age and Duration Calculations
DATEDIF is a hidden function that Microsoft does not document properly but has been in Excel for decades. It calculates the difference between two dates in years, months, or days. Most people use simple subtraction for date differences, but that gives you a raw number of days that is hard to convert manually. Example: =DATEDIF(B2, TODAY(), "Y"). This returns the age in full years from a birthdate in B2. Use "YM" for months only or "MD" for days only. The function ignores errors in most practical scenarios, which is both a blessing and a problem because silent failures are harder to debug than loud ones. I learned about this function the hard way after building an employee tenure tracker that produced incorrect results during leap years. Simple date subtraction adds an extra day in leap years that throws off age calculations if you are not careful. DATEDIF handles leap years correctly by design. That single switch saved me from having to rebuild the entire tracker from scratch.
TEXT and DATEFORMATTING Functions
TEXT converts a value into text with a specified format. It is one of the most underused functions because most people format cells using the ribbon interface instead of embedding formatting in the formula itself. There are good reasons to use TEXT inside formulas when you are building dynamic labels or concatenating values. Example: =TEXT(TODAY(), "MMMM DD, YYYY"). Returns the current date as "July 15, 2025" instead of "7/15/2025". Combine this with CONCATENATE or the ampersand operator to build readable output strings for reports. The limitation here is that TEXT returns actual text, not a date value. If you then try to do date arithmetic on the result, it will fail. I once concatenated a formatted date into a label and then tried to sort the resulting range chronologically. Excel sorted it alphabetically because the dates were now stored as text. The fix was to keep the date in one column and use TEXT only in a separate display column.
ERROR HANDLING WITH IFERROR
#N/A, #DIV/0!, #VALUE!, #REF! — these errors show up constantly in real spreadsheets. IFERROR catches them and returns a custom value instead. It is not a perfect solution because it also hides legitimate errors that you need to fix, but it makes reports look presentable. Example: =IFERROR(VLOOKUP(E2, A:C, 3, FALSE), "Not Found"). If the VLOOKUP fails, the cell displays "Not Found" instead of #N/A. Clean, readable, and professional-looking in a dashboard context. The pitfall is that IFERROR masks the underlying problem. I reviewed a workbook once where an IFERROR was hiding a broken formula that had been producing #N/A for months. The person who built it had wrapped everything in IFERROR and moved on without checking why the lookup was failing in the first place. The missing data went undetected until an audit. Use IFERROR, but verify your results independently before handing the file to anyone else.
UNIQUE and FILTER (Newer Functions)
If you are on Excel 365 or Excel 2021+, UNIQUE extracts distinct values from a range, and FILTER returns a subset of data based on criteria. These replace entire sections of manual work that used to require pivots, conditional formatting, or helper columns. Example: =UNIQUE(A2:A500). Returns every unique value from the range A2 through A500. =FILTER(A2:C500, B2:B500="Active"). Returns all rows where column B equals Active. Both spill automatically into adjacent cells, which means you do not need to drag formulas down. The catch is compatibility. These functions only work in newer Excel versions and do not work at all in Google Sheets or LibreOffice Calc. If you share your workbook with people who use older software or alternative platforms, your formulas will return #NAME? errors. I learned this the hard way when a client asked to view a dashboard I built with FILTER, and their Excel 2016 installation had no idea what FILTER meant.
AGGREGATE for Robust Calculations
AGGREGATE is similar to SUM or AVERAGE but with options to ignore hidden rows, errors, and nested subtotals. It is overkill for simple lists but becomes indispensable when dealing with filtered data or messy source files that contain errors from merged imports. Example: =AGGREGATE(9, 5, C2:C1000). The 9 means SUM, the 5 means ignore error values and hidden rows. This is more reliable than SUM on a range that might contain #N/A values from failed lookups. I use AGGREGATE instead of SUBTOTAL when I need to sum a range that includes error values. SUBTOTAL ignores hidden rows but will stop and return an error if it encounters a #N/A in the range. AGGREGATE with the ignore-errors option handles both problems in a single function call.
Practical Limitations You Should Know About
No function is perfect. Excel has hard limits that will break your workflow if you hit them. The row limit is just over one million rows. Column limit is sixteen thousand plus. Formulas have a character limit of around eight thousand characters. Nested IF statements top out at sixty-four levels in most versions. Power Query exists precisely because these limits become real problems when you are working with large datasets. If your data exceeds about fifty thousand rows with complex formulas, expect slowdowns. Each calculation triggers a full recalculation cycle, and the larger the range, the longer it takes. I once had a file with three hundred thousand rows and about two hundred complex formulas. Opening it took approximately four minutes. Closing it took three. Saving took five. The file was functional but painful to use day to day. Moving the data model to Power Pivot and DAX measures reduced load time to about twelve seconds.
When to Stop Using Formulas and Start Using Other Tools
There comes a point where adding more formulas makes the workbook worse instead of better. If you find yourself nesting eight different functions into a single cell, or if your sheet contains more formulas than actual data, it is time to step back. Power Query handles data transformation more efficiently. Pivot tables aggregate large datasets faster. A proper database connection bypasses Excel's calculation engine entirely. Formulas are the right tool for defined, structured, moderate-sized data. They are the wrong tool for cleaning messy imports, processing millions of rows, or building something that needs to be audited by someone who does not know how your worksheet works. Know the boundary and respect it.
Functions Of Ms Excel With Example
The examples above cover the functions I actually reach for on a daily basis. Everything else is either a niche case or something that can be handled by a different tool. Pick two or three from this list, build a small practice sheet, and use them until they feel automatic. Then add another one. That is how you stop treating Excel like a stranger and start treating it like a utility.
