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

Introduction

We interact with data almost every day. But having data is one thing, and having clean, usable data is another. This is where Excel comes in. It can be a good starting point for cleaning, organising, and analysing data.
Excel is an electronic spreadsheet program that allows users to organize, calculate, format, and analyze data.
Data cleaning is the process of identifying and correcting errors, missing values, duplicates, and inconsistent formats in raw data sets.

Basics of Excel

The first thing you'll see once you open Excel is rows, columns, and cells.
A row runs horizontally and is labeled with numbers (1, 2, 3), while a column runs vertically and is labeled with letters (A, B, C)
A cell is the individual rectangular box formed by the intersection of a vertical column and a horizontal row.
These rows, columns, and cells are contained within a worksheet.
One or more worksheets form a workbook.
The image below contains an example of an Excel worksheet with its features;

Inside the worksheet, there is basic information I need to know, such as;

• Sorting which is the process of rearranging your rows of data into a specific order based on the values in one or more columns. This includes arranging text from A to Z or Z to A, arranging numbers from smallest to largest or largest to smallest, and arranging dates and times from oldest to newest or newest to oldest.
• Filtering a tool that hides rows you do not want to see so you can focus only on the data that matches your rules.
• Basic formulas, where I learned that every formula has to begin with an equals sign (=). These formulas include addition, such as =1+1 or =A1+B1, and subtraction, such as =2-1 or =A1-B1.

• Formatting which is the process of changing the visual appearance of data in a spreadsheet to make it easier to read and understand without changing the actual data values.

  Data Cleaning

  I learnt some basic data cleaning techniques such as;

• Removing duplicates

• Handling missing values

• Removing extra spaces with TRIM()

• Standardizing text with UPPER(), LOWER(),PROPER()

• Fixing inconsistent dates

To understand how data cleaning works in Excel, I used a small example containing employee records. At first, the data looked normal, but a closer look revealed some errors.

Employee Name	Department	Location	Hire Date
john kamau	Finance	Nairobi	12/01/2024
Jane Wanjiku	HR	nairobi	15/01/2024
John Kamau	Finance	Nairobi	12/13/2024
brian Otieno	IT	NAIROBI	18/01/2024
Mary Akinyi	HR	Kisumu	20/01/2024

1. Removing duplicates

The first issue I noticed was that John Kamau appeared twice with the same information. Duplicate records can affect analysis by making some results appear higher than they actually are.

I used Excel's Conditional formatting feature to identify and remove the repeated record.

2. Standardising text

The Location column also contained different versions of the same location:

• Nairobi
• nairobi
• NAIROBI

Although they refer to the same place, Excel can treat them as different values when analysing the data.

I standardised the entries so that they followed the same format.

3. Checking dates

The Hire Date column also needs attention. Dates may appear in different formats depending on how the data was entered. I checked that the values were recognised as actual dates and then applied a consistent date format. Such as correcting the 12/13/2024 to 12/01/2024.

4. Fixing the Word format

The employee name format is in improper format with words such as john kamau in lower cases instead of proper cases. So i used the formula =PROPER(A2) to change the format to John Kamau

Conclusion

I initially thought cleaning data meant simply removing blanks and duplicates. I learned that consistency is equally important, especially with dates, categories, and text.