11 Excel Tips Every Salesforce Admin Needs
Being a Salesforce Admin means you have to be highly knowledgeable about Salesforce, but most people don’t realize there are other skills required to be a successful Admin – skills that aren’t necessarily related to Salesforce!
Almost from the moment I became an administrator, I’ve also had to manipulate spreadsheet imports in Excel. It might be because of mass-creating new records for a custom object, or perhaps it’s down to importing a list of new prospects for the sales team. Today I’ll be sharing with you the tasks I do most frequently 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 the sales team – is the most common scenario, especially for smaller companies that do not yet have a marketing ops team (where 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.
1. Find Duplicates
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.
Just highlight the column you want to find duplicates in, click Conditional Formatting and then Highlight Cells Rules, followed by 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.
2. Find Text that Contains
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.
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.
Salesforce wants to have the First and Last name in separate fields, so we need them separated. This is also great for separating things like addresses, states, and countries, etc.
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.
Concatenate is the opposite of Text-to-Columns. In some cases, Salesforce wants all the data in just a single field, like phone number.
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 into one field in Salesforce. Just make sure to relabel your column header for the column you want to keep.
5. Paste Special
To import the actual data, and not the formula =concatenate(…….)
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!
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 is 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 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.
Image: Columns 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, here’s 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.
Now our Country column is filled out! But wait – your list 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.
Any list of people you get for import to Salesforce hopefully include “First Name and Last Name. However, sometimes what you get is kind of a mess – like this list here. You could import these names as is, but there’s a problem – if you happen to send any kind of automated email that includes First Name or Last Name, the recipient is going to immediately know that:
- The email is automated.
- Your company couldn’t even be bothered to scrub their email list!
If you’ve ever received a message from a recruiter on LinkedIn, you probably know what I’m talking about!
Lucky for those of us who are Salesforce Admins (but not Excel experts!), this is a very easy fix. The formula is simple:
The same function can be used for any text field, but use caution if you’re going to use it on something like Account Name or Company Name on the Lead object. Certain companies may be in all caps, for example, HSBC is most commonly spelled with all capital letters.
Similarly, I’ll share the functions for converting the entire cell to upper or lower case. They’re equally simple:
A few months ago, I was lucky enough to work with a brand new Salesforce instance. The sales team had a very small number of Opportunities that, up until that point, were listed on a spreadsheet. Now, there are a number of fields on the Opportunity that have a character limit – most notably Opportunity Name and the Next Step fields. LEN comes in very handy when you want to make sure that you won’t get an error due to a cell having too many characters.
So we can see that, for our fourth Opportunity, we need to reduce the character count so that we don’t get an error on import.
9. Removing the first or last number of characters from a cell
This one is a favorite of mine when I need to remove something like the first four characters, or the last three characters from a cell. In this example, we’ll use some website URLs.
We’ll dig into this one a little, because once you understand what you’re working with, it’s actually very flexible.
Remove the first 4 characters: =RIGHT(A3, LEN(A3)-4)
Remove the last 4 characters: =LEFT(A3, LEN(A3)-4)
So, if we want to remove the first few characters, we’ll use RIGHT and if we want to remove the last few characters, we’ll use LEFT. Make sure to enter the cell that you want to modify, and then finally, a minus character, followed by the number of characters you want to remove. In this example, we’re removing four characters, but you could remove any number that you need to.
10. Extracting Email Domain from an Email List
Some Salesforce users do a lot of work filtering on Email Domains of Leads or Contacts, and they want this data as text rather than as a formula field. You can easily extract the Email Domain using Excel too.
11. Leading Zeros (and general formatting)
Stepping back from all these interesting and useful formulas, there’s a lot you can do to improve data quality just by formatting your cells properly, and knowing what type of values Salesforce expects to see for each field type. This is a very common mistake for new admins (I’ve done it plenty of times myself!). All you need to do is to right-click on the cell (or column/row, as needed).
Now, this isn’t the hard part. The hard part here is knowing which fields need a specific format, and remembering to format them all the time – then, keeping this in mind for all future data loads you look at.
Here’s a great example. In the United States, postal codes are generally recorded as five numerical digits. Salesforce has an office in San Francisco, California, which has a postal code of 94105. The New York City office is located in the postal code 10036. If you were to put these on a CSV and import, you’d have no problem, even if you don’t bother with formatting.
However, not every place has a postal code exactly like this. Some of the postal codes in the US start with zeros. This becomes a problem in Excel when you don’t format. New Jersey Postal Codes start with a zero. When you put that in to Excel without formatting, you end up with this:
When you want to keep leading zeros in a spreadsheet, set your format to Text.
I’ve put together a basic guide on formatting fields for CSVs that might help when you’re getting started. These are just the most common fields. If you’re ever unsure, test in a sandbox. You can do CSV imports into a sandbox just like you do in production, so that’s a great way to know if you’re going to get import errors, and fix them before doing your production import.
These tips only scratch the surface of everything you can do in Excel. Knowing how to fix data on a spreadsheet before you import it is just one of the steps to ensuring your org maintains the highest data quality in the long term.
To be a Salesforce Admin you have to be highly knowledgeable about Salesforce, but there are lots of other skills required to be successful. 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!
Please share in the comments below ideas for your favorite Salesforce Excel fixes!
Good article, point 10 I’ll add to my book of knowledge 🙂
TIP: Replace the concatenate function with the & sign, this is easier to remember and in use. I.e. formula = “(“&E2&”) – “&F2 ==>> result is (408) – 555-5555
Holding Control while filling cells down in a column is another trick to ensure excel doesn’t try to be smart and sequentially update numbers or dates…
auto-fill sequentially updates the date:
whereas holding control will maintain the date throughout the column:
A little trick that’s gotten me is if you have a text field that you want to auto-fill down for all records in an excel for a load AND it ends with a date(ex. reason field with date of exception), excel will sequentially update dates on those cells as well!
Thank you! I had no idea number/date sequencing could be avoided while using this feature.
Thank you for this quick and useful tip!
I’d also add TRIM() function for getting rid of whitespace. I often throw TRIM() around any other text mod I’m doing, for instance, TRIM(PROPER()).
Salesforce inspector chrome plugin (data import) and Excel (or Google sheets) are the first tools that a good admin must use
Great article, I’d like to add that you could use the & operator instead of CONCATENATE() or TEXTJOIN() which also allows you to choose a delimiter like “,” for creating string lists etc.
Also the rather new XLOOKUP() function is a bit easier to use than the VLOOKUP() – if you haven’t heard of it yet.
You took the words right out of my mouth 🙂
I love the app from Xappex.com; a plugin for Excel. All the functionality that excel provides, linked directly to my Salesaforce-data!
(I don’t work for Xappex or anything, I’m just a fan.)
Agreed. Also big fan
My favourite tip would be to do as much as possible of this in Power Query as then the data cleanup only requires setting up once and can be run on multiple sets of data without having to duplicate activities.
Great tips! I’m a fan of using TEXTJOIN instead of Concatenate because you can skip blank columns.
Concatenates a list or range of text strings using a delimiter.
▪ Delimiter: Character or string to insert between each text item.
▪ Ignore_empty: if TRUE(default), ignores empty cells.
▪ Text1: text1,text2,… are 1 to 252 text strings or ranges to be joined.
This is a great list with some amazing reminders to formulas I don’t use often! The formula that I find even more useful than VLOOKUP is XLOOKUP. The difference is that you don’t need to count your columns or organize your spreadsheet to make VLOOKUP easier to format, it also defaults to an exact match. XLOOKUP is my new go-to, especially when I use Data loader to export all of the fields in an object. I can simply use my mouse to select the columns I’m matching and returning data for..
To compare formulas:
=XLOOKUP(C2,’Reference List’!A:A,’Reference List’!B:B)
Great article, Stacy! One of my superpowers as a new Salesforce admin was having a lot of experience scrubbing data in Excel.
With regard to item 3 (Text-to-Columns), the most important reason to move your target column to the end is to avoid overwriting any data in the columns to its right (with the newly split data). So you will need to either insert empty columns to the right of your target column or move it to the end (as you’ve done). Likewise, anyone using this feature should anticipate that some names may include middle initials, middle names, or suffixes (PhD, Jr, Sr, III), etc., which will also take up additional columns (so you may need 3 or more columns to gather the data as opposed to just 2). Keep that in mind if inserting empty columns to capture your split data.
I find myself using the menu function to select visible cells only all of the time, especially when using filters. Its a great way to copy only those records that meet certain criteria. To access it, highlight your cell, go to “Find and Select” on the menu and scroll down to “Go to Special” and then click the radio button for “Visible cells only”. Any cells that are in hidden rows or columns or that are hidden by filters will not be copied.
Oh yikes… still using VLOOKUP in 2022 huh?
I think this author needs to learn a but more… and switch to Sheets.
It’s worth noting that not everyone has the latest version of Excel and therefore may not have access to the newer XLOOKUP. Many people are enjoying the new XLOOKUP functionality, as you can see from other comments, but there are also many still using (and enjoying) VLOOKUP. Typically, whether someone is using Microsoft Excel or Google Sheets is dictated by their work setup and their preferred tool. Since Google Sheets supports most functions found in other tools e.g. Excel, it shouldn’t matter which tool the author is using for demonstration purposes.
I use a formula to convert Salesforce ID15 to ID18.
You cannbot rely on ID15 to do lookups and remove duplicates based on ID fields. Excel is not case sensitive for those fonctions but ID15 is case sensitive.
Knowing VLOOKUP and its mightier upgrade XLOOKUP (+1 Hans!) isn’t just for prepping Salesforce data, it’s a VALUABLE LIFE SKILL! I use them everywhere.
I will add that for me the most common Salesforce use case was for loading data with lookups to other objects (e.g., contacts and accounts.) Load an Account export into an Excel tab, then in a tab of new raw Contact data use VLOOKUP to pull in the SFID. You can take multiple rounds of matching on company name, company website, and so on until you can connect them.
All I can say is THANK YOU for concisely condensing into a single article how to do everything you really need wrt Excel. Great article. One of the best I’ve seen on the topic anywhere.
I definitely agree with the recommendation for Xappex.com (XL-Connector) in one of the previous posts. Wider, more flexible and convenient than Data Loader (and I also do not work for Xappex or anything, I’m just a fan.)
Second recommendation regarding section 6. Vlookup – Should be replaced with its modern version Xlookup which is more flexible and easy to use. Detailed explanation with examples: https://exceljet.net/excel-functions/excel-xlookup-function
Ahhh man!!!! So close. This would have been perfect if you’d chosen Xlookup instead.
Great write up.
Gopal Krishna Debnath
Excellent tips. Very helpful for practical life.
Very much Informative. Thanks for sharing
Good article, much appreciated
One of the best articles I’ve found on excel skills every Salesforce Admin should know. Not everyone has the newest version of Excel (which has XLOOKUP) on their work computer so I appreciate you focusing on VLOOKUP. Once you learn VLOOKUP it’s an easy transition to XLOOKUP.
TRANSPOSE OPTION AS WELL.. & transpose data
ROWS –> COLUMNS & vice-versa