DEV Community: David Samuel

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

David Samuel — Tue, 01 Sep 2026 05:41:31 +0000

Introduction

Excel is a spreadsheet program that enables crunching of numbers. The powerful software not allows one to organize data but also facilitates cleaning, formatting and analysis through application of a myriad of formulas and functions. Akin to other Microsoft software packages, Excel’s interface is relatively easy to navigate, even for a beginner. The interface majorly comprises a program frame, menu tabs, a ribbon, a formula bar, a workspace, sheet tabs, and a status bar as shown in the figure below:

The workspace is made up cells, where each cell denotes a small rectangular box formed when a vertical column intersects with a horizontal row. The cells serve as data storage units where typing and editing of numbers, texts, formulas, and dates can be done. Each cell gets a unique name derived from a combination of its column letter and row number. For instance, cell A1 denotes the small box formed where column A meets row 1. Notably, a cell can hold diverse data forms such as numeric values, text labels, Boolean values, or formulas. It is also possible to format the appearance of a cell and its contents by adjusting elements such as size, number formats, fonts, and borders. A single spreadsheet consisting of columns and rows is called a worksheet. Usually, worksheets are located at the bottom at the Excel window. The complete Excel file that one creates, saves, and shares is referred to as a workbook. Markedly, a workbook can hold a single or multiple worksheets.

Data Cleaning

It is common for data entered into an Excel spreadsheet to be inaccurate, duplicated, incomplete, or inconsistent. Such data is generally referred to as dirty data. Dirty data arises from factors such as human error and poor collection processes. To render the data reliable for business analysis, there is a need to find and fix errors, inconsistencies, and duplicates. This process is referred to as data cleaning.

Steps in Data Cleaning

Removal of Duplicates

It is common for duplicates to occur in an Excel worksheet. Such occurrences can lead to erroneous analysis and inaccurate findings. As such, removal of duplicates is a key step in data cleaning. Removing duplicates is simple and entails selecting the entire data range, navigating to the data tab, and clicking remove duplicates as shown in the figure below:

Fixing Typos

Typos can result from misspellings, inclusion or exclusion of unwanted characters, presence of extra spaces in text, and a mix-up of upper- and lower-case characters in texts. Misspellings are corrected using the “Find and Replace” function. Here, one locates misspelt texts in each column and replaces it with the correct one. In a dataset where the word "finance" has been misspelt as "finanace", for example, the error can be fixed by relacing the erroneously spelt word with the correct one as shown in the figure below:

On the other hand, presence of extra spaces in texts is corrected using the TRIM(text) function. The function works by removing all leading and trailing spaces therefore leaving just a single space between words. In the dataset shown in my example below, the text “Paul” had 2 leading spaces that were corrected using the TRIM(text) function.

In an event where upper and lower-case characters are mixed up in texts, the error can be rectified by using UPPER(text), LOWER(text), and PROPER(text) functions accordingly. Notably, the UPPER(text) function converts text to upper case while the LOWER(text) and PROPER(text) functions convert text to lower and proper cases respectively. The example below shows use of the PROPER(text) function to correct "jAMeS" and yield the proper noun "James".

Creating New Columns

When cleaning data, it may be necessary to create new columns crucial for analysing the data. This can be done by either combining texts from different cells or extracting portions of the texts entered in an Excel sheet. Notably, combining texts from different cells applies the CONCAT(text) function. On the other hand, extracting text from the left, right, and middle is done using the LEFT(text), RIGHT(text), or MID(text) functions respectively.
The example below shows how a country code can be extracted from a phone number by applying the LEFT(text) function. Here, the first 4 characters from the left are extracted.

Fixing Data Formats

Erroneous data formats in an Excel sheet can be fixed by highlighting the relevant column, navigating to the “Home” menu and then selecting the “Number” tab in the ribbon. From this section one can then select the correct data format or type that best suits their column. For example, currency figures entered as numbers can be changed to currency by selecting the necessary column, navigating to the “Numbers” tab, and selecting “Currency” from the drop-down menu. The same approach can be used to rectify any other erroneously classed data to the correct data type or format.

Handling Outliers and Missing Values

An outlier is a figure that lies an abnormal distance from other figures in a dataset. Such data points should be removed or adjusted accordingly, for example by replacing them with the median. Similarly, missing values can be handled by either filling blank cells or removing entire incomplete rows. Where filling is the most preferred approach, the missing values are filled using either the mean, mode, or median. Because a dataset may contain outliers which affect the mean significantly but have little to no effect on mode and median, the mode and the median are the most preferred figures for filling blanks.

Conclusion

