DEV Community: Malaika Musee

Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

Malaika Musee — Tue, 22 Sep 2026 11:08:29 +0000

Introduction

Jumia is an E-commerce platform that allows individuals and businesses to buy and sell products online. Think of it as an online marketplace. This project seeks to transform raw product pricing and performance data into an interactive Excel dashboard, that provides a summary of useful information. This varies from examining the correlation between product pricing and discount levels influence purchase, as well as analyzing feedback from customers through product rating and reviews.

The goal of this analysis is to provide an overview of Jumia products’ performance. In this article, I will walk you through my end-to-end process where I clean and organise the raw data, enrich the data through calculations, analyze trends and correlations within the data, and finally, summarize key findings through an interactive dashboard.

Dataset description:

The raw dataset is made up on 6 columns A-F with Row 1 as the header row, followed by 115 data entries, with the data range as A1:F116.

A: Product (name of the product)
B: Current price in Kenya shillings
C: Old price in Kenya shillings
D: Discount percentage
E: Reviews (number of reviews per product)
F: Rating (product rating out of 5)

Data Cleaning and Preparation:

At first glance through the data, there are a number of issues ranging from duplicate entries, missing values, especially in the reviews and rating columns, as well as inconsistencies in alignment, formatting and data types.

In the product and rating columns, the values in those columns are not fully visible at a glance. To fix this:
Click anywhere within your data > Ctrl+A to select your entire range of data > On the Home tab, under the Cells group > Select the dropdown under Format > Select Autofit Column Width

Additionally, in order to have the header row frozen in place as you scroll down the data:
On the View tab, under the Window group > Select the dropdown under Freeze Panes > Select Freeze top row

Standardize data types

Ensure that all columns are in the right data type as follows:

A: Product (name of the product) - TEXT
B: Current price in Kenya shillings -CURRENCY(KES)
C: Old price in Kenya shillings -CURRENCY(KES)
D: Discount percentage -DECIMAL NUMBER (2dp)
E: Reviews (number of reviews per product) -NUMBER
F: Rating (product rating out of 5)-NUMBER

Steps:
Select column ie Product column > On Home tab under Number group > Select the correct data type from the dropdown OR click on the arrow at the bottom right of the Number group > Select correct data type as in TEXT

Select column Current price > On Home tab under Number group > Select the correct data type from the dropdown OR click on the arrow at the bottom right of the Number group > Select correct data type as in CURRENCY > Set to 2 decimal places > Select the currency symbol as KES/Ksh

Follow the same process for all columns to standardize the values in each to the right data type. Note that, text columns are align to the left, while numeric columns are aligned to the right.

Then, looking at the rating column, this is meant to be a value between 0 and 5, with Number as the data type. However, the words “out of 5” are in each cell in that row which makes it difficult to do calculations such as sum, average etc.
Therefore, rename the column to “Rating (out of 5), then have individual values as in 1,2,3.5 etc. This allows us to have the data in the column in Number format.

Find and replace command

Instead of manually going through rows of data to check for typos or change a label, this command allows you to “search and update”. Similarly this can be sued to find and standardize missing values in both text and numeric columns.

Select the column range ie Rating column > Input the shortcut Ctrl + H to access the find and replace dialogue box:

Under Find what: Input what excel is to search for within the selected range, in this case, “out of 5”
Under Replace with: Input what excel should replace the value with, in this case, leave blank

In the Current price and Old price columns, the correct data type should be currency, with the symbol as either KES of Ksh. However, in the raw data the currency prefix is manually typed in.
Use Find and Replace to solve this. Select column, then open dialogue box:
Find ‘Ksh’ and Replace with ‘blank’

Similarly, in the review column, it is impossible to have a negative number of reviews. Ideally, the column should have whole numbers representing the number of reviews given by customers for each product. Ue the find and replace option to remove the negative sign on some of the entries on the column.

Find and replace command can also be used to deal with blanks within the data range.
To do this:
Select entire data range (Ctrl + A) > Go to Home tab under Editing group > Select Sort & Filter dropdown > Select Filter option (has a funnel icon)
Each column header will now have a downward facing arrow that displays the values in that column as well as Sort & Filter options for that column.
In text columns, replace the blanks with either Unknown/ Not provided / N/A, while in numeric columns, replace the blanks with ‘null’.

Remove duplicates

In case of accidental double entries, the Remove duplicates command tells Excel to look for rows where entries in every single column match, then deletes the entire duplicate row.
To remove duplicates > Ctrl + A to select entire range > On Data tab under Data tools group > Select Remove duplicates tool > On dialogue box, ensure to check “My data has headers”

As a result, Excel finds and removes 3 duplicate values, 112 unique values remain.

After cleaning this data, our range is now A1:G113.

Data Enrichment

Here, we need to add the following calculated columns to contribute to our analysis:

1. Absolute discount (Kes)
This is the discount amount in Kenya Shillings, obtained by subtracting the current price from the old price
To obtain values for this column, input the formula:
=Old price – Current price

