DEV Community: Velma Ketra Lukaya

From Raw Data to Published Reports: A Practical Guide to the Power BI Workflow

Velma Ketra Lukaya — Fri, 02 Oct 2026 13:30:42 +0000

Power BI is often described as a visualisation tool, but its real value lies in the workflow that precedes the visuals: connecting to data, assessing and cleaning it, calculating with DAX, structuring it into a well-designed model, and finally publishing and securing the result.

This article follows that workflow in the order in which it is typically learned and applied and recommends model design for a typical business intelligence project.

1. The Power BI Ecosystem

Component	Description	Role in the workflow
Power BI Desktop	Free Windows application for connecting to data (Excel, CSV and others), cleaning it, modelling it, writing DAX, and building visualisations, dashboards and reports	Authoring
Power BI Service	Cloud-based (browser) service for publishing, sharing and collaboration, with real-time updates; the full feature set is a paid licence	Publishing and sharing
Power BI Mobile	On-the-go access to reports and interaction with visuals; not used for report creation	Consumption
Power BI Report Server	Hosts reports on an organisation's own server, typically for data-security reasons	On-premises hosting

2. Introduction: Power BI as a Workflow

Power BI is a Microsoft tool that turns raw data into interactive insight. Compared with Excel, it is designed for

Larger datasets
Supports dashboards, reports and analytical calculations
Work with real-time data. It shares many function names with Excel, but its calculation language is DAX (Data Analysis Expressions).

Treating Power BI purely as a charting tool misses most of its value. Reliable reports depend on a connected sequence of stages:

data  →  preparation  →  modelling  →  analysis  →  visualisation  →  sharing

Each stage depends on the one before it. A data-quality problem that is not corrected in preparation becomes a modelling error, then a calculation error, and finally an incorrect figure on a dashboard. The sections that follow treat the stages in order.

Power BI Desktop, the environment in which data is connected, prepared, modelled and visualized.

Desktop interface

Home: Get data and Transform data.
Modeling: Manage relationships.
Insert: add a visual.
View: control layout.
Fields pane: lists all fields in the model (columns are fields; records are rows).
Visualizations pane: used to select and configure a chart or table.
Three views: Report (create and view reports), Model (manage relationships), Table (data in tabular form).

3. Data Sources

Power BI connects to a range of external sources, including Excel, CSV, SQL databases and the web. In the course, a Kenya crops dataset served as the working example. It passes through Power Query for cleaning and transformation, then into the data model, where relationships are created between tables holding different information.

 Excel ─┐
 SQL   ─┤
 CSV   ─┼──►  Power BI Desktop  ──►  Power Query  ──►  Data model
 Web   ─┘                           (clean and        (relationships)
                                     transform)

The choice of source influences how much preparation is required, which is why inspection (Section 4) follows immediately after import.

4. Importing and Inspecting the Raw Dataset

Importing data is the beginning of the work. Before any analysis, the dataset should be examined in Power Query, which has four main areas:

Ribbon: transformation commands.
Query pane: lists every table; each can be worked on individually.
Data preview: the rows and columns of the selected table.
Applied Steps: a recorded list of every change, which also functions as an undo mechanism.

Common data-quality problems

Numbers stored as text. A numeric-looking column that is left-aligned in the preview is usually being treated as text.
Unrecognised dates.
Missing values and pseudo-blanks: N/A, Error, blank, null.
Numeric columns with the wrong data type.
Duplicate records.

A missing value is not automatically zero

The meaning of a missing value depends on the column. A blank in a pest-control column may be a genuine answer (no pest control used), whereas a blank in a county column is simply absent information. Replacing every blank with 0 converts "unknown" into "zero" and distorts averages and totals later in the analysis.

Identifiers are text, not numbers

Some values that look numeric should be stored as text: ID numbers, employee IDs, phone numbers and coordinates. They are never summed or averaged, and numeric storage can strip leading zeros. Data types should reflect what a column means.

A practical convention for blanks, by column type:

Numeric column: leave as null, or replace with a number.
Text column: use a consistent label such as N/A or Not provided.

5. Cleaning and Transforming Data in Power Query

5.1 Data types and errors

When a column intended to be numeric or date-typed contains text, errors appear because of the mismatch. An error can be handled in several ways:

replace with null/blank;
replace with the actual value, if known;
remove the row;
replace with the median or mean (for example, calculated without outliers).

To correct a data type, select the column and use Transform → Detect data type.

5.2 Consistent categories

Use one name to represent a county.
Use one category to represent blank, unknown and similar values.
Fill missing values with the mode only when filling is unavoidable.

5.3 Duplicates

Duplicates are removed using a unique ID: rows sharing the same unique ID represent the same record.

5.4 Formatting and applying changes

Use Transform columns → Format for text clean-up, then Close & Apply to load the result into the model. Every action is recorded in Applied Steps, so the process is reversible and repeatable.

Why this precedes modelling

Everything downstream trusts this stage. Relationships fail on keys with inconsistent spellings, averages are wrong if blanks were silently converted to zeros, and counts are inflated by duplicates.

6. DAX: From Preparation to Analysis

DAX (Data Analysis Expressions) is the formula language Power BI uses to create calculations: calculated columns, measures and calculated tables. Where Excel formulas operate on cells, DAX operates on columns and tables and responds to filters (the filter context), a concept that recurs throughout the following sections.
DAX functions fall into six categories:

Category	Purpose
Aggregate	Mathematical and statistical summaries
Filter	Modify or create filter context and tables
Logical	Evaluate conditions to support decisions and categorisation
Text	Format and clean text
Date and Time	Extract and compare date components
Time Intelligence	Period-based calculations (see note in Section 7.9)

7. DAX Function Categories

7.1 Aggregate functions

Function	Behaviour
`SUM`	Adds all numbers in a column
`AVERAGE`	Arithmetic mean
`MIN`, `MAX`	Lowest and highest value
`MEDIAN`	Middle value of the data
`COUNT`, `COUNTA`	Count non-blank values
`COUNTROWS`	Counts rows in a table
`DISTINCTCOUNT`	Counts distinct values
`COUNTBLANK`	Counts blanks
Standard deviation, variance	Measures of spread

Mean versus median. The mean is affected by outliers; the median is not. When the mean and median are close, the data is approximately normally distributed. For skewed variables (left- or right-skewed), the median often represents a typical value better.

Iterators: SUMX and AVERAGEX. These evaluate an expression for each row of a table and then sum or average the results. They are used when a quantity is not stored in the table but can be derived from columns that are, for example revenue computed from yield and price. The table reference comes first, followed by the expression. The calculation requires knowing what constitutes the value, not the value itself, and blanks in the inputs will change the result.

Illustrative example

Total Revenue = SUMX( Crops, Crops[Yield] * Crops[Market Price] )

Arithmetic versus geometric mean. AVERAGE is the ordinary arithmetic mean. GEOMEAN multiplies the values and takes the nth root, and is appropriate for ratios, growth rates and compounding changes.

7.2 Arithmetic functions

Arithmetic in DAX is mostly expressed through operators and expressions such as the SUMX example above. DIVIDE is the safe division function and handles division by zero gracefully, which is useful in ratio measures.

7.3 Filter functions

Filter functions ensure that calculations reflect the intended filter context.

FILTER returns a subset of a table that meets a condition. The result is a new table that can be named and used like any other table.
ALL ignores the active filters.
ALLEXCEPT removes all filters except those on a specified column.
A check for whether a column is filtered to a single value (HASONEVALUE) returns TRUE when the column has exactly one value in the current context.
CALCULATE is covered in Section 7.4.

Syntax conventions

FILTER( Table, Column = "value" )          -- e.g. only Kericho county

Illustrative example:

Kericho Only =
FILTER( 'Kenya_Crops_Dataset 5', 'Kenya_Crops_Dataset 5'[County] = "Kericho" )

&& is logical AND (all conditions must be met) and must be written with two ampersands; a single & is concatenation.

|| is logical OR that requires only one of the stated conditions to be met to return results of the DAX function

For several values from one column, use the IN operator with curly brackets, for example {"Nairobi", "Meru", "Nakuru"}.

Excel's conditional aggregations (SUMIF, AVERAGEIF) have no direct DAX functions of the same name; the equivalent is built with CALCULATE.

7.4 CALCULATE

CALCULATE evaluates an expression in a modified filter context. The expression is typically an aggregate (sum, average, min, max). Because it returns a single value, it is used to create a measure. It accepts multiple filter conditions separated by commas; for two conditions on the same column, use IN.

Examples of questions answered with CALCULATE:

total yield for a given county;
market price for Nairobi;
total cost of production for farmers who planted more than 10 acres of maize in Kiambu county;
how many farmers made a loss;
average market price of tomatoes planted during the dry season.

Sales Today = CALCULATE( SUM( Sales[Sales] ), Sales[Date] = TODAY() )

If a total-sales measure already exists, it can be reused:

Sales Today = CALCULATE( [Total Sales], Sales[Date] = TODAY() )

Illustrative example:

Kiambu Maize Cost =
CALCULATE(
    SUM( Crops[Cost of Production] ),
    Crops[County] = "Kiambu",
    Crops[Crop Type] = "Maize",
    Crops[Planted Area] > 10
)

7.5 ALL and removing filters

To prevent a KPI from being affected by slicers or filters, apply ALL to the relevant table or columns. ALLEXCEPT ignores filters on every column except the one named (for example, county).
e.g a measure named Total Revenue all county, which sums Revenue (KES) while applying ALL to the County and Crop Type columns, so the card ignores slicers on those columns.

Total Revenue all county =
CALCULATE(
    SUM( 'Kenya_Crops_Dataset 5'[Revenue (KES)] ),
    ALL( 'Kenya_Crops_Dataset 5'[County] ),
    ALL( 'Kenya_Crops_Dataset 5'[Crop Type] )
)

A measure using ALLEXCEPT so that the card ignores all filters except County and Crop Type filters.

7.6 Logical functions

