How To Create a Macro to Clean Excel Data

Suitable for users of Excel 2013, 2016, 2019, 2021, and Excel for Microsoft 365. 

Objective

Create an Excel Macro to automate the process of cleaning data and assign the Macro to a clickable button. 
 

Macros Explained

An Excel Macro is a set of instructions that help us automate repetitive tasks.

For example, maybe every Friday we put together a summary report. This involves several different steps that need to be executed in Excel. We might have to create a new spreadsheet, add a heading, format the heading, copy the data from another report, format the data and then print the report. 

If we have to do this every Friday, it’s pretty tedious and means we are wasting time on repetitive tasks that could be automated using a Macro. 

A Macro allows us to perform the task once and record the steps. We can then recall or re-run the Macro whenever we want to execute those steps again. It’s a great time-saving utility in Excel. 

Once the Macro has been recorded and saved, we can create a shortcut to it on the Quick Access Toolbar or even create our own Macro ribbon in Excel. Creating a custom ribbon is particularly useful if we have a lot of Macros. We can even attach Macros to buttons, shapes, and images so we can click on the button or shape and run the Macro. 

Macros essentially save us a huge amount of time. 

It’s important that we are clear on the steps we need to record before we record the Macro. If we are recording lots of steps, it’s good to write the steps down first so we aren’t clicking around looking for the command we need when recording. 

I would also recommend making a few copies of the worksheet in case something goes wrong. When we run a macro, if it fails or doesn’t work correctly, we cannot use Undo to back out of the changes. 

In this article, we are going to create a macro to clean up a dataset prior to analyzing. 

Let’s take a look at the data. 

We need to Macro to perform the following steps:

• Remove all blank rows from the data.
• Remove any duplicate entries. 
• Make the case in column A consistent. 
• Split the customer name column into two columns, ‘First name’ and ‘Last name’. 
• Apply date formatting. 
• Remove hyperlinks. 
• Only show the last 4 digits in the credit card number. 

We are going to create a macro to automate these steps. 

Turn on the Developer tab

Before we begin, we need to ensure we have the Developer tab visible in Excel. This tab is not visible by default. 

• Click on the File tab. 
• Click on Options. 
• Select Customize Ribbon from the list. 
• On the right-hand side, put a tick in the box next to the Developer ribbon. 
• Click OK. 

SET UP a Macro

Before we record the macro, we need to do some basic setup. 

• Click on the Developer tab. 
• In the Code group, click on Record Macro. 

NOTE: We can also record macros from the View ribbon in the Macros group. 

The Macro Recorder will open. We need to give our Macro a name. Ensure that the name is descriptive so other people looking at this workbook understand what the Macro does.

Macro names should either be one word or multiple words with no spaces or separated with underscores. 

We can then assign a shortcut key to the Macro. This is optional but it will allow us to run our Macro quickly using the keyboard. Remember, many keyboard shortcuts are already assigned to other commands in Excel. Don’t assign Ctrl+C, Ctrl+V, or other common shortcuts to the macro as it will overwrite the current functionality of the shortcut. A safe option is to use a Ctrl+Shift keyboard shortcut. 

We then need to choose where to store the Macro. If we store the Macro in This Workbook, the Macro will only be available for use in the current workbook. If we choose to store the Macro in Personal Macro Workbook, the Macro will be available to use in all workbooks. 

• Select This Workbook. 

• Add a Description. Make this as detailed as possible. 

• Click OK. 

Record a Macro

As soon as we click OK, we are recording the Macro. We know when we are recording a Macro as we will see the button Stop Recording in the Code group of the Developer ribbon. 

Macros do not record how much time it takes to perform a task. So, we could start the recorder, leave our desk for 10 minutes, and then record the rest of the Macro. The Macro will not record that 10-minute break. 

The Macro Recorder will record where we click on the screen which is why it is a good idea to know the steps we need to execute in advance. 

Remove blank rows

The first step is to remove the blank rows from the dataset. 

• Select columns A to E. 
• From the Home tab, in the Editing group, click the drop-down next to Find & Select. 
• Click Go To Special. 
• Select Blanks. 

• Click OK. 

This will highlight the blank cells within the selection. 

• From the Home tab, in the Cells group, click the drop-down under Delete. 
• Click Delete Sheet Rows. 

Remove Duplicate Entries

The next step is to check for and remove any duplicate entries in the data. 

• Click anywhere in the data. 
• From the Data tab, in the Data Tools group, click Remove Duplicates. 
• Make sure all columns are selected and click OK. 
• Click OK again to confirm the removal. 

Change Case

