3 formulas to save time in Excel
Excel is ubiquitous. It's on everybody's computer. It's easy to use, convenient, and, in many cases, free.
It’s how my interest in data began and I've used it to create anything from Monte Carlo simulations to K-means cluster analysis to glossy interactive dashboards. What I love most about it is its sheer versatility. Need to input some data? Cool. Clean it? Fine. Transform it? Sure. Visualise it? No problem.
That said, there’s a case to be made that Excel’s “glory walks hand-in-hand with its doom”, to quote one of the greatest movies of all time. Sure, in the hands of a true maestro it can store data, capture clever business logic, conduct predictive analytics, account for inadvertent change, and showcase user interfaces that would make business intelligence tools green with envy. However, most regular users are far from maestros. The user base has a positively skewed capability distribution, or to put it another way, most of its users have never progressed beyond “Basic” skill. This, combined with its widespread accessibility, is likely to blame for its undeserved poor press in recent years.
Returning to the Tips, Microsoft has been teaching the old (Excel) dog a lot of new tricks since 2019. 41, to be precise. For today, I’ve narrowed it down to three of these. The formulas have a simple syntax, and they're likely to replace a more complicated alternative (or, dare I say, manual - gasp!!) that you're now using. Most importantly, if you spend a lot of time using Excel, they could literally save you days.
So, shortlisted for this Tuesday, I have UNIQUE, TEXTJOIN, and SWITCH! I've found that a visual example often trumps a long narrative, so I've included an image with an example for each formula. Enjoy!
=UNIQUE (Use this if you have a large list with duplicate entries and want to create a list that only shows unique values! This could save you minutes or days, depending on your present approach.)
=TEXTJOIN (use this to combine text from multiple cells into one, with or without a delimiter. I have seen many do this manually in the past, so again, it has the potential to save days for some)
=SWITCH (use this to consistently switch one word to another e.g. “England” to “GBR” - if you can’t get your head around Nested IFs or lookups, this is for you)
If you think these would make a worthy addition to your Excel toolkit, or if they've helped you optimise your current workflows in any way, I’d love to hear.