Logical functions evaluate conditions and return values depending on whether those conditions are true or false. They support decision-making, filtering and categorising data (for example profitable or non-profitable; low, no or high profit).

Function	Behaviour
`IF`	Tests a condition; two conditions, two outputs
Nested `IF`	More than two conditions and outputs
`AND`	True only if both conditions are true
`OR`	True if at least one condition is true
`NOT`	Reverses the logical value
`SWITCH`	A cleaner alternative to nested `IF`; evaluates an expression against matching values; `SWITCH( TRUE(), … )` allows multiple conditions
`ISBLANK`	Tests for blanks, usually inside `IF`

Two points matter in practice. Blanks fall into the "else" branch of an IF, so ISBLANK should be used where blanks need separate handling. And AND/OR used alone return only TRUE/FALSE; nested in an IF, the output can be any quoted text.

Illustrative example:

Profit Category =
IF( ISBLANK( Crops[Profit] ), "Not recorded",
    IF( Crops[Profit] < 0, "Loss", "Profitable" ) )

Use of AND

Use of OR

7.7 Text functions

Text functions clean and format text: LOWER, UPPER, PROPER, CONCATENATE/CONCAT, SPLIT, TRIM, CLEAN, LEFT and RIGHT. Because they are data-preparation tasks, they are generally better applied in Power Query.

TRIM removes extra leading or trailing spaces.
CONCATENATE joins two text values without a delimiter; for more values, or to insert a space or word, use the & operator.

Illustrative example:

Full Label = Crops[County] & " - " & Crops[Crop Type]

Without space

With space

7.8 Date and time functions

Need	Function
Current date / date and time	`TODAY`, `NOW`
Extract components (numeric)	`YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`
Text form of a component	`FORMAT( date, "YYYY" )`, `"YY"`; `"MMMM"` full month name, `"MMM"` short; `"DDDD"` day name
Quarter	`FORMAT( date, "Q" )` (see appendix)
Difference between two dates	`DATEDIFF( date1, date2, interval )` with interval day, month or year
Working days between dates	`NETWORKDAYS` (excludes weekends, optionally holidays)
Build a date	`DATE`
Week number	`WEEKNUM`; return type 1 = week starts Sunday, 2 = week starts Monday

Illustrative example:

Order Month Name = FORMAT( Orders[Order Date], "MMMM" )
Days to Delivery = DATEDIFF( Orders[Order Date], Orders[Delivery Date], DAY )

7.9 Time intelligence

Time-intelligence functions form a category in the course structure, but no specific functions or examples were covered, so none are presented here as demonstrated content.

8. Understanding DAX Outputs

Before writing a formula, the first question is: what output is required?

Required output	DAX object	Notes
A single value	Measure	Appears in the Fields pane; usually shown in a Card visual
A column in which each row is evaluated separately	Calculated column	Adds an entire column to a table
A new table	Calculated table	For example, data for a specific date or specific counties

A measure is evaluated according to the filter context in which it is used, so it responds to slicers and to the rows and columns of a visual. A calculated column is evaluated row by row when the model is refreshed. Choosing the wrong object type is a common source of errors even when the formula itself is correct.

9. From DAX Functions to Business Questions

DAX is not an exercise in memorising formulas. The essential step is understanding the question and identifying what to filter, what to aggregate and what output is needed.

Business question	Approach
Total sales for today	Filter the sum to that date: `CALCULATE( SUM( sales ), date = TODAY() )`, or `CALCULATE( [Total Sales], date = TODAY() )`
How many sales	Count rows or transactions
How many customers	Counts people who completed transactions, regardless of how many items each bought; `COUNTROWS` or `COUNT` on the customer ID. Different approaches can give the same answer
*How many different* customers**	A distinct count (`DISTINCTCOUNT`)
Total yield or market price for a county	`CALCULATE` with a county filter
Average market price for tomatoes in the dry season	`CALCULATE` with `AVERAGE` and two conditions

These calculations assume tables that are organised sensibly. If the same customer is recorded under several spellings, "how many different customers" cannot be answered correctly. This is the bridge to data modelling.

10. Data Modelling: Structuring Data for Analysis

Data modelling is the process of organizing tables and defining the relationships between them so that data can be correctly filtered, analyzed and summarized. It involves two tasks:

defining and organizing tables;
defining relationships between tables.

Why it matters

Better reporting structure.
Reduced repetition.
Fewer errors.
Ease of creating reports.

A well-designed model also keeps DAX simple, supports query performance (each visual issues a query against the model, and fact/dimension designs suit how those queries filter, group and summarise), improves readability and maintainability, and scales more gracefully as new subject areas are added.

11. Flat Table

A flat table holds all information in one table. The Kenya crops dataset began this way: county, crop type, planted area, revenue and price all sit in each row.

FLAT TABLE   (illustrative diagram)
┌────────────────────────────────────────────────────────────┐
│ County | Crop  | Farmer | Season | Area | Yield | Revenue … │
│ Kiambu | Maize | F001   | Dry    | 12   | …     | …         │
│ Kiambu | Maize | F002   | Dry    | 8    | …     | …         │
│ Kericho| Tea   | F003   | Wet    | 20   | …     | …         │
└────────────────────────────────────────────────────────────┘
   descriptive text is repeated on every row

Aspect	Assessment
Advantages	Simple to understand; quick to start; no relationships to configure; adequate for a small, single-topic dataset
Disadvantages	Descriptive values repeat on every row; corrections must be made in many places; tables become very wide; descriptions and measurements are mixed
When appropriate	Small, one-off analyses with a single subject
Power BI implications	More repeated text is stored; no reusable dimensions to filter from; scales poorly as subjects are added

The multi-table alternative stores facts (such as transactions or hospital visits) separately from detail tables (such as doctor details), linked to the main table to avoid repetition.

12. Fact Tables and Dimension Tables

Fact table

A fact table:

Keeps the specific records, events or transactions to be analyzed (for example, all patients who visit a hospital);
Measures something;
Contains numerical records, metrics and quantities;
Allows the grain of the data to be understood;
Contains both primary and foreign keys.

Grain (granularity) is a description of what each record in a table represents. It indicates what was done. The test for any fact table is: what does one row represent? In the hospital example, one row of FactVisit represents one patient visit. If the grain is unclear, counts and totals become unreliable; for instance, if a visit were repeated once per test performed, "number of visits" would be overstated.

Generic examples : FactSales (one row per sale line), FactOrders (one row per order), FactTransactions (one row per payment).

Dimension table

A dimension table

Describes something, giving context for the events held in the fact table (for example, patient details);
Contains descriptive text content;
Has a primary key.

Example: DimPatient, DimDoctor, DimDepartment, DimCustomer, DimProduct, DimDate, DimLocation.

	Fact table	Dimension table
Answers	What happened? How much?	Who, what, where, when?
Contents	Numbers, metrics, keys	Descriptive attributes
One row represents	One event (the grain)	One entity
Keys	Primary and foreign keys	Primary key
Typical size	Many rows	Fewer rows

Separating them means each description is stored once, while the fact table stays focused on events.

13. Star Schema

A star schema consists of one central fact table surrounded by dimension tables, each connected to the fact table.

                 DimDepartment
                       │
                       │
   (other dimension) ── FactVisit ── DimPatient
                       │
                       │
                   DimDoctor

Advantages

Simple, readable model diagram.
Descriptions are stored once, reducing repetition.
Dimensions filter and the fact table is summarised, which matches the way Power BI visuals query a model.
Simpler DAX: measures aggregate fact columns while slicers come from dimensions.
Easy report creation.

Disadvantages

Requires planning, since a flat table must be split into facts and dimensions.
Some descriptive values still repeat within a dimension (for example, a county name for every patient in that county).

Appropriate use: most business intelligence and reporting models.

14. Snowflake Schema

A snowflake schema works like a star schema, but the dimension tables are broken down into several smaller related tables. The structure is hierarchical: fact → dimension → sub-dimension.

 DimCountry ── DimCounty ── DimPatient ── FactVisit

	Star	Snowflake
Dimensions	One table per dimension	Dimension split into related sub-tables
Redundancy	Some repetition inside dimensions	Less repetition (more normalized)
Relationship hops from slicer to fact	One	Two or more
Model diagram	Compact	Larger, more tables

Advantages:
-Less repeated data within dimensions;
-Explicit hierarchies;
-A shared lookup (such as county) can be maintained in one place.

Disadvantages:
-More tables and relationships,
-Longer filter paths, and a
-Busier model that is harder for report builders to navigate.

Appropriate situations: when a sub-dimension is shared by several dimensions, or when the source data is already normalized.

15. Comparing Flat, Star and Snowflake Schemas

Feature	Flat table	Star schema	Snowflake schema
Structure	Everything in one table	One fact table plus dimension tables	Fact table plus dimensions split into sub-tables
Number of tables	1	Few	Most
Redundancy	High	Low to moderate	Lowest
Model complexity	Simplest to set up; harder to maintain as it grows	Moderate; easy to read	Highest
DAX / reporting	Adequate for simple cases; filtering limited to that one table	Simple: measures on facts, slicers from dimensions	Works, but filters pass through more relationships
Performance	Wide tables with repeated text can be inefficient	Recommended by Microsoft for Power BI	Extra relationship hops; usually no benefit unless the structure is needed
Scalability	Poor	Good	Good, with growing complexity
Maintainability	Corrections repeated across many rows	Fix a description once	Good for hierarchies; heavier to manage
Appropriate use	Small, single-topic analysis	Most BI projects	Shared hierarchies; normalised sources

16. Primary Keys and Foreign Keys

A primary key is a unique identifier column that gives each record in a table a unique identity.
A foreign key is a primary key from one table that is used in a different table.
A common column is required before a relationship can be created.

Example. CustomerID is unique in DimCustomer (one row per customer) but may appear many times in FactSales, because one customer can make many purchases. DimCustomer[CustomerID] is therefore a primary key, FactSales[CustomerID] is a foreign key, and the relationship is one-to-many.

