top of page

3 Ways to use Formulas when Importing Excel Spreadsheets to Salesforce

Last week we covered the basics of Excel to Salesforce imports. This week, we examine how Salesforce Admins can use formulas to speed the data transfer process between Excel and Salesforce

IMG_4042_edited.jpg

By Connor Laux

March 22, 2021

IMG_5297.PNG

            If companies expect to compete in the 21st Century, they need to access the full power of their data. Both Excel and Salesforce are great platforms to store data but storing data in two locations is inefficient for every company. When both platforms are combined, Excel and Salesforce offer an unparalleled ability to process information.

​

            However, minor differences between the platforms can slow the data transfer process, creating inefficiencies for both Salesforce Admins and your company. Learning how to transfer data between Excel and Salesforce with formulas is a necessary skill for every Salesforce Admin who wants their company to perform at the highest level.

​

            Learning how to transfer information from Excel to Salesforce with formulas. Never fear! It can be easy to move information between platforms with data imports and exports so long as you know our three easy tricks for how to prepare Excel spreadsheets for import to Salesforce.

​

            We covered some of the basic ways to prepare an Excel spreadsheet for import, but moving data from Excel to Salesforce with formulas is a step up from basic integrations between both programs. If Salesforce Admins want to optimize transferring data from Excel to Salesforce with formulas, they need to know how to combine and separate columns, match users to their Salesforce ID, and how to convert formulas to values.

​

​

Combining and Separating Columns

 

            While linking Excel to Salesforce with formulas might seem intimidating, combining and separating columns is the first step to easing your workload as a Salesforce Admin. Since data entry in Salesforce is so customizable, there’s a good chance that your objects in Salesforce will differ from your spreadsheets.

​

            For instance, you may have a single field for phone numbers in your Salesforce org, but store the area code and seven-digit number in separate Excel columns. You can rectify these discrepancies by combining two columns into one with the Concatenate command, or separate one column into two via the Text to Column command.

​

            To use the Concatenate Excel formula, first create a new column to contain the combined data. Select the first cell in the new column, then click the function button (which is found to the left of the cell editor), search for CONCATENATE, and select the command. This will bring up a window with two fields. In the Text1 field, select the cell with the text you wish to appear first in the combined cell, and in the Text2 field select the cell with the text you wish to appear second.

 

​

​

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

​

           If you are combining more than two columns, you can add a third column in the Text3 field, and so on. After clicking OK, the first cell of the new column will be combined. You can paste this cell to the rest of the column by clicking the small box in the lower right hand corner of the cell and dragging down the column.

​

           The Text to Column command separates columns around a column break, and sends everything after the column break to a new column. A column break can be a common character called a delineator (such as a space or dash), or a user-determined character limit.

 

​

​

​

​

​

 

 

 

 

 

 

           To use the command, create a new column to serve as the destination for the post-break portion of the original column. Then, highlight the column that will be separated, and click on the Text to Column tool in the Data Tools tab of the Data ribbon. If your column break is a delineator, select Delimited, click next, and indicate which character is your delineator. If it is a fixed number of characters, select Fixed Width, click next, and set the leading character limit in the preview window.

​

​

​

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

​

​

​

​

​

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

           Selecting finish will send everything after the column break to the empty column you created. If you want to send the data to a different column, you can specify that column by clicking next.

​

​

           Matching Users and Records to their Salesforce IDs

​

           When you prepare spreadsheets for transfer from Excel to Salesforce with formulas, some new records must be linked to pre-existing parts of your org. Most records are linked to a user in your company (the record’s owner) and others are related to a larger account (the parent record). These hierarchies of records help to organize your org and make it easier to see how all of your data are connected.

​

           To create these relationships during a Salesforce import from Excel, Salesforce ID columns are required. Salesforce ID columns contain the User ID of the owner or the record ID of the parent record. Fortunately, users and records can be matched to their Salesforce ID without hassle by using the VLookup.

​

           In order to perform a VLookup, you need a reference table that contains the name of the record/user as they appear in your Excel spreadsheet and their Salesforce ID. One reliable way of generating a table like this is with a data export from Salesforce[3] . The table needs to be formatted so that the parent record or user name is in a column to the left of their ID, and the IDs need to be sorted in ascending numerical order.

​

           The VLookup is by far the most complex Excel formula described in this article, but when broken into steps it’s not too bad.

​

           1) First add a column to your spreadsheet for the Salesforce IDs. Then select the first cell of that column, click the function button, and search for VLOOKUP. This will bring up a window with 4 fields.

​

           2) In the first field (Lookup_value), select the cell that will be matched to the lookup table. This will be the user/parent record’s name.

​

           3) The next field (Lookup_array) will designate the location of the lookup table. Select the entire table exported from Salesforce. Add a dollar sign before the column letter and row number of both cells that outline the table, as this will lock the table when you paste the formula down the column.

​

           4) The third field (Col_index_num), is the column containing Salesforce IDs. You should indicate this column by its number, the leftmost column of a table is number 1, the one to the right of it is number 2, and so on.

​

           5) The fourth and final field (Range_lookup) asks if you want to find the closest match or an exact match. If you want an exact match, type false. Click OK and the first row will be matched to its Salesforce ID. Then paste the formula in the first cell down through the rest of the column to match the remaining IDs.

​

​

           Converting Formulas to Values

​

           Formulas are amazing time savers, but if you want to prepare spreadsheets from Excel to Salesforce with formulas you need to perform one more step. Salesforce does not understand the format of Excel formulas, so you will need to replace formulas with values.

​

           Replacing formulas with values can be easily accomplished with a special paste. To use a special paste, highlight all the data you wish to convert from a formula to a value, right-click it and select copy. Then, click on the paste menu in the Clipboard tab of the Home ribbon. Select the Values option. You can ensure the paste worked by clicking on a cell and looking at its editor. If the editor matches the cell, the cell contains a value, not a formula.

 

​

           Formulas are the Way Forward

 

           These tools, combining or separating columns, VLookup, and special pastes, are capable of saving hours of busywork when transferring data from Excel to Salesforce with formulas. Every Admin who wants to evolve their capabilities with Salesforce should use formulas to save time on tasks. If you integrate formulas into workflows and become a formula master, there is little you can’t accomplish in Excel. Good luck out there!

​

​

​

Picture1.png
Picture1.png
Picture1.png
Picture1.png
bottom of page