DEV Community: Adrian Mageto

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

Adrian Mageto — Sat, 29 Aug 2026 23:57:19 +0000

Introduction

Excel is a program developed by Microsoft. Excel is used to organize data in column and rows and allows you to do mathematical functions.

Excel is typically used for :

Cleaning data
Data entry
Visuals and graphs
Financial modeling
Data analysis

Overview

This chapter is to give you an overview of Excel. Excel is made up of the Ribbon and the Sheet.

At the picture below, the ribbon is marked with a red rectangle and the sheet is marked with a green rectangle:

Ribbon

The ribbon contains App launcher, Tabs ,Commands as shown in the picture below.

The Sheet Explained

The Sheet is a set of row and columns. It forms the same pattern as we have in math exercise books, the rectangle boxes formed by the pattern are called cells.
Have a look at the picture below. Hello World was typed in cell C4. The reference can be found by clicking on the relevant cell and seeing the reference in the Name Box to the left, which tells you the cell address is C4.

Data Types

Text: Aligned to the left by default.
Numbers: Aligned to the right by default used in numeric calculations.
Currency: Formatted numbers displaying currency symbols or standard calender dates.
Percentanges: Decimal values displayed as parts of 100.

Data Organization :Sorting, Filtering and Tables

This allows for quick exploration without changing the original values.

Sorting and Filtering

Ranges can be sorted using the*Sort Ascending* and Sort Descending commands.
Sort Ascending: from smallest to largest.
Sort Descending: from largest to smallest.
The sort command work for text too, using A-Z order.
The commands are found in the Ribbon under the Sort & Filter menu.

This also means that numbers can also be sorted according to the formatted data type whether: dates, currency or age.

Filtering

Filter is similar to formatting a table, but it can be applied and deactivated.
The menu is accessed in the default Ribbon view or in the data section in the navigation bar.
Filters are applied by selecting a range and clicking the filter command.
It is important to have a row of headers when applying filters. Having headers is useful to make the data understandable.

Data Cleaning Tools

Important Data Cleaning techniques:

Handle missing values: Filter to isolate blank cells, or highlight them.
Removing Duplicates: Select your table go to Data> Remove Duplicates, and choose key columns.
Trimming Extra Spaces: Using the function =TRIM(cell address)
Using the Find & Replace command(CTRL+H)to swap out typos or unnecessary additional texts.

###Excel Conditional Formatting
Conditional formatting is used to change the appearance of cells in range based on your specified conditions.
The conditions are rules based on specified numerical values or matching text.

Conditional formatting is used to :

Highlight duplicate cells.
Highlight blank cells.
Highlight values above or below a certain number.
Can be used to create data bars and set icons.

Data Validation

Feature used to control and restrict the type of data entered into a cell.
Data Validation can be used to:

Create drop-down lists
Allow only whole numbers within a specific range.
Restrict entries to dates or specific texts lengths.

PivotTable

Is a functionality in Excel which helps you organize and analyze data.
It lets you add and remove values, perform calculations, and filter and sort data sets.
To create one ;

Click on a data set, and select Insert > PivotTable. PivotTables have components:

Columns

Columns are vertical tabular data.
The column includes the unique header, which is on the top.
The header defines which data you are seeing listed downwards.

Rows

Rows are horizontal tabular data.
Data in the same row are related.

Filters

Filters are used to select what data you see.

Values

Values define how you present the data.
You can define how you Summarize and Show values.

Fields and layout

The TablePivot is displayed how by your settings.
The PivotTable Fields panel is used to change how you see the data.
The settings can be separated in two: Fields and Layout.

Fields

The checkboxes can be selected or unselected to display or change the property of the data.

In this example, the checkbox for Speed is selected.

Speed is now displayed in the table.