Illustrative example:

DimCustomer

CustomerID (PK)	CustomerName
C1	Amina
C2	Brian

FactSales

SaleID	CustomerID (FK)	Amount
S1	C1	500
S2	C1	300
S3	C2	450

Referential integrity means every foreign-key value in the fact table has a matching primary key in the dimension. If FactSales contained C9 but DimCustomer had no C9, those sales would not attach to any customer and would appear under a blank member in visuals.

17. Relationships in Power BI

A relationship shows how tables are connected. It is created by matching a primary key in a dimension table with a foreign key in the fact table. Relationships are necessary because, once data is distributed across tables, Power BI needs to know which fact rows belong to which dimension rows. They are what allow filters to travel from one table to another.

Key properties:

Keys: primary key ↔ foreign key.
Cardinality: how rows in one table relate to rows in another (Section 18).
Cross-filter direction: single or both (Section 19).
Referential integrity (Section 16).
Active and inactive relationships: only one relationship between two tables can be active at a time. Additional relationships can exist but are inactive (shown as dashed lines in Model view) and do not filter unless a DAX measure activates them with USERELATIONSHIP. A relationship must exist and be active for columns from different tables to be used together.

18. Relationship Cardinality

Cardinality describes how data in one table relates to data in another, based on the count of matching rows. It should be distinguished from column cardinality, which refers to how unique the values in a single column are.

One-to-many (1:*), the most common type

A single row in one table matches multiple rows in the other.

Example: one patient (dimension) → many visits (fact); illustrative: one DimCustomer row → many FactSales rows.
Common in dimensional models because every dimension-to-fact link is of this type: the "one" side holds unique keys and the "many" side holds the events.

DimCustomer (1) ─────────────< FactSales (*)

Many-to-one is the same relationship read from the other direction.

One-to-one (1:1)

One row in a table links to only one row in another table. It arises when there is no separate information that justifies splitting the table, and it relates to normalisation.

Illustrative example: Employee and EmployeeBadge, with exactly one badge record per employee.
Before using it, consider whether the two tables should simply be combined into one (for example by a merge in Power Query). Filters flow in both directions in a 1:1 relationship.

Employee (1) ─────────── (1) EmployeeBadge

Many-to-many (:)

Multiple rows in one table link to multiple rows in another.

Illustrative example: students and courses.
It requires careful modelling because neither side has unique keys, so filters can match many rows on both sides and totals can become hard to interpret. A bridge (junction) table converts it into two one-to-many relationships.

Students (*) ───────────── (*) Courses
   safer:  Students (1)──<  Enrolments  >──(1) Courses

19. Filter Direction

Filter direction controls which table filters which when a user interacts with a slicer or visual.

Single-direction filtering

Filters flow from the "one" side (dimension) to the "many" side (fact).

DimProduct
    │
    ▼
FactSales

Selecting a product in a DimProduct slicer filters the corresponding rows in FactSales, so a revenue card then shows only that product's revenue (illustrative). This is the default for a one-to-many relationship and the preferred setting in a star schema.

Bidirectional filtering

DimProduct
    ▲
    ▼
FactSales

Filters flow in both directions. It should be used sparingly because it can cause:

ambiguous filter paths, where multiple routes between two tables leave it unclear which one Power BI should use, potentially deactivating a relationship or producing route-dependent results;
unnecessary complexity, since each bidirectional relationship makes the model harder to reason about;
unexpected filtering behaviour, as slicers begin filtering one another and numbers change in ways report users do not expect;
reduced performance on large models.

Guideline: keep relationships single-direction from dimension to fact, and use Both only for a specific, understood reason.

20. Establishing Relationships in Power BI

Identify the matching keys: the primary key in the dimension and the foreign key in the fact table.
Create the relationship by either:
- drag and drop, from primary key to foreign key in Model view; or
- Modeling → Manage relationships on the ribbon.
Set the cardinality and the cross-filter direction.

Preparing keys when none exist

When a flat table is split into dimensions, the following workflow applies:

Clean the data.
Create dimension tables, working out which columns belong to each dimension.
Give each dimension a unique identifier. If none exists, create one: right-click on the table → Add index column (from 1).

In the original table, find and replace the descriptive name with the unique index, which then serves as the foreign key.
Clean the data again and check for errors.
Create relationships and ensure they are active.
Calculate (DAX).
Display (dashboard).

The same result can also be achieved by merging the original table with the new dimension in Power Query, which leads to the next topic.

21. Joins in Power Query

Relationships connect tables inside the model. A join combines tables earlier, during data preparation. A join combines two tables using matching columns identified through keys, and is performed in Power Query because the table is being transformed to create a new, merged table.
In Power Query the command is Merge Queries. Which table's rows are kept in full depends on the join type.

Example used for all six joins

Illustrative example
Join key: CustomerID.

Customers (left table)

CustomerID	CustomerName
C1	Amina
C2	Brian
C3	Chebet

Orders (right table)

OrderID	CustomerID
O1	C1
O2	C1
O3	C4

Brian (C2) and Chebet (C3) have no orders, and order O3 belongs to C4, who is not in the Customers table.

21.1 Inner join

Keeps only matching records in both tables. Use when only records existing on both sides are wanted.

CustomerID	CustomerName	OrderID
C1	Amina	O1
C1	Amina	O2

Customers ∩ Orders         only the overlap

21.2 Left outer join

Keeps all rows from the left (first) table and only the matching rows from the right. Use to list all customers whether or not they ordered.

CustomerID	CustomerName	OrderID
C1	Amina	O1
C1	Amina	O2
C2	Brian	null
C3	Chebet	null

all of Customers + matching Orders

21.3 Right outer join

Keeps all rows from the right (second) table and the matching rows from the left.

CustomerID	CustomerName	OrderID
C1	Amina	O1
C1	Amina	O2
C4	null	O3

matching Customers + all of Orders

21.4 Full outer join

Keeps all records from both tables, whether or not they match.

CustomerID	CustomerName	OrderID
C1	Amina	O1
C1	Amina	O2
C2	Brian	null
C3	Chebet	null
C4	null	O3

Customers ∪ Orders         everything

21.5 Left anti join

Keeps only the rows in the left table that have no match in the right. Useful for finding customers who never ordered, and for data-quality checks.

CustomerID	CustomerName
C2	Brian
C3	Chebet

Customers − Orders         left only

21.6 Right anti join

Keeps only the rows in the right table that have no match in the left. Useful for finding orders whose customer is missing, which is a referential-integrity problem (Section 16).

OrderID	CustomerID
O3	C4

Orders − Customers         right only

Summary

Join	Rows kept	Result in the example
Inner	Matches only	2 rows
Left outer	All left + matching right	4 rows
Right outer	All right + matching left	3 rows
Full outer	Everything	5 rows
Left anti	Left rows with no match	2 rows
Right anti	Right rows with no match	1 row

22. Join Output Illustrations

 Left table = L,  Right table = R,  overlap = matched rows

 Inner       :        [ L ∩ R ]
 Left outer  :   [ L ........ ∩ ]       all of L, matched R
 Right outer :        [ ∩ ........ R ]   matched L, all of R
 Full outer  :   [ L ........ ∩ ........ R ]
 Left anti   :   [ L only ]
 Right anti  :                [ R only ]

23. Power Query Joins vs Power BI Relationships

Both mechanisms connect tables through matching keys, but they occur at different stages and do different things.

 Power Query                         Data model
 ───────────                         ──────────
 Merge / Join  ──►  Data           ──►  Relationships  ──►  DAX / Visuals
 (combine rows)     transformation       (connect tables)
 "preparation"                           "modelling"

Does a Power Query merge physically combine data? Yes. A merge creates a new table, or adds columns to an existing one, in which matched rows sit side by side. After Close & Apply, that wider table is what is loaded into the model.

Does a relationship physically combine tables? No. A relationship creates no new table and moves no data. Both tables remain separate. It is a rule in the model stating that when one table is filtered, the other is filtered through a shared key. Power BI applies it at calculation time, when a visual or measure needs columns from both tables.

When does each happen? Joins happen during preparation (Power Query); relationships are defined during modelling (Model view), after loading.

When is a merge preferable to a relationship?

To add a column from another table, such as bringing a county name into a table that only held a county code (illustrative).
To flatten a snowflake by merging sub-dimensions into one dimension.
To filter rows by existence in another table, using anti joins.
To bring a key into a fact table when creating dimensions.

A relationship is preferable when tables hold different kinds of information (dimension and fact) and should stay separate while still filtering one another.

What can excessive merging cause?

Wider tables with more columns.
Greater redundancy, since descriptions repeat on every row (the flat-table problem).
Loss of dimensional structure, because facts and dimensions blend into one table and clean, reusable dimensions disappear.
Higher model complexity and maintenance cost, and row multiplication if the key is not unique on one side of the merge.

Why do fact and dimension tables stay separate? A star schema works because dimensions filter and facts summarise. Merging everything into one table abandons that structure. Keeping the tables separate and relating them stores each description once, keeps the fact table focused on its grain, and keeps DAX simple.

	Power Query merge (join)	Power BI relationship
Stage	Data preparation	Data modelling
Location	Power Query Editor	Model view
Physically combines data?	Yes: creates a merged table	No: tables stay separate
Output	A new table or extra columns	A filter path between tables
Controlled by	Join kind (six types)	Cardinality and filter direction
When applied	At refresh / load	At query time, when visuals and measures run
Best for	Cleaning, enriching, flattening, checking	Linking facts to dimensions

In short: join during preparation; relate during modelling.

24. From Model to Visualisation

Once data has been cleaned, modelled, related and calculated, reports can be built. In Report view, a chart type is selected and the fields to plot are added. Visuals are interactive: selecting part of one visual filters the others, including KPIs, and even the components of a bar are interactive.

