5 Groovy SUMPRODUCT Examples – Advanced Excel Formula Tutorials

In this blog post we look at 5 groovy examples of the SUMPRODUCT function. This is one of the great functions of Excel. A function that can turn you from being an Intermediate/Advanced Excel user to an Excel guru instantly.

The SUMPRODUCT function is powerful, versatile and expansive. It is the go to function when looking for an alternative to array formulas.

If you are a newbie – check out our SUMPRODUCT function guide to get an understanding of how it works.

Ok, are you ready to rock on with these 5 awesome SUMPRODUCT examples?

Let’s do this.

If you prefer to watch videos, check out this video covering the tutorials from this blog post.

Continue reading

5 Excel INDIRECT Function Examples

In this blog post we look at 5 examples of the INDIRECT function of Excel. This is a very misunderstood function, which can be incredibly useful and powerful.

Prefer to watch the video? The video tutorial below will demonstrate all 5 INDIRECT function examples.

Continue reading

Create a Searchable Drop Down List Just Like Google Search – Excel Trick

This blog post will take you through the steps to create a searchable drop down list in Excel – just like Google search. This is a great Excel trick for working with large drop down lists.

In this tutorial we will use a list of 87 names that as we type into the drop down list, it searches the names, and the list shortens to show only those names containing that string of characters.

searchable drop down list in Excel

There are a few formulas to write to get this done. Everything is shown and provided in this tutorial. If you prefer a video. Check out the video tutorial below.

Watch the Video

Continue reading

Use Own Icon for Macro Button on Ribbon

In this blog post we look at how you can use your own icon or image for the button of your macro on the Ribbon. This could be an image that you have created, or one that you downloaded from a website.

By using our own icons we can create customised workbooks and not be constrained by the standard Microsoft icon set provided.

In this example I have a macro that exports all the worksheets of a workbook to PDF, and I want to assign it to the icon shown below. This icon was downloaded from flaticon.com, a fantastic resource for icons that you can use for your macros.
Custom icon for macro button Continue reading

Excel Formula to Extract Postcode from an Address

I was asked recently in class how to extract a postcode from an address in the UK. The person asking needed a formula because the spreadsheet updates often and they wanted an automated solution.

The problem with extracting UK postcodes is that they are highly irregular. They will be at the end of the full address and can come in a different number of characters e.g. E1 6AX, RM3 8HN and LE41 8JX.

They are not as structured as a US zip code may be and harder to extract. Because of this the formula is intense, but I am going to break it down and explain it in detail.

Extract postcode from UK address with Excel formula

Continue reading

Excel Formula to Find the Least Frequent Value

This tutorial explains an Excel formula to find the least frequent value in a list. This formula will work whether the value is a number, or text. In this example we want to return the name that occurs the least.

The spreadsheet below shows a list of names with the answer in cell D2. Ross is the name that occurs the least in that list.

Excel formula for least frequent value

This formula returns the least frequent value from the list in A2:A16. The formula is explained below so keep reading.

{=INDEX(A2:A16,MATCH(MIN(COUNTIF(A2:A16,A2:A16)),COUNTIF(A2:A16,A2:A16),0))}

Continue reading

Create a Rolling Chart for Last 6 Months

When creating reports in Excel, a common requirement is to report on a rolling basis. For example, this could mean the last 12 months, the last 6 weeks or the last 7 days.

Whatever the timeframe being reported, this can mean a lot of time editing chart sources and formulas to show the right data.

This blog post looks at creating a dynamic rolling chart to show the last 6 months of data, so when new data is added to the table, the chart automatically updates to report the last 6 rows (months).

Rolling chart showing last 6 months sales

Continue reading

Euro 2016 Planner – Excel Template

Euro 2016 is here and I have a Euro 2016 planner to share. The spreadsheet has a separate sheet for the group stage and the knockout stage. Enter the results into the boxes on the sheets and everything is calculated automatically.

The group league tables are ranked as the results are entered. As soon as a groups fixtures have been completed, the knockout schedule is created. The quarters, semi-final and final fixtures are then calculated as these results are entered.

The spreadsheet is unlocked so you are free to check out the formulas and how it all works. Download the Euro 2016 planner spreadsheet.

Euro 2016 Planner Template
Continue reading

Highlight Max and Min Values on a Column Chart

When using column charts to compare values, you may want to highlight the maximum and minimum values on the chart. By highlighting these columns it removes any confusion when trying to view the top and bottom values.

Highlighted max and min values

Finding the Max and Min Values

To show the max and min values on a column chart, we will first need to identify the max and min values of our range. These values will then be used as a second data series when we create the column chart. Continue reading

Use VLOOKUP to Find the Last Match in a List

VLOOKUP is an awesome Excel function. We all know this. But it certainly has its limitations. One of these is that it can only return the first match from a list.

This is great when looking for a unique value. But what about when the value you are looking for appears multiple times in the list, and you want to return the last match.

Sure we could sort the list so that the last match would become the first, but this is not always an option.

This blog post looks at using the VLOOKUP function to return the last match in a list. The technique and formulas used can be adapted to find the 2nd or 3rd match if required.

VLOOKUP to find last match

Continue reading