Get file name of current workbook thumb

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

Change number format based on value

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

Create top 10 using formulas Thumb

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

Data-errors-rules-matrix-thumb

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-character-thumb

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

top10-excel-vba-dashboard-books

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