Nabid In Motion Nabid In Motion

Study interactive :: Progress tools open in the Study Hub reader.

Microsoft Excel for Data Analysis

Using Microsoft Excel for data analysis, from basics to advanced techniques.

Table of Contents

Excel Fundamentals and Cell Management

Introduction, Installation, and Interface Overview

What is Excel? Microsoft Excel is a spreadsheet application used for data organization, analysis, and visualization. It's essential for data analysis, financial modeling, and business intelligence.

Installation:

Key Interface Components:

Cell Operations: CRUD, Naming, Formatting, and Selection

CRUD Operations (Create, Read, Update, Delete):

Create (Enter Data):

Read (View Data):

Update (Edit Data):

Delete:

Cell Naming:

// Create named range
1. Select cell/range
2. Name Box (left of formula bar) → Type name → Enter
3. Or: Formulas → Define Name

// Use named range in formulas
=SUM(Sales)  // Instead of =SUM(A1:A10)

Cell Formatting:

Selection Techniques:

Row and Column Management, Auto-adjust, and Shortcuts

Row Operations:

Column Operations:

Productivity Shortcuts:

Number Formatting and Custom Formats

Number Formats:

Custom Number Formats:

// Format codes:
0 = Display digit (force zero if needed)
# = Display digit (hide zero)
? = Display digit (add space for alignment)
, = Thousand separator
. = Decimal point
% = Percentage
$ = Currency symbol
"text" = Display text
[Color] = Color code (Red, Blue, Green, etc.)

// Examples:
#,##0.00        // 1,234.56
$#,##0.00       // $1,234.56
0.00%           // 12.34%
#,##0.00 "USD"  // 1,234.56 USD
[Red]#,##0.00   // Red for negative

Apply Custom Format:

  1. Select cells
  2. Right-click → Format Cells → Number tab
  3. Select "Custom"
  4. Enter format code

Flash Fill and Smart Data Entry

Flash Fill (Excel 2013+): Automatically fills data based on pattern recognition.

Example:

// Column A: "John Smith"
// Column B: Type "John" in first cell
// Press Ctrl + E (Flash Fill)
// Excel fills: "John" for all rows

When to Use:

Enable Flash Fill:

Smart Data Entry:

Efficient Navigation and Productivity Shortcuts

Navigation Shortcuts:

Editing Shortcuts:

Selection Shortcuts:

Freeze Panes, Split View, Zoom, and Page Layout

Freeze Panes: Keep rows/columns visible while scrolling.

Steps:

  1. Select cell below/right of what to freeze
  2. View → Freeze Panes → Freeze Panes
  3. Or: Freeze Top Row / Freeze First Column

Split View: Divide window into panes.

Steps:

  1. View → Split
  2. Drag split bar to adjust
  3. View → Split again to remove

Zoom:

Page Layout:

Excel Basics

Understanding Excel Interface

Key Components:

Cell References

Relative References (A1):

Absolute References ($A$1):

Mixed References (A$1 or $A1):

Example:

// Calculate percentage of total
=B2/$B$10  // B2 is relative, $B$10 is absolute

Basic Operations

Entering Data:

Selecting Ranges:

Copying and Pasting:

Data Entry and Basic Functions

Essential Functions

1. SUM

Add numbers

=SUM(A1:A10)        // Sum range
=SUM(A1, A3, A5)   // Sum specific cells
=SUM(A1:A10, B1:B10) // Sum multiple ranges

2. AVERAGE

Calculate average

=AVERAGE(A1:A10)

3. COUNT, COUNTA, COUNTBLANK

Count cells

=COUNT(A1:A10)      // Count numbers only
=COUNTA(A1:A10)     // Count non-empty cells
=COUNTBLANK(A1:A10) // Count empty cells

4. MIN and MAX

Find minimum and maximum

=MIN(A1:A10)
=MAX(A1:A10)

5. MEDIAN

Find median value

=MEDIAN(A1:A10)

6. MODE

Find most frequent value

=MODE(A1:A10)

7. ROUND, ROUNDUP, ROUNDDOWN

Round numbers

=ROUND(3.14159, 2)    // 3.14
=ROUNDUP(3.14159, 2) // 3.15
=ROUNDDOWN(3.14159, 2) // 3.14

8. ABS

Absolute value

=ABS(-5)  // 5

9. SQRT

Square root

=SQRT(16)  // 4

10. SUMIF and SUMIFS

Conditional sum

// Sum values where condition is met
=SUMIF(A1:A10, ">100", B1:B10)

