DEV Community: Janet Kangogo

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

Janet Kangogo — Wed, 02 Sep 2026 19:13:35 +0000

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:

Work Experience
Education Level
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.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push

Janet Kangogo — Mon, 24 Aug 2026 17:50:30 +0000

Git is used to track changes made to files in a project. The basic Git workflow has four main parts:

Working Directory → Staging Area → Commit → Push

A simple project can contain different types of files, for example:

hello.py
practice.sql
README.md

The same Git workflow can be used for all these files.

Working Directory

The working directory is where the project files are created and edited.

For example, hello.py can contain:

A SQL file can contain:

And README.md can contain:

When a file is changed, Git can detect the change.

The current state of the project can be checked using:

git status

It can show modified and untracked files, staged and unstaged changes, as well as the current branch.

The output can show something like:

Which means the file has been changed, but the change has not been staged yet.

Staging Area

The staging area is used to select the changes that should go into the next commit.

To stage hello.py:

git add hello.py

On checking git status, the output can show something like:

More than one file can also be staged:

git add hello.py practice.sql

To stage all the changes:

git add .

Note that: Not every change has to be included in the same commit.

Commit

After staging the required changes, a commit can be created:

git commit -m "Your Commit Message"

A commit records the staged changes in the local Git repository.

The message after -m describes the change.

The commit history can be checked using:

git log

This shows the commits that have already been made.

Create a repository on Github

A repository should be created on GitHub where the project would be pushed.
The GitHub repository acts as the remote repository for the project.

On GitHub, New repository or the “+” icon was selected, then a name and description were added before creating the repository.

Push

A commit is stored in the local repository. It does not automatically go to GitHub.

Push: Sends committed changes from the local repository to the remote repository, such as GitHub, using git push or git push origin main to specify the remote (origin) and branch (main).

End Result

When you refresh git hub, the repository is updated

The Full Workflow

The Git workflow can be shown as:

Working Directory > git add > Staging Area > git commit> Local Repository > git push > Remote Repository (GitHub)

Conclusion

The Git workflow shows how changes move from the working directory to GitHub. The changes are checked using git status, added using git add, committed using git commit, and then pushed using git push.