Home > Articles > Home & Office Computing > Other Applications

This chapter is from the book

This chapter is from the book

QuickBooks 2013 In DepthQuickBooks 2013 In Depth

Learn More Buy

This chapter is from the book

This chapter is from the book

QuickBooks 2013 In DepthQuickBooks 2013 In Depth

Learn More Buy

Managing Lists

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.

    tip.jpgTIP

    For accounts that have an account number assigned, deselecting the Use Account Numbers option does not remove the account number—it simply hides the field. For accountants, turn on the feature and assign your desired account numbers, and then turn the feature off when the file is returned to your client. When you review the file again, any accounts created since your last review will not have an account number, which makes locating them easy.

    Alternatively, accountants who use the QuickBooks Accountant 2013 software can track changes made to the chart of accounts, including additions, renames, deletions, or merges made using the Client Data Review tools. More information is available in Appendix A, “Client Data Review.”

  • 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.

note.jpgNOTE

On a report, if you see an amount reporting to an “Other” subaccount row title under a main account, it is the result of posting to the main (or parent) account rather than to the appropriate subaccount.

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.

caution.jpgCAUTION

When working in your own data, make sure your columns of data in Excel match the Add/Edit Multiple List Entries columns when cutting and pasting from Excel.

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

Figure 4.10. Use the Add/Edit Multiple Lists Entries to add or modify your lists efficiently.

tip.jpgTIP

If a sample file is not listed, select File, Close Company or File, Close Company/Logoff. In the No Company Open dialog box, select a file to practice with from the Open a Sample File drop-down list.

Practice Using the Add/Edit Multiple List Entries Feature

Learn how to use the Add/Edit Multiple List Entries feature by adding a customer record and customizing the columns of data displayed. To complete this task, open the sample data as instructed in Chapter 1.

  1. From the menu bar, select File, Open Previous Company and select the sample data file you previewed in Chapter 1.
  2. From the menu bar, select Lists, Add/Edit Multiple List Entries. In the Add/Edit Multiple List Entries dialog box that displays, the Customers list should default in the List drop-down. (If you are adding or modifying one of the other supported lists, your data fields will vary from the figures shown here.)
  3. Click the Customize Columns button on the right side. The Customize Columns dialog box displays.
  4. From the Available Columns panel on the left, select the Terms field and click Add. The Terms data field is now added to the Chosen Columns panel.
  5. Click the Move Up or Move Down button with Terms selected to reposition the location of that data column. Adding, removing, or repositioning columns can be very useful if you are copying data from an existing Excel worksheet.
  6. With your cursor, in the Chosen Columns panel, select the M.I. (middle initial) field. Click the Remove button.
  7. Click OK to close the Customize Columns dialog box.
  8. Optionally, select the View drop-down list to filter the list results or type in the Find box a specific search term to add as an additional filter to the resulting list.
  9. For practice in this sample data, right-click and select Insert Line. Complete the data entry fields as displayed for this practice adding a customer record. Or, if your own business’s customer list is already in an Excel spreadsheet, you may also use Excel’s cut and paste functionality to add the customer details from an existing Excel spreadsheet. From the grid, you can right-click with your cursor for other functionality. From this menu, you can use Copy Down, Duplicate Row, Clear Column, and other useful features.
  10. Click Save Changes. QuickBooks indicates the number of customer record(s) that have been saved. Click OK. If you are modifying several records, you might want to save your work often.
  11. Click Close to close the Add/Edit Multiple List Entries dialog box when you have completed your additions or modifications.

tip.jpgTIP

Using the Add/Edit Multiple List Entries dialog box for importing via Excel’s cut and paste can be an efficient way to get started and to update existing lists.

caution.jpgCAUTION

On the Customers list, the Name field is the “look-up” or internal name you give a customer. It can be the same as the Company Name field or different. The customer invoice will display the name you type in the Company Name field, so be sure not leave it blank.