// Multiple conditions
=SUMIFS(B1:B10, A1:A10, ">100", C1:C10, "Yes")

11. COUNTIF and COUNTIFS

Conditional count

=COUNTIF(A1:A10, ">100")
=COUNTIFS(A1:A10, ">100", B1:B10, "Yes")

12. AVERAGEIF and AVERAGEIFS

Conditional average

=AVERAGEIF(A1:A10, ">100", B1:B10)
=AVERAGEIFS(B1:B10, A1:A10, ">100", C1:C10, "Yes")

Logical and Data Validation Functions

Logical Functions

1. IF

Conditional logic

=IF(A1>100, "High", "Low")
=IF(A1>100, "High", IF(A1>50, "Medium", "Low"))

2. AND, OR, NOT

Logical operators

=AND(A1>100, B1<50)  // Both conditions true
=OR(A1>100, B1<50)   // Either condition true
=NOT(A1>100)         // Reverse condition

3. Nested IF

Multiple conditions

=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F")))

4. IFS (Excel 2016+)

Simplified multiple conditions

=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")

5. SWITCH (Excel 2016+)

Switch statement

=SWITCH(A1, 1, "One", 2, "Two", 3, "Three", "Other")

Error Handling Functions

IFERROR

Handle errors gracefully

=IFERROR(VLOOKUP(A1, Table, 2, FALSE), "Not Found")
// Returns "Not Found" if VLOOKUP returns error

IFNA (Excel 2013+)

Handle #N/A errors specifically

=IFNA(VLOOKUP(A1, Table, 2, FALSE), "Not Found")
// Returns "Not Found" if #N/A error

Difference:

Data Validation

Setting Up Data Validation

Steps:

  1. Select cells
  2. Data → Data Validation
  3. Choose validation criteria
  4. Set input message and error alert

Validation Types

1. Whole Number:

Allow: Whole number
Data: between
Minimum: 1
Maximum: 100

2. Decimal:

Allow: Decimal
Data: between
Minimum: 0
Maximum: 1

3. List:

Allow: List
Source: Yes,No,Maybe

4. Date:

Allow: Date
Data: between
Start date: 2023-01-01
End date: 2023-12-31

5. Text Length:

Allow: Text length
Data: between
Minimum: 5
Maximum: 50

6. Custom Formula:

Allow: Custom
Formula: =AND(A1>0, A1<100)

Input Messages

Guide users on what to enter

Error Alerts

Show error when validation fails

Types:

Lookup and Reference Functions

VLOOKUP

Vertical lookup

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:

// Find price for product ID
=VLOOKUP(A2, Products!A:D, 4, FALSE)
// A2 = Product ID
// Products!A:D = Lookup table
// 4 = Column 4 (Price)
// FALSE = Exact match

Limitations:

HLOOKUP

Horizontal lookup

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Example:

=HLOOKUP("Q1", A1:D4, 3, FALSE)

INDEX and MATCH

More flexible than VLOOKUP

INDEX: Returns value at row/column intersection

=INDEX(array, row_num, [column_num])

MATCH: Returns position of value

=MATCH(lookup_value, lookup_array, [match_type])

Combined:

// Find price for product (more flexible than VLOOKUP)
=INDEX(Products!D:D, MATCH(A2, Products!A:A, 0))

Advantages over VLOOKUP:

XLOOKUP (Excel 365)

Modern replacement for VLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example:

=XLOOKUP(A2, Products!A:A, Products!D:D, "Not Found", 0)

Advantages:

INDIRECT

Reference cells dynamically

=INDIRECT("A" & B1)  // If B1=5, returns A5
=INDIRECT("Sheet1!A1")

OFFSET

Reference cells relative to starting point

=OFFSET(reference, rows, cols, [height], [width])

Example:

=OFFSET(A1, 2, 1)  // Returns B3 (2 rows down, 1 column right)

CHOOSE

Select from list of values

=CHOOSE(index_num, value1, value2, value3, ...)

Example:

=CHOOSE(A1, "Low", "Medium", "High")

Text Manipulation Functions

Basic Text Functions

1. CONCATENATE / CONCAT

Combine text

=CONCATENATE(A1, " ", B1)
=A1 & " " & B1  // Alternative using &
=CONCAT(A1, " ", B1)  // Excel 2016+

2. LEFT, RIGHT, MID

Extract characters

=LEFT(A1, 5)    // First 5 characters
=RIGHT(A1, 5)   // Last 5 characters
=MID(A1, 2, 5)  // 5 characters starting at position 2

