Being a Salesforce Admin means you have to be highly knowledgeable about Salesforce, sure, but most people don’t realize there are other skills required to be a successful Admin – that aren’t related to Salesforce!
Almost from the very moment I became an Administrator, I’ve also had to manipulate spreadsheet imports in Excel. It might be because you’re mass-creating new records for a custom object, or, perhaps because you’re importing a list of new prospects for your Sales Team. Today I’ll be sharing with you the Top 5 most frequent things I do in Excel – these are the Excel skills I wish I had known before I became an Administrator.
Excel Skills Are Still Relevant
I think that my example, importing a list of new prospects for your Sales Team, is the most common scenario, especially for smaller companies that do not yet have a Marketing Ops team, and so, the Salesforce Admin fills that role. Even today, years after I started, I still get this almost identical email from my clients.
“Hi Stacy, can you import this list of new Leads for me?”
Well, sure I can! But we need to clean it first. As the old saying goes, “Bad data in, bad data out”, and in order to clean the list, you’re going to need these valuable Excel skills.
Skill #1: “Find Duplicates”
Why you need it: You don’t want duplicates in your database! Trust me. Allowing duplicates in your org is like knowingly letting termites into your home. Sooner or later, either it will crumble or you’ll pay a fortune to fix it.
How To Do It: Just highlight the column you want to find duplicates in, click “Conditional Formatting” and then “Highlight Cells Rules”, then “Duplicate Values”.
Then, click “OK”
Now you can see Becky is on this list twice. You can delete the duplicate row, and keep working.
A note about names: You can look for duplicate first and last names too – but beware, it’s common enough for people to have the exact same first and last name. On this list, I have two people named Aaron Brodeur, but they have unique emails and work at different companies, so I know they’re not the same person.
Skill #2: “Find Text that Contains”
Why you need it: Almost as bad as importing duplicates is importing un-marketable people. Perhaps people that work at your own company, or if there happen to be competitors on your list.
How to do it: Using the same steps above, we can use Conditional Formatting to highlight text that contains email domains that I do not want on my list.
Now I can see that my Sales Rep Judith has inadvertently added herself to this list, I can remove her from the list since she’s not a prospect. This is great for finding any personal domains too, like Gmail or Yahoo, if you want those removed from your list.
Skill #3: “Text-To-Columns”
Why you need it: Salesforce wants to have the First and Last name in separate fields, so we need them separated. Also great for separating things like addresses, states & countries, etc.
How to do it: Here, I’ve moved the column I’m going to be separating to the end of my spreadsheet just to make it easier to read. Just highlight the column you want to separate, and go under your “Data” tab in Excel, and click “Text-to-Columns”
Then just select how you want to split up the data.
When you click “Finish” your data on the spreadsheet will move accordingly! Now you just need to re-label your column headers and get rid of any columns you no longer need.
Skill #4: Concatenate
Why you need it: Concatenate is the opposite of Text-to-Columns. In some cases, Salesforce wants all the data in just a single field, like phone number.
How to do it: This one involves writing an excel formula. The formula is pretty simple.
=concatenate( all the stuff I want to smoosh together goes here, separated by commas )
You’ll also need to add in any spaces or characters. Whatever text you want to add (including spaces) should be added in quotes, and separated by commas. In this example, my Area Code and Phone are in separate cells, and I want them combined, but also formatted with parentheses around the area code.
=concatenate( “(“, E2,“) – ”, F2)
Spelt out, it says: First put an open-parentheses, then the value of E2, then a close-parentheses, a space, a dash, another space, then the value from F2.
And now you have the phone number in one cell, which can come in to one field in Salesforce. Just make sure to relabel your column header for the column you want to keep.
Skill #5: Paste Special
Why you need it: To import the actual data, and not the formula =concatenate(…….)
How to do it: Now that you’ve concatenated some data, you can see in the spreadsheet the proper value, but you really need to delete column E and F here, and you need column G to just show you the phone number. But if you were to import it just like this, all your phone numbers would say “=concatenate(…….)”
Admin Confession! Yes, I have done this, and yes, it was very embarrassing. Just ‘copy’ the formula cells you just made, in this example, cell G2-G7. Right-click, and then click “Paste Special”
Select “Values” then click “OK”
Now you can get rid of all the improperly formatted columns that you no longer need!
Now we have a sparkly clean list! Now, this has just been an example, your list will probably have a lot more columns that need cleaning, and a lot more values. I’d also recommend learning how to do a VLOOKUP – there’s a lot of great lessons on how to do that online, so give that a try. Another great one to research is “Find & Replace” which I use all the time particularly on orgs that have State & Country Picklists enabled.
There are also a lot of marketing tools out there that will do some of this for you (Marketo, Pardot, HubSpot, etc.) At a minimum, most of them will not import duplicate emails. These tools are mostly great, and if you have them, I’d highly recommend you make use of them. However, you’ll probably have at least some scrubbing of lists that needs to be done, and having these skills in your back pocket will make you that much more valuable and efficient as a Salesforce Administrator.
Bonus Skill! VLOOKUP
Thanks for all the great comments! I initially didn’t include VLOOKUP because for me, while I use it all the time, it’s actually a difficult topic to teach. But we’ve gotten some questions about VLOOKUP so I will give it my best shot!
Like all these other skills, I use VLOOKUP a lot when scrubbing lists for import. There are a lot of situations where you might need to fill out value, but you don’t want to go searching for the result, and there are too many records to do this by hand. For example, let’s say I’m using State & Country Picklists in my org, and I have a tradeshow list that only has States/Provinces, but not countries. I won’t be able to import that since the “State/Province” dropdown is dependent on the Country dropdown.
I need the Country column filled out, but I don’t want to go down the list and populate each one by hand. Now is the time to use VLOOKUP!
VLOOKUP is a quick way to populate a bunch of cells all at once, from a single reference point. You’ll need a couple of things:
- The list you want to clean (above)
- The list you’re going to use to do the cleaning (the reference list, below)
So now we have two tabs on our spreadsheet. The List Import that we need to scrub, and also the Reference List we’re going to use to get the missing data (Country.) The Reference List will be where you are going to find the value you need, so you will need to prepare this one.
On the list, you need to scrub and sort the column you’re going to be searching for alphabetically (that would be our State column in this example.) Do the same on the Reference List, sort alphabetically by the value you’re searching for.
Sorted alphabetically by State.
Now we are ready to actually begin the VLOOKUP process. What the formula is going to do is search for the value you tell it to, find it on the Reference List, and return with whatever you tell it to. For me personally, it helps to think of the formula as a series of questions that I need to answer.
So in Excel, what this really looks like:
Once the formula is working in the first cell, just drag it down to all the cells you need to fill. Now, with a larger list, you will want to sort by Column D and resolve any errors. In this case, I have one misspelt Province, so I’ll just go ahead and fix that one manually.
So now our Country column is filled out! But wait – your list still is not ready for import to Salesforce. If you click on any of the values in column D, you’ll see the formula, rather than the actual Country. On Column D, just copy the entire column and then do Paste Special -> Values.
Save it as a CSV and then you’re done!
There are a lot of tutorials online that explain how to do VLOOKUP, but not many that explain things in the context of Salesforce Admin work, so I hope this helps you out! I’ve used VLOOKUP in this exact situation before, and I’ve also used it to sort records out to new owners, or even if I have to create a new custom field and I want to backfill all the existing records.
Being a Salesforce Admin means you have to be highly knowledgeable about Salesforce, sure, but most people don’t realize there are other skills required to be a successful Admin. Some of those are soft skills, interpersonal relationship management, time management, etc. which are a bit harder to learn – but now you’ve read this post, you are now equipped with new Excel skills that are easy to learn, incredibly helpful for getting through your admin workload, and will really impress your users!