Microsoft Word and Excel: Make Your Own Office Add-On
By Dennis O’Reilly
The right add-on program can mean the difference between loving and hating an application, but you don’t always need a separate utility to rejuvenate your software. In fact, if you know about macros, you can use them to automate and customize your tasks.
My favorite Microsoft Office enhancer is a simple autoformat macro routine for Word and Excel. (Click here for a more detailed look at Office macros.)
I’ll start with the Microsoft Word macro. The first thing that I do after opening somebody else’s Word file is change its loopy font and format to the settings I prefer. But instead of going through the same series of menu clicks every time, I reformat the file with one click of a button or with a keyboard shortcut.
My Word autoformat macro has four steps (your macro and menu selections will vary):
Press <Ctrl>-A to select the entire document.
Click Edit, Clear, Formats to remove the document’s existing formatting. This way the file won’t revert to 9-point Bookman Old Style (or whatever the original font was) when you enter text on a new line, for example.
Choose File, Page Setup, Margins and enter 2.5 under the Right setting to move the right margin in 1 inch.
Select Format, Font, click Arial in the Font list, choose 12 in the Size list,and click OK.
If you want to create a macro in Microsoft Excel, you’ll need to follow one extra step:
Enter <Ctrl>-A to select the entire workbook.
Choose Edit, Clear, Formats to reset the file’s existing formatting (this avoids reverting to the original format when entering new data).
Click Format, Cells, Alignment and check Wrap text.
Select the Font tab in the Format Cells dialog box, choose Arial under Font, and click 12 under Size.
Choose the Border tab, select the Outline icon, and click OK.
To record a macro, open Word or Excel and click Tools, Macro, Record New Macro. In the Macro name box, enter a name, but don’t use spaces and don’t start with a number. (I call my Word autoformat macro “msformat1,” and my Excel macro “chartformat1.”) Under ‘Store macro in’, select All Documents (Normal.dot) in Word or Personal Macro Workbook in Excel to make the macro available in all the files you open.
If you want to create a toolbar button for the macro or give it a keyboard shortcut, now’s the time. Click the Toolbar button to open the Customize dialog box. Choose Commands, and drag the macro you just named to any toolbar. Assign an icon to your macro shortcut to make it pop: Click Modify Selection, Change Button Image and choose an icon (see the accompanying screen shot).
To activate the macro with a keyboard shortcut, click the Keyboard button, make sure your new macro is selected under Commands, and click in the ‘Press new shortcut key’ box. Enter your preferred key sequence (I chose <Alt>-<Shift>-V) and choose the Assign button. Click Close twice to set the macro recorder in motion.
A cassette-tape icon (or is it an unhappy robot face?) and the Macro Recorder toolbar appear. Step through this reformat macro routine, and when you’re done, click the Stop button on the Macro Recorder toolbar. Your macro is now ready to use: Click the icon on the toolbar, or type the keyboard shortcut you assigned to it.
Note that when you close the workbook in Excel, you’ll be asked if you want to save the changes you made to your Personal Macro Workbook to make it available the next time you open Excel. Click Yes.
Note: When you purchase something after clicking links in our articles, we may earn a small commission. Read ouraffiliate link policyfor more details.