The Excel team recently announced new dynamic array formulas, which can create unique lists, sort and filter with a simple formula. These new formulas are being rolled out to Office 365 subscribers over the next few months. However, not everybody has the subscription and will upgrade when it is sensible for their business, often combining Read More
Category: Formulas
Spreadsheet Day 2018
Happy Spreadsheet Day! 17th of October is Spreadsheet Day, the day each year when we celebrate… you guessed it… spreadsheets. VisiCalc, the first spreadsheet application, shipped on 17 October 1979. Originally it was only compatible with the Apple II and cost $99. I don’t know if VisiCalc had formulas and functions. Sometimes I wonder what Read More
Excel Tables – Absolute cell & column references
One of the first things we learn in Excel is the magic of the $ symbol. It freezes the row or column, so when copying a formula, the cell reference does not change. With the introduction of Tables came a different (and more semantic) way to reference cells, called structured references. However, structured references don’t Read More
Create a running total in an Excel Table
Excel Tables are one of the best features of Excel. Tables were originally introduced as part of Excel 2003, under the name of Lists, but were expanded and renamed to Tables in Excel 2007. Whilst it is possible to use the standard cell referencing with a Table, they have their own referencing style. Using this Read More
3 ways to change or insert images based cell values
From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell value. Or you may think if this a formula which changes a picture, rather than a number. Ultimately, the purpose is to link an image or picture to a Read More
Getting values from a closed Excel workbook
One of the things which scares me in Excel is seeing this message: So many things can go wrong with linked files, yet unsuspecting users tend to use them with pride. “Look, I’ve linked this so that I don’t have to update a cell, isn’t this cool!”. If they knew the risks they might not Read More
Excel’s AVERAGE function – the hidden pitfalls
AVERAGE is one of those easy functions in Excel. Like, super easy! It has just one argument, the cells, or numbers to be averaged. So what’s the problem? It’s too easy to get wrong, yet we might not notice for weeks, months, years or ever. Look at the screenshot below. The result of each method Read More
The real reason INDEX/MATCH is better than VLOOKUP
If you read a lot of Excel blogs and sites, you will see a common topic arise which encourages us all to use the INDEX MATCH formula combination, rather than VLOOKUP. I’m not going to disagree with that recommendation, however I feel the question of “Why is INDEX MATCH better than VLOOKUP?” is often not Read More
AGGREGATE: The best Excel function you’re not using
In Excel 2010, Microsoft introduced a new function called AGGREGATE. It is one of the most powerful functions in Excel, yet I’ve rarely seen it used in a real-life scenario. My aim with this post is to show you how good AGGREGATE is, so that you can make use of its power. What does Read More
Change tab color based on a cell value in Excel
Wouldn’t it be great to change the color of a worksheet tab based on a calculation? If you’re not convinced, here are some possible scenarios: When a schedule reconciles or includes a specific cell value If there is an error in a range of cells To color code tabs to match a contents page You Read More