## Get the file name of the current workbook

There are times in Excel when we may want to display or use the file name of the current workbook, or maybe the name of a specific worksheet.  Whilst there are a variety of options none of them are particularly obvious.  This post will consider some of the possible options: Header/Footer section CELL Function VBA Read More

## Top tips for working with complex formulas

Note: This post was originally only shared with my newsletter subscribers, but I decided it was too good to only share once.   Have you ever had a formula which looks like this? Of course you have, we all have.  Just by looking at the formula you know it is going to take a while Read More

## Change number format based on a cells value

Imagine a situation where you have built a dynamic report or dashboard that you a really proud of.  The user can make selections from a drop-down box and the report updates automatically, it’s a beautiful thing.  There is just one issue with these types of dynamic reports/dashboards – not all the information is of the Read More

## 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

## Quickly find errors in data using a rules matrix

Being able to analyze a lot of data quickly to find potential anomalies and errors is a skill.  We should not assume that the data we use in our spreadsheets is error free.  To make this assumption might be dangerous, especially if we are providing insights and advice based on that data.  But in a Read More

## Using spacer characters in helper columns

What are helper columns? Using helper columns is a very common practice in Excel.  If you’re not familiar with the term, it is a column which stores intermediate results to be used in other calculations.  Look at the example below: If we wanted to VLOOKUP on the Product or the Part Number we could just Read More

## Top 10: Excel, VBA and Dashboard books

If you want to take your Excel, VBA or Dashboard skills to the next level then these 10 books will provide you with the skills you need to become an expert. Complete Excel Reference Sometimes we just need something which is going to give us a really solid understanding of Excel as a whole. [1] Read More