If you are moving from Quicken to the Excel Checkbook and you’d like to bring your existing data from Quicken, here are the steps. It’s best if you already have some comfort with Excel and entering basic formulas. We’ll start with a summary.
Summary
- Export your transactions from Quicken to a CSV file format or Tab-delimited format
- Open that CSV file using Microsoft Excel, and reformat the data so that it has a similar column layout as the Excel checkbook template. This step includes the need to put deposits into a separate column since Quicken puts all amounts in one column.
- Open the Excel Checkbook template and set the opening balance in cell G6
- Copy transactions from the Quicken CSV file and paste them into the checkbook template, starting in cell A7
- Copy the category values from the Quicken CSV file and paste them into the checkbook template under the Sub-Category column
- Review the category values and update them as needed to match with prior Quicken categories
Exporting Transactions from Quicken
According to online posts, the way to export your transaction data is to be in the account register, and then select the PRINT function and select “Export To” and select the “tab delimited” option. Other online help articles say that you can export your data by visiting the Quicken report that shows your transactions, and then click the button for Export Data. Please note that Quicken displays the Export Data button only for reports, not graphs.
When you open the exported data file in Microsoft Excel, it should look something like the following.
Reformatting Notes
- Insert a new column between Date and Payee and call it TRX Type.
- Insert another new column before Payee called Check No.
- Insert a new column before Category, and call it Withdrawal
- Insert another new column and call it Deposit
When you’re done, your spreadsheet should look something like this:
If your spreadsheet looks like the sample above with the Withdrawal column in “E”, enter the following formula into the E2 cell:
=IF(H2<0, H2 * -1, "")After doing so, copy that formula all the way down the sheet. The quickest way to do so is by pointing your mouse on cell E2 but at the bottom-right corner until you see a black plus symbol. Then double-click.
Enter this formula into the F2 cell:
=IF(H2>0, H2, "")Copy that formula down the sheet just like you did for the E2 formula.
At this point, your spreadsheet should look something like this:
Before moving on, I recommend that you sort your spreadsheet on the Account column, since you’ll probably want to copy the transactions for each account into different register worksheets in the Excel Checkbook. But you are now ready to copy & paste your transactions from this Quicken file into your Excel Checkbook register. Be sure to use “Paste as Values” when you paste into the Excel Checkbook register. See this video for a helpful guide on copying & pasting transactions. You can skip ahead to the 2:55 minute mark.