Most people that work with data are always looking for ways to process data faster. Majority of reports generated in office environment for analysis purposes are created in Excel. Putting together these reports takes time. Even though the reports look the same, it takes a lot of steps to produce.
Everyone knows that automating these reports can save them money and time, but they don’t know how or have the time to learn how. I would like show you the 3 ways to automate excel reports. Then, you can decide which is the best way for your situation.
The easiest way to create automation is by using macro recorder. By selecting developer tab, you will see macro recorder button.
If you cannot find the developer tab, you have enable it first. To do that, you select File tab, Options on the bottom left hand menu, Customized Ribbon, then Developer tick box. You will see the developer tab after clicking OK.
Just click record, to start recording every single step that you make. To stop recording, you can click the stop button and to play the recorded steps, you click play. The weakness of this method is it’s very hard to add and remove steps. The only way to edit is by opening the editor window and you will be exposed with VBA or codes.
The method with the most flexibility is by opening the editor window directly and open a module. You can automate any task you can imagine with this method. The material to code VBA is widely available on the internet for free. If you have the time to learn VBA, this is the best way to automate.
If you want to automate, but don’t have the time to learn VBA, you can use Excel macro template to automate. In the template, you select action, workbook, sheet, and range for each task that you want to automate. After making a list of tasks, you just need to click Go to run all the tasks at once. This method doesn’t have the flexibility of coding VBA, but it will get the job done for basic automation. You can download the template here to try.