Q: How do you get the PivotTable to show the missing dates in your data? A: Not as easily as it should be, but here are a couple of workarounds you can use. Option 1: If you don’t care how Excel formats your dates The limitation of this option, as you will see, is that [...]
One of the most useful features in Excel are custom number formats yet they remain largely underused. In my Excel TV interview this week I shared a tip (at 52min) which uses Custom Number formats inside the TEXT function, and coincidently Jordan Goldmeier (of Excel blog; Option Explicit VBA) also used Custom Number formats in [...]
Each year our sons choose a cake they want me to make for their birthday. Now, I am no baker and their cake of choice is usually something elaborate they find on the internet, and from there I talk them down to something I think I can manage. Last weekend our eldest son, Conor, turned [...]
If you import data to Excel from another program chances are the dates will come in formatted as text, which means they’re not much use to you in formulas or PivotTables. There are many ways to fix the dates and the method you choose will depend partly on the format they’re in and partly based [...]
They say the true Excel fan has uninstalled Word from their PC, and removed the F1 key from their keyboard (accidently pressing F1 opens ‘Help’ which slows you down). After all, who needs Word when you have everything you need to write a letter right there in Excel? A must know tool for us Excel [...]
Excel Cursor Trick

Curse You Excel Cursor

by on January 30, 2014

in Excel

The Scenario You need to change the range referenced by your chart/conditional formatting/named range so you merrily open the Edit dialog box, like the one below for a chart, and start using your arrow keys to move the cursor into position, except all of a sudden the range referenced starts changing before your eyes. What’s [...]
If you’re frustrated using Excel on a Mac because you don’t know the shortcut keys you’re used to using on a PC, then fret no more. Now you can quickly get up to speed with your Mac keyboard using a KeyCuts Excel keyboard cover. Plus you might learn a few new ones too. It has [...]
Hyperlinks with Buttons

Excel Hyperlink Buttons

by on November 20, 2013

in Excel

I like to use hyperlinks in my Excel workbooks to help with navigation. I link them to a Shape that looks like a button to make it intuitive for the user, and also because I think it looks nicer than a text hyperlink. If I have a lot of worksheets in the file I’ll make [...]
Excel Shortcuts

Favourite Excel Shortcuts

by on November 7, 2013

in Excel

Sometimes it’s the little things that make a big difference so today I’m going to share with you a few of my favourite Excel shortcuts. They’re not necessarily all keyboard shortcuts either. These are the type of skills that you pick up over the years watching your colleagues wield their Excel swords. So, without further [...]
Excel tabular data ideal format

Excel Tabular Data Format

by on October 30, 2013

in Excel

Today I want to share with you an Excel secret. It’s so powerful that when you know this your Excel life will become much easier. I say it’s a secret because I get A LOT of questions from people struggling to use the built in Excel tools like PivotTables, and functions like SUMIFS, COUNTIFS etc. [...]