So here’s the scenario.  You want to call down a list of Leads and update them as you go.  Rather than going through the list in CRM, you would like to make edits directly in Microsoft Excel (or, possibly, you may want to provide the list to a third party to make calls using Excel and then import the results back into CRM).  There are many similar scenarios (for example, some of our clients use CRM for candidate management and they manage large lists of contacts at targeted companies that they frequently need to update in bulk – Excel is perfect for that scenario).  Using CRM 2011, records can be exported, modified and re-imported with an ease and delight never before dreamed possible!  Here’s how:

Exporting

First off, you’ll need to export the records that you wish to update.  Note that, even if you just want to create new records “in bulk” using Excel, you can start by exporting records from CRM to create a template that is easier to import later.  To export, follow the steps below:

1. Navigate to the type of record you want to export (such as Leads).
2. Select the view containing the records you want to export.
3. If necessary, use Advanced Find to modify the view to be sure it filters to the records you want and includes the columns that you want to edit in Excel.
4. In the ribbon menu, click the Export to Excel button, pictured below.

5. You will be presented with the export dialog box (below). Be sure to check the box “Make this data available for re-importing…”

6. Here comes the hard part: Click the Export button on the dialog box.
7. Next, you’ll see another dialog box inviting you to Open, Save or Cancel.  You’ll feel the temptation to click the Open button.  Fight it.  This button is evil.  Bad things happen when you click it (at least bad things are happening in beta – maybe they’ll be fixed by the time CRM 2011 is in production).  Instead, click the Save button.  This is your happy place.  Good things happen when you click the Save button.  Go to the Save button, there is peace and serenity in the Save button.  Got it?
8. Phew – you made it!  So now you just need to save your file. Dynamics CRM is very flexible in this area.  You can give your file almost any name that you can imagine.  Don’t hold back your creative spirit, give it any name at all.

Now you’re ready to move forward with making changes to the file.  Before doing so, you may want to pause here to get over your annoyance at all the extra stuff you had to read so far in order to wade through these instructions.  Hey – it’s a blog and it’s free and I’ve gotta do something to keep myself entertained while writing these things.  So assume the lotus position and let go of the anger before moving forward.

Updating

Okay, here we are, ready to make updates to records.  Actually, this is very simple.  You can just open the spreadsheet, make your edits and save it.  If you want to add records, then you can add them right to the end of the list before you save.  Yes, it’s that easy.  And if you order today, Microsoft will throw in a set of Ginsu knives!  (I’ll likely get a cease and desist from the ginsu legal department for that comment – so if I change it to “free toaster” later, you’ll know why).

Back to business!  There are a few things you’ll want to know as you make your edits.  Microsoft has really beefed up how easy it is to edit records in Excel now.  These changes will help you to avoid errors and speed your data entry.  Here’s a quick overview with a picture.  The picture really doesn’t add any value – but I’m told that pictures make people happy and words make people frown.  I want people to be happy, so I include a picture.

• Picklist fields: If you exported any picklist fields (for some unknown reason, Microsoft also calls these Option Sets now), then you will be given a dropdown list to make your selection.  This also goes for boolean fields. I don’t care who you are … that is one cool new feature!
• Numbers: Microsoft also includes “validation rules” so you can only enter numbers within the appropriate range for number fields, saving you untold thousands of hours in data entry.
• Lookups: Okay, so the news isn’t quite as exciting for lookup fields – you don’t get the handy-dandy drop-down list for lookups.  But you CAN still enter the correct text value for a lookup field and if you enter it exactly right, then it will import and set the relationship.  What’s more, if the lookup value you want is included in the Excel export, then the auto-fill feature built right into Excel will help you select the correct value.  Try to do THAT with SalesLogix 1.5!

One question I am frequently asked is: “why are the first columns missing in the Excel file?”  Actually, I’ve never been asked that question, but doesn’t it make it sound more credible when I preface my comment with that statement?  Here’s why: Dynamics CRM embeds some hidden columns containing ID numbers and revision dates.  This enables CRM to ensure that it doesn’t create duplicate records and to be sure that changes made in Excel don’t overwrite more recent changes that may have been made directly to Dynamics CRM.  Wasn’t that thoughtful of Microsoft to think about that?  One of their product managers came up with the idea for that at a corporate retreat while they were singing “cum by ya” around the campfire.  I get misty every time that I tell that story.

Re-Importing

Last step.  Only a few more paragraphs of this madness before you can be done with this posting!  Let’s get right to it.

1. Go back into CRM.
2. In the ribbon menu, there is an Import Data button.
3. Find it.
4. Click it.
5. Honestly, if you needed steps #3 and #4 to get through this process, then you really shouldn’t be importing data into anything.  Have you considered teaching at the college level instead?
6. Now browse to the file that you made the changes to and click the Next button.  No, you don’t get a picture.
7. NOW you get a picture.

8. Decide if you want to include duplicates (seriously?  why would you want to do that?  are you getting paid by the hour to remove duplicates or something?)
9. Choose an owner for new records (don’t worry, any existing records that you modified will still be owned by the same owner, this option only applies to new records).*
10. Click the OK button

Voila!  The little gerbils inside of the Dynamics CRM import engine get right to work importing your data.  Those little suckers are running pretty hard, but it still may take a while for them to finish importing all of your data.  You can click the handy-dandy link to the Imports part of the workplace area if you want to check the status of your import – or you can navigate there manually if you’re a glutton for punishment.  If any of your data is not valid, then you can check the import job to see what type of errors occurred.

* So you’re probably wondering what happens if you export the owner name and change it and then import the records.  Will CRM reassign the records to the new owner.  Well here’s the honest answer: I don’t know and I don’t feel like checking that out right now.  Find out for yourself and post it as a comment to this blog!

Pour Conclure

I hope you enjoyed reading this blog entry as much as I enjoyed writing it.  Let us know what you think – do you like a side-dish of sarcastic entertainment with your informative blog, or would you prefer just a pain course of sarcasm without any intelligence at all?  We want to hear from you and we promise to approve all comments that are complementary in nature.