Kusleika and I are at the Excel and Access Power User Workshop this week. Between you and me, he’s not doing that well.
Anyway, one of our attendees asked why Access 2007 can’t export more than 65,000 rows to Excel 2007. I replied with my usual professorial demeanor “What? You’re on crack!”
This is when he demonstrated these steps:
When you insert a comment into Excel, your username is automatically inserted into the comment followed by a colon. For instance, if you log into your computer using the user name TrunkOfFunk. Your comments will all start with TrunkOfFunk:.
.
This annoys me to no end. Theoretically, this "feature" is supposed to tell you who inserted the comments. I don't think I have ever needed to know that Donna inserted a comment into cell B2. In my opinion, the user name just clutters up the comment.
.
If you share my distain for the forced username in comments, here is a small procedure that will go through all comments and remove the username. This is especially handy for those of you who have embarrasing user names like Lou Briccant.
A week ago, Dick Kusleika posted an excellent article outlining some Formula Tips. I've already made plans to steal and use his ideas in my training. But my plagiaristic habits are not the reason for this post.
?
.
» More: Excel Defunct Defaults
I was fiddling around with Custom Lists the other day, when I noticed that Microsoft prevents me from deleting any of their built-in Custom Lists.
Don't believe me? Just pull up the Custom Lists dialog box and try to delete that lame Sun – Sat list. You can't do it. The Delete button is disabled like this:
» More: Fun with Custom Lists
Excel Annoyance #119: Sticky Click.
Every so often, my Excel gets into a state where no matter where or how I click, it highlights a range. That is to say, it anchors the current cell, then highlights a contiguous range from the anchored cell to wherever I'm clicking next. I call this Sticky Click.
There's an old saying that goes "You learn something new every day". That's fine, but it's embarrassing when what you learned that day is something that you should have learned years ago.
I recently was working with Access when I ran into a situation I've never run into before. As I did some research, it became clear that this "problem" I was having seemed to be common knowledge.
It revolved around special characters. Since we all have things to do today, I'll simplify the scenario to a basic example.
Most of us know that you can build an Access macro by selecting actions, and defining arguments for those actions.
This in itself is no big deal, but when you have many actions to add, it can be quite tedious.
Here's what I mean:
If 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.
We all know that you can add commands to the Quick Access Toolbar in both Excel and Access 2007.
This will activate a dialog box that allows you to select commands to add to the Quick Access Toolbar.
I was perusing all the commands that are "Not in the Ribbon" when I found two of the most useless commands I can think of.
Here'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
