Even if you're an experienced Excel user, there's always something new to learn. Microsoft Excel is packed with powerful features and formulas that often fly under the radar. In this post, we'll uncover some lesser-known Excel formulas and hidden tricks that can significantly improve your efficiency. We’ll also explore how Data Validation can optimize your spreadsheets and why using “Clear Cells” macros can be a game-changer for workplace productivity. Whether you're an intermediate user or an advanced Excel pro, these tips will help you work smarter and faster. I encourage you to ask AI what you might not know you could do with Excel, and this goes for other things as well. It was really fun doing a deep dive and learning new tricks of the trade.
Newer or Underutilized Excel Formulas to Supercharge Efficiency
Excel’s formula library has expanded in recent years, introducing functions that simplify tasks which used to require complex workarounds. Here are some powerful but underutilized formulas you should know about:
XLOOKUP – This newer lookup function is a modern replacement for VLOOKUP and HLOOKUP. XLOOKUP is incredibly flexible: it can search both left-to-right and right-to-left, return exact matches by default, and even find the nearest match if an exact one isn’t found. It essentially replaces the widely used VLOOKUP, HLOOKUP, and INDEX/MATCH functions ( How XLOOKUP is Different from VLOOKUP and HLOOKUP | Excel Accountant ), offering a single function to handle all those lookup needs. For example, with XLOOKUP you no longer worry about the lookup column needing to be the first column. It can find a value in any column and return a related value from any other column in the same row. This means fewer errors and easier formula writing compared to the old VLOOKUP limitations.
Dynamic Array Functions (FILTER, UNIQUE, SORT) – Excel 365 introduced dynamic arrays, which allow formulas to spill results into multiple cells automatically. The FILTER function, for instance, lets you extract all records that meet a certain condition without manual filtering. You write one formula and all matching rows appear, spilling into adjacent cells. In one example,
=FILTER(A5:D20, C5:C20=H2, "")will return all rows from A5:D20 where the value in column C equals whatever is in H2 (FILTER function - Microsoft Support). No need for copy-pasting or writing multiple formulas – Excel generates the results dynamically. Similarly, UNIQUE can extract a list of unique values from a range in one go (great for pulling unique names or items from a list), and SORT/SORTBY can automatically sort a range as a formula. These functions eliminate many manual steps; advanced users who have dealt with array formulas before will appreciate how seamless this new approach is.LET – The LET function is a hidden gem that allows you to assign names (like variables) to intermediate calculations inside a formula. This makes complex formulas easier to read and can improve performance by calculating a sub-expression once and reusing it. For example, you could let
x = SUM(A1:A100)and then usexmultiple times in a larger formula, instead of writingSUM(A1:A100)over and over. The result is cleaner, more efficient formulas. Intermediate users may not have used LET yet, but it can be very powerful for simplifying nested formulas.AGGREGATE – AGGREGATE is like a supercharged version of functions such as SUM, AVERAGE, or MAX. It can perform various aggregations (sum, count, average, etc.) while optionally ignoring hidden rows, errors, or other subtotals. For instance,
AGGREGATE(9,6, range)would sum a range while ignoring any errors (the9denotes SUM and the6is an option to ignore error values). This formula is underutilized, but it’s extremely handy when dealing with filtered lists or data that might contain errors. Instead of cumbersome workarounds likeSUMIFto exclude errors, a single AGGREGATE can do the job.LAMBDA – For the more advanced users, Excel now even lets you create your own custom functions using the LAMBDA function. With LAMBDA, you define a formula and give it a function name as if you were creating a mini program – no VBA or macros required. In effect, you can create custom, reusable functions and call them by a friendly name, available throughout the workbook LAMBDA function - Microsoft Support. This opens up a new world of possibilities: if you have a complex calculation you use frequently, you can turn it into a personal function (say,
=MYCALC(param1, param2)) that behaves like a native Excel function. While LAMBDA is an advanced feature (currently available in Excel 365+), it’s worth noting as it represents Excel’s future – empowering users to extend Excel without code LAMBDA function - Microsoft Support.
(The above are just a few examples; other honorable mentions include the TEXTJOIN function for concatenating with a delimiter – more on that later – and the versatile SUMPRODUCT, often hailed as one of Excel's most flexible functions for conditional calculations (Excel TEXTJOIN Function - Chandoo.org and Excel TEXTJOIN Function - My Online Training Hub).)
Hidden Tricks with Classic Formulas (VLOOKUP, TEXTJOIN, CONCATENATE)
Even the well-known formulas can have hidden tricks. Let’s revisit a few classics – VLOOKUP, TEXTJOIN, CONCATENATE – and see how to use them in smarter ways.
VLOOKUP: Beyond the Basics
Most Excel users know VLOOKUP for fetching data from a table by matching an ID or key. But VLOOKUP has some lesser-known capabilities and pitfalls to be aware of:
Approximate Match for Ranges: By default, VLOOKUP’s last argument (
range_lookup) is TRUE (approximate match). When your data is sorted, you can exploit this to perform range lookups – for example, finding a tax rate or grade based on a numerical score bracket. If you set up the first column of the table with threshold values (in ascending order), VLOOKUP can return the appropriate category for any value by finding the next largest match below it. This is a handy trick for converting scores to grades or sales amounts to commission rates without a lengthy nested IF.Wildcard Matches: Did you know VLOOKUP can do partial matches with wildcards? If you use
FALSE(exact match) for the last argument and your lookup value is text, you can include the*or?wildcard in the lookup value. A question mark?stands for any single character, and an asterisk*stands for any sequence of characters VLOOKUP function - Microsoft Support. For example,=VLOOKUP("John*", A2:C100, 3, FALSE)would find the first name starting with "John" and return the 3rd column from that row (perhaps retrieving a phone number for "Johnson"). This trick is great for when you only know part of the text you're looking for. Tip: If your wildcard lookup might accidentally match unintended data, consider refining the lookup range or switching to more robust methods.Preventing Errors: A classic combo is wrapping your VLOOKUP with error handling. Use
IFERRORto catch the#N/Awhen a lookup value isn’t found. For instance,=IFERROR(VLOOKUP(...), "Not found")will return "Not found" instead of a nasty error. This isn’t exactly hidden, but it’s essential for making your spreadsheets user-friendly. Intermediate users sometimes forget this and end up with error clutter.Left Lookup Workaround: One of VLOOKUP’s well-known limitations is that it can only search the first column of your table array. What if the data you need to search is to the right of the value you want to return? One trick is to use the CHOOSE function inside VLOOKUP to rearrange columns in memory. For example,
=VLOOKUP(id, CHOOSE({1,2}, lookup_column, return_column), 2, FALSE)creates a temporary two-column array where the lookupcolumn becomes the first column and the returncolumn the second How XLOOKUP is Different from VLOOKUP and HLOOKUP | Excel Accountant. This way, you can effectively look left! This is an advanced trick (and XLOOKUP makes it unnecessary), but it’s good to know for older Excel versions.
(By the way, if you have the option, remember that XLOOKUP can do all of the above more elegantly — it searches left or right, defaults to exact match, and can handle errors with an optional argument. It’s literally built to overcome VLOOKUP’s shortcomings (How XLOOKUP is Different from VLOOKUP and HLOOKUP).
TEXTJOIN & CONCATENATE: Smarter Text Combining
Combining text strings is a common task – such as merging first and last names, or assembling an address from parts. CONCATENATE (or the & operator) has long been used for this, but modern Excel gives us TEXTJOIN, which offers more power and convenience.
TEXTJOIN: This function is a powerful way to join multiple text values with a delimiter. It was introduced in Excel 2019/Office 365, and many users still aren’t aware of it. TEXTJOIN lets you specify a delimiter (like a comma, space, or other separator) and even ignore empty cells. It’s far better than CONCATENATE in many cases because you can pass a whole range of cells instead of listing each cell individually, and you can choose to ignore blanks Excel TEXTJOIN Function - What is it, how to use it & 3 advanced examples » Chandoo.org - Learn Excel, Power BI & Charting Online. For example, if you have a list of words in A1:A5,
=TEXTJOIN(", ", TRUE, A1:A5)will produce a single text string like "word1, word2, word3, ..." skipping any empty cells in the range automatically. This single formula replaces what could be a messy combination of ampersands or multiple CONCATENATE calls.Hidden trick: You can use TEXTJOIN to combine results conditionally by nesting an IF formula inside it. This acts like a filter-then-concatenate. For instance, say you have a list of tasks in B2:B10 and in C2:C10 you have statuses. If you want to list all tasks that are "Completed" in one cell, you could use:
=TEXTJOIN(", ", TRUE, IF(C2:C10="Completed", B2:B10, "")).
Enter it as an array formula (Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365), and it will join only the tasks where the status is "Completed". This is an advanced use case, but it shows how versatile TEXTJOIN can be.CONCATENATE (and the
&operator): CONCATENATE is the older function to join strings. It’s straightforward but requires listing each item (e.g.,=CONCATENATE(A1, " ", B1)to join first name in A1 and last name in B1 with a space). A quicker trick is to use&instead: the formula would be=A1 & " " & B1. This is shorter to type and does the same thing. In fact, Excel has a newer function CONCAT (short for concatenate) which works like CONCATENATE but also allows range references. However, if you have TEXTJOIN available, you rarely need CONCAT.One nifty trick with CONCATENATE/
&: you can add special characters in between. For example, to combine text into multiple lines within a single cell, useCHAR(10)as the delimiter (and ensure the cell has "Wrap Text" enabled). For instance:=A1 & CHAR(10) & B1 & CHAR(10) & C1will put the contents of A1, B1, C1 each on a new line in the same cell. This isn't a separate formula, but a clever usage of&and CHAR(10) that many intermediate users don't know.
In summary, TEXTJOIN is the go-to for any scenario where you have to concatenate many pieces or a range of cells with a separator. It simplifies what used to require tedious formulas Excel TEXTJOIN Function - What is it, how to use it & 3 advanced examples » Chandoo.org - Learn Excel, Power BI & Charting Online. But if you're on an older version of Excel that lacks TEXTJOIN, remember the classic tricks with CONCATENATE (or &) and things like CHAR(10) for adding line breaks.
Data Validation: Optimize Data Entry and Accuracy
Improving productivity isn’t just about formulas – it’s also about avoiding mistakes and rework. This is where Data Validation comes in. Data Validation is a feature that lets you restrict or control the type of data that can be entered into a cell, ensuring that inputs are sensible and errors are minimized. This not only preserves data integrity but also saves you time that would otherwise be spent cleaning up mistakes.
Why is Data Validation so powerful for efficiency?
Prevents Data Errors at the Source: By setting rules on cells (e.g., numbers between 1 and 100, dates in the current year, text no longer than 20 characters, etc.), you ensure that invalid data can’t be entered. This maintains data accuracy by restricting the types of data allowed in a cell The Power of Data Validation: Ensuring Accuracy in Your Spreadsheets in Excel | Noble Desktop. The result is fewer downstream errors in formulas or analysis. As the saying goes, “garbage in, garbage out” – data validation helps keep the garbage out!
Consistent Inputs with Drop-Down Lists: One of the most common uses of data validation is creating drop-down lists. Instead of typing values (and risking typos or inconsistent entries like "NY" vs "New York"), you provide a predefined list of choices. This simplifies data entry and ensures consistency The Power of Data Validation: Ensuring Accuracy in Your Spreadsheets in Excel | Noble Desktop. For example, in a "Status" column you might restrict entries to Completed, In Progress, On Hold by giving a drop-down. Users save time (just click to select), and you avoid variants that could break formulas or require cleanup.
Guiding the User with Messages: Data Validation settings allow you to set an Input Message (a little tooltip that appears when the cell is selected, telling the user what is expected, like "Enter a date in YYYY-MM-DD format") and an Error Alert (a message that appears if the rule is violated, e.g., "Invalid entry – please choose from the list"). These prompts guide users in real-time, which improves accuracy and reduces frustration. It’s like having built-in instructions for your spreadsheet.
Advanced Validation with Formulas: For power users, data validation can be extended with custom formulas. This means you can impose almost any rule you can dream up. Want to ensure a deadline date is always after a start date? You can use a custom rule like
=C2 > B2on the deadline cell (assuming Start Date is in B2 and Due Date in C2). Or ensure a text entry contains "@" (for an email address) with a formula rule like=ISNUMBER(SEARCH("@", A1)). If the rule formula returns FALSE, the input is rejected. This is incredibly useful to enforce complex constraints and maintain data quality.
Incorporating data validation in your spreadsheet design upfront can save hours of troubleshooting and cleaning data later. It’s an optimization that ensures your formulas and analysis have good data to work with, ultimately boosting your productivity by reducing errors and manual corrections Data Validation: Techniques, Types, and Best Practices.
Clear Cells Macros: Automate Routine Cleanup Tasks
Have you ever spent time clearing out old data or blanking out input cells to start a fresh report? Doing that manually every time can be tedious and error-prone. Enter Clear Cells Macros – a simple but effective way to automate the cleanup.
What is a "Clear Cells" macro? It’s essentially a small program (written in Excel’s VBA, or recorded with the Macro Recorder) that, when run, will clear the contents of specified cells or ranges. Instead of you manually selecting cells and hitting delete (or Clear Contents) across multiple sheets or sections, the macro does it in one go. This can be a huge time-saver in a repetitive workflow.
The first time I came across this macro, it was in a workbook at my job. I later thought of a different use for it in another workbook, copied over a few rows and edited the cell references to the ones that needed clearing. You can do the same. Just copy/paste from Sub to End Sub below, and edit the cells. Notice b8 is only referencing one cell, so it doesn't need the ", " but c11 twice has the same outcome. One thing to keep in mind, is you can't undo the clear cells command once it's done, so save often and be mindful.
Sub Clearcells() Range("b2", "e3").ClearContents Range("b8").ClearContents Range("c11", "c11").ClearContents Range("b9", "e10").ClearContents End Sub
To link a button to your ClearCells macro in Excel, you have a few options:
1. Using a Form Control button:
• Go to the “Developer” tab in the Excel ribbon. (Enable it in Options if you don't see it.)
• Click on “Insert” and choose the “Button” under “Form Controls”.
• Draw the button on your worksheet where you want it to appear.
• In the “Assign Macro” dialog that appears, select your “ClearCells” macro and click “OK”.
• Right-click the button to edit its text if desired.Benefits of using a Clear Cells Macro (or any automation with macros):
Time Savings and Efficiency: Macros can execute a series of actions in milliseconds that might take you minutes to do by hand. Repetitive tasks like clearing cells, formatting sheets, or copy-pasting values can be done at the click of a button. In fact, with careful use, macros can help you automate repetitive tasks and keep your Excel sheet tidy and efficient How to Minimize Excel Sheet. Imagine having a monthly report template where, after copying last month’s data elsewhere, you need to clear input fields for the new month. A macro can clear all the relevant cells across the workbook instantly, so you’re ready to start fresh. This consistency not only saves time but also ensures you don’t accidentally delete something you shouldn’t – the macro will only clear the cells it’s programmed to.
Consistency and Accuracy: When you do things manually, there’s always a risk of missing a cell or clearing the wrong range. A well-written macro does the exact same steps every time, perfectly. That reduces mistakes. For example, a Clear Cells macro can be set to clear only the input cells (and not touch formula cells), so you never inadvertently wipe out a formula. It’s like having a reliable assistant who never deviates from the instructions.
Easy to Use (Even for Non-Programmers): You don’t need to be a VBA guru to create a simple clear-cells macro. Excel’s Macro Recorder allows you to record your actions (e.g., select range X, Clear Contents, select range Y, Clear Contents, etc.) and save that as a macro. Next time, you just run it (you can even assign it to a button on the sheet or a keyboard shortcut). In essence, Excel macros help you automate repetitive tasks without requiring advanced programming skills, bringing significant time savings How to Minimize Excel Sheet - Bricks. Once set up, anyone using the workbook can press the button and perform the cleanup routine in seconds.
Real-world example: Suppose you manage an order form in Excel that people fill out. At the end of each day or week, you need to clear the form for the next set of entries. Instead of manually deleting each cell or writing over old data (which could lead to mistakes or leftover old values), a Clear Cells macro can wipe all input fields (while leaving headings and formulas intact) instantly. In a workplace setting, this kind of macro ensures a fast turnaround and lets you focus on actual analysis rather than housekeeping tasks.
Macros in general are a huge boon for productivity. They essentially let Excel do the boring work for you. From our simple "clear cells" example to much more complex sequences of actions, macros free you from repetitive chores. Just always remember to save your workbook (or keep a backup) before running macros that modify data, and ensure you trust the macro’s source (since macros are code that can potentially do harmful things if misused). But when used properly, they are like having an autopilot for your spreadsheets. As one source puts it, increased productivity is a key benefit: macros can automate repetitive tasks like formatting cells or running calculations, thereby saving time and reducing errors Macros In Excel - Automated Productivity and Time Savings.
Conclusion
The world of Excel is vast. New functions like XLOOKUP, FILTER, and LET can transform the way you build formulas, making them more powerful and efficient. Classic formulas like VLOOKUP or good old CONCATENATE still have tricks up their sleeve that can save you time when you know how to use them to their fullest. Features beyond formulas – such as Data Validation to keep your data clean, and macros to automate tasks – are just as crucial in boosting productivity. The key takeaway is to continuously explore and update your Excel toolkit. Each formula or feature you add to your repertoire is another opportunity to streamline your workflow and impress with faster, error-proof spreadsheets.
Stay tuned to this blog for consistent updates as we build a community. If you're interested in working closer together or want to keep in touch, let me know in the comments of my YouTube channel @GeneRussAI, or reach out on X @DegeneRussAI
Thanks for stopping by!