Visual	Purpose
Bar chart	Comparing categories
Pie / Donut / Treemap	Part-to-whole relationships; treemaps suit many categories
Scatter	Relationship between two numeric variables
Card	Displaying a KPI or single headline value (typically a measure)
Map	Geographic data (bubble, filled and shape maps)
Gauge / KPI	Targets and comparison
Slicer	Filtering by user selection

These visuals depend on the earlier stages. The card shows a CALCULATE measure (Section 7.4); the slicer filters through a relationship (Sections 17 and 19); chart categories come from clean dimension columns (Section 5); and the Total Revenue all county card (Section 7.5) shows how ALL makes a KPI ignore slicers deliberately.

25. Publishing to Power BI Service

Key terms

.PBIX: the Power BI Desktop file.
Semantic model: the entire data ecosystem: tables, relationships and connections, measures, and the model itself.
Report: the visuals, graphs and dashboards that report users read to evaluate the data.
Workspace: a collaborative area in Power BI Service where a team creates, manages and stores Power BI content.
Power BI Service: the browser version of Power BI, used to create, share and consume content.

What publishing does

Publishing sends the report and the semantic model from the .pbix file to the selected workspace.

Power BI Desktop (.pbix)
          │
          ▼  Home → Publish → select destination
Power BI Service
          │
          ▼
      Workspace
          │
          ▼
Semantic model  +  Report
          │
          ▼
Sharing / Collaboration  (workspace roles and security)

The semantic model is the same model built in Model view, now hosted in the cloud, so earlier modelling decisions affect every report built on it.

Steps

Before publishing: sign in to Power BI from the Desktop app, then sign in to Power BI Service online.
Create a workspace: open the left pane → Workspaces and complete the details (name, description, image, domain, Power BI Pro where external users need access, contact list).
Publish: in Desktop, Home → Publish → select destination.
Open the workspace and refresh to see the report and semantic model.
Share: subscriptions allow a particular report page to be shared with an intended person; a report with multiple pages can also be shared as a link.

26. Workspace Roles and Security

Workspace roles

Role	Description
Admin	Controls the workspace and access
Member	Can collaborate and publish into the workspace
Contributor	Can create and change content, with limited access functions
Viewer	Can consume content without editing

Exact permission lists should be confirmed against current Microsoft documentation.

Row-level security (RLS)

Row-level security controls which rows of data a user can see based on their assigned role, to keep data secure. Rules specify who needs access to what, and roles can be created before the report is shared and tested with View as.

Define roles and their DAX filter rules in Power BI Desktop (Modeling → Manage roles).
Publish, then assign users or groups to each role in Power BI Service. A defined role is not enforced until users are assigned.
RLS applies to Viewers. Members of the Admin, Member and Contributor roles have edit permission on the semantic model, so RLS does not restrict them.
Test with View as before sharing.

RLS filters travel across relationships like any other filter and by default follow single direction, which is a further reason to keep filter directions simple.

27. Recommended Power BI Model

For a typical business intelligence project, a star schema is the recommended design. The reasoning is that it fits how Power BI queries models and the types of questions BI projects ask, not that it is universally superior.

Criterion	Flat table	Star schema	Snowflake
Query / report performance	Repeated text bloats wide tables	Matches how Power BI visuals query the model; recommended by Microsoft	Extra hops between tables
DAX simplicity	Simple at first; awkward as questions grow	Simple: measures on facts, slicers from dimensions	More relationships to reason about
Readability	Hard to tell what each column represents	Clear: facts in the centre, dimensions around	Larger diagram
Scalability	Poor	Good: add a dimension or fact	Good, with added complexity
Data redundancy	High	Low	Lowest
Maintainability	Same value fixed in many rows	Fix a description once	Good for hierarchies; heavier to manage
Ease of report creation	One place for everything, but limited filtering	Easy: dimension fields for slicers and axes, fact fields for values	Builders must understand the chain
Filter propagation	Nothing to manage	Predictable: dimension → fact	Passes through several tables
Model complexity	Lowest initially	Moderate	Highest

When another design is appropriate

Flat table: a small, one-off analysis with one subject, where building dimensions costs more than it returns.
Snowflake: when a sub-dimension is genuinely shared (for example, a county table used by both patients and clinics) or the source is already normalised and flattening is not worthwhile. Merging the sub-tables in Power Query to obtain a simpler star should be considered first.

Recommended relationship design

One fact table at the centre, with a grain that can be stated in one sentence (for example, one row per visit).
One dimension table per descriptive subject (patient, doctor, department, date, location), each with a unique primary key.
Foreign keys in the fact table matching those primary keys. Where no natural key exists, create an index column and carry it into the fact table.
Cardinality: one-to-many (1:*) from each dimension to the fact table. Avoid many-to-many unless unavoidable, using a bridge table where needed, and consider merging one-to-one pairs into a single table.
Filter direction: single, from dimension to fact. Use Both only for a specific, understood reason, to avoid ambiguous paths.
Active relationships on the main path; an inactive relationship only where a measure deliberately uses USERELATIONSHIP.
Referential integrity checked in Power Query using a right anti join (fact keys with no dimension match) before loading.

 DimDepartment   DimDoctor
        \          /
         \        /
 DimPatient ── FactVisit ── DimDate
   (1)──────────►(*)         (1)──►(*)      single-direction, 1:* from every dimension

(Illustrative design based on the hospital example, with a date dimension added.)

28. The Complete Workflow

 1. Understand the problem / question
              ↓
 2. Identify the data source
              ↓
 3. Import / connect data
              ↓
 4. Inspect the raw data
              ↓
 5. Clean and transform with Power Query
              ↓
 6. Understand the structure of the data
              ↓
 7. Build the data model
              ↓
 8. Create fact and dimension tables
              ↓
 9. Establish relationships
              ↓
10. Apply appropriate cardinality / filter direction
              ↓
11. Use DAX to calculate and analyse
              ↓
12. Create visualisations
              ↓
13. Build the report
              ↓
14. Publish to Power BI Service
              ↓
15. Share, secure and collaborate

How the stages connect

Steps 1–3: The business question determines which data is needed and where it lives. Import brings a copy into Desktop.
Steps 4–5: Imported data is rarely analysis-ready. Data types, blanks, errors and duplicates are resolved in Power Query, with Applied Steps recording each action. Joins are available here for combining or checking tables.
Steps 6–8: Clean data is organised: measurements into a fact table with a clear grain, descriptions into dimension tables, usually in a star schema.
Steps 9–10: Primary keys in dimensions link to foreign keys in the fact table through one-to-many, single-direction relationships. The tables then work as one model without being merged.
Step 11: DAX operates on top of the model. CALCULATE, FILTER and ALL depend on filters moving through relationships, so the better the model, the simpler the DAX.
Steps 12–13: Visuals present measures and fields and respond to the filters defined in the model.
Steps 14–15: The .pbix is published to a workspace as a semantic model and report, with roles and RLS governing who can do and see what.

29. Conclusion

Power BI is best understood as a chain of dependent practices rather than a set of separate features. Data enters through connections to sources such as Excel, CSV, SQL and the web. Its quality must be established in Power Query before it can be trusted. DAX then turns clean data into answers, but its effectiveness depends on a well-structured model: fact tables with a defined grain, dimension tables with unique keys, one-to-many relationships and carefully chosen filter directions. Joins and relationships are complementary tools applied at different stages: joins combine data while it is being prepared, and relationships connect tables once they are loaded. Reports, dashboards and publishing sit at the end of the chain, so their reliability reflects the care taken in every earlier stage.

For most business intelligence projects, a star schema built on clean, correctly typed data offers the best balance of performance, simplicity, readability and maintainability.

Excel Jumia

Velma Ketra Lukaya — Fri, 02 Oct 2026 10:13:30 +0000

Getting started with MS Excel for data analytics: From basics to data cleaning

Velma Ketra Lukaya — Wed, 09 Sep 2026 18:34:50 +0000

Microsoft Excel from Beginner to Data Analysis: What Each Part Does, When to Use It, and How to Find It

This guide has two purposes.

An introduction to Excel itself: the interface, the ribbons, the formula bar, formatting, formulas, charts, PivotTables, and dashboards.
Process to take raw data and turn it into something trustworthy and visual: clean it, organize it, calculate on it, analyze it, display it and report it.

That second thread matters because a beautiful dashboard built on messy data is still a bad analysis. So as we move through Excel's tools, we'll keep returning to the question: where does this fit in the data → clean → calculate → analyze → visualize → report pipeline?

1. Getting Started with Excel

Microsoft Excel is a spreadsheet application used to work with data. You can use it to:

Enter and store data
Organize datasets
Clean messy data
Perform calculations
Analyze information
Create charts
Build PivotTables
Create reports and dashboards

A useful mental model for the whole journey is:

Data → Clean → Calculate → Analyze → Visualize → Report

In practice, data is retrieved from an engineer, a system, another team, as raw figures, text, and numbers. Your job as the analyst is to clean it, decide what it means, and deliver insight from it. Before you can analyze anything, the data needs a reasonable structure and quality. Data cleaning is specifically about working on the quality and structure of the data before analysis begins

Opening Excel

When you open Excel, you'll usually land on a start screen with options such as:

Blank Workbook
Recent workbooks
Templates
Options for opening existing files

How to get there

Open Microsoft Excel.
From the start screen, select Blank Workbook.

You now have a new Excel workbook.

2. Understanding the Excel Interface

Before entering data, it helps to know what you're looking at. A workbook is made up of several major areas:

Title Bar
Quick Access Toolbar
Ribbon
Name Box
Formula Bar
Worksheet (columns, rows, cells)
Sheet tabs
Status Bar

2.1 Title Bar

Sits at the top of the window and shows the name of the currently open workbook for example, Book1 - Excel. Once you save, the title updates to whatever you named the file. It tells you at a glance which workbook and which file is currently active.

2.2 Quick Access Toolbar

Shortcuts to commonly used commands (Save, Undo, Redo, etc.), so you don't have to dig through the Ribbon for actions you use constantly.

[: Excel interface showing the Title Bar and Quick Access Toolbar]

