DEV Community: Denis Ndiritu

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

Denis Ndiritu — Tue, 01 Sep 2026 03:31:34 +0000

In today's world, decisions are not based on intuition or guesses. They are not based on what one thinks is the right thing. If you own a business, you would like to make a decision based on certain trends. If you are a teacher, you may want to see the progress of a student over a certain period. This and more requires one key ingredient; data. This data is collected using various methods, over a long period of time, and you are able to analyze it to get meaningful information from it. As the name says, the final output, informs you on a decision you should make, based on the trends that your data has shown.

The data, however, may not be clean. The data can contain duplicates, extra spaces on certain values, blank rows and columns, wrong data types on values, inconsistent cases, and many more. Getting insights from such data may not give you accurate insights. This is where data cleaning comes in handy.

You would get provided with a piece of data either in csv or xlsx format, and use Microsoft Excel to start the cleaning process. Let's dig deep into this.

Opening An Excel File

First things first, we would want to open our data in Microsoft Excel. We also need to make sure that we have a copy of our raw data somewhere safe so that we can come to it for any references. To safely get the data from the excel file open Microsoft Excel and click Blank workbook. This will open a new workbook with no information in it. If you had already opened it, then simply navigate to File and click Blank workbook to create a new workbook.

Getting Data From our Raw File

We now have our blank workbook. We now need to get our data into the workbook so that we can get started with the cleaning process. To do this, we will navigate to the Data tab on our top bar menu. We shall then click the Get Data button that has a cylinder+table icon. Once clicked, we get a few options. Microsoft provided a ton of options which you can choose to get your data from, depending on your source. You can get data from files e.g Excel Workbook, Text/CSV, XML, JSON, PDF, Folder, or SharePoint Folder. You can even get data from Database, Azure etc.

Once you identify you source e.g. CSV, simply select it. You will then get a window to navigate to where your raw data is located. Navigate to that location and select your data. Click the Open button or double click your data source. This will then open a preview of the data inside the selected file, showing the encoding format of the file, the delimiter, and the data inside the file. Click the Load button at the bottom of your preview window.

This will now open your raw data inside a table. Note that this is now single worksheet that you have of your raw data. In our worksheet, we may want to duplicate our data so that we can separate it from the raw worksheet, and now what we shall clean. If you did the above steps properly, you would notice a tab at the right part of your window saying Queries & Connections. What happened is that the Get Data created a live connection to the external data source using an underlying query. When we click on this query, we are taken to the Power Query Editor where we can now make certain changes. These changes are structured as steps which are recorded and can be undone. The changes do not touch your worksheet until you Close & Load.

Why Should One Use Power Query Over Normal Excel?

The Power Query Editor is preferred over traditional Excel the moment that your data cleaning would need automation, has grown in terms of volume, or needs to have steps reproducible. This enhances visibility of steps done on your data, which can also be repeated over and over again.

Identifying Data Types in Excel

An excel file can have as low as one column and as many columns as possible. Each column holds information, which has been stored in a certain data type. It is important that you ensure that each column has consistent data type depending on what the column is.

A data type is a classification that specifies which type of value a field or variable can hold. Excel recognizes the following data types in cells: Text, Number, Dates, Times, Logical (Boolean) values, and Errors.

When cleaning your data, it is therefore important to ensure that each column has a consistent data type across its cells. Cleaning up data is very simple. Simply select the column you would like to specify its data type. In the Home tab, in the Number menu, specify the data type. Repeat the steps for all the other columns.

Similar Values in a Column, Duplicates or Not?

When cleaning our data, we mentioned that we may have values that repeat themselves. At times, this might be erroneous, while some may have repetitive values, but values in the subsequent columns be different, which might change how we handle the duplicate values.

For example, if we were checking for duplicates on a column such as a product name, and the product had batch numbers, it would not be a surprise when we have repeating product names, but different batch numbers. This would suggest that we have variations of our product, due to the batch number. In such a case, we would not remove the duplicate product. However, if the batch number was the same across the same product, this would highly suggest that some of the records are duplicates, which would necessitate us to remove one record, and remain with a single non-duplicate record.

Formatting Data

We had already started formatting our data but had not gone through other methods of formatting our data. In the Power Query Editor, one can format columns as they would wish to. Some options provided are:

