Skip to main content
Webinar: Data-Driven Success: Streamlining Your Business Operations

Wednesday, April 12th, 2023 | 1PM ET | 10AM PT | Register Now

loader logo
Firm Operations

Microsoft Excel Tips and Tricks – Formatting a QuickBooks Export

Microsoft Excel Tips and Tricks – Formatting a QuickBooks Export - BQE Software

In my previous post, Planning Your Projects for 2015 I showed how to take a report from QuickBooks, Export it to Excel and then after some quick maneuvers around the spreadsheet, I was able to get at the total # of projects for the year, and based on that, average revenue per project. Now I want to show you some Microsoft Excel Tips and Tricks which will explain how I got the data from what it looked like when it was originally exported to Excel to what it looked like when I started the analysis.

The short answer is that QuickBooks Exports the customers and jobs in a separate column for each level (Customer:Job Level1:Job Level2:Job Level3). The Microsoft Excel tips and tricks I show you in this video will demonstrate how you can re-purpose the layout such that you have a simple linear report showing each project with all 3 levels on the same line along with the corresponding revenue for that project for the period you are reporting on. This enables you to write a simple COUNTA formula in Microsoft Excel to get a quick count on the # of projects. Then it’s an easy division (Sales / # of Projects) to get average sales (or Revenue) per project.

Microsoft Excel makes it easy to get at this information quickly. Then if you refer back to my previous post on Planning Your Projects for 2015 you can see how we start using this information in conjunction with a report in BillQuick to look for opportunities in 2015 to increase revenue without having to increase resources (ie expenses).

Taking the time to do this kind of analysis using QuickBooks, Microsoft Excel, and ultimately the kind of intelligent information that BillQuick provides us can go a long way to helping you identify opportunities to increase sales and profits while maintaining or improving efficiency. This is how you can work smarter and not harder in 2015. I hope to bring you more helpful tips along these lines to ensure that you have the best 2015 possible. Please post your comments and questions below.

Similar posts

Get notified on the latest for your industry

Be the first to know the latest insights from experts in your industry to help you master project management and deliver projects that yield delighted clients and predictable profits.