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