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.

Tags: , ,

Trackback from your site.

Comments (3)

  • Avatar

    Shane Eloe

    |

    Dustin –

    Alternatively in that situation you could put several =SUMIF functions (that sum based on a reference number) at the bottom of your spreadsheet and assign the appropriate reference number in either column C or D. Then you don’t have to do any of the formatting at all.

    Reply

  • Avatar

    Dustin Wheeler

    |

    Shane,

    I frequently use the Excel SUMIF function in cases where the amounts I’m combining are not adjacent to each other because they are not in the same alphabetical order (for example, advertising and marketing). When the amounts are next to each other (for example, office expense and office supplies), I’ve found that using that macro is a faster method and produces a format that is easier for the reviewer to follow.

    You gave me a great idea! One of my future blog posts will be about the SUMIF function.

    Reply

  • Avatar

    Chris

    |

    You Excel freaks, I LOVE IT. There was a part of me rooting you on to go faster Dustin. I am a freakin’ dork and I love Excel. I have no suggestions like my man Shane to rebutt your process. SUMIF me brotha!!!!

    Reply

Leave a comment