3. LEN

Count characters

=LEN(A1)  // Returns length of text

4. UPPER, LOWER, PROPER

Change case

=UPPER(A1)   // UPPERCASE
=LOWER(A1)  // lowercase
=PROPER(A1)  // Title Case

5. TRIM

Remove extra spaces

=TRIM(A1)  // Removes leading/trailing spaces

6. SUBSTITUTE

Replace text

=SUBSTITUTE(A1, "old", "new")
=SUBSTITUTE(A1, "old", "new", 2)  // Replace 2nd occurrence

7. REPLACE

Replace by position

=REPLACE(A1, 1, 3, "New")  // Replace 3 chars starting at position 1

Find text position

=FIND("text", A1)    // Case-sensitive
=SEARCH("text", A1) // Case-insensitive

9. TEXT

Format number as text

=TEXT(A1, "0.00")      // "123.45"
=TEXT(A1, "$#,##0.00") // "$1,234.56"
=TEXT(A1, "mm/dd/yyyy") // Date format

10. VALUE

Convert text to number

=VALUE("123")  // Returns 123

Advanced Text Functions

TEXTJOIN (Excel 2016+)

Join text with delimiter

=TEXTJOIN(", ", TRUE, A1:A10)  // Join with comma, ignore empty

SPLIT (Excel 365)

Split text into array

=TEXTSPLIT(A1, ",")  // Split by comma

Date and Time Functions

Basic Date Functions

DAY, MONTH, YEAR

Extract date components

=DAY(A1)    // Returns day (1-31)
=MONTH(A1)  // Returns month (1-12)
=YEAR(A1)   // Returns year (e.g., 2024)

WEEKNUM

Get week number

=WEEKNUM(A1)           // Week number (1-52)
=WEEKNUM(A1, 2)        // Week starts Monday (2)

TODAY and NOW

Current date and time

=TODAY()  // Returns current date
=NOW()    // Returns current date and time

Note: These are volatile functions (recalculate every time)

Date Calculations

// Days between dates
=A2-A1  // Returns number of days

// Add days to date
=A1+30  // Add 30 days

// Date arithmetic
=DATE(2024, 1, 15) + 30  // Add 30 days to date
=EDATE(A1, 3)            // Add 3 months (Excel 2013+)
=EOMONTH(A1, 0)          // End of month

Time Functions

=HOUR(A1)    // Extract hour (0-23)
=MINUTE(A1)  // Extract minute (0-59)
=SECOND(A1)  // Extract second (0-59)
=TIME(14, 30, 0)  // Create time (14:30:00)

Dynamic Array Formulas (Excel 365)

Dynamic arrays automatically spill results to multiple cells.

FILTER

Filter data based on conditions

=FILTER(A1:C10, B1:B10>100)
// Returns rows where column B > 100

=FILTER(A1:C10, (B1:B10>100)*(C1:C10="Yes"))
// Multiple conditions (AND)

SORT

Sort data dynamically

=SORT(A1:C10)                    // Sort by first column
=SORT(A1:C10, 2, -1)             // Sort by column 2, descending
=SORTBY(A1:C10, B1:B10, -1)      // Sort by column B, descending

UNIQUE

Extract unique values

=UNIQUE(A1:A10)           // Unique values
=UNIQUE(A1:A10, TRUE)     // Unique rows
=UNIQUE(A1:A10, FALSE, TRUE)  // Return values that appear once

RANDARRAY

Generate random numbers

=RANDARRAY(5, 3)              // 5 rows, 3 columns, 0-1
=RANDARRAY(5, 3, 1, 100)      // Random integers 1-100
=RANDARRAY(5, 3, 1, 100, TRUE) // Random integers, no duplicates

SEQUENCE

Generate sequence of numbers

=SEQUENCE(10)           // 1 to 10 (vertical)
=SEQUENCE(1, 10)        // 1 to 10 (horizontal)
=SEQUENCE(5, 3, 10, 5)  // 5 rows, 3 cols, start 10, step 5

Combining Dynamic Arrays

// Filter and sort
=SORT(FILTER(A1:C10, B1:B10>100), 2, -1)

// Unique and sort
=SORT(UNIQUE(A1:A10))

// Multiple operations
=UNIQUE(SORT(FILTER(A1:C10, B1:B10>100), 3))

Modern Excel Functions

LET (Excel 365)

Define variables within formulas

// Without LET (repetitive)
=IF(SUM(A1:A10)>100, SUM(A1:A10)*0.1, SUM(A1:A10)*0.05)

