DEV Community: Makori Nyachaki

Building a Jumia Product Performance Dashboard in Excel.

Makori Nyachaki — Sat, 05 Sep 2026 12:31:13 +0000

Introduction

Building a professional dashboard is rarely a simple task; it requires careful planning and a series of strategic decisions. A clean, well-structured build ensures that complex business contexts are easy to understand, providing stakeholders with an immediate overview of the problem at hand, key findings, and actionable solutions. Clarity is the hallmark of an effective dashboard.

In this post, we will analyze Jumia's product performance to translate raw data into meaningful insights that drive business growth and improve service delivery. By examining customer engagement, pricing models, and promotional effectiveness, we can better understand the specific factors influencing product success.

Business Questions

Are larger discounts correlated with a higher volume of reviews?
Do highly-rated products naturally attract stronger customer engagement?
What is the relationship between product pricing and customer ratings?
Which products represent the top performers based on ratings and review volume?
Which products require a pivot in their current pricing or marketing strategy?

Data Methodology

Data Quality Audit

After receiving the dataset, find any errors that may exist within the dataset. We checked whether the dataset had headers, checked for formatting, duplicates, missing values, data types, and any inconsistencies. We used filters and conditional formatting to find out where these errors were. This was very helpful in identifying the errors during the data cleaning process.

Note: Copy your dataset and retain the original. Work on the copied dataset.

We found the following errors and inconsistencies in the dataset:

Mispelt headings Ratingd instead of Rating
Mismatching Header cases old prices and Current prices
Trailing white spaces and extra spaces in column 1
Duplicates: we found 3 duplicates with an exact match in all fields.
Negative review values in the reviews column
One price range in both the Current Price and Old Prices columns instead of a price value.
Improper data types in the Rating and Prices columns.
Missing values in the Rating and Review columns.

So, after copying our dataset into the new sheet to retain the original dataset, create a data table. Select all the data, go to Insert > Table from the Tables group section.

Fig 1: An image of Raw Data

Data Cleaning

Now that we have understood the structure and where the underlying issues are in our data, it is time to get rid of the messy details from our dataset in readiness for analysis.

The following are the steps and decisions that we made through the cleaning process.

For the headings, we had to change to correct spellings for Rating from Ratingd
For the price columns, we had to convert headers using the PROPER() function to convert them to proper case. That is, the first letter of each word is capitalized.
=PROPER(B1) =PROPER(C1)
B1 and C1: is the address to the Current Price and Old Price headers in the sheet. The function returns the new text formatted to proper case.
Then we used copy and paste special(Paste as Values), where we copied and pasted the new text to the header, replacing the old one.
Removed all trailing whitespace and extra spaces using the TRIM() function.

Steps

Insert a new column to the right of the Product column
In Cell B2, type the function below:

=TRIM(A2)
Press Enter to return the new text value
The column is auto-filled.

_Copy and Paste Special _

Select all the values in the B column, i.e., from B2:B116
Copy the values using the Shortcut CTRL + C
Then, select cell A2, and right- click on it
Then from the dialog box choose, Paste as Values instead of Paste.

Use the CLEAN() function to also remove non-printable characters from the Product column.

Steps

Clear the column we used for trimming
In B2 enter the following function =CLEAN(A2)
Copy the values in B2:B116, paste special in in cell A2.

Duplicates

Three duplicates were found; they were an exact match in every field. Remove them to avoid redundancy and incorrect results of analysis due to redundancy.

Steps

Select all the data
On Data Tab click on Remove Duplicates in the Data Tools group section
Select all the fields, and then click OK

A confirmation pop-up message will show you the number of duplicates removed.

Negative Reviews

For the negative values in the Reviews column, convert them to absolute values using the ABS() function.

Steps

Insert a new column to the right of the review column
On the new column, F, enter the following function in cell F2: =ABS(E2)

E2 is the address of the first review negative value

Press Enter
Copy and Paste Special the values to replace those in the review column.

Price Ranges

Used the midpoint of the price range to get the value for the particular products. We found the midpoint using the formula:

`Price = (Lower Range Limit + Upper Range Limit) / 2`

Data Types

Current Price and Old Price Columns

Used Find and Replace to replace KSh with Blanks
Converted the remaining values to Number type, then to Currency type to 2 decimal places with the symbol KSh, and used the , 1000 separator

Rating

Converted Rating to Number type, formatted to one decimal place.

Review

Converted Review column to Number type, a whole number.

Steps to Change to Number Type

Select the Column with the data
Go to the Home tab; in the Number group section, from the drop-down, select More Number formats
On the left side of the pop-up window, select Number or Currency and specify the other options.
Then click OK to apply the changes.

Missing Values

For missing values and blanks, never assume anything. Leave them blank as they are. Note that a Blank does not mean zero.

Exclude blanks from metrics that require the missing field.

Add a data status field (for example, Complete, Missing Rating) if it can help users understand better.

Enriching Fields and Excel Formulas

We added the following columns to our cleaned data.

1. Discount Amount

Use the following formula to get the values for the column:

=@[Old Price]-@[Current Price]

Optionally, compare the advertised discount with the calculated discount:

=IFERROR(([@[Old Price]]-[@[Current Price]])/[@[Old Price]],"")

Flag any difference instead of overwriting the advertised value.

2. Rating Category

Rating values are classified as follows:

Poor < 3

Average Between 3 and 4

Excellent > 4.5

=IF([@Discount]="", "Missing", IF([@Discount]<20%, "Low Discount", IF([@Discount]<=40%, "Medium Discount", "High Discount"))).

4.1-4.5 are not classified; if the original boundaries must be followed literally, label 4.1-4.5 as Unclassified.

3. Discount Category

Classify your Discount into the following categories:

