Daily Dose of Excel

Hi everyone,

I am busy building my very first COM addin for Excel and I’ve now come to the stage that I need some beta testers.

Who would be willing to run some tests on my new “Excel Formula Reference Auditing Utility” (see screenshot below)?

If interested, send me an email:
info@jkp-ads.com

What’s in it for you? a free copy of the tool once the beta is finished.

###EDIT Oct 29, 2007###
I’d like to thank everyone who has volunteered for beta testing. For now, I have sufficient people doing testing, so the subscription is closed.

Regards,

Jan Karel Pieterse
www.jkp-ads.com

I had a worksheet table with blank rows separating the groups.
I needed to add another column - a formula - but wanted to retain the blank rows for formatting tidiness.

The table after adding the formula column:

Notice the formula produces zeros for the blank rows. I could just select each cell (D3, D5, D8, D10) and hit delete, but for thousands of rows that would be time consuming.

Here's how I did it:
- Select column C.
- From the Edit menu, select Go To..., then click Special...
- Select Blanks, then click OK

Then I ran a macro which allows me to move the selection over one column.
In this example, I typed 0, 1 for the Input to SelectionOffset.
After the Selection was moved, I hit the delete key.

The Control+ArrowKey shortcut is my new favorite shortcut. I seem to be using the heck out of it lately.

In Excel (cell navigation), it works like the End key. Control+Down Arrow is the same as End, Down. Add the Shift key for a handy way to select column of data.

In Excel (cell editing), the left and right arrows move to the next word. Find a long formula, press F2 to edit it, and start pressing Control+Left Arrow to move left one "word" at a time. Words are not quite so easy to distinguish as in a text editor, but it does a pretty good job. The up and down arrows don't seem to do anything special. At least not that I can see.

In the VBE, left and right arrows move one word at a time, just like in cell editing. Since the Object.Property.Property type strings have a lot of periods, they make for nice breakpoints. The up and down arrows move one procedure at a time. I used to use Control+PgUp/PgDn, but I like this better. Instead of taking you to the first line of the procedure, which is usually a Sub or a procedure header, this takes you to the first line below the procedure declaration statement (Sub, Function, etc.). Also unlike PgUp/PgDn, this scrolls the cursor to the top of the code window - very handy.

Did you know that Excel's scrollbars had a right click menu? I didn't until just recently. (Hat tip: Shane and JWalk).

I can't think of a use for these menus. My mouse is already down there, so why wouldn't I just left click on the arrow (Scroll Right), left click in the scroll area (Page Right), or drag the scroll bar (Scroll Here). I'm still not sure what Right Edge does.

Does anyone use these?

Sometimes filling a sheet full of data can be slow and painful. It's the navigating between cells that slows me down.
You know how it goes, type the First Name, (arrow right), (arrow right), (arrow right), type the Last Name, (arrow down), (arrow left), (arrow left), (arrow left), type the Occupation, etc...

Maybe you've resorted to using the mouse for field navigation.
Maybe you've even set up sheet protection so the cursor automatically moves between unlocked cells.

Here is another way.

If you have a bunch of cells selected, you can press Enter (or Tab!) to move between them (it cycles through them).
The trick is in the order that you select them.
So if you have 5 cells:
Click Cell 2
While holding down the Ctrl key click Cell 3, Cell 4, Cell 5 then Cell 1

If you want to save that selection for later, make it a Named Range. Excel remembers the order you clicked and saves that as part of the Name.

So Dick is the keyboard guru here, but there's a lot of people that use the mouse a lot. I know I do. And I was reading Jensen Harris' blog today about how a Paste Special operation takes 6 clicks right now, but only 3 in Office 12.

However, there are a number of ways in which you can shorter this operation. One way, is to customize the toolbars, and display the 'Paste Values' button as shown in the screenshot.

The steps required to add this button are:

1. Right click on any toolbar, and click 'Customize'
2. Click on the 'Commands' tab
3. Select the 'Edit' category
4. Scroll down in the commands list until you see the 'Paste Values' button
5. Click and drag this button to the toolbar where you want it.
6. Click on 'Close' and you're done!

This reduces the number of clicks needed to 3 (Select Range, click on Copy, click on Paste Values).

However, there's another method that blows people away each time I use it... it's that cool factor I guess.

Take this data for example, I'm using =RAND(), just like Jensen is.

Now, select the range, right click and hold on one of the borders (*not* in the fill handle), drag the range as if you were going to move it to a different place, and then drag it back to its original position. Just offseting one row or column willl do the job.

When you release the mouse button, a popup menu will appear, giving you some pretty cool options, one of those being 'Copy here as Values only'. Basically doing the Copy and Paste in one single operation.

I guess technically this is still a 3 click operation, but there's a lot less mouse movement involved, and, you can use it to create links or hyperlinks for example.