The RANDARRAY function makes use of the changes made to Excel’s calculation engine. These changes enable a single formula to spill results into multiple cells. The regular RAND function calculates a single random number, which is greater than or equal to zero and less than 1. The RANDARRAY function calculates a random number in the Read More
Category: Blog
SEQUENCE function in Excel
The SEQUENCE function is one of the new functions which Microsoft released as part of introducing dynamic arrays. This function makes use of changes to Excel’s calculation engine, which enables a single formula to display (or “spill” if using the new terminology) results in multiple cells. In the case of SEQUENCE, it will generate a Read More
FILTER function in Excel
Filtering is a common everyday action for most Excel users. Whether using AutoFilter or a Table, it is a convenient way to view a subset of data quickly. Until the FILTER function came to Excel, there was no easy way to achieve this with formulas. When Microsoft announced the changes to Excel’s calculation engine, they Read More
SORTBY function in Excel
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 to those on a Read More
SORT function in Excel
The SORT function 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 to those on a Microsoft Read More
UNIQUE function in Excel
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 the remove duplicates from the ribbon, Pivot Tables and using complex formulas, but that is now a thing of the past. When Microsoft announced changes to Excel’s calculation engine in September Read More
Dynamic arrays in Excel
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 Read More
Loop through selected sheets with VBA
Looping through worksheets is one of the most common actions performed by those who use VBA to automate Excel. However, we don’t always want to apply a macro to every worksheet; sometimes, we only want it for each selected sheet. Depending on the action being performed, this can cause an error. This occurs where the Read More
How to add a drop-down list in Excel
We come across drop-down lists on the internet and other software applications all the time. They are useful because they limit the options available to the user, as only pre-validated items can be selected. In this post, we’ll look at different ways to add a drop-down list in Excel. In Excel, there are three different Read More
Switch chart between monthly and quarterly
In this post, I want to answer a question asked by a reader. The question was from Nick: “I have a chart on a separate chart sheet, where I’d like to add a developer object (button, option box or checkbox) to control whether data is shown quarterly or monthly. Any idea how to do that?” Read More