Low Discount < 20%

Medium Discount 20%-40%

High Discount > 40%

=IF([@Discount]="", "Missing", IF([@Discount]<20%, "Low Discount", IF([@Discount]<=40%, "Medium Discount", "High Discount")))

4. Price Category

On the Analysis worksheet, define thresholds rather than arbitrary values. A preferred approach uses the first and third quartiles

=QUARTILE.INC(tblProducts[Current Price],1) =QUARTILE.INC(tblProducts[Current Price],3)
Name those sales as Price_Q1 and Price_Q3, respectively.
Use the following classifications

=IF([@[Current Price]]="", "Missing", IF([@[Current Price]]<=Price_Q1, "Low Price", IF([@[Current Price]]<=Price_Q3, "Medium Price", "High Price")))

Remember to record the final KSH thresholds in the Data Dictionary for ease of interpretation by other users.

5. Engagement and Performance Flag

For strong 'Customer Engagement,' define a measurable rule such as review count at or above the 75th Percentile. You may also create flags for:
high discount and low rating;
high discount and low engagement;
many reviews and average rating; and
strong engagement and excellent rating
State the exact thresholds.

Note: Do not select the products subjectively.

Pivot Tables and Analysis

Calculate:

Metric	Example Excel formula
Total products	`=ROWS(tblProducts[Product])`
Average current price	`=AVERAGE(tblProducts[Current Price])`
Average old price	`=AVERAGE(tblProducts[Old Price])`
Average discount	`=AVERAGE(tblProducts[Discount])`
Average rating	`=AVERAGE(tblProducts[Rating])`
Total reviews	`=SUM(tblProducts[Review])`
Most expensive price	`=MAX(tblProducts[Current Price])`
Least expensive price	`=MIN(tblProducts[Current Price])`

Then find and return the product names with the least and most expensive prices:

=XLOOKUP(MAX(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])
=XLOOKUP(MIN(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])

Report all ties using FILTER,, not only the first match.

Scatter Plots

To establish relationships between variables, create scatter plots, using one product per point:

Discount (x) versus reviews (y);
Rating (x) versus reviews (y); and
Current price (x) versus rating (y).

Add a linear trendline, display the equation and R-squared value, and calculate the Pearson Correlation:

=CORREL(tblProducts[Discount],tblProducts[Review])
=CORREL(tblProducts[Rating],tblProducts[Review])
=CORREL(tblProducts[Current Price],tblProducts[Rating])

A PivotTable or Filter helper range containing only complete pairs may be required because blanks can cause errors in some versions of Excel.
Interpret direction carefully.
Note that correlation does not establish causation.

Ranked Tables

Create ranked tables for:

top 5 and bottom 5 products by rating;
top 10 products by discount;
top 10 products by reviews;
top 10 products by rating;
products with high discounts but low ratings;
products with high discounts but low engagement; and
products with many reviews but average ratings.

