How to clean data in Excel before Switching CRMs

Excel can be a powerful tool for cleaning data before migrating to a CRM system. Many businesses use excel before switching to a CRM and it's best to clean your data before integrating a new system. In this post we’ll show you the key steps to cleaning your data.

Jump menu:

Follow these steps to clean your data

1. Import your data
2. Identify and remove duplicates
3. Correct inconsistencies and errors
4. Standardise data formats
5. Handle missing data
6. Validate data accuracy
7. Normalise data
8. Create data quality reports
   

""Clean data is the lifeblood of a successful CRM migration. Messy data can lead to inaccurate insights, wasted time and frustrated users" says our lead Solutions Engineer, Lochlan Ross from Modern Visual.

"By dedicating time to clean your data before switching CRMs, you'll ensure a smooth transition and unlock the full potential of your new system. Leveraging these features and following a well structured cleaning process will enable you to transform messy data into a valuable asset for your new CRM."

Lochlan Ross, our Lead Solutions Engineer at Modern Visual. 

Follow these steps to clean your data

1. Import your data

• Open Excel & import from CSV: If your data is in CSV format, use the "Data" tab > "Get & Transform Data" > "From Text/CSV" to import the file.
• Import from other sources: If your data is in another format (e.g., Excel, database), use the appropriate import method (e.g., "From Table/Range").
  

• Select the data range.
• Go to "Data" > "Remove Duplicates."
• Choose columns to check for duplicates.

3. Correct inconsistencies and errors

• Use conditional formatting: Highlight cells with errors or inconsistencies. For example, use conditional formatting to highlight cells with invalid email addresses or dates in the future.
• Use formulas: Employ formulas like IFERROR to catch errors and return a default value.

• Format cells: Use the "Home" tab and the "Number" group to format cells as dates, times, numbers, or text.

Formatting Cells:

1. Select the range: Choose the cells you want to format.
2. Go to the "Home" tab: Click on the "Home" tab at the top of the Excel window.
3. Use the "Number" group: Find the "Number" group, which typically contains buttons for formatting numbers, currency, dates, times, and more.
4. Choose the desired format: Click on the appropriate button to format the selected cells. For example, to format cells as dates, click on the "Date" button.

Use functions: 

The TEXT function is particularly useful for converting dates or numbers to specific formats. 

5. Handle missing data

• Identify missing values: Use functions like COUNTIF to identify cells with missing values.
• Fill missing values: Decide how to handle missing values. Options include:
  • Filling with a default value (e.g., "N/A")
  • Deleting rows with missing values
  • Interpolating missing values based on surrounding data (if applicable)

• Use data validation: Create custom validation rules to restrict data entry.
  You can use data validation to ensure email addresses follow a specific format (e.g. John@business.com).
  
  Follow these steps:
  
  

7. Select the cells: Choose the cells where you want to restrict data entry.
8. Go to "Data" > "Data Validation". This opens the Data Validation dialog box.
9. Set the "Criteria": Here, you can define the rules for valid data entry.
10. "Allow": Select the type of data allowed (e.g., "Whole number", "Decimal", "Text", "List").
11. "Custom formula is": Choose this option for more complex validation rules using formulas.
12. Set input message (Optional): This message appears when users select a cell with data validation enabled. You can provide instructions or guidance on the expected format.
13. Set error alert (Optional): This customises the message displayed if users enter invalid data. You can choose a "Stop" alert to prevent data entry or an "Informative" alert to warn them of the error.

7. Normalise data

Data normalisation is a crucial step in data cleaning and preparation. It ensures that data is consistent, standardised and suitable for analysis. 

Common normalisation techniques

• Data type conversion: Converting data to appropriate data types (e.g., text, numbers, dates).
• Case normalisation: Standardising text to uppercase or lowercase.
• Data splitting: Separating combined data into its constituent parts (e.g., splitting full names into first and last names).
• Data aggregation: Combining multiple data points into a single value (e.g., calculating averages or sums).
• Data standardisation: Applying a consistent format or scale to data (e.g., standardising units of measurement).

Example: Splitting full names

If you have a column containing full names, you can split it into separate columns for first and last names. This makes it easier to analyse data based on individual names.

Steps:

1. Select the data: Select the column containing the full names.
2. Go to "Data" > "Text to Columns".
3. Choose a delimiter: Select the character that separates the first and last names (e.g., a space).
4. Click "Finish". Excel will split the column into two new columns.

Example: Extracting parts of text

You can use functions like LEFT, RIGHT, and MID to extract specific parts of text.

• LEFT(text, num_chars): Returns the specified number of characters from the beginning of a text string.
• RIGHT(text, num_chars): Returns the specified number of characters from the end of a text string.
• MID(text, start_num, num_chars): Returns a specified number of characters from a text string, starting at a specified position.

This is particularly useful when you want to: 

• Extract parts of names (First name, Last name)
• Extract the username or domain name from an email address
• Extract a product category from a product code
• Extract the area code from a phone number

8.Create data quality reports

• Use pivot tables: Create pivot tables to summarise and analyse your data. Pivot tables are a powerful tool in Excel that can be used to summarise and analyse data efficiently. They allow you to quickly create cross-tabulations, calculate aggregates and identify trends in your data.
  
  
• Use formulas: Employ formulas like COUNTIF, COUNTA, and COUNTBLANK to calculate data quality metrics.

Steps to create a pivot table:

1. Select your data: Choose the range of cells that contains the data you want to analyse.
2. Insert a pivot table: Go to the "Insert" tab and click "Pivot Table."
3. Choose a location: Select where you want the pivot table to be placed. You can choose a new worksheet or an existing location within your spreadsheet.
4. Drag and drop fields: Drag the fields you want to analyse to the appropriate areas of the pivot table:
   • Rows: Fields you want to use as row labels.
   • Columns: Fields you want to use as column labels.
   • Values: Fields you want to summarise (e.g., sum, average, count).
5. Customise the pivot table: You can further customise your pivot table by:
   • Changing the value field settings (e.g., sum, average, count, etc.).
   • Adding or removing filters.
   • Formatting the pivot table.

Additional Tips:

• Backup your data: Create a copy of your original data before making changes.
• Use named ranges: Assign names to frequently used ranges for easier reference.
• Leverage Excel's features: Explore Excel's built-in features and functions for data cleaning and analysis.

Clean data means a smooth CRM migration.

By implementing these steps and using Excel you can optimise your data quality and facilitate a seamless transition to your new CRM.

Start strong with organised, accurate records so your new system can work smarter from day one.