DEV Community: Collins Muatha

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

Collins Muatha — Sun, 06 Sep 2026 17:09:13 +0000

Introduction
Excel is a spreadsheet software tool that is widely used to organize, format, calculate, and analyze data in rows and columns. Its popularity stems from its user-friendly interface and powerful built-in functions and features, which make it easy for beginners and professionals alike to handle day to day data tasks. This article takes you through step-by-step guidelines on how to use and navigate Excel—from basic layout components to the practical data cleaning methods that are vital for data analytics.

Excel Workbook Basics

Workbook and Worksheets
An Excel workbook is the main file that can contain one or many worksheets. A worksheet is a single grid containing multiple rows and columns. The worksheet tabs are located at the bottom-left corner of the workbook window. You can easily add a new worksheet by clicking the + icon next to the existing tabs.
Cell
A cell is the individual block formed at the intersection of a row and a column. Each cell has a unique address (like A1 or B2) used to reference and store data points.
Ribbon
The Ribbon is the extensive toolbar located at the top of the Excel window. It houses a variety of buttons, commands, and icons. To make navigation intuitive, it organizes these tools into specialized tabs and groups for quick formatting, mathematical modeling, and comprehensive data management.

*Data Cleaning *
Data cleaning is the process of identifying and correcting errors within a dataset so that it is reliable, accurate, and ready for analysis. As a best practice before beginning, always copy your original worksheet to a new sheet. Renaming this secondary sheet to "Clean" allows you to preserve your raw data intact while performing all modifications safely on the duplicate workspace.

Text Functions
Text functions are used to standardize strings, repair data entry inconsistencies, and combine fields:UPPER: Converts all lowercase characters in a text string to uppercase.LOWER: Converts all uppercase characters in a text string to lowercase.TRIM: Strips out irregular spacing before, after, and between text characters, leaving only single spaces.CONCAT: Joins text strings from multiple cells together (e.g., combining a "First Name" and "Last Name" column to generate standard corporate email addresses).
Find and Replace
Find and Replace is often the initial step in cleaning a dataset. It is used to standardize entries by merging variations of data that were entered incorrectly or inconsistently due to human error. For example, a dataset might contain both "Human Resource" and "HR." While these mean the exact same thing, Excel treats them as different values.To resolve this, you can open the tool using the keyboard shortcut Ctrl + H. In the dialogue box that appears, you enter the inconsistent text in the "Find what" field and your standardized term in the "Replace with" field. Clicking "Replace All" instantly merges these variations into a single, uniform term across your entire spreadsheet.
Conditional Formatting
Conditional formatting is a tool that changes the appearence of cells, it let me use colors or databars to highlight high values or errors without having to look at rows or columns manually.
Sorting and Filtering
Sorting and filtering are essential for auditing data. Sorting text columns from A to Z and numeric columns from Largest to Smallest quickly exposes data entry errors. For example, sorting a numerical column like Age immediately isolates text anomaliessuch as the word "thirty" typed instead of the number 30making them easy to find and fix. As highlighted below the age column has thirty keyed in as a text instead of number.
Removing Duplicates
Duplicate entries distort data metrics and cause calculation errors. To remove them, select your dataset and navigate to the Data tab on the Ribbon, then click Remove Duplicates under the Data Tools group.This action opens a dialog box (as shown below). For an accurate cleanup, ensure you check "My data has headers", then select the unique primary key column—such as Employee IDwhile unchecking the others.This tells Excel to isolate and remove duplicate rows based strictly on unique identifier duplicates, keeping your main dataset completely clean.
Blank and Missing values
On the columns that have blanks i changed them to '0' on the numeric columns and on the text columns i changed them to 'Unknown'. This is done to reduce inaccuracies.

7 Aggregate Functions
Aggregate functions allow you to perform mathematical operations across an entire column or row range:SUM: Adds a selected range of numbers together.PRODUCT: Multiplies a series of numbers together.POWER: Raises a specific number to a designated power.MIN: Finds the absolute lowest numerical value in a data range.MAX: Finds the absolute highest numerical value in a data range.MEDIAN: Identifies the exact middle number from a sorted set of values.MODE: Returns the most frequently occurring number in a dataset.AVERAGE: Calculates the mathematical mean by summing a group of numbers and dividing by their count.

