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 …
Continue reading Go To Special in Excel
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 …
Continue reading Using the Excel SUMIF function to find that out-of-balance journal entry
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?
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.
To fix this kind of rounding difference in …
Continue reading Excel’s set precision as displayed option
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 …
Continue reading A Simple Time-Saving Summation Excel Macro
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 …
Continue reading Why Reinvent the Spreadsheet? Use a Template!
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 …
Continue reading Using Excel Macros to Manipulate QuickBooks Reports