How to Clean Data in Excel (PC/Windows Version) [Tutorial Video] (10:20)

NOTE: This tutorial on how to clean data in Excel uses the PC/Windows version shortcuts. We do cover Mac Excel shortcuts in our full Excel & Financial Modeling Fundamental course, but they’re presented in completely separate sets of lessons.

Below, we will list the key PC/Windows shortcuts as well as Mac alternatives.

How to Clean Data in Excel, Part 1: Typical Tasks, Functions, and Shortcuts

Our task here is simple: we have customer order data, but the addresses are not presented in an ideal format.

We want to separate the entire “Address” for each order into Street Address, City, State, and ZIP Code fields:

To do that, we will use the following functions and shortcuts:

Navigation, Selection, and Row/Column Shortcuts:

Ctrl + Spacebar: Select Column (same in Mac Excel)

Shift + Spacebar: Select Row (same in Mac Excel)

Shift + Ctrl + Arrow Keys: Select to Boundary (same in Mac Excel)

Shift + Arrow Keys: Select Multiple Cells (same in Mac Excel)

Alt, I, C: Insert Column (Ctrl + Shift + + in Mac Excel)

Alt, O, C, W: Change Column Width (No equivalent in Mac Excel)

Alt, O, C, A: Auto-Fit Column Width (No equivalent in Mac Excel)

F2: Edit Cell (same in Modern Mac Excel; may need Ctrl + U in older versions)

Alt, E, D: Delete Cells/Rows/Columns (Ctrl + – in Mac Excel)

Copy/Paste/Formatting Shortcuts:

Ctrl + 1: Format Dialog Box (⌘ + 1 in Mac Excel)

Ctrl + H: Replace (same in Mac Excel)

Alt, E, S, F: Paste Formulas (Ctrl + ⌘ + V, F in Mac Excel)

Alt, E, S, V: Paste Values (Ctrl + ⌘ + V, V in Mac Excel)

Useful Functions for Cleaning Data in Excel:

Alt, A, E: Text to Columns function (No equivalent in Mac Excel)

=PROPER: Makes first letter in each word uppercase (same in Mac Excel)

=TRIM: Removes extra spaces (same in Mac Excel)

=UPPER: Makes text all uppercase (same in Mac Excel)

=SUBSTITUTE: Replace text within text based on search (same in Mac Excel)

How to Clean Data in Excel, Part 2: Adding Columns and Text to Columns

We start this process by inserting a few extra columns to the right of the Address, City, State, and ZIP column, and then using the Text to Columns function (Alt, A, E) to separate the data based on the commas:

We select cell F3, right next to the original data, for the destination cell, and then click “Finish”:

We can now delete the original data column, select all the new data, and then cut and paste it into the original column (E):

At this point, we can also change the title of each column to something more appropriate, such as Address, City, State, and ZIP.

How to Clean Data in Excel, Part 3: Fixing Each Column

We can fix the ZIP Code formatting by selecting the whole column with Shift + Ctrl + Down, pressing Ctrl + 1, and then going to Number, Special, and Zip Code:

The City and State columns have extra spaces at the beginning and end, as well as improper capitalization. To fix these issues, we can use the PROPER and TRIM functions for the City and UPPER and TRIM for the State:

We can then copy both these formulas, select to the bottom with Shift + Ctrl and the arrow keys, and then use Alt, E, S, F to paste these as formulas:

Now, we want to copy and paste these as values (Ctrl + C and then Alt, E, S, V) – this is because we want to delete the old data.

When we delete that old data, these formulas will stop working, so we need to paste everything as values first so that our spreadsheet saves these new, correct values.

Then, we can cut and paste these over the original, poorly formatted data with Ctrl + X and Ctrl + V:

Next, we can select these three columns with Shift + Ctrl + arrow keys and then use the Alt, O, C, A shortcut to auto-fit the column widths based on the widest item in each column:

Finally, to fix the Street Address column, we can use the PROPER function to capitalize each word correctly. We do not need TRIM because these names do not have extra spaces.

Once again, we enter PROPER, copy and paste the formula down with Ctrl + C and Alt, E, S, F, and then copy and paste values with Alt, E, S, V:

Once we’ve pasted these as values, we can then press Ctrl + X to cut this entire column and paste it over the original, incorrectly formatted data:

How to Clean Data in Excel, Part 4: Fixing Annoying, Remaining Problems

If you take a close look at this data, you’ll find a few small problems – for example:

We could try to use the SUBSTITUTE function to fix these issues, but it’s easiest to do a simple Find and Replace with Ctrl + H, where we search for ” Sw ” or ” Se ” and replace them with ” SW ” and ” SE ” respectively (and do something similar for Nw and Ne):

With that done, we can then auto-fit the Street Address column with Alt, O, C, A and then delete the blank columns on the right-hand side.

Cleaning up data in Excel is not that complicated if you know the appropriate shortcuts and functions – it’s just a matter of practice and becoming very efficient with the keyboard shortcut.