While I love the reporting features in QuickBooks, sometimes I still want to do more with it. While many of you have probably exported reports to Excel before, here are some features you may not have used.
View What YOU Need
A feature often overlooked is the Advanced Excel Options. Sounds intimidating for some, but they’re not hard and can be very helpful.
After you run your report, click the Excel button (your version may say Export).
Indicate that you want to create a new worksheet and click Advanced. This window opens:
Figure 1: The Advanced Excel Options window displays the formatting tools you can carry over from QuickBooks and the features in Excel that you want to be active.
There will be options already checked, but I want to take a look at 3 in particular.
Freeze Panes – For large spreadsheets, this helps you know which column and row you’re working on by allowing you to keep the column headings and the row descriptors visible no matter how far down or to the right you scroll. If not turned on, you could lose track of where you are in a large spreadsheet!
Auto Outline allows collapsing and expanding. When your report opens in Excel spreadsheet, you’ll notice that there is a series of vertical lines to the left of your data, and a group of numbers that corresponds to them running above horizontally.
Figure 2: Excel’s Auto Outline feature adds tools to the left of your data that let you collapse and expand subsections.
To collapse a section so that only the totals show, click on the minus (-) sign next to the line that should remain (in this example, it’s Total Checking/Savings). Do the same for Total Accounts Receivable and Total Other Current Assets. Then scroll down and do the same thing for the other asset subtotals. Here’s what you’ll see:
Figure 3: As you can see, the minus (-) signs have turned into plus (+) signs, which allows you to expand the rows back to their original states.
Auto Filter – Suppose you want to see a list of subcontractors or you want to contact customers within a particular area code. Auto Filter may be a useful tool. Before exporting to Excel, be sure the AutoFilter is turned on in the Advanced Options section. Once in Excel, you can filter this report in ways you can’t filter in QuickBooks..Each column with data will have a drop-down box, showing all the data in that particular column.
Figure 4: Notice the drop-downs for each column
When you click on the drop down(s) on which you want to filter, all data has a checkmark. If you want to see just a few, remove the check from Select All. It will remove all checkmarks you can you can easily pick and choose from any column what you want to look at. If you want most, then simply remove the checkmark next to the unwanted data. There’s even an option for blanks (it will be at the bottom of the list). So you could remove the checkmark from blanks and you would be left with all the other options. Or you can choose to show only those with blank cells.
Figure 5: Each drop-down contains all the data in that column. You can click on any of the drop-down arrows and select/de-select the options you want
If you click on Text Filters, you get several additional ways to narrow down the data that aren’t available in QuickBooks. So if you wanted to look just Subcontractors or a particular area code, you could select Begins With and enter your text.
Figure 6: Note the different ways to filter text
This feature becomes even more powerful when you use filters in more than one column.
So next time you think about exporting a report out to QuickBooks, think about these 3 options and see if they don’t make it easier for you. Contact our office if you would like assistance.