Excel is an extremely powerful tool for data cleaning. Ordinarily, cleaning entails removing duplicates, fixing typos and data formats, creating new columns, and handling outliers and missing values. Data cleaning improves accuracy of the data and subsequently determines the quality and reliability of analysis.

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

David Samuel — Sun, 23 Aug 2026 19:29:13 +0000

Creating my first GitHub project entailed use of diverse resources namely Git, GitHub, and VS Code. As the first step to creating the project, it was critical to have a deep understanding of concepts such as Git, Git Bash, GitHub, and VS Code. Without the understanding, it would have been impossible to link Git to GitHub and publish the new project.

Definition of Terms

Git and GitHub can be confusing terms, especially for beginners. While GitHub is a relatively commonplace terminology for anyone that has some level of interaction with people in the field of tech, Git is rarely mentioned. At a personal level, I had encountered the term GitHub, but never had I come across Git. As such, differentiating the two and understanding both was a crucial step in building my first GitHub project. From the onset, I learned that Git is a software program that helps one track changes in files stored in their local machine. While the software is pre-installed in some operating systems, it has to be manually installed in Windows. Git is run using Git Bash, an interface for writing Git commands. On the other hand, GitHub is cloud-based website that stores Git projects and allows collaboration among developers. Unlike Git which is local, GitHub is remote and is used to publish code created on different code editors such as VS Code.

Creating a Local Folder

Creating a local folder using Git Bash is an alternative to the traditional approach of right clicking on the screen and creating a new folder. Doing so is a step-wise procedure entailing selecting the directory within which you want to create the new folder as well as creating the new folder and the sub-folders and files within it. For my project, I created the folder on my desktop.
To list all the directories on my pc, I ran the command below in my Git Bash terminal:
ls
With Desktop being one of my directories and the directory within which I intended to create my new folder, I ran the command below to change directory to Desktop and consequently manipulate it as desired:
cd Desktop
I then created my folder christened “My-First-GitHub-Project” within Desktop by running:
mkdir My-First-GitHub-Project
Subsequently, I created 3 folders (Data, Notebook, Scripts) within “My-First-Desktop-Project” folder. This phase entailed two steps, the first one being changing directory from Desktop to “My-First-GitHub Project” folder using the command below:
cd My-First-GitHub-Project
This step was followed by creating the aforementioned folders using the Git Bash commands below:
mkdir Data
mkdir Notebook
mkdir Scripts
I also created two files within “My_First_GitHub_Project” folder using the commands below:
touch README.md
touch Analysis.py

Linking and Pushing the Project to GitHub

Linking Git to GitHub

Prior to pushing the project to GitHub, it was necessary to link Git to GitHub using the steps outlined below:
Step 1: Configuring Git
The step entailed setting up my username and GitHub email using the commands below:
git config --global user.name "My Name"
git config --global user.email “my-name@gmail.com”
Step 2: Verifying Configuration
To verify my configuration, I ran the command below:
git config --global –list
Step 3: SSH Key Generation
To generate the SSH key, I ran the command below:
ssh-keygen -t ed25519 -C “my-name@gmail.com”
The above step was followed by adding and confirming a blank passphrase. Subsequently, a public and a private key pair was generated as shown below:

id_ed25519
id_ed25519.pub

Step 4: Copying Public Key
To copy the public key, I ran the clip command with the path to where the key was stored as shown below:
clip < /c/Users/MyPC/.ssh/id_ed25519.pub
Step 5: Adding the Key to GitHub
To add the key to my GitHub account, I first logged in, then navigated to profile, then settings, then SSH and GPG Keys where I selected “New SSH Key”.
Next, I pasted the key I had copied in the “key” section and gave a brief description in the “title” section.
Step 6: Testing if The Key Was Added Properly
I then tested if the key had been added properly by going to the Git Bash terminal and typing the command below:
ssh -T git@github.com
From this command, I confirmed that the SSH connection had been established and was working properly.

Pushing to GitHub

I used VS Code to push the project created on my local PC to GitHub using the steps outlined below:

Opened VS Code
Clicked on “File”
Clicked on “Open Folder”
Selected the “My-First-GitHub-Project” folder created on my desktop
Navigated to “Source Control” and selected “Initialize Repository”
Wrote a “Commit Message” and then selected “Commit and Sync”
Clicked “Publish Branch” and selected “Publish to GitHub as private repository” since I did not want the repository to be public
Logged in to GitHub using the “Open on GitHub” button to check my newly created repository.

Overall, the whole exercise of creating my first GitHub project not only helped me conceptualize how to push a project from a local folder to GitHub but also instilled a deeper comprehension of the difference between Git and GitHub, various Git Bash commands, and how to navigate between VS Code and GitHub.