DEV Community: Waweru

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

Waweru — Wed, 02 Sep 2026 19:21:32 +0000

Introduction

Microsoft Excel is a spreadsheet program used to organize, calculate, analyze, and visualize data in rows and columns. It is organized in worksheets that are contained in the workbook - the file.
Data analysis is the process of cleaning, organizing and examining raw data to find useful patterns the support business decisions.
Data is often received in it rawest form which is disorganized and in order to make business sense of it, the data must be cleaned and organized. So how do we clean raw data and make business sense out of it.

1. Filtering

Filtering is temporarily conceals rows that do not fit your rules. It displays a smaller, relevant subset of data while leaving the original row order untouched.

Using filtering to clean data

Auto fit the column width to see column data properly. Home tab Cell.
Select the row with all the headers and select filter. This will enable us to remove unwanted data fields our columns.
When cleaning data for non-unique input values we need columns to have only one instance of a text field. We do this by deleting all other text fields until we remain with unique ones. An example would be having HR and human resource both as input fields instead of one instance.

In this column we have only one instance of every department. In such columns we often use data validation to ensure that inputs are strictly of a specific type. Example Male or female, or a specific department.

Using filtering for analysis
In excel we can filter by certain types of data namely text, numbers and date. This means we can conceal all other data and get only the data that fits our criteria

Above is text filter. We are filtering last names column for names beginning with 'mbo'

We have various options for number filters. Example we can filter for salaries that are greater than 50,000 using the option provided.

This is date filter. We use it to get specific time lines and time ranges.

2. Sorting

Sorting changes the physical position and order of rows (such as A to Z, Z to A, smallest to largest, or by cell color). All data remains visible on the sheet unlike filtering that hides data giving only what was filtered. It is found in home -> editing

Multilevel Sorting

Here we are sorting our data by the columns department, gender and age. This rearranges the entire worksheet to fit this parameters.

3. Conditional Formatting.

Conditional formatting in Excel is a tool that changes the look of cells automatically based on their content. It lets you use **colors, icons, or data bars to spot trends, high values, or errors and duplicates **without looking through rows by hand

In this instance we are using conditional formatting on the employeeID column to highlight duplicates using a specific color, since the ID values are supposed to be unique any duplicate should be deleted
Conditional formatting can also be used on dates and time, getting values that are greater or less than, to highlight text that contains a specific text, getting top 10%. Explore the conditional formatting options

4. Data Validation

Data validation is a feature that restricts what users can type into a cell or a range of cells to keep the data clean and accurate. Found in the data tab --> data tools group

In the above image we have validate the department column to only accept a list of departments available in the organization

In this example we see that to input values into the department column we have a drop down list to select from

The data validation criteria can also cater for dates, time whole numbers, lists and text length. This ensures that the data being input in the cell already meets the criteria for that column or cell. The process ensures incoming data is clean.

5. Functions

Aggregation Function
Aggregate function calculate a single summary value such as a sum, average, or count from a list of numbers
Count - The function is used to count non-numeric cells.
CountA - counts non-blank cells in the column
CountBlank - Counts blank cells in a column

Conditional Aggregations
A conditional aggregate function in Excel summarizes a set of data (like adding numbers or counting rows) only for the cells that meet a specific rule or condition.
Examples are COUNTIFS, SUMIFS, COUNTIF.

In the above image we use SUMIFS to calculate the sum of salary(range) if the gender is female(range and text) and the department is Finance (range and text). Using the syntax provided.

Statistical Functions
Statistical Functions to analyze, summarize, and interpret data. They include mean, median and mode.
Note that the median and the average should not have a big difference if the data is consistent and has no outliers

Here we find the median for out salary column

Date and time functions
Excel date and time functions work like a hidden digital calendar and clock that let you track, display, and calculate days and hours with ease.
=YEAR(A1) =MONTH(A1) =DAY(A1) This are some of the date and time functions

Text Functions
This are functions that format text for a certain expected outcome. Example: CONCAT(adding two strings together), TRIM, PROPER(first letter capitalization)

In this example we use the text functions CONCAT and LOWER to join the first and last name and form an email field from that text

Conclusion.
This article explains how you can clean data and analyze data using excel using tools like data validation, functions, filters and sorts. It helps capture the power of excel in your data analysis journey.

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

Waweru — Sat, 22 Aug 2026 09:52:30 +0000

Introduction

Git is a version control system that tracks changes in code. When writing code we need to save every significant change or feature that does a certain function as a commit that can be reverted to if we ever need that instance of the code. This helps programmers to traverse code since nothing is ever lost in the product life cycle of the project. It also helps in debugging since we have a copy of the previous working state. So how do we start out with git you ask.

Install git

Install git for your specific operating system. After installing git you will note that the file comes with git bash which is a command line terminal that is used to run commands or instruct the version control git.
To ensure that git has been installed open git bash and run the command git --version which will return the version of git installed

Download Visual Studio Code

Install VS code a text editor for writing code.

Create a github account

GitHub is a cloud-based platform used by developers to store, track, and collaborate on software code. It operates as a hosting service for git, an open-source version control system that tracks changes made to files.

Head over to github and create and account

Creating a directory tracked by git

On git bash run mkdir <filename>. This will create a folder ie the project that will contain your files of code.
Enter the folder through cd <filename> and create a file README.md file which explains what this project does touch README.md. Run the command ls to list files in the directory
Open the vs code from terminal by running code . and edit the README.md file. Now the file is ready to be tracked by git on github

Staging, committing and pushing

We now need to initialize the directory making sure that git is now tracking the file. Running the commands ls-la will confirm that git has been initialized in the repository. There are two ways of doing this either via the text editor VS code or the terminal. Let us go through both.

1. VS code

Make sure your VS code is connected to your github account.

Click the source control icon (third from top) and Initialize Repository. This will introduce git to track the file
Hover over the file and click the + button to add the file. This changes the file from U (untracked) to A (added)
Write the commit message in the input box above describing what you are committing eg add README.md file.
On the commit button press the drop down arrow **for more options and **press commit and sync. This will now stage your repository to be pushed to github.
Click on publish branch and set the repository to either public or private.
Go to your github and refresh to see your newly pushed repository that is now tracked by git.

2. Using the terminal

Open your terminal ie. gitbash and go to your folder.
Initialize Git using git init.
Add your files using git add . to track them.
Commit the files using git commit -m "first commit". This should describe what we are committing and will to save the change locally.
Link your remote repository using git remote add origin <your-repo-url>.Preferably use the ssh link option
Push the files using git push -u origin main to send your code to GitHub and publish the repository as the main branch.

With that now you know how to set up a project and keep track of your changes using github.