How to create Excel macros and automate your spreadsheets
Use macros to combine multiple tasks into a single transaction.
Excel macros are like miniprograms 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 MINIMACROS 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.
Once the mini-macros are recorded, tested, and perfected, we can merge them into one big macro or leave them as minimacros. 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 ( go.pcworld. com/wkbk).
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 opposite.
1. Enter this date formula in cell A1: =Today(). Now this cell always displays today’s date. Be sure; however, that your store location (branch name and number) are entered in B1.
2. Leave row 2 blank. Once the static data and initial dynamic data are entered, we’ll use row 2 for the totals. This might seem like a strange custom, but for macro spreadsheets, it’s the best way because this row is stationary and always visible.
3. Next, enter the field names (and/or any other field-specific information) in row 3 (e.g., from A3 through J3, or however many fields your spreadsheet requires).
Tip: You can text-wrap the information in the individual cells if the data is lengthy. For example, you can put the store contact information all in one cell and wrap the lines. Press Alt+ Enter to insert extra lines in the cells.
4. Next, enter the static data in column A. That is the record information in your spreadsheet that rarely changes. If your business uses product numbers or ID codes, which are unique because there is only one code per product, enter those in column A beginning on row 4 (don’t skip to row 5). Other static data fields might include the Product
Description, the Product Price, sales tax percentage, etc.
Do not skip rows or leave any rows blank for column A. Every row must contain the unique field’s data—if not a product code, then some other unique identifier. We do this for two reasons:
• Column A is the main navigational column. The macro moves and navigates through the spreadsheet based on the Home (A1) position and column A. The macro will fail if you ignore this rule, because blank rows disrupt the actions of the directional keys.
• If you decide to create multiple/ relational tables later for Pivot Reports, you must have a unique, key field to connect the related tables. Check out our Excel pivot tables tutorial for more information ( go.
pcworld.com/pivt).
5. Normally, the Product Description resides in column B, the Quantity Sold in column C, Product Price in column D, Extended Cost in E, Discounts in F, Sales Tax in G, and Totals in H. The column totals are across the top on row 2, remember? Format the column widths based on the length of the field names, and adjust the row height to 20 on all rows. Change the Top/bottom alignment to Center, select the justification you prefer (left, right, center), and then format the spreadsheet “styles” to your preference.
6. Once the master database is set up, do not move anything. If you need to add fields, use the Insert Column command. For example, if you wanted to add a second sales tax, position your cursor anywhere on column H (Totals) and click the tab: Home > Insert > Insert Sheet Columns. The new column drops in to become the new H column, and the Totals column moves over to I. This process does not affect the macro.
7. The same process applies to rows. Normally I would caution you to insert rows “inside” the active database area. For example, if the formula says =SUM(B3:B20) and you insert or use a row outside of the formula’s range like B21, the new record’s data is not included in the formula and therefore, does not calculate.
8. Now we’ll set up that formula range. Enter the following formulas on row 2 (this is a one-time task):
C2: =SUM(C4:C500)
E2: =SUM(E4:E500)
F2: =SUM(F4:F500)
G2: =SUM(G4:G500)
H2: =SUM(H4:H500)
Next, enter the following formulas in these columns (also a one-time event):
E4: =SUM(C4*D4), then copy from E4 down to E5:E500
F4: =SUM(E4*10%), the current discount percentage in your store, then copy from F4 down to F5:E500
G4: =SUM(E4-F4)*6.25, where 6.25 is the sales tax in your area, then copy from G4 down to G5:G500
H4: =SUM(E4-F4+G4), then copy from H4 down to H5:E500
Now that you have all the spreadsheet formulas in place, all you have to do is enter the quantity (column C) for each computer sold (daily, weekly, or monthly). If the prices change, enter the new prices in column D. The rest of this database is all formulas or static information.
9. As seen above, with “macro” spreadsheets, you set the formula range to be many rows beyond the last record, so you can just add new records at the end and not worry about adjusting the range. Because the macro sorts the database, the new records are relocated to the proper position. The spreadsheet data in our example ends on row 210. The formula range extends out to row 500, so it’s safe to add the next new record on row 211.
10. Once the spreadsheet is defined and set up with the structure, static data in place, and correct formulas, make 12 copies in worksheets 2 through 13. Edit the tabs on the bottom to identify the individual stores. Change the name of the sheet1 tab to Master, because this is your master database file.
11. Change the location data on row
1 to identify the store information (that matches the store on the tab) on all 12 spreadsheets. Next, email an electronic copy of each branches’ spreadsheet to each of the store managers; for example, send the Boston sheet to Boston, the
Dallas sheet to Dallas, etc.
Their copies include the spreadsheet formulas that work on their individual spreadsheets (but not the formulas of the combined spreadsheets in the workbook).
12. The macro provides the formulas for