DEV Community: jayson kibet

Python used in Data Analytics

jayson kibet — Sat, 09 May 2026 14:20:04 +0000

Introduction

Python is simply a high-level programming language used in data analytics,web development,automation,AI and so many more fields.
It was created by Guido van Rossum and released in 1991.
I will walk you through how it's used in Data Analytics.

Why is python popular in data analytics

Python consistently ranks among the world’s most popular programming languages because it balances simplicity,power and flexibility which is often rare in programming languages.

1.Python looks simple and easy to read

Compared to other programming languages like Java or C++,Python code is much simple and usually takes fewer lines.It makes it easier for beginners to learn.

unlike java:

2.Used in data analytics

You can calculate the average of sales or anything by writing a simple piece of code:

3.It has plenty of libraries

A good example is the 'Pandas'.It lets you load a spreadsheet(or CSV file) and start exploring it immediately.

4.Productivity

Despite being slower than other languages,Python tends to be more productive since it only needa a few lines of code and as a developer,you can build a project much faster.

Python libraries used in data analytics

These are basically the tools you'll use.

1.Pandas

Pandas lets you load a spreadsheet (or CSV file) and start exploring it immediately.It is also so powerful since it helps you clean,organize,filter and analyze data with very little code.

It is widely used by data analysts and data scientists to work with tables and large datasets efficiently.Learning Pandas is one of the most important steps in becoming comfortable with data analytics using Python.

3.Numpy

Numpy handles mathematical operations.

In the above photo,i calculated the mean in less than a minute.It's incredibly useful when you're crunching hundreds of values.You can also calculate the median,standard deviation without writing loops.Another reason why analysts love it.

4.Matplotlib and Seaborn

These are the best for visualization in Python that turn your data into charts.Matplotlib is the foundation and Seaborn sits on top of it and organizes things in a nicer way with less effort.

A chart is worth a thousand numbers.These libraries turn your boring tables into something you can actually see and understand.
1.Bar charts for comparisons - plt.bar(['A','B','C'], [10,25,15]) shows which category wins.
2.Histograms for distributions - plt.hist(ages) reveals if your customers are mostly young or old.
3.Seaborn makes everything prettier - sns.barplot(data=df, x='city', y='sales') gives you professional colors and cleaner layouts without fiddling.

Using python in data cleaning,analyzing and visualizing

When you're working as a data analyst(or even just exploring data for fun),you'll follow the same process almost every time:
Clean the data,analyze the data then visualize the data

1.Data cleaning

The raw data is always full of messy stuff like duplicates,wrong capitalizaion,empty cells,wrong data types and many more.It is your job to clean it.So python allows you to clean it in a much easier way

By running that,you can save a lot of time that you could have spent in excel.

I also love python since you can save the code and still run it months later.In simple terms,i mean Excel forces you to repeat the same clicks every time.Python remembers.

2.Analyzing the data

once your data is clean,you can now solve every question you want.Python gives you answers fast and the more specific your questions,the more useful the answers become.

You don't need to memorize all these.Just know they exist.Knowing you can answer almost anything in seconds is what makes Python fun.

3.Creating visuals

Numbers in a table are hard to understand and confusing especially thousands of rows.Charts make things click immediately.

That creates a bar chart showing which regions are selling the most.When you bring a chart like that into a meeting,people get it way faster than if you'd read the numbers aloud.
You can create more than a bar graph:

fun fact:Most people in meetings don't care about your math.They care about what they can see.A clean chart does the talking for you. You just point and say"Look at this."
Once you write the code for a chart,you can reuse it on next month's data with zero extra work.

Real-world examples of python in Data Analytics

Apart from the theory part,Python is used behind the scenes in running almost every industry it the day-to-day life

1.Healthcare

Medical institutions used Python to help save lives.How?During the Covid-19 pandemic,Python helped researchers model how the virus spreads and which interventions worked best.Running on laptops,shaping public health decisions in real time.

2.Industries like Youtube and Netflix

Youtube suggests videos based on what you watch often.It's not by magic.That's Python.Without Python,you'd need a lot of humans picking videos for you manually.So how is it done?Python tracks how long you watch each video,It compares your patterns to millions of other users.Those "Up next" suggestions then Calculated in milliseconds.

