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

In this tutorial on how to clean data in Excel, you’ll complete a practice exercise to clean up and separate address data into separate columns and fix problems with the capitalization and punctuation using the text and formatting functions.



For Aspiring Investment Bankers Only:

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

Claim Your FREE Discounted Cash Flow (DCF) Video Tutorial Series

Master Financial Modeling As It Is Performed In Real Life, With Our Simple 3-Step Method

FREE INSTANT ACCESS

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:

Customer Order Data in Excel 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:

Text to Columns Function

Text to Columns Function, Part 2

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

Text to Columns Function, Part 3

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

How to Clean Data in Excel - Cutting Data

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:

How to Clean Data in Excel - ZIP Codes

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:

PROPER and TRIM Functions

UPPER and TRIM Functions

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:

How to Clean Data in Excel - Pasting 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:

Cutting and Pasting Data

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:

Auto-Fit Column Width

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:

Fixing Address Data with PROPER

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 - Pasting Remaining 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:

How to Clean Data in Excel - Direction Abbreviations

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):

How to Clean Data in Excel - Direction Abbreviations, Part 2

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.

Master Excel & Financial Modeling

Learn Excel, valuation, and transaction modeling from the ground up - including stock pitches, case studies, deal and investment recommendations, and more.

LEARN MORE