CODEX

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…


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. …


For all the great things about Excel, one of the most confusing things is how it stores and displays data.

Every cell has a number format, which can be selected from the dropdown in the Home ribbon tab. However, it is common practice to leave it on the default option General, as this automatically adapts to the value.


CODEX

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

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…


The standard way of storing data in Excel is to have one cell for one value. However, a value might be composed of multiple parts — making it harder to work with. You might also copy content from a webpage, only to find it all packs into the same cell when you paste it.

For any scenario like this, you could use Text to Columns to separate each part according to a delimiter. Alternatively, Power Query has a Split Column By Delimiter feature that does the same thing.

But what happens if you don’t want to split the data?

As…


There’s bound to have been a time when you’ve entered text into a cell, only to find it doesn’t all fit.

Widening the cell column would make sense, however, you may not want to if it interferes with something else.

In this situation, typically you’d use Merge & Center, which is found in the Home tab.


How to Use Goal Seek in Excel.

Excel’s Goal Seek is a What-If Analysis feature that allows backsolving calculations. It’s useful in situations where you have a known output value but want to find the input that dictates it.

In the ribbon, it is found in Data (tab) > What-If Analysis (Forecast group) > Goal Seek.


Still got that old copy of Excel installed?

Uninstall it, bin it (if it’s a CD-ROM), and get with the times.

STOP being an Excel dinosaur!

You’re Missing Out on the Updates

You may not be aware, but unless you have a Microsoft 365 subscription, you will not receive any updates (apart from security patches).

That means if you own a perpetual version of Office or any specific program, you are stuck with it. You miss out on all the exciting new tweaks, features and functions Microsoft is rolling out.

In the past, it was a viable excuse to say you couldn’t see the point of…

Andrew Moss

Writer | Spreadsheet Creator | Excel Aficionado https://www.linkedin.com/in/andrewcharlesmoss

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store