3. The Ribbon

The Ribbon is the area that holds all of Excel's tools and toolbars, organized into tabs. Each tab groups related commands together.

Tab	Main Purpose
Home	Formatting and everyday editing
Insert	Tables, charts, PivotTables, shapes and other objects
Page Layout	Page and printing settings
Formulas	Functions and formula tools
Data	Sorting, filtering, validation, cleaning and analysis
Review	Comments, protection and reviewing
View	How the workbook is displayed

3.1 Home Tab

It holds tools for:

Font, font size, bold, italic, underline, font colour
Fill colour, borders
Alignment, Wrap Text, Merge & Center
Number formatting
Conditional Formatting
Sorting and filtering
Copy and paste

How to get there: Home → choose the relevant tool

4. Entering and Formatting Data

4.1 Cells

An Excel worksheet is made of cells which are the intersection of a column and a row. Columns are letters (A, B, C…), rows are numbers (1, 2, 3…). The first cell is A1. The cell in column C, row 5 is C5.

4.2 What is a Cell Reference?

A cell reference tells Excel exactly where a piece of information lives. A1 means column A, row 1. You use references inside formulas:

=A1+B1

This tells Excel to take the value in A1 and add the value in B1.

5. Rows, Columns and Ranges

Rows run horizontally and are numbered. A row commonly represents one record-one employee, one transaction, one observation.

Columns run vertically and are lettered. A column commonly represents one variable or field.

Employee ID	Name	Department	Salary
1001	Jane	Finance	80,000
1002	John	HR	70,000

Each row is an employee; each column is a field describing that employee.

Ranges are groups of cells. A1:A10 means cells A1 through A10. A1:D10 is a rectangular range from A1 to D10. Ranges matter because most Excel functions operate on them:

=SUM(A1:A10)

6. Worksheets and Workbooks

A workbook is the Excel file itself e.g., Employee Analysis.xlsx. A workbook can contain multiple worksheets, which you can think of as individual pages inside it — for example: Employee Data, Calculations, PivotTable, Dashboard.

Renaming a worksheet: Right-click the sheet tab → Rename (or double-click the tab).

7. Basic File Operations

7.1 Save

File → Save, or Ctrl + S on Windows.

7.2 Save As

File → Save As — creates a new copy or saves under a different name/format. Common file types:

.xlsx — standard Excel workbook
.xls — older Excel format
.csv — comma-separated values

A CSV is fine for simple tabular data, but it doesn't preserve Excel-specific features like multiple worksheets, formatting, PivotTables, or charts.

8. Formatting Your Data

Formatting changes how data looks without changing its underlying value. Good formatting makes a spreadsheet easier to read and easier to trust.

8.1 Font

Home → Font — type, size, bold, italic, underline.

8.2 Font Colour

Home → Font Color — useful for headers, warnings, important values, titles.

8.3 Fill Colour

Home → Fill Color — great for headers, KPI cards, and highlighting.

8.4 Borders

Home → Borders — bottom, top, left/right, all, or outside borders make table structure easier to see.

8.5 Alignment

Left, centre, right, top, middle, bottom. Text is conventionally left-aligned; numbers and dates right-aligned.

A useful diagnostic: if a column of numbers is sitting left-aligned instead of right-aligned, that's often a sign Excel is treating them as text, not numbers which will silently break SUM, AVERAGE, and other calculations on that column. Checking that a column's alignment is consistent throughout is a quick way to catch a data-type problem before it costs you a wrong total.

8.6 Wrap Text

Home → Alignment → Wrap Text makes long text wrap onto multiple lines within the same cell.

8.7 Merge & Center

Home → Merge & Center combines cells into one and centres the content — handy for titles like EMPLOYEE SALARY REPORT. Avoid merged cells inside your raw data table, though — they interfere with sorting, filtering, and analysis.

8.8 Number Formatting

Home → Number display 10000 as 10,000, 10,000.00, or as currency (e.g., KSh 10,000). You can also format as percentage, date, or time.

9. AutoFill and Flash Fill

AutoFill

Extends a pattern (January, February, March…) or copies a formula down a column. Enter the first value, select the cell, grab the small square at the bottom-right corner (the fill handle), and drag.

Flash Fill

Recognizes a pattern in your data and completes the rest automatically. For example, if a column has "John Smith", "Jane Doe", "Peter Kamau" and you start typing just the first names in the next column, Flash Fill can complete the pattern for you.

How to get there: Data → Flash Fill, or Ctrl + E

10. The Formula Bar

The Formula Bar shows the actual contents of the currently selected cell. If C2 contains =A2+B2, the Formula Bar shows that formula not the number it evaluates to. This is essential for viewing and editing long formulas, checking what's really stored in a cell, and telling a formula apart from its displayed result.

11. Writing Your First Formula

Every formula starts with =.

=10+20   → 30

The real power comes from cell references:

A1 = 10
B1 = 20
=A1+B1   → 30

12. Excel Arithmetic Operators

Operator	Meaning	Example
`+`	Addition	`=A1+B1`
`-`	Subtraction	`=A1-B1`
`*`	Multiplication	`=A1*B1`
`/`	Division	`=A1/B1`
`^`	Exponent	`=A1^2`

^ raises a number to the power of something — =10^3 means 10 raised to the power of 3.

13. Basic Excel Functions

A function is a built-in Excel formula designed for a specific task. Excel's basic functions split naturally into two families: aggregate functions, which combine numbers into a single computed value, and statistical/counting functions, which tell you something about the shape or completeness of your data.

Aggregate functions

SUM— adds values.

=SUM(A2:A10)

Use for total sales, total salary, total expenses, total marks.

AVERAGE-the mean.

=AVERAGE(A2:A10)

MEDIAN- the middle value when the data is sorted. Unlike the average, the median isn't dragged around by extreme values, which makes it a better "typical value" when a dataset has outliers.

=MEDIAN(A2:A10)

MODE- the most frequently occurring value.

=MODE.SNGL(A2:A10)

MIN / MAX — smallest / largest value in a range.

=MIN(A2:A10)
=MAX(A2:A10)

PRODUCT— multiplies a range of values.

=PRODUCT(A2:A5)

POWER— raises a number to a power (same idea as ^).

=POWER(10,3)   → 1000

SQRT— square root.

=SQRT(25)   → 5

Statistical / counting functions

These are especially useful for checking the completeness of a dataset before you analyze it:

COUNT— counts cells containing numbers only.

=COUNT(A2:A10)

COUNTA— counts all non-blank cells (text and numbers).

=COUNTA(A2:A10)

COUNTBLANK— counts only blank cells.

=COUNTBLANK(A2:A10)

The distinction between COUNT, COUNTA, and COUNTBLANK matters: running all three on the same column is a fast way to spot missing data before it silently distorts an analysis.

14. Relative and Absolute Cell References

Write =A2*B2 and drag it down, and Excel automatically adjusts it to =A3*B3, =A4*B4, and so on. These are relative references — they shift with the formula.

Sometimes you want a reference to stay fixed. Use $ to lock it an absolute reference:

=$A$1

This always points to A1, no matter where the formula is copied. For example, if A1 holds a tax rate of 16%:

=B2*$A$1

Copied down the column, $A$1 stays locked while B2 still adjusts to B3, B4, etc.

15. Intermediate Functions

Once basic formulas feel comfortable, you can start asking Excel to make decisions and find information across your dataset the core of real analysis.

16. Conditional (Aggregate) Functions

These calculate something based on one or more conditions: COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS.

COUNTIF — counts cells matching one condition.

=COUNTIF(B2:B100,"Female")

"How many female employees are in the dataset?"

COUNTIFS — counts cells matching multiple conditions (which can span different columns).

=COUNTIFS(B2:B100,"Female",C2:C100,"Finance")

"How many female employees work in Finance?"

SUMIF — sums values based on one condition.

=SUMIF(B2:B100,"Finance",D2:D100)

"Total salary paid to employees in Finance."

SUMIFS — sums with multiple conditions.

=SUMIFS(D2:D100,B2:B100,"Finance",C2:C100,"Female")

AVERAGEIF / AVERAGEIFS- average based on one or more conditions.

=AVERAGEIF(B2:B100,"Finance",D2:D100)

A quick way to remember the argument order: SUMIF and AVERAGEIF start with the condition range, then the criteria, then the range to calculate. SUMIFS and AVERAGEIFS flip that they start with the range to calculate, followed by all of the condition range/criteria pairs.

17. Logical Functions

Logical functions let Excel automate decision-making: determining bonuses, matching customer records, checking eligibility, or classifying values into bands like High, Medium, and Low.

=IF(logical_test, value_if_true, value_if_false)
=IF(B2>=50,"Pass","Fail")

AND — TRUE only if every condition is true. Reach for AND when you're combining conditions across different columns that must all hold e.g., married, male, and over 50.

=AND(A2>50,B2>50)

OR — TRUE if at least one condition is true. Reach for OR when you're checking multiple possible values within the same column e.g., department is Finance or HR.

=OR(A2="Finance",A2="HR")

Combining IF with AND/OR

=IF(AND(B2>=50,C2>=50),"Pass","Fail")

"Pass only if both B2 and C2 are at least 50."

Nested IF — chains multiple conditions to build categories:

=IF(B2>=80,"High",IF(B2>=50,"Medium","Low"))

80+ → High
50–79 → Medium
Below 50 → Low

A nested IF is read from the top down: Excel checks the first condition, and if it's false, falls through to the next IF inside it. This matters when you're deciding where a blank value should land, put the ISBLANK check first, so blanks get routed to their own outcome instead of silently falling into the last category.

ISBLANK — checks whether a cell is empty. Useful for handling incomplete datasets, often combined with IF:

=IF(ISBLANK(A2), "No data", IF(A2>=50,"Pass","Fail"))

18. Date Functions

Dates are everywhere in real-world datasets, and they're a frequent source of cleaning headaches.

