How to cre­ate Ex­cel macros and au­to­mate your spread­sheets

Use macros to com­bine mul­ti­ple tasks into a single, one-sec­ond trans­ac­tion.

PCWorld (USA) - - Here’s How - BY JD SARTAIN

Ex­cel macros are like minipro­grams that per­form repet­i­tive tasks, sav­ing you a lot of time and typ­ing. For ex­am­ple, it takes Ex­cel less than one-tenth of a sec­ond to cal­cu­late an en­tire, mas­sive spread­sheet. Man­ual op­er­a­tions slow you down, so in­stead use macros to com­bine all of these chores into a single one-sec­ond trans­ac­tion.

EX­CEL MACROS: TIPS FOR GET­TING STARTED

We’re go­ing to show you how to write your first macro. Once you see how easy it is to au­to­mate tasks us­ing macros, you’ll never go back. First, some tips on how to pre­pare your data for macros:

• Al­ways be­gin your macro at the

Home po­si­tion (use the key com­bi­na­tion

Ctrl+home to get there quickly).

• Use the di­rec­tional keys to nav­i­gate: Up, Down, Right, Left, End, Home, etc., and short­cut keys to ex­pe­dite move­ment.

• Keep your macros small and fo­cused on spe­cific tasks. This is best for test­ing and edit­ing (if needed). You can al­ways com­bine these mini-macros into one BIG macro later once they’re per­fected.

• Macros re­quire “rel­a­tive” cell ad­dresses, which means you “point” to the cells rather than hard-code the ac­tual (or “ab­so­lute”) cell ad­dress (such as A1, B19, C20, etc.) in the macro. Spread­sheets are dy­namic, which means they con­stantly change, which means the cell ad­dresses change.

• Fixed val­ues and static in­for­ma­tion such as names, ad­dresses, ID num­bers, etc., are gen­er­ally en­tered in ad­vance and not re­ally part of your macro. Be­cause this data rarely changes (and if it does, it’s just to add or re­move a new record), it’s al­most im­pos­si­ble to in­clude this func­tion in a macro.

• Man­age your data first: Add, edit, or delete records, then en­ter the up­dated val­ues. Then you can ex­e­cute your macro.

WHY START­ING WITH MINIMACROS IS EAS­IER

For this ex­am­ple, we have a store owner who has ex­panded her ter­ri­tory from a single store to a dozen in 12 dif­fer­ent ma­jor cities. Now the CEO, she’s been manag­ing her own books for years, which wasn’t an easy task for a single store, and now she has 12. She has to col­lect data from each store and merge it to mon­i­tor the health of her en­tire com­pany.

We cre­ated a few mini-macros to per­form the fol­low­ing tasks:

1. Col­lect and com­bine the data from her 12 stores into one work­book in a Mas­ter three-di­men­sional spread­sheet.

2. Or­ga­nize and sort the data.

3. En­ter the for­mu­las that cal­cu­late the com­bined data.

Once the mini-macros are recorded, tested, and per­fected, we can merge them into one big macro or leave them as minimacros. Ei­ther way, keep the mini-macros, be­cause it’s much eas­ier and more ef­fi­cient to edit the smaller macros and re-com­bine them than it is to step through a long, de­tailed macro to find er­rors.

We’ve pro­vided a sam­ple work­book for the above sce­nario so you can fol­low along with our how-to. Feel free to cre­ate your own spread­sheet too, of course ( go.pcworld. com/mcwb).

PREP WORK: THE MAS­TER SPREAD­SHEET

If you’re build­ing your spread­sheets from the ground up, start with the Mas­ter spread­sheet. En­ter the date for­mula in A1 and the store lo­ca­tion in B1. See the screen shot on the fol­low­ing page.

1. En­ter this date for­mula in cell A1: =To­day(). Now this cell al­ways dis­plays to­day’s date. Be sure; how­ever, that your store lo­ca­tion (branch name and num­ber) are en­tered in B1.

2. Leave row 2 blank. Once the static data and ini­tial dy­namic data are en­tered, we’ll use row 2 for the to­tals. This might seem like a strange cus­tom, but for macro spread­sheets, it’s the best way be­cause this row is sta­tion­ary and al­ways vis­i­ble.

3. Next, en­ter the field names (and/or any other field-spe­cific in­for­ma­tion) in row 3 (e.g., from A3 through J3, or how­ever many fields your spread­sheet re­quires).

Tip: You can text-wrap the in­for­ma­tion in the in­di­vid­ual cells if the data is lengthy. For ex­am­ple, you can put the store con­tact in­for­ma­tion all in one cell and wrap the lines. Press Alt+en­ter to in­sert ex­tra lines in the cells.

