Excel Macros Report: Free Video Tutorial & Transcription

Enhance your productivity with the power of Excel macros to automate daily reports. Learn how to create, record, and test macros to automate report formatting tasks, potentially saving hours of work over time.

Key Insights

Excel training classes provide in-depth coverage of Excel macros and reports, beneficial for everyday tasks like formatting daily reports.
Creating a macro involves writing out a list of instructions, recording the macro in Excel, and then testing the macro to ensure it performs the desired tasks.
Recording a macro involves going to the Developer tab in Excel, clicking Record Macro, and then performing the tasks to be automated, such as formatting a column as a date column or splitting a column into two.
Testing a macro can be done by undoing all the actions taken during the recording of the macro and then running the macro to ensure it performs the tasks correctly.
Using a macro can significantly reduce the time spent on repetitive tasks, such as formatting a daily report. In a classroom example, a task that took two minutes each day totaled 12 hours over a year, while the macro performed the same task in less than a second daily, totaling only six minutes over a year.
Macros not only save time but also ensure accuracy in tasks, making them a powerful tool for increasing efficiency in your work.

In this video, we'll create a macro that's going to format a report that we are going to imagine we need to create every single day.

Excel macros and reports are covered extensively in our Excel training classes in NYC. For those outside New York, find and compare the top Excel classes in-person or Excel classes online.

Macros Report

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.

Write Instructions

It's recommended that when you're creating a macro, write out your list of instructions so you can go step by step through the instructions and not miss any steps.

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 1, 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.

So 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.

Create Macro

So in order 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 "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.

Record Macro

If you look at Record Macro, as soon as 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 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 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 1, right-click and choose Insert. 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 autofill. 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.

Test Macro

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.

Save Hours of Work

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 was 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.

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.

Recap

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.