Creating a Macro in Microsoft Excel

Having trouble working with data sets in Excel? Spending too much time preparing data, and not enough time analyzing it? I have routinely used Macros within excel to shorten the process of creating effective data models, so I decided it is a good time to teach others how to use this neat tool. Effectively using macros on timely processes will save your business lots of time, in return saving money.

Macros are essentially programs that you create in Excel through a process generally known as “Visual Coding”. Basically, you press “Record Macro”, then show the intermediate steps and calculations until you get your final product, then you end the recording. Now anytime you have input data laid out the same as your original macro, you can press that button and the final product will be made instantly.

I created a mock list of purchase orders and corresponding price and cash received information. Through the process I am about to show you, I created a macro to input the data into a professional spreadsheet with a click of a button. The finalized spreadsheet can analyze what accounts still have a balance left in them, and the total number of accounts for the given period. Feel free to take my macro and personalize it to match your company’s needs.

Warning Before You Begin:

Macros are a complex function of Excel. I do not
advise beginning Excel users to attempt to make a macro, first learn how to work within Excel and once you have a solid understanding of how to use Excel and the various functions then try creating macros. With that being said, this instructable is made assuming the readers understand how to operate within excel.

• Access to Microsoft Office and Excel
• A general understanding of how to use Excel

First Image:

Organize the data you want included in your table onto an excel spreadsheet. Once everything is in Excel you need to record the macro. With macros you only get one attempt, so I recommend reviewing this in full before attempting.

Second Image:

To record the macro, you will need to activate the
developer tab, to do this go to File>Options>Customize Ribbon then select the Developer tab and click “OK”.

Third Image:

Now that you have the Developer tab in your Ribbon,click on the Developer tab then click “Record Macro”. Create a title that is suited to the file’s content and press “OK”, everything after this step will be the process of recording the macro until the step where it is concluded. Also note hat all cells highlighted in yellow are to help guide you through this instructable DO NOT put this in your own document, for it will alter the macro.

First Image:

First create a copy of the open tab so you have identical tabs open. Return to your original tab and select the data you wish to organize. Create a table with headers and add a few rows of space above it, I did 5 rows.

Second Image:

Create a title at the top and create a box that will hold the check figures. The check figures for this template are “Number of Customers” and “Cash yet to Collect”. As for the organization of this template, you may do a you please but be aware that my instructable is specific to the layout I chose.

This step is quite simple; however, a mistake here can lead to the check figure not working in the final macro. In the box next to the “Number of Customers” cell, create a “COUNT” formula to total up the number of cells in a single row of the table. I chose to count the cells I highlighted yellow. Also, good to note that my full formula is at the top of the picture as well.

Beware, these next two steps are extensive and can be confusing, so read carefully. From here on out my instructions will be specific to my data model. If you require a different process please use mine as reference but not as a 100% accurate model, for it may not work for your file.

First Image:

Go to the 2nd tab in the workbook (the copied tab)

In column J create a new row titles “Amount Left”. In Cell J2 (highlighted cell above) enter the formula =G2-H2 and copy it down to the rest of the cells. Return to the original tab with the working template.

Second Image:

In cell E4, make a new formula totaling the Amount left column from the copied page before. The formula bar shows the finished formula.

To create a visual to show what customers still have outstanding balances we will need to create a separate table.

First Image:

To begin return to the copied tab, shown above. For the new table we will only want the information for customers with an outstanding balance. First in columns “L” and “M” title them “First Name” and “Last Name” respectively. Then in cell L2 type the formula =IF($J2>0,A2,””). This formula written out states, if cell J2 is greater than 0, then show the value in cell A2, if it is not greater than 0 leave the cell blank.

Make sure that when you reference cell J2 you put a dollar sign ($) in front of the J, this allows us to copy the data to other cells correctly. The $ sign keeps the reference in column “J” but allows the rows to change accordingly. Next copy the formula to the rest of the cells in column “L” and “M” (circled above).

Second Image:

To accurately pull the cash value, we use a similar value as to the names before. This time create a formula that states, =IF(J2>0,J2,””). The only difference in this formula is that if the value is greater than zero, we want the data from “Amount Left”. Copy this data to the rest of the cells.

Third Image:

Next copy the new data from the copied tab, then go to
the template tab and right click below the table (doesn’t matter where). After right clicking, look under paste options and choose “Paste Values”, this pastes only the numbers and not the formulas.

Fourth Image:

Create a filter for the table and choose one of the headers. Click the drop-down menu and select filter all out except blanks. Once the filter is in place the row numbers will show up blue, I highlighted the cells yellow to make it easier. Select the blue rows and delete these rows. Once the rows are deleted, clear the filter as shown below:

To make the Uncollected Cash by Customer data more professional, format the data as a table. Once that is complete, add a Title to the table and click “Merge & Center” to make it uniform to the table. If not done so already make sure the numbers are formatted to “Accounting” to show that dollar signs and cent amounts. At this point the macro is complete and the final product should look something like this:

Go up to the “Developer” tab in the ribbon and click the “Stop Recording” button. At this point the macro is finished and ready to be used!

When you use the macro in the future, if the beginning data is laid out as it is in Step 1, the macro should work perfectly! Follow these steps closely and I promise you will Excel. Good luck!