5 Excel Skills Every Salesforce Admin Needs

Share this article...

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.

“Formassembly"

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)

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

Summary

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!

6 thoughts on “5 Excel Skills Every Salesforce Admin Needs

  1. Avatar

    For duplicates using countif might be better as visual highlights wouldn’t be helpful when there are more than 100 duplicates

  2. Avatar

    The best thing an Admin can do is learn XLOOKUP ( the super powered alternative to VLOOKUP). The next best thing is get yourself XL_Connector. This APP has been brilliant for me as Admin. I can import directly into a Spreadsheet, make changes and upload the changes directly from my file. It will download VRs, Workflows, Flows and Processes. I run 2 orgs and I can simply switch between them. Do yourself a favour and check it out. Support is also just the best.

  3. Avatar

    Here’s a shortcut to concatenate: skip the concatenate() formula and just use ampersand between the items you wish to concatenate.

    The equivalent of =concatenate( “(“, E2,“) – ”, F2) is =”(“&E2&”) – “&F2.

    1. Avatar

      I do use VLOOKUP all the time, but I’m not great at teaching it (I blame my non-technical background) so I left it off this post. Deserves an honorable mention for sure though!

Leave a Reply