Create Excel macros to efficiently automate repetitive daily formatting tasks, significantly reducing report preparation time. Learn the practical steps involved, from manual formatting to macro recording and execution.
Key Insights
- Recording Excel macros can substantially reduce the time spent on repetitive tasks; for example, formatting a daily report manually could take up to 12 hours annually, but a macro can perform the same task in mere seconds.
- When creating macros, it is beneficial to clearly outline each step beforehand to ensure accuracy and efficiency—for instance, formatting column A as date, splitting column B data into two columns, and inserting appropriate header rows.
- Macros not only improve speed but also ensure consistency and accuracy when formatting CSV reports by automating detailed processes like text-to-column splitting, column insertion, and auto-fitting columns.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
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 through the instructions 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 in our headers listed here. We'll then select all the columns and auto-fit them, and then select cell A1.
If this is a report that I would have to create every morning, these are the steps I would take to format that report, especially if this is coming in as a CSV file. CSV files sometimes don't have headers and we need to format it that way. So to create the macro, I'll go to the developer tab and I'll click record macro.
Now I'm going to call this macro the 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'm not going to write a description. If you look at record macro, as soon as I click okay, 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 the data in column C. So that is why I'm inserting a new column. So there's space for my information. When I click on column B, 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.