// With LET (efficient)
=LET(
    total, SUM(A1:A10),
    IF(total>100, total*0.1, total*0.05)
)

Benefits:

LAMBDA (Excel 365)

Create custom functions without VBA

// Define LAMBDA function
=LAMBDA(x, y, x+y)(5, 3)  // Returns 8

// Create named LAMBDA
1. Formulas → Name Manager → New
2. Name: AddNumbers
3. Refers to: =LAMBDA(x, y, x+y)
4. Use: =AddNumbers(5, 3)

// Complex example
=LAMBDA(price, quantity, discount,
    (price * quantity) * (1 - discount)
)(100, 5, 0.1)  // Returns 450

Use Cases:

Data Organization and Analysis Tools

Sorting (Basic, Custom, Multi-Level)

Basic Sorting:

  1. Select data range
  2. Data → Sort
  3. Choose column and order (A-Z or Z-A)

Custom Sort:

  1. Data → Sort
  2. Add Level for multiple criteria
  3. Choose sort order for each level

Multi-Level Sort:

// Example: Sort by Region, then by Sales
Level 1: Region (A-Z)
Level 2: Sales (Largest to Smallest)

Sort Options:

Filtering (AutoFilter, Advanced Filter)

AutoFilter:

  1. Select data range
  2. Data → Filter
  3. Click dropdown arrows to filter

Filter Options:

Advanced Filter:

  1. Set up criteria range
  2. Data → Advanced
  3. Select List range and Criteria range
  4. Choose "Copy to another location" if needed

Criteria Examples:

// Criteria range:
Region    Sales
North     >1000
South     >500

// OR conditions (different rows)
Region
North
South

Data Cleaning

Remove Duplicates:

  1. Select data range
  2. Data → Remove Duplicates
  3. Choose columns to check

Find/Replace:

Text-to-Columns:

  1. Select column
  2. Data → Text to Columns
  3. Choose delimiter (Comma, Tab, Space, Custom)
  4. Set data format for each column

Delimiters:

Data Merging, Consolidate, and Append

Consolidate: Combine data from multiple ranges

Steps:

  1. Select destination cell
  2. Data → Consolidate
  3. Choose function (Sum, Average, Count, etc.)
  4. Add references (ranges to consolidate)
  5. Check "Top row" and "Left column" if needed

Append Data:

Merge Data:

Grouping and Outlining Data

Group Rows/Columns:

  1. Select rows/columns
  2. Data → Group
  3. Choose Rows or Columns
  4. Click +/- to expand/collapse

Auto Outline:

  1. Data → Outline → Auto Outline
  2. Excel automatically groups based on formulas

Ungroup:

Use Cases:

Subtotals and Aggregations

Subtotals:

  1. Sort data by grouping column
  2. Data → Subtotal
  3. Choose:
    • At each change in: (grouping column)
    • Use function: (Sum, Average, Count, etc.)
    • Add subtotal to: (columns to summarize)

Aggregation Functions:

=COUNT(A1:A10)      // Count numbers
=COUNTA(A1:A10)     // Count non-empty
=COUNTBLANK(A1:A10) // Count empty
=COUNTIF(A1:A10, ">100")     // Conditional count
=COUNTIFS(A1:A10, ">100", B1:B10, "Yes")  // Multiple conditions
=SUMIF(A1:A10, ">100", B1:B10)            // Conditional sum
=SUMIFS(B1:B10, A1:A10, ">100", C1:C10, "Yes")  // Multiple conditions

Excel Tables and Structured Data

Creating Excel Tables

Steps:

  1. Select data range
  2. Insert → Table (Ctrl + T)
  3. Confirm "My table has headers"
  4. Click OK

Table Features

1. Automatic Formatting:

2. Structured References:

// Instead of A2:A10, use:
=SUM(Table1[Sales])
=SUM(Table1[Sales], Table1[Quantity])

3. Automatic Expansion:

4. Total Row:

5. Slicers:

Table Best Practices

  1. Use Headers: Always include header row
  2. No Blank Rows: Keep data contiguous
  3. Consistent Data Types: Same type in each column
  4. Name Tables: Give meaningful names
  5. Use Structured References: More readable formulas

Pivot Tables for Data Analysis

Creating a Pivot Table

Steps:

  1. Select data range
  2. Insert → PivotTable
  3. Choose location (New Worksheet or Existing)
  4. Click OK

Pivot Table Areas

1. Rows: Categories for grouping 2. Columns: Secondary grouping 3. Values: Measures to summarize 4. Filters: Filter entire pivot table

