Sunday, October 24, 2021

How to create Excel macros and automate your spreadsheets

Excel macros are like mini-programs that perform repetitive tasks, saving you a lot of time and typing. For example, it takes Excel less than one-tenth of a second to calculate an entire, massive spreadsheet. It’s the manual operations that slow you down. That’s why you need macros to combine all of these chores into a single one-second transaction.

Excel macros: Tips for getting started

We’re going to show you how to write your first macro. Once you see how easy it is to automate tasks using macros, you’ll never go back. 

First, some tips on how to prepare your data for macros:

  • Always begin your macro at the Home position (use the key combination Ctrl+ Home to get there quickly).
  • Use the directional keys to navigate: Up, Down, Right, Left, End, Home, etc., and shortcut keys to expedite movement.
  • Keep your macros small and focused on specific tasks. This is best for testing and editing (if needed). You can always combine these mini-macros into one BIG macro later once they’re perfected.
  • Macros require “relative” cell addresses, which means you “point” to the cells rather than hard-code the actual (or “absolute”) cell address (such as A1, B19, C20, etc.) in the macro. Spreadsheets are dynamic, which means they constantly change, which means the cell addresses change.
  • Fixed values and static information such as names, addresses, ID numbers, etc. are generally entered in advance and not really part of your macro. Because this data rarely changes (and if it does, it’s just to add or remove a new record), it’s almost impossible to include this function in a macro.
  • Manage your data first: Add, edit, or delete records, then enter the updated values. Then you can execute your macro.

Why starting with mini-macros is easier

For this example, we have a store owner who has expanded her territory from a single store to a dozen in 12 different major cities. Now the CEO, she’s been managing her own books for years, which wasn’t an easy task for a single store, and now she has 12. She has to collect data from each store and merge it to monitor the health of her entire company.

We created a few mini-macros to perform the following tasks:

  1. Collect and combine the data from her 12 stores into one workbook in a Master three-dimensional spreadsheet.
  2. Organize and sort the data.
  3. Enter the formulas that calculate the combined data.
ALSO READ  7 Interesting Galaxy S20 Settings & What They Mean « Android :: Gadget Hacks

Once the mini-macros are recorded, tested, and perfected, we can merge them into one big macro or leave them as mini-macros. Either way, keep the mini-macros, because it’s much easier and more efficient to edit the smaller macros and re-combine them, than try to step through a long, detailed macro to find errors.

We’ve provided a sample workbook for the above scenario so you can follow along with our how-to. Feel free to create your own spreadsheet too, of course. 


This sample Excel workbook will help you practice creating and using macros. JD Sartain

Prep work: The Master spreadsheet

If you’re building your spreadsheets from the ground up, start with the Master spreadsheet. Enter the date formula in A1 and the store location in B1. See screen shot below.


Leave a Reply

This website uses cookies. By continuing to use this site, you accept our use of cookies.