3. E-commerce

A good example it those online stores.
Recommendations-"People who bought this also bought that" is pure Python work.
The Dynamic pricing:Python changes prices in real time.When it's raining,umbrella prices go up.

4. Banking and fraud detection

When your bank notices a suspicious transaction or sends you an alert at 2 am,chances are Python caught it before you even noticed something was wrong.Python builds a "normal behavior profile" for your account by learning your spending habits like buying coffee at 8 AM,paying rent on the 1st,and never spending more than

Why You Should Start Learning Python

I will be honest with you,if you are curious about working with data,whether large or small,you should start learning Python as soop as possible.According to me,it's one of the best investments of time.Pythons is so simple and beginner-friendly and you can get so many sites that can teach you more about python.
It is also a high skill in demand.You can land to any job easily and set a career path.
You can also build real things quickly.You can load an excel file,clean it and create dashboards in a few minutes.YOU can even analyze your Spotify history.
So go ahead and Open your jupyter notebook,Write "Hello." Load a file,make a mess,break things and fix them.That's the whole journey.

conclusion

You don't need to be a "math person" or a "tech person" to learn Python for data analytics.Just be curious.Most data work is loading a file,cleaning the messy parts,making a few charts and telling someone what you found.This article has explained how you do it.

Introduction to Python

jayson kibet — Fri, 08 May 2026 10:16:38 +0000

The word “Python” might sound technical and complicated at first and many beginners assume that it will be difficult.I actually almost fell for that trap.Python was actually designed to be beginner-friendly.In fact,Python is widely considered one of the easiest programming languages because of its readable syntax.

What is Python

Python is a high-level programming language used in data analytics,web development,automation,AI and so many more fields.
It was created by Guido van Rossum and released in 1991.It was named after the British comedy show 'Monty Python's Flying Circus',not after the snake.He explained that he wanted something short,unique and slightly mysterious

Instillation

I will be giving steps in setting up a jupyter notebook.
Anaconda is an open source distribution of Python and R products so within anaconda is our jupyter notebook as well as a lot of other things but our point of interest is the jupyter notebook.

step 1