4. Next, en­ter the static data in col­umn A. That is the record in­for­ma­tion in your spread­sheet that rarely changes. If your busi­ness uses prod­uct num­bers or ID codes, which are unique be­cause there is only one code per prod­uct, en­ter those in col­umn A be­gin­ning on row 4 (don’t skip to row 5). Other static data fields might in­clude the Prod­uct De­scrip­tion, the Prod­uct Price, sales tax per­cent­age, etc.

Do not skip rows or leave any rows blank for col­umn A. Ev­ery row must con­tain the unique field’s data—if not a prod­uct code, then some other unique iden­ti­fier. We do this for two rea­sons:

• Col­umn A is the main nav­i­ga­tional col­umn. The macro moves and nav­i­gates through the spread­sheet based on the Home (A1) po­si­tion and col­umn A. The macro will fail if you ig­nore this rule, be­cause blank rows dis­rupt the ac­tions of the di­rec­tional keys.

• If you de­cide to cre­ate mul­ti­ple/ re­la­tional ta­bles later for Pivot Re­ports, you must have a unique, key field to con­nect the re­lated ta­bles. Check out our Ex­cel pivot ta­bles tu­to­rial for more in­for­ma­tion ( go.

pcworld.com/xlrp).

5. Nor­mally, the Prod­uct De­scrip­tion re­sides in col­umn B, the Quan­tity Sold in col­umn C, Prod­uct Price in col­umn D, Ex­tended Cost in E, Dis­counts in F, Sales Tax in G, and To­tals in H. The col­umn to­tals are across the top on row 2, re­mem­ber? For­mat the col­umn widths based on the length of the field names, and ad­just the row height to 20 on all rows. Change the Top/bot­tom align­ment to Cen­ter, se­lect the jus­ti­fi­ca­tion you pre­fer (left, right, cen­ter), and then for­mat the spread­sheet “styles” to your pref­er­ence.

6. Once the mas­ter data­base is set up, do not move any­thing. If you need to add fields, use the In­sert Col­umn com­mand. For ex­am­ple, if you wanted to add a sec­ond sales tax, po­si­tion your cursor any­where on col­umn H (To­tals) and click the tab: Home > In­sert > In­sert Sheet Col­umns. The new col­umn drops in to be­come the new H col­umn, and the To­tals col­umn moves over to I. This process does not af­fect the macro.

7. The same process ap­plies to rows. Nor­mally I would cau­tion you to in­sert rows “in­side” the ac­tive data­base area. For ex­am­ple, if the for­mula says =SUM(B3:B20) and you in­sert or use a row out­side of the for­mula’s range like B21, the new record’s data is not in­cluded in the for­mula and there­fore, does not cal­cu­late.

8. Now we’ll set up that for­mula range. En­ter the fol­low­ing for­mu­las 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, en­ter the fol­low­ing for­mu­las in these col­umns (also a one-time event):

E4: =SUM(C4*D4), then copy from E4 down to E5:E500

F4: =SUM(E4*10%), the cur­rent dis­count per­cent­age 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 spread­sheet for­mu­las in place, all you have to do is en­ter the quan­tity (col­umn C) for each com­puter sold (daily, weekly, or monthly). If the prices change, en­ter the new prices in col­umn D. The rest of this data­base is all for­mu­las or static in­for­ma­tion.

9. As seen above, with “macro” spread­sheets, you set the for­mula range to be many rows be­yond the last record, so you can just add new records at the end and not worry about ad­just­ing the range. Be­cause the macro sorts the data­base, the new records are re­lo­cated to the proper po­si­tion. The spread­sheet data in our ex­am­ple ends on row 210. The for­mula range ex­tends out to row 500, so it’s safe to add the next new record on row 211.

10. Once the spread­sheet is de­fined and set up with the struc­ture, static data in place, and cor­rect for­mu­las, make 12 copies in work­sheets 2 through 13. Edit the tabs on the bot­tom to iden­tify the in­di­vid­ual stores. Change the name of the sheet1 tab to Mas­ter, be­cause this is your mas­ter data­base file.

11. Change the lo­ca­tion data on row 1 to iden­tify the store in­for­ma­tion (that matches the store on the tab) on all 12 spread­sheets. Next, email an elec­tronic copy of each branches’ spread­sheet to each of the store man­agers; for ex­am­ple, send the Bos­ton sheet to Bos­ton, the Dal­las sheet to Dal­las, etc.

Their copies in­clude the spread­sheet for­mu­las that work on their in­di­vid­ual spread­sheets (but not the for­mu­las of the com­bined spread­sheets in the work­book).

