Streamlining Daily Reports with Macros for Accuracy

In this section, we're going to create a macro that's going to format a report that we are going to imagine we need to create every single day. We'll first do this manually during the recording process, and then we'll take a look and see how the macro can more efficiently do the same work in a shorter amount of time. So let me walk you through the steps that are involved to format this report.

It's recommended that when you're creating a macro, you write out your list of instructions so you can go step by step and not miss any steps. And I have my instructions written here. What we want to do is format column A as a date column.

Then we're going to insert a column at column C so we can split column B into a three-digit number and a five-digit number. We'll then select row one, insert a new row, and enter the headers listed here. We'll then select all the columns and auto-fit them, and then select cell A1.

If this is a report I need to create every morning, these are the steps I’d take to format it, especially if it comes in as a CSV file. CSV files sometimes don't have headers and we need to format them that way. So to create the macro, I'll go to the Developer tab and click Record Macro.

Now, I’m going to call this macro 'report_macro'. I'll use an underscore because you can't have spaces in the macro name and call this report_macro. I'm not going to use a keyboard shortcut.

I'm going to save the macro in this workbook, and I won’t write a description. Once I click OK, you’ll see it says Stop Recording. Now, all my actions within the workbook are being tracked.

I'm going to click on column A, then I'm going to go to the Home tab, head over to the number group, and choose short date. That's my first step. Then I'll click on column C and sequence is important here.

---

If you forget this step, you'll get stuck when you try to split columns because you don’t want to overwrite data in column C. That’s why I’m inserting a new column. So there's space for my information. When I click on column B, I’ll go over to the Data tab and choose Text to Columns.

I'm going to choose Fixed Width this time. All the values in the column are exactly the same size, so I'll click next. I'm going to split those values right after the third digit.

Then I can simply click finish and I've moved the following five digits after the third digit over into column C. I'm going to click on row one, right-click, and choose Insert. I'm going to click on cell A1 and start typing in my headers, pressing tab each time. So date, customer number, then product number, quantity, price, category, product, region, sales rep.

Then I want to select columns A through I and auto fit them. Then I'll simply select cell A1 and now I can breathe a sigh of relief, go over to the Data Developer tab and choose Stop Recording. Now I want to be able to test this macro, so I am going to undo all of the actions I just took by pressing control Z. I'll know I've reached the starting point when column A is unformatted and that's where I am.

Now usually when I teach my macro classes, I have students do this as the first exercise. I say to them, I'm going to time you as a group. You all need to format the report in exactly the way that I just showed you and you all have to work as a team.

So I'm not going to stop the clock unless everyone has the report exactly set up the way that I just displayed. So I would do this in a class, one particular class. It took everyone about two minutes to do this.

---

I then as an object lesson multiplied those two minutes by 365 and told them that if this is a report they needed to create every morning and it took them two minutes to create the report at the end of the year, it would take them 12 hours to do that. And I would tell them this is how long it would take for me to do it if I used a macro. So I'm going to click on Macros and I'm going to select my report_macro.

Now you can start timing as soon as I click Run. That’s how long. So practically less than a second.

Now if I did multiply a second by 365, the total amount of time would be six minutes. And so that is just one two-minute report. Imagine how many other activities we perform during the course of our workday that could be simplified with the recording of a macro.

So macros are very powerful and they pay off in dividends when it comes to saving time and being more efficient with your work, not only quick but also accurate.