These data cleaning tricks for HR professionals are the only tricks and tips you need to make your life as an HR officer or manager effortless.

You rely on the Microsoft Excel spreadsheet app to handle all data in your human resources management department or agency. That’s not all! Most of the time, all new data comes in an Excel workbook. So, old and new data can easily become unusable if not formatted and cleaned correctly.

Follow along with me as I explain the most useful and commonly used Excel data cleaning methods that are specific to HR datasets and processes. Let’s get started!

Convert Datasets to Excel Tables

The most common mistake that any HR person makes is not converting the raw data into a table. When you transform an existing dataset into an Excel table, you can easily sort and filter the dataset to make it more presentable and easy to read.

Moreover, you can perform structured referencing, automatic total row calculations, dynamic range expansion, data validation, removing duplicates, and conditional formatting.

So, whenever you start working with raw datasets downloaded from software or online databases, convert the dataset to a table.

To do this, select the part or all of the dataset that you want to analyze.

Press Ctrl + T.

The Create Table dialog box will show up.

Click OK.

Excel will convert the raw dataset into a table.

Use Sorting and Filtering

Often, data in an Excel workbook contains unusual characters that make no sense. Instead of manually removing those, you can simply use the Sort & Filter tool to pull the correct and appropriately formatted data to the top of the workbook and then easily delete the unwanted rows.

For example, look at the screenshot shown above. I’ll show you how to extract the valid records using the Sort & Filter tool by eliminating the rows that contain unwanted characters.

Besides, you can also slice and dice your dataset with the Sort & Filter tool to reduce the data load on the audience’s eyes by filtering to a specific range of data that matters the most. So, let’s find out how to use this tool efficiently:

Navigate to the source dataset you need to clean. Press Ctrl + Shift + L to activate the Sort & Filter tool for all the column headers of the dataset.

Now, click on the first column header’s Sort & Filter drop-down arrow and unselect the entry that shows bizarre characters.

Repeat the step for all other columns in the dataset.

As soon as you unselect odd characters and click OK on the Sort & Filter context menu, Excel implements the modification instantly.

So, by the time you unselect all the strange characters using the Sort & Filter tool, your dataset contains all the valid entries.

You can now select the whole data by pressing Ctrl + A and copy by pressing Ctrl + C.

Then, go to a different worksheet or a different cell in the same worksheet and press Ctrl + V to paste the cleaned data.

There you go! You’ve successfully cleaned your HR management dataset using the Sort & Filter tool.

In another example, let’s say you’ve got a dataset that resembles the one given above in the screenshot.

The dataset contains employee records from different departments, but you only need to present the ones that work in the production facilities.

Here, you can apply the Sort & Filter function by pressing the Ctrl + Shift + L keys altogether.

Now, click on the Sort & Filter drop-down menu for the column that indicates the department for the employees, like Facility in the current exercise.

Uncheck all except for the Production one.

Click OK.

The data will be filtered and sorted to hide employee records from all departments except for the Production department.

You can copy and paste the data to a different cell range.

You’ve successfully cleaned your data of unwanted entries.

Fill Missing Cell Values

Suppose there are data missing in a data entry workbook because the data entry agent manually punched in the data from paper sheets. If you’ve got the reference source data in a paper and you’d like to fill up those blank cells with appropriate data from the source, this method will come in handy.

You don’t need to find blank cells in the worksheet manually by looking for them. Instead, you can use the Go To Blank feature of the Go To Special tool. Let me show you how to use this Excel feature.

Select the dataset or cell range where blank cells exist.

Press Ctrl + G to bring up the Go To dialog box.

Click on the Special button in the Go To dialog box.

Click Blanks in the Go To Special dialog box.

Excel will highlight all the cells that are blank.

You can click on the Fill Color command in the Font block of the Home tab.

Select a cell background color other than white or No Fill.

Excel will color the blank cells with the shade you’ve selected from the Fill Color context menu.

Now, you can easily fill up these blank cells and make the dataset complete by manually entering the missing data in the blank cells.

Find and Remove Duplicate Data

If your dataset contains duplicate data, don’t try to go through the whole worksheet and manually detect and delete duplicate data. Instead, you can automate the process using the Remove Duplicates command in the Data Tools block of the Data tab in the Excel ribbon menu.

Open the worksheet that contains a dataset where you suspect duplicates could exist.

Select the entire dataset and click on the Remove Duplicates command in the Data Tools block of the Data tab.

The Remove Duplicates dialog box will show up.

Click the OK button to execute the Remove Duplicates command.

Excel will remove all the duplicate values.

Sometimes, removing duplicates can be a bit tricky if you’re working on a timesheet data set where the employee’s name should recur multiple times, but the date and time stamps should only be once.

For such situations, you need to tweak the Remove Duplicates tool a bit. Let me show you below how.

Look at the dataset shown above. In this time sheet entry, 5/3/2025 repeats two times and is hence considered a duplicate.

Select the dataset and click on the Remove Duplicates command.

Go to the Columns section of the Remove Duplicates dialog box and uncheck the column headers below which there are no duplicates.

Click OK.

Excel will remove the duplicate entries in the selected column only.

As you can see in the current example, Excel removed duplicates by the Date column and not by the First Name and Last Name columns.

