This tutorial will return non-adjacent columns with the FILTER function. Dynamic array functions such as FILTER and SORT can only return consecutive columns natively. However, there are various methods to specify the columns to return from the array. Functions such as CHOOSECOLS, CHOOSE and INDEX are wonderful for this task.
You can download the Excel workbook to follow along
This tutorial will look at how to calculate moving average in Excel. The moving average is sometimes referred to as the rolling average as it averages the values from a rolling, or moving, date range. In this post, our focus is on a seven-day moving average.
You can download the Excel workbook used in this tutorial to follow along.
The IMAGE function in Excel enables us to return an image to a cell using an Excel formula.
This function makes it easy to lookup images based on a cell value, and because the image is stored in the cell, it is returned when sorting and filtering, and when using other functions such as XLOOKUP, FILTER and IF.
The IMAGE function accepts five arguments, but only the source argument is mandatory. The other arguments enable us to specify alternative text and to specify the size of the image.
IMAGE(source, [alt_text], [sizing], [height], [width])The Excel IMAGE function can handle numerous image formats including JPEG, PNG, bitmap, TIFF, and more. The source, however, must be a URL using the ‘https’ protocol.
Download the practise workbook to follow along.
Let’s see some examples of it in action.
In this tutorial, we will introduce you to the fabulous SEQUENCE function in Excel along with two ‘real world’ examples of its use.
The SEQUENCE function is a dynamic array function and is only available in Excel 365, Excel 2021 and in Excel Online.
When you first see the SEQUENCE function, it is maybe not that clear how it will come in useful. So, two examples will be provided toward the end of this tutorial, and in the following video, to see some practical uses.
You can download the Excel workbook used in this tutorial to follow along.
In this tutorial, we see how to sort by drop down list in Excel.
We have a table containing details about pizza and their performance such as the feedback rating, how many have been sold and the % change from the previous pizza data.
We want to be able to select the column to sort by from a drop down list, and then the table will be automatically sorted by the chosen column.
Watch the video, or read on for the full tutorial.
The tutorial uses the power of the array engine of Excel to sort by drop down list. However, there is another video at the end of the tutorial that shows how to do this in all versions of Excel.
Download the sample workbook to follow along.