lowercase -> Normalizes the selected column to have all its values as lowercase.
UPPERCASE -> Normalizes the selected column to have its values as upper case.
Capitalize Each Word -> Capitalizes the first letter of each word in each cell in the selected column.
Trim -> Removes leading and trailing whitespaces in each cell of the selected column.
Clean -> This will remove all non-printable characters in each cell of the selected column.
Add Prefix -> This will add a text that you will specify and will be appended before each value in the cells of the column you have selected.
Add Suffix -> This will add a text that you will specify and will be appended after each value in the cells of the column you have selected.

These are some of the ways you can manipulate your text values in Excel to have consistency.

Sorting and Filtering

Sorting

When cleaning your data, you may want it to appear in a certain order. You may want the first column to be sorted in ascending order. You may also want to combine sorting so that your data is arranged based on more than one column. This is where sorting comes in. Sorting does not add or remove any data. It just changes how they order themselves to allow one to see patterns, trends or even outliers. Some of the methods of sorting include:

Alphabetical (A to Z or Z to A -> This sorting method is used for text columns e.g. product names, or employee names.
Numerical (Smallest to Largest or Largest to Smallest) -> This is used for financial or quantity data and can be used for cases such as getting the lowest prices, finding the product with the highest quantity etc.
Chronological (Oldest to Newest or Newest to Oldest -> This can used to track dates e.g. oldest student in a class, the nearest deadline etc.
Multi-Level Sorting -> This kind of sorting allows one to sort data by more than one column at a time.

Filtering

You may want to see data based on specific criteria. This is made possible by Microsoft Excel using the magic of Filters. Filters allows you to focus on what you need, while temporarily hiding the rows that don't meet the criteria specified. You can always get the full data back by clearing your filter. Some of the filter methods include:

Checkbox Filtering -> The filter allows you to just check or uncheck boxes. Rows with the unchecked values will be filtered out, remaining with what has been checked.
Text Filtering -> The text filters allow one to search for values based on criteria such as Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain. This will also provide you with a custom filter to create your own.
Number Filtering -> This allows you to filter numbers according to criteria such as Equals, Does Not Equal, Greater Than, Greater Than Or Equal To, Less Than, Less Than Or Equal To, Between, Top 10, Above Average, and Below Average. You can also create custom filters just like with the text filters.

Functions

We have cleaned our data, we have sorted it, and we have also filtered. We would want to have a bit of insights now. To do this, we need to use the functions provided by Microsoft Excel.

Instead of rearranging, we will now use functions to analyze our data. In this article, we shall talk about statistical functions and date functions offered in excel to help in analyzing data.

Statistical Functions

Statistical functions allow you to get a single value (metric) from a range of data points. Examples include:

MIN and MAX -> The two functions help you get the lowest and maximum values in a range respectively. You can get, for example, the highest salary given to your employees, or the lowest age among your staff.
AVERAGE -> This function will add up all selected values and divide by the total count.
MEDIAN -> Median is used to identify the exact middle number in a sorted list.
MODE -> The Mode function allows you to find the value that appears the most in your dataset. ### Date Functions Date functions are important when used in a dataset, as they can help you extract and analyze your data by time periods. For example:
YEAR -> The YEAR function extracts the four-digit year from a full date.
MONTH -> The MONTH function pulls the month from a full date, returning a value from 1 (January) to 12 (December).
DAY -> The DAY function pulls the day from a full date, returning a value ranging from 0 to 31.

Closing

This is just a scratch to the powerful world of Excel, and yet we can do much with the little we have covered. It is important that one understands all skills, as they build up as one continues exploring more and more functionalities of
Microsoft Excel.

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

Denis Ndiritu — Sun, 23 Aug 2026 15:13:55 +0000

Imagine working on a project for several weeks. You make hundreds of changes across dozens of files. Then your computer crashes. Which version of your project was working yesterday? What did you change? Which changes did your colleague make? And how do you combine everyone's work without overwriting each other's changes?

This is where Git comes in.

What is Git?

Git is an open-source system that enables users to manage their projects, from a single file to large projects. Git is basically a version control system. It provides the necessary resource to help one manage files in the local machine, track the files that have been changed, attach a pointer which marks files which were changed, who did it, and when, and to make it redundant, these files can be pushed into a remote shareable project called repository in Github.

Git also has something very key that is called branches. A branch is basically an isolated area of your project where you can work on a copy of the main project without making changes to the main work. Once you are done working on your branch, Git makes it possible to merge all the changes to the main branch.

For this article, I will cover these concepts, guiding one on the step by step process of how to do all this. This article assumes that one has Git installed and configured, and has a Github account. We shall also assume that one has a text editor installed, or is conversant with commands in the terminal. We shall cover these concepts in another article.

Creating a Project On Your Machine

To start, we need to create a folder where we shall be working on our project. On your machine, you can use the good old right click and select New Folder, or, if in terminal, use the command mkdir as follows:
mkdir new_project
This creates a folder called new_project

We shall then open our folder in the terminal, so that we can do the following:

Initialize Git
Create a file
Stage it
Commit it
Link local repository to remote repository
Push to Github account

Initializing Git

At this point, we have our folder. So to open it in terminal, we can open our favorite terminal, or simply open our folder in our text editor. Once this is done, we can now initialize Git using this command:
git init
This command simply initializes a local Git repository in your project folder. You can confirm this by confirming that we have a .git folder inside our main folder. During initialization, you will note that you will be in a default branch called main or master. To confirm this, simply run the command:
git branch

Creating a File

In our terminal, we can be able to create a file using the command:
touch <filename>
This will create a file inside our main folder. For this case let's call our file main.py At this point, you can run certain commands like:
git status
which allows us to see files that we have not started to track, files we have modified, files we have staged, files we have modified after staging, and so on. Files that are not tracked means that they will not be pushed to Github once we get to that.

We can now write a simple sentence on our terminal using a command called echo as follows:
echo "print('This is a new file')" > main.py
The echo command prints text, but when used together with the greater than sign (>) and the file name, will write the text into the file specified. At this point, we are on our working directory. Git does not know what it needs to stage. So how do we stage files?

Staging a File(s)

Staging a file basically tells Git that we want to commit our changes that we have made so far. Moreover, it will also stage any new, modified, or deleted paths too. We can do this by basically staging everything or staging a single file, or staging specific files.
To do so, we can do the following

git add . // Stages everything that is yet to be tracked.
git add <filename> // Stages a single file.
git add <filename_1> <filename_2> ... // Stages specific files.

At this point, we are in the staging area. We know what we want to commit/update in our remote repository. In the next stage, we will commit the staged files.

Committing a File(s)

We now commit our changes so Git records a snapshot of what we have done. This is important, as git uses these commits to help the user go back to a certain point in your branch.
To create a commit, We use this command:
git commit -m "Add main Python file"
The commit message should be in double quotes. Once this is done, we need to to point our local folder to our remote repository. If not created, you will need to create a repository.

Note: At this stage of our workflow, we are now in the local repository. We have a full committed history of what we have done, but it is local. We are yet to update on our remote repository.

Link Our Local Repository to Remote Repository

We will first create a repository on Github. To do this, simply navigate to your Github account on your browser and click New Repository button on your Repositories page. You will then provide a name for your repository, provide a description, and you can later have it as Private or Public of other users to access it. Once that is done, you can click the Create repository button. For this case, our repository is called health_records_analysis

This has now created our repository. This repository contains a link which we can share to other users, and this is the link that we need to link our local folder with the newly created repository. To do this, simply access it once you scroll down in the page you are in the simply copy the http link. This is what we shall use. The link usually looks like this http://github.com//

We will then go back to the terminal we were in and enter the following command
git remote add origin <repository_link>

Great! We can now conduct the final step, pushing

Pushing Our Changes To Github

To push to Github, we only need a single command:
git push -u origin main

git push will send our local commit to the remote repository
origin basically is the name of our remote repository
main is the branch we are pushing to
-u will establish origin/main as the remote branch.

You can confirm this by going back to your Github account and refresh. You will see the file we just pushed, and the commit message. As you may have guessed, this is the remote repository. This is now where we have pushed our changes, and all this can be shared to relevant users.

Closing

Learning Git is important as a person working with any kind of projects that require proper tracking, can be collaborative, and even makes your work neat. It is a skill that is important, and one that I, personally has had fun learning, and look forward to sharing my progress.