Posts Tagged ‘Macro’

A Simple Time-Saving Summation Excel Macro

One of my first blog posts was about how I use macros with QuickBooks reports exported from Excel.  That post also contains a brief explanation of what a macro is, so please read it first if you are unfamiliar with macros. In my work, I often find myself combining similar types of expenses provided by a client on a spreadsheet for a more condensed presentation on a financial statement or a tax return.  The repetitive task of selecting cells, adding borders, and entering formulas became tiring with one particular financial statement I was working on, so I decided to write a macro that would perform those tasks automatically. The video below shows me combining expenses from a spreadsheet twice.  The first, in which I add borders to the cells and enter the sum formula manually, takes 57 seconds.  In the second, I select the range of cells and use a shortcut key on my mouse to run the macro.  With the help of the macro, I complete the spreadsheet in only 11 seconds. There are benefits to using the macro besides just saving time.  It reduces eye movement and mouse clicks, both of which seem to wear me out from using the computer over a period of time. Note: The data contained in this video is completely fictional.  You may want to make the video appear in full-screen mode to better read the text.  If you can’t see the video below, click here.

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:
  1. Combine the amounts from the debit and credit columns, show the amounts from the credit column as negative, and rename the resulting column “unadjusted.”
  2. 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.
  3. 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.
  4. Change the bolded account names and column headings to regular font.
Note: The data contained in this video is completely fictional.  You may want to make the video appear in full-screen mode to better read the text.  If you can’t see the video below, click here. Accountants: If you’re looking to add to your Excel skills, I recommend learning how to write macros.  I don’t write macros very often, but my coworkers and I use macros I’ve written every day. Clients/Potential Clients: If a macro could help you save time by automating a repetitive task in Excel, please let me know!