Daily Dose of Excel

Mike Alexander has updated his table driven Ribbon customizer. You can find it here:

http://www.datapigtechnologies.com/Custom_UI_Builder.zip

He says:

The utility now creates and adds a module with all the call-back functions to the newly created buttons, allowing the buttons to work right away.

As always, the source code is open for anyone who wants to build on this and create a better table-driven Ribbon Customizer.

If you try it out, post your comments here.

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.

Some toolbar popups can be undocked and made to be their own toolbar. This is useful if you need temporary, but constant, access to certain tools. To undock, click on the bar that runs across the top of the popup.

Such as a shapes popup from the Drawing toolbar

Or the borders popup from the Formatting toolbar

Brett points out that Excel has a rudimentary icon editor. Right click on a toolbar button and choose Customize to enter Customize Mode. Then right click on the button again and choose Edit Button Image…

You get a 16 x 16 grid in which you can create your own icon. Here’s one that I created for my dictionary add-in. It’s supposed to be a book with the world behind it.

If you use this method to create your own icons, take a look at how some of the built-in icons look in this editor. It will give you some ideas on how to create yours.