Excel Power Pivot Named Sets

Power Pivot Named SetsExcel PivotTable Named Sets enable you to pick and choose which columns or rows you want included in your PivotTable report, but there’s a catch. They require you to use Power Pivot to create your PivotTable. Don’t worry, it’s so easy you won’t even realise you’re using Power Pivot. Note: Named Sets are only available […]

Change the Color of Words in Text

Change the color of words in textOne of our students (hi Leah ) sent me a question last week asking how she could change the color of words (sub-strings) in text. She was trying to visually indicate where words were on the sheet. She supplied some code that she got somewhere on the net. I’m not sure where from so if […]

Excel Custom Data Validation to Limit Entries

Excel Custom Data ValidationIn this tutorial we’re going to look at how Santa is using Excel Custom Data Validation to limit the number of presents kids request on their Christmas wish list. With Christmas fast approaching our kids are already talking about what Santa might bring them this year. Little do they know that Santa will be implementing […]

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!

Excel SlicersNot 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

Excel Formula to Spread Income or Costs Over MonthsWARNING: 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 […]