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
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…
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. …
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.
An Electoral College tie is highly unlikely, but not inconceivable
The world is watching on as the United States is just hours away from finding out who their next president will be. Will Donald Trump come up trumps again and manage to secure a second term? Or will favourite Joe Biden become the fresh blood in the Oval Office?
Or…perhaps we won’t know for days—even weeks–who has won. Given what a crazy year 2020 has been, one should not rule anything out.
There is another possibility I wanted to focus on though, and that is the chance of a tied election.
Introducing the second instalment
This article discusses v2. For v1, go here.
Recently I posted an article about a US 2020 election simulator I had created, and I’m pleased with all the positive feedback I’ve received. I have made a few tweaks and added additions for version 2, which I will go through.
⚠️ Microsoft 365/Excel 2019 for Windows/macOS Required
Download the file: 2020-US-Election-Simulator-v2.xlsm
Discussion about this workbook can also be found at Reddit.
Additions
Automated State Strength
In v1, the strength of each state’s political leaning according to the poll averages were taken from the Financial Times’s election tracker. You could manually update the Strength column by…
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