2. Rating category
This classifies the rating for each product, with reference to the Rating column, as:
Where a rating below 3 is marked as “Poor”, a rating above 4.5 is marked as “Excellent” and a rating between 3 and 4.4 is marked as “Average”.
To obtain values for this column:
=IF(ISBLANK(I2),"Not provided",IF(I2<3,"Poor",IF(I2>4.5,"Excellent","Average")))

In the function above, the Nested IF function is used to test multiple conditions in one formula. The general syntax is:
=IF(logical_test1, value_if_true1, IF(logical_test2, value_if_true2, value_if_false2))

Additionally, our reference column, the Rating column contained blanks which Excel interprets as zero. Using the =ISBLANK() function to check if a cell is blank and return True for blank cells and False for null cells.
Therefore, rows where the rating column is blank, will return “Not provided” in the the Rating category column.

3. Discount category
This classifies the discount percentage for each product, with reference to the Discount percentage column, as:
Where a discount below 20% is marked as ‘Low discount”, a discount between 20% and 40% is marked as “Medium discount”, and a discount above 40% marked as a “High discount”
To obtain values for this column:
=IF(F2<0.2,"Low discount",IF(F2>0.4,"High discount","Medium discount"))

4. Pricing category
This classifies the products based on their Current price as:
Products costing below Kes.1300 marked as “Budget friendly”, those costing between Kes.1300 and Kes.2500 marked as “Value” while those that cost above Kes.2500 marked as “Premium”.
To obtain values for this column:
=IF(B2<1300,"Budget friendly",IF(B2>2500,"Premium","Value"))

Next in the data enrichment process, we need to calculate a summary of the statistics we have. The following values were calculated:

Summation	Reference column	Formula	Result
Average current price	B: Current price	`=AVERAGE(B2:B113)`	KES 1,186.89
Average old price	C: Old price	`=AVERAGE(C2:C113)`	KES 1,811.11
Average discount percentage	F: Discount percentage	`=AVERAGE(F2:F113)`	0.37/37%
Average rating (out of 5)	I: Rating	`=AVERAGE(I2:I113)`	3.9
Total number of products	A: Products	`=COUNT(A2:A113)`	112 products
Total number of reviews	H: Reviews	`=COUNT(H2:H113)`	57

This now gives us our cleaned and enriched data for analysis:

Trend analysis

Now that our data is cleaned, and we have a summary of all key metrics, we will be using pivot charts and tables to check for patterns, trends and correlations between the variables in the data set. This will give us insights into the performance of different products, and guide business recommendations.
This also provides visual representation which is especially helpful in disseminating this information to the relevant decision makers.

From our data, we will be answering the following questions:

Are higher discounts leading to higher customer engagement?
Do highly rated products have higher or lower prices?
Which products are performing best based on reviews and ratings?
Which products may need improved pricing strategies?
What recommendations would you give to Jumia sellers?

From the business questions above, pivot tables and charts allow us to investigate relationships and trends within our data.
Pivot tables – Allow us to compare different measures and summarize information.
To inset a pivot table:
Click anywhere within your data range > On Insert tab > Select Pivot table > Select from Data/ Range > Select to place in a new worksheet > OK

On the right side of the worksheet is the Pivot table fields panel. There are 4 fields in a pivot table:

Filters – Allows you to include or exclude specific data from the pivot table
Columns – Displays data in columns across the top of the pivot table, to create comparison layers.
Rows – Displays data in rows on the pivot table, allows for grouping categories.
Values – Contain the numerical data to be interpreted or summarized. Performs calculations such as average, count, sum etc

Now, onto the relationships under investigation:

1. Relationship between discounts and reviews.
Insert a pivot table onto a new worksheet > Move Review column in the Values field > Set the summation to Count of reviews > Move Discount Category column to the Rows field

This shows the number of reviews for products in the high, medium and low discount category.
To insert the chart above to give a visual representation of the pivot table:
Click inside the pivot table >Go to Pivot Table Analyze on the menu bar > Select pivot chart > Select Column chart and preview > OK

The products with the highest discounts have the highest number of customer reviews, while the products with the lowest discounts have the lowest number of customer reviews. This implies that higher discounts drive more customer engagement, while lower discounts barely lead to customer interactions.

Recommendation:
Higher discount products should be more highlighted to the customer in marketing efforts to maximise traffic. The business may consider increasing the discount for the products with low discounts, or removing them from the promotions to make room for products with greater chance of generating customer interest

2. Relationship between ratings and reviews
Insert a pivot table onto a new worksheet > Move Reviews column to Values field > Set summation to Count for reviews > Move rating category to the Rows field

This shows the number of reviews for the respective rating categories ie Excellent, Average and Poor rating.
Insert a pie chart, as above, for a visual summary of the table.

Customer feedback is split between the positives and negatives. 19 excellent reviews and 12 poor reviews make up over half the total 57 customer reviews. This is against a total of 112 products.

Recommendation:
Identify the 12 customers who gave a poor review in order to identify the root cause, be it product defects, delays in delivery, poor customer service etc
Additionally, the business could incentivize detailed feedback, such as offering 10% off a customer's next purchase after completing a feedback form. This could help drive active customer feedback.

3. Relationship between pricing and ratings
Insert a Pivot table onto a new worksheet > Move Rating column to Values field > Set summation to average for rating values > Move Price category column to Rows field

