• Go Ask Debbie

Excel Macros for Productivity

Updated: Jun 24, 2021

#Excel, #ExcelProductivity, #ExcelTraining, #ExcelTutorial, #ExcelMacros

Macros in Excel can tremendously help with your productivity!

Macros, in essence, record steps you would conduct on a regular basis in Excel. And then, Excel performs the entire set of steps when you tell it to.

When I first learned Macros was many years ago when I had to run multiple reports each and every month. It basically turned about 2 weeks worth of work into about 2 days!

And many of my students have had similar results by just learning how to setup and use Excel Macros.

For a full tutorial on how to use Macros in 3 Easy Steps, CLICK HERE.

Lately, I've had quite a few students saying their macros has disappeared. I've had this happen to me over the years as well and found this amazingly helpful tip.

To make sure you always have access to your Macros, copy them to a Personal Macro Workbook.

If you find yourself recreating the same macros, you can copy those macros to a special workbook called Personal.xlsb that is saved on your computer. Any macros that you store in your personal workbook become available to you whenever you start Excel on that same computer.

This is different than Excel's default behavior, where a macro only works in the workbook that contains it.

To create the personal workbook, you first need to create a macro and store it in the Personal Macro Workbook. Before you get started, make sure that the Developer tab is available in the ribbon in Excel.

Create and update the Personal Macro workbook by following these steps:

  • On the Developer tab, in the Code group, click Record Macro.

  • In the Record Macro dialog box, type a meaningful name for the macro in the Macro name box. Make sure you don’t use any spaces in the name.

  • In the Store macro in box, select Personal Macro Workbook.

  • Click OK.

  • Perform the actions that you want to record. (Meaning, perform the steps that you would normally perform that are repetitive. For example, every month, I used to create a report from a data set that would create Pivot Tables, Sum and Average data, and format in a specific manner. All of those things are the actions that I want to record so that I do not have to repeat them each and every month).

  • On the Developer tab, in the Code group, click Stop Recording.

  • Close the workbook. A message appears that prompts you to save the changes that you made to the Personal Macro Workbook.

  • Click Save to save the workbook.

Any time you create a new macro and save it in your personal workbook or update any macros that it contains, you are prompted to save the personal workbook just as it did the first time you saved it.

Next time you need to run a Macro, it is now saved in the Personal Macro Workbook!


For this and many other tips and shortcuts, CLICK HERE to download my 65+ Ways to Use Microsoft Office to Be More Productive!


Recent Posts

See All