At Altruic Advisors, we’re big fans of QuickBooks Online for nonprofit organizations. QBO comes with many built-in sorting and filtering options for reports, which can help you view your financial data in different ways. But sometimes, you may need even more reporting options to fit your organization’s needs.
For example, let’s say your mission is to provide free educational workshops for parents. Your organization contracts with several different instructors to lead these workshops. When you pay these instructors for their services, you always include the workshop date and the name of the workshop in the expense description.
In QuickBooks Online, you can view a report of all your contractor payments. You can easily sort this report by date of payment or by instructor name. But what if you occasionally need to sort it by the workshop date or workshop name instead? One easy way to do this is to export the QBO report to Excel, and then use the Text-to-Columns feature to customize your data columns.
Using Text-to-Columns
From QuickBooks Online, click the Export icon in the top-right corner of the report and choose “Export to Excel.”
Open the report in Excel. You’ll see that the workshop name and workshop date are combined within a single column on the spreadsheet.
To split this data set, first insert a new, blank column to the right. This column will hold the second half of your data.
Select the cells you want to split, then go to the Data tab and click “Text to Columns.”
In the pop-up window, choose the “Delimited” option.
On the next screen, choose how you want to separate your data. The standard options are tabs, semicolons, commas, or space. But you can choose any other character instead. In this example, the date portion of the description is separated by parentheses.
On the third and final screen, you can choose the data format for each column. In this example, we want to set the second column to a date format.
After you click “Finish,” you’ll see that your data has been separated into two columns – one for the workshop name, and one for the workshop date.
Now you can sort this spreadsheet by the name of the workshop…
…or by the date of the workshop.
Excel’s Text-to-Columns feature is a great way to separate data and enhance your reporting. If you plan to use this feature often, you may want to standardize your descriptions within QuickBooks Online. Using commas, colons, or other characters as your delimiters will make it easy to separate data into different columns.