Insert a column chart to visually represent this table.

Both the premium and value price categories have a rating above 4, out of the highest rating possible of 5. Customers buying this products are getting high quality products and value for their money.
However, budget friendly products have a lower rating indicating that lower cost items fail to meet customer expectations.

Recommendations:
Identify features, packaging or customer service that comes with premium and value products, and where feasible, see if these elements can be scaled down to the budget friendly products.

Dashboarding

A dashboard serves as the control centre of your analysis. It allows you to monitor key performance indicators (KPIs) at a glance.

A few key things to consider while building your dashboard include:

Choosing the right visuals: For example, a line chart to track sales trends over time or a bar chart to compare performance between different product categories.
Keep it focused: The dashboard is meant to be a summary of important information, and it is therefore best to avoid clutter.
Interactivity: Ensure your dashboard is responsive to filters, by use of slicers allowing you to view information for a specified subset of data.
Format: Use a consistent colour scheme, layout, and uniform alignment to ease user friendliness and ensure your report has a professional look.

Slicers
Slicers are a visual filter tool that allow filtering of data in pivot tables and charts, without altering the underlying data.
They serve to ease user interactivity, allowing users to filter data by various criteria.
In our dashboard, we will insert slicers for price category, rating category and discount category. To do this:

Click anywhere within the pivot table > Go to PivotTable Analyze on the menu bar > Under the Filter group select Insert slicer > Select slicer/ column to filter by ie Discount category, Rating category and Price category > select Ok

Layout

Header text box containing the title of the analysis. In our case: JUMIA PRODUCT PERFORMANCE DASHBOARD
Key Performance Indicators: Text boxes under the title that show important metrics such as total number of products, average product price, average discount percentage, average rating and total number of customer reviews.
Slicers to filter the data being viewed through the dashboard. In our case, slicers for discount, rating and pricing categories.
Key visuals such as pivot charts.

Interactivity
To ensure the interactivity of your dashboard, ensure to report connections on all slicers. What this does, is to link each slicer to multiple pivot tables and charts in the workbooks.
Therefore, all connected visuals show the same filtered data.
To achieve this:
Right click on the slicer > Select Report Connections or Pivot Table Connections > Check the boxes for all the pivot tables or charts you want to connect to that slicer > OK

After incorporating all the above into my dashboard, this was the result:

Project Resources

You can explore the full dataset and the Excel workbook used in this analysis in my GitHub repository.

From raw data to actionable insights: The role of DAX and dashboards in Power BI

Malaika Musee — Mon, 09 Feb 2026 19:13:51 +0000

Introduction

Importance of Data Analysis in Decision-Making

Data analysis enables organizations to comprehend their performance and take the right business decisions by identifying trends, issues encountered by their enterprises, and available business opportunities.

Overview of Power BI as a Tool for Analysts

Power BI: It is a powerful tool used in the field of data visualization and analysis. With the help of this tool, an individual can easily connect with numerous data sources and create reports in the most efficient manner.

Understanding Raw Data

Definition of Raw Data and Its Sources

Raw data consists of unprocessed information collected from different sources in the form of surveys, sales records, and website traffic, among others. In other words, it consists of unprocessed information.

Challenges Analysts Face with Messy Data

Analysts may encounter issues of missing values, data format, and irrelevant data while dealing with the raw data. Data cleaning may be a tedious task but is vital.

Power Query: Cleaning and Transforming Data

Introduction to Power Query

Power Query is a powerful data transformation and preparation tool that helps analysts transform raw data into a form that can be effectively analyzed.

Key Power Query Features

Home > Edit Queries: This feature enables editing and manipulating queries that connect to various sources.
Applied Steps: Allows users to view and/or change the steps taken in transforming the data.
Remove Rows: Facilitates the process of removing unwanted rows of data.
Pivot Column: Enables you to convert rows to columns, hence organizing your data better.
Merge Queries: This feature allows users to combine data from various sources into one data set.

Benefits of Using Power Query

Power Query makes data cleaning and transformation easier, which in turn makes it easier for data analysts to handle unclean data and transform it for better analysis.

The Power of DAX

Introduction to DAX (Data Analysis Expressions)

DAX is a formula language used in the Power BI tool to create custom calculations and data modeling. It is very helpful in performing complex calculations on the data.

How DAX Helps in Data Manipulation and Analysis

In addition to this, with the help of DAX, analysts can make use of measures and calculated columns that help gain better insight into their data. For instance, they might want to compute a total or average for a certain year or maybe carry out a

Examples of Common DAX Functions

Some common DAX functions include:
- SUM(): Adds up numbers in a column.
- AVERAGE(): This function calculates the average of numbers.
- COUNT(): Returns the count of items in a column.

Visualizing Data with Power BI's Chart Options

Because Power BI offers many different chart options, users are able to clearly visualize and analyze their data.
There are several options to choose from, and each type of chart can be used to arrive at different kinds of insights.
There are column charts to make comparisons, line charts to illustrate trends, pie charts to show proportions, and more.
Besides this, Power BI also offers advanced charts and analytics tools. The key is to select an optimal mix of charts that clearly and concisely address the objective.

