Traditional and modern methods
Extracting unique values in Excel has always been possible, but it’s not always been easy. Only since the implementation of the new calculation engine has it become a doddle — thanks to the UNIQUE function.
Having said that, for many years you’ve been able to use the Remove Duplicates feature, an advanced filter or Power Query. However, nothing compares to the seamless nature of a dynamically updating formula, so that’s what I will be concentrating on.
The word unique is often abused in everyday life. Technically it means one instance of something — and only one. …
CODEX
Microsoft’s rival fares better in several areas
Excel is the behemoth of the spreadsheet kingdom. Across the length and breadth of every nation, it reigns supreme. As the market leader, the program has enjoyed a long stranglehold on its competition since dethroning the now-defunct Lotus 1–2–3 in 1995.
Despite its monopolistic dominance, others haven’t given up. Alternatives include Apple Numbers, LibreOffice Calc, Zoho Sheet and Quip Spreadsheets.
The one I wanted to focus on though is Google Sheets.
My first real experience of Sheets was at university in 2012, where I recall using it for a group assignment that was part of my web development degree. …
CODEX
Unintended calculations can occur
Did you know your Excel calculations can be affected by filtering tables or hiding rows?
It’s one of those questions you may well answer with an emphatic yes. But if I dig beneath the surface, I know many of you wouldn’t be able to tell me the difference between the SUM, SUBTOTAL and AGGREGATE functions, a 9 and a 109, or a 4 and a 5.
If that sounded like gobbledygook, don’t worry, because all will be clear by the end.
When you work with numeric table data in Excel, there’s a good chance you’ll want to perform a calculation…
CODEX
Another game-changer from Microsoft
It’s fair to say that Excel formulas are becoming like a programming language. Over the past couple of years, Microsoft has fundamentally changed Excel’s calculation engine and introduced a set of functions that allow us to construct formulas to do much more with less.
It doesn’t stop there.
If you are familiar with programming languages, you’ll know what a lambda is. It’s an anonymous function that is passed as an argument or returned from a function call. Well, now that very concept has made its way to Excel.
I am going to introduce to you the new LAMBDA function—which at…
Five methods you can use
A few months ago I encountered a scenario I hadn’t faced before. I was dealing with a large number of financial transactions from multiple bank accounts that were being housed in separate Excel worksheets. I needed to consolidate them so they would appear in one single table. This was part of an interactive profit and loss system I was building.
It may sound like a simple task, but I needed an automated solution that would update in real time.
Like with any Excel conundrum you’re faced with, the first port of call is trusty Google. …
CODEX
When Excel is misused, it can have catastrophic consequences
Excel is so ubiquitous it can give many the false impression they are more adept with it than they actually are. Let’s face it: everyone has it on their computer — whether at home or the workplace. But how many users can say they are aware of all its features and capabilities?
Most of the time, the mistakes people make have nothing to do with not possessing guru-esque Excel knowledge. They usually stem from being unaware of the nuances and intricacies that make up the application.
For all the benefits spreadsheets provide, they can be a double-edged sword. On one…
Images can appear differently on mobile, and it’s darn well frustrating
I have a gripe.
Don’t get me wrong—I like Medium as a platform. However, since starting to write articles last year, there is one problem that has frustrated me to the core: images.
You wouldn’t notice this problem if you only use the large built-in Unsplash gallery. Or if you get your pictures from places like Google Images. In fact, you wouldn’t notice it if you avoid the Medium mobile app altogether.
Most writers use full scale images, and this is never a problem. For writers like myself though, it can be a different story.
CODEX
Using XML and VBA
Microsoft’s ribbon interface has been with us since the launch of Office 2007. Although met with mixed reaction, over time many acknowledged it was a viable replacement for the ageing bland menus we had become accustomed to.
Microsoft developed the ribbon to increase user awareness of what was nested within each Office product. It was said that users would frequently request new features, oblivious to the fact that the very things they wanted to accomplish were hidden away in amongst a plethora of menu options.
For the most part, the ribbon has solved this issue due to its more aesthetically…
CODEX
Ready, Edit, Enter and Point
No matter how experienced you are with Excel, there is always something you wish you knew earlier. There’s a good chance you weren’t already aware of the different cell modes that exist in Excel. However, it’s worth being aware of them because they can save you time in certain instances.
You will see the current cell mode at the bottom-left of your Excel window.
Andrew Moss
I mainly focus on Excel-related content in my articles, often providing example workbooks to download, too. https://www.linkedin.com/in/andrewcharlesmoss
