The SORTBY function was announced by Microsoft in September 2018 and is one of Excel’s new dynamic array functions. SORTBY makes use of the changes made to the calculation engine, enabling a single formula to spill calculations into multiple cells. At the time of writing, the SORTBY function is only available in Excel 365, Excel … Read more
The SORT function in Excel, is one of Excel’s best new features. It’s one of a group of functions that make use of Excel’s new dynamic array calculation engine, enabling Excel to spill results into multiple cells from a single formula. At the time of writing, the SORT function is only available for Microsoft 365, Excel … Read more
I can’t even begin to count the number of times I have created a unique list in Excel. I have performed it manually using remove duplicates from the ribbon, using PivotTables and with complex formulas, but that is now a thing of the past. When Microsoft announced changes to Excel’s calculation engine in September 2018, they also … Read more
Excel has changed… like seriously, changed. Every time we used Excel in the past, we accepted a simple operating rule; one formula one cell. Even with advanced formulas, it was still necessary to have a cell for each. But this has changed; Excel now allows a single formula to fill multiple cells. This is possible because Microsoft has changed … Read more
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. You may think of this as a lookup function that returns a picture, rather than a value. Ultimately, the purpose is to link an image or picture to a cell; … Read more
This post looks at how to calculate CAGR in Excel. CAGR stands for Compound Annual Growth Rate; it calculates the annual percentage growth over a period of time, where growth is compounded each year. CAGR is often used in finance to understand how different investments have performed over time. Excel does not have its own … Read more
The space character for an Excel user can be a real problem. When exporting data from various IT systems, that export will often include extra spaces around values. Finding a way to remove spaces in Excel is a crucial skill for every user. We often can’t see the space characters, but Excel sees them. They … Read more
One of the first things we learn in Excel is the magic of the $ symbol. It freezes the row or column, so the cell reference does not change when copying a formula. This is known as an absolute reference. With the introduction of Tables came a different (and more semantic) way to reference cells, called structured … Read more
Within Excel formulas, double quotes are used to denote the start and end of a text string. Therefore, the obvious question is: how to add double quotes in Excel? Look at the following formula as an example: The double quotes are at the start and end of the text string. But what if we want the … Read more
We have all experienced it, and often at the worst possible moment. For whatever reason, Excel’s formulas aren’t calculating correctly. The good news is that it is usually something simple. Once we know the most likely causes, it is easier to troubleshoot the problem. So, in this post, we look at the most likely reasons … Read more