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 aging 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 pleasing design and how it splits up the different sections. …
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.
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 choosing from the following categories: SOLID DEM, LEAN DEM, TOSS-UP, LEAN REP and SOLID REP. …
Introduction
I am going to show you the different ways you can build a football league table in Excel. Some of the methods are old school but others utilise Excel’s new capabilities.
In case you weren’t already aware, Excel has undergone a big change to its calculation engine fairly recently. The concept of dynamic arrays was first introduced back in September 2018, however, for many Microsoft 365 users the first batch of new functions took an awfully long time to appear. Unless you have been an Office Insider, you will not have been able to use them. …
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.
Often I will use portion-based screenshots in my articles—because that’s the nature of what I do. It was whilst writing my ‘How to Build a Custom Ribbon in Excel’ story this problem became most apparent. I needed to capture numerous screenshots of controls contained in the custom ribbon I was explaining. …
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.
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 pollsters, but only narrowly in front with the bookmakers. Given the events of 2016 though, trust in any of these figures should be met with extreme caution. …
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
Introduction
In a previous post, I talked about the different ways you could create a football league table in Excel. Rather than show just one way, I wanted you to be able to see the old and new ways of doing it. Since dynamic array capabilities were unveiled, it has unravelled a plethora of possibilities with how Excel can be used.
Here, we are going to look at the new LET function. It is only available in beta versions of Excel at the moment, but it’s worth checking out because it will challenge your existing way of writing formulas.
If you have any experience with programming languages, you’ll probably be aware of the concept of a variable. A variable is a pre-defined name used to store information that is typically referenced multiple times throughout a coding script. …
About
