Barba CFO can provide the financial management that is necessary to run your small or medium-sized business. Our wide variety of services range from personalized projects to bookkeeping and CFO for hire services. For a FREE CONSULTATION, or any questions, please leave your contact information here to receive a quick response.
Have you ever wanted to look at your QuickBooks data in a different way, but the reports available in QuickBooks don’t quite fit the bill? If so, you’ve probably exported your report into Excel and then performed your analysis or made some formatting changes to the report. This can be fraught with frustration, errors and take a lot of precious time. And while QuickBooks has made strides over the years in providing more and better reports, for most users, including small businesses, there is usually a need to manipulate the data and show it in a way not available in the predefined reports or even by using custom reports in QuickBooks.
Excel is one very helpful tool for this need. My favorite report for working with transaction data in Excel (and therefore the best) is the Transaction List By Date report. I’m going to show you how to create this report, making it Excel friendly, so that you can begin to analyze and present your data in new ways with less effort, frustration and errors.
To Do In QuickBooks
To access this report in most recent versions of QuickBooks, do the following:
Go to Reports on the menu bar, choose Accountant & Taxes from the drop down list and then Transaction List By Date.
Once the report is displayed, you will need to make some changes to get it ready for Excel as follows:
Customize Your Report
Click the Customize Report button at the top right of the report window.
Now with the Modify Report:Transaction List By Date dialog box open, you will notice that there are 4 tabs along the top: Display; Filters; Headers/Footers; Fonts & Numbers.
Display tab
While in the Display tab, there are 2 sections REPORT DATE RANGE and COLUMNS.
In the REPORT DATE RANGE section Select the date range for your report using either the drop down for pre-defined dates or a custom date range by choosing your starting and ending date in the From and To date boxes.
In the COLUMNS section, you will choose the fields from QuickBooks that you want to show in the columns on your report. You can ignore the Sort options to the right of the column list, as you can and will do this in Excel. In the column list, you can check and uncheck the columns you want, but there are some columns I almost always check and uncheck. Here a list of some of the most common columns selections I typically make. This list is not all-inclusive, but only suggestions. You can choose whichever columns you want.
Filters tab
Now click on the Filters tab. The only thing I would recommend doing here is making sure that the Detail Level is set to All to ensure that you see all debits and credits for each transaction – if you choose one of the other 2 options for Detail Level, your debits won’t equal your credits, so don’t do this.
Finally, I would not recommend doing anything else here as you already selected your date range on the Display tab and any additional filtering can be done in Excel. I find it’s better to get all the data and then exclude data you don’t need once you get it into Excel.
Other tabs
For the remaining 2 tabs, Headers/Footers and Fonts & Numbers, you don’t need to make any changes there that you can’t make in Excel, so now you are ready to click the OK button and see the results of your report.
Memorize Your Report
If the report has the columns you want and you think it’s ready for exporting to Excel, do one more thing before exporting the data – Memorize the report for future use. Do this by clicking the Memorize button along the middle of the top of the report window. In the Memorize Report dialog box, name your report with a name you will remember and save it in main Memorized Report List or in a Memorized Report Group.
Export Your Report
Now you are ready to export your report data to Excel. Do this by clicking on the Excel button at the top of the report window and selecting Create New Worksheet from the dropdown. When the Send Report to Excel dialog box comes up the radio buttons for Create new worksheet and in new workbook are selected by default. For the purposes of this instruction, leave those selections.
Advanced… button
Finally, click on the Advanced… button toward the bottom right of the dialog box. This is where you can make selections to make the data better for use in Excel. When the Advanced Excel Options dialog box comes up, it will display the last used settings for exporting to Excel, so be sure you check this each time you run this report as it may have been changed by another user when they were creating a report.
In the QuickBooks Options: section, uncheck Space between columns. This will make it easier to create a table in Excel and save you time deleting blank columns.
In the Excel Options: section, uncheck Freeze panes (keep headers and labels visible). This will allow you to set your own panes later, although when you create a table in Excel, the table headers move into the column frame when you scroll below the first page anyway, so in most cases, it won’t be necessary. Also, uncheck Include QuickBooks Export Guide worksheet with helpful advice. This will prevent you from having an extra worksheet to delete or move around later.
In the Printing options: section, in most cases under Show report header, it’s only necessary to select the radio button for On printed report only. If you have multiple companies that you will be exporting, then it might be helpful to choose the other option On printed report and screen, but for most situations this will add some formatting steps in Excel that aren’t necessary.
Finish Export
Click the OK button. Now you are finally ready to export your report data. Click the Export button. Depending on the size of the report you are exporting it may take a few minutes to complete the export.
To Do In Excel
When the report is fully exported to Excel, the report will automatically pop up in Excel or the Excel icon will be blinking on your task bar, so click on it.
Clean Up
Now that you have your data in Excel, you are almost ready to start working with your data. But before that, there are a few more things you will do to get your data ready for analysis. First lets, clean up any blank rows and columns. If you used the same setting recommended here, you will need to delete the first column of your worksheet as the only data there will be in rows 2 and the last row of your report indicating the date range you specified. You don’t need this as each transaction in your data has a date field there already. So go ahead and delete column A by clicking on the A at the top of the column, which selects the whole column, and leaving the mouse there, right click the mouse and choose Delete from the list of options. Now remove row 2 of the worksheet by clicking on the 2 to the right of the row, right click and choose Delete.
Excel Tables
Now you are ready to create a table for your data. Tables in Excel are a very powerful tool which allow you to more easily create pivot table and charts, and filter and sort data and perform meaningful analysis. While there are a few limitations with Tables, which I will get into in a later post, overall, tables are the feature I use most in Excel because it allows me to do so many different things from just one table of data.
Insert Your Table
To create your table, make sure you are in any cell in the report data (it doesn’t matter where as long as you are somewhere in the data and not outside of it). On the menu at the top click the INSERT tab. If you don’t see this tab in your Excel view, right click on any menu option you do see and select Customize the Ribbon from the dropdown. In the Excel Options window, check off the Insert box under Main tabs list to the right. Click OK. Now click the INSERT tab. Click on Table. In the dialog box, the data range for your table should automatically be selected, if you were in any cell within the table when you clicked on Table. Make sure the My table has headers box is checked and click OK.
Voila! You now have a table and you are almost ready to start analyzing your data.
Design and Format Your Table
Notice that with the addition of the table a new menu option becomes available DESIGN. Click on that menu option and check the Total Row box in the Table style options section (near the middle of the DESIGN ribbon). Checking this box will automatically bring you to the last row of your data and add a number to the last row in the last column of your table. Depending on the data in that column the number could represent a sum of the data in the column or a count of the number of items in the column (if the last column is a text field, for example). You want to get a sum total for your Amount column, so click in the last row of that column. You will see a down arrow pop up indicating that you can choose from a list of options. Click the arrow and choose Sum from the list of options. Assuming your transaction debits equal your credits this total should display as 0.00.
What Next?
This is where the fun begins. While there are many other formatting things you might want to do as you become more practiced with Excel tables, essentially, you are ready to start analyzing. Go ahead and create pivot tables, filter and sort columns and create charts all from this same table of data. I will give you some helpful types of analysis you can do from this data in future posts, but if you want help customizing some analytics for your business before then or need help with this or other reporting and analysis features in QuickBooks or Excel, contact Barba CFO. Each of our Associates has decades of experience with both QuickBooks and Excel and can help you make sense of your information so you can make sound business decisions whatever your business’s goals may be.
Happy analyzing!