12. The macro pro­vides the for­mu­las for the Mas­ter. The Mas­ter is the spread­sheet for the com­bined to­tals of all stores. If you are the one who col­lates all the data and ex­e­cutes the Mas­ter macros and you also man­age an in­di­vid­ual store, you must use one of the 12 sheets you copied for your store. The Mas­ter is for the grand to­tals only.

13. Once the branches email their in­di­vid­ual spread­sheets, it’s safer to just copy the in­di­vid­ual sheets from the 12 stores’ work­books man­u­ally.

PRO­GRAM­MING MACROS Macro1: Col­lect and com­bine data

1. Ac­cess your data­base folder and open your spread­sheet ti­tled Mas­terdb.xlsx

2. Open one of the new store spread­sheets, such as the one ti­tled Bos­tondb.xlsx

3. Move your cursor back to the Mas­terdb so it’s the ac­tive sheet.

4. Se­lect the De­vel­oper tab and click Record Macro or press ALT+L+R. The Macro Name field says Macro1, and that’s a good name.

5. En­ter a short­cut key (if you like) in the Short­cut key field box (en­ter the let­ter M; you can cre­ate a but­ton on the Rib­bon menu later). 6. In the Store Macro In field box, click the down arrow and se­lect Per­sonal Macro Work­book from the list, then click OK.

Now you are record­ing the macro. Fol­low the in­struc­tions be­low, ex­actly, and use your mouse to nav­i­gate around the spread­sheet. Please note that phrases in­side square brack­ets are tips, notes, and ex­pla­na­tions of the in­struc­tions. Do not in­clude these phrases or any­thing they say in your macro.

1. Move back to the Bos­tondb spread­sheet, then right-click the Bos­ton tab. In the pop-up menu, se­lect Move Or Copy.

2. In the Move Or Copy di­a­log, check the box that says Copy.

3. In the Move Se­lected Sheets di­a­log, click the down arrow be­side the To Books field box.

4. Se­lect “Mas­terdb.xlsx” from the list. 5. In the sec­ond di­a­log: Be­fore the Sheet, se­lect the first spread­sheet on the list called “Mas­ter,” then click OK.

6. Ex­cel copies the sheet and re­lo­cates your cursor to the Mas­terdb. No­tice the new tab that says “Bos­ton2.” Ver­ify that the info in cell A1 shows the store num­ber fol­lowed by a re­cent date (9/29/18 in this ex­am­ple). If yes, you’re good to go.

7. Right-click the tab of the orig­i­nal Bos­ton spread­sheet and se­lect Delete from the pop-up menu.

8. Ex­cel warns in a di­a­log box that you can’t undo delet­ing sheets. If you’re cer­tain you want to re­move it, click Delete. Why? Be­cause you want to re­place it with the new Bos­ton sheet that the Bos­ton man­ager sent to you.

9. Move the Bos­ton2 tab be­tween the Mas­ter and Chicago tabs. If you keep the “2” from Bos­ton2, it will be eas­ier to quickly rec­og­nize which sheets have been up­dated each month.

10. Click Ctrl+home to re­lo­cate the cursor to cell A1 and re-en­ter this date for­mula: =TO­DAY() (if this for­mula is miss­ing), then press the

En­ter key.

Ex­e­cute the Macro

1. Se­lect the De­vel­oper tab again and click Stop Record­ing or press ALT+T+M+R.

2. Save the Mas­ter file, then save the Bos­tondb file.

3. Go back to the Mas­terdb spread­sheet and run the macro: Press Ctrl+m.

NOTE: Re­mem­ber that the plus sign (+) means a “si­mul­ta­ne­ous” com­bi­na­tion key­stroke; that is, Ctrl+shift-j means: Press and hold down the Ctrl and Shift keys with your left hand, then press the J key with your right hand, then re­lease all three keys si­mul­ta­ne­ously. The dash (or hy­phen) means a “con­sec­u­tive” com­bi­na­tion key­stroke, such as End-down, which means press the End key and re­lease, then press the Down arrow and re­lease. These are not in­ter­change­able, so watch the signs.

4. If the macro works as ex­pected, re­peat this process again for each of the re­main­ing 11 spread­sheets, then run the macros, save the files, and exit all spread­sheets ex­cept the Mas­ter.

NOTE: The only avail­able

short­cut keys are Ctrl+m (which you have al­ready used), Ctrl+shift-m, Ctrl+j, and Ctrl+shift-j. Be­cause short­cut keys are in short sup­ply and the char­ac­ter com­bi­na­tions don’t make any log­i­cal sense any­way, the best solution for your mini-macros are macro but­tons on the Rib­bon menu with names that make sense, such as Bos­ton for the Bos­ton macro and Dal­las for the Dal­las macro. Check out this other Ex­cel macros how-to, where there’s a sec­tion with de­tailed in­struc­tions on how to cre­ate, name, and use macros ( go.pcworld.com/num).

