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
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.
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.
