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 [...]
Copying and Pasting are two of the most common tasks you’ll perform in Excel. This is especially true for me and I can tell because the shortcut keys (CTRL+C for copy and CTRL+V for paste) on the new keyboard I got just last week are already showing signs of wear. Most of the time I [...]
My second Excel love is PivotTables (VLOOKUP is still my number 1). PivotTables make short work of summarising data and if you change your mind like the wind you can easily satisfy those whims with little more than a drag and a drop, unlike formulas which often require a lot more cajoling. Once you have [...]
Using Conditional Formatting in PivotTables has its ups and downs. Unfortunately I find them mostly ‘downs’ but let’s not dwell on the negative as the few ‘ups’ might still make it worthwhile depending on your needs. The Upside When you apply Conditional Formatting to the Values area of your PivotTable the formatting will automatically expand/contract [...]
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 [...]