Basic Pivot Table Example

Data: Sales with Date, Product, Region, Amount

Pivot Table Setup:

Result: Total sales by product

Common Calculations

Change Value Field Settings:

Show Values As:

Grouping Data

Group Dates:

Group Numbers:

Group Text:

Advanced Pivot Table Techniques

Calculated Fields

Create custom calculations in pivot table

Steps:

  1. PivotTable Analyze → Fields, Items & Sets → Calculated Field
  2. Enter name and formula
  3. Click Add

Example:

Name: Profit Margin
Formula: =Profit/Sales

Calculated Items

Create custom items within a field

Steps:

  1. Select field in Rows/Columns
  2. PivotTable Analyze → Fields, Items & Sets → Calculated Item
  3. Enter name and formula

Example:

Name: Q1-Q2 Total
Formula: =Q1+Q2

Slicers and Timelines

Slicers:

Timelines:

Multiple Value Fields

Add multiple measures

Example:

Pivot Charts

Create charts from pivot tables

Steps:

  1. Select pivot table
  2. Insert → PivotChart
  3. Choose chart type

Advantages:

GETPIVOTDATA

Extract specific values from pivot table

=GETPIVOTDATA("Sum of Sales", $A$3, "Product", "Widget")

Data Visualization Basics

Chart Types

1. Column/Bar Charts

Use for: Comparing categories

Types:

2. Line Charts

Use for: Trends over time

Types:

3. Pie Charts

Use for: Proportions

Types:

4. Scatter Plots

Use for: Relationships between variables

Types:

5. Area Charts

Use for: Cumulative trends

Types:

Creating Charts

Steps:

  1. Select data
  2. Insert → Choose chart type
  3. Customize with Chart Tools

Chart Elements

Add Elements:

Format Elements:

Advanced Charting Techniques

Combination Charts

Combine different chart types

Example:

Steps:

  1. Create chart
  2. Right-click series → Change Series Chart Type
  3. Choose different type

Secondary Axis

Use when values have different scales

Steps:

  1. Right-click series → Format Data Series
  2. Check "Secondary Axis"

Dynamic Charts

Charts that update automatically

Method 1: Excel Tables

Method 2: Named Ranges with OFFSET

// Named Range: SalesData
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

Sparklines and Mini Graphs

Mini charts in cells for quick trend visualization.

Types:

Steps:

  1. Insert → Sparklines
  2. Choose type
  3. Select data range and location

Customize Sparklines:

Example:

=SPARKLINE(A1:A12)  // Shows trend in single cell

Advanced Visuals

Waterfall Chart: Shows cumulative effect of positive/negative values.

Steps:

  1. Select data
  2. Insert → Charts → Waterfall
  3. Customize as needed

Use Cases:

Sunburst Chart: Hierarchical data visualization (Excel 2016+).

Steps:

  1. Prepare hierarchical data
  2. Insert → Charts → Sunburst
  3. Customize colors and labels

Treemap Chart: Shows hierarchical data as nested rectangles (Excel 2016+).

Steps:

  1. Select hierarchical data
  2. Insert → Charts → Treemap
  3. Customize colors and labels

Use Cases:

Statistical Charts

Histogram: Shows frequency distribution.

Steps:

  1. Data → Data Analysis → Histogram
  2. Select input range and bin range
  3. Check "Chart Output"

Pareto Chart: Combines column chart and line chart (80/20 rule).

Steps:

  1. Sort data descending
  2. Calculate cumulative percentage
  3. Create combo chart (Column + Line)

Box Plot (Box and Whisker): Shows distribution, quartiles, and outliers (Excel 2016+).

Steps:

  1. Select data
  2. Insert → Charts → Box and Whisker
  3. Customize as needed

Specialized Charts

Stacked Column 100%: Shows proportions that add up to 100%.

Steps:

  1. Create stacked column chart
  2. Right-click series → Format Data Series
  3. Change to "100% Stacked"

Area Chart 100%: Shows cumulative proportions over time.

Steps:

  1. Create area chart
  2. Change to "100% Stacked Area"

Scatter Plot: Shows relationship between two variables.

Steps:

  1. Select X and Y data
  2. Insert → Charts → Scatter
  3. Add trendline if needed

Funnel Chart: Shows stages in a process (Excel 2019+).

Steps:

  1. Select data
  2. Insert → Charts → Funnel
  3. Customize stages

Use Cases:

Interactive Charts using Drop-downs and Form Controls

