How to create Excel macros and automate your spreadsheets

Use macros to combine multiple tasks into a single, one-second transaction.

Credit: Rob Schultz

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.

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.

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. 
01 build the master spreadsheet first JD Sartain / IDG Worldwide

Build the Master spreadsheet first.

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.

02 enter the formulas to calculate the columns and rows JD Sartain / IDG Worldwide

Enter the formulas to calculate the columns and rows.

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 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 the Master. The Master is the spreadsheet for the combined totals of all stores. If you are the one who collates all the data and executes the Master macros AND you also manage an individual store,  you must use one of the 12 sheets you copied for your store. The Master is for the grand totals only.

13. Once the branches email their individual spreadsheets, it’s safer to just copy the individual sheets from the 12 stores’ workbooks manually.

03 copy master 12 times name tabs JD Sartain / IDG Worldwide

Copy the Master spreadsheet 12 times, then name the tabs.

Now we're ready to program a macro! Just click to the next page.

Join the newsletter!


Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

Tags Office 2013Office 2016

Keep up with the latest tech news, reviews and previews by subscribing to the Good Gear Guide newsletter.

JD Sartain

PC World (US online)
Show Comments

Cool Tech

Toys for Boys

Family Friendly

Stocking Stuffer

SmartLens - Clip on Phone Camera Lens Set of 3

Learn more >

Christmas Gift Guide

Click for more ›

Brand Post

Bitdefender 2019

This Holiday Season, protect yourself and your loved ones with the best. Buy now for Holiday Savings!

Most Popular Reviews

Latest Articles


PCW Evaluation Team

Michael Hargreaves

Microsoft Office 365/Dell XPS 15 2-in-1

I’d recommend a Dell XPS 15 2-in-1 and the new Windows 10 to anyone who needs to get serious work done (before you kick back on your couch with your favourite Netflix show.)

Maryellen Rose George

Brother PT-P750W

It’s useful for office tasks as well as pragmatic labelling of equipment and storage – just don’t get too excited and label everything in sight!

Cathy Giles

Brother MFC-L8900CDW

The Brother MFC-L8900CDW is an absolute stand out. I struggle to fault it.

Luke Hill


I need power and lots of it. As a Front End Web developer anything less just won’t cut it which is why the MSI GT75 is an outstanding laptop for me. It’s a sleek and futuristic looking, high quality, beast that has a touch of sci-fi flare about it.

Emily Tyson

MSI GE63 Raider

If you’re looking to invest in your next work horse laptop for work or home use, you can’t go wrong with the MSI GE63.

Laura Johnston

MSI GS65 Stealth Thin

If you can afford the price tag, it is well worth the money. It out performs any other laptop I have tried for gaming, and the transportable design and incredible display also make it ideal for work.

Featured Content

Product Launch Showcase

Don’t have an account? Sign up here

Don't have an account? Sign up now

Forgot password?