- You can quickly create records in QuickBooks by copying and pasting data from a spreadsheet into the Add/Edit Multiple List Entries window. To really speed things up rearrange the columns in QuickBooks so they match what's in your spreadsheet. Let's start by looking at the spreadsheet. Here I have an Excel spreadsheet with information about new customers. The columns start with a customer ID, the company name, a couple of phone numbers, address fields, contact information, and finally Customer Type.
Well, let's see what QuickBooks has for its columns. I'm gonna jump over to QuickBooks. To open the Add/Edit Multiple List Entries window head up to the Menu bar, click Lists, at the bottom of the drop-down menu choose Add/Edit Multiple List Entries. That opens up the window and the good news is it's set to customers. So, let's go ahead and look at what columns we have in this table. It starts with Name which is the same as a customer ID, so that's good so far.
And then the Company Name. At that point, things start to change. We've got information about the contact then some phone numbers, then address fields. Bottom line, the columns don't match what's in the spreadsheet. Now, you can rearrange those columns in either place, Excel or Quickbooks. But let's say that the spreadsheet is something that you use to put in new customer information. So, we're gonna change the columns in QuickBooks to match the spreadsheet that way it'll be ready the next time you wanna copy in some customer records.
Well, to do that head up to the top-right corner of this window and click Customize Columns. The Customize Columns dialogue box has two lists. Chosen Columns on the right are the columns that are already in place in the table. On the left are Available Columns. So, if you need to add some they are there waiting for you. We have to make some changes to make the QuickBooks columns match what's in the spreadsheet. The first thing is we're gonna remove some fields that we don't need.
In the Chosen Columns list I'm gonna start with Mr/Ms/. That's not in this spreadsheet. Select the field and click Remove. We're also gonna ditch the middle initial. Select that field and click Remove again. We don't need Alternate Phone. Now, you've got this down by now, right? Select the field and click Remove. Do that for Bill To 2 and finally scroll down to the bottom and get rid of CC Email.
Select it and click Remove. The next step, we have to move some fields around. The name has to be after the address. I'm gonna start with First Name. Select that field and in the middle between the two lists click Move Down and click it repeatedly until First Name comes after the Bill To 5 field. Now, rinse and repeat for Last Name. Select that and go ahead and click Move Down until the field is after First Name.
One last change. I wanna add the Customer Type to the end of the list. To do that, I'm gonna select Email, which is the last field, and then I'm gonna head over to the left side and choose Customer Type in Available Columns. Well, I wanna add this field to the chosen columns list. You guessed it, click the Add button in the middle. Now, the fields are the same in both places. Click OK to close the Customize Columns dialogue box.
And there's the table in it's full glory with all the fields in the same order as the spreadsheet. Finally, we're ready to copy data. Let's jump back over to Excel. What I wanna bring in are the records for Avulon through Delish Food. To do that I'm gonna drag over the cells that I wanna copy and paste. I'm gonna start with the Avulon customer ID and drag down the four rows and then I start dragging over until I've selected all of the cells with values for those four customers.
Just like in other programs, press control + C to copy this data to the clipboard. Let's jump back over to QuickBooks. Drag the vertical scroll-bar on the right side of the window down to the very bottom so you can see the first blank row in the table. And click the first blank cell which is the Name field. Now, we wanna paste that data in. Press control + V just like you would in any other program.
And there are the four records. If I scroll in the horizontal scrollbar there's no red text, no error messages. Everything looks good. These records are ready to save. That's where the Save Changes button comes in. Click that and the Record(s) Saved message box appears, telling you 4 customer records have been saved. Click OK. Then click Close at the bottom-right corner of the window and that closes the Add/Edit Multiple List Entries window.
That's all there is to it. When the columns in both places match up copying data in QuickBooks only takes a couple of clicks.
- Adding, hiding, and sorting list entries
- Changing fields in record windows
- Finding info quickly
- Numbering transactions
- Adding logos to checks
- Applying discounts and credits to bills
- Invoicing cost overruns or fixed-price jobs
- Receiving payments
- Using journal entries
- Saving time on common tasks
Skill Level Intermediate
Q: This course was updated on 12/08/2017. What changed?
A: The following topics were updated: using Find and Search, restarting transaction numbering, sorting bills, and tracking most recent year’s earnings and earlier retained earnings separately.