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…
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…
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
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.
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.
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…
An Excel-based simulator to visualise your state predictions
This article discusses the original release, v1. For v2, go here.
On Tuesday 3rd November, the winner of the 2020 United States presidential election will be announced. Unless you’ve been hiding in a basement, you will already know that the race to the White House is between incumbent Republican president, Donald Trump, and Democratic challenger, Joe Biden.
As a Brit watching from across the Atlantic (the pond), I can’t wait to stay up in the early hours of the morning watching the results unravel state by state.
It has already been a fascinating tussle, with Biden comfortably ahead amongst the…
CODEX
A convenient way of displaying season match results
Maybe you’re a football fan — maybe you’re not. Besides, the new Premier League season started last weekend. Amidst the empty stadiums and general doom and gloom, you’d be forgiven for not feeling as enthusiastic about it compared to previous seasons. However, I’ve got something for you that will whet your appetite.
I thought I’d show you a really cool results matrix I built in Excel, which allows you to view all match results for a given season. In addition, you can calculate totals and highlight matches based on a number of different criteria.
Prerequisites
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
