DEV Community: joy yego

SQL Fundamentals: Building and Querying a School Database

joy yego — Mon, 20 Jul 2026 18:39:04 +0000

SQL Fundamentals: Building and Querying a School Database

Schools generate a surprising amount of structured data: student records, subject lists, exam scores, attendance — all of it begging to be organized, filtered, and analyzed. In this article, we'll build a small database for a fictional secondary school, Greenwood Academy, and use it to walk through six core SQL skills:

A. Building the Database
B. Filling the Database
C. Querying the Data
D. Range, Membership & Search Operators
E. COUNT
F. CASE WHEN

By the end, you'll be able to design a schema, populate it, and write queries that go well beyond SELECT *.

Section A: Building the Database

Before writing a single query, we need somewhere for the data to live. In SQL, a schema is a named container that groups related tables together — helpful once a database has dozens of tables across different departments (academics, finance, HR, etc.).

CREATE SCHEMA greenwood_academy;
USE greenwood_academy; -- MySQL syntax; in PostgreSQL: SET search_path TO greenwood_academy;

CREATE TABLE students (
    student_id      INT PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    gender          VARCHAR(1),
    date_of_birth   DATE,
    class           VARCHAR(10),
    city            VARCHAR(50)
);

CREATE TABLE subjects (
    subject_id      INT PRIMARY KEY,
    subject_name    VARCHAR(100) NOT NULL UNIQUE,
    department      VARCHAR(50),
    teacher_name    VARCHAR(100),
    credits         INT
);

CREATE TABLE exam_results (
    result_id       INT PRIMARY KEY,
    student_id      INT NOT NULL,
    subject_id      INT NOT NULL,
    marks           INT NOT NULL,
    exam_date       DATE,
    grade           VARCHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);

Schemas evolve, and ALTER TABLE lets you change a table without rebuilding it:

-- Add a column
ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);

-- Rename a column
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;

-- Remove a column that's no longer needed
ALTER TABLE students DROP COLUMN phone_number;

This is a common real-world pattern: add a column to test whether you need it, then drop it once you've decided it doesn't belong.

Section B: Filling the Database

With the structure ready, INSERT INTO adds rows:

INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city) VALUES
(1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
(2, 'Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
(3, 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
(4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
(5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2', 'Nakuru');

INSERT INTO subjects (subject_id, subject_name, department, teacher_name, credit_hours) VALUES
(1, 'Mathematics', 'Sciences', 'Mr. Njoroge', 4),
(2, 'English', 'Languages', 'Ms. Adhiambo', 3),
(3, 'Biology', 'Sciences', 'Ms. Otieno', 4),
(9, 'Computer Studies', 'Sciences', 'Mr. Oduya', 3);

INSERT INTO exam_results (result_id, student_id, subject_id, marks, exam_date, grade) VALUES
(1, 1, 1, 78, '2024-03-15', 'B'),
(2, 1, 2, 85, '2024-03-16', 'A'),
(3, 2, 1, 92, '2024-03-15', 'A'),
(5, 3, 2, 49, '2024-03-16', 'D');

Data isn't static — UPDATE and DELETE handle corrections:

-- Esther moved from Nakuru to Nairobi
UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

-- A mark was entered wrong: 49 should have been 59
UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

-- A result was cancelled and needs to be removed
DELETE FROM exam_results
WHERE result_id = 9;

Always confirm your inserts landed correctly:

SELECT COUNT(*) FROM students;
SELECT COUNT(*) FROM subjects;
SELECT COUNT(*) FROM exam_results;

Section C: Querying the Data

Filtering rows with WHERE is where SQL starts to feel powerful:

-- All Form 4 students
SELECT * FROM students WHERE class = 'Form 4';

-- All Sciences subjects
SELECT * FROM subjects WHERE department = 'Sciences';

-- All exam results with strong performance
SELECT * FROM exam_results WHERE marks >= 70;

-- Only female students
SELECT * FROM students WHERE gender = 'F';

Combine conditions with AND / OR:

-- Form 3 students from Nairobi
SELECT * FROM students
WHERE class = 'Form 3' AND city = 'Nairobi';

-- Form 2 or Form 4 students
SELECT * FROM students
WHERE class = 'Form 2' OR class = 'Form 4';

And joins pull related tables together — for example, showing each result with the student's name and subject:

SELECT s.first_name, s.last_name, sub.subject_name, e.marks
FROM exam_results e
JOIN students s ON e.student_id = s.student_id
JOIN subjects sub ON e.subject_id = sub.subject_id
ORDER BY e.marks DESC;

Section D: Range, Membership & Search Operators

Instead of chaining several comparisons, SQL offers compact operators for common patterns.

BETWEEN — range checks

-- Results in the "middle" band
SELECT * FROM exam_results WHERE marks BETWEEN 50 AND 80;

-- Exams within a date window
SELECT * FROM exam_results
WHERE exam_date BETWEEN '2024-03-15' AND '2024-03-18';

IN / NOT IN — membership checks

-- Students in specific cities
SELECT * FROM students WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

-- Students outside the lower forms
SELECT * FROM students WHERE class NOT IN ('Form 2', 'Form 3');

LIKE — pattern/text search

-- First names starting with 'A' or 'E'
SELECT * FROM students WHERE first_name LIKE 'A%' OR first_name LIKE 'E%';

-- Subjects with "Studies" in the name
SELECT * FROM subjects WHERE subject_name LIKE '%Studies%';

Section E: COUNT

COUNT collapses many rows into a single summary number — the basis of almost every dashboard metric.

-- How many students are in Form 3?
SELECT COUNT(*) AS form3_count
FROM students
WHERE class = 'Form 3';

-- How many results are 70 marks or above?
SELECT COUNT(*) AS strong_results
FROM exam_results
WHERE marks >= 70;

Pairing COUNT with GROUP BY gives per-category breakdowns:

SELECT class, COUNT(*) AS student_count
FROM students
GROUP BY class
ORDER BY student_count DESC;

Section F: CASE WHEN

CASE WHEN brings if/else logic directly into a query — perfect for turning raw numbers into human-readable labels.

Grading exam performance:

SELECT
    student_id,
    subject_id,
    marks,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS performance
FROM exam_results;

Classifying students by seniority:

SELECT
    first_name,
    last_name,
    class,
    CASE
        WHEN class IN ('Form 3', 'Form 4') THEN 'Senior'
        WHEN class IN ('Form 1', 'Form 2') THEN 'Junior'
    END AS student_level
FROM students;

You can even combine CASE WHEN with COUNT to build an instant summary report:

SELECT
    COUNT(CASE WHEN marks >= 80 THEN 1 END) AS distinctions,
    COUNT(CASE WHEN marks >= 60 AND marks < 80 THEN 1 END) AS merits,
    COUNT(CASE WHEN marks >= 40 AND marks < 60 THEN 1 END) AS passes,
    COUNT(CASE WHEN marks < 40 THEN 1 END) AS fails
FROM exam_results;

One query, an entire school's performance snapshot.

Wrapping Up

Starting from nothing, we:

Designed a schema and three related tables (Section A)
Populated them and corrected mistakes with UPDATE/DELETE (Section B)
Filtered and joined data with WHERE (Section C)
Used range, membership, and search operators for precise filtering (Section D)
Aggregated results with COUNT (Section E)
Added conditional logic with CASE WHEN (Section F)

These six building blocks - schema design, data manipulation, filtering, operators, aggregation, and conditional logic, cover most of what you'll reach for in day-to-day SQL work, whether you're managing a school's records or analyzing a company's sales.

Securely Connecting Power BI to an Aiven PostgreSQL Database Using SSL Certificates

joy yego — Fri, 10 Jul 2026 18:57:31 +0000

Learn how to connect Power BI Desktop to an Aiven PostgreSQL database using SSL certificates, troubleshoot common connection issues, and start building dashboards securely.

Cloud-hosted databases have become the backbone of modern analytics, and platforms like Aiven make it incredibly easy to deploy and manage PostgreSQL instances. But if you're connecting Power BI Desktop to an Aiven database for the first time, you might run into SSL certificate errors or wonder why the connection isn't as straightforward as it is with a local database.

I recently faced the same challenge while building a Power BI dashboard, and after a bit of troubleshooting, I discovered that the solution was simply trusting Aiven's SSL certificate through Windows.

In this guide, I'll walk you through the complete process so you can connect securely and start building dashboards without the frustration.

Prerequisites

Before we begin, make sure you have:

Power BI Desktop installed
An active Aiven PostgreSQL service
Your database credentials (Host, Port, Username, Password)
The Aiven CA certificate (ca.pem)
Administrator access to install certificates on Windows

How the Connection Works

Power BI communicates with your PostgreSQL database over an encrypted SSL connection. Before Windows allows that connection, it must trust the certificate used by Aiven.

Once the certificate is installed, Power BI can securely authenticate and retrieve your data.

Architecture Diagram

Power BI Desktop
│
▼
Windows Certificate Store
(Trusted Root Certification Authorities)
│
▼
Encrypted SSL Connection
│
▼
Aiven PostgreSQL Database

Step 1: Download the CA Certificate

Under Connection Information, download the CA Certificate.

The downloaded file is usually named: ca.pem

Save it somewhere convenient.

Step 2: Trust the Certificate in Windows

Before Power BI can connect securely, Windows needs to trust the certificate.

Open the Windows Run dialog (Win + R) and type: certmgr.msc

Navigate to:

Certificates
└── Trusted Root Certification Authorities
    └── Certificates

Right-click Certificates, then select:

All Tasks
    ↓
Import...

The Certificate Import Wizard will open.

Browse to the downloaded certificate and complete the wizard.

Once imported, Windows will trust Aiven's certificate.

Note: Depending on your environment, you may need to convert the certificate from .pem to .cer before importing it.

Step 3: Open Power BI

Launch Power BI Desktop.

Navigate to:

Home → Get Data → PostgreSQL Database

A PostgreSQL connection dialog will appear.

Step 4: Enter Your Connection Details

One mistake many beginners make is copying the entire PostgreSQL connection URL into the Server field.

For example, this will not work:

postgres://avnadmin:password@hostname:12345/defaultdb

Power BI expects only the hostname and port.

Enter the details like this:

Server

your-service.aivencloud.com:12345

Database

defaultdb

Choose your preferred connectivity mode:

Import – Copies the data into Power BI (recommended for most users)
DirectQuery – Queries the database in real time

Then click OK.

Step 5: Authenticate

Power BI will prompt you for your database credentials.

Enter:

Username: avnadmin
Password: Your PostgreSQL password

Click Connect.

Since the certificate has already been trusted by Windows, Power BI can now establish a secure SSL connection.

Step 6: Select Your Tables

If the connection succeeds, the Navigator window will display all available tables.

Choose the tables you want and click:

Load to import the data immediately, or
Transform Data if you want to clean or shape your data first.

I usually recommend Transform Data so you can verify data types and remove unnecessary columns before building your report.

Step 7: Build Your Dashboard

Congratulations!

Your Power BI report is now connected securely to your Aiven PostgreSQL database.

From here, you can create:

Sales dashboards
Customer analytics
Operational reports
Financial dashboards
KPI tracking
Real-time business insights

Every refresh will retrieve the latest data securely from your cloud database.

Common Issues and Their Fixes

SSL Certificate Error

Cause: Windows doesn't trust the certificate.

Solution: Import the Aiven CA certificate into the Trusted Root Certification Authorities store.

Authentication Failed

Cause: Incorrect username or password.

Solution: Verify your PostgreSQL credentials in the Aiven Console.

Could Not Connect to Server

Cause: Incorrect hostname or port.

Solution: Use the Host and Port values from Aiven's Connection Information page—not the PostgreSQL connection URL.

Invalid Server Name

If your Server field looks like this: postgres://avnadmin:password@hostname/defaultdb

Power BI will reject it.

Instead, enter only:
hostname:port

Best Practices

When working with cloud-hosted databases:

Never expose your database password in screenshots.
Avoid sharing full connection URLs publicly.
Keep your CA certificate in a secure location.
Rotate passwords regularly.
Use least-privilege database accounts whenever possible.

Conclusion

Connecting Power BI to an Aiven PostgreSQL database might seem intimidating at first, especially if you're new to SSL certificates. However, once you understand how Windows trusts certificates and how Power BI expects connection details, the process becomes straightforward.

The biggest lesson I learned was not to paste the full PostgreSQL connection URL into the Server field. Instead, use only the hostname and port, install the Aiven CA certificate into Windows' trusted certificate store, and let Power BI handle the rest.

With a secure connection in place, you're free to focus on what really matters—transforming raw data into meaningful insights through interactive dashboards.

If this guide helped you, let me know in the comments! I'd also love to hear about your experience connecting Power BI to cloud databases or any tips you've discovered along the way.

Happy dashboard building!

Understanding Relationships, Schemas, and Joins in Power BI

joy yego — Mon, 29 Jun 2026 07:11:44 +0000

Power BI isn't just about creating beautiful dashboards it's about telling the right story with your data. Behind every interactive report is a well-designed data model that connects tables efficiently. If you've ever wondered why your visuals aren't filtering correctly or why your measures return unexpected results, chances are your relationships need attention.

In this article, we'll explore relationships, schemas, and joins in Power BI and see how they work together to build reliable reports.

What Is a Data Model?

A data model is the collection of tables and the relationships between them. Instead of storing everything in one large table, Power BI encourages organizing data into logical tables such as Customers, Orders, Products, and Sales.

Figure 1: A simple Power BI data model connecting Customers, Orders, Products, and Sales tables.

Understanding Relationships

Relationships allow Power BI to connect tables using common columns.

Primary Key (PK): A unique identifier in a table (e.g., CustomerID in the Customers table).
Foreign Key (FK): A column that references the primary key in another table (e.g., CustomerID in Orders).

Power BI supports three relationship types:

One-to-One: One record matches one record.
One-to-Many: One customer can place many orders (the most common relationship).
Many-to-Many: Multiple records in one table relate to multiple records in another.

Star Schema vs. Snowflake Schema

When building a Power BI model, you'll often hear about Star and Snowflake schemas.

A Star Schema places a central Fact table (such as Sales) in the middle, surrounded by Dimension tables like Customers, Products, and Date. Microsoft recommends this design because it simplifies relationships and improves report performance.

A Snowflake Schema normalizes dimension tables into additional related tables. While it reduces data redundancy, it also increases model complexity and can make reports harder to maintain.

Figure 2: A Star Schema with one fact table connected to multiple dimension tables.

Relationship Cardinality and Cross-Filter Direction

When creating relationships, Power BI asks you to define:

Cardinality
- One-to-One
- One-to-Many
- Many-to-Many
Cross-filter Direction
- Single: Recommended for most models.
- Both: Useful in specific scenarios but can introduce ambiguity if overused.

Figure 3: Configuring cardinality and cross-filter direction.

Automatic vs. Manual Relationships

Power BI can automatically detect relationships when tables share matching column names and compatible data types.

However, manual relationship creation is often necessary when:

Column names differ.
Multiple relationships exist between tables.
Auto-detection fails due to data quality issues.

Relationships vs. Power Query Joins

Although both connect data, they serve different purposes.

Model Relationships	Power Query Merge (Joins)
Connect tables without combining them	Combine data into a new table
Created in Model View	Performed in Power Query Editor
Used after data is loaded	Used during data preparation
Ideal for Star Schemas	Ideal for cleaning and enriching data

Key Takeaways

Build a Star Schema whenever possible for better performance.
Use One-to-Many relationships for most business scenarios.
Keep tables separate using relationships instead of merging everything together.
Reserve Power Query Merge for data transformation and cleaning.
Review cardinality and cross-filter direction carefully to ensure accurate report interactions.

A clean data model is the foundation of every successful Power BI report. Taking the time to design relationships correctly will make your dashboards faster, easier to maintain, and more reliable as your data grows.

Power BI and DAX: Transforming Data into Business Insights

joy yego — Wed, 24 Jun 2026 07:40:17 +0000

In today's data-driven environment, organizations need more than just data they need actionable insights. Microsoft Power BI has emerged as one of the leading Business Intelligence (BI) tools, enabling users to clean, analyze, visualize, and share data effectively.

A key component that makes Power BI powerful is DAX (Data Analysis Expressions), a formula language used to create calculations, measures, and business metrics that drive meaningful reporting.

The Importance of Data Cleaning

Before analysis begins, data must be prepared. Data cleaning ensures that datasets are accurate, consistent, and ready for reporting. In Power BI, tools such as Power Query help users:

Remove duplicate records
Handle missing values
Correct data types
Standardize formats
Transform raw data into usable information

Clean data is the foundation of reliable dashboards and accurate business decisions.

Understanding DAX

DAX, short for Data Analysis Expressions, is the formula language used in Power BI to create calculations and analytical logic. Unlike traditional spreadsheet formulas, DAX works with entire tables, columns, and relationships.

DAX is commonly used to calculate:

Total Revenue
Total Profit
Average Sales
Profit Margins
Key Performance Indicators (KPIs)
Dynamic business metrics

These calculations help transform raw datasets into meaningful insights that support decision-making.

Measures vs Calculated Columns

One of the most important concepts in DAX is understanding the difference between Measures and Calculated Columns.

Measures are dynamic calculations that change based on filters and report interactions. They are commonly used in visualizations and dashboards.

Calculated Columns perform row-by-row calculations and store the results in the data model. They are useful for creating categories, labels, and classifications.

Knowing when to use each improves report performance and efficiency.

Common DAX Functions

Several DAX functions are essential for effective analysis:

SUM – Calculates totals.
AVERAGE – Finds average values.
COUNTROWS – Counts records.
DISTINCTCOUNT – Counts unique values.
IF – Performs logical testing.
SWITCH – Simplifies multiple conditions.
DIVIDE – Safely performs division calculations.

These functions help analysts create dynamic and insightful reports.

Building Interactive Dashboards

Power BI dashboards combine data visualizations, KPIs, filters, and DAX calculations into a single interactive view.

Effective dashboards allow users to:

Monitor performance in real time
Track business goals
Identify trends and patterns
Explore data using filters and slicers

By combining clean data, DAX calculations, and compelling visualizations, dashboards transform complex information into actionable business intelligence.

Conclusion

Power BI is more than a visualization tool—it is a complete analytics platform. Through data cleaning, DAX calculations, and interactive dashboards, organizations can turn raw data into meaningful insights. As businesses increasingly rely on data-driven decisions, mastering Power BI and DAX has become an essential skill for modern analysts and professionals.

Git and GitHub: Essential Tools for Modern Software Development

joy yego — Tue, 23 Jun 2026 07:51:24 +0000

In modern software development, managing code efficiently is just as important as writing it. Whether you're working on personal projects or collaborating with a team, Git and GitHub have become essential tools for version control and project management.

What is Git?

Git is a distributed version control system that helps developers track changes in their code over time. Instead of saving multiple versions of a project manually, Git records every modification, allowing developers to review, revert, and manage changes efficiently.

Some key benefits of Git include:

Tracking code changes over time
Reverting to previous versions when needed
Working on multiple features simultaneously using branches
Facilitating collaboration among developers

With Git, developers can experiment with new ideas without risking the stability of the main project.

Understanding GitHub

While Git manages code locally, GitHub is a cloud-based platform that hosts Git repositories online. It enables developers to store, share, and collaborate on projects from anywhere in the world.

GitHub provides features such as:

Repository hosting
Team collaboration
Pull requests and code reviews
Issue tracking
Project documentation
Continuous Integration and Deployment (CI/CD)

These capabilities make GitHub one of the most widely used platforms in software development.

Core Git Commands Every Developer Should Know

Getting started with Git involves learning a few fundamental commands:

git init
git add .
git commit -m "Initial commit"
git push origin main

Other useful commands include:

git clone – Copy a repository to your local machine.
git pull – Retrieve the latest changes from a remote repository.
git branch – Create and manage branches.
git merge – Combine changes from different branches.
git status – Check the current state of your repository.

Mastering these commands forms the foundation of effective version control.

The Power of Branching

One of Git's most powerful features is branching. Branches allow developers to work on new features, bug fixes, or experiments independently without affecting the main codebase.

Once the work is complete, changes can be reviewed and merged back into the main branch. This workflow improves collaboration and reduces the risk of introducing errors into production code.

Why Git and GitHub Matter

Git and GitHub have transformed how software is developed by enabling seamless collaboration, better project organization, and reliable version control. They are used by individual developers, startups, and large technology companies alike.

Beyond software development, GitHub also serves as a portfolio platform where developers can showcase projects, contribute to open-source software, and demonstrate technical skills to potential employers.

Conclusion

Git and GitHub are fundamental tools for anyone pursuing a career in technology. Git provides a reliable system for tracking and managing code changes, while GitHub enables collaboration and project sharing on a global scale. Together, they form the backbone of modern software development and are essential skills for every aspiring developer.

Excel Functions, Pivot Charts, and Dashboards: Turning Data into Insights

joy yego — Tue, 23 Jun 2026 07:44:19 +0000

Microsoft Excel remains one of the most powerful tools for data analysis and reporting. From organizing large datasets to creating interactive dashboards, Excel enables users to transform raw data into meaningful insights that support informed decision-making.

Excel Functions: The Foundation of Data Analysis

Excel functions automate calculations and simplify complex tasks, making data analysis faster and more accurate. Some of the most widely used functions include:

VLOOKUP/XLOOKUP – Retrieve information from large datasets.
SUMIF and COUNTIF – Perform calculations based on specific conditions.
IF Statements – Create logical tests and automate decision-making.
AVERAGE, MAX, and MIN – Generate quick statistical summaries.

These functions reduce manual work, improve accuracy, and help users uncover valuable information from their data.

Pivot Tables and Pivot Charts

When working with thousands of records, analyzing data manually can be time-consuming. Pivot Tables provide a dynamic way to summarize and organize data without altering the original dataset.

With Pivot Tables, users can:

Calculate totals and averages.
Group data by categories.
Compare performance across regions, products, or periods.
Identify trends and patterns.

Pivot Charts take this analysis a step further by visualizing Pivot Table results. Instead of reading rows of numbers, users can quickly understand performance through charts such as bar charts, line charts, and pie charts.

For example, a sales team can use Pivot Charts to compare monthly revenue across different regions and identify top-performing products at a glance.

Dashboards: Bringing Everything Together

Dashboards combine Excel functions, Pivot Tables, and Pivot Charts into a single interactive reporting tool. They provide a comprehensive overview of key metrics and business performance.

A well-designed dashboard typically includes:

Key Performance Indicators (KPIs)
Interactive charts
Pivot Tables
Slicers and filters
Conditional formatting

Dashboards allow users to explore data dynamically and gain insights in real time. Instead of reviewing multiple spreadsheets, decision-makers can access all critical information from one centralized view.

Organizations increasingly rely on data-driven decisions, making Excel proficiency a valuable skill across industries. Mastering functions, Pivot Tables, Pivot Charts, and dashboards enables professionals to analyze information efficiently, communicate insights effectively, and support strategic business decisions.

Conclusion

Excel is far more than a spreadsheet application. Its powerful functions, Pivot Tables, Pivot Charts, and dashboards work together to transform raw data into actionable insights. Whether you're a student, analyst, or business professional, developing these skills can significantly improve your ability to understand, visualize, and communicate data.

[Boost]

joy yego — Sat, 06 Jun 2026 12:48:16 +0000

joy yego

Jun 6

How is Excel Used in Real World Data Analysis?

3 min read

How is Excel Used in Real World Data Analysis?

joy yego — Sat, 06 Jun 2026 12:47:42 +0000

Introduction

What is excel? Excel is a program developed bey Microsoft used to store, organize, analyze and visualize data. It consists of rows running vertically and columns running horizontally,which can be manipulated by any user in any form that is using different formulas and functions within excel.

How is excel used in Data Analytics?

Data Analysis is the process of collecting, cleaning, transforming and analyzing data to discover useful insights, identifying patterns that support informed decision making. From day to day organizations tend to generate large amounts of data from different transactions, customer interactions etc.

Uses of Excel for data analysis

Customer analysis: Based on the different customer information collected by different businesses, analysts use excel to analyze this information. For example, using excel you can discover using online store covers customers aged 18 to 32 years.
Sales analysis: Based on the different records analysts use excel to analyze the sales and discover for example the product that generates the most income.
Excel is used for data collection and storage: allows analysts to collect and store large amounts of data.
Visualization: to provide a quick view of the business performance through pie charts, KPIs etc.

What are the different features in excel?

Excel is used for data cleaning and preparation: Data usually contains errors, duplicates, missing values and inconsistencies. Cleaning prepares the data for accurate analysis. This data can be cleaned using different excel tools such as :

Remove duplicates
Find and Replace
Text to columns
Sort among others.

Also in cleaning text data, there are different functions used in cleaning text data such as:

UPPER() and LOWER()
PROPER()
TRIM()

Data sorting, filtering and organizing:
Sorting is the arrangement of data in different orders such as ascending or descending among others.

Filtering allows one to display only rows that are in a certain criteria.

Data before filtering

Data after filtering

Performing calculations: Data analysis revolves around different mathematical and statistical functions and formulas to derive insights from the datasets quickly. These functions include:

sum()
sumif()
count()

Data validation: It involves restricting the type of data that a user inputs within the spreadsheet. It can be through drop downs or through custom inputs.

Data Formatting: This could be number formatting or conditional formatting. Number formatting involves changes on numbers display without altering the actual value improving readability and meaning. Number formats include:

Currency
PercentageS

Conditional formatting: highlights cells based on rules or criteria. Helps in in spotting outliers and trends as well.

IT department

Conclusion

Excel is a valuable data analytics tool that is underestimated. It has a friendly user interface which make it easier for anyone to use. It is used organize, clean , analyze and visualize data. One can perform different calculations and as well as creating reports and dashboard, transforming raw data into meaningful information used for decision making.

Reflection

Learning Microsoft Excel has been an important step in my journey as an aspiring data analyst. At first, I viewed Excel as a simple spreadsheet application used for storing data and performing basic calculations. However, as I gained more experience, I discovered that Excel is a powerful analytical tool that can transform raw data into meaningful insights.

Overall, learning Excel has strengthened my confidence in working with data and has inspired me to continue exploring the field of data analytics. It serves as a reminder that powerful insights often begin with mastering the fundamentals.