Workbook & Worksheet Events in Excel VBA

Workbook & Worksheet Events in Excel VBAWhat Are Events? An Event occurs when certain actions are performed by the user or VBA. For example when you open a workbook the Workbook_Open event is triggered. When you (or your VBA) saves a workbook, the Workbook_BeforeSave event is triggered. These events are built into Excel VBA and are executed automatically by Excel. You […]

My 2014 Microsoft MVP Summit Experience

mvp_logoI arrived back from the 2014 Microsoft MVP Summit in Seattle on Sunday a little tired but bounding with excitement for the future of Excel. It was wonderful to meet my Excel Idols – the other Excel MVP’s and wax lyrical about Excel and other geeky topics. Microsoft took great care to make sure the […]

What the CELL!

Excel CELL FunctionExcel’s CELL function doesn’t get a lot of press but it can be handy to know. The CELL Function simply returns information about the formatting, contents or location of a cell. Let’s look at an example: In cell A5 I have a date and in C5 I have my CELL formula =CELL(“format”,A5). The result returned […]

Excel GETPIVOTDATA Function

getpivotdata functionIn its simplest form Excel’s GETPIVOTDATA function enables you to extract values from a PivotTable report, but if you’re like me when you first came across the GETPIVOTDATA function you were less than pleased with the results. Understandably so, because in it’s default form it’s quite inflexible. However, the benefit in using GETPIVOTDATA, as opposed […]

Excel Chart Makeover

Chart MakeoverGetting the formatting in your chart right can make the difference between clear, concise information and just a pretty picture. Take a look at this stacked column chart using some of the built in Excel chart formats: What’s your initial reaction? Here are some of mine: Striking use of colour It looks nice But then […]

Interactive Excel Formulas

Interactive Excel FormulasDynamic formulas are the cornerstone of interactive reports like dashboards. They enable our report recipients to interact with the data, and if set up right they can make our reports quicker and easier to update each month/week etc. With just one data validation list or Combo Box we can control all of the formulas feeding […]

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 […]