Category: Sleeter Articles

Analyze QuickBooks Data with Excel Pivot Tables

by David Ringstrom,CPA
 
When it’s time to crunch numbers in QuickBooks, most users rely on reports within the software, while others turn to analytics programs. In either case, you may still sometimes need to look at your accounting data in new ways. In such cases, pivot tables can make quick work of sussing out patterns or issues with your data that might be hard to discover otherwise. In Excel, pivot tables allow you to create instant reports and/or charts by simply dragging and dropping fields with your mouse. This means you can quickly rearrange even massive reports into interactive, yet compact summaries.
Continue reading on www.sleeter.com.
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

QuickBooks Statement Writer vs. Excel FSM

by David Ringstrom,CPA
 
Creating financial statements from QuickBooks data can be a tricky endeavor. Yes, it’s easy enough to chooseReports, Company and Financial, and then Profit & Loss—as long as you don’t mind account numbers embedded into your P&L report. QuickBooks Premier Accountant Edition and Enterprise Solutions users have a built–in tool known as QuickBooks Statement Writer that allows deep integration between Excel and QuickBooks, as well as Microsoft Word. A third-party application called Excel FSM offers functionality similar to the QuickBooks Statement Writer to users of all versions of QuickBooks, both Desktop and Online. A fourth option is to roll your own financial statements in Excel without the benefit of any add-ons. In this article I’ll provide an overview of the pros and cons of each of these approaches.
Continue reading on www.sleeter.com.
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Troubleshooting When Excel Goes Awry

by David Ringstrom,CPA
 
It can be maddening when a trusted program such as Microsoft Excel suddenly starts exhibiting odd behavior. It’s sometimes difficult to tell if the culprit is Excel, Windows, or some sort of malware that’s wormed its way into your computer. In this article I’ll run through my typical list of items to rule-out when Excel seems to misbehave.
Continue reading on www.sleeter.com.
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Charting QuickBooks Data

by David Ringstrom,CPA
 
You may be surprised to discover that QuickBooks offers over one dozen charts. Some of these are buried within theReports menus, while others appear within the Company Snapshot feature. In this article I’ll show you to fastest way to access charts in QuickBooks, tricks for interacting with these charts, as well as a few charting tricks in Microsoft Excel.
Continue reading on www.sleeter.com.
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Backing Up and Restoring Excel Workbooks

by David Ringstrom,CPA
 
As you’re likely aware, you can establish automatic, unattended back-ups of your QuickBooks data. Further, if you’re using the cloud based QuickBooks Online, then practically every keystroke you make is backed up as you go. It’s a different world in Microsoft Excel, however. Users sometimes inadvertently lose hours of work on a given spreadsheet. Reasons might include forgetting to save, workbooks that get corrupted, or any number of other scenarios. In this article I’ll discuss some measures that you can take to establish a similar sense of security for your Excel spreadsheets that you have with QuickBooks.
Continue reading on www.sleeter.com.
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Automating an Excel-Based Financial Statement

by David Ringstrom,CPA
 
QuickBooks allows you a fair amount of control over financial statements – just about any user can easily customize the built-in reports. Users of Enterprise Solutions and Accountant versions can go farther and use the Financial Statement Designer to integrate QuickBooks and Excel. Yet, sometimes neither of these options is sufficient, so users dump data over to Excel to start building their own reports. This, in turn, often results in repetitive efforts and data integrity issues. As soon as you add or change a single transaction in QuickBooks, your Excel-based financial statement is out of date. However, there’s a way to have your cake and eat it too. In this article, I’ll demonstrate how to build a three-sheet Excel workbook from which you’ll be able to easily view and update a profit and loss report for any month of the year.
Continue reading on www.sleeter.com.
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Use Excel Pivot Tables to Analyze QuickBooks Data

by David Ringstrom

When it’s time to crunch numbers in QuickBooks, most users rely on reports within the software, while others turn to analytics programs. In either case, you may still sometimes need to analyze your accounting data in new ways. Pivot tables can make quick work of figuring out patterns or issues with your data that might be hard to discover otherwise. Pivot tables allow you to create instant reports and/or charts by dragging and dropping fields with your mouse. This means you can quickly rearrange even enormous reports into interactive, yet compact summaries.

See www.sleeter.com to read the rest of the article.

 

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Make QuickBooks Data Analysis More Simple in Excel

by David Ringstrom

If you’re unfamiliar with certain naunces within the two programs, analyzing QuickBooks data in Excel can be an exercise in frustration. In this article, I’ll show you techniques for both programs that can transform how you crunch numbers. Among other things, I’ll share my favorite report for QuickBooks data analysis in Excel, a technique for creating one-click report access in QuickBooks, and a brief overview of the Table feature in Excel.

See www.sleeter.com to read the rest of the article.

 

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

CSV Secrets: QuickBooks Reports to Excel

by David Ringstrom

 

While most users export QuickBooks reports as Microsoft Excel workbooks, I often use the comma-separated value (CSV) format instead. Doing so strips extraneous formatting from the report so that I can immediately filter a report or analyze it with a pivot table. In this article, I’ll show you how to use the CSV format to create Excel workbooks that automatically update themselves, along with any helper formulas you may wish to add alongside your QuickBooks data. In future articles, I’ll share techniques you can use to capitalize on this functionality.

See www.sleeter.com to read the rest of the article.

 

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Merging QuickBook Reports Using Excel VLOOKUP

by David Ringstrom, CPA

Generating reports in QuickBooks is generally quick and easy – until you hit on a specialized need. Certain reports allow you to add or remove columns of data, but sometimes a key piece of data you need won’t be available on a given report. For instance, the Inventory Stock Status by Vendor report shows you which items you need to reorder, but it doesn’t provide the ability to add the vendor’s contact information. In this article, I’ll demonstrate how you can use Excel VLOOKUP to merge QuickBooks reports data. I’ll also show how Excel’s HYPERLINK function can generate clickable links to your email software for sending new messages.

Continue reading article at www.sleeter.com

 

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Microsoft Excel Text Extraction Techniques

by David Ringstrom,CPA
 
Reports exported to Excel from QuickBooks often contain data aggregated into a single column that you may want to pare down. I’ve seen any number of folks over the years resort to manually retyping the data they want into a new column. In my experience retyping data is typically only required in special circumstances, as in most cases you can easily transform the data as needed. In this article I’ll share several Microsoft Excel text extraction techniques, including the rarely used InStrRev function.
Continue reading on www.sleeter.com.
About the author:

David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.