Drop-down Lists:

  1. Create drop-down (Data Validation → List)
  2. Use INDIRECT for dependent lists
  3. Link chart data to drop-down selection

Form Controls:

  1. Developer → Insert → Form Controls
  2. Add Checkbox, Option Button, Scroll Bar, Spin Button
  3. Link to cells
  4. Use cell values in charts

Example:

// Scroll bar linked to cell A1 (1-12 for months)
// Chart uses OFFSET to show data based on A1
=OFFSET(Data!$A$1, 0, A1-1, 10, 1)

Conditional Formatting and Sparklines

Conditional Formatting

Apply formatting based on conditions

1. Highlight Cells Rules

2. Top/Bottom Rules

3. Data Bars

Visual bars in cells

Steps:

  1. Select range
  2. Home → Conditional Formatting → Data Bars
  3. Choose style

4. Color Scales

Color gradient based on values

Steps:

  1. Select range
  2. Home → Conditional Formatting → Color Scales
  3. Choose scale

5. Icon Sets

Icons based on values

Steps:

  1. Select range
  2. Home → Conditional Formatting → Icon Sets
  3. Choose set

6. Custom Formulas

Use formulas for conditions

Example:

// Highlight if value > average
=$A1>AVERAGE($A$1:$A$10)

Managing Conditional Formatting

View Rules:

Edit Rules:

Delete Rules:

Dashboard Design Principles

Dashboard Layout

1. Top Section: Key metrics (KPIs) 2. Middle Section: Main charts and analysis 3. Bottom Section: Detailed data tables

Design Best Practices

1. Use Consistent Colors:

2. Limit Information:

3. Use Appropriate Chart Types:

4. Add Context:

5. Make it Interactive:

KPI Dashboard Example

Layout:

Row 1: KPI Cards (Sales, Profit, Orders, Customers)
Row 2: Trend Chart (Sales over time)
Row 3: Category Breakdown (Pie/Bar chart)
Row 4: Regional Map/Chart
Row 5: Data Table (optional)

Advanced Dashboarding Techniques

Dynamic Dashboards

1. Using Slicers:

2. Using Dropdowns:

3. Using Buttons:

Interactive Elements

1. Hyperlinks:

2. Camera Tool:

3. Form Controls:

Dashboard Navigation

1. Index Sheet:

2. Navigation Buttons:

3. Breadcrumbs:

Power Query and Data Transformation

Introduction to Power Query in Excel

Power Query is Excel's data transformation tool (same as Power BI).

Getting Data

Data Sources:

Common Transformations

1. Remove Columns:

2. Change Data Types:

3. Remove Rows:

4. Split Columns:

5. Merge Columns:

6. Add Custom Column:

7. Group By:

Advanced Power Query

1. Merge Queries:

2. Append Queries:

3. Pivot/Unpivot:

4. Parameters:

Loading Data

Options:

Parameters and M Language Basics

Parameters: Reusable values in Power Query.

Steps:

  1. Home → Manage Parameters → New Parameter
  2. Define name, type, and value
  3. Use in queries: #"Parameter Name"

M Language Basics: Power Query uses M language for transformations.

Common M Functions:

// Text functions
Text.Upper([Column])
Text.Lower([Column])
Text.Trim([Column])

// Number functions
Number.Round([Column], 2)
Number.Abs([Column])

// Date functions
Date.Year([Column])
Date.Month([Column])

// List functions
List.Sum([Column])
List.Average([Column])

// Conditional
if [Column] > 100 then "High" else "Low"

Custom Column Example:

// Add Column → Custom Column
if [Sales] > 1000 then "High" else "Low"

Power Pivot and Data Models

Introduction to Data Models and Star Schema

What is Power Pivot? Power Pivot extends Excel's data modeling capabilities, allowing you to:

Star Schema: Common data model structure:

Enable Power Pivot:

  1. File → Options → Add-Ins
  2. Manage: COM Add-ins → Go
  3. Check "Microsoft Office Power Pivot"

Creating Relationships

Steps:

  1. Power Pivot → Manage Data Model
  2. Add tables (if not already added)
  3. Diagram View
  4. Drag to create relationships

Relationship Types:

Cardinality:

Cross Filter Direction:

Inactive Relationships:

Building KPIs, Hierarchies, and Managing Relationships

KPIs (Key Performance Indicators):

  1. Power Pivot → KPIs → New KPI
  2. Select measure
  3. Define target value
  4. Set status thresholds

Hierarchies: Organize related columns.

Steps:

  1. Power Pivot → Diagram View
  2. Right-click table → Create Hierarchy
  3. Add columns (e.g., Year → Quarter → Month)

