## Cell ranges: basic things 99% of users don’t know

I have never been taught how to use ranges in Excel, I doubt you have either.  They are just too basic for anybody to teach, right?  Select some cells and there is a : (colon) symbol placed between the start cell and the end cell.  Maybe we could throw in a few \$ (dollar signs) Read More

## Using the FORECAST function with seasonality

In my opinion, one of the best functions in Excel is FORECAST.  We can feed this function values from the past and it will use linear regression to forecast a value for a future point.  We have seen this function previously when we considered how to interpolate a value for existing data.  As we saw Read More

## Interpolate values in Excel with the FORECAST function

Recently, I received the following question from a reader: “I have an Excel question – Is there a way to interpolate a value from a table?  I have an X and Y that are not on the table, but have correlated data so want to calculate the interpolated value”. At first, I thought I could Read More

## Creating a top 10 using formulas

Creating a Top 10 is quite common when reporting information, especially on dashboards.  It is easiest when working with sorted data, we just select the Top 10 items from the list.  It is also relatively easy when using Auto Filter, Tables and Pivot Tables, as it is a default filter setting within these Excel features. Read More

## Advanced VLOOKUP Cheat Sheet

We have now reached the end of the Mastering VLOOKUP series.  Hopefully, you’ve picked up a few tips and tricks to take your VLOOKUP skills to the next level.  If you’ve been following along you will have learned more than just VLOOKUP: Faster calculation from an approximate match – this also applies to HLOOKUP and Read More

## VLOOKUP: List all the matching items

In the previous post in this series, Mastering VLOOKUP, we learned how to look up the 2nd, 3rd, 4th or nth item from a data set.  Today, we will be expanding on that by looking at how to return all the matching values in the dataset.  Plus, we will combine this with partial matches, so Read More

## VLOOKUP: Lookup the nth item (without helper columns)

In this series, Mastering VLOOKUP we have covered a lot of ground.  But this post is one of the most fiendish of all.  VLOOKUP, by its design, is intended to return the first match it finds.  But, if we want the 2nd, 3rd or nth VLOOKUP it not so easy.  Often people resort to helper Read More

## Automatically expand the VLOOKUP data range

The 2nd argument in the VLOOKUP function is the table_array.  This is the range of cells which contains the data to be used within the VLOOKUP function.    For example, in the code below A1:C9 is the table_array. =VLOOKUP(“O Redding”,A1:C9,3,FALSE) When using the VLOOKUP function, we often know how many rows there are in the data, Read More

## How to use VLOOKUP with columns and rows

There are times when our data is laid out in columns and rows.  In these circumstances, we may need to look up to both the column and the row at the same time, such as in the data below. But how can we achieve this?   VLOOKUP & MATCH By combining the VLOOKUP function with Read More

## Using wildcards with VLOOKUP

Have you ever played Scrabble?  It is the game where you receive points for constructing words from letters printed on small plastic tiles.  In my opinion, the best tile to get is always the blank tile.  It doesn’t score any points, but it can be used to represent any letter at all.  Therefore, if you Read More