Importing Other Excel Data into Checkbook for Excel

Checkbook for Excel can import Quicken Interchange Format (.QIF) files directly using the “Import Quicken” function on the Checkbook Toolbar. It can also import data from earlier versions of the program using the "Import Data from a Previous Version" button on the Welcome screen. However, many users would like to import data from other Excel files, text files etc. Although the program will not import other Excel files automatically, if you can open the file in Excel, you can use this procedure to copy and paste your data into a Checkbook for Excel Ledger.

In order to use this procedure, you will need to request the password to unprotect the Ledger sheet. Only registered users of the software will be given the password. If you are a registered user, simply e-mail us at support@djicomputer.com to request the password. If you are not a registered user, it will be necessary to register the product first. You will also need to have a basic working knowledge of Excel also.

  1. Make sure at least one transaction has been entered into the Ledger in addition to the original balance in the first row of the ledger. If one has not already been entered, enter a new transaction before proceeding.
  2. From the Excel menu, select Tools-Protection-Unprotect sheet to unprotect the Ledger where you will be pasting the data. Use the password provided.
  3. Open the file that you want to copy from or activate it if it is already open. Ideally, the data should be in columnar format similar to Checkbook for Excel.
  4. Copy all the transaction names and then paste them into the “Pay To/Deposit From” column in the Checkbook Ledger.
  5. Next, copy all the transaction dates and then paste them into the Date column in the Checkbook Ledger. If there are no dates to copy, you will need to enter a valid date for each transaction row that you create. Enter one date and then copy and paste it to all the other transactions.
  6. Next, copy all the check numbers or transaction types and then paste them into the Check column in the Checkbook Ledger. This column must have an entry that matches one of the following:  a valid number corresponding to the check number, a “+” sign for a deposit, a “-“ sign for a withdrawal, “Debit Card” for a debit card or credit card transaction, “ATM” for an ATM withdrawal. When in doubt, just use the “-“ sign for withdrawal and the “+” sign for deposit. The column must have an entry for each transaction. You can copy and paste from one cell to another to fill all the rows.
  7. Skip the Print column for now.
  8. Next, copy the expense or income categories and then paste them into the Category column in the Checkbook Ledger. The category column can be left blank if there are no categories.
  9. Skip the check mark column for now.
  10. For the amount column, check transactions, debit cards, ATM, and withdrawal transaction must be entered as negative numbers and deposits must be entered as positive numbers. If the file you are copying from does not have negative numbers as required, you can simply create another column with a formula that multiplies the amounts by –1 to convert them to negative numbers. Once you are sure they have the right signs, copy the transaction amounts and then paste them into the Amount column in the Checkbook Ledger.
  11. Next, go to the Balance column in the row just above where you started pasting your data and copy the formula down the page to fill the Balance column for each of the transactions you just pasted.
  12. If you wish to copy and paste address information or transaction category information, complete the section below titled “Importing Additional Information” before continuing and then return to this step. Otherwise, you have completed pasting your data and you can continue with the next step.
  13. Now that you have pasted all your data, review the Checkbook Ledger and make sure that there are no blank rows in data area of the Ledger and that there is an entry in the Date, Check, Payment To/Deposit From, Amount and Balance columns for each transaction that was pasted.
  14. Once you are sure your data has been properly pasted, enter a new transaction using the normal Checkbook for Excel function. Now delete the transaction that you just entered using the “Delete Transaction” function. This should fix the ledger formatting and the Checkbook Ledger should now show all your transactions that you pasted with the proper row and cell formatting. It will also automatically protect the Ledger sheet.
  15. You can now use the “Mark Checks to Print” function to automatically mark any checks you want to print. See the Checkbook for Excel help file for more details on how to use this function.
  16. You can also use the Balance/Reconcile function to mark transactions as cleared in the check mark column. See the Checkbook for Excel help file for more details on how to use this function.

Importing Additional Information

  1. Display the row and column headers by selecting Tools-Options to display the Options dialog box. In the Window Options area of the View tab, make sure the “Row & column headers” check box is checked.
  2. Unhide columns I through AR and using the methods described above, copy and paste the data the file you are importing into the appropriate columns. Make sure any split amounts have the proper sign, negative numbers for debits and positive numbers for deposits.
  3. Once you have pasted all the data, hide columns I through AR once again and return to Step 14 above. You can also hide the row and column heading if you like.

 Return to Checkbook for Excel Home Page

Return to DJI Computer Solutions Home Page