Macro2: Or­ga­nize and sort data

This one is easy, but with so many spread­sheets, it can be a daunt­ing task if you do it man­u­ally. Ex­cel ac­tu­ally pro­vides a way to mod­ify all your spread­sheets at once, but this task is un­re­li­able when sort­ing.

Fol­low the Record Macro in­struc­tions

(4, 5, 6 un­der Macro1 above) to cre­ate this next macro. Name the macro Macro2 and use Ctrl+shift-m for the short­cut (you can cre­ate a but­ton on the Rib­bon menu later). This macro af­fects all the spread­sheets in the Mas­terdb, so en­sure this file is open and ac­tive.

1. Press Ctrl+home [to move cursor to A1].

2. Press the Down arrow key three times.

3. Press Shift-end-down-end-right [Hold down the Shift key, press the End key and re­lease, press the Down Arrow and re­lease, press the End key and re­lease, press the Right arrow and re­lease all].

4. Se­lect Data > Sort. In the Sort di­a­log, choose Model Num­ber from the drop-down list in the Sort By field box, then choose Val­ues from the Sort On field box, and then choose A-Z from the Sort Or­der field box, and click OK.

5. Press Ctrl+home.

6. Click the next tab at the bot­tom to ac­cess the next spread­sheet (i.e., Chicago af­ter Bos­ton), and re­peat all steps above: 1–6, and then con­tinue with the fol­low­ing in­struc­tions be­low. Re­mem­ber, the macro is record­ing through all these steps.

7. Click the Mas­ter spread­sheet tab, press Ctrl+home.

8. Se­lect the De­vel­oper tab (from

Rib­bon menu) and click Stop Record­ing or press ALT+T+M+R.

9. Save the Mas­ter file, Mas­terdb.

10. With cursor still in Mas­terdb spread­sheet, run the macro: Press Ctrl+shift-m.

Macro3: En­ter for­mu­las

The for­mu­las for the in­di­vid­ual stores’ spread­sheets are al­ready in place. You en­tered those back in step 9 of the Prep Work sec­tion above. These for­mu­las are for the Mas­ter spread­sheet, which cal­cu­lates all the oth­ers and com­bines the grand to­tals into one “mas­ter” sheet. We use a macro for this process rather than do­ing it man­u­ally 12 times.

Fol­low the Record Macro in­struc­tions (4, 5, 6 un­der Macro1 above) to cre­ate this next macro. Name the macro Macro3 and use Ctrl+j for the short­cut (you can cre­ate a but­ton on the Rib­bon menu later). This macro af­fects all the spread­sheets in the Mas­terdb, so en­sure this file is open and ac­tive.

1. Press Ctrl+home [to move cursor to A1].

2. Press Down-right-right

3. =Sum (bos­ton : den­ver! c2) En­ter (En­ter this for­mula in cell C2, where the tabs named Bos­ton and Den­ver rep­re­sent the first and last spread­sheet tab names in your work­book. This is ex­clud­ing the Mas­ter, of course, be­cause you are cal­cu­lat­ing all the val­ues in cell C2 from the first tab Bos­ton through the last tab Den­ver and en­ter­ing the to­tals in cell C2 of the Mas­ter. Then the En­ter key is pressed.)

4. Up-arrow, Ctrl+c (Moves cursor back up to cell C2 and copies this for­mula.)

5. Right-right-shift-right-right-right En­ter (moves cursor to the right twice and stops on cell E2, press the Shift key and hold down while mov­ing to the right three times, which high­lights cells E2 thru H2, then press the En­ter key).

6. (While these cells are still high­lighted, press) Shift-ctrl+4

7. ALT+T+M+R (Press these keys si­mul­ta­ne­ously or se­lect the De­vel­oper tab and click Stop Record­ing.]

Save, copy, and dis­trib­ute

1. Ctrl+home

2. Save the Mas­ter file, Mas­terdb.

3. Send copies of the Mas­terdb to all store man­agers.

Build the Mas­ter spread­sheet first.

En­ter the for­mu­las to cal­cu­late the col­umns and rows.

Copy the Mas­ter spread­sheet 12 times, then name the tabs.

Record macro di­a­log box.

Record the macro, com­bine the data, delete du­pli­cate sheets

Macro sorts the spread­sheet by Model Num­ber.

En­ter+ cal­cu­late for­mu­las in Mas­ter for mul­ti­ple sheets.

Newspapers in English

Newspapers from USA

© PressReader. All rights reserved.