Statistical Functions
Statistical tracking functions help you audit data volume and monitor data density:COUNT: Counts cells that contain numerical values only.COUNTA: Counts any cell that is not completely blank (reads text, numbers, and errors).COUNTBLANK: Tracks dataset completeness by counting only the empty cells in a range.
Conditional Aggregations
Conditional functions allow you to count, sum, or average data only when specific business criteria are met:COUNTIF: Counts cells matching a single condition (e.g., finding the total number of staff assigned to the IT department).COUNTIFS: Counts cells matching multiple criteria simultaneously (e.g., finding how many female staff members are over the age of 30).SUMIF: Adds numerical values together based on a single condition (e.g., calculating the combined total payroll salary for female staff).SUMIFS: Adds numerical values together based on multiple criteria (e.g., calculating the total salary paid strictly to male staff members who are over 30).AVERAGEIF: Calculates the mean value based on one condition (e.g., determining the average age of all male staff).AVERAGEIFS: Calculates the mean value using multiple criteria (e.g., determining the average age of male staff members working inside the HR department).

Conclusion
Excel remains an indispensable asset in the data analytics toolkit because of its accessibility, intuitive interface, and deep structural tools.As covered throughout this guide, successful data analysis depends heavily on the foundational step of data cleaning.By mastering basic sheet layouts, applying precise text functions, using shortcuts like Ctrl + H, and strictly managing duplicate entries using primary identifiers like Employee ID, you transform chaotic datasets into trusted information spaces.When your data is clean, the deployment of aggregate, statistical, and conditional functions unlocks accurate operational insights allowing businesses to make confident, strategic choices based on factual metrics.

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

Collins Muatha — Sat, 29 Aug 2026 19:14:32 +0000

Introduction

When I first started learning Git, commands such as' git add',
'git commit', and 'git push' seemed confusing. I later understood
that each command represents a different stage in saving and sharing
project changes.

In this article, I explain the Git workflow using a small practical
project.

What is Git?

Git is a version control system that records changes made to files.
It allows developers to track project history, return to earlier
versions, and work safely on projects.

The Git workflow

The basic workflow is:

Working directory → Staging area → Commit → Remote repository

1. Working directory

The working directory is the project folder on my computer.
When I create or edit README.md, the changes first exist here.

Example:

'''bash
mkdir git-workflow-demo
cd git-workflow-demo
'''

2. Initialize a Git repository

'''bash
git init
'''

This command tells Git to start tracking the project.

To check the status:

'''bash
git status
'''

3. Staging area

The staging area allows me to choose which changes should be included
in the next commit.

'''bash
git add README.md
'''

To stage all files:

'''bash
git add .
''''

The staging area is useful because I may have several changes in my
working directory but only want to save some of them in one commit.

4. Commit

A commit is a saved snapshot of the staged changes.

'''bash
git commit -m "Add first Git workflow demo"
'''

A good commit message should be short and meaningful.

5. Connect the project to GitHub

After creating a repository on GitHub, I connect it to my local project:

'''bash
git remote add origin git@github.com:YOUR-USERNAME/git-workflow-demo.git
'''

I can confirm the connection with:

'''bash
git remote -v
'''

6. Push changes to GitHub

'''bash
git branch -M main
git push -u origin main
'''

The 'git push' command uploads local commits to the GitHub repository.

Making another change

After editing the file again, I can repeat the workflow:

'''bash
git status
git add README.md
git commit -m "Update project explanation"
git push
'''

Common mistakes

Forgetting to stage changes

If I commit without running git add, Git may report that there is
nothing to commit.

Using an unclear commit message

A message such as changes does not explain what was changed.
A message such as Update README instructions is clearer.

Pushing before configuring the remote

If Git does not know the GitHub repository address, I must first run:

'''bash
git remote add origin REPOSITORY-URL
'''

Sharing a private SSH key

The private key must remain on my computer. Only the public key should
be added to GitHub.

Conclusion

The Git workflow is easier to understand when viewed as a sequence:

Make changes in the working directory.
Select changes with 'git add'.
Save a snapshot with 'git commit'.
Upload the commit with 'git push'.

This small project helped me understand how local work is tracked and
shared on GitHub.

Useful commands

''''bash
git status
git add .
git commit -m "Meaningful message"
git log --oneline
git remote -v
git push
''''