Friday, October 26, 2007

My Excel Personal.XLS Macros Template Example

Personal.xls is your repository of macros that is available every time you use excel. Without further ado, I'll present mine. They really aren't innovative, but assign keyboard shortcuts to many common actions. With the goal of using the keyboard as much as possible, this greatly saves time.

My Personal.XLS Workbook

Macros involving number formatting

I've never seen macros that deal with number formatting, yet I find myself formatting numbers ALL THE TIME. The whole point of excel is to see trends in numbers, and the right formatting can hugely help this process. I hate seeing workbooks with calculations on them that have 6 numbers to the right of the decimal point. Who cares? I need to see the bigger picture.

I predominately use three formats - numbers, currency, and percentages. Thus, I set up six macros to quickly format cells, both with no decimal points and with two.

Macros involving cell shading

Inevitably I'm highlighting certain cells to point out something to my job manager. I wrote up two quick shortcuts to color cells one of two colors - light yellow and pumpkin.

Macros involving column titles

Whenever you're working with data you're inevitably titling columns of data. I realized that I might as well have a common single way of formatting titles instead of changing it every single time I do a new workbook. Thus, I've created a title macro, Ctrl+t. It
  • Centers the cell
  • Bolds the cell
  • Puts a thin black line on the bottom border of the cell.

A second macro relating to titling is merging cells together. Excel has a nice toolbar button that does this but there is no keyboard shortcut. Ctrl+j will
  • Merge the cells together
  • Vertically & horizontally center them
Keyboard shortcut macro for the excel AutoFill function

Excel has a nice function you can use with the mouse, AutoFill. Say you have a set of data, say a list of 10 numbers in a column. To the right of the first row you type in a formula. You want the formula to be copied down. You can copy the cell and paste it down, but that's several steps. You can, instead, move your cursor to the lower right corner of the cell until it turns into a cross, and then double click. Excel will automatically copy the cell down as far as the left column does.

This is a really nice function, but there's no keyboard shortcut. Thus, I whipped up Ctrl+d to mimic this function.

Adding table borders - Ctrl+l

Bordering a set of data gives a little visual cue that typically helps data be picked up quickly. The format that I use is one that simulates a 3-dimensional setting, placing the table above the Excel spreadsheet tableau. It puts a thin black line on the up and left sides of the selection, and a thicker black line on the bottom right.

Adding labels to Excel charts in-line with the data - Ctrl+Shift+l

I copied this macro from somewhere else a while back but from whom I simply can't remember, which takes the legend description of each series, and applies it as a data label to the last data set in a series. It's a far superior way to label data.

How legend data shows up now.

What changes when you run this macro.

Exporting Charts and Text to a GIF - Ctrl + Shift + k

I've detailed this macro on this post about how to export excel objects to a gif, and added the macro to my macros template. For posting stuff to the web, there's no better way.

Disabling the F1 Key - Invoked on Startup

In this post I explain why and how to disable the F1 key. By putting it in my PERSONAL.xls file it runs at startup.


Zach said...

This is a great idea. With a little attention to common tasks, anyone could put together a nice stack of time-saving shortcuts.

We also advocate using keyboard-based menu navigation (i.e. Alt-letter-letter...) to get around quickly in Excel. While this does take more keystrokes, it ultimately gives you the ability to twiddle any feature without moving your hands off the keyboard.

Anonymous said...

ALT-e-i-? is auto-fill

Matt Hodan said...

Nice macro. I've created something similar here:

Good idea with the disable F1 key.


Anonymous said...

I am not able to download or see the file. Can you repost it? The highlighting function would be a godsend

Consultant Ninja said...

I took it down to make some modifications and updates. You can download it now.

The auto-fill that I've developed, January 3rd, is a lot faster & smarter than Alt+e+i. That requires that you highlight all the cells that you want to fill down to. Well that's the most time consuming point.

I also changed the number of digits in #s and %s to be just 1 past the decimal. I found that most of my analysis didn't need to go past 1.

John said...

this is great since i lost my fast track macros. question - can you get the macros to load without having to open the workbook all the time (have them on by default somehow?) thanks

Consultant Ninja said...

Check here to see how to install this by default.

Rename it to personal.xls. When you run excel it will show up. Go to window->hide, close excel, save the changes, and restart. the macros work then.

Anonymous said...

arigato ninja, you slay me with your macro collection, thank you for the chart relabeler.

Anonymous said...
This comment has been removed by a blog administrator.

Friday, October 26, 2007