DEV Community: Otuko Mauwa

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

Otuko Mauwa — Mon, 31 Aug 2026 01:39:32 +0000

Introduction

Excel is spreadsheet software tool that is wildly and commonly used to organize, format, calculate and analyze data in row and columns. It is widespread due to it's user friendly interface and powerful inbuild functions and features that makes it easy for beginners and professional alike to do day-to-day data related tasks. This article takes you through a step by step guidelines on how to use and navigate excel from basic navigation to practical data cleaning methods that are vital for data analytics.

Excel Workbook

1) Workbook and Worksheets
An excel workbook is the main file that can contain one or many worksheets. A worksheet is the single grid that contains multiple rows and columns. The worksheets are mainly found on the bottom left of the workbook. The below image indicates the different worksheets and a worksheet can be added by clicking the +

2) Cell
A cell is an intersection of arow and column.

3) Ribbon
A ribbon is a toolbar at the top part of a worksheet that has buttons and icon. It organizes tools into groups for quick formatting and data management.

Data Cleaning

Data cleaning is the process of finding and fixing errors so that the data can be easy to work on for accurate information and analysis. Before starting on data cleaning i copied the original worksheet to another worksheet and renamed it to clean and the clean worksheet is where i will be working on cleaning the data.

1) Test Functions
Upper - Changes text characters to Uppercase
Lower - Changes text characters to lowercase
Trim - Removes spaces in characters before and after and also in between
Concat - Can be used to join characters of two cells like using the first name and last name to create an email address

2) Find and Replace
Find and replace is the initial step in data cleaning the dataset by merging similar data that were enter incorrectly such human resource and HR which basically mean the same thing but were not keyed to the dataset as one. Find and replace tool will be used to merge them into one.

3) 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.

4) Sorting and Filtering
The first step to data cleaning is sort each column from the largest to smallest for numeric columns and from A to Z for text columns to be able to find cells that have wrong date keyed in them. As highlighted below the age column has thirty keyed in as a text instead of number.

To change it from text written 'thirty' and correcting it into a number format

5) Removing Duplicates.
The second part is to remove duplicates which tends to distort data. On the ribbon the data part has the tool for removing duplicate and it displays a dialog box which then i selected the primary column which is unique to make removing duplicates effective.

6) 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
Power () - Raises the a number to a specified power
Product() - Multiplies numbers
Min() - Finds the smallest number in a row or column
Sum - Adds a range of numbers
Max() - Finds the largest number
Median() - Returns the middle number from a set of given numbers
Mode() -Returns the most occuring number in a set of numbers
Average() Adds a group of numbers together then divides the sum by count numbers

All the functions and formulas must start with an (=).

8) Statistical Functions
Count - Counts numerical cells only
Counta - Count the number of cells that are not blank
Countblank - Counts all the blanks in a columns or rows.

9)Conditional Aggregations
Countif - In the dataset I used countif to find the number of staff in IT department

Countifs - Used it to find the number of female staff that are above 30.

Sumif - Used it find out the total amount of salary for female staff.

Sumifs - Used it to calculate the total salary of male staff that are above 30

Averageif - Used it to calculate the average age of male staff.

Averageifs - Used to calculate the average age of male staff that are in the HR department

Conclusion

Excel serves as a vital software tool that is used in data analytics due to its reliability and easy to use features that makes complex tasks easy to manage. Data cleaning is the corner stone of analytics that determines whether can be trusted and used for making bold decisions that shape outcomes of a business.

Using the above techniques and guidelines assures you of more reliable way of data cleaning.

My First GitHub Project: From a Local Folder to GitHub Using Git and SSH.

Otuko Mauwa — Sun, 23 Aug 2026 01:20:14 +0000

Introduction

Git is local software that i installed on my machine to help me manage codes while github is an online platform that host git repositories.
This is a step by step guide of how i used git to publish my first project to github while a SSH key for a secure connection with github.

Tools Used

This task was done in the following environments

Windows Operating system
Git

git --version
git version 2.55.0.windows.4

Github account

Actions Taken to Link Git and Github

This are the step by step methods that i used to mlink git and github

1) Configuring Git

Configured Git with my github username and the email I used in setting up my github account

git config --global user.name "alfieotuko"
git config --global user.email "alfiemauwa7@gmail.com"

2) Generating the SSH key

The next action is to generate ssh key. The command will generaye two ssh keys, a private ssh key which should not be share and a public ssh key which is what will be used to link the git and github. The generated public ssh key will be copied to be then pasted on github.

ssh-keygen -t ed25519 -C "alfiemauwa7@gmail.com"
clip < "c/user/omolo/".ssh/id_ed25519.pub

3) Inserting the SSH to Github

The copied ssh key will be be added to github by going to the github settings under the SSH AND GPG keys and then clicking on the ssh key. To confirm if the added ssh key was successful;

ssh -T git@github.com

After which it will display a message that it was successful.

4) Creating Local Folder
Creating a folder in the local directory that will hold data of the project.

cd Desktop
mkdir kenya-hospital-records-analysis
cd kenya-hospital-record-analysis

5) Creating a File and a Folder

Creating a README.md file inside the kenya-hospital-record-analysis folder alongside the file also creating a folder named "data".

touch README.md
mkdir data

6) Creating a Git Repository

This is to initialize and turn the folder in a git repository and to check in which branch is the folder in

git init 
git status
On branch main

7) Stage and Commit

This is to stage and saving the project checkpoint history but still not saved in online cloud

git add .
git status 
On branch main
git commit -m "first commit for kenya-hospital-records-analysis"

8) Linking To Github

Creating a new and empty repository on github and copying the the URL of the ssh from github and pasting it on git there by saving it on the online platform.

git remote add origin git@github.com:alfieotuko/kenya-hospital-records-analysis

9) Pushing to Github

To save the repository to github

git push -u origin main

Adding URL Link

Here is the link to my github
alfieotukoGithub

Conclusion

The local folder was saved and hosted in a cloud based github repositories with the ssh providing a secure hosting.