10 Nov2009
Using Excel Macros to Manipulate QuickBooks Reports
Since I began working in accounting, I have observed some fellow accountants exporting data from accounting software programs to Excel and then performing repetitive tasks of reformatting the spreadsheet (moving data to other cells, entering calculated fields, changing fonts, resizing columns, etc). This has provided me with the opportunity to use and share a valuable skill I learned in one of my classes in college so that these tasks can be completed automatically with the push of a button. One of the powerful features of Excel is Visual Basic for Applications (VBA), a programming language for creating macros. A macro is a set of instructions for Excel to complete automatically that would otherwise be performed manually by a person. There are countless ways that you could use macros in Excel. In fact, they can do about anything you would normally do except actually “think” for you. Completing the tasks I described above of reformatting and manipulating data is just one simple example of what macros are capable of. The video below is a demonstration of a macro I wrote that I call “the trial balance formatter.” It is designed to take an Excel export of a trial balance report from QuickBooks and automatically make the following changes:- Combine the amounts from the debit and credit columns, show the amounts from the credit column as negative, and rename the resulting column “unadjusted.”
- Add four columns to the right of the unadjusted column for adjustments. Columns C and E are for the amount of the adjustments and have a width of 12. Columns D and F are for letters identifying the adjustments and have a width of 2.
- Add a column to the right of the adjustments titled “adjusted.” Formulas adding the amounts in the unadjusted and adjustments columns are automatically entered into the cells in this column.
- Change the bolded account names and column headings to regular font.