Because I need to keep the duplicate values in the First Name and Last Name columns.

Delete Extra Spaces

Suppose you maintain a database of employee addresses in an Excel worksheet. You often copy and paste the addresses from this master worksheet and use the data in several online and offline apps. If the master file contains an unusual number of spaces in between text strings, you may get into trouble.

In this section, I’ll show you how to remove extra spaces from one or many text strings in Excel using an Excel VBA macro.

If you’re new to VBA macros, go to this Excel tutorial to learn how to create a macro from a VBA script:

📒 Read More: How To Use The VBA Code You Find Online

Now that you know the trick to create a VBA macro, use the following VBA script to set up a macro in your Excel workbook:

Sub RemoveExtraSpaces()
    Dim rng As Range
    Dim cell As Range
    
    ' Prompt user to select the range
    On Error Resume Next
    Set rng = Application.InputBox("Select the range of text cells", "Select Text Data", Type:=8)
    On Error GoTo 0

    ' Exit if user cancels
    If rng Is Nothing Then Exit Sub

    ' Loop through each cell and clean up spaces
    For Each cell In rng
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = Application.WorksheetFunction.Trim(cell.Value)
        End If
    Next cell
    
    MsgBox "Extra spaces removed!", vbInformation
End Sub

To run the VBA macro, press Alt + F8.

The Macro dialog box will show up.

Select the RemoveExtraSpaces macro and hit Run.

A dialog box will show up asking you to select the source data cell range.

Upon clicking the OK button, Excel VBA will remove all unwanted spaces from the text strings you selected.

⚠️ Warning: Any changes you make in the worksheet using a VBA script or macro become permanent. You can’t undo it. Make a copy of the workbook as a backup.

Transform Data Using Text to Columns

Sometimes, you get a spreadsheet file or CSV file that contains the data you need, but in a form that won’t fit in an Excel spreadsheet. When you import such datasets into your Excel worksheet, you either get all the data in one cell or spread across the worksheet in a form that can’t be used in any formula or other automation functions.

Here steps in the Text to Columns tool of Excel. It allows you to import CSV or other similar files into your Excel worksheet in an organized fashion so that you can use the imported data effortlessly.

Suppose you got text strings as shown in the above screenshot. You see a pattern in the text strings, which is first name, date of birth, address, and city. Let me show you below how to use the Text to Columns tool to convert these text lines into a tabulated dataset.

Select the input cell range, preferably a column, and click on the Text to Columns command in the Data Tools block of the Data tab.

The Text to Columns wizard will show up. Select the Delimited option in the dialog box and click Next.

On the next screen, choose the delimiter, which is a comma in the current tutorial, and click Next again.

Click Finish on the third dialog box.

The input text strings will be converted to a dataset containing delimited data in columns.

You can now restructure the extracted data into a table showing Full Name, DOB, Address, and City. Refer to the screenshot given above.

Use Proper Date and Time Formats

Properly formatting dates in Excel is a challenging task because Excel processes date entries as numbers instead of actual dates. So, you often get bizarre date entries in spreadsheet files extracted from a different software or as a CSV file.

For example, the date entries in the dataset shown above aren’t in a useful format and need fixing.

Select the column that contains date entries and click on the Short Date option in the Number Formatting context menu in the Number block of the Home tab.

Don’t forget to use the Short Date number formatting function in both the Start Date and End Date columns.

That’s it! You’ve successfully fixed the date formatting in your Excel dataset.

Perform Spell Checking

If you need to process a lot of text strings in your Excel worksheet and are worried about the spelling in the dataset, you can use the built-in Spelling tool in the Proofing block.

Select the cell range that contains the text strings you want to spell check.

Click on the Spelling command in the Proofing block of the Review tab.

The Spelling dialog will show up.

Keep clicking on the AutoCorrect button until all the text strings have been proofread for spelling mistakes and corrected.

Suppose there are 3 spelling issues in one text string, you must click the AutoCorrect button three times. If it’s five text strings, you’re looking at 15 clicks of the AutoCorrect button.

Data Validation to Prevent Errors

When creating data entry forms for HR, use Excel’s Data Validation tool to ensure only approved content is entered. For instance, restrict a column to accept only numbers. This keeps your shared worksheet clean, consistent, and error-free.

Suppose you want to add the following Data Validation rules for the dataset shown above:

• First Name: From the designated list
• Last Name: From the designated list
• DOB: Only short dates as MM/DD/YYYY
• Access ID: Only alphanumeric values
• Department: From the designated list

Firstly, select the cell ranges that will only accept characters from the English alphabet.

Click on the Data Validation command in the Data Tools block of the Data tab.

The Data Validation dialog box will open.

Change the Allow option to List and use the Source field to either enter a list of first names, last names, and department names manually or enter the workbook, worksheet, and cell range references where these lists exist.

Navigate to the Input Message tab and enter a Title and Input message in the respective fields. These messages will flash to inform the users.

Click OK to save the changes you’ve made.

Follow the same steps to assign the required Data Validation rules for the DOB and Access ID columns.

Conclusions

So these are some of the data cleaning tricks you can use in Microsoft Excel when you process a massive amount of data as an HR.

If you liked the guide or have found the techniques useful, use the comment box below to share your acknowledgement. You can also share suggestions if you have any.