TODAY() — today's date. Useful for tracking deadlines, calculating age, flagging overdue tasks.

NOW() — current date and time.

YEAR() / MONTH() / DAY() — extract the year, month, or day from a date.

DATEDIF(start, end, "unit") — the difference between two dates, e.g. =DATEDIF(A2,B2,"Y") for complete years.

NETWORKDAYS(start, end) — working days between two dates, automatically excluding weekends. Useful for project timelines, employee working days, turnaround time.

Dates are also one of the most common places data quality breaks down. Watch for hire-date entries like 30/02/2019 (there's no February 30th) or 2020/13/05 (there's no 13th month)
These are invalid dates that need to be fixed at the source before any date function can be trusted on that column.

19. Text Functions

Text functions are essential for cleaning datasets. They apply specifically to text-type data, and are typically used to standardize text, remove extra spaces, extract part of a string, or combine text from multiple cells.

UPPER / LOWER / PROPER — change case.

=UPPER(A2)    "nairobi" → "NAIROBI"
=PROPER(A2)   "john kamau" → "John Kamau"

TRIM — removes unnecessary/extra spaces, especially useful on imported data.

=TRIM(A2)

LEFT / RIGHT — extract characters from the left or right side of a string.

=LEFT(A2,3)
=RIGHT(A2,3)

LEN — counts the number of characters in a string. Handy for calculating the length of a substring before extracting it, or for spotting entries that are suspiciously short or long.

=LEN(A2)

MID — extracts characters from the middle of a string. If A2 contains EMP-10545:

=MID(A2,5,5)   → "10545"

FIND — returns the position of one piece of text within another.

CONCAT — combines text from multiple cells.

=CONCAT(A2," ",B2)

If A2 = "John" and B2 = "Kamau", the result is "John Kamau".

A few real applications

Phone numbers with country codes: use LEFT to pull out a country code (e.g. +254), and combine it with a logical function (IF) to assign a country name based on the code you found.
Employee IDs like EMP-10945: use MID to pull the numeric part out on its own.
Generating email addresses: combine LOWER and CONCAT to build a standardized address from a first name and last name, e.g.:

  =LOWER(CONCAT(A2,B2,"@company.com"))

This is a common cleaning/standardization exercise: taking two separate text fields and deriving a consistent third field from them.

20. Lookup Functions

Lookup functions let Excel search for something and pull back related information essential once your data is spread across multiple tables.

VLOOKUP — searches for a value in the first column of a range and returns a value from another column in that same range.

=VLOOKUP(lookup_value, table_array, column_index, FALSE)
=VLOOKUP(A2,$F$2:$I$100,2,FALSE)

Two things to keep in mind: the column_index is a number, not a column letter, and the lookup value must be located in the first column of the range you're searching
That's VLOOKUP's main limitation.

Example: Table 1 has Employee ID → Name. Table 2 has Employee ID → Salary. VLOOKUP lets you pull salary into Table 1 using Employee ID as the shared key.

XLOOKUP — a more flexible successor available in newer Excel versions. It can search in either direction and doesn't require the lookup column to be first it can traverse data horizontally without VLOOKUP's positional restriction.

=XLOOKUP(what_you_are_looking_for, where_to_search, what_to_return)

HLOOKUP

HLOOKUP (Horizontal Lookup) searches for a value across the top row of a table and returns a corresponding value from a specified row below it. It is useful when data is arranged horizontally rather than vertically.

The basic syntax is
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

INDEX + MATCH — a two-function combination that does what VLOOKUP does, without the "must be in the first column" restriction. MATCH finds the position of what you're looking for; INDEX returns the value sitting at that position.

=INDEX(what_you_want_to_return, MATCH(lookup_value, lookup_array, 0))

Because INDEX and MATCH are separate, the lookup value doesn't need to be in the first column of anything, you just point MATCH at wherever it lives.

21. Organizing Your Data

Once you can calculate and manipulate values, the next step is making sure the dataset itself is properly organized, this is where data cleaning and structure become the priority. Common problems to watch for:

Missing fields
Mixed data types (e.g. numbers stored as text)
Inconsistent labeling (e.g. "IT" and "I.T" treated as different categories)
Inconsistent capitalization or letter case
Inconsistent date patterns, or outright invalid dates
Pseudoblanks
Duplicate records
Outliers

22. Data Cleaning

Data cleaning means preparing data so it can be analyzed reliably. Imagine a Department column containing:

Finance
finance
FINANCE
Fin.

Excel treats these as four different values unless you standardize them first.

A suggested cleaning order

There's no single required sequence, but working through checks in roughly this order catches problems before they compound:

Cell size and display — column widths, and freezing header rows/columns so you don't lose context while scrolling.
Data type consistency — check that a column is entirely numbers, entirely text, or entirely dates. A column that "looks" numeric but has left-aligned entries mixed in with right-aligned ones usually has a hidden text/number mismatch.
Sort — sorting a column often surfaces inconsistent entries, blanks, or oddities that were easy to miss row by row.
Find and Replace — start with the easiest cases (often numerical or clearly-patterned text), confirm your changes by re-sorting afterward, and make sure you're being column-specific rather than replacing across the whole sheet by accident.
Remove duplicates — after the above steps, so you're not duplicating already-flawed data.
Conditional formatting — use it last, as a visual QA pass to spot what's left.

22.1 Missing Values

Possible approaches:

Delete the entire row, if a large amount of important data for that record is missing.
Leave the cell blank.
Replace it with a clear label like "Unknown."
Fill it using an appropriate statistic: mean, median, or mode, when that's contextually reasonable (for example, filling a missing HR-related field with the mode if you're confident about the typical value for that group).

The right choice depends on the situation, not a fixed rule Always ask what a blank means in that specific column before deciding how to treat it.

22.2 Pseudoblanks

A cell isn't always technically empty even when it represents missing information. Watch for:

None
N/A
Unknown
-

These pseudoblanks should generally be converted to one consistent representation (e.g., "Unknown") so they're treated the same way during analysis.

That said, be careful: in some datasets a genuinely blank cell and a cell marked "None" don't mean the same thing. For example, in a training-attendance column, a blank might mean "no data collected," while "None" might specifically mean "attended zero trainings" which is a real, meaningful value.
Don't collapse the two automatically; check what each one is actually representing first.

22.3 Find and Replace

Home → Find & Select → Replace, or Ctrl + H.

Use case: your dataset contains Nairobi, NBI, Nrb referring to the same place
Find and Replace lets you standardize them quickly. As above, do this column by column and re-check your work rather than assuming a global find-and-replace is safe.

22.4 Removing Duplicates

Duplicate records distort totals, averages, and counts, but don't delete every row that merely looks similar. First decide which field should actually be unique: Employee ID, phone number, email, patient ID, etc.

A useful QA step before deleting anything: use Conditional Formatting to highlight duplicates based on that unique identifier first (e.g., duplicate phone numbers or duplicate Employee IDs), then look at what's flagged.

If every field in the flagged rows matches, it's a true duplicate hence safe to delete.
If only the unique identifier matches but other fields differ, investigate before deleting, it may be a genuine data-entry issue rather than a duplicate. Once you've resolved it, filter to confirm the duplicates are gone, and clear the conditional formatting rule so it doesn't linger and confuse the next person to open the file.

How to actually remove duplicates:

Select your dataset.
Data → Remove Duplicates.
Choose the column(s) that determine uniqueness.
Click OK.

22.5 Outliers

An outlier is a value unusually different from the rest of the data. If employee ages are 24, 27, 31, 29, 26, 4 that 4 shouldn't be deleted immediately. It could be a data entry error, a genuine (if unusual) observation, or a special case. Investigate before ruling it out, without changing the data itself in the process of checking.

This is also where central tendency measures earn their keep: the mean is pulled toward outliers, but the median is largely unaffected by them, which is why median is often the safer "typical value" to report alongside, or instead of, the mean when a dataset has extreme values.

23. Conditional Formatting

Conditional Formatting changes how cells look based on defined conditions without changing the underlying data itself.

How to get there: Home → Conditional Formatting

Highlight Cells Rules — greater than, less than, equal to, between (inclusive of the borderline numbers), contains specific text or a subset of a word.
Top/Bottom Rules — top 10, bottom 10, top %, bottom %, or a top-N like "top 5 earners."
Colour Scales — apply a gradient of colour based on magnitude, e.g. a 0–10 scale shading from white toward dark green as values increase.
Data Bars — an in-cell bar sized to the value.

Conditional formatting is particularly good for spotting patterns, high/low values, missing information, and potential problems at a glance for example, quickly seeing the top 3 earners in a particular age group in a large dataset.

24. Sorting Data

Sorting rearranges data by a chosen variable: text (A→Z or Z→A), numbers (smallest→largest or reverse), or dates (oldest→newest or reverse).

How to get there: Data → Sort

You can also perform multi-level sorting where one sort is applied after another. For example: sort by Department, then within each department, sort by Gender.

25. Filtering Data

Filtering temporarily hides rows that don't meet your criteria Nothing is deleted, and you can always return to the full dataset afterward.

How to get there: Data → Filter

Once enabled, dropdown arrows appear in your headers, offering:

Text filters — equals, does not equal, begins with, ends with, contains, or a specific value anywhere in the text.
Number filters — greater than, less than, equal to, or between (inclusive of the boundary values).
Date filters — equal to, before, after, between.

26. Freeze Panes

Large datasets get hard to navigate once your headers scroll out of view. Freeze Panes keeps selected rows or columns visible while you scroll through the rest.

How to get there: View → Freeze Panes

For example, if headers are in row 1, freeze the top row so it stays put no matter how far down you scroll.

27. Excel Tables

An Excel Table turns a plain range into a structured, self-maintaining table; a more structured way of formatting your data.

How to get there

Select your dataset.
Insert → Table.
Confirm your table has headers.
Click OK.

Shortcut: Ctrl + T

Tables automatically give you filters, structured formatting, automatic expansion as you add rows, structured references, and much easier PivotTable/chart source management. A good dataset should generally have one header row, consistent columns, no unnecessary blank rows, and no merged cells inside it.