4. Creating Effective Dashboards

Key Elements of a Successful Dashboard

A good dashboard will display the data in such a way that it is clear and unambiguous. Key elements will include:
- Visualizations: Charts and graphs that make data easy to understand.
- Filters: Options for the user to concentrate on specific data points.
- Interactivity: Options that provide access to any information through clicking on them.

Tips for Visualizing Data Effectively

Simple charts can be used as a means of communication. Avoid cluttering your chart with information, focusing only on the most important information that interests your audience. Color coding can be used for better communication.

5. Translating Insights into Action

How to Interpret Data Insights

Analysts have to interpret the findings from their research into specific actions. It entails understanding the findings from the data as well as how they can be used for business decisions.

Real-World Applications and Decision-Making Processes

For example, in a sales dashboard, if the team notices a decline in sales for a particular item, they may decide to change their marketing policies or improve the quality of their products.

6. Conclusion

Recap of the Importance of Using Power BI

Power BI is a useful data analysis tool that allows users to convert messy data into useful insights. It makes data analysis easier, regardless of the analyst's skill level.

Introduction to Microsoft Excel for Data Analytics

Malaika Musee — Sun, 25 Jan 2026 15:16:26 +0000

What is Excel?

Microsoft Excel is a spreadsheet program that helps you collect, organize, analyze, and visualize data.

Key Components

Rows: Horizontal lines of cells, numbered from 1, 2, 3, etc.
Columns: Vertical lines of cells, labeled A, B, C, etc.
Cells: The intersection of a row and a column, where data is entered.

Excel Interface

Taskbar: The bar at the top containing tools and options for your spreadsheet.
Ribbon: The main toolbar with tabs like Home and Insert for different functions.
Quick Access Toolbar: A small toolbar for frequently used commands like Save and Undo.
Formula Bar: Displays the content of the active cell and allows editing.
Name Box: Shows the address of the active cell (e.g., A1).
Worksheet Tabs: Each tab at the bottom represents a separate sheet in the workbook.
Workbook: The entire Excel file that contains one or more worksheets.

Key Features

Saving: Use File > Save As to save your work for the first time.
Quick Save: Press Ctrl + S to save changes quickly.

Formatting and Cleaning Data in Excel

Text Formatting

To format text as Bold, Italic, or Underline, and to adjust Font Size and Type, select the desired cells (e.g., A1:D1), navigate to the Home tab, and in the Font group, click B for Bold, I for Italic, U for Underline, change the font type (e.g., Calibri, Arial), adjust the font size (e.g., from 11 to 14), and select a font color.

Alignment

Alignment determines how content is positioned within a cell:

Select the cells you want to format.
Go to the Home tab and find the Alignment group.
Choose from the following options:
- Horizontal Alignment: Align Left, Center, or Align Right.
- Vertical Alignment: Top, Middle, or Bottom.
- Wrap Text: Allows long text to display on multiple lines within the same cell.
- Merge & Center: Combines multiple cells into one and centers the text.

Number Formatting

Number formats modify how numbers appear without changing their actual values. Common formats include:

Steps:

Select the cells containing numbers.
Go to the Home tab and find the Number group.
Use the dropdown menu to select the desired format (e.g., Number, Currency, Percentage, Short Date).

Conditional Formatting

Conditional Formatting automatically applies formatting based on specific rules:

Select the data range (e.g., B2:B20).
Go to the Home tab and click on Conditional Formatting.

You can use Highlight Cell Rules to format cells based on conditions like Greater Than or Less Than, apply Top/Bottom Rules to emphasize the highest or lowest values, and utilize Data Bars to visually represent values by filling cells proportionally.

Removing Duplicates

To eliminate duplicate entries in the range A1:U877, first, select the dataset. Then, go to the Data tab and choose the option to Remove Duplicates, ensuring the "My data has headers" box is checked if your first row contains titles.

The "Employee ID" is used as the unique identifier to detect and remove duplicate entries in the dataset shown in the image. Utilizing a unique identifier is crucial to ensure each employee is represented only once, maintaining the integrity and accuracy of the data.

Sorting and Filtering Data

Sorting Data

Sorting allows you to rearrange the order of rows based on the values in selected columns.
You can sort text in alphabetical order (A to Z or Z to A), numbers from smallest to largest or largest to smallest, and dates from oldest to newest or newest to oldest.

To perform a basic sort:

Click a cell in the column you want to sort.
Go to the Data tab and choose Sort A to Z or Sort Z to A.
Excel will sort the entire table based on the selected column.

For a more advanced, multi-level sort:
Add additional sort levels by clicking Add Level (e.g., sort by First Name A-Z first, then by Department A-Z and finally by Hire Date- Oldest to Newest).

Filtering Data

Filtering allows you to hide rows that don't meet specific criteria and show only the rows that do. This helps you quickly find and focus on the data you need.

To apply a filter:
Click anywhere in your table (ensure it has headers).
Go to the Data tab and click Filter (or on the Home tab, click Sort & Filter > Filter). Dropdown arrows will appear on the header cells.

