DEV Community: Josephine Mackylah

From Local to Cloud: Lessons I Learned Connecting Power BI to SQL Databases

Josephine Mackylah — Tue, 14 Jul 2026 15:38:02 +0000

Introduction.

Data is only as valuable as the insights it provides. Before creating dashboards or writing DAX measures in Power BI, you must first establish a secure connection to your data source. In today's environments, that often means connecting to both on premises SQL Servers and cloud hosted databases, each with its own security requirements. This guide walks you through securely connecting Power BI Desktop to local and cloud SQL databases, including configuring SSL certificates for cloud connections.

Understanding Data Connectivity Modes.

Before building dashboards in Power BI, I learnt that the most important decisions is choosing how Power BI will connect and interact with my SQL database.
Power BI mainly provides two connectivity modes:

Import Mode loads a snapshot of your SQL data into Power BI’s memory. This delivers fast performance and allows you to take full advantage of Power BI’s data modeling and DAX capabilities. However, because the data is stored as a snapshot, regular refreshes are required to keep reports updated.
Direct Query keeps the data within the SQL database and sends live queries whenever users interact with a report. This is useful for very large datasets or situations requiring frequent updates. However, performance depends heavily on the capacity and speed of the underlying database.

Connecting Power BI to a Local SQL Database

When working with a local SQL database, the connection process is usually straightforward because the data environment is within your machine. Before connecting, ensure you have the SQL Server details, database access permissions, and the appropriate authentication credentials.

Gathering Your Database Connection Details

Before connecting Power BI to SQL database, collect the key connection details from the database management tool (such as SSMS or pgAdmin).

You will need:
Server Name: The location or address of your database server (for example, localhost, or an internal network address).
Database Name: The specific database containing the tables you want to analyze.
Authentication Method: The credentials required to access the database, such as Windows/Active Directory authentication or a database username and password.

Initializing the Database Connection

To begin connecting Power BI to a database, open Power BI Desktop and navigate to the Home tab.
Select Get Data, choose More to explore available data sources, then search for a database type (such as SQL Server or PostgreSQL).
Select the appropriate connector and click Connect to start the setup process.
In the database setup window, enter a Server pathway and a Database name.
Select Import and then Click OK.

Authenticate and Load Your Data

A credential prompt will appear. If a database utilizes corporate single sign-on, select Windows -> Use my current credentials. For dedicated SQL logins, select the Database tab and manually type a database user credentials.
Click Connect. If prompted with an unencrypted connection warning, click OK or Run (safe for internal testing environments).
The Navigator window will open. Check the boxes next to the tables you wish to pull, and click Load to commit them to a workspace.

Connecting Power BI to a Cloud SQL Database (Aiven)

Cloud databases such as Aiven PostgreSQL provide scalability and flexibility, but connecting to them requires additional security considerations. Since these databases are accessed over the internet, they rely on encrypted connections to protect data during transmission.
Power BI requires a trusted and secure connection before accessing cloud databases. This means a local machine must recognize and trust the cloud provider’s Certificate Authority (CA) to verify the database server’s identity and establish a secure connection.

Extract Connection Properties from Aiven

Step 1:

Download the CA Certificate, Look near the bottom of your image in the Connection information block. Locate the row labeled CA certificate.
Click the small Download icon (the arrow pointing down into a tray) located on the far-right side of that row.
Save the file to a secure folder on your computer (it will be downloaded as a file typically named ca.pem).

Download and Convert the SSL Certificate

Step 2:

Unhide and Copy the Database Credentials. You will also need the correct login credentials shown on this screen to fix the password error from your earlier.
Click the Eye icon next to the asterisks in the Password row to reveal your actual password. Copy it.
Note the User listed right above it is avnadmin (not postgres like you had earlier).Note the custom Port is 14306 (not 5432).

As a prerequisite, confirm that the PostgreSQL database is available by connecting to it through DBeaver before attempting to connect with Power BI.

Select the dataset you want to import. If your data has already been loaded into the PostgreSQL database, click Load to import the data into Power BI Desktop, and then proceed with data cleaning, data modeling, and dashboarding.

Use the troubleshooting below to identify common connection errors, understand their causes, and implement the appropriate solution.

1. Error Message / Symptom The remote certificate is invalid according to the validation procedure." Root Cause Analysis The Aiven SSL certificate was installed under the Current User certificate store, or it was not registered correctly with the system Corrective Action Re-run Phase 2, Step 2.3. Ensure you install the certificate under Local Machine and place it in the Trusted Root Certification Authorities store.

2.Error Message / Symptom Connection Timeout or "Unable to connect to the remote server" Root Cause Analysis A local or corporate firewall is blocking outbound network traffic on Aiven's assigned port. Corrective Action Contact your network administrator or IT department to whitelist the outbound traffic for your specific Aiven port, as cloud databases typically use non-standard ports.

