Text to Columns with an Excel Formula

text_to_columns_formulas_thumbIf you reformat data brought into Excel from an external source regularly then you’ve most likely come across Excel’s Text to Columns tool. You can also reformat text using formulas like MID, SEARCH, LEFT and RIGHT to name a few but this can be tedious and hurt your head If you perform the same head […]

Excel Table Absolute Structured References

tbl_abs_ref_thumbExcel Tables are one of my favourite features. They make working in Excel much more efficient in so many ways, but they have some quirky (read cool) features, one of which is the Structured References they use in formulas. And if you’ve ever tried to write a formula that references an Excel Table then you […]

I’ll Have a Slicer That!

Not cake, sorry. I’m talking about Excel Slicers and while they are ‘sweeeet’, as my 8 year old says, and a piece of cake to use, they aren’t quite as tasty. Slicers were introduced in Excel 2010 and they’re an interactive control that enables you to filter data in PivotTables, PivotCharts, Excel Tables and CUBE […]

Excel Formula to Spread Income or Costs Over Months

WARNING: this tutorial contains some advanced formula concepts which may incur the following side effects: Negative side effects: Headache, urge to bang head on desk, fidgeting in seat, urge to look for distractions like e-mail to give your brain a rest. Positive side effects: Jumping up and cheering out loud when you realise you cracked […]

Sorting Excel Date Slicers

Slicers are an amazing new feature introduced in Excel 2010. I used them to build these 3 interactive dashboards on Olympic data from 1896 to 2012: By the way, I teach you how to build dashboards like this in my Excel Dashboard course. While Slicers are a great addition to Excel, they unfortunately have some […]

Fix Excel Chart Axis with a Ghost Series

Below is a set of Panel Charts. In this case they’re actually 4 separate charts aligned close together. Comparing one chart to the next isn’t easy since each chart uses a different vertical axis scale: If we could put all charts on the same axis then we could remove the 3 superfluous axes and ensure […]

3 Steps to Easy Bullet Graphs in Excel

Bullet graphs were developed by data visualisation expert Stephen Few to address the need for visually rich displays of data in small spaces which are typical of a dashboard report. They enable you to compare your actual measure to a target/budget and also a qualitative range as denoted by the background fill. While Excel is […]

Excel Protect Ranges with Different Passwords

Did you know you can set different passwords to protect specific ranges of your Excel worksheets? It’s called Allow Users to Edit Ranges and you’ll find it on the Review tab in the Changes group: Let’s say you have several different users of a workbook and you want to restrict each user’s editing rights to […]

Protect and Unprotect All Sheets in a Workbook

I recently had a client who has a requirement to protect and unprotect a lot of sheets. This was something they didn’t do very often, but when they did, they described the process of doing it manually as cumbersome. I’m sure that once you get more than a few sheets, protecting and unprotecting each one […]