Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

Introduction

Excel is a useful tool for working with data, but before any analysis can begin, the data needs to be in a usable state. A dataset may have blank cells, duplicate records, inconsistent entries, or values that do not follow the expected format.

Cleaning the data helps correct these issues and makes the dataset more consistent. Once the data is clean, it becomes easier to use Excel tools such as formulas, Pivot Tables, filters, and charts to analyze and understand the information.

1. Start With a Copy of the Data

Before making any changes, keep the original dataset untouched and work from a duplicate sheet.

Keeping the original data provides a reference point in case values are accidentally changed or removed.

2. Make the Data Easier to Read

Formatting makes a large dataset easier to inspect without changing the actual data.

Wrap Text

Wrap Text displays long content on multiple lines within the same cell.

AutoFit

AutoFit automatically adjusts the width of a column or height of a row according to its contents.

3. Cleaning the Data

Before analysing the data, it is important to check for issues that could affect the results. This includes missing values, inconsistent entries, duplicates, unnecessary columns, and values that do not make sense.

Missing and Inconsistent Values

Missing information may appear as an empty cell or as values such as: None, N/A, Unkown

Some of these values are not technically blank because they contain text, but they may still represent missing information.

The meaning of a value should be checked before changing it. For example, None may represent missing information in one column but be a valid response in another.

Where different entries have the same meaning, they can be standardized. For example, None, N/A can be changed to Unkown

This keeps the data consistent and makes it easier to analyse.

Handling Blank Values

How a blank value is handled depends on the type of information in the column.

A blank may be:

• Left blank if the correct value is not known.
• Replaced with Unknown for categorical data.
• Filled with an appropriate value where its meaning is known.
• Investigated further before making any changes.

For numerical columns, adding text such as Unknown should be avoided because it can interfere with calculations.

Checking for Duplicates

Duplicate records can affect totals, counts, averages, and other calculations.

It is safer to select the entire dataset when removing duplicates, as similar records may contain typos or inconsistencies.

Checking for Unusual Values

The dataset should also be checked for values that do not make sense when compared with other information in the same record.

For example:

Hiring Year: 1920
Age: 50

Both are valid numbers, but they are unlikely to make sense together.

Unusual values should be investigated rather than automatically deleted because some may be genuine.

After these checks, the dataset is more consistent and ready for validation and further analysis.

4. Filters

Once the dataset has been cleaned, filters can be used to view specific records without deleting or changing the rest of the data.

For example, the HR dataset can be filtered by:
Work Experience = 7 Years
Education Level = Bachelors
Employment Type = Permanent

Filters are useful when checking particular categories, identifying unusual records, or focusing on a smaller part of a large dataset.

5. Multilevel Sorting

Sorting arranges data in a particular order. Excel also allows sorting by more than one column.

For example, the dataset can be sorted by:

1. Work Experience
2. Education Level
3. Employment Type

Excel first sorts by Work Experience, then by Education Level within each department, and finally by Employment Type

This is known as multilevel sorting.

6. Text Functions

Excel provides several functions for working with text. These can be useful when cleaning names, codes, email addresses, and other text fields.

Functions always start with the equal sign i.e =

• LEN() – Counts the number of characters in a cell.
• TRIM() – Removes extra spaces from text.
• LEFT() – Extracts characters from the left side of text.
• RIGHT() – Extracts characters from the right side of text.
• MID() – Extracts characters from the middle of text.
• FIND() – Finds the position of a character or text within a cell.
• CONCAT() – Joins text from multiple cells together.

These functions can be used individually or combined depending on how the text needs to be cleaned or changed.

7. Conditional Formatting

Conditional Formatting changes the appearance of cells when they meet certain conditions. This makes it easier to notice important or unusual values.

Common conditions include:

• Greater than
• Less than
• Between
• Top 10
• Bottom 10
• Above average
• Below average

For example, employees who have worked for less than 18 years can be highlighted automatically.

Data Bars provide another way of comparing numerical values. Excel displays a bar inside each cell based on the size of the value.

Larger values have longer bars, making differences easier to see without creating a separate chart.

8. Statistical and Mathematical Functions

These are for carrying out basic calculations and understanding numerical data.

• POWER() – Raises a number to a specified power.
• SQRT() – Returns the square root of a number.
• PRODUCT() – Multiplies numbers together.
• MIN() – Returns the smallest value.
• MAX() – Returns the largest value.
• MODE() – Returns the most frequently occurring value.
• MEDIAN() – Returns the middle value.
• COUNT() – Counts cells containing numbers.
• COUNTA() – Counts non-empty cells.
• COUNTBLANK() – Counts empty cells.

These functions provide a quick way of understanding the values contained in a dataset.

9. COUNTIF() & SUMIF()

COUNTIF() and SUMIF() are useful for counting or adding values based on specific conditions in a dataset.

• COUNTIF() – Counts records that meet one condition.
• COUNTIFS() – Counts records that meet multiple conditions.
• SUMIF() – Adds values that meet one condition.
• SUMIFS() – Adds values that meet multiple conditions.

10. Working With Dates and Time

Dates are useful in data analysis because they can be used to calculate periods and identify trends.

Some common Excel date functions include:

• TODAY() – Returns the current date.
• NOW() – Returns the current date and time.
• YEAR() – Extracts the year from a date.
• MONTH() – Extracts the month from a date.
• DAY() – Extracts the day from a date.
• DATEDIF() – Calculates the difference between two dates.
• NETWORKDAYS() – Calculates working days between two dates, excluding weekends.

11. Mean, Median, and Outliers

The mean is the average of a group of numbers, while the median is the middle value when the numbers are arranged in order.

Consider the below example:

The value 500,000 is much higher than the other values. This affects the mean because it pulls the average upwards.

The median is less affected by extremely high or low values.

Comparing the mean and median can therefore help identify data that may be skewed or contain possible outliers.

An outlier should not automatically be deleted. It may be an incorrect entry, but it could also be a genuine value that is simply different from the rest.

IMPORTANT: Data Validation

Data Validation helps control what can be entered into a cell. This reduces inconsistent entries and helps keep the dataset clean when inputted at the data collection step.

For example, if the Department column should contain only HR, IT, Finance or Unknown

a dropdown list can be created to restrict entries to these values.

Without data validation, the same department could be entered in different ways. Excel would treat these entries as different values, creating inconsistencies and adding more data cleaning tasks.

Conclusion

The main takeaway is that good analysis starts with clean data. Preparing and cleaning the data makes it easier to work with and analyse.