Using the dropdown arrows to check or uncheck specific values, or apply text, number, or date-based filters.
For example, if you want to filter data for employees in the HR and Finance departments, click on the dropdown filter arrow in the Department Column Header, and select as follows:

Result after applying filter:

Freeze Panes

Freeze Panes is a useful feature that keeps specific rows or columns visible as you scroll through your worksheet. This is particularly helpful for keeping headers or important information in view.
Select the entire data range > Go to View Tab in the top ribbon > Freeze Panes

Options include:
-Freeze the Top Row
-Freeze the First Column
-Custom Freeze

Basic Calculations in Excel: Formulas and Functions

Excel provides two primary ways to perform calculations: formulas and functions.

Formulas

Formulas are user-defined expressions that allow you to perform calculations and manipulations on data. Formulas can be simple, such as =A1+B1, or more complex, involving multiple operations and cell references.

To create a formula:

Start the formula with an equal sign (=).
Include cell references, values, and mathematical operators (e.g., +, -, *, /).
The formula will calculate the result and display it in the cell.

Example: To calculate the sum of cells A1 and B1, the formula would be =A1+B1.

Functions

Functions are pre-built, predefined formulas that perform specific calculations or operations. Functions have a unique syntax and require arguments to be provided.

To use a function:

Start with an equal sign (=).
Type the function name, followed by open and close parentheses ().
Inside the parentheses, provide the necessary arguments, separated by commas.

Example: To calculate the sum of the values in cells A1 through A10, the function would be =SUM(A1:A10).

Some common Excel functions include:

SUM(): Adds up the values in a range of cells.
AVERAGE(): Calculates the average of the values in a range of cells.
COUNT(): Counts the number of cells in a range that contain numeric values.
IF(): Performs a logical test and returns one value if the test is true, and another if the test is false.

Formulas	Functions
Formulas are user-defined expressions that perform calculations or manipulations on data.	Functions are pre-built, predefined formulas that perform specific calculations or operations.
Formulas can be simple, such as `=A1+B1`, or complex, involving multiple operations and cell references.	Functions have a specific syntax and require arguments to be provided, such as `=SUM(A1:A10)`.
Formulas are flexible and can be customized to suit specific needs.	Functions are optimized for specific tasks and provide a standardized way to perform common calculations.
Formulas can include a combination of cell references, operators, and values.	Functions are identified by their unique names, such as `SUM`, `AVERAGE`, `IF`, etc.
Formulas are entered directly into cells, starting with an equal sign (=).	Functions are called within a formula, also starting with an equal sign (=).

Text Functions

Excel provides a set of text functions that help clean and standardize text data. These functions include:

UPPER(kenya): Converts all letters in the provided text to uppercase-KENYA
LOWER(KENYA): Converts all letters in the provided text to lowercase-kenya
PROPER(kenya): Capitalizes the first letter of each word in the provided text- Kenya
TRIM(" Kenyatta University "): Removes any leading, trailing, or extra spaces between words in the provided text- KenyattaUniversity
LEFT("Mombasa",3): Returns the specified number of leftmost characters(3) from the provided text- Mom
RIGHT("Nakuru",4): Returns the specified number of rightmost characters from the provided text- Kuru
MID(text, start_num, num_chars): Returns the specified number of characters from the middle of the provided text, starting from the indicated position.

Aggregate Functions

Excel also provides a set of aggregate functions that can perform calculations on a range of cells. These functions include:

SUM(number1, [number2], ...): Adds the values in the specified range or list of numbers.
AVERAGE(number1, [number2], ...): Calculates the average (mean) of the values in the specified range or list of numbers.
MIN(number1, [number2], ...): Returns the smallest number in the specified range or list of numbers.
MAX(number1, [number2], ...): Returns the largest number in the specified range or list of numbers.
COUNT(value1, [value2], ...): Counts the number of cells that contain numeric values in the specified range or list of values.

For example, in the following data:

Average performance score, column I, is calculated as follows:

-Sum on bonus, column K, is calculated as follows:

-Minimum work experience (years),column N, is calculated as follows:

Logical Functions

Excel's logical functions allow you to perform conditional operations and make decisions based on specific criteria. These functions include:

IF(logical_test, value_if_true, value_if_false): Returns one value if a specified condition is true, and another value if the condition is false.
For example, to promote employees with over 20 years work experience, and retain the rest in their current positions, input as follows:

AND(logical1, [logical2], ...): Returns TRUE if all of the given conditions are true, and FALSE otherwise.
For example, To identify employees with over 20 years experience AND more than 10 projects, input as follows:

OR(logical1, [logical2], ...): Returns TRUE if any of the given conditions are true, and FALSE otherwise.
For example to identify employees with over 20 years experience OR more than 10 projects, input as follows:

NOT(logical): Returns the opposite of the given logical value (TRUE becomes FALSE, and FALSE becomes TRUE).

NESTED IF: Allows for the nesting of multiple IF statements to create complex conditional logic.
Imagine you have a spreadsheet where you need to determine the grade of a student based on their test score. The grading system is as follows:

If the score is 90 or above, the grade is "A".
If the score is between 80 and 89 (inclusive), the grade is "B".
If the score is between 70 and 79 (inclusive), the grade is "C".
If the score is between 60 and 69 (inclusive), the grade is "D".
If the score is below 60, the grade is "F".

The nested IF function works like this:

The first IF statement checks if the score is 90 or above. If it is, the grade is "A".
If the first condition is not met, the function checks if the score is between 80 and 89 (inclusive). If it is, the grade is "B".
If the first two conditions are not met, the function checks if the score is between 70 and 79 (inclusive). If it is, the grade is "C".
If the first three conditions are not met, the function checks if the score is between 60 and 69 (inclusive). If it is, the grade is "D".
If none of the previous conditions are met, the grade is "F".

=IF(score>=90, "A", IF(score>=80, "B", IF(score>=70, "C", IF(score>=60, "D", "F"))))

Lookup Functions

Excel's lookup functions enable you to find and retrieve data from a range or table. These functions include:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Looks up a value in the leftmost column of a table and returns the value in the same row from a specified column.
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]): Looks up a value in the topmost row of a table and returns the value in the same column from a specified row.
INDEX(array, row_num, [col_num]): Returns the value in a specified cell in an array.
MATCH(lookup_value, lookup_array, [match_type]): Finds the position of a value in an array that matches the specified value.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]): Performs a flexible lookup, allowing for more advanced search options.

Conditional Math Functions

Excel's conditional math functions allow you to perform calculations based on specific criteria or conditions.
These functions include:

SUMIF(range, criteria, [sum_range]): Sums the values in a range that meet a single condition. Provide the range to evaluate, the criteria to match, and an optional range to sum. For example, to find the sum of the salary and bonus for employees who's salary is above Ksh.50,000, input the following:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...):
Sums the values in a range that meet multiple conditions.
Provide the range to sum, followed by pairs of criteria ranges and their respective criteria.
COUNTIF(range, criteria):
Counts the number of cells in a range that meet a single condition.
Provide the range to evaluate and the criteria to match.
For example, to count the number of employees aged above 40 years:

COUNTIFS(range1, criteria1, [range2, criteria2], ...):
Counts the number of cells in a range that meet multiple conditions.
Provide the ranges to evaluate and their respective criteria, in pairs.
AVERAGEIF(range, criteria, [average_range]): Calculates the average of values in a range that meet a single condition.
Provide the range to evaluate, the criteria to match, and an optional range to average.
AVERAGEIFS(average_range, criteria_range1, criteria1, ...): Calculates the average of values in a range that meet multiple conditions.
Provide the range to average, followed by pairs of criteria ranges and their respective criteria.
For example, to find the average age of married employees with a performance score above 6, input as follows:

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...): Returns the largest value in a range that meets one or more conditions. Provide the range to evaluate, followed by pairs of criteria ranges and their respective criteria.

Understanding Pivot Tables in Excel

What is a Pivot Table?

A Pivot Table is a powerful data analysis tool in Excel that allows you to summarize, analyze, explore, and present your data in a concise format.
It enables you to extract meaningful insights from large datasets by organizing and aggregating data in a structured way.

Key Features of Pivot Tables

Dynamic Data Analysis: Easily rearrange data to view it from different perspectives.
Data Summarization: Quickly calculate sums, averages, counts, and other statistics.
Interactive Filtering: Use filters to focus on specific data points.

How to Create a Pivot Table

Prepare Your Data: Ensure your data is organized in a tabular format with headers. Each column should represent a different variable.
Select Your Data: Click anywhere in the data range you want to analyze.
Insert a Pivot Table:
- Go to the Insert tab on the Ribbon.
- Click on PivotTable.
- In the dialog box, confirm the data range and choose where to place the Pivot Table (new worksheet or existing worksheet).
Build Your Pivot Table: A new sheet will open with a PivotTable Field List on the right side.

The Four Fields of a Pivot Table

Filters: Allows you to filter the entire table based on specific criteria.
Columns: Displays data across the top of the table, defining column headers.
Rows: Displays data down the side of the table, defining row labels.
Values: Contains the data to be summarized or aggregated, such as sums or counts.

For example, the below pivot table shows the average salary per department, for male and female employees, with a filter on marital status, showing data for only those who are married:

Understanding Slicers in Excel

What are Slicers?

Slicers are visual filters in Excel that provide an intuitive way to filter data in Pivot Tables and regular tables. They allow users to quickly and easily filter data by clicking on buttons, enhancing the interactivity of data analysis.

Key Features of Slicers

User-Friendly Interface: Slicers present filtering options in a clear, visual format, making it easy to understand which filters are applied.
Multiple Selections: Users can select multiple items in a slicer at once, allowing for more complex filtering.
Real-Time Filtering: Changes made through slicers immediately update the data displayed in the Pivot Table or table.

How to Create a Slicer

Select Your Pivot Table: Click on the Pivot Table you want to filter.
Insert a Slicer:
- Go to the PivotTable Analyze tab (or Options in older versions).
- Click on Insert Slicer.
Choose Fields: In the dialog box, select the fields for which you want to create slicers (e.g., Region, Salesperson).
Position the Slicer: After inserting, you can move and resize the slicer on your worksheet.

Using Slicers