You will go to your browser and search'anaconda.com'(https://www.anaconda.com/) and hit the download option.You will select your device either windows,MacBook or linux.

step 2

Once the file is downloaded read the terms and conditions and any necessary information that pops up.

step 3

Select the instillation type either just for me(recommended) or all users(requires admin privileges)
It will then show you where its being installed in in your computer as an actual file path.

step 4

Click the windows key on your keyboard and you will see the anaconda navigator(app).You will then see all the distribution and R where are stored like the visual studio code,spyder,Rstudio and the jupyter notebook.

step 5

Since we want the jupyter notebook,click the launch button jupyter notebook.You'll be using that to save folders and organize your notebooks if you have previous projects.

step 6

Click the 'New'button,that will open up python 3 kernel.That is where you'll be writing all your codes.

Writing your first code

Almost every programmer starts with a simple program called 'hello world'.It's actualy a culture by now.You can use either single or double quotes.

The 'print'command displays the output of your code.You can write anything and use the command to display it as below

Types of data in Python

Different type of information is stored differently in python

1.string(str)

This stores words,characters or sentences.It is displayed using double or single quotes at the beginning and the end of that string.

2.Integer(int)

These are numbers.You don't use any quotes while defining an integer.These are also numbers without any decimal points.

3.Float

These are integers but with a decimal point.You don't use any quotes again in defining a float

4.Boolean

This is used to define either true or false.Commonly used in conditions.

5.List([])

A list stores more than one item in one variable.It is defined using the square brackets []

6.Dictionary({})

This is used to store information using keys and values.All keys and values that are strings should have quotes before and after.Only the integers dont use quotes.When you want to highlight the keys just write 'dictionary.keys()' and values 'dictiorary.values()

NOTE:A list[],you have privileges like you can add items,remove items and change items within the list. This privilege is limited in tuples().However,Tuples are quite faster and uses less memory than lists.

The PEP 8 rules in Python

PEP 8 rules are crucial in Python because they promote readable, consistent code that multiple developers can easily understand, reducing bugs and speeding up maintenance.Following PEP 8 rules makes your code professional.
1.Use 4 spaces to keep code clean and readable.
2.Keep lines under 79 characters for easier reading and better structure.
3.Leave 2 blank lines between functions and classes for clear separation.
4.Place imports at the top of the file and group them logically
5.Use snake_case for variables/functions and CamelCase for classes to stay consistent.
6.Add spaces around operators and after commas for readability and cleaner code style.(a = b + c)
7.Write comments with #
8.Use triple quotes ' ' ' to describe what functions,classes, or modules.

conclusion

Throughout this guide,we explored the basics of Python including data types,lists,tuples and PEP 8 rules.Python’s simple and readable syntax makes learning programming much easier for beginners.
Python is actually easier than I thought when I first started learning it.With regular practice,anyone can slowly build confidence and improve their programming skills.

SQL joins Explained

jayson kibet — Thu, 16 Apr 2026 15:17:45 +0000

Introduction

SQL JOINs are one of those concepts that seem simple in theory but confusing in practice.At first,SQL joins really confused me and didn't make any sense because i had the data i needed but in different tables.Putting them together actually felt harder than I thought.

1.Inner join

This join returns only the rows that match in both tables that you want to join.
The syntax is:select column
from t1
inner join t2
on t1.column=t2.column;

2.left join

LEFT JOIN returns all rows from the left table, plus any matching rows from the right.When there's no match,the result will be recorded as NULL.The order of the table in this join matters
the syntax is:select column
from t1
left join t2
on t1.column=t2.column

3.Right join

This is actually the opposite of the left join.It selects all rows from the right and matches them on the left.

4.full outer join

A Full outer join returns all rows from both tables.when it doesn't match, the missing side shows NULL.

5.self join

This is simply a table joining itself.Often when rows reference other rows in the same table.

Note:Always remember to put the ON function after the join

Window functions and Group by function

I once knew group by is a very powerful tool and could summarize my data in seconds and get results quickly until i came across the window functions and suddenly everything became a bit difficult.They almost did the same job but in a different way.

1.Group by

This command is used to combine rows into groups and return one result as per a group.You can group your results either by gender(female or male) or by age or any way you want your data to be presented.

Note:Do not include a where clause together with a group by function perhaps you can use a having clause to be more specific with your group by clause.

Window functions.

Window functions allow you to perform calculations across a two or more rows without removing any rows

Note:a.PARTITION BY divides the data into groups using PARTITION BY and ORDER BY specifies the order of rows within each group using ORDER BY.

1.sum()

In simple terms,the sum command creates a running total.Each row adds its value to the total so far.

2.avg()

Calculates the average value within a window.

3.Row_number()

This asigns a sequential number to each row.Every row gets a unique value.

4.Rank() and Dense rank()

when two rows have the same value RANK() and DENSE_RANK() both gives the same rank but they differ in what comes next
RANK() - It skips numbers after a tie , handles ties eg.1,2,3,3,5
DENSE_RANK() - It does not skip.However,the next rank after two tied at 2 is 3 eg.1,2,3,3,4,5.It doesnt leaves gaps in ranking

Functions every beginner should know

As a data analyst,you should go beyond just analyzing data and mastering basic commands like where function,group by and order by and so on.To become extraordinary,you need some powerful functions to help you analyze data with ease.

1.Lag()

This lets you access data from the previous row without writing complex queries.It looks at the previous row's value.

2.Lead()

This is the opposite of the lag function.It looks at the next row's value

NOTE:The Lag and Lead is perfect for making comparison over time either students performance in two exams or employees salary.

3.NTILE

This divides rows into equal groups(buckets)
It's useful for percentages,quartiles and even splitting students into perfomance bands according to their exams results.

It gives priority to the 1st groups.

4.substring

It selects the number of characters in a certain column.It helps in data cleaning and formating.

It wll select only the first 3 letters.

5.Date_part

This functions extracts either the year,month or day from a date

6.UNION

Despite UNION not being a function,i feel every beginner should master it.
It combines results from two queries and it also removes duplicates as a bonus.

common mistakes to avoid

1.running a delete or update statement

Running the delete or update statement can wipe your table at a glance.Always specify the row you want to update or delete by introducing the where clause.

2.Having and where clause

The where clause filters rows before the grouping happens.
Having filters after the aggregations.
Also,where clause does not go together with group by function.

3.Null values

Null is not the same as 0(zero).
Ignoring them leads to empty results.Always use IS NULL or IS NOT NULL.Don't use the =(equal) sign

4.Joins

When you run a join query,always include the word ON.This is where you want to join your table with common columns.

conclusion

We all learn by making mistakes.Every wrong query,every error message and every unexpected result is part of the process.Fixing them is what sharpens your thinking.The goal isn’t to write perfect queries from the start but understanding the structure of the queries that give the right answer to the question.

What really is DDL and DML and their comparision

jayson kibet — Sun, 12 Apr 2026 17:23:34 +0000

Introduction

About three weeks ago,I jumped into learning SQL.I thought it's all about writing queries and getting results by running them and thats all.Yes i had a point but when i went deeper in learning SQL,I realized there’s a clear split in how SQL actually works.DDL (Data Definition Language) and DML (Data Manipulation Language).

DDL(Data Definition Language)

This is what you use to create and define your database.It deals with the structure of your table.When you run them,the table either exists or it doesn't.You can think of it as an empty house(empty rooms just walls)

DML(Data Manipulation Language)

This actually works with the data inside your table.You can think of it as the staff inside your house.

NOTE:DDL deals with the structure while DML deals with the data.

How I used CREATE, INSERT, UPDATE, and DELETE

1.Create

This command creates the table of your title choice

2.Insert

This command allows you to fill your table by inserting data into the table you created following the data type strictly.

The data type should corespond with the data you put in the table you created.

3.Update

The update command allows you to make changes in your table for instance if you made a mistake and you need to make immediate changes.

4.Delete/drop

The 'delete' command allows you to eliminate a specific rows of your choice while alter messes with the columns.

Filtering and Where

The where clause is actually one of the most important commands.Its actually my 'best friend'since I interact with it a lot.Without this you cannot execute the delete and update command since it will affect every row.

1.=(equals to)

=(equals to) only filters the data corresponding to your where clause.

It will only highlight the form 4 students.

2.>=(greater than or equals to)

This only selects the data that is above or equal the highlighted command in the where clause.

3.Like

It is done by adding a percentage before of after a character to give a hint that there is some characters but it's just not given.

subjects like computer studies will be highlighted.

4.Between

This gives the data that is in the range that you highlighted

Marks like 70s will be included.

5.In

This matches the list.After the function'In',it is then followed by brackets and data separated by a comma in between them inside the bracket

Case when

This is a condition inside a query.It's almost similar to the 'if' statement in Excel and power BI.I didn't expect myself to like this as much as i do at the moment.

What i found challenging while attempting my assignment

In a spreadsheet, you click a cell and type. In SQL,you describe exactly what you want to change and under what conditions. The approach feels rigid at first, but it is actually more precise. You cannot accidentally drag a formula into the wrong column.
Also while doing the assignment,I ran an UPDATE without a WHERE clause.Every data in the table immediately became 'Unknown'.That mistake reinforced two habits: always try to always write the WHERE clause first before anything else and always work inside a transaction when experimenting.

Conclusion

The past few weeks taught me that SQL isn't about memorizing commands.First,you set up the structure with DDL,you then work inside it with DML.Think of it like building a house from scratch.At one point I ran an UPDATE without a WHERE clause and watched every single row change at once.That really got me frustrated.Now 'WHERE' and 'CASE WHEN' don't feel like random syntax anymore,they feel like tools I actually want to use.
If you're just starting out,always remember to write your conditions before your changes.

How to publish a Power BI report and embed it in a website.

jayson kibet — Sun, 05 Apr 2026 18:11:41 +0000

Introduction

Power BI is a powerful intelligence tool that enables users to transform raw data and generate insights and interactive dashboards and reports.One of its most useful features is the ability to publish reports to the Power BI Service and embed them into websites so that your work can reach your boss or client or add into your potfolio website.
This guide walks through the full process step by step: creating a workspace, publishing a report, generating embed code, and embedding the report into a website.

Step 1: Create a Workspace in Power BI Service

Before you can share anything,you need to put it somewhere.That space is called a workspace in power BI.In simple terms,a worksapce is like your report's new home.
It's designed mostly for power BI reports,dashboards and datasets.Each workspace can have its own set of people with different permission levels.
How to create:
1.open your web browser and go to app.powerbi.com. Sign in with your Microsoft account either work or school account.
2.Once you're logged in, look at the left-hand menu. You'll see icons for Home, Browse, Workspaces. Click Workspaces.At the bottom of the workspace list, you'll see a button that says + New workspace. Click it.

A window will pop up asking for some details.
.NAME:Put something clear and simple like"Electronics Sales Dashboard"
DESCRIPTION:Write something like "Monthly sales data for the electronics team"though its optional.
ADVANCED OPTION: This is where you set who can access and what license type to use
3.Click Apply.

The Four Workspace Roles
Power BI gives you four different levels of access.
Admin-He does Everything. Delete the workspace, add or remove people, change settings, upload reports — total control
Member-Edit and share content, but cannot add other members or delete the workspace
Contributor-Upload and edit reports, but cannot manage permissions or share with new people
Viewer Can only look at reports. No editing, no uploading, no sharing

step2. Uploading and Publishing Your Report

Once your workspace is ready, it's time to get your report from Power BI Desktop into the cloud.
Publishing from Power BI Desktop

Open your finished report in Power BI Desktop. 2.Save your file (Ctrl+S) 3.Click the Publish button on the Home ribbon at the top. 4.If asked, sign in with your Microsoft account. 5.A small window will pop up showing your available workspaces. Pick the one you just created. 6.Click Select.

7.Wait a moment while Power BI uploads and checks your file. This usually takes afew seconds close to a minutes, depending on how big and complex your report is.
8.When it's done, you'll see a success message. Click the link to open your report in Power BI Service (the cloud version).

Double-Check That Everything Worked

After publishing, go back to Power BI Service and click on your workspace. You should see three things:
The Report (your actual visuals and pages)
The Dataset (your data model)
Maybe a Dashboard if you made one

Click on the report name to open it. Click through every page. Test your slicers, your drill-throughs, and any custom visuals. Sometimes things look perfect in Desktop but act weird in the cloud.

step3.Creating the embed code

Power BI gives you three different ways to embed a report. Picking the wrong one can either break things or expose private data.

Publish to Web-Anyone with the link and it's Best for Personal blogs, public portfolios and marketing demos
Secure Embed-Only people with Power BI accounts you approve Pro for each viewer and it's best for Internal company portals, private team sites
Power BI Embedded-Any app user (no Power BI account needed) Azure subscription (paid) best for Customer-facing products, SaaS apps

Option A: Publish to Web (Public)

This is the easiest method and perfect for portfolio pieces or blog posts. But be aware that anyone on the internet can access and view your report. Never use this for customer data, salaries, or anything private.
Here's how to do it:

1.Open your published report in Power BI Service.
2.In the top menu, click File → Embed report → Publish to web (public).
3.Read the warning. Seriously. It's telling you that this report will be public and search engines can index it. Don't click past this without thinking.
4.Click Create embed code.
5.Click Publish to confirm.
6.You'll see two things: a Link (direct URL) and an HTML

That weird long string in the src part is your report's unique ID.

Option B: Secure Embed (For Internal Use)

This is best and highly recomended for private and sensitive data like employee salary and sales figures.
This is how you do it:
1.Open your report in Power BI Service.
2.Click File → Embed report → Website or portal.
3.Copy the URL and embed code provided.
4.Anyone who tries to view the embedded report will have to sign in with their Microsoft account. And they only see it if you've given them permission in the workspace.

Option C:Power BI Embedded (For Commercial Products)

This one is for developers building apps that show reports to customers. Think of a SaaS product that includes analytics for thousands of none users who have Power BI licenses. This requires Azure setup, API knowledge, and a paid subscription. If you're just trying to put a report on your blog or team site, ignore this option.

Step 3:Embedding the Report on a Website

You've got your embed code. Now let's put it somewhere people can actually see it.

Method 1: Simple HTML Page

2.Replace YOUR_REPORT_ID_HERE with the actual URL from your embed code
3.save the file as dashboard.html
4.Double-click the file to open it in your browser. You should see your live report on the page.

Method 2: WordPress

1.Add a Custom HTML block to your page or post.
2.Paste your iframe code inside.
3.Publish or update the page.

Method 3: Medium or Dev.to

These platforms don't allow raw iframe codes for security reasons. Your best bet is to host the HTML page and then share the link in your story.

Troubleshooting

When the Publish button is grayed out and won't let you click it,it means you're probably not signed in. Go to File → Sign in and log into your Microsoft account.
When you get an error,You probablly don't have permission to publish to this workspace.This is either the workspace admin hasn't given you access and you should reach out to them to add you as a Member or Contributor.
When the embedded report shows a blank screen or says "Access denied."For public embeds, your Power BI admin might have disabled "Publish to web" in the tenant settings.You should ask them to turn it on or for secure embeds, make sure the viewer has been added to the workspace.
When your report shows old data,it means your dataset probably isn't refreshing automatically. Check that scheduled refresh if it is turned on and that your data source credentials haven't expired. Expired passwords is the main reason reports stops updating.

Note:

Security should be the first priority always.
Do NOT use "Publish to web" for anything with personal information, customer data, financial details, or trade secrets. Once it's public, it's public forever. Google can find it. Your competitors can find it.

Conclusion

Publishing a Power BI report takes your work from a file stuck on your computer to a live tool that anyone can access. The whole process — workspace, upload, embed code, website takes few minutes once you understand the steps.
The real skill is knowing which embedding method fits your situation and keeping your data safe. Publish your report out and let your insights do the work.

Understanding Data modeling in power bi,Joins,relationships and schemas explained.

jayson kibet — Sun, 29 Mar 2026 18:38:47 +0000

Introduction

When I first heard about Power BI, I thought it's all about making charts and dashboards. I came to realize even the most beautiful visuals can produce wrong numbers when it's not well organised and most beginners start questioning themselves why their totals are not adding up correctly.

Data Modeling

Data modeling is organising and structuring your data so that it can be efficient and accurate for Power BI to easily analyse it.
In simple terms, modeling is what separates a report that looks good from what is actually correct. You can think of it like a building plan. You cannot skip the plan and expect the building to go well. If you skip data modeling, you will get inaccurate report.

Joins

Joins in Power BI combines data from multiple tables based on related columns. It uses relationships and DAX for joining data unlike SQL where you write join statements.

1. Inner Join

An inner join returns only the rows where both tables have a matching value and leaves out the ones that don't have a match. This join gives you a clean and trimmed result with no missing pieces on either side.

2. Outer Join

A full outer join returns all rows from both tables. Where there is a match, the columns are filled in from both sides. Where there is no match on either side, you get nulls.
You can only use it when you need a complete picture of both datasets.

3. Left Join

A left join returns all rows from the first table and the matching rows from the second table. If a row in the left table has no match in the right table, the right-side columns come back as null. It acts as 'don't leave anyone behind.'

4. Right Join

A right join is the exact opposite of the left join. It returns all rows from the right table and the matching rows from the left table. Unmatched rows on the left side come back as null.

5. Right Anti Join

This join keeps only the rows from the right table that don't match anything on the left. Commonly used to find new data on the second table that don't match with the main dataset.

6. Left Anti Join

A left anti join returns only the rows from the left table that have no matching rows in the right table. Used to find the 'what's missing.'

Power BI Relationships

Power BI understands how tables get connected.When you connect two tables in Power BI, that connection is called a relationship.

1. Cardinality

Cardinality describes how many rows on each table can merge. It helps you understand the structure of your data and how to join the tables perfectly.

a. 1:1

This matches one-to-one whereby each row in table A matches exactly one row in table B.

b. 1:M

In this case, one row in table A matches many rows in table B. They are most efficient and predictable in Power BI and I recommend to always aim for 1:M.

c. M:M

Multiple rows in table A can match multiple rows in table B.
This relationship can cause unexpected filter behaviour, double counting, and you can resolve it by introducing a bridge table.

d. M:1

This relationship, many rows in table A matches to 1 row in table B.

Active vs Inactive

Tables can be connected in different ways when building data models.
An active relationship is always the default and always on between two tables and appears as a solid line in model view. Every DAX and every visual in your report uses the active relationship automatically.
Inactive relationship is a stand-by connection ignored by default. You can create it only when you need a second or third path between two tables.

Schemas

Schemas simply is the structure of your data model. It shows how tables are arranged and connected to each other. You can think of it like a map to your data.

1. Star Schemas

This is the recommended standard for Power BI.
It takes one flat table and splits it into separate, clean tables, one fact table in the middle surrounded by dimension tables hence resembling a star.

2. Snowflake Schema

This is basically like a star schema where some of the dimension tables are broken down further into smaller tables.

Conclusion

I used to create dashboards that had weird numbers and calculations that I couldn't even explain. Data modeling made Power BI make sense to me and once the model was right, everything started to fall in place. Once you understand joins, relationships and schemas, your reports start telling accurate story and not just creating beautiful visuals.

How Excel is Used in Real-World Data Analysis

jayson kibet — Wed, 25 Mar 2026 14:02:05 +0000

INTRODUCTION

I used to think Excel is a boring spreadsheet full of rows and columns only used by accountants and nobody cared about it. Everything changed the moment I jumped into data analytics. I came to realize that Excel really is a spreadsheet software developed by Microsoft that allows you to make calculations and analyse data. It is also the most powerful tool in data analytics. Excel is used in almost every industry you can think of.

Real World Use Cases of Excel

1. Education Institutions

Excel is used to monitor the trend in performance of students in schools by calculating their score, the mean mark and highlighting the top performers.

2. Business

Excel is used by most companies and firms to track down their sales, profits and losses. It can also show their product's performance in various tables and charts.

3. Healthcare

Health institutions use Excel to monitor their patients and keep their track records. It can also calculate the number of patients and medicines distributed within the health facility.

4. E-Commerce

A major example is Jumia. It uses Excel to calculate discounts distributed among different products and identify the most sold products.

Key Formulas in Excel

Excel is run by different formulas.

1. IF Statement

One of the most effective formulas is the IF statement. This gives a specific command. For example:
=IF(A2<30,"young","old")
This statement tells us that in cell A2, if its value is less than 30, then it should be classified as young and the rest should be old.

2. SUM Statement

The SUM statement adds up the highlighted column. For example:
=SUM(A2:A20)
This command sums up the values from the second row up to the twentieth row.

3. VLOOKUP

VLOOKUP searches for a value in one column and returns a matching value from another column: =VLOOKUP(A2,product_table,2,FALSE)
This formula searches for the value in cell A2 in the first column of the table and returns the corresponding value from the second column.

Data Cleaning

Before analysing data, you ought to clean it for easier analysis. One of the major ways of cleaning is by removing duplicates. You can also replace some values by simply pressing Ctrl+H. This shortcut allows you to find a value and replace it.
Another common formula is TRIM.
This special command eliminates unnecessary spaces before, after and between words inside the cells. Example:
=TRIM(A2)
It eliminates unnecessary spaces in cell A2.

Sorting and Conditional Formatting

Sorting allows you to arrange the selected column in either ascending or descending order, whereas conditional formatting allows you to change the colour of the highlighted cell depending on what you want to highlight. It makes it easier for you to scan and spot patterns in large data instead of going through every row.

Data Visualization

Excel goes beyond calculations and writing formulas.

1. Pivot Tables

Pivot tables in Excel summarise data in seconds without writing formulas and making calculations, simply by dragging and dropping the columns you want to analyse.

2. Charts and Graphs

Excel allows you to present your data in the form of graphs and charts for easy understanding. You can also use a pie chart to show how various parts make a whole.

Conclusion

At first, I underestimated Excel, but learning it has completely changed how I see data. It is now one of the first tools I would recommend to anyone starting in data analytics.