Managing Relationships:

DAX (Data Analysis Expressions)

Calculated Columns, Measures, and Tables

Calculated Columns: Add new column to table (row-by-row calculation).

Steps:

  1. Power Pivot → Design → Add Column
  2. Enter DAX formula
  3. Press Enter

Example:

// Calculated column
Profit = Sales[Revenue] - Sales[Cost]

Measures: Aggregations that calculate on-the-fly (not stored).

Steps:

  1. Power Pivot → Home → Measures → New Measure
  2. Enter DAX formula
  3. Name the measure

Example:

// Measure
Total Sales = SUM(Sales[Amount])
Average Sales = AVERAGE(Sales[Amount])

Calculated Tables: Create new table from DAX.

Steps:

  1. Power Pivot → Design → Table → New Calculated Table
  2. Enter DAX formula

Example:

// Calculated table
Sales Summary = 
SUMMARIZE(
    Sales,
    Sales[Product],
    "Total Sales", SUM(Sales[Amount])
)

DAX Operators and Syntax

Operators:

+  // Addition
-  // Subtraction
*  // Multiplication
/  // Division
=  // Equal
<> // Not equal
>  // Greater than
<  // Less than
>= // Greater than or equal
<= // Less than or equal
&& // AND
|| // OR

Syntax:

// Basic syntax
MeasureName = FUNCTION(Table[Column])

// With filter
MeasureName = 
CALCULATE(
    SUM(Table[Column]),
    Table[Category] = "A"
)

Text, Math, Logical, Filter, and Date Functions

Text Functions:

CONCATENATE("Hello", " ", "World")
LEFT("Text", 2)        // "Te"
RIGHT("Text", 2)       // "xt"
LEN("Text")            // 4
UPPER("text")          // "TEXT"
LOWER("TEXT")          // "text"

Math Functions:

SUM(Table[Column])
AVERAGE(Table[Column])
MIN(Table[Column])
MAX(Table[Column])
COUNT(Table[Column])
ROUND(Table[Column], 2)
ABS(Table[Column])

Logical Functions:

IF(condition, value_if_true, value_if_false)
AND(condition1, condition2)
OR(condition1, condition2)
NOT(condition)
SWITCH(expression, value1, result1, ...)

Filter Functions:

FILTER(Table, condition)
CALCULATE(expression, filter1, filter2, ...)
ALL(Table)              // Remove filters
ALLSELECTED(Table)      // Keep user filters
RELATED(Table[Column])  // From related table

Date Functions:

YEAR(Date[Date])
MONTH(Date[Date])
DAY(Date[Date])
TODAY()                 // Current date
NOW()                   // Current date/time
DATEDIFF(Date1, Date2, DAY)

Time Intelligence Functions

DATEADD: Shift date by period.

DATEADD(Date[Date], -1, YEAR)  // Previous year
DATEADD(Date[Date], 1, MONTH)  // Next month

DATESBETWEEN: Filter dates between range.

DATESBETWEEN(
    Date[Date],
    DATE(2024, 1, 1),
    DATE(2024, 12, 31)
)

TOTALYTD: Year-to-date total.

TOTALYTD(
    SUM(Sales[Amount]),
    Date[Date]
)

SAMEPERIODLASTYEAR: Compare to same period last year.

Sales LY = 
CALCULATE(
    SUM(Sales[Amount]),
    SAMEPERIODLASTYEAR(Date[Date])
)

Other Time Intelligence:

TOTALQTD()  // Quarter-to-date
TOTALMTD()  // Month-to-date
PREVIOUSYEAR()
PREVIOUSMONTH()
PREVIOUSQUARTER()

Relationship Functions

CROSSFILTER: Change cross-filter direction.

CALCULATE(
    SUM(Sales[Amount]),
    CROSSFILTER(Sales[CustomerID], Customer[ID], BOTH)
)

RELATED: Get value from related table.

Customer Name = RELATED(Customer[Name])

USERELATIONSHIP: Use inactive relationship.

CALCULATE(
    SUM(Sales[Amount]),
    USERELATIONSHIP(Sales[Date], DateTable[Date])
)

Variables and Performance Optimization

VAR and RETURN: Define variables for readability and performance.

// Without VAR
Total Sales = 
IF(
    SUM(Sales[Amount]) > 1000,
    SUM(Sales[Amount]) * 0.1,
    SUM(Sales[Amount]) * 0.05
)