Filter Data: Click on the buttons within the slicer to filter the data displayed in the Pivot Table.
Clear Filters: Use the clear filter button (typically an icon with an eraser) in the slicer to remove all filters at once. The below image shows slicers for the categories: Department, Marital Status and Education level. As peer filters applied in the slicers, the data displayed is for the HR, Finance and IT departments, as well as for those who are married:

Introduction to Charts in Excel

Charts in Excel are graphical representations of data that help visualize trends, patterns, and comparisons.
They make it easier to interpret complex data sets and communicate insights effectively.
Excel offers various chart types, each suited for different kinds of data analysis.

Comparison of Chart Types

Chart Type	Description	Best Used For
Column Chart	Displays data using vertical bars, with categories on the x-axis and values on the y-axis.	Comparing values across categories.
Bar Chart	Similar to column charts but uses horizontal bars.	Comparing values across categories, especially with long category names.
Line Chart	Represents data points connected by lines, showing trends over time.	Displaying trends or changes over time.
Pie Chart	Circular chart divided into slices, representing proportions of a whole.	Showing percentage or proportional data.
Combo Chart	Combines two or more chart types, such as line and column charts, in a single chart.	Comparing different data series with different value ranges.

Bar chart showing total bonus by education level

Column chart showing average salary by department and gender

Line chart showing average performance score by hire month

Pie chart showing employee count by remote work status

Dashboarding in Excel

What is Dashboarding?

Dashboarding in Excel refers to the process of creating a visual interface that consolidates and presents key metrics and data insights in a single view.
Dashboards are designed to provide quick access to important information, enabling users to monitor performance, track progress, and make informed decisions efficiently.

Key Features of Excel Dashboards

Data Visualization: Dashboards utilize various charts, graphs, and tables to represent data visually, making it easier to understand complex information at a glance.
Interactivity: Users can interact with dashboards through features like slicers, buttons, and drop-down lists, allowing for dynamic data exploration.
Real-Time Data Updates: Dashboards can be linked to live data sources, ensuring that the information displayed is always current and relevant.

Components of an Excel Dashboard

Charts and Graphs: Visual representations of data (e.g., bar charts, line charts, pie charts) that highlight trends and comparisons.
Tables: Organized data sets that provide detailed information and support the visual elements.
Slicers and Filters: Tools that allow users to filter data dynamically, enabling them to focus on specific metrics or time periods eg; department, marital status, education level and gender.
Key Performance Indicators (KPIs): Metrics that are critical to the success of an organization, often displayed prominently on the dashboard for quick reference eg; Total salary, Total bonus, Average age, Average performance score

Conclusion

Excel is a powerful tool for data analytics, enabling users to organize, analyze, and visualize data efficiently. Its features, such as Pivot Tables and charts, facilitate quick insights and informed decision-making.

Getting Started with Git and GitHub: A Beginner's Guide

Malaika Musee — Sun, 18 Jan 2026 10:10:36 +0000

Introduction

In the world of software development, version control is essential. Git is a version control tool that helps you track changes in your code, while GitHub allows for collaboration and showcases your projects. In this article, we will explore how to set up Git Bash, connect it to your GitHub account, and understand the basics of pushing and pulling code.

What is Git Bash?
Git Bash is a command line interface for Windows that provides a Unix-like environment for Git. It allows you to execute Git commands and manage your repositories efficiently.

What is Git Hub
GitHub is a web-based platform that uses Git for version control. It enables collaboration among developers, allowing them to see each other's work, contribute to projects, and maintain a portfolio of their projects.

Why Use Git and GitHub?

Version Control: Track changes to your code over time.
Collaboration: Work with others seamlessly.
Portfolio: Showcase your projects to potential employers.

Installing GitBash
Download Git Bash: Visit Git Install select your operating system, and download the installer.
Install Git Bash: Follow the installation prompts to set up Git Bash on your device.

Verifying Installation
After installation, open Git Bash and run the following command to check the installed version:

git --version

Configuring Name and Email

Set your name

git config --global user.name "YourName"

Set your email

git config --global user.email "YourEmail"

Check configurations

git config --list

Connecting GitHub to Git Bash Using SSH Keys

An SSH key is a secure access credential used in the SSH protocol. It consists of a pair of cryptographic keys: a public key (stored on the server) and a private key (kept on your computer).

Importance of a SSH Key

Secure Connection: SSH keys create a secure, encrypted connection to GitHub.
Authentication: They allow authentication without needing to enter your username and password each time.
Convenience: Once set up, SSH keys streamline access to your repositories.
Access Control: You can generate different keys for different devices, controlling access to your account.
Prevents Unauthorized Access: SSH keys help protect your repositories from unauthorized users.
Widely Supported: Most Git clients, including Git Bash, support SSH keys as a standard practice.
Easy to Manage: You can easily revoke or regenerate keys if they are compromised, enhancing security._

Steps to Generate an SSH

Check for Existing SSH Keys:

ls ~/.ssh

Generate a New SSH Key:

ssh-keygen -t ed25519 -C "YourEmail"

Start the SSH Agent:

eval "$(ssh-agent -s)"

Add Your SSH Key to the SSH Agent:

ssh-add ~/.ssh/id_ed25519

Adding Your SSH Key to GitHub

Copy the Public Key:

clip < ~/.ssh/id_ed25519.pub

Add the SSH Key to GitHub

Go to GitHub, navigate to Settings > SSH and GPG keys > New SSH key. Paste the copied public key and give it a title (e.g., "GitHub Key"). Click Add SSH key.

Test Your Connection:

ssh -T git@github.com

Result:

Basic Commands in Git

mkdir "kenya"
Meaning: Make Directory
Function: Creates a new folder named "kenya". This command is used to organize your projects by creating a dedicated directory for related files.

cd "kenya"
Meaning: Change Directory
Function: Navigates into the "kenya" folder you just created. This allows you to perform actions within that specific directory.

git init
Meaning: Initialize Repository
Function: Initializes a new Git repository within the current folder ("kenya"). This command sets up the necessary files and structure for Git to track changes in this directory.

touch "nairobi"
Meaning: Create File
Function: Creates a new file named "nairobi" within the current folder. The touch command is commonly used to create empty files.

touch student.py
Meaning: Create Python File
Function: Creates a new Python file named "student.py". This file is ready for you to start writing Python code.

git add .
Meaning: Add Files to the Repository
Function: Stages all changes, including the newly created "nairobi" file, for the next commit. The . signifies that all files in the current directory should be added.t add .`

touch README.md
Meaning: Create README File
Function: Creates a README file named "README.md". This file typically contains information about the project, such as its purpose, how to use it, and other relevant details.

cd ~
Meaning: Change to Home Directory
Function: Navigates back to your home directory. The tilde (~) represents the path to the home directory of the current user, allowing you to quickly return to a familiar starting point.

Mastering Git Staging, Committing, and Pushing/Pulling

Staging Files
Imagine you're working on a school project, and you've made changes to several files. The staging area in Git is like a holding area where you can choose which changes you want to "save" for the next update to your project.
The staging area in Git is where you prepare your changes before committing them to the repository.
This allows you to selectively choose which modifications you want to include in the next commit, rather than committing everything at once.
The following are the main commands for staging files:

git add <file-name>
Stage a specific file for the next commit.

git add .
Stage all modified and new files in the current directory

git add -A
Stage all modified, new, and deleted files in the entire project.

git rm --cached <file-name>
Unstage a file that has been added to the staging area.

git reset <file-name>
Unstage a file and leave the working directory unchanged.

Committing to the Repository
After you've added the files you want to the staging area, you can create a new "commit" to save those changes to your project's history.
Commits are like checkpoints in your project, where you can go back and see what changes were made at that point.

git commit -m "Finished adding new chapter to report"
This command creates a new commit with the changes you've staged, and adds a message describing what you did, like "Finished adding new chapter to report".

git commit --amend -m "Updated the new chapter in the report"
This command allows you to modify the previous commit. You can update the message to better describe the changes, like "Updated the new chapter in the report".

git reset --soft HEAD~1
This command undoes the last commit, but keeps the changes in the staging area. This is useful if you need to make some final edits before officially saving the changes.

Pushing and Pulling from Repositories

Connecting to a Remote Repository

git remote add origin <remote-repository-url>
This command connects your local Git repository to a remote repository, typically hosted on a service like GitHub, GitLab, or Bitbucket.

The "origin" name is a common convention for the primary remote repository.

Pushing Changes to the Remote Repository
git push -u origin master

This command "pushes" your local master branch (the main branch of your project) to the remote origin repository.

The -u flag sets the upstream branch, which means that from now on, you can simply use git push without specifying the remote and branch, and Git will know to push to the origin repository's master branch.

Pushing your changes means you're uploading your local commits to the remote repository. This allows your teammates to access and work on the same codebase.
Let's say you've been working on a new feature and have made several commits to your local master branch. When you're ready to share your work with your team, you can use the git push command to upload your changes to the remote repository.

Pulling Changes from the Remote Repository

git pull

This command "pulls" the latest changes from the remote repository and merges them into your local branch.
This is essential for keeping your local project up-to-date with the work done by your teammates.

Pulling changes means you're downloading the latest commits from the remote repository and integrating them into your local project.
Imagine your team has been collaborating on the project, and your teammates have pushed several commits to the remote master branch.
To get those changes on your local machine, you can use the git pull command.
This will fetch the latest commits from the remote repository and merge them into your local master branch, ensuring that your project is synchronized with the team's work.

Cloning a Remote Repository
git clone <remote-repository-url>

This command creates a local copy of a remote repository.
This is useful when you want to start working on a project that is hosted on a remote server, such as when you join a new team or start a new project.

Cloning a remote repository means you're downloading the entire project history and setting up the necessary Git configuration on your local machine.
For example, your team lead shares the URL of the project's remote repository with you. You can then use the git clone command to create a local copy of the project on your computer, allowing you to start working on the project immediately.

Conclusion
Git is an essential tool for managing projects and collaborating, whether you're new to programming or an experienced data scientist. By learning the basics of staging files, committing changes, and syncing with remote repositories, you can take control of your project's version history and work seamlessly with your team.