3. Error Message / Symptom An error occurred while reading data from the provider: 'Invalid password Root Cause Analysis Incorrect login credentials or cached credentials stored in Power BI's credential manager. Corrective Action In Power BI Desktop, go to File →Options and settings → Data source settings. Select your database connection, click Clear Permissions, and reconnect using your correct cloud database credentials.

Final Thoughts and Key Takeaways

-It is easy to get frustrated when a connection gets blocked, but cloud environments like Aiven enforce strict SSL encryption for a reason to keep your data safe. Once you know how to navigate the handshake, it becomes a routine setup rather than a roadblock.

-Remember that Power BI doesn't validate certificates on its own it relies entirely on your local Windows operating system. If you take the extra ten seconds to correctly drop your certificate into the Local Machine's Trusted Root folder, Windows handles the trust automatically behind the scenes.

-The best part about this configuration is that it is a one-time task. Once that secure pipeline is locked in place, you can completely forget about ports and protocols and put 100% of your focus where it belongs: writing clean DAX and designing impactful dashboards.

The First Thing Power BI Taught Me Had Nothing to Do with Dashboards

Josephine Mackylah — Sun, 28 Jun 2026 22:22:11 +0000

When I decided to learn Power BI, I had one picture in my mind, beautiful dashboards.
I imagined creating interactive reports with colorful charts, KPIs, and slicers that would impress anyone who looked at them. Like many beginners, I thought the magic of Power BI started with visualization.
I couldn't have been more wrong.
The first thing Power BI taught me had nothing to do with dashboards.
It taught me that before you can tell a story with data, you must first clean the data.

My First Surprise

On my very first day, I expected to import data and immediately start building charts. I imagined dashboards coming together within minutes.
Instead, I found myself spending most of my time in Power Query, cleaning and preparing the data before I could do anything visual.
At first, I was confused. I kept asking myself: "Why am I spending so much time cleaning data? I thought Power BI was about building dashboards."
But when I looked more closely at the dataset, everything started to make sense.
I noticed something simple, yet very important.
Some names that were meant to represent the same person were written in different ways:

- Josephine
- josephine
- JOSEPHINE

To a person, these are clearly the same name.
But to Power BI, they are treated as three completely different values.

I also discovered several data quality issues that I hadn’t expected.
There were empty cells where important information was missing, and some text columns had things like errors, null and N/A, which I had to standardize by replacing them with consistent values such as Unknown, Not Provided, depending on the context and data type. For example, I used Unknown or Not Provided for text fields, and handled numeric fields differently by replacing error cells with null since Power BI recognizes null as a special value that represents missing or undefined datas to ensure they remained valid for calculations.

I also encountered duplicate records, which inflated totals and could easily lead to misleading results if not corrected.
Again, some dates 01/02/2026 were stored as text instead of proper date formats, making it impossible to perform time-based analysis until they were converted correctly and also IDs were treated as numbers when they should be text.

I found extra spaces and inconsistent formatting, which made identical values appear different and affected grouping and filtering. For example, "Uganda" vs "Uganda " You can say Some values looked identical but weren’t—hidden spaces made Power BI treat them as different entries.
I also realized that the dataset had incorrect data types, where text, numeric, and date fields were not properly assigned. This meant some columns that should have supported calculations were treated as plain text, while others that should have been categorical or date-based were misinterpreted, affecting accuracy and performance.

Then Came My Next Challenge: DAX

After spending time cleaning and preparing the data, I thought I was finally ready to build visuals.
But Power BI had another lesson waiting for me. This is where I met something called DAX (Data Analysis Expressions).
At first, it looked simple just formulas, right? I quickly realized it was not that simple.
DAX wasn’t just about writing calculations. It was about thinking in logic and asking the right questions of my data.

Instead of just “adding numbers,” I had to start thinking like an analyst:

- What is the total cost of production?
- What is the total profit value?
- What is the total revenue?
- What is the total planted area?

Suddenly, I wasn’t just clicking around Power BI anymore, I was reasoning with data.

My First DAX Moments

My first simple functions felt like a breakthrough:
SUM() to calculate totals
AVERAGE() to find mean values
COUNT() and DISTINCTCOUNT() to understand records
Even though they looked basic, they opened a new way of thinking.
I remember realizing something important:
Cleaning data prepares it.
DAX gives it meaning.

My First Encounter with Logical Functions in DAX

This is where Power BI started to feel less like a tool and more like a way of thinking.
Logical functions are what allow Power BI to make decisions based on conditions. Instead of just calculating values, I could now tell Power BI:

- “If this condition is true, do this…”
- “If not, do something else…”
The first logical function I used was IF().
It helped me answer simple questions like:
If a value is above 50, return “Pass”
Otherwise, return “Fail”
Result = IF([Score] >= 50, "Pass", "Fail")

Then things got more interesting with nestedIFs.
Instead of just two outcomes, I could handle multiple categories and also take care of the blanks:
Excellent Good Average Poor
Forexample;
Grade =IF([Score] >= 80, "Excellent",IF([Score] >= 60, "Good",IF([Score] >= 50, "Average", "Poor") At this point, I realized

Data analysis is often about classification, not just numbers.

One of the biggest challenges I faced was missing data.
That’s where ISBLANK() came in handy.
Example:
Revenue Category Nested IF =IF(ISBLANK('Kenya_Crops_Dataset'[Revenue (KES)]),"Not Provided",IF('Kenya_Crops_Dataset'[Revenue (KES)] > 500000,"High Revenue",IF('Kenya_Crops_Dataset'[Revenue (KES)] > 100000, "Medium Revenue",IF('Kenya_Crops_Dataset'[Revenue (KES)] > 0,"Low Revenue","Not Provided"))))
This helped me handle empty values instead of ignoring them.
I learned that missing data is still data you just need to label it properly.

Sometimes one condition was not enough. So I learned how to combine conditions: AND/OR Example:
High Revenue and Profitable =IF(AND('Kenya_Crops_Dataset'[Revenue (KES)] > 100000,'Kenya_Crops_Dataset'[Profit (KES)] > 0),"High Revenu Profitable Farm","Other Farm")

Maize or Rice = IF(OR('Kenya_Crops_Dataset'[Crop Type]"Maize",'Kenya_Crops_Dataset'[Crop Type] = "Rice"),"Priority Crop","Other Crop")
I realized something that Logical functions don’t just process data they simulate thinking and they made me think like an analyst, not just a tool user.

This is just the begining

As I look back at my first steps in Power BI, I realize something important.
I didn’t start by building dashboards. I started by cleaning messy data, fixing inconsistencies, and learning how to think logically about information.
Then came DAX where numbers stopped being just numbers and started becoming decisions. And finally, logical functions taught me that data is not just something you display, but something you interpret.
What I thought would be a journey about visuals turned out to be a journey about thinking.
And I am still at the beginning. Every dataset I touch now reminds me of one thing, before insights, there is understanding. Before understanding, there is preparation.
Power BI is not just teaching me how to analyze data it is teaching me how to see it differently.

How Excel is Used in Real-World Data Analysis.

Josephine Mackylah — Sun, 28 Jun 2026 19:12:51 +0000

Before dashboards light up with insights, before SQL queries pull records from databases, and before machine learning models make predictions, most data begins its journey in a spreadsheet. For decades, Microsoft Excel has been the tool that professionals rely on to collect, organize, clean, and analyze information. From tracking hospital records and managing business sales to monitoring project performance and preparing reports, Excel remains one of the most widely used tools in the world of data analysis.
As I began my journey in Data Science and Analytics, I quickly realized that Excel is far more than a simple spreadsheet application. It is often the first place where raw data is transformed into meaningful information. In this article, I explore how Excel is used in real-world data analysis and what I have learnt this week.

What is Excel?

Microsoft Excel is a spreadsheet application that organises data into rows and columns inside a grid called a worksheet. Each cell in that grid can hold text, numbers, dates, or crucially, formulas that compute values dynamically based on other cells.

Understanding Excel's Building Blocks

A workbook is the entire Excel file that contains one or more worksheets. Think of it as a digital binder used to store related data and analyses in a single file.
A worksheet, commonly called a sheet, is an individual page within a workbook where data is entered and managed. A workbook can contain multiple worksheets for organizing different sets of information.
A row is a horizontal line of cells identified by numbers (1, 2, 3, and so on). Rows are typically used to store individual records or observations.
A column is a vertical line of cells identified by letters (A, B, C, and so on). Columns are usually used to store specific categories or variables of data.
A cell is the intersection of a row and a column. It is the smallest unit in a worksheet and serves as the location where data is entered. Each cell has a unique reference, such as A1 or B5.
A range is a group of two or more cells selected together. Ranges are commonly used when performing calculations, formatting data, or applying formulas. For example, A1 represents a range containing multiple rows and columns.

A Critical Step in Analysis that i learnt is Data cleaning.

One of the most valuable lessons I learned during my first week with Excel is that the quality of an analysis depends heavily on the quality of the data being analyzed. Even the most advanced analytical techniques can produce misleading results if the underlying data is inaccurate or incomplete.

In real-world scenarios, datasets often contain duplicate records, missing values, inconsistent formatting, extra spaces, and incorrect entries. These issues can lead to errors in calculations, inaccurate reports, and poor decision-making.

Excel provides several tools that help address these challenges, including Remove Duplicates, text functions such as TRIM() and PROPER(), and data validation features that improve data accuracy and consistency. By cleaning and preparing data before analysis, analysts can ensure that their findings are reliable and meaningful.

Function Purpose Examples I learnt.

UPPER() Converts text to uppercase =UPPER("jose") → JOSE
LOWER() Converts text to lowercase =LOWER("JOSE") → jose
PROPER() Capitalizes first letter of each
word =PROPER("jose macky") → Jose Macky
TRIM() Removes extra spaces from
text =TRIM(" Jose Macky ") → Jose Macky
LEFT() Extracts leftmost characters =LEFT("Jose", 2) → Jo
RIGHT() Extracts rightmost characters =RIGHT("Jose", 2) → hn
MID() Extracts characters from the
middle =MID("Jose", 2, 2) → oh
LEN() Returns length of text =LEN("Jose") → 4
FIND() Finds position of a substring
(case-sensitive) =FIND("o", "Jose") → 3
SUBSTITUTE() Replaces text within a string
=SUBSTITUTE("Jose Macky", "Macky",
"Smith") → Jose Smith

I also learnt Why Excel Matters in Data Analysis

Data analysis is about turning raw data into meaningful insights that support better decision-making. However, before any patterns can be identified or trends discovered, data must first be collected, organized, and prepared. This is where Excel plays a crucial role.

Excel serves as the foundation for many data analysis workflows because it provides a simple yet powerful environment for managing data. It enables users to organize information into structured tables, perform calculations automatically, clean and validate datasets, and quickly sort or filter records to focus on relevant information. These capabilities allow analysts to transform raw data into accurate, reliable, and actionable insights.

Whether tracking sales performance, managing patient records, analyzing survey responses, or monitoring project indicators, Excel remains one of the most widely used tools for preparing data for analysis and informed decision-making.

Organizing Data for Better Insights

A key takeaway from my first week of learning Excel is that effective data analysis begins with proper data organization. Excel structures information into rows and columns, creating datasets that are easy to understand, manage, and analyze.
For example, a retail company might use Excel to record and organize sales transactions as shown below:

Product     Quantity Sold   Unit Price   Sales Date
Laptop             10           $500          01/06/2026
Mouse              25           $15        02/06/2026

With data organized in this format, it becomes much easier to calculate revenue, identify best selling products, track sales trends over time, and generate reports that support business decision-making. This demonstrates how Excel's structured layout transforms raw data into information that can be analyzed effectively.

Sorting and Filtering for Better Insights

As datasets grow larger, finding specific information manually becomes increasingly difficult and time consuming. This is where Excel's sorting and filtering features become valuable tools for data analysis.

Sorting allows users to arrange data in a meaningful order, such as ranking student scores from highest to lowest or organizing products by price. Filtering, on the other hand, enables users to display only the records that meet specific criteria while temporarily hiding the rest.

For example, a school administrator can sort examination scores to quickly identify top-performing students, while a sales manager can filter transactions to view sales from a particular region or month. These features make it easier to explore data, identify trends, and focus on relevant information without modifying the original dataset.

Using Formulas and Functions to Automate Analysis

One of Excel’s most powerful capabilities is its ability to automate calculations using formulas. Instead of relying on manual calculations or external tools, Excel allows users to build dynamic formulas that automatically update whenever the underlying data changes.

This makes it especially useful in real-world scenarios such as calculating monthly expenses, determining employee salaries, computing student performance averages, or estimating project costs. For example, a simple formula like:

=A1+B1
adds the values in two cells and instantly updates the result whenever either value changes. This dynamic behavior reduces manual effort and minimizes the risk of errors.

Beyond basic formulas, Excel also provides built-in functions that simplify and speed up data analysis.

Some of the most commonly used functions include:

SUM() – Adds a range of values
Example: =SUM(B2)
AVERAGE() – Calculates the mean of a dataset
Example: =AVERAGE(B2)
MAX() – Returns the highest value in a range
Example: =MAX(B2)
MIN() – Returns the lowest value in a range
Example: =MIN(B2)
COUNT() – Counts the number of numeric entries in a range
Example: =COUNT(B2)

These functions are widely used in data analysis to quickly summarize large datasets and uncover key trends. They help analysts move from raw numbers to meaningful insights in a matter of seconds.

Real-World Applications of Excel in Different Industries

In the business and sales sector, companies use Excel to track sales performance, monitor inventory levels, analyze customer trends, and prepare financial reports. These activities help organizations understand their performance and make informed decisions that improve efficiency and profitability.
In healthcare, Excel plays an important role in managing and analyzing data. Hospitals and health programs use it to track patient information, monitor disease trends, generate monthly reports, and support monitoring and evaluation activities. This helps health professionals maintain accurate records and improve service delivery.
In education, schools and academic institutions rely on Excel to record student performance, calculate grades, track attendance, and generate academic reports. This simplifies administrative work and ensures that student data is well-organized and easy to access.
In the agriculture sector, Excel is used to monitor crop yields, analyze farmer productivity, track training attendance, and manage project performance indicators. These insights help agricultural organizations improve planning and support farmers more effectively.
In monitoring and evaluation, Excel is a key tool for data management and reporting. It is used to clean survey datasets, calculate indicators, track project progress, and produce donor reports. This ensures that program data is accurate, consistent, and useful for decision making.

Personal Reflection.

Before learning Excel, I viewed data as something abstract—hidden inside systems and far removed from everyday understanding. After working with Excel, that perception changed. Data became something tangible, something I could explore, manipulate, and question directly.

As I began to use Excel more deeply, I realized that data is not just passive information sitting in a system. It is something that can be explored and interrogated. Even simple analyses, such as using a SUMIFS formula to answer a question like how many projects in Berlin, handled by employees above 30 years, were completed, helped me see data in a new light. It became clear that data carries patterns, tells stories, and can respond when the right questions are asked.

What also stood out to me was how much of data work is actually about preparation rather than analysis. Before this learning experience, I assumed data analysts spent most of their time discovering insights and producing visualizations. However, I have come to understand that a large part of the process involves cleaning and preparing data—removing duplicates, correcting inconsistencies, standardizing formats, and ensuring accuracy using tools like TRIM and SUBSTITUTE.

Another powerful feature that stood out to me in Excel is Conditional Formatting. This tool allows data to visually communicate its meaning by automatically highlighting values based on specific rules. For example, high-performing values can be marked in green, while low or concerning values can appear in red. This makes it much easier to quickly interpret patterns, detect outliers, and understand performance trends without going through every single value manually. It showed me how Excel is not just about calculations, but also about turning data into something visually meaningful and easier to interpret.

Conclusion

My first week of learning Excel has shown me that data analysis starts with strong data foundations. Before advanced tools and complex models come into play, it is Excel that provides the essential skills for organizing, cleaning, validating, and analyzing data.
Through this learning journey, I have gained practical skills in formatting, sorting, filtering, data validation, formulas, and functionstools that are widely used across industries every day. More importantly, I have learned that the quality of any analysis depends on how well the data is managed from the start.
Excel is not just a spreadsheet tool, it is the foundation of effective data analysis. As I continue my journey in Data Science and Analytics, I now understand how powerful Excel is in transforming raw data into meaningful insights that support better decision making.

Understanding Excel in Real-World Data Analysis: My First Week of Learning.

Josephine Mackylah — Sun, 07 Jun 2026 12:16:49 +0000

What is Excel?

Understanding Excel’s Structure

I have learnt that Excel is built on a simple but powerful structure that makes it easy to organize and analyze data effectively.

A workbook is the entire Excel file. It acts like a digital binder that can contain one or more worksheets, allowing users to store related datasets and analyses in a single place.

A worksheet, often called a sheet, is an individual page within a workbook where data is entered, organized, and analyzed. A single workbook can contain multiple worksheets, each used for different types of information.

Data in a worksheet is arranged in rows and columns. Rows are horizontal lines identified by numbers and are typically used to represent individual records or observations. Columns are vertical lines identified by letters and are used to store specific categories or variables.

A cell is the smallest unit in a worksheet, formed by the intersection of a row and a column. It is where data is entered, and each cell has a unique address such as A1 or B5. The Name Box in Excel displays this cell address, helping users quickly identify locations within a dataset.

A range refers to a selection of two or more cells, often used for calculations, formatting, or analysis. For example, A1 represents a range of cells spanning multiple rows within a single column, allowing users to perform operations on grouped data efficiently.

Different types of data in excel.

One of the important lessons I learned while working with Excel is that data comes in different types, and understanding these types is essential for proper analysis. Each type of data behaves differently and determines how it can be used in calculations, sorting, and reporting.

Text data, also known as labels, includes words, names, and categories. This type of data is mainly used for identification and description rather than calculations. Examples include product names such as Laptop or Mouse, employee names, or locations like Kampala or Berlin.
Numeric data represents values that can be used in mathematical operations. This includes numbers such as sales amounts, quantities, and prices. Because numeric data supports calculations, it is the foundation for formulas like sums, averages, and totals in Excel.
Date and time data represent specific points or periods in time. Examples include dates like 01/06/2026 or timestamps such as 10:30 AM. This type of data is particularly useful for analyzing trends over time, such as monthly sales performance or project timelines.
Currency data is a special type of numeric data used to represent money values. It is commonly used in business and financial analysis to track sales, expenses, profits, and budgets. For example, values like $500 or UGX 1,200,000 are treated as currency to ensure clarity and consistency in financial reporting.
Percentage data represents values out of 100 and is often used to show proportions, rates, or changes. Examples include discount rates, growth percentages, and performance indicators such as 15% increase in sales. This type of data is especially useful for comparing performance across categories.

Basic Arithmetic Operations in Excel.

One of the most interesting things I discovered in Excel is that behind every calculation is a simple set of operations that behave almost like building blocks of analysis. At first glance, they may look basic, but together they power almost every financial report, sales summary, and analytical insight.

It starts with addition, where Excel quietly does the work of bringing numbers together. Instead of manually calculating totals, a formula like =A1+B1 instantly combines values, whether it is daily sales, expenses, or quantities.
Then comes subtraction, which helps uncover differences. With something as simple as =A1-B1, Excel can show profit after expenses, remaining stock, or performance gaps—turning raw numbers into meaningful comparisons.
Multiplication takes things a step further. It allows Excel to scale values, such as calculating total cost by multiplying price and quantity using =A1*B1. This is where simple data begins to feel like real-world business analysis.
With division, Excel helps break things down into understandable units. A formula like =A1/B1 can show averages per item, cost per unit, or ratios that reveal deeper insights hidden within the data.
Finally, there is exponentiation, represented by the ^ symbol. Something like =A1^2 may seem simple, but it plays an important role in more advanced analysis such as growth trends, projections, and statistical calculations.

Excel Shortcuts That Make Work Feel Effortless.

Instead of manually copying and pasting data, a quick Ctrl + C followed by Ctrl + V feels like second nature once you get used to it. And when mistakes happen as they always do in data work Ctrl + Z becomes a lifesaver, allowing you to instantly undo an action and recover your work.

As datasets grow larger, navigation becomes important. That is where Ctrl + Arrow Keys comes in, allowing you to jump across thousands of rows or columns in seconds instead of scrolling endlessly.

When working with structured data, turning on filters is a common task. With just Ctrl + Shift + L, you can instantly activate filtering and start slicing through data to focus only on what matters.

Formatting also becomes much easier with Ctrl + 1, which opens the formatting menu where you can adjust number formats, alignment, and borders to make data clearer and more professional.

Ctrl + A, which allows you to instantly select all the data in a worksheet or a specific data range. Instead of manually dragging your mouse across rows and columns, this shortcut highlights everything with a single action.

A Critical Step in Analysis that I learnt is Data cleaning.

Function Purpose Examples I learnt.

UPPER() Converts text to uppercase =UPPER("jose") → JOSE
LOWER() Converts text to lowercase =LOWER("JOSE") → jose
PROPER() Capitalizes first letter of each
word =PROPER("jose macky") → Jose Macky
TRIM() Removes extra spaces from
text =TRIM(" Jose Macky ") → Jose Macky
LEFT() Extracts leftmost characters =LEFT("Jose", 2) → Jo
RIGHT() Extracts rightmost characters =RIGHT("Jose", 2) → hn
MID() Extracts characters from the
middle =MID("Jose", 2, 2) → oh
LEN() Returns length of text =LEN("Jose") → 4
FIND() Finds position of a substring
(case-sensitive) =FIND("o", "Jose") → 3
SUBSTITUTE() Replaces text within a string
=SUBSTITUTE("Jose Macky", "Macky",
"Smith") → Jose Smith

I also learnt Why Excel Matters in Data Analysis

Organizing Data for Better Insights

Product     Quantity Sold   Unit Price   Sales Date
Laptop             10           $500          01/06/2026
Mouse              25           $15        02/06/2026

Sorting and Filtering for Better Insights

Using Formulas and Functions to Automate Analysis

=A1+B1
adds the values in two cells and instantly updates the result whenever either value changes. This dynamic behavior reduces manual effort and minimizes the risk of errors.

Beyond basic formulas, Excel also provides built-in functions that simplify and speed up data analysis.

Some of the most commonly used functions include:

SUM() – Adds a range of values
Example: =SUM(B2)
AVERAGE() – Calculates the mean of a dataset
Example: =AVERAGE(B2)
MAX() – Returns the highest value in a range
Example: =MAX(B2)
MIN() – Returns the lowest value in a range
Example: =MIN(B2)
COUNT() – Counts the number of numeric entries in a range
Example: =COUNT(B2)
SUMIFS() – A function that adds values based on multiple conditions.
AVERAGEIFS() – A function that calculates the average of values based on multiple conditions.

These functions are widely used in data analysis to quickly summarize large datasets and uncover key trends. They help analysts move from raw numbers to meaningful insights in a matter of seconds.

Date and Time Functions in Excel

TODAY() → =TODAY() → returns: 07/06/2026 NOW() → =NOW() → returns: 07/06/2026 14:35 DATE() → =DATE(2026,6,7) → returns: 07/06/2026 TIME() → =TIME(14,30,0) → returns: 14:30:00 DAY() → =DAY("07/06/2026") → returns: 7 MONTH() → =MONTH("07/06/2026") → returns: 6 YEAR() → =YEAR("07/06/2026") → returns: 2026 HOUR() → =HOUR("14:45:00") → returns: 14 MINUTE() → =MINUTE("14:45:00") → returns: 45 SECOND() → =SECOND("14:45:30") → returns: 30 DAYS() → =DAYS("10/06/2026","01/06/2026") → returns: 9 NETWORKDAYS() → =NETWORKDAYS("01/06/2026","10/06/2026") → returns: 8 EDATE() → =EDATE("01/06/2026",2) → returns: 01/08/2026

Real-World Applications of Excel in Different Industries

In the business and sales sector, companies use Excel to track sales performance, monitor inventory levels, analyze customer trends, and prepare financial reports. These activities help organizations understand their performance and make informed decisions that improve efficiency and profitability.
In healthcare, Excel plays an important role in managing and analyzing data. Hospitals and health programs use it to track patient information, monitor disease trends, generate monthly reports, and support monitoring and evaluation activities. This helps health professionals maintain accurate records and improve service delivery.
In education, schools and academic institutions rely on Excel to record student performance, calculate grades, track attendance, and generate academic reports. This simplifies administrative work and ensures that student data is well-organized and easy to access.
In the agriculture sector, Excel is used to monitor crop yields, analyze farmer productivity, track training attendance, and manage project performance indicators. These insights help agricultural organizations improve planning and support farmers more effectively.
In monitoring and evaluation, Excel is a key tool for data management and reporting. It is used to clean survey datasets, calculate indicators, track project progress, and produce donor reports. This ensures that program data is accurate, consistent, and useful for decision making.

Personal Reflection.

Before learning Excel, I viewed data as something abstract hidden inside systems and far removed from everyday understanding. After working with Excel, that perception changed. Data became something tangible, something I could explore, manipulate, and question directly.

What also stood out to me was how much of data work is actually about preparation rather than analysis. Before this learning experience, I assumed data analysts spent most of their time discovering insights and producing visualizations. However, I have come to understand that a large part of the process involves cleaning and preparing data removing duplicates, correcting inconsistencies, standardizing formats, and ensuring accuracy using tools like TRIM and SUBSTITUTE.

And lastly, Data Validation in Excel is one of those features that controls what data can be entered into a cell to ensure accuracy and consistency. It helps prevent errors by setting rules such as allowing only numbers within a range, specific dates, or predefined list values. A common use is dropdown lists, which reduce typing mistakes and maintain uniform data entry, thus improving data quality by ensuring that only valid and reliable information is captured from the start.

Conclusion

Understanding Excel in Real-World Data Analysis: My First Week of Learning.

Josephine Mackylah — Sun, 07 Jun 2026 12:16:49 +0000

What is Excel?

Understanding Excel’s Structure

I have learnt that Excel is built on a simple but powerful structure that makes it easy to organize and analyze data effectively.

A workbook is the entire Excel file. It acts like a digital binder that can contain one or more worksheets, allowing users to store related datasets and analyses in a single place.

Different types of data in excel.

Text data, also known as labels, includes words, names, and categories. This type of data is mainly used for identification and description rather than calculations. Examples include product names such as Laptop or Mouse, employee names, or locations like Kampala or Berlin.
Numeric data represents values that can be used in mathematical operations. This includes numbers such as sales amounts, quantities, and prices. Because numeric data supports calculations, it is the foundation for formulas like sums, averages, and totals in Excel.
Date and time data represent specific points or periods in time. Examples include dates like 01/06/2026 or timestamps such as 10:30 AM. This type of data is particularly useful for analyzing trends over time, such as monthly sales performance or project timelines.
Currency data is a special type of numeric data used to represent money values. It is commonly used in business and financial analysis to track sales, expenses, profits, and budgets. For example, values like $500 or UGX 1,200,000 are treated as currency to ensure clarity and consistency in financial reporting.
Percentage data represents values out of 100 and is often used to show proportions, rates, or changes. Examples include discount rates, growth percentages, and performance indicators such as 15% increase in sales. This type of data is especially useful for comparing performance across categories.

Basic Arithmetic Operations in Excel.

It starts with addition, where Excel quietly does the work of bringing numbers together. Instead of manually calculating totals, a formula like =A1+B1 instantly combines values, whether it is daily sales, expenses, or quantities.
Then comes subtraction, which helps uncover differences. With something as simple as =A1-B1, Excel can show profit after expenses, remaining stock, or performance gaps—turning raw numbers into meaningful comparisons.
Multiplication takes things a step further. It allows Excel to scale values, such as calculating total cost by multiplying price and quantity using =A1*B1. This is where simple data begins to feel like real-world business analysis.
With division, Excel helps break things down into understandable units. A formula like =A1/B1 can show averages per item, cost per unit, or ratios that reveal deeper insights hidden within the data.
Finally, there is exponentiation, represented by the ^ symbol. Something like =A1^2 may seem simple, but it plays an important role in more advanced analysis such as growth trends, projections, and statistical calculations.

Excel Shortcuts That Make Work Feel Effortless.

As datasets grow larger, navigation becomes important. That is where Ctrl + Arrow Keys comes in, allowing you to jump across thousands of rows or columns in seconds instead of scrolling endlessly.

Formatting also becomes much easier with Ctrl + 1, which opens the formatting menu where you can adjust number formats, alignment, and borders to make data clearer and more professional.

A Critical Step in Analysis that I learnt is Data cleaning.

Function Purpose Examples I learnt.

UPPER() Converts text to uppercase =UPPER("jose") → JOSE
LOWER() Converts text to lowercase =LOWER("JOSE") → jose
PROPER() Capitalizes first letter of each
word =PROPER("jose macky") → Jose Macky
TRIM() Removes extra spaces from
text =TRIM(" Jose Macky ") → Jose Macky
LEFT() Extracts leftmost characters =LEFT("Jose", 2) → Jo
RIGHT() Extracts rightmost characters =RIGHT("Jose", 2) → hn
MID() Extracts characters from the
middle =MID("Jose", 2, 2) → oh
LEN() Returns length of text =LEN("Jose") → 4
FIND() Finds position of a substring
(case-sensitive) =FIND("o", "Jose") → 3
SUBSTITUTE() Replaces text within a string
=SUBSTITUTE("Jose Macky", "Macky",
"Smith") → Jose Smith

I also learnt Why Excel Matters in Data Analysis

Organizing Data for Better Insights

Product     Quantity Sold   Unit Price   Sales Date
Laptop             10           $500          01/06/2026
Mouse              25           $15        02/06/2026

Sorting and Filtering for Better Insights

Using Formulas and Functions to Automate Analysis

=A1+B1
adds the values in two cells and instantly updates the result whenever either value changes. This dynamic behavior reduces manual effort and minimizes the risk of errors.

Beyond basic formulas, Excel also provides built-in functions that simplify and speed up data analysis.

Some of the most commonly used functions include:

SUM() – Adds a range of values
Example: =SUM(B2)
AVERAGE() – Calculates the mean of a dataset
Example: =AVERAGE(B2)
MAX() – Returns the highest value in a range
Example: =MAX(B2)
MIN() – Returns the lowest value in a range
Example: =MIN(B2)
COUNT() – Counts the number of numeric entries in a range
Example: =COUNT(B2)
SUMIFS() – A function that adds values based on multiple conditions.
AVERAGEIFS() – A function that calculates the average of values based on multiple conditions.

These functions are widely used in data analysis to quickly summarize large datasets and uncover key trends. They help analysts move from raw numbers to meaningful insights in a matter of seconds.

Date and Time Functions in Excel

Real-World Applications of Excel in Different Industries

In the business and sales sector, companies use Excel to track sales performance, monitor inventory levels, analyze customer trends, and prepare financial reports. These activities help organizations understand their performance and make informed decisions that improve efficiency and profitability.
In healthcare, Excel plays an important role in managing and analyzing data. Hospitals and health programs use it to track patient information, monitor disease trends, generate monthly reports, and support monitoring and evaluation activities. This helps health professionals maintain accurate records and improve service delivery.
In education, schools and academic institutions rely on Excel to record student performance, calculate grades, track attendance, and generate academic reports. This simplifies administrative work and ensures that student data is well-organized and easy to access.
In the agriculture sector, Excel is used to monitor crop yields, analyze farmer productivity, track training attendance, and manage project performance indicators. These insights help agricultural organizations improve planning and support farmers more effectively.
In monitoring and evaluation, Excel is a key tool for data management and reporting. It is used to clean survey datasets, calculate indicators, track project progress, and produce donor reports. This ensures that program data is accurate, consistent, and useful for decision making.

Personal Reflection.

What also stood out to me was how much of data work is actually about preparation rather than analysis. Before this learning experience, I assumed data analysts spent most of their time discovering insights and producing visualizations. However, I have come to understand that a large part of the process involves cleaning and preparing data removing duplicates, correcting inconsistencies, standardizing formats, and ensuring accuracy using tools like TRIM and SUBSTITUTE.