// With VAR (calculates once)
Total Sales = 
VAR TotalAmount = SUM(Sales[Amount])
RETURN
    IF(
        TotalAmount > 1000,
        TotalAmount * 0.1,
        TotalAmount * 0.05
    )

Performance Tips:

  1. Use measures instead of calculated columns when possible
  2. Use VAR to avoid multiple calculations
  3. Filter early in CALCULATE
  4. Use ALLSELECTED instead of ALL when appropriate
  5. Avoid circular dependencies

Automation, AI, and Integration

Macros: Record, Edit, and Run

Recording Macros:

  1. Developer → Record Macro
  2. Name macro (no spaces)
  3. Choose shortcut key (optional)
  4. Perform actions
  5. Stop Recording

Running Macros:

Editing Macros:

  1. Developer → Macros
  2. Select macro → Edit
  3. Opens VBA editor
  4. Modify code

Macro Security:

VBA (Visual Basic for Applications)

Introduction: VBA allows automation and custom functionality.

Basic VBA Example:

Sub HelloWorld()
    MsgBox "Hello, World!"
End Sub

Sub FormatCells()
    Range("A1:A10").Font.Bold = True
    Range("A1:A10").Interior.Color = RGB(255, 255, 0)
End Sub

Sub LoopExample()
    Dim i As Integer
    For i = 1 To 10
        Cells(i, 1).Value = i * 2
    Next i
End Sub

Common VBA Tasks:

VBA Resources:

Office Scripts: Excel Web Automation

What are Office Scripts? JavaScript-based automation for Excel Online.

Enable:

  1. Excel Online
  2. Automate tab
  3. New Script

Example:

function main(workbook: ExcelScript.Workbook) {
    let worksheet = workbook.getActiveWorksheet();
    let range = worksheet.getRange("A1");
    range.setValue("Hello from Office Scripts!");
}

Use Cases:

Copilot in Excel: AI-Powered Formula Suggestions and Insights

What is Copilot? AI assistant in Excel (Microsoft 365).

Features:

How to Use:

  1. Select data
  2. Home → Copilot (if available)
  3. Ask questions or request analysis
  4. Review suggestions

Example Prompts:

Note: Requires Microsoft 365 subscription with Copilot license

Excel + Python Integration

Perform Data Science within Excel

Python in Excel (Excel 365):

  1. Insert → Python
  2. Write Python code in cells
  3. Use pandas, numpy, matplotlib, etc.

Example:

# In Excel Python cell
import pandas as pd
import numpy as np

# Access Excel data
df = xl("A1:C10", headers=True)

# Perform analysis
result = df.groupby('Category')['Sales'].sum()

# Return to Excel
result

Use Cases:

Requirements:

Capstone Project Ideas

1. Sales Performance Dashboard

Objective: Create comprehensive sales analysis dashboard.

Components:

Skills Used:

2. HR Attrition Analysis

Objective: Analyze employee turnover and identify patterns.

Components:

Skills Used:

3. Financial KPI and Forecasting Report

Objective: Track financial performance and forecast future trends.

Components:

Skills Used:

4. Excel Automation using Macros and VBA

Objective: Automate repetitive tasks and create custom tools.

Components:

Skills Used:

5. AI-Powered Reporting using Excel Copilot

Objective: Leverage AI for data insights and reporting.

Components:

Skills Used:

Best Practices

Data Organization

  1. One Row Per Record: Normalize data
  2. No Blank Rows/Columns: Keep data contiguous
  3. Consistent Formatting: Use styles
  4. Named Ranges: Make formulas readable
  5. Documentation: Add notes and instructions

Formula Best Practices

  1. Use Tables: Automatic expansion
  2. Avoid Hardcoding: Use cell references
  3. Use Named Ranges: More readable
  4. Test Formulas: Verify results
  5. Document Complex Formulas: Add comments

Performance Tips

  1. Limit Volatile Functions: NOW(), TODAY(), RAND()
  2. Use SUMIFS Instead of Array Formulas: Faster
  3. Avoid Entire Column References: Use specific ranges
  4. Use Excel Tables: Better performance
  5. Minimize Conditional Formatting: Can slow down

Security

  1. Protect Sheets: Prevent accidental changes
  2. Hide Formulas: Protect intellectual property
  3. Data Validation: Prevent invalid entries
  4. Password Protection: For sensitive data
  5. Backup Files: Regular backups

Resources

Official Documentation

Free Courses

YouTube Channels

Books

Practice Resources

Try next: Clean one messy CSV in Excel (or Sheets), then rebuild the same clean steps in pandas.