![ ](https://dev-to-uploads.s3.us-east-2.amazonaws.com

You can click the downwards arrow to change how the data is presented.

Layout

Drag and drop fields to the boxes to the right to display data in the table.

You can drag them to the four different boxes that we mentioned earlier (four main components):

Filters
Rows
Columns
Values

Excel Functions

AVERAGE Function

The AVERAGE function is a premade function in Excel, which calculates the average (arithmetic mean).

It is typed =AVERAGE()
`
It adds the range and divides it by the number of observations.

Example:

The average of (2, 3, 4) is 3.
3 observations (2, 3 and 4)
The sum of the observations (2 + 3 + 4 = 9)
(9 / 3 = 3)
The average is 3

CONCAT function

CONCAT is a function in Excel and is short for concatenate.

The CONCAT function is used to link multiple cells without adding any delimiters between the combined cell values.

It is typed =CONCAT()
`

COUNT Function

The COUNT function is a premade function in Excel, which counts cells with numbers in a range.

It is typed =COUNT()

Note: The COUNT function only counts cells with numbers, not cells with letters.

COUNTA Function

The COUNTA function is a premade function in Excel, which counts all cells in a range that has values, both numbers and letters.

It is typed =COUNTA()

COUNTBLANK Function

The COUNTBLANK function is a premade function in Excel, which counts blank cells in a range.

It is typed =COUNTBLANK()
`
Note: The COUNTBLANK function is helpful to find empty cells in a range.

COUNTIF Function

The COUNTIF function is a premade function in Excel, which counts cells as specified.

It is typed =COUNTIF()

COUNTIFS

The COUNTIFS function is a premade function in Excel, which counts cells in a range based on one or more true or false condition.

It is typed =COUNTIFS:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The conditions are referred to as critera1, criteria2, .. and so on, which can check things like:

If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
The criteria_range1, criteria_range2, and so on, are the ranges where the function check for the conditions.

SUMIF Function

The SUMIF function is a premade function in Excel, which calculates the sum of values in a range based on a true or false condition.

It is typed =SUMIF:

=SUMIF(range, criteria, [sum_range])The condition is referred to as criteria, which can check things like:

If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
The [sum_range] is the range where the function calculates the sum.

SUMIFS Function

The SUMIFS function is a premade function in Excel, which calculates the sum of a range based on one or more true or false condition.

It is typed =SUMIFS:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] ...)
The conditions are referred to as criteria1, criteria2, and so on, which can check things like:

The [sum_range] is the range where the function calculates the sum.

SUM Function

The SUM function is a premade function in Excel, which adds numbers in a range.

It is typed =SUM

Note: The =SUM function adds cells in a range, both negative and positive.

How to use the =SUM function:

Select a cell
Type =SUM
Double click the SUM command
Select a range
Hit enter

MAX Function

The MAX function is a premade function in Excel, which finds the highest number in a range.

It is typed =MAX

MIN Function

The MIN function is a premade function in Excel, which finds the lowest number in a range.

It is typed =MIN

MODE Function

The MODE function is a premade function in Excel, which is used to find the number seen most times.

This function always returns a single number.

It is typed =MODE.SNGL
It returns the most occurring number in a range or array.

MEDIAN Function

The MEDIAN function is a premade function in Excel, which returns the middle value in the data.

It is typed =MEDIAN

LOWER Function

The LOWER function is used to lowercase text in a cell.

Changing the letter case of your cell values can be great when there is a lot of case inconsistency among the cell inputs or when preparing your dataset for case-sensitive usage.

It is typed =LOWER
If you want to use the function on a single cell, write:

=LOWER(cell)

RIGHT Function

The RIGHT function is used to retrieve a chosen amount of characters, counting from the right side of an Excel cell. The chosen number has to be greater than 0 and is set to 1 by default.

It is typed =RIGHT

LEFT Function

The LEFT function is used to retrieve a chosen amount of characters, counting from the left side of an Excel cell. The chosen number has to be greater than 0 and is set to 1 by default.

It is typed =LEFT

CONCLUSION

Excel is a useful tool for data analytics, especially for beginners. It helps users clean, organize, analyze, and visualize data efficiently. By mastering functions and features such as data validation, conditional formatting, and data cleaning, users can turn raw data into meaningful insights.

MY FIRST GITHUB PROJECT

Adrian Mageto — Sat, 22 Aug 2026 15:16:04 +0000

Introduction

In this article I will explain my practical experience of creating Git repository,connecting it to GitHub using SSH,commiting my files and pushing the project to a remote repository.

Step 1 :Creating My Local Project

The first step is to create a folder through file explorer which as the best option for me and name it (my-project)
Next step was to open GitBash and run the command

cd my- project
To open the file directory

Step 2 : Initializing Git

This is to tell Git that my my-project folder should become a Git repository.
To initialize Git I ran the command:

git init

Step 3:Repository Status Check

After initializing Git, I used :

git status

This command is very useful as it can be used throughout the process it tells what's happening inside my repository.
At some point people may encounter:

On branch main
Nothing to commit, working on a tree clean

It could seem like an error,however I learned that this means Git has checked my project and found no changes that need to be committed.

Another situation that I encountered is where Git told me that the older my project was already initialized .This taught me to use the command:

git status
To understand the current status of my project.

Step 4:Connecting GitHub Using SSH

The next thing I learnt was on how to generate SSH key which will be used to connect my computer securely to GitHub.
To generate an SSH key I ran the command:

ssh-keygen -t ed25519 -C "your_email@example.com"
Under the double quotes use the email used for your GitHub account.

Then press entre to save it on the default location.
You'll then be told to enter passphrase which is simply a password,create a simple one which you can memorize easily like 1234.Then entre it again when asked.

You then start the SSH agent
Run;
eval "$(ssh-agent -s)"

Then add your SSH keyy
Run:
ssh-add ~/ .ssh/id_ed2559
It will ask you to entre the passphrase you created.
Copy your public key y running the command:

cat ~ .ssh/id_ed25519 .pub

Add the entire line generated by coping it then go on GitHub-Settings-SSH and GPG keys-New SSH key,give it a title and paste the public key and save it.

Then test the connection ack in Git Bash through the command:
ssh -T git@ithub.com

Step 5:Creating a Repository on GitHub

After that create your repository,give it a title, add a small description about it ,choose its visibility to public finally press create repository.

Step 5:Adding Files

This means to prepare the files and add changes in the current directory.

To do this one runs the command:

git add .
I could then check the status again:

git status
The files would appear as changes ready to be committed.

Step 6:Create a Commit

This stage is like a saved checkpoint in my project.
To create a Commit run the command:

git commit -m "Initial commit"

The message "Initial commit" is just a commit statement which can be different to your preference.

Step 7:Connect the Local Repository to GitHub

I connected my local repository to GitHub
Firstly one should open GitHub go to where he repository you created is click on it then copy the SSH which is used as a location key.
Then using the command :

git remote add origin (paste the SSH key copied from GitHub)

Then to check whether the connection was established we use the command :

git remote -v
Then to ensure your current branch is called main use the command:

git branch -M main

Step 8:Push the Project to GitHub

To push the folder

I used:
git push -u origin main

The -u option sets the remote ranch as the upstream branch .This means that later I can usually use:

git push

Then go to your GitHub and click on your repository, you will find the folder my-project inside your online GitHub repository.

Conclusion

Creating my first GitHub project helped me move from simply having files on my computer to managing a project using a professional development workflow.

To understand the Git command projects I came up with my own mnemonic "I See All Commits Reach Big Places"

git init
git status 
git add .
git commit"commit statement"
git remote add origin <repository-url>
git branch -M main
git push -u origin main