One of the things which continually causes problems for all data professionals is dates. Over time, Microsoft has given us a plethora of tools and Excel functions for handling dates, yet they can still catch us out from time-to-time. Even with Power Query (Excel’s tool for manipulating data), we can run into issues when trying Read More
Category: Blog
VBA Code to insert, move, delete and control pictures
While it is not often that we have to control pictures or images within Excel, based on the number of Google searches for this topic, it’s clearly something people want to know more about. This post serves as a reference to cover most of the scenarios we’re likely to encounter to copy, insert, move, delete Read More
VBA code to copy, move, delete and manage files
Automating processes with VBA can involve copying, moving, deleting and managing lots of files. Thankfully, VBA has many built-in functions to undertake these tasks for us. These files do not necessarily need to be Excel workbooks; they can be any file type. Download the example file I recommend you download the example file for this Read More
Combining multiple tables in a PivotTable
When most people use PivotTables, they copy the source data into a worksheet, then carry out a lot of VLOOKUPs to get the categorization columns into the data set. After that, the data is ready, we can create a PivotTable, and the analysis can start. But we don’t need to do all those VLOOKUPs anymore. Read More
Advanced dynamic array formula techniques
In the final part of this dynamic array series, we’ll look at a few advanced formula techniques. We won’t be covering the individual functions in detail but considering how we can combine them to solve some tricky problems. Many of these techniques have been covered briefly as examples in previous posts, but now we’ll dig Read More
Using dynamic arrays with other Excel features
So far, in this series, we’ve looked at how dynamic arrays work and the new functions that are available to us. Most of our examples have focused on calculations that occur on the worksheet. But we’ve not yet considered how dynamic arrays work with other Excel features, such as charts, data validation, conditional formatting, etc. Read More
RANDARRAY function in Excel
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
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
