2 Data Import Clean and Prepare Your Data Using Excel Salesforce

Summary

This screencast offers a concise guide to enhancing data preparation for Salesforce imports using Excel tools. It covers essential techniques like sort and filter to eliminate duplicates and enforce naming conventions, VLOOKUP for matching Salesforce IDs, and Paste Special as Values to convert formulas to static data. Additional tips include using Format Cells to retain leading zeros, Find and Replace for data cleaning, Concatenate for merging text, and Text to Columns for splitting data. The video concludes with saving data as a CSV for Salesforce import, ensuring all formulas are converted to values and unneeded columns are removed.

Takeaways

• 🧹 Cleaning and preparing data for import can be time-consuming, but Excel tools like sort and filter can help streamline the process.
• 🔄 Sort and Filter can locate duplicates, apply naming conventions, and isolate certain groups of data.
• 📝 VLOOKUP is essential for matching Salesforce user IDs, account names, and other fields during the import process.
• 🔒 It's important to use absolute references in VLOOKUP to avoid errors when copying formulas.
• 📋 Paste Special as Values converts formulas into values, preventing errors when deleting columns or data used in formulas.
• 0️⃣ Format Cells can restore leading zeros in data like zip codes and phone numbers, which Excel might drop automatically.
• 🔍 Find and Replace is useful for cleaning up data, such as removing extra spaces, dashes, or other unwanted characters.
• 🔗 Concatenate allows you to combine multiple text fields into one, such as combining street addresses or names.
• 📊 Text to Columns separates data in a single column into multiple columns based on a delimiter, like splitting names into first and last names.
• 💾 Saving as CSV converts all formulas to values and removes extra formatting and sheets, making it suitable for Salesforce data import.

Q & A

What is the main purpose of the video script?

The main purpose of the video script is to provide a tutorial on how to clean up and prepare an import file for Salesforce using various Excel tools to make the process easier and faster.

How can the 'Sort and Filter' feature in Excel help with data cleaning?

The 'Sort and Filter' feature in Excel helps with data cleaning by making it easy to locate duplicates and isolate certain groups of data, which can then be used to apply naming conventions or locate and complete blank records.

What does the script suggest for standardizing state abbreviations in an import file?

The script suggests using the 'Filter' tool to isolate records containing variations of a state name, such as California, and then standardizing them to a two-letter abbreviation, such as 'CA'.

How is VLOOKUP used in the context of the script?

VLOOKUP is used to match Salesforce user IDs to the names of record owners or to match account IDs to account names, ensuring that new accounts are assigned to the correct salesperson during the import process.

Why is it important for the matching value in a VLOOKUP to be in the leftmost column and sorted in ascending order?

It is important for the matching value in a VLOOKUP to be in the leftmost column and sorted in ascending order to ensure accurate and efficient matching of values within the lookup table.

What is the significance of using 'Paste Special as Values' in the script?

Using 'Paste Special as Values' is significant because it converts formulas to their computed values, which is necessary when preparing an import file for Salesforce, as CSV files do not support Excel formulas.

How can 'Format Cells' help with preserving leading zeros in data?

'Format Cells' can be used to format data such as zip codes and phone numbers to ensure that leading zeros are not dropped by Excel, which is crucial for maintaining data integrity during import.

What is the role of 'Find and Replace' in cleaning up data as described in the script?

'Find and Replace' is used to clean up data by replacing unwanted characters or values with the desired ones, such as removing dashes, decimals, and empty spaces from phone numbers.

How does the 'Concatenate' function assist in combining data from different cells?

The 'Concatenate' function assists by allowing users to combine text from different cells into one cell, which can be useful for combining address lines, phone numbers, or other pieces of information.

What does the 'Text to Columns' feature do in the context of the script?

The 'Text to Columns' feature is used to separate data that is in one column but needs to be split into two or more columns, such as separating first and last names, to meet the data import requirements for Salesforce.

Why is it necessary to save the Excel file as a CSV before importing into Salesforce?

It is necessary to save the Excel file as a CSV before importing into Salesforce because CSV format does not support Excel formulas or formatting, ensuring that all formulas are converted to values and unneeded columns or formatting are removed.