If you type a name in the Name column that already exists, QuickBooks highlights the duplicate name and requires you to amend the name. You cannot have the same Name field duplicated on a single list or other lists. For example, a customer named ABC Services cannot exist more than once on the customer list and cannot also be a vendor with the same spelling. If you have a customer that is also a vendor, modify the vendor listing to ABC Services-V and put the actual name of the vendor in the Company Name field and Print on Check As field in the vendor record.

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:

  1. From the menu bar select File, Utilities, Import, Excel Files.
  2. 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.
  3. 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

    Figure 4.11. Choose which type of list you want to add information to using this utility.

  4. Click to select the type of data you want to add to QuickBooks. You can choose from
    • Customers
    • Vendors
    • Products I Sell
  5. 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.

    arrow.jpg For more information, see “Creating a QuickBooks Backup,” p. 619.

    tip.jpgTIP

    If you choose, you can save this spreadsheet as you work. It will have an Excel file extension of .xlsm, a Macro-Enabled Workbook. When you are ready to return to finish adding to the data, return to step 1 of this section, click the Find It Now button, and then browse to the stored workbook.

  6. 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

    Figure 4.12. Preformatted spreadsheets make entering data with this utility easy.

  7. 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.
  8. 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.

    note.jpgNOTE

    Although manually adding or copying data from Excel are both options using this utility, you may find that working with the Add/Edit Multiple List Entries (from the prior section) is a more efficient method for this type of entry.

  9. If you selected Add My Data Now, you will be prompted to Save & Add My Data Now or I’ll Add My Data Later.
  10. 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.
  11. Select the View Customer List button to view the data that was imported.
  12. 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.

arrow.jpg For more information, see “Express Start,” p. 35.

tip.jpgTIP

Accounting professionals have another method for creating a new file using a previously set-up chart of accounts. Create a template QuickBooks file with the chart of accounts and other settings as desired. If you’re using QuickBooks Accountant, from the menu bar select File, New Company, From Existing Company File, and then follow the onscreen prompts.

Follow these instructions to import the chart of accounts from an Excel spreadsheet.

  1. From the menu bar select, File, Utilities, Import, Excel Files. The Import a File dialog displays with the Set Up Import tab selected.
  2. In the Select a File pane, browse to the location of your saved Excel spreadsheet that contains the chart of accounts.
  3. 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

    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.

  4. 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.
  5. Type a name into the Mapping Name field. This enables you to select this mapping for use again.

    tip.jpgTIP

    It helps when creating the Excel worksheet to type data labels that will be easy to match to the QuickBooks fields.

  6. For the Import Type, in this example select Account.
  7. 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

    Figure 4.14. When using the Advanced Import, map the columns of an existing spreadsheet to the fields in QuickBooks.

  8. Click Save to store this mapping for future use. You are returned to the Import a File dialog box.
  9. 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.
  10. 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
  11. 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

    Figure 4.15. Preview the import, and view any rows with errors.

  12. 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.
  13. 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.
  14. 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:

  1. 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.
  2. From the open QuickBooks file, on the menu bar select, File, Utilities, Export, Lists to IIF Files.
  3. 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

    Figure 4.16. The Export dialog box shows choices of lists available for export.

  4. 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.

tip.jpgTIP

Creating individual IIF files for each of the master lists you want to export is preferable to creating one combined file. In other words, create one file for your chart of accounts separate from a file for vendors or customers. This way, if one list has trouble importing, it won’t prevent the other lists from importing.

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

Figure 4.17. A chart of accounts IIF format file that can be imported into another QuickBooks file.

note.jpgNOTE

The Add/Edit Multiple List Entries feature does not include modifying the chart of accounts list. To import the chart of accounts, see the section “Advanced Import from an Existing Excel Worksheet” in this chapter.

To import the saved IIF file into a new QuickBooks file, follow these steps:

  1. 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.)

    caution.jpgCAUTION

    The IIF file format is a tab-delimited text file with an extension of .iif. Be sure to keep this format when saving your changes; do not save as an .xls or .xlsx Excel file type.

  2. From the menu bar select, File, Utilities, Import, IIF Files.
  3. In the Import dialog box, browse to the location of the stored IIF formatted file.
  4. With your cursor, select the file and click Open.
  5. 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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020