Most Underrated Excel Functions You Must Know. Microsoft Excel is a flexible tool that is popularly used for data analysis, financial modelling, and various other tasks. While many users are familiar with basic functions like SUM, AVERAGE, and COUNT, Excel offers a plethora of functions that often go unnoticed. These underrated or obscure functions can significantly enhance your productivity and make complex tasks seem effortless.
In the article, we will dig into seven of the most underrated Excel functions that you must try to use the full potential of this versatile software.
What are Underrated/Obscure Excel Functions?
Underrated or obscure Excel functions are those tools that provide specialized capabilities but are not commonly known or used by the majority of Excel users. These functions are often tucked away in the Excel function library and might not be as prominently featured as the basic arithmetic functions. However, they can be incredibly valuable in specific scenarios, helping you perform tasks that might otherwise be time-consuming or challenging.
1. CONCATENATE
While CONCATENATE might not seem obscure at first glance, it’s often overshadowed by the “&” operator for joining text strings. However, the CONCATENATE function can be more versatile, especially when dealing with multiple strings or ranges. It allows you to combine text from different cells or strings, providing more control over formatting and delimiters.
One drawback of CONCATENATE is that it cannot automatically skip empty cells. If you’re dealing with ranges containing empty cells, you might end up with unintended extra spaces or separators.
2. TEXTJOIN
TEXTJOIN is a versatile function that enables you to combine many text strings with a specified delimiter. What sets it apart from CONCATENATE is its ability to ignore empty cells, making it extremely useful when working with data that might have gaps. This function is particularly handy for creating comma-separated lists or generating reports.
One potential disadvantage of TEXTJOIN is that it requires a bit more complex syntax compared to CONCATENATE. This might slightly increase the learning curve for some users.
3. IFERROR
Handling errors in Excel can be a real headache. The IFERROR function comes to the rescue by allowing you to specify a value or message to display when a formula results in an error. This can make your spreadsheets look more polished and prevent users from being confused by error codes.
A limitation of IFERROR is that it only handles errors generically. It doesn’t provide specific information about the type of error that occurred, which might make troubleshooting more challenging.
4. VLOOKUP
VLOOKUP might not be entirely obscure, but it’s often underutilized due to its perceived complexity. The function enables you to search for a value in a table and return a corresponding value from another column. It is incredibly useful for data retrieval and can save you significant time when dealing with huge datasets.
One demerit of VLOOKUP is that it only looks up values in the leftmost column of the table. This can be a constraint when you need to search in columns to the right.
5. INDEX and MATCH
INDEX and MATCH are two functions that, when used together, offer a powerful alternative to VLOOKUP. The combination allows you to find a value in a table and return a corresponding value from a different column, with the added flexibility of working with non-contiguous ranges. INDEX and MATCH are particularly handy for more complex lookup scenarios.
The main drawback of INDEX and MATCH is that they require a two-step process, which can be slightly more complex and harder to remember than VLOOKUP.
6. TRANSPOSE
The TRANSPOSE function can be a lifesaver when you need to flip your data from rows to columns or vice versa. Rather than manually copying and pasting, TRANSPOSE automates the process, saving you time and reducing the risk of errors.
One limitation of TRANSPOSE is that it can only work with a single array of data at a time. Transposing multiple ranges or data sets may require additional steps.
7. NETWORKDAYS
Calculating the number of workdays between two dates might seem straightforward, but it becomes more complex when you need to account for holidays and weekends. NETWORKDAYS simplifies this task by considering only the working days, allowing you to exclude specified holidays and weekends from the calculation.
A potential disadvantage of NETWORKDAYS is that it doesn’t consider half-days or shifts, which might be necessary for certain industries or scenarios.
Also See: MS Excel Interview Questions and Answers
Conclusion
Excel is a treasure trove of functions designed to simplify your data manipulation and analysis tasks. While the basic functions are essential, the underrated and obscure functions discussed in this article can take your Excel skills to the next level. Whether you are combining text, handling errors, performing advanced lookups, or manipulating data, these functions offer solutions to challenges you might not have even realized could be streamlined.
So, the next time you find yourself facing a seemingly daunting Excel task, remember to explore these underrated functions and unlock their hidden potential. Your productivity and proficiency with Excel will undoubtedly benefit from their incorporation into your workflow
