Macros in Microsoft Excel are a great way to perform the same set of actions repeatedly. When you export reports from QuickBooks Online you may find that you want to format them differently. You may also find that you frequently export the same reports for clients. Macros in Microsoft Excel is a great way to save time doing this. Here’s how it works.
To use Macros in Microsoft Excel for quick formatting on your QuickBooks Online reports you first need to show the developer tab in Microsoft Excel. Right click the tabs across the top and choose “Customize the Ribbon”:
Then choose to show the Developer Tab:
Especially in this context when you record Macros in Microsoft Excel you will want to “Use Relative References.” This means that when you are recording and you move the cell pointer, excel will recognize the pattern of your movements and not the specific cell you went to. Since you won’t know how many lines of data you’ll be getting you will have to use relative references. When you select a range you will want to use the navigational resources Microsoft Excel has to offer so that you can have Microsoft Excel find the starting point of your data and know how to get to the end point regardless of the size of the range where the data is found.
Let’s say you have a formatting template that you would like to see applied any time you export a profit and loss statement from QuickBooks Online. This is where recording macros in Microsoft Excel will help save time.
- First export the report from QuickBooks Online.
- Then go to the developer tab.
- Make sure you select Use Relative References
- Then Click Record Macro.
Microsoft Excel will prompt you to name the Macro. No special characters or spaces. Keep this real simple like “PL_Report.”
Now it’s time to start recording your actions. Remember that you have to account for all possibilities. You might be at the home cell but when you or someone else runs this Macro later, they may not be. So it’s always a good idea to make your first action to be selecting cell “A1”. Simply press F5 (Goto) and type in A1, then click ok. If you are already there you will see nothing happen, but Microsoft Excel will record that action in the Macro nonetheless. Now you can start from there knowing the shape / outline of the report you just exported. Keep in mind always that you cannot rely on certain things being the same. In this case, mainly the report may go any number of lines down. This is why I mentioned above that you need to understand the navigational implications in Microsoft Excel when you do this.
Watch the video for this segment and you will see how I create a Macro to format the Profit and Loss report from QuickBooks Online in Microsoft Excel however you like.
Share this Post