Use a PivotTable(Rows: Product; Values: relevant measure; sort in ascending/descending order, and apply Value Filters > Top 10.

Use review count as a tiebreaker for rating ranks and rating as a tiebreaker for review ranks. Exclude ratings from rating lists and show how you handle ties.

Seller Performance Questions

Translate ranked tables into evidence-based answers:

Strong Demand

Which products have review counts above the engagement threshold?

Pricing/Marketing Attention

Which products combine weak engagement or weak ratings with high price/high discount?

Many Reviews and Average Experience

Which products have high engagement but fall in the Average rating category?

Promotion Inefficiency

Which high-discount products remain below the engagement threshold?

Fig 2: Cleaned Data

Pivot Tables

These are the suggested PivotTables:

PivotTable	Rows	Values	Recommended visual
Rating mix	Rating Category	Count of Product	Doughnut or column chart
Discount mix	Discount Category	Count of Product	Column chart
Price vs rating	Price Category	Average of Rating	Column chart
Engagement by discount	Discount Category	Average/Sum of Review	Column chart
Top products by rating	Product	Average of Rating	Horizontal bar chart
Top products by reviews	Product	Sum of Review	Horizontal bar chart
Top products by discount	Product	Average of Discount	Horizontal bar chart

And use Insert > Slicer for Rating Category, Discount Category, and Price Category. Connect each slicer to every compatible PivotTable using Report Connections (or Pivot Table Connections).
Remember to test each slicer independently.

Fig 3: Pivot Tables and Pivot Charts

Dashboard Design and Slicer Connections

Here is the structure of the Jumia Product Performance dashboard:

+------------------------------------------------------------------+
| Title, refresh date, and slicers                                 |
+------------------------------------------------------------------+
| Total Products | Avg Price | Avg Discount | Avg Rating | Reviews |
+------------------------------------------------------------------+
| Top 10 by Rating | Top 10 by Reviews | Top 10 by Discount        |
+------------------------------------------------------------------+
| Discount vs Reviews | Rating vs Reviews | Price vs Rating         |
+------------------------------------------------------------------+
| Rating Mix | Discount Mix | Key Insights / Recommendations       |
+------------------------------------------------------------------+

Dashboard Standards

Ensure a clean and tidy dashboard is created. Use consistent color, formatting, font, and relevant symbols for the different columns.

Fig 4: Image showing Jumia Product Performance Dashboard

Key Findings

High discounts do not mean a high number of reviews or better engagement. From the analysis, there is no relationship at all between discount and reviews. The correlation coefficient between discount and reviews is -0.14.
There is a weak relationship that establishes no pattern between rating and reviews. This indicates that more reviews do not translate to higher ratings in the data; hence, no causation. The correlation coefficient stands at 0.06.
The analysis establishes a weak positive correlation between prices and ratings of 0.11. Prices are not influenced by the rating of the product.

Business Recommendations

Investigate quality issues on highly discounted products but have low ratings.
Find out why highly rated products have higher prices than the average and lower-rated products. Does this influence come from sales made?
Combine promotions of products with high ratings and higher prices to increase engagement and check if they can translate to sales.

Limitations and Lessons

We've learnt valuable lessons throughout the project. We were able to use and apply functions, build PivotTables and Pivot Charts, build a clean and interactive dashboard, access price, discount, rating, and engagement relationships, clean data for real data analysis, and communicate findings, limitations, and business recommendations. This will go along way in helping sellers to make informed business decisions for their businesses.

Conclusion

None of the performance metrics- discount, rating, and reviews translate to better engagement. They do not influence either metric or engagement.

Links to GitHub

To review and see the completed project, visit Jumia Product Performance Dashboard on GitHub.

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

Makori Nyachaki — Sat, 29 Aug 2026 22:12:42 +0000

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

Excel

Introduction

Excel is one of the most foundational tools critical to Data Analysts. It is useful for preparing data for analysis. Excel can be used from the point of data collection to creating dashboards. It is the foundational tool used by most Data Analysts in cleaning, reformatting, and structuring the dataset ready for analysis. Excel provides unique technical tools that support collecting, cleaning, analyzing, visualizing, and reporting. Removing duplicates, filtering and sorting, validating data, aggregating, and performing some statistical operations are some of the major tools that Microsoft Excel supports and are useful during data analytics.

Excel provides a ton of benefits to many organizations and businesses. It can serve as a database for storing and retrieving business data, i.e., sales data; it can serve as an electronic ledger, a data analytics tool, and many more functions.

Understanding Excel

Before diving into data analytics using Excel, we first need to understand Excel and all its functional components that are core to data analysis. This will give us an overview of the Excel interface and basic functions for the different components.

Interface

Ribbon - Contains all the command tabs you will work with. The tabs are: File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help, and many others. These tabs have more groups from which we can access and apply different commands to our dataset.
Toolbar - At the top right corner of the workbook. Used to hold and display the most common commands we use in Excel, like "Save", "Undo", "Redo", or/and any other command you may wish to add to the toolbar.
Scroll Bar(Horizontal and Vertical) - Scrollbars at the end of the worksheet. The vertical scrollbar is used to scroll through your worksheet up and down, and vice versa. The horizontal one helps you scroll through his/her worksheet from left to right and back.
Worksheet - Where you work from. It contains cells where you do your data entry.
Columns - Named alphabetically and move vertically in the worksheet.
Rows - Named numerically and moves across/horizontals in the worksheet
Cell - An intersection between a row and a column. Where we enter our data.
Formula Bar - Where you enter and edit your formulas in Excel.
Name Bar/ Address Bar - Displays the address of the active cell.

Workbook - One or more worksheets existing in the same Excel file.

Home Tab

Most of our formatting commands will be done from this tab. It is divided into around 8 groups:
- Clipboard: From here you can access the Copy, Cut, Paste, and Format Painter commands.
- Font: From here you can access text formatting commands. They include: Bolding, changing font face, font size, borders, highlighting, font color, italicizing, underlining, and many more commands.
- Alignment: You can access text alignment(Vertical and horizontal) commands, orientation, and indentation commands.
- Number: Where you can access different data type formatting features. i.e., date, currency, number, and many more.
- Styles: Conditional Formatting, Formatting as Table, and applying different cell styles.
- Cells: You can insert, delete, and format cells from this group.
- Editing: AutoSum, find and replace, filter, and sort command options are accessible from this group.
- Add-ins: Where you can access your Excel add-ins.

Insert Tab

A very important tab too. We can access the following groups essential for data analytics:
- Tables: We can work with Pivot Tables from this group
- Charts: We can create and insert charts from this group during our visualization and reporting.
- Sparklines: Insert sparklines for monitoring trends in our analytics.
- Filter: You can insert slicers and timelines, which are used for filtering our data and output in the dashboard mostly.

Formulas Tab

This tab is very essential and critical to data aggregation and statistical manipulation. The tab contains formulas and functions that are used to carry out mathematical and statistical manipulation on all numeric and textual data. It contains the following sections:
- Function Library: Contains Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trigonometric, and many more functions used while working with our dataset.
- Formula auditing: Contains tools that are used to understand the logic and structure of complex formulas(Dependents and Precedents).

Data

It contains the following groups, which serve an important role in preparing our data for analysis:
- Get & Transform: Used to import data from different sources, i.e., web and CSV files. Also, it is useful in doing transformation of datasets into Excel-compatible datasets.
- Sort and Filter: Used for sorting and filtering our dataset. This is important when you want to understand your data ranges or find anomalies that exist in your dataset for correction and further investigation.
- Data Tools: From here, we can get data validation commands, remove duplicates, convert text into columns, and many other commands that help maintain data consistency, reliability, and integrity during collection and analysis.
- Forecast: Used to do what-if analysis and forecast what will happen in the future to help with reporting and in making informed decisions.

Data Analysis

Introduction

Data analysis is the process of investigating a dataset to uncover patterns, trends, and insights for informed decision-making and to drive growth and success in business and organizations. Through data analytics, businesses and organizations can solve problems, improve operations, and optimize stakeholder decisions. Therefore, data analysis involves transforming raw opinions or data into meaningful facts or information.

Data analysis is crucial across fields like marketing, sales, health, finance, sports, and retail industries. It helps industry owners to be decisive and informed for a better future that fosters growth.

Types of Data Analytics

These are the types of data analytics. They all serve to achieve a common goal.

Descriptive - Used to answer the question: what happened? It investigates and summarizes historical uncover trends and patterns. Involves data aggregation and visualization.
Prescriptive - Used to answer the question: what is likely to happen next? It uses models to foretell what the future looks like based on past events. Involves machine learning and linear regression.
Prescriptive Analytics - Answers the question: What should we do? It recommends actions by combining data analysis with optimization techniques. Involves algorithm optimization.
Diagnostic - Answers the question: Why did it happen? Uses statistical methods to identify the cause. Involves correlation analysis and hypothesis testing.

Steps in Data Analysis

There are several steps involved in data analysis. The sequential steps are important if you want to begin collecting data by yourself.

We will use a publicly available dataset to illustrate the steps involved in data preparation before the actual analysis. Some of the steps may require actual data collection; hence, our dataset will not be used in those steps but rather in the preceding steps.

We are going to use the Cafe Sales - Dirty Data for Cleaning Training Dataset, available for download from Kaggle.

Note: Before you start the process, ensure that you keep a copy of your data.

Data Preparation

This is the process of collecting, cleaning, transforming, and analyzing the collected data. It readies

Data preparation ensures its accuracy, reliability, consistency, and regulatory compliance. These increase the level of trust and collaboration.

Data Collection

Data collection involves gathering raw opinions and facts right from the source.

There are various methods involved in data collection, which include but are not limited to: Conducting interviews, surveys, API integrations, IoT devices, web scraping, observation, or getting it from third-party sources.

The data is then loaded into tools like spreadsheets for further activities like cleaning and analysis.

Data Quality

Before the cleaning process, data is assessed to determine whether it meets the data standards that can help us avoid costly mistakes such as misleading results and flawed decisions.

Ensuring data is accurate, timely, complete, consistent, relevant, and of integrity is the key objective in this step.

Data Cleaning

Data cleaning ensures that datasets are complete, accurate, and consistent before analysis. This is achieved by getting rid of errors, fixing broken data, and addressing all issues that the dataset might have.

Data cleaning helps us to handle missing values, duplicates, outliers, inconsistent formatting, and irrelevant data.

From this point forward, we will be using our dataset to demonstrate how we can handle these issues we have listed. We are cleaning our data using Microsoft Excel. Open your CSV dataset using Excel.

Open the Dataset

- Navigate to the project folder `excel-data-cleaning/`
- Then go to the data folder `data/`
- Double-click on the file `dirty_cafe_sale.csv`; it will open in Excel.

Saving a copy of your dataset in Excel format

- In  excel on the Ribbon click on the *File* Tab, then go *Save AS*
- Choose your save location. In our case, it is in the project root folder, then in the `dashboard/` subfolder.
- Give it the name, in our case *cleaned_cafe_sales.xlxs*
- Click on *Save*

Now you have made a copy of your dataset, which is a working file. So inspect your file carefully to determine the missing details and existing mistakes that are visible.

You can also bold your column titles, convert the dataset into tables, and apply other basic formatting, like changing the font if the current font is not readable.

To Bold Titles

- Select the first row containing titles.
- Click on the *Home* Tab
- Navigate to the *Font* group section. 
- Click on the *Bold* icon(*B*)

Alternatively, you can use the shortcut Ctrl + B to bold your selected titles.

Converting the Dataset into a Table

- Select all your data.
- Click on the *Insert* tab
- In the _Tables_ group section, click on the Table icon.

Alternatively, you can use the Ctrl + T Keyboard shortcut after selecting your data. This helps us to easily filter and organize our data.

Filtering and Sorting

You can filter and sort your dataset to check for blanks and missing information.

*Filter*

Applying a filter to your data allows you to select a specific type of data you want to view in a given column. In our case, we want to view Blanks in the Item column.

- Select the data
- Click on the *Home* tab
- On the _Editing_ group section click on *Sort & Filter*
- From the drop-down, click on *Filter*
- A filter icon is displayed on the title column.

- Click on the Filter icon on the title of any column. In the drop-down list of commands, in the Fields section, uncheck _Select All_, scroll through the field list and select *Blanks*, and click *OK* to see only blank fields.

Note:

To clear filters, click on the filter icon on the respective column, then click on Clear Filter from Item.
You can also sort the field using the steps. However, you can click on Sort A to Z - Ascending or Sort Z to A - Descending order.
You can also filter or sort custom, i.e., Filter by color or Sort by color, in case you have already applied conditional formatting.

Since our column has blanks, we need to count the blanks to know how many there are using the Excel function:

=COUNTBLANK(B2:B10001): there are 333 blank cells, which translates to around 3.33%. This will help us to understand the amount of data that we need to clean and how it will affect analysis or the level of bias. Below 5%, we will clean our data by replacing blanks with Unknown. We will do cleaning later.

Conditional Formatting

Conditional formatting highlights duplicate values, helping in spotting patterns or identifying anomalies.

Applying Conditional Formatting

- Select the columns or range to inspect; in our case, the  _Quantity_ Column.
- Navigate to the *Home* tab.
- On the *Styles* group, Click on _Conditional Formatting_
- Choose a criteria to conditionally format your column i.e. _Highlight Cells_, then Go to _Duplicate Values_, choose a color scheme and Click *OK*.
- All duplicate values in the column will be highlighted.

These are some of the basic tools in Excel we'll use while dealing with missing data, detecting outliers, duplicate detection, identifying inconsistent formats, irrelevant data, and detecting erroneous entries.

*1. Removing Duplicates*

Removing duplicates helps analysts to avoid redundancy, which can distort analysis, inflate counts, and skew findings that can lead to wrong and misguided business decisions.

To remove duplicates

- Select all data
- Click on the *Data* tab
- In the *Data Tools* section, click on the Remove Duplicates icon

- the fields from which you want to get rid of duplicates. In our case, we want to remove all matching records, so we'll select all fields.

- Click *OK*. All duplicates will be removed, with a pop-up dialog window indicating the total number of duplicates removed.

In our case, there were no duplicate records; thus, the message was "No duplicate values found."

*Handling Missing Values*

Missing values can result from human error, system limitations, incomplete forms, or data integration issues. All these can lead to inaccurate analyses, biased models, and misleading conclusions due to the variation resulting from missing data.

Excel provides multiple imputation techniques we can use to handle missing data. These techniques include, but are not limited to, replacing missing values with the mean, median, or mode. This depends on the type of data you are dealing with. For example, a data analyst replaces the product rating with an overall average of the product rating across all blanks in the product rating column to ensure consistent review data.

We can use the following techniques in Excel to handle missing data:

1. Filtering and Sorting to Find Blanks

You first have to find where missing values are.

Apply filters as discussed above on the item column to find blanks. Replace all blanks with the text value.

Steps

- Click on the filter icon on the *Item*column.
- Uncheck all Fields, then select only the *Blanks* field and click *OK*
- On the first cell of the *Item* column, type the text _Unknown_ and copy it down through the blank cells.

To Copy the Text

- Select the cell where you typed the replacement text("Unknown")
- Drag your pointer to the bottom right of the selected cell; your pointer turns into a dark cross at the tip of the bottom right corner.
- Click and drag the cross down the empty cells to copy the text into the blank cells
- Click and save your work once all blank cells are filled, and clear the filters.

Alternatively, you can use Find and Replace. To use Find and Replace:

- Undo the changes you have made and clear all filters first.
- Select the Item column
- Navigate to *Home* tab, on the *Editing* group click on _Find & Select_

- From the dropdown options, click on Replace
- In the Dialog box, leave the Find what: text box, leave it blank. We are finding blanks
- On the Replace with textbox enter "Unknown" text, and click Replace All to make the changes.

- A message box will be displayed showing how many blanks the changes were made to.

Also, you can use the IF() function to find and replace missing values in a column. The syntax is =IF(B2="", "Unknown", B2)

Explanation

B2="": Cell referencing the value contained in it; also the condition to check if B2 is blank.
Unknown: Value returned if B2="" is TRUE, that is, it is blank.
B2: If B2="" is FALSE, it retains the value or text held in B2.

To achieve this, you need to create a blank column to the right of the item column, then enter the function in cell C2, copy and drag down the formula, then copy the values held in cell C2:C10001, then paste special (As values only) the values into cell B2. All the other text values will be replaced.

*Text Cleaning and Standardization*

Inconsistencies such as trailing whitespaces, unprintable characters, and mixed text cases are common in text-based data. However, Excel offers functions that we can use to correct these errors and mishaps.

What is an Excel Function?

A function in Excel is an inbuilt formula used to perform mathematical, logical, statistical, or financial operations in Excel. A function must begin with an equal sign.

In cleaning text data, we use functions such as:

a. TRIM()

Used to remove extra spaces that were typed into cells and fields.

In our dataset, we can apply the function to Item to remove any trailing or extra spaces.

We need a new column to do this, then later copy and paste special the values of the new column.

In Cell C2 of the new column, type the formula on it and press Enter, then copy it down the column to get the rest of the values.

=TRIM(B2)

b. CLEAN()

Used to remove any unprintable characters. Also requires a new column and later copying the values into the original column.

We are going to work on the Item column, then copy the values to the original column.

In cell C2 enter =CLEAN(B2)

Copy the formula down to auto-fill values.

c. LEFT(), RIGHT(), and MID()

LEFT() is used to extract the specified number of characters from a text value starting from the left. I.e. Extracting the first three characters of the Transaction ID we use =LEFT(A2, 3) and we get "TXN"
RIGHT() extracts the specified number of characters starting from the right. I.e., extracting the last three characters of the transaction ID in cell A2: =RIGHT(A2, 3) we get "124"
MID() is used to extract a specified number of characters starting from a specified position. I.e. extacting the 4 characters of the Transaction ID starting position 2. =MID(A2,2,4) we get "N_99"

d. UPPER(), PROPER(), and LOWER()
UPPER() - Converts a text string into all uppercase.
PROPER() - Converts a text string into proper case; that is, it capitalizes the first letter of each word. We will convert our Item column to proper case for consistency.
LOWER() - Converts a text string into all lowercase.

To convert our Item column into proper case, we need another column, then copy the values back to the original column.

- Use this function in cell C2 `=PROPER(B2)`
- Copy it down through the column for all values.
- All the Item values have been converted into proper case.

Standardizing Dates

Dates may be formatted as:

- MM/DD/YYYY
- DD/MM/YYYY
- YYYY-MM-DD

And sometimes they may be misinterpreted depending on the local regional settings.

To format a date, you may use the Excel built-in date feature by following the steps below:

- Select the date column
- Go to *Home* tab, on the _Number_ group, from the drop down list choose _More numbers Formats_.
- From the dialog window, choose _Dates_ from the right section, then specify the date format you prefer and click *OK* to apply the changes.

We'll format the Transaction Date and change the column to Date format for consistency.

Alternatively, you can use the TEXT() function to format dates. The syntax is, e.g., =TEXT(H2, "MM/DD/YYYY), where H2 is a field containing a date.

Other date functions can help us work best with dates. I.e. NOW(), TODAY(), DATEDIF(), YEAR(),MONTH(), WEEK(), DAY()... You can research them.

e. CONCAT()

Used for joining two or more texts. For example: Joining the first and last name to make one name.

i.e. =CONCAT(A1," ", B1)

This is important for preparing data for standardized formatting, reporting, or creating lookups across datasets.

There are many more functions that can be used to handle text data.

*Handling Outliers*

Outliers are errors resulting from values that are much higher or lower than the expected range. For example: finding a person with an age of 223 in the age field.

The errors may result from human error, measurement inconsistencies, or valid but extreme occurrences.

We can use Filter & Sort to work around these inconsistencies. We can also use basic statistics to determine these errors.

Basic statistics: Mean, Standard Deviation, Median, and Mode

Mean and Standard deviation are data-driven methods to define what is considered a normal dataset.

AVERAGE() function is used to calculate the mean. i.e., calculating the average Quantity in our data.

=AVERAGE(C2:C10001) We get 3.

STDEV() is used to find the standard deviation of a sample. i.e the standard deviation of our Quantity column:

=STDEV(C2:C10001) we get 1.4

Values more than 2 or 3 standard deviations from the mean may be considered outliers.

MODE() and MEDIAN() functions are used to find the value with the highest frequency and the middle number, respectively.

For missing values, depending on the type of data you are working on. The mean, mode, or median can be used to fill in the missing values, considering that the data distribution is normal.

*Data Validation and Restriction*

Data validation ensures that data entered into a worksheet conforms to specific rules or formats, minimizing the risk of errors or inconsistencies. It is important before data collection is carried out. However, you can validate your data if already been collected to avoid future data entry errors.

For example: We are going to validate our Location field to only allow "In Store", "Take Away", or "Unknown".

To access data validation:

- Select the _Location_ column in our dataset.
- Click on the *Data* tab from our Ribbon
- Click on the validation icon.

- From the dialog box, choose List as the validation criteria

- Enter the list of items, separated by commas, OR refer to a list range.
- Here is how it looks on the worksheet.

Error Alerts and Input Message

These are useful guides during data entry.

Input Message: A brief output that appears when the user selects the validated cell, offering guidance on the expected input.

Error Alert: A message that appears if the user tries to enter invalid data. Stop: blocks entry, Warning: Allows the entry but prompts the user, and Information: gives the user feedback without blocking.

These messages, if well designed they reduce confusion and improve the quality of the data.

*Pivot Tables*

Pivot tables are tools in Excel that we can use to summarize our data. They are important tools used during visualization to create charts.

To create a pivot table in Excel:

Select the data

Click on *Insert_ tab, and from Tables group click o Pivot Table
From the dialog box, choose where to enter your pivot table; choose a new sheet

Click OK
Select the fields you want to summarize using your Pivot Table.

Summary of the Complete Data Cleaning Process

Selected all my data and converted it into a table. I used the shortcut Ctrl + T.
Added BOLD to my column titles and increased their font size to 14.
Used filters and sort to inspect my data. Accessed the dataset to find missing values, outliers, or any inconsistencies within the data.
Found blanks, columns with ERROR, and UNKNOWN as their input. I used Find and Replace to fill the blanks with Unknown, and also replaced ERROR and UNKNOWN with Unknown for consistency.
Quantity Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Secondly, I set the data type for the column to Number type, a whole number without a fraction part.
Price Per Unit Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Converted the column to the Currency type with the United States Dollar sign as the currency prefix, formatted to 2 decimal places. From observation through sorting and filtering, using the item and its respective price per unit, all items add up to the same price for the individual item. I had to manually fill in the blanks for individual items with the correct price.
Total Spent Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with blanks. Converted the column to the Currency type with the United States Dollar sign as the currency prefix, formatted to 2 decimal places. From the observation through sorting and filtering, total spent was the product of Quantity and Price Per Unit. So, I had to recalculate the total spent on each transaction using the PRODUCT() function in a new column, then used copy and paste special to paste the new values into the Total Spent column.
Recalculated the Quantity since some values were blank, yet they had the Price Per Unit and Total Spent filled. To get the Quantity used the formula =[@Total Spent]/[@Price Per Unit]. Then used Paste Special.
Payment Method Column: Used Find and Replace to replace the text strings ERROR and UNKNOWN with Unknown. Validated the field as a list field with the following options: "Cash", "Credit Card", "Digital Wallet", and "Unknown".
Location Column: Found blanks, columns with ERROR, and UNKNOWN as their input. Used Find and Replace to fill the blanks with Unknown, and also replaced ERROR and UNKNOWN with Unknown for consistency. Validated the field as a list type with only three fields: "In-Store", "Take Away", and "Unknown"
Transaction Date Column: Found blanks, columns with ERROR, and UNKNOWN as their input replaced ERROR and UNKNOWN with Blanks for consistency. The blanks were untouched. Then converted the field to a date type with the format "DD/MM/YYYY".
Inserted a pivot table summarizing the quantity of each item.
Found the average quantity, price per unit, and total spent.
Found the maximum and minimum price per unit.
Found the Total sales using the SUM() function. i.e. =SUM(E:E)

These are the operations performed on the dataset.

Conclusion

We have learnt great and unique technical skills in data analysis. We have found how important data analysis is and the best way to carry it out. If standardized, results will be meaningful in helping stakeholders make informed decisions that drive growth. Data cleaning is the most time consuming in the whole process, but if done right, it will serve a great deal.

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

Makori Nyachaki — Sun, 23 Aug 2026 05:34:57 +0000

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

GIT

Introduction

Git is an open source version control system used to manage projects. It is fast and efficient to manage projects using git. You can easily track the project version, history, modifications, and many more project activities. It allows for ease of collaboration while working on projects as teams.

Installing Git

Download and install git from Git. Ensure you download for your respective operating system.

Configuring Git

Before using Git effectively, you have to configure your identity by setting up your username and email.

This allows to easily track any changes to your project and the associate, that is; who made the changes?

To set up your identity, follow the following steps, using Git Bash a git terminal:

Steps

To set your username

git config --global user.name "YOUR NAME"

In place of YOUR NAME type in your correct user name that will be associated with your github account activity.

To set your email

git config --global user.email "youemail@example.com"

Note: It is recommended to use your correct email that is associated with your GitHub account.

Rename you GitHub branch to main

git config --global init.Branch main

After setting up your identity you can verify your configurations to confirm your identity for each field set.

To check your:

User name

`git config --global user.name`

`git config --global user.email`

Branch

git config --global init.Branch`

You can use the following command to check all you account details instead:

`git config --global --lists`

SSH

SSH key is a secure credential used to authentication to accessing network resources confidentially without need of a password. The ssh-key contains two keys. A Public and Private key.

Public key is used publicly, that is it is shared between peers. Used to identify you remote resources.

Private key it is a pair key that is kept as a secret for identifying your local machine.

Generating an SSH Key

Using Git Bash

Before generating a new SSH Key, it is recommended to check and confirm if you have an existing key which you can use.

To check if you have an existing key use the following command:

`ls -al ~/.ssh`

The command will list all existing ssh keys. Here sample output:

If there is no existing key, you can generate a new key that you will use to link your computer with your github account.

To generate a new ssh key, follow the following steps:

Genereate a new ssh-key

ssh-keygen -t ed25529 -C "youremail@example.com"

The email should be the one associated with with your github account.

Follow the prompt by adding the name of your key, and also you can add the passphrase which is optional.

The passhrase should be memorable, you will need to use it each time you want to access you github account.

Add your key to the SSH-Agent.

Open your Powershell Terminal and follow the steps below:

- Run the following command to start your SSH Agent

    `Get-Service -Name ssh-agent | Set-Service StartupType Manual`

- Add your key to the  path using the  command

    `ssh-add <PATH/TO/YOUR/PRIVATE_KEY>`

After the command, you should specifify the path to your private key and add it to your host machine's ssh-agent. i.e. ssh-add "C:/Users/<YOUR_USER_NAME/.ssh/<YOUR_PRIVATE_KEY>

If you don't know your computer's user name, you can use the whoami command to see your username.

Your PRIVATE_KEY_NAME is the name that you gave to your key.

- Once you're done, close your Powershell terminal and return to Git Bash.

- Type the following command

    `git config --global core.sshCommand "C:/Windows/System32/OpenSSH/ssh.exe`

- Then Clip and copy your key using the following commands:

    `clip < ~/.ssh/<PUBLIC_KEY.pub>`

i.e clip < /.ssh/my_key.pub

    `cat ~/.ssh/<PUBLIC_KEY.pub>`

i.e. 'cat ~/.ssh/my_key.pub` to view your public ssh key

- After displaying your key, select and copy the key to add to your GitHub account.

Adding Your Key to GitHub

To connect your computer and git-hub is necessary to facilitate access to your remote resources from your local computer.

You need to add your SSH Keys to GitHub to facilitate the connection. To achieve this, follow the steps:

If you have an existing GitHub account:

a. Login to your account using your email/username and password.

b. From the profile section, go to Settings

c. Scroll to SSH and GPG Key. Navigate and click on New SSH Key on the SSH Key Section to your right.

e. Add the Title/Name of the key i.e. Ubuntu Laptop, the key will be used for Authentication

f. Paste your key to the available textbox.

g. Finally, click on Add Key to save your key. You'll be prompted to re-enter your git-hub account password (login password), to confirm your authenticity before saving.

Now you are ready to go. Before you start, it is good you test your connection to check if it was successful.

Run Powershell as an Administrator and use the following command to test connection:

`ssh -T git@github.com`

If the connection is successful, your username will be displayed along with a success message.

If successful, you are good to go and work on your project.

Working With Folders

To keep you project structural and more organized; you've to keep its files and scripts in folders.

This will make it easier to navigate and access the project. Follow the steps below to work with folders in Windows.

In Git Bash Terminal(Opened normally)

To navigate and change location use the following command
```
`cd <path_location>`
```

To select the current location

    `cd .`

To get to the previous location one step back use

    `cd ..`

Getting back to the root directory use

    `cd ~`

You can also move to a specific folder by specifying the path to that folder

    `cd <path/to/folder>

example: cd "C:/Users/Admin/Desktop/My-Project"

My-Project/ is a folder located in Desktop, which is for the user with the username Admin.

To create a directory or folder use
```
`mkdir <directory_name>`
```

i.e. Create a directory named My-First-Project in Desktop mkdir My-First-Project

Navigate to your created folder. Assuming you are on Desktop, use the following command:
```
`cd My-First-Project/`
```

So your bash terminal path will look like ~/Desktop/My-First-Project

To list and view items use the following command
```
`ls`
```

It will list all files and folders excluding the hidden files and folders. As shown below:

To list and view all files and folders including the hidden ones you add -a attribute to the ls command as below

    `ls -a`

To list files and display their attributes we add the -l attribute to the ls command.

    `ls -l`

Note: You can combine the attributes to list all files and their attributes i.e. as ls -al

You can delete an empty folder using
```
`rm -r <folder_name>`
```

To delete a folder together with its contents we use the rm -r <non-empty_folder_name> command.

Example: Create a folder in you my project folder using the mkdir command, inspect the folder created and its contents, then delete the folder using the rm command:

Steps

Create scripts: mkdir scripts
Navigate to scripts and back to your project folder: cd scripts/ and cd ..
Inspect scripts: ls scripts/
Delete scripts: rm -r scripts/

Note: rm without any attribute will no delete a directory/folder even if empty, the attribute -r tells the command rm that what you're deleting is a directory.

You can copy and paste contents between different folders.

To copy contents moves a duplicate of the files to another location leaving the original intact.

While using the cp command you have to specify the file/contents path and the destination address. Example:

To copy some file named hello.txt to the scripts folder; you need to recreate the folder and create the file in the root directory then copy it to scripts folder in your projects folder

Steps

In your project folder create scripts mkdir scripts
Navigate to your project root folder and create the file hello.txt touch hello.txt
Copy the file to scripts cp hello.txt ~/scripts
Inspect both locations to see that the file exists in both location. ls and ls scripts

Note: You can specify the file path in the cp command if the file is in a different location and the destination path address

i.e. cp <~/path/to/file_to_be_copied.txt> <~/path/to_destination/>

You can move a file or any content completely from the source to an new destination using the mv command.

Example: Move hello.txt file from the project's folder to a new folder named docs

`mv hello.txt docs/

Note: You can also specify the file path in case the files and destination address are in different locations i.e. mv <~/path/from_source/hello.txt> <~/path/to_destination_folder/>

Working With Files

While working in your project you will need to create different files for your project.

The different files will be saved in different directories depending on their type and use.

The organization will help us to achieve a proper project structure, easier to navigate and access our project files.

We will create our files using Git Bash terminal.

To create a file we'll use the touch command.

Create a file named hello-world.txt in your project root directory.

    `touch hello-world.txt`

Use the ls command to confirm your file has been created.
To view the contents of the file we use the cat command, since our file is empty it will display 'nothing'.
```
`cat hello-world.txt`
```
To add contents to our file we use the echo command.

Add the following text to the file hello-world.txt; "Hello, World"

    `echo "Hello, World!" > hello-world.txt`

We use the > to redirect the output to our file. > Rewrites any content in the file.

If you want to add more content to the file use >>, more text will be added to our file.

Example; add the following text to our file "I am happy to learn SSH and Git".

    `echo "I am happy to learn Git and SSH" >> hello-world.txt`

cat hello-world.txt to view the contents of the file.

* Editing you file using nano *

Nano is an inbuilt text editor in Git Bash.

It helps us to navigate through our files; scripts, text file, .... once we make mistakes.

To open our script or text file we use the nano command in git bash then specify the file we want to open, for example open hello-world.txt using nano:

    `nano hello-world.txt'

There are many more commands for nano that will help you edit your text file. For example the most common commands are:

1. Ctr + O - To save your file

2. Ctrl + X - To exit your nano text editor

3. Ctrl + K - To Cut text in the editor

4. Ctrl + U - To Paste your text

5. Ctrl + G - To find help for you nano editor.

There are many more commands of help that you can use to edit you files in nano editor.

Your First Project

Initialize your first project with git

Before running the command, check to confirm that you are in your project folder using the pwd command.

The pwd command means print working directory it shows your current location with regard to the root directory.

If in your project directory, the path will end with the name of the project folder. I.e. My-First-Project

Use the git init command.

The command is used to initialize our project directory as a git repository.

Git repository structure.

After running the above command, you may notice notification of git initializing an empty git repo...

If you list to view the structure using the ls command you may notice a .git/ folder.

The folder contains Git's internal repository information, which git uses to manage activities such as: push, commits, pull, Branches, project history, repository configurations,....

Checking the project status

    `git status`

Used to show what's happening with your project currently.

It can show things such as untracked files, staged files to be committed yet, if the working tree is clean meaning everything has been committed.

The above show that there is nothing that has been staged for commit, there are three untracked files in the initialized repository.

Staging your project files

    `git add .`

It adds your project files to git for tracking.

Checking on the status of the staged file, you should get such an output with git status command

It prepares all you changes for the next commit.

You can stage a single file by specifying the file/folder you want to stage i.e. git add <file_name.txt>.

You should see almost a similar output when using git status command after staging a single file

Using the . means you stage everything in your project folder.

Commiting you staged files

    `git commit -m "<commit message>"`

Creates a saved checkpoint.

The message should be precise and clear, related to the activity carried out. i.e. "My First Project Using Git Commit"

It does not upload anything to git-hub yet.

Uploading to git-hub

    `git push -u origin <branch_name>

Sends all the committed changes to GitHub. However, in our case we get the above error because we have not linked our local project repo to a git-hub remote repository.

We will be linking our repositories in the next section.

After running this command, you can easily check on the changes whether they reflect on GitHub via your browser.

However, for us we are yet to see these changes. This may not work since we are yet to create a remote repository which we will then connect to our local project in our laptop.

Creating a remote repository

To create a remote repository, follow the following steps:

Login to your git-hub account
In your account click on "Create New Repository"
Enter the name of your Repository as "My-First-Project"
Do not create a README, uncheck the checkbox
Provide a brief descriptions for your project. The description is: "My First Project: From a Local Folder to Git hub Using Git and SSH"
Then click on Create Repository

A new empty repository is created with the name "My-First-Project"

Navigate to your repository
Click on the code section, then click on ssh tab and copy the ssh code

We'll use the ssh code to connect our remote and local repositories before pushing our project.

Connecting our local and remote repositories

Navigate to your git initialized project folder

Type in the following command and thereafter paste the ssh code for your remote repository and hit enter.

    `git remote add origin <your_remote_ssh_code>`

i.e. git remote add origin git@github.com:MakoriNyachaki/My-First-Github-Project.git

Rename your branch

If your remote repository's branch is not main rename it to main

    `git branch -m main`

Check the remote

    `git remote -v`

Push your project to git-hub

    `git push -u origin <branch_name>`

i.e. git push -u origin main this works to upload our committed changes to our git-hub repository.

Summary

An introductory article for learning GIT and SSH. It is thrilling practical exercise for generating ssh keys, working with folders and files, connecting local project folders with remote repositories, and working with git. The introductory article requires one to do further research on so many commands that are available for the different tools we have used here in order to be an expert. Let's learn forward.