Next, we need to make the case consistent in column A. Currently, the customer names are in upper, lower, and proper cases. We need to make all of them a proper case (capitalized first letter of each word only). 

• Select column B. 
• Right-click the mouse and choose Insert from the menu to insert a new blank column. 
• Click in cell B2. 
• Type =PROPER(A2) 

• Press Ctrl+Enter to stay in the same cell. 
• Double-click the fill handle to copy the formula down. 

We can now delete column A, however, the formulas in column B refer to column A. If we delete column A we will get errors in column B so we need to copy the values in column B and use paste special. 

• Select column B. 
• Press Ctrl+C. 
• From the Home tab, in the Clipboard group, click the drop-down arrow underneath Paste. 
• Click Paste Values.

• Press ESC to de-select the cell range. 
• Select cell A1. 
• Drag the cell across to B1 to move the heading. 
• Select column A. 
• Right-click the mouse and click Delete. 

Split Names

Next, we need to split the customer name into two columns, ‘First Name’ and ‘Last Name’. We can do this using the Flash Fill Utility. 

• Select column B. 
• Right-click the mouse and click Insert to insert a new blank column. 
• In cell B2, type the first name ‘Brandi’. 
• Press Ctrl+Enter to stay in the same cell. 
• Highlight all cells in the column. 

• From the Data tab, in the Data Tools group, click Flash Fill or press Ctrl+E. 

• Select column C. 
• Right-click the mouse and click Insert to insert a new blank column. 
• In cell C2, type the last name ‘Rodriguez’. 
• Press Ctrl+Enter to stay in the same cell. 
• Highlight all cells in the column. 
• From the Data tab, in the Data Tools group, click Flash Fill or press Ctrl+E. 
• Add headings to columns B and C, ‘First Name’ and ‘Last Name'.
• Select column A. 
• Right-click the mouse and click Delete. 

Apply Date Formatting

We need to apply the correct formatting to the dates in column D. 

• Select column D. 
• From the Home tab, in the Number group, click the drop-down next to General. 
• Click Short Date. 

Remove Hyperlinks

Next, we need to remove the hyperlinks from the email addresses in column E. 

• Select column E. 
• Right-click the mouse and select Remove Hyperlinks from the menu. 

Hide the Credit Card Number

The final step is to hide the credit card number and only display the last 4-digits. The rest of the credit card numbers will show as asterixis (*). 

• Click in cell G2. 
• Type =”****-****-****-“&RIGHT(F2,4)

This formula replaces the first 12 digits with asterixis and then extracts the last 4 digits from the credit card number. 

• Use the fill handle to copy the formula down. 

• Select cells G2:G21. 
• Press Ctrl+C. 
• From the Home tab, in the Clipboard group, click the drop-down underneath Paste. 
• Select Paste Values. 
• Press ESC to deselect. 
• Select cell F1 and drag it to cell G1 to move the heading. 
• Select column F. 
• Right-click the mouse and click Delete. 

Our clean dataset should look like this. 

Stop Recording

Once the steps have been completed, we can stop recording the Macro. 

• From the Developer tab, in the Code group, click Stop Recording. 

Run the Macro

Let’s test the Macro works by running it on one of the copies we made of our workbook. We can re-run the Macro in a couple of ways: using the Macros button or using the keyboard shortcut. 

• From the Developer tab, in the Code group, click Macros. 
• Select the Macro and click Run. 

The Macro will now execute the recorded steps. If there is an issue or the Macro doesn’t work, we will receive an error that we can investigate. This is where it becomes beneficial to know some basic VBA. 

Assign a Macro to a button

To make our Macros easier to run, we can add them to the Quick Access Toolbar, create our own Macro ribbon, or assign them to a clickable button, shape, or image. 

We are going to create a button on the worksheet and assign the Macro to it. 

• From the Developer tab, in the Controls group, click the drop-down under Insert. 
• From the Form Controls group, select Button (Form Control). 
• Draw a button on the worksheet. 

The Assign Macro window will open. 

• Select the ‘Clean_Data’ Macro from the list. 
• Click OK. 

• Double-click on the button and rename it to ‘Clean Data’. 

We can format the button. 

• Right-click on the button and select Format Control. 

• Click the button to run the Macro. 

Final Note about Macros

The Macro in this lesson was recorded using Absolute Referencing which is the default. This means that the cells we select when recording the Macro are absolute and the Macro will use these cells no matter what dataset we are running the Macro on. 

This can be confusing for people when first learning Macros. 

To make Macros more flexible so they can be used on similar data located in different cells, we need to create our Macro using Relative Referencing. 

Turn on Use Relative References on the Developer tab before recording the Macro.