One of my clients asked me if there was a way to quickly select non-blank cells in an Excel worksheet – similar to the way you would use GoTo->Special->Blank Cells. As I was explaining the VBA technique that would allow him to do this, I could see by his 1000-yard stare that he was allergic to VBA programming. So I gave him my quick and easy shortcut key trick.
Archive for the ‘Spreadsheet Auditing’ Category
GoTo Special Non Blank Cells
June 4th, 2010Highlight Named Ranges Part 2
April 6th, 2010Last week, I posted ‘Highlight All Named Ranges Part 1′, where I shared a macro that will literally color all the cells that belong to any Named Range yellow.
This allows you to get a visual indication of how many Named Ranges exist and where they are.
Highlight All Named Ranges Part 1
April 1st, 2010If you spend your time auditing other people’s worksheets, you’ll know that Excel users love their named ranges. I’ve run across some spreadsheets where 50 or more individual cells are given individual names. This makes auditing a spreadsheet an extremely muddy experience.
It sometimes helps to know where the named ranges are.
» More: Highlight All Named Ranges Part 1
Leveraging the Edit Directly in Cell Option
August 31st, 2009Every now and then, I find myself in trying to decipher someone else’s spreadsheet. If the spreadsheet is complicated enough, the first thing I do is turn off the ‘Edit Directly in Cell’ Option.
Turning off the ‘Edit Directly in Cell’ option tells Excel to allow the editing of a formula in the formula bar only. Although this may seem like an odd thing to do, there are peripheral benefits that make this technique a useful auditing tool. Here’s what I mean:
Array Formula Heresy
July 22nd, 2009If you want to commit Excel heresy, tell people you don’t like Array formulas. Oh the blasphemy – the sacrilege!
The truth is I really do try to avoid using array formulas when there is a reasonable non-array alternative. The long-and-short of it is that I think array formulas are difficult to understand and too easy to break.
I’m man enough to admit that at least part of the reason I avoid array formulas is because I’m not smart enough to understand all of the array formulas I see. In fact, I have to wrap duct tape around my head before looking at some of the fancy array creations I see other gurus employ.
Finding Ghost Cells in Excel
July 9th, 2009Here’s some breaking news……Formatting fonts white in order to hide values is not clever. It’s annoying.
Here is an example.
» More: Finding Ghost Cells in Excel
