Go To Special in Excel

Just as a GPS is a handy gadget for a stubborn guy who refuses to ask for directions, Excel’s Go To Special feature quickly finds and selects cells in a spreadsheet without all of the U-turns and dead ends.  The Go To Special window can be launched under the Find and Replace button in Excel 2010 and it offers a plethora of options to choose from. One of the most useful abilities of Go To Special is selecting blank cells.  For example, I first select all of the cells in a table (A1:D6 shown below).  In the Go To Special window, I select the Blanks option.  After clicking on OK, the empty cells in the table are selected. With the empty cells selected, I can then change the cells’ background color to yellow, for example.  I can also enter a value for the cells, such as zero, making the value apply to all of them at once by holding the Ctrl key while pressing enter. The Errors option (under Formulas) in the Go To Special screen will select all of the cells with errors.  It’s a fast and effective way of making sure a spreadsheet is error-free.  After all, few things are more embarrassing than giving someone a spreadsheet with errors where there are supposed to be numbers. How have the other Go To Special options been helpful for you?  Please share by leaving a comment below.

Using the Excel SUMIF function to find that out-of-balance journal entry

One of the most underrated Excel functions that I use often is the SUMIF function.  On my Excel function tournament bracket, SUMIF ranks slightly behind the SUM, AVERAGE, IF, COUNT, PMT, and RATE functions.  Still, it has a shot at upsetting one of the higher seeds on any given day. The SUMIF function adds the values in cells specified by a given condition or criteria.  Tim Gavin, a fellow CPA blogger, wrote a great blog post last week for the Sikich Technology Blog explaining how the SUMIF function works.  I won’t reinvent the wheel here, so check out his post for a tutorial.  I simply want to give an example of how I use it. Let’s say I’m cruising along in Excel making journal entries.  Each entry is designated by a letter.  Some entries have multiple debit and credit adjustments.  Work comes to a screeching halt when I check the column totals and realize that my journal entries are out of balance by $200.  It may be easy to spot the difference with only four entries in the screenshot I’ve provided below, but just imagine that I’ve exhausted the alphabet and am up to entry z.  With so many adjustments, finding the discrepancy could become a painful and time-consuming ordeal. Out of Balance Adjustments Here comes the SUMIF function to the rescue.  First, I enter the letters of the alphabet for each journal entry down a column (using the fill handle to drag down the letters sequentially saves some wear and tear on the keyboard).  Then I enter the formula shown below and copy it down for each letter.  The formula adds the amounts for the debit and credit column that correspond to each letter of the alphabet. SUMIF formula The results are in.  Adjustments a, b, and c balance out.  It was adjustment d that threw me off my game. SUMIF results

Excel’s set precision as displayed option

When I was in first grade, I learned that 2 + 2 = 4.  Excel, who I have come to know and trust, thinks the answer is five.  How is this possible? Two plus two is ... five?The truth is told after I increase the decimal places shown for each of these cells.  The twos on the spreadsheet really aren’t twos after all.  They’re 2.43 and 2.35, which both round down to two.  The sum, 4.78, rounds up to five. 2.43 + 2.35 = 4.78To fix this kind of rounding difference in Excel 2010, click on the File tab and go to Options.  On the left side of the window that appears, click on Advanced.   Scroll down and check the box next to “set precision as displayed.” Set Precision As DisplayedA window will appear, warning that “data will permanently lose accuracy.”  For example, when I decrease the decimal so that all of the numbers are shown as whole numbers, they actually become whole numbers (increase the decimal again, and both twos will be shown as 2.00).  Don’t select this option if you need complete data accuracy in your spreadsheet.  I can’t be held responsible for a few lost pennies. 2 + 2 = 4This option is especially helpful for an accountant whose report must always foot. Thanks to Val Steed of K2 Enterprises for showing this in a session at the 2011 AICPA TECH+ Information Technology Conference.

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.

Why Reinvent the Spreadsheet? Use a Template!

Need a loan amortization schedule?  There’s a template for that. Creating a personal monthly budget?  There’s a template for that. Is one of your kids getting married and you’re planning out the expenses?  There’s a template for that. Okay, you get the point and you’re not amused with my not-funny “there’s an app for that” parody, so I’ll stop there. Friends have sometimes asked me to help them create spreadsheets for all of the above purposes, perhaps unaware of the many templates that exist for Microsoft Excel.  Sure, there are some complex customized spreadsheets you should ask a CPA for help with, but for common and simple stuff, you could use a template for free.  There are tons of them. To view a list of templates available in Excel, click on the Office Button on the upper-left corner of the Excel window, then click on New.   You might only see a few under Installed Templates, but there are many more you can download from Microsoft Office Online (you’ll see a disclaimer from Microsoft saying that these templates were supplied by members of the Microsoft Office Online community, and Microsoft does not promise that the templates will work for your purposes or be free from viruses and defects). The most common spreadsheet I am asked to produce is a loan amortization schedule.  Excel has a template for this that I am particularly impressed with.  All you have to do is enter values such as the loan amount and interest rate in the orange colored cells and the table magically appears.  Change one of the values, and the table revises itself immediately.  The template is also capable of handling extra principal payments, either regular or occasional, and adjusting for the total number of payments automatically. If you only want to only use Excel and not any other accounting software, you can download templates for check registers, general ledgers, invoices, sales receipts, balance sheets, and income statements.  Doing bookkeeping in Excel doesn’t sound like a lot of fun to me and I wouldn’t recommend this to anyone, but using these templates is certainly easier than trying to do it from scratch. You’ll also find several templates that are helpful for tax purposes, such as a mileage log and business travel expense log.  Use these templates to organize your information, and your tax preparer will be impressed. What if you can’t find the template you want on Microsoft Office Online?  You might be able to find it among the thousands of templates from Google Docs (Google’s free suite of online productivity software which includes a spreadsheet program similar to Excel).  If you’d rather use Excel, you can create the spreadsheet in Google Docs with the template and then save it as an Excel file.  I have done this with a few Google Docs templates, and they work perfectly in Excel. The next time you need to create a spreadsheet, just remember, there might be a template for that.

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!