You have learned about some of the more important lists to set up in your QuickBooks file. This section shares details on managing these lists, including preference settings that can help you be more efficient and accurate with your daily data entry.
This section also provides you with multiple options for importing your lists (but not transactions) into your new QuickBooks file and managing these lists after they are in QuickBooks. When importing your lists, you can select from multiple methods, including the following:
- Adding or modifying the list directly in QuickBooks, one item at a time.
- Adding or modifying your lists using Add/Edit Multiple List Entries, or adding or modifying multiple records at a time in a single list.
- Using the Advanced Import tool in QuickBooks.
- Importing a list from another QuickBooks file using the IIF (Intuit Interchange File) format.
If you are starting with a new QuickBooks file, you might first want to review the preference settings that affect the chart of accounts.
Chart of Account Preferences
Setting preferences in QuickBooks can help you be more accurate and efficient with your data entry. In Chapter 2, “Getting Around QuickBooks,” you learned about Company Preferences that are global for all users and My Preferences that affect only the settings for the currently logged-in user.
Using specific preferences, you can modify how many of the QuickBooks features work. To do so, from the menu bar, select Edit, Preferences and select the Accounting—Company Preferences tab as previously displayed in Figure 4.6.
Here is a list of the preferences found in the Accounting—Company Preferences tab that affect the chart of accounts:
Use Account Numbers—Selecting this option turns on the data field that holds a numeric assignment for each chart of accounts. By default, this feature is not selected in a newly created QuickBooks file.
- Show Lowest Subaccount Only—You can select this preference only if you selected the Use Account Numbers checkbox and if each account has been assigned an account number. If you have created a subaccount listing under a main (parent) listing, when you type the subaccount name, only the subaccount name and account number will display in transactions. Without the feature selected, when you type a subaccount name on an expense transaction, you will see both the main account name and subaccount name.
- Require Accounts—By default, this feature is selected in a newly created QuickBooks file. If this feature is not selected, any transactions saved without selecting an account will be posted to an automatically created uncategorized income or uncategorized expense account. This process follows the rule that there must always be a debit and credit side to each transaction. Fortunately, you do not have to know how to post a debit or credit because QuickBooks does this thinking for you with each transaction.
Add/Edit Multiple List Entries
Adding or modifying several entries in a single QuickBooks list can be a daunting task when you have to work with one list entry at a time. In Chapter 1, you might have created your lists using the QuickBooks Setup to add the people you do business with, the products or services you sell, and your bank accounts.
With the Add/Edit Multiple List Entries feature (see Figure 4.10), you can add to or modify customer or vendor records. You can also add or modify your service, inventory, and non-inventory items. Using this feature can save you precious time, particularly when you want to add or make changes to multiple entries on a list all at once.
Figure 4.10. Use the Add/Edit Multiple Lists Entries to add or modify your lists efficiently.
Add Your Excel Data to QuickBooks Utility
Lists can also be added to QuickBooks for Vendors, Customers, and Products You Sell (Items) directly from a ready-made Excel spreadsheet using the Add Your Excel Data to QuickBooks utility. If you choose to use the Advanced (Excel) Import feature of this utility, you can also import a chart of accounts list, something you cannot do using the Add/Edit Multiple Lists Entries feature.
Enter Data into Preformatted Spreadsheets
Follow these steps to import lists using the preformatted Excel spreadsheets included with this utility:
- From the menu bar select File, Utilities, Import, Excel Files.
- An information message may display regarding the Add/Edit Multiple List Entries feature discussed in the prior section. Optionally, place a checkmark in the Do Not Display This Message in the Future box.
- Click No to continue with using the Add Your Excel Data utility. The Add Your Excel Data to QuickBooks Wizard displays, shown in Figure 4.11.
Figure 4.11. Choose which type of list you want to add information to using this utility.
- Click to select the type of data you want to add to QuickBooks. You can choose from
- Products I Sell
- In this example, click Customers. QuickBooks might display an information message about not being able to undo this import. It is prudent to make a backup of your data file before proceeding. Click Yes.
For more information, see “Creating a QuickBooks Backup,” p. 619.
- Optionally, click the Show or Hide Coach Tips and Show or Hide Detailed Instructions. Both provide onscreen details about working with this preformatted spreadsheet. See Figure 4.12.
Figure 4.12. Preformatted spreadsheets make entering data with this utility easy.
- Begin adding data to the spreadsheet using one of these two methods:
- Copying and pasting from an existing Excel worksheet.
- Manually type your data into the spreadsheet.
- When your work is complete, you have the following options:
- Save the workbook and later open it by returning to step 1, selecting the option to Find It Now, and browsing to the stored file.
- Click the Add My Data Now button.
- If you selected Add My Data Now, you will be prompted to Save & Add My Data Now or I’ll Add My Data Later.
- If you selected Save & Add My Data Now you will be prompted to select a location to store the file. Click Save. QuickBooks provides confirmation listing the number of records that were imported successfully.
- Select the View Customer List button to view the data that was imported.
- Click Close to leave the Add Your Excel Data to QuickBooks.
This utility has been available for many years and will help you get your lists quickly into a new QuickBooks file. I personally prefer working with the Add/Edit Multiple List Entries dialog because I can arrange the columns of data to match my existing spreadsheet I might be copying from.
Advanced Import from an Existing Excel Worksheet
The advantage to using the Advanced Import is the capability to import a chart of accounts list into a new QuickBooks file from an existing Excel worksheet. This is a common practice among accounting professionals. The other methods of importing discussed in this chapter so far do not offer the capability to import one of the most important lists, the chart of accounts.
However, most users will not necessarily need to import this list. When you use the Express Start feature for creating a new QuickBooks file, QuickBooks automatically creates a chart of account list based on the industry type you selected during that process.
For more information, see “Express Start,” p. 35.
Follow these instructions to import the chart of accounts from an Excel spreadsheet.
- From the menu bar select, File, Utilities, Import, Excel Files. The Import a File dialog displays with the Set Up Import tab selected.
- In the Select a File pane, browse to the location of your saved Excel spreadsheet that contains the chart of accounts.
- If there are multiple sheets in the workbook, select the appropriate sheet to use from Choose a Sheet in This Excel Workbook, as shown in Figure 4.13.
Figure 4.13. The Advanced Import is a useful utility if you already have your lists in an Excel worksheet. This utility does not require that you copy and paste the details.
- If this is your first time using this utility, in the Data Mapping pane, select Add New in the Choose a Mapping drop-down. The Mappings dialog box displays.
- Type a name into the Mapping Name field. This enables you to select this mapping for use again.
- For the Import Type, in this example select Account.
- The fields available in QuickBooks will be listed in the left column (see Figure 4.14). The fields in your selected spreadsheet will be displayed in the Import Data pane on the right. From the drop-down, select the matching field.
Figure 4.14. When using the Advanced Import, map the columns of an existing spreadsheet to the fields in QuickBooks.
- Click Save to store this mapping for future use. You are returned to the Import a File dialog box.
- Click Preview to see the results of what the import will be prior to completing this step. Any errors in the import file will have the word Error displayed in red text. Click any row with an error, and QuickBooks details the reason so you can fix it before importing.
- Select how to handle errors by choosing from the following:
- Import Rows with Errors and Leave Error Fields Blank
- Do Not Import Rows with Errors
- Click Import to complete the process. Or click OK to close the Preview dialog box, and Cancel to not make any changes. See Figure 4.15.
Figure 4.15. Preview the import, and view any rows with errors.
- If you selected Import, QuickBooks displays an information dialog recommending that you back up your data prior to the import. The import process cannot be undone. Click Yes to continue the import, or No to make a data backup first.
- If you selected Yes to the Import message, QuickBooks confirms the records that were imported. Select Save to save the Error Log (browse to a location to store the file) or Don’t Save.
- To view the chart of accounts that was imported, from the menu bar select Lists, Chart of Accounts.
With QuickBooks 2013 you have several options for importing your lists. Getting started using QuickBooks is fast and easy. The next section details another method you may choose to use, especially if you are choosing to copy lists from one QuickBooks file to another.
Importing an Intuit Interchange Format File (IIF)
The term Intuit Interchange Format (IIF) refers to data exchange functionality that has been around for some time. It is a method for exporting lists from one QuickBooks file and importing these lists into a new QuickBooks file. The process creates a tab-delimited value format file with the extension of .iif. You can view and edit this file using Excel.
The most common use for this tool is to export lists from one QuickBooks data file to a new QuickBooks data file. The process is easy and relatively error free. Other uses for the tool include transaction imports. This book does not cover the topic of transaction imports using IIF formatted files; however, you can find more information about this utility by typing IIF into the search field at www.quickbooks.com/support.
The IIF format is a preferred and easy method to use if you already have a QuickBooks file with a chart of accounts (or other lists) that you want to replicate in another file.
The only disadvantage to working with an IIF format file is all the extra information that is in the text file, making it awkward to edit or add to the existing information.
To export an IIF-formatted chart of accounts file from an existing QuickBooks file, follow these steps:
- From the menu bar select, File, Open or Restore Company to open the QuickBooks file that has the chart of accounts (or other lists) you want to export and duplicate in another file.
- From the open QuickBooks file, on the menu bar select, File, Utilities, Export, Lists to IIF Files.
- In the Export dialog box, select the Chart of Accounts checkbox (or other type of list you want to export), as shown in Figure 4.16.
Figure 4.16. The Export dialog box shows choices of lists available for export.
- Click OK. You will be prompted to provide a filename and to save the file. Remember the location you stored the file in; you will need to browse to the file to import into another QuickBooks file.
Figure 4.17 shows the exported QuickBooks chart of accounts in the IIF format in an Excel workbook. You can see it is not as user friendly as the Add/Edit Multiple List Entries dialog box discussed previously.
Figure 4.17. A chart of accounts IIF format file that can be imported into another QuickBooks file.
To import the saved IIF file into a new QuickBooks file, follow these steps:
- From the menu bar, select File, Open or Restore Company, and select the QuickBooks file you want to import the previously exported list into.
If you have not already created your new file select, File, New, and follow the prompts. (See Chapter 1 for more information.)
- From the menu bar select, File, Utilities, Import, IIF Files.
- In the Import dialog box, browse to the location of the stored IIF formatted file.
- With your cursor, select the file and click Open.
- QuickBooks then imports the IIF formatted file into the QuickBooks data file. Click OK to close the Your Data Has Been Imported message box.
Now that you have your new data file with new lists from another file, you are ready to begin entering transactions. Think of all the time you saved by not having to manually create each list item in the new file.