28. Data Validation

Data Validation controls what a user can type into a cell. Its most common use is a dropdown list. Instead of letting people freely type "Finance," "finance," "FINANCE," or "Fin." give a fixed list: Finance, HR, Marketing, IT.

How to get there: Data → Data Validation → Allow → List, then enter the permitted values or point to a range containing them.

This is one of the cheapest ways to prevent inconsistent labeling before it ever enters your dataset.

29. Charts: Turning Data into Visual Information

Once your data is clean and analyzed, it's time to visualize it. The guiding principle: choose the chart based on the question you're trying to answer, not the other way around.

Column and Bar Charts

Best for comparing categories i.e "Which department has the highest total salary?"

How to get there: select the data → Insert → Column or Bar Chart

For long category names, consider whether a clustered or stacked layout communicates the comparison more clearly.

Pie and Donut Charts

Pie charts show parts of a whole "What percentage of employees belong to each department?"
Donut charts work similarly with a hollow centre.
Use both only with a small number of categories: once you're at roughly six or seven categories or more, they become hard to read. At that point, consider another chart

Line Charts

Best for showing a trend over time how a value changes across months, for example.

How to get there: Insert → Line Chart

Area Charts

Similar to a line chart, but the area beneath the line is shaded It useful when you want to emphasize magnitude or volume rather than just direction.

Scatter Charts

Used to examine the relationship between two numeric variables, "Is there a relationship between hours studied and exam score?" Plot one variable on each axis: select the two variables directly and insert the chart.

When describing what a scatter chart shows, it helps to name the relationship along a simple scale: no relation, weak, or strong and whether it's positive or negative.

Combo Charts

Combines two chart types in one e.g., one category shown as a column and another as a line, such as Sales (columns) against Profit Margin (line). Useful for comparing two related measures on different scales.

Formatting a Chart

Once created, click the chart to reveal chart-specific tabs (Chart Design, Format) where you can adjust:

Axes and their titles
Chart Title
Horizontal / vertical axis
Legend
Data labels
Data table
Error bars
Trendline
Gridlines
Chart style

Whatever charts you build, try to keep a consistent visual theme with the rest of your dashboard this matters especially for charts with visually "light" elements, like a donut chart's hollow centre.

30. PivotTables

A PivotTable is one of Excel's most powerful tools: a summary table that lets you summarize, analyze, and present data interactively by dragging and dropping fields to spot patterns and trends without changing the original data. It's the foundation for most reports and dashboards.

Instead of manually calculating "total salary by department" across 10,000 rows, you build a PivotTable and let Excel do it.

30.1 Preparing Data for a PivotTable

Before building one, make sure:

Text blanks are filled (don't leave meaningful gaps).
All columns have headers.
There's nothing extra to the right of, or below, the dataset.
The original data structure is preserved.

A good structure looks like:

Employee ID	Name	Department	Gender	Salary
1001	Jane	Finance	Female	80,000
1002	John	HR	Male	70,000
1003	Mary	Finance	Female	90,000

30.2 Creating a PivotTable

Click anywhere inside your dataset.
Insert → PivotTable.
Choose your data source.
Choose where you want the PivotTable placed.
Click OK.

30.3 The Four PivotTable Areas

Rows — determines what categories appear vertically. E.g., Department → Rows produces Finance, HR, IT, Marketing.
Values — the numbers Excel calculates. E.g., Salary → Values gives totals per department.
Columns — breaks the values down further by another category. E.g., Department → Rows, Gender → Columns, Salary → Values lets you compare salary across department and gender at once.
Filters — restricts the whole PivotTable to a subset, e.g. Department = Finance only.

Caution: it doesn't really matter which column you drag into Values — apart from the fact that it must be a numeric column, and ideally one without blanks. Blanks in the values column can distort counts and totals, so pick a clean numeric field.

30.4 Grouping Data in PivotTables

Sometimes you want bands instead of individual values — e.g., age ranges (18–25, 26–35, 36–45) instead of every single age. Right-click a value inside the PivotTable and choose Group.

31. PivotCharts

A PivotChart is a chart connected directly to a PivotTable rather than manually selecting data for a chart, the chart is linked to whatever the PivotTable is currently showing. This is especially useful for dashboards: change the PivotTable, and the chart updates with it.

How to get there

Click inside the PivotTable.
Insert → PivotChart.
Choose the chart type.
Click OK.

32. Slicers

Slicers are a visual, click-based way to filter PivotTables instead of opening a dropdown and picking "Finance," you click a button labelled Finance.

How to get there: click your PivotTable → PivotTable Analyze → Insert Slicer, then choose the field to filter by (Department, Gender, Location, Year, etc.).

Connecting one slicer to multiple charts: by default, a slicer only controls the PivotTable it came from. To make one slicer filter several PivotTables/PivotCharts at once (which is what you want on a dashboard), right-click the slicer, choose Report Connections, and select every PivotTable that should respond to it. Without this step, clicking a slicer on your dashboard may only update one chart while the rest stay frozen which defeats the point of an interactive dashboard.

33. Building an Excel Dashboard

A dashboard is a single-page visual report that summarizes all the key information at a glance as opposed to a longer, more detailed report. It should be interactive and updatable via slicers, and typically includes a title, KPIs, and a set of charts chosen specifically for what you've analyzed.

┌─────────────────────────────────────────────┐
│             SALES DASHBOARD                 │
├─────────────┬─────────────┬─────────────────┤
│ TOTAL SALES │ CUSTOMERS   │ AVG. SALE       │
│  KSh 5.2M   │    1,240    │ KSh 4,200       │
├─────────────┴─────────────┴─────────────────┤
│                                             │
│              SALES TREND                    │
│                                             │
├───────────────────────┬─────────────────────┤
│ SALES BY DEPARTMENT   │ SALES BY REGION     │
│                       │                     │
└───────────────────────┴─────────────────────┘

33.1 Setting Up the Dashboard Sheet

A simple way to start: create a new sheet, select the whole sheet (Ctrl + A), and fill it with a background colour that matches your intended theme, before placing anything on it.

33.2 Dashboard Header

Create a header using a shape: Insert → Illustrations → Shapes → Rounded Rectangle is a common, clean choice.

From there, adjust fill colour, outline, add your title text, choose a font, and centre it.

33.3 KPI Cards

KPIs (Key Performance Indicators) are the numbers you want someone to understand instantly e.g. Total Sales, Customers, Average Sale, and similar headline figures. Place these directly below the header, since they're the first thing a viewer should see.

Worth knowing: in plain Excel, KPI cards are typically static text/values you update manually or via formula, they don't automatically shift when a slicer is clicked, the way they would in a tool like Power BI.
Genuinely interactive KPIs that respond live to slicer clicks are more of a Power BI capability; in Excel, plan your dashboard with that limitation in mind.

33.4 Adding Charts to the Dashboard

Move your finished charts and PivotCharts onto the dashboard sheet. A typical dashboard mixes:

KPI cards
A trend chart (e.g., sales over time)
A breakdown by category (department, region, etc.)
A comparison or performance chart

Choose the charts based on the questions your data is actually answering not just because you happen to have built them.

33.5 Making the Dashboard Interactive

A department slicer with buttons for Finance, HR, IT, Marketing (or a year slicer with 2024, 2025, 2026) lets a viewer click through the data themselves instead of reading a static snapshot.

Remember to connect each slicer to every relevant PivotTable via Report Connections (see Section 32) so the whole dashboard responds together, not just one chart.

33.6 Dashboard Design

A dashboard isn't "every chart you've ever made" it's a small, curated set that communicates quickly. Keep it:

Clean and uncluttered
Consistent — one visual theme across header, KPI cards, charts, fonts, and colors
Well-aligned
Focused on the handful of numbers and trends that actually matter

36. Power Query: Automating the Cleaning Step

Everything covered so far e.g. removing duplicates, fixing pseudoblanks, standardizing text, splitting columns — can also be done manually, once.
Power Query is Excel's built-in tool for building a repeatable data-cleaning and transformation process. Instead of manually editing cells, you record a series of transformation steps once, and Power Query replays those exact steps automatically every time you refresh the data even if the underlying data has changed or grown.

This is where Power Query sits in the bigger workflow: it belongs at the Clean and Organize stages, before you ever touch a formula, PivotTable, or chart. Think of it as an automated, auditable version of the manual cleaning process from earlier in this guide.

36.1 Why Use Power Query Instead of Manual Cleaning?

Repeatability — clean a dataset once, and reapply the same steps to next month's file in seconds.
Traceability — every transformation is recorded as a visible step, so you (or anyone else) can see exactly what was done to the data and in what order.
Non-destructive — Power Query works on a copy of the data pulled into its own editor; your original source file or sheet is untouched.
Handles messier sources — it can pull data from CSVs, folders full of files, databases, and web pages, not just a worksheet already sitting in Excel.

36.2 Getting Data Into Power Query

How to get there: Data → Get Data (or Data → From Table/Range if your source is already an Excel Table on the same sheet)

Common source options include:

From Table/Range — turns existing worksheet data into a query
From Text/CSV
From Folder — combines multiple files in a folder into one dataset
From Web
From Database (SQL Server, Access, etc.)

Once you select a source, Power Query opens the Power Query Editor in a separate window.

36.3 The Power Query Editor

The Editor has three areas worth knowing:

Preview pane (centre) — shows your data as it currently looks, after whatever steps have been applied so far.
Queries pane (left) — lists every query you've built in this workbook.
Applied Steps pane (right) — the recorded, ordered list of every transformation you've made to this query.

The Applied Steps panel is the heart of Power Query. Each transformation e.g. removing a column, filtering rows, changing a data type, appears as its own named step. You can click any step to see the data exactly as it looked at that point, reorder steps, or delete one without affecting the others. This is what makes Power Query auditable in a way manual cleaning never is.

36.4 Core Transformations

Most of the cleaning tasks covered earlier in this guide have a direct Power Query equivalent but recorded as a repeatable step instead of a one-time edit.

Changing a column's data type
Click the data-type icon in a column header, or Transform → Data Type. This is worth doing early and deliberately a column silently stored as text instead of number is one of the most common causes of broken calculations downstream.

Removing duplicates
Select the column(s) that should be unique → Home → Remove Rows → Remove Duplicates. Same logic as before: decide which field defines a duplicate before removing anything.

Removing or keeping specific rows
Home → Remove Rows lets you remove blank rows, error rows, duplicates, or the top/bottom N rows. Home → Keep Rows does the inverse.

Filtering
Click the dropdown arrow in a column header, just like a worksheet filter but here, the filter becomes a permanent, recorded step rather than a temporary view.

Splitting a column
Transform → Split Column → By Delimiter (or by number of characters). Useful for pulling apart something like EMP-10545 into a prefix and an ID number, or splitting a full name into first and last name — the Power Query equivalent of the LEFT/RIGHT/MID work from earlier.

Merging or combining columns
Transform → Merge Columns combines multiple columns into one — the Power Query equivalent of CONCAT useful for building something like a standardized email address as a repeatable step rather than a manual formula.

Trim, Clean, and Case changes
Transform → Format offers Trim (remove extra spaces), Clean (remove non-printable characters), and UPPERCASE/lowercase/Capitalize Each Word — direct equivalents of TRIM, UPPER, LOWER, and PROPER.

Replacing values
Transform → Replace Values — the Power Query equivalent of Find and Replace, but applied as a saved, reusable step rather than a one-off edit. This is a clean way to handle pseudoblanks: replace "N/A", "None", and "-" with a single consistent value like "Unknown" in one recorded step.

Filling down blanks
Transform → Fill → Down fills a blank cell with the value from the cell above it and is useful for datasets where a category label is only entered once at the top of a group and left blank for the rows beneath it.

36.5 Combining Data From Multiple Sources

Append Queries — stacks two or more queries with matching columns on top of each other, like combining January, February, and March exports into a single table.
Home → Append Queries

Merge Queries — joins two queries together based on a matching column, similar in spirit to VLOOKUP or INDEX/MATCH, but built to work reliably across entire tables rather than one lookup at a time.
Home → Merge Queries

You'll choose a join type
The most common is a Left Outer join, which keeps every row from your main table and pulls in matching data from the second table wherever it exists.

36.6 Loading the Result Back Into Excel

Once your Applied Steps produce clean, well-structured data, you close out of the Editor and choose where it should land.

How to get there: Home → Close & Load (or Close & Load To… for more control)

Options typically include:

Load as a Table on a worksheet
Load as a PivotTable Report directly
Load Only Create Connection — keeps the query available as a source for other queries or PivotTables, without dumping the data onto a sheet

36.7 Refreshing the Query

When new data arrives you don't repeat any manual cleanup. You just:

Data → Refresh All

Power Query reruns every recorded step, in order, against the new data, and the cleaned result updates automatically ready to feed straight into your PivotTables, charts, and dashboard.

Where Power Query Fits in the Bigger Picture

Updating the full workflow from earlier in this guide, Power Query slots in right after data enters Excel and before manual analysis begins:

34. The Complete Excel Workflow

Putting it all together, the journey from a blank workbook to a finished dashboard follows this path:

OPEN EXCEL
     ↓
CREATE WORKBOOK
     ↓
ENTER DATA
     ↓
FORMAT DATA
     ↓
CLEAN DATA
     ↓
ORGANIZE DATA
     ↓
WRITE FORMULAS
     ↓
USE FUNCTIONS
     ↓
SORT & FILTER
     ↓
ANALYZE DATA
     ↓
CREATE PIVOTTABLES
     ↓
CREATE CHARTS / PIVOTCHARTS
     ↓
ADD SLICERS
     ↓
BUILD DASHBOARD
     ↓
REPORT INSIGHTS

The mindset worth carrying forward: don't start with a chart start with good data. A beautiful dashboard built on poorly structured data is still a poor analysis.

35. Excel Cheat Sheet: Where Do I Find It?

I want to…	Go to…
Change font	Home → Font
Change font colour	Home → Font Color
Change cell colour	Home → Fill Color
Add borders	Home → Borders
Wrap text	Home → Wrap Text
Merge cells	Home → Merge & Center
Change number format	Home → Number
Highlight values	Home → Conditional Formatting
Find/replace text	Home → Find & Select → Replace
Sort data	Data → Sort
Filter data	Data → Filter
Remove duplicates	Data → Remove Duplicates
Flash Fill	Data → Flash Fill
Data Validation	Data → Data Validation
Freeze rows/columns	View → Freeze Panes
Create a Table	Insert → Table
Create a PivotTable	Insert → PivotTable
Create a PivotChart	Insert → PivotChart
Create a normal chart	Insert → Charts
Add a slicer	PivotTable Analyze → Insert Slicer
Connect a slicer to multiple PivotTables	Right-click slicer → Report Connections
Create shapes for a dashboard	Insert → Shapes
Find formulas/functions	Formulas

Finally

Learning Excel isn't about memorizing hundreds of buttons. It's about understanding what problem each tool solves and where that problem sits in the bigger picture of turning raw data into a decision someone can act on.

If you remember only one framework from this guide, remember:

Enter — put your data into Excel.
Format — make the information readable.
Clean — fix missing values, inconsistent labels, duplicates, invalid dates, and other data-quality problems.
Organize — use tables, sorting, filtering, validation, and freeze panes.
Calculate — use formulas and functions like SUM, AVERAGE, MEDIAN, IF, COUNTIF, and XLOOKUP.
Analyze — use conditional functions, lookups, and PivotTables to actually answer questions.
Visualize — pick the chart that fits the question, not just the one that looks nicest.
Report — bring KPIs, charts, PivotTables, and slicers together into one interactive dashboard.

That progression is what takes you from someone who can type numbers into Excel to someone who can use Excel to clean, analyze, and communicate insight from data.

The Git-Github workflow- A simplified cheat sheet

Velma Ketra Lukaya — Sun, 23 Aug 2026 20:34:59 +0000

Introduction

Git and Github are tools that work together in managing and storage of your projects
Git is a version control system that runs on your computer and keeps track of changes made to you file and folder locally, while GitHub is an online platform where the Git repositories can be stored and shared as backup and to collaborate with other people
For the purpose of understanding, I created a project called Kenyan hospital health records on my local computer

General workflow

The overall process is
-Connecting your computer to GitHub using the SSH

Configuring your user name and email
Testing the connection

-Using Git to move my project from my Local computer to GitHub

Initialising Git
Staging the files
Committing staged files
Creating a Github Repository
Connecting the local repository to GitHub
Pushing the project to GitHub
Pulling changes from GitHub to local computer

Part 1: Connecting Git to GitHub using SSH

Before creating a new SSH, check whether your computer has one by opening the terminal and running:
ls -al ~/.ssh

If you do not already have it create one using ssh-keygen -t ed25519 -C "your_email@example.com"
which would create a public and private key to be kept private and in secret

Display and copy the public key to be added to GitHub settings and add a new SSH key, give the key a name e.g My computer, paste your key and click add SSH key

Check your Git configuration by using git config --global --list and test whether your computer communicates with GitHub by running ssh -T git@github.com

This confirmed that my computer was successfully connected to GitHub through SSH.
Now you are ready to push your local project onto GitHub

Part 2: Creating folders and files on your local computer

Using terminal, run pwd to identify the exact location
Thereafter, use the command cd to move into the desired location eg cd Desktop to move in to the desktop. You can use the same command to move into a directory of choice eg Documents
Within the desktop, use mkdir which instructs the terminal to make a directory inside your desktop
Cd into that folder and create folders of choice using mkdir and files of choice using touch e.g. touch README.md

To confirm, use ls to list out all components of your folder.

If you have any changes to make onto your README.md file , use
echo "# Your description in makrdown langage" > README.md
If you use **> it overwrites everything un the README.md file while >> adds additional information into the README.md as such echo "Additional information" >> README.md without erasing existing information
Alternatively nano README.md and make all changes using markdown language, save and revert to terminal.
To confirm changes made, use cat README.md

Part 3: Initialise GIT

Initialise the git repository

Run git init. This instructs Git to start tracking your project and creates a hidden .git folder inside your project

Check Git status

Run git status which is important to inform you about hat is happening inside your repository
At this stage, Git will show you that there are files that are not being tracked yet

Stage the files

Run git add . to add all files to the staging area and if you check status you will see that git has begun tracking your files
as such

Committing the files

Using the command **git commit -m "describe the commit"

Part 4: Create the GitHub repository

In your GitHub account, create a new GitHub repository and name it similarly to the local project
This creates an online repository
At this point you now have a local repository and an online one but they are still not conected
On your GitHub repository you will have a SSH/HTTPS repository URL which you will use to tell your local repository where your GitHub repository is located
copy the key and on terminal add
git remote add origin *SSH repository URL*

Check that the connection had been saved using git remote -v
where you will see such results

Set the branch of your project to main by using git branch -M main
Confirm the branch of your project is set to main by using the command git branch

Once at this stage, you are ready to push the committed projects onto git hub

On the terminal, use git push -u origin main
The command means:

git push → send my commits to a remote repository
origin → the GitHub repository I connected earlier
main → the branch I want to send
-u → remember this connection for future pushes

On Github confirm that the project is now successfully uploaded

Henceforth

Check any changes you have made using git status*
Stage the changes using git add .
Commit the changes using git commit -m "Describe your changes"
Push the changes to GitHub using git push -u origin main"

If you make changes on git hub
you can simply git pull and the changes made in your online repository will be synchronized to you local repository. This fetches the changes and integrates them into your current branch

In a nutshell

Git runs on my computer and tracks the history of my project.
GitHub stores my Git repository online.
SSH provides the secure way for my computer to authenticate with GitHub.