Locked lesson.
About this lesson
As a Marketer, it’s incredibly important to have up-to-date, clean data. In this lesson we apply some common edits to unusable data to clean it up for use.
Quick reference
Importing and cleaning data
Good data characteristics
- A single row of headings
- Beware of headings spread over two rows; using merged cells will cause issues with Excel functionality that identifies and uses headings.
- If you do have a longer heading, try the wrap text function on the home tab.
- No breaks in between columns or rows
- Excel regards a break as the end of the data range and will not take rows or columns after the break into account in calculations or other functionality.
- Consistent data types in columns
- If a column should consist of dates and some of the data is aligned to the right and others to the left of a cell – you probably have inconsistencies in how Excel regards the data.
Data imported into Excel will auto-align to the right if Excel recognizes the data as numbers or dates and will auto-align to the left if Excel regards the data as text.
Text to columns
- If data jumbles together in one cell instead of different cells, try this to separate the data
- Data separated by special characters or is delimited (a certain consistent length) is perfect for this function
Trim
- The TRIM function is used to remove spaces before and after text
- Trim will also remove all spaces but one from between text
COPY and PASTE SPECIAL
- Check out the paste special screen when you next use it.
- In the video we use paste special to paste VALUES only – to get rid of the formula and only paste the correct values
- 00:04 As a marketer, it's incredibly important to have up-to-date clean data.
- 00:09 Clean data means that you have the right information about your prospects
- 00:12 and leads.
- 00:13 And you're not missing any relevant or
- 00:15 vital information that you can use in your marketing campaigns.
- 00:19 Like the country they're based in, their current email address, or
- 00:22 how they've been engaging with your marketing.
- 00:25 You can use this data to segment and nurture your leads to
- 00:28 help them through the buyer's journey and toward becoming paying customers.
- 00:33 In this video, we're going to look at how you can clean up your email marketing data
- 00:36 to make it more actionable for you and your business.
- 00:40 This can be quite common when you're importing data from different sources, or
- 00:44 copying information from different formats.
- 00:46 So let's see how we can clean up this data and turn it into something usable for
- 00:51 Excel.
- 00:52 If I look at the data a little closer, I can see the different columns or
- 00:56 what should have been different columns are imported into one column.
- 01:00 The data is separated.
- 01:02 The names of the data columns are separated by commas.
- 01:06 I can confirm that,
- 01:07 if I look at some of the data, there are commas in between the different fields.
- 01:11 Well, Excel has a function to help us sort this out, and it's called Text to Columns.
- 01:17 If I select my data Ctrl+Shift and down arrow and then go to the Data tab,
- 01:22 under the Data Tools groupings you'll find Text to Columns.
- 01:27 Click on it and we get a wizard that consists of three screens.
- 01:31 Now, the first choice we need to make is we need to choose whether the fields
- 01:36 are separated by a character, like a comma, or if it's a fixed-width field.
- 01:40 Ours are separated by a comma, so we'll check off that it's delimited data.
- 01:46 Now, on the next screen, we'll choose a comma as the delimiter.
- 01:50 Your default might be a tab.
- 01:51 And if you choose the wrong character,
- 01:53 you won't see any difference in the preview window.
- 01:56 But if you choose the right one, like a comma, in this case,
- 01:59 you'll see the preview makes a big difference.
- 02:02 The final screen in this wizard is used to define the data type in each field, but
- 02:06 we're going to skip that for now.
- 02:08 We'll do this later.
- 02:10 And we click Finish, and there we have our data.
- 02:13 We can adjust the column widths so that it looks a little better.
- 02:18 Now, if this is good data, there's a few characteristics it will comply with.
- 02:23 The first one is that there are row headings,
- 02:26 which we can see right across the top.
- 02:29 The second one is that there shouldn't be any blank lines.
- 02:34 But we can see there's a couple in here.
- 02:36 The third is that they are consistent data types.
- 02:39 Now, with data types, how do you recognize consistent data types?
- 02:43 Well, if this is a text field, for example, and if Excel imported the text
- 02:47 field as a text field, then it would auto align the data to the left.
- 02:53 If it imports a date or number, like in the date field,
- 02:56 it will automatically import the data to the right-hand side of the cell.
- 03:01 This seems like consistent data types.
- 03:04 Let's fix the blank lines first.
- 03:08 And I can fix that by sorting the data and
- 03:10 it will either sort the blank lines to the bottom of the data or to the top.
- 03:16 So I select my data by selecting the headings and
- 03:19 Cntrl+Shift+right arrow then Cntrl+Shift+down arrow.
- 03:23 This quick selection method will stop once it hits a break.
- 03:28 So I'll have to continue holding the Ctrl+Shift, press and
- 03:31 release the down arrow key until you select all the data.
- 03:34 And then on the data tab under the sort and
- 03:38 filter grouping there is a sort button.
- 03:42 Before I click it, you'll see I have selected my data, including the headings.
- 03:47 So if I click on the sort button, there's a tick box here that says my data has
- 03:52 headers, and I have selected the headers.
- 03:55 So I've checked that off and now Excel deselects the headings,
- 04:00 because you don't want the headings sorted into your data.
- 04:04 And I can choose to sort by name.
- 04:06 Any order really is good here.
- 04:09 So let's hit OK.
- 04:12 As you can see, it has sorted the data and
- 04:15 those blank lines have moved to the bottom of the range of data out of our way.
- 04:20 If they were at the top, I could just select the blank lines, right-click, and
- 04:24 delete but it's not a problem in this instance.
- 04:27 To fix the format of my date column, I'm going to select the date column,
- 04:32 Shift+Cntrl+down arrow, and I'm just going to change the format.
- 04:38 Excel sees the date as a serial number.
- 04:40 The number shows how many days have passed since the first of January 1900,
- 04:46 so this is 43,000 days since then, and so on.
- 04:50 So if the date imports as this type of number,
- 04:53 it's ideal because the regional settings of the computer and
- 04:57 possible differences in Excel versions does not corrupt your data.
- 05:02 On the Home tab under the number grouping, I can click on the drop-down and
- 05:07 I can select a short date format for my dates.
- 05:10 And that fixes the date column.
- 05:14 Now, the last thing that appears to be wrong here,
- 05:17 if you look at the name column, is that the alignment is not quite right.
- 05:21 And this often happens with text columns.
- 05:23 The problem is that text often imports extra spaces,
- 05:26 sometimes before a word, sometimes after, sometimes there's extra spaces in between.
- 05:32 To fix that, I'm going to insert a column and I'm going to use a trim function.
- 05:37 I'm going to get rid of this column again later.
- 05:39 But what the trim function does is it will remove all the spaces before the word, and
- 05:44 all the spaces after the word, and all the extra spaces in between words.
- 05:49 So in between words, there should only be one space, but
- 05:53 if there are two or three it will remove the extras.
- 05:57 So we'll use the trim function on the first record here in our data.
- 06:01 And once we've done that, we can copy the formula down by double-clicking
- 06:07 on this fill handle, the little square in the bottom right-hand corner.
- 06:12 And now we have all our names.
- 06:14 So now we want to copy the clean version of the names into the first column.
- 06:20 We do Ctrl+C or Cmd+C on the Mac to copy.
- 06:24 We move across to the correct column, but
- 06:27 we don't do a regular paste because we'd be copying and pasting the trim formulas.
- 06:33 Instead, let's do a paste special.
- 06:35 So let's go up to paste.
- 06:37 Paste special, and I want to paste values only.
- 06:42 And then we hit OK.
- 06:43 And that sorts out all the extra spaces.
- 06:47 And now we can select that second column, and we can delete it.
- 06:52 And that sorts out and cleans up all my email marketing data.
- 06:56 As a marketer, data is king.
- 06:59 Without knowing how to quickly and accurately clean large sums of data,
- 07:03 I'm left with two options, to manually clean it or not use it at all.
- 07:08 Cleaning it manually would take far longer and
- 07:11 may even result in more errors being made along the way.
- 07:14 Not using the data at all means missed insights and opportunities.
- 07:19 So, with these handy tools and tricks up your sleeve, you can have usable data
- 07:24 in a matter of minutes and can confidently use it knowing it's error-free.
Lesson notes are only available for subscribers.