Currently the Xero interface for expenses is pretty poor. It’s not in order of date of expense, can’t be filtered, and it’s hard to match up recurring payments. It’s much easier to view in Excel, but Xero doesn’t make this easy either. You’d expect there’d be a “export expenses” button but as of the writing of this guide that doesn’t seem to exist.
To get detailed expense reports as an administrator out of Xero into Microsoft Excel, follow these steps:
-
Navigate to Accounting -> Reports
-
Under Accounting select More Reports then Detailed Account Transactions
-
Change the date range to the dates you would like to export for
-
On the bottom right use the Export menu to export to Excel
The resulting spreadsheet will have all account transactions, so you need to filter from there by filtering Expense Claims. Use the “Format as Table” function to allow filtering and ordering of the table.
Three handy columns can split up the transaction field into Contact, Spent at, and Description. Add the following cells:
- L4: Contact
-
L5:
=LEFT(E5,FIND("-",E5)-2) - M4: Spent at
-
M5:
=RIGHT(LEFT(E5,FIND("-",E5,LEN(L5)+3)-2),LEN(LEFT(E5,FIND("-",E5,LEN(L5)+3)-2))-LEN(L5)-3) - N4: Description
- N5:
=RIGHT(E5,LEN(E5)-LEN(L5)-LEN(M5)-6)
You can then highlight L5:N5 and then use the bottom right tab to drag down to the end of the report. This way you can get expenses for a specific person or order by date.