I want to teach you with this article on how to join multiple tasks into a single one-second event and this way automate a lot of mundane time-consuming task in your excel workflow.
Excel is a fast working application for executing various calculations, accounting, analysis, visualization, etc. In fact, it takes one-tenth of a second to calculate a large spreadsheet, nevertheless, it's the manual work of typing and formatting that slows you down your workflow. That is the reason you want Excel macros to connect multiple tasks into a single action.
In this article you will learn:
• Tips for using an Excel macro
• How to prepare a master dataset for a macro environment
• How to automate data collection with macro
• How to automate data sorting with macro
• How to automate formulas with macro
I have given a sample workbook for the for the scenarios described in this article so you can follow along step by step. If needed, you can create a custom spreadsheet with your values in it.
Let's begin with our first macro. Once you understand how easy it is to automate tasks using macros, you’ll never look back. But, before we jump in, here are some tips on how to manage your data before implementing a macro function:
For this example, we have an eCommerce store owner who has expanded his territory from a single store to 8 different major cities. Now the CEO has been running his own books for years, which was a challenging task for a single store, and now he has 8. He has to collect data from each store and merge it to monitor the progress of the entire company.
We built a few smaller macros to accomplish the following tasks:
Once small macros are built and tested, we can merge them into one master macro or just leave them as independent smaller macros. Either way, it is advised to keep smaller macros because it's much easier to work and debug them in contrast to big and detailed macros.
The first thing to remember is that In case you are building a spreadsheet from scratch, always start building your master spreadsheet first.
In the following chapter, I will guide you through a step by step process of creating a master spreadsheet:
Row 1: Enter the date formula in A1 and the store location in B1. See image below:
A1: =Today()
B2: Branch name and number
Row 2: Once the dynamic date and static info are entered, leave the entire second row reserved for the necessary calculations of every column. This is a good practice because this row is fixed and always visible.
Row 3: Enter the category names and any other field-specific information you would need. In the example below, they are extended from A3 through J3.
Column A: Enter the static data in column A. (values in your spreadsheet that rarely change). If your project works with product numbers or ID codes (in our example SKU numbers) or other unique value per product, enter them in Column A beginning from row 4 (don’t skip to row 5). Other static data fields might include the Product Description, the Product Price, sales tax percentage, etc. - these values can be placed in separate columns.
Every row in column A must include a unique value for two main reasons:
a) Column A is the main navigational column for our macro. It navigates through the spreadsheet based on the Home (A1) position and column A.
If some of the cells contain a blank field, it will disrupt the macro function.
b) If you want to build multiple tables later and connect them through Pivot Reports, you must have unique values from each table to properly join them.
There is a usual standard location for each category but this depends on your type of data.
a) Column B = Product Description
b) Column C = Quantity sold
c) Column D = Product price
e) Column E = Extended Cost
f) Column F = Price Discounts
g) Column G = Sales Tax
h) Column H = Totals
Remember, the total values of these fields are in the static row 2 for each column.
Once the master database structure is set up, do not change anything. If there's a need of adding additional columns to the table, use the insert column command.
For example, if you want to add a second discount level, position your cursor anywhere on column G and click the tab: Home > Insert > Insert Sheet Columns. This process does not affect the macro function.
The same rule applies to rows. Be careful when inserting rows inside an active. For instance, if the formula in row 2 states =SUM(B3:B20) and you insert a row outside of the formula’s range like B21, the new record won't be included in the formula's calculation and therefore you have to modify it to =SUM(B3:B21)
Let's set up the formula range to include all the current and possible future rows, this way you won't need to change this in the future.
a) C2: =SUM(C4:C500)
b) E2: =SUM(E4:E500)
c) F2: =SUM(F4:F500)
d) G2: =SUM(G4:G500)
e) H2: =SUM(H4:H500)
As seen above, we set the formula range to be several rows beyond our last record (500), so if there's ever a need to add new records, we can do that easily and not worry about adjusting the range.
At this stage, let's begin entering the formulas to calculate the different values in these columns:
a) E4: =SUM(C4 * D4), then copy from E4 down to E5:E500
b) F4: =SUM(E4 * 10%), where 10% represents the current discount percentage, then copy from F4 down to F5:E500
c) G4: =SUM(E4-F4) * 6.25%, where 6.25% represents the current Sales Tax in your country, then copy from G4 down to G5:G500
d) H4: =SUM(E4-F4+G4), then copy from H4 down to H5:E500
After all the spreadsheet formulas are inserted, all you will have to do is enter the sold quantity for each product in column C (daily, weekly, or monthly). If the product prices change, enter the new figures in column D. The foundation of this database is all formulas and static information.
Once we established our spreadsheet with a proper structure, our fixed data, and correct formulas, make 8 copies in worksheets 2 through 9. Rename every worksheet at the bottom to identify every store and change the name of the sheet1 work to 'Master'.
Change the location data on row 1 to identify the store information (that matches the store on the tab) on all 9 spreadsheets. In this step you can email a copy of each branch spreadsheet to each of the store managers; for example, send the Texas sheet to Texas, etc. Their copies include the spreadsheet with static data of their branch and formulas that operate on their spreadsheet only.
The macro function will provide the formulas (grand totals of all stores) for the Master spreadsheet only.
Once the individual stores get back to you with their spreadsheets, you will copy the individual sheets from the 8 stores manually to your master spreadsheet.
Done, at this point, you are recording every step you make in the macro. Follow the next directions, and use your mouse to navigate through the spreadsheet.
Formulas for individual stores are already in position, we entered them back in the Prep Work section above. The following formulas will be for the Master Spreadsheet alone which will calculate the combined sum of all store spreadsheets.
The programming part of this macro is going to be the same as for Macro1 and Macro2, and it follows:
