Thursday, December 25, 2025

Chapter 1: Power BI – What It Is and How It Works

Chapter 1: Introduction to Power BI




1. What is Power BI?

Power BI is a business analytics tool created by Microsoft. It helps you connect to data, clean it, analyze it, and create interactive reports and dashboards. Power BI turns raw data into meaningful insights.

2. Why Do We Use Power BI?

  • To visualize data in the form of charts, tables, and dashboards.
  • To make data-driven decisions.
  • To combine data from multiple sources like Excel, SQL, web data, etc.
  • To share reports with teams or clients easily.
  • To refresh dashboards automatically.

3. Components / Parts of Power BI

Power BI has 3 main components:

a) Power BI Desktop

A Windows application used to build reports. You can connect to data, clean data using Power Query, create visuals, and write DAX formulas here.

b) Power BI Service (Online)

A cloud-based platform where you publish reports, create dashboards, share reports, and schedule automatic refresh.

c) Power BI Mobile App

Used to view dashboards and reports on mobile devices.

4. Power BI Workflow (How Power BI Works)

Power BI follows this simple process:

  1. Get Data – Import data from Excel, SQL Server, Web, etc.
  2. Transform Data – Clean, remove duplicates, merge tables using Power Query.
  3. Model Data – Create relationships between tables.
  4. Create Visuals – Charts, maps, KPI cards, tables.
  5. Publish Report – Upload to Power BI Service.
  6. Share Dashboard – Share with others for decision-making.

5. Power BI File Types

  • .pbix – Report file created in Power BI Desktop.
  • .pbit – Template file (structure without data).

6. Power BI Key Features

  • Connects to multiple data sources.
  • Provides built-in visuals and custom visuals.
  • Supports DAX for advanced calculations.
  • Automatic data refresh in Power BI Service.
  • Interactive dashboards (filters, slicers, drill-down).

7. Commonly Used Power BI Terms

a) Dataset

The collection of data that you import or connect to in Power BI.

b) Report

A collection of visuals (charts, tables) based on a dataset. A report can have multiple pages.

c) Dashboard

A single-page summary containing visuals pinned from reports.

d) Visualization

A chart, graph, or table that displays your data visually (bar chart, line chart, card, etc.)

e) Data Model

The structure of your tables and their relationships.

8. Example to Understand Power BI

Suppose you have sales data in an Excel file. You import the file into Power BI Desktop, remove empty rows, create a relationship between Sales and Products tables, and build visuals like Sales by Region. Then you publish the report to Power BI Service to share with your team.

9. Summary

Power BI is a complete data analytics tool used to connect, clean, analyze, visualize, and share data. It is simple to learn, powerful for business intelligence, and helpful for decision-making.

Power BI Practical Assignments (Without Lookup Functions)

Assignment 1: Sales Dashboard

Dataset: Sales Table and Product Table

  • Import Sales and Product tables.
  • Clean data: remove duplicates and blank rows.
  • Create relationship between tables.
  • Create visuals: Sales by Category, Sales by Region, Monthly Sales, Top 5 Products.
  • Create DAX: Total Sales, Total Quantity, Sales Growth Percentage.
  • Create and publish a one-page dashboard.

Assignment 2: HR Employee Performance Report

Dataset: Employee Table and Attendance Table

  • Load both datasets into Power BI Desktop.
  • Clean Attendance table and format date column.
  • Create DAX: Total Present Days, Attendance Percentage.
  • Create visuals: Attendance % by Department, Age Distribution.
  • Create slicers for Department and Employee Type.
  • Use conditional formatting to highlight attendance below 70%.

Assignment 3: E-commerce Dashboard

Dataset: Orders, Customers, Products

  • Create relationships using CustomerID and ProductID.
  • Create DAX: Total Revenue, Total Orders, Average Order Value.
  • Create visuals: Revenue by Month, Orders by City, Customer Segmentation.
  • Insert Date slicer and create KPI cards.

Assignment 4: Financial Statement Analysis

Dataset: Profit & Loss Excel Sheet

  • Import P&L sheet and unpivot monthly columns.
  • Create visuals: Expense Breakdown, Income vs Expense.
  • Create DAX: Net Profit = Income - Expense.
  • Apply conditional formatting for negative values.

Assignment 5: Power Query Data Cleaning

Dataset: Raw Excel File

  • Remove duplicate rows.
  • Replace errors in dataset.
  • Split Full Name into First Name and Last Name.
  • Merge Orders and Returns tables.
  • Create a custom column: Profit = Sales - Cost.

Assignment 6: School/College Dashboard

Dataset: Students Table

  • Calculate Total Marks and Percentage using DAX.
  • Create visuals: Marks by Subject, Average Marks by Class, Attendance Trend.
  • Enable drill-down for Class → Student.

Assignment 7: Hospital Data Analysis

Dataset: Patients Table and Doctors Table

  • Import data tables and create relationships.
  • Create visuals: Patient Count by Disease, Age Distribution, Doctor-wise Patients.

Assignment 8: Inventory Management Dashboard

Dataset: Stock and Sales Tables

  • Create new column: Current Stock = Opening Stock - Sold Quantity.
  • Create Stock Alert Column (Stock < 10 = "Low").
  • Create visuals: Low Stock Items, Stock vs Sales.

Assignment 9: Power BI Time Intelligence Practice

Dataset: Sales Table with Date Column

  • Create a Date Table.
  • Create DAX: YTD Sales, MTD Sales, Previous Year Sales.
  • Create visuals comparing current vs previous year.

Assignment 10: Power BI Copilot Tasks

  • Ask Copilot to write DAX for Year-to-Date Sales.
  • Create a line chart for Monthly Sales using Copilot.
  • Ask Copilot to create a summary of the dataset.
  • Ask Copilot to generate a Profit Margin measure.
  • Compare Copilot's DAX formulas with your own formulas.
- No comments:

Wednesday, December 24, 2025

Advanced Excel Notes: Full Guide for Students & Professionals

Advanced Excel – Complete Notes for Beginners to Experts




1. Pivot Table

A Pivot Table is an advanced Excel tool used to summarize, analyze, compare, and explore large datasets quickly without using formulas. It automatically organizes data into a structured summary format using rows, columns, values, and filters.

1.1 Need of Pivot Table

  • Summarizes thousands of records instantly.
  • Creates automatic totals, averages, counts.
  • Removes duplicates automatically.
  • Allows grouping by month, year, or number ranges.
  • Helps build dashboards with slicers and charts.
  • Provides dynamic filtering and sorting.

1.2 Components of Pivot Table

Component Description
Filters Allows filtering entire Pivot Table by categories such as Year or Region.
Rows Displays main categories like Product or Customer.
Columns Creates subdivisions such as Region or Month.
Values Performs calculations like SUM, COUNT, AVERAGE.

1.3 Steps to Create a Pivot Table

  1. Select the complete dataset.
  2. Go to Insert → Pivot Table.
  3. Select New Worksheet.
  4. Drag fields to Rows, Columns, Values, Filters.
  5. Format and apply slicers if needed.

1.4 Value Field Settings

Common calculations you can apply:

  • Sum
  • Count
  • Average
  • Max / Min
  • % of Grand Total
  • Running Total
  • Difference From

1.5 Grouping in Pivot Table

You can group data in multiple ways:

  • Date Grouping: Month, Quarter, Year
  • Number Grouping: 0–100, 101–200, etc.
  • Text Grouping: Combine categories

Steps: Right-click a value → Group.

1.6 Pivot Chart

A Pivot Chart is a dynamic chart linked to a Pivot Table. Types include:

  • Column Chart
  • Bar Chart
  • Line Chart
  • Pie Chart

1.7 Practical Questions (Pivot Table)

  1. Create a Pivot Table to show total sales by product.
  2. Group date field into months and show monthly sales.
  3. Create a Pivot Table showing region-wise comparison.
  4. Show % of Grand Total for product categories.
  5. Create a Pivot Chart showing product performance.

2. Consolidate

Consolidate combines multiple sheet totals into one summary sheet.

Example

Sheet1 – North Region

Month Sales
Jan5000
Feb6000

Sheet2 – South Region

Month Sales
Jan7000
Feb5500

2. Consolidate

The Consolidate feature in Excel is used to combine data from multiple worksheets or ranges into a single summary sheet. It is helpful when data is stored across months, branches, departments, or separate files.

2.1 Why Use Consolidate?

  • To combine data from multiple sheets.
  • To prepare total summary reports.
  • To merge monthly or branch-wise data automatically.
  • To apply functions such as SUM, AVERAGE, COUNT, MAX, MIN.
  • Reduces manual copy-paste work.

2.2 Types of Consolidation

Type Description
By Position Used when data is arranged in the same cell structure across sheets.
By Category Used when headings match but cell positions differ.

2.3 Steps to Use Consolidate

  1. Go to Data → Consolidate.
  2. Select the function (SUM, AVERAGE, COUNT, etc.).
  3. Click Add and select ranges from multiple sheets.
  4. If headings exist, check:
    • Top Row
    • Left Column
  5. Click OK.

2.4 Example Use Cases

  • Combine monthly sales from Jan, Feb, Mar sheets.
  • Summarize expenses across departments.
  • Create a summary of branch sales.
  • Combine student marks from different class sections.

2.5 Practical Questions (Consolidate)

  1. Consolidate monthly sales from 12 sheets using the SUM function.
  2. Merge department-wise expense reports with category-based consolidation.
  3. Combine marks from three classes using AVERAGE to find overall class performance.
  4. Create a summary of total sales from multiple branches.
  5. Combine region-wise data stored in separate files into one summary sheet.

3. What-If Analysis

What-If Analysis is a powerful Excel feature used to test different situations and understand how changing input values affects output results. It helps in forecasting, planning, budgeting, and decision-making without modifying original data.

Excel provides three What-If Analysis tools:

  • Goal Seek
  • Scenario Manager
  • Data Table

3.1 Why Use What-If Analysis?

  • To test future outcomes.
  • To make business decisions using data.
  • To adjust values and analyze the impact instantly.
  • To create financial models.
  • To compare multiple versions of data.

3.2 Uses in Real Life

  • Forecasting future sales.
  • Loan planning and EMI testing.
  • Budget creation.
  • Profit planning based on cost/price changes.
  • Comparing best-case and worst-case scenarios.

3.3 Three Tools Inside What-If Analysis

Tool Purpose
Goal Seek Finds required input to achieve a desired output.
Scenario Manager Compares multiple versions of data.
Data Table Shows how outputs change based on multiple input values.

3.4 Steps to Open What-If Analysis

  1. Go to Data tab.
  2. Click on What-If Analysis.
  3. Select:
    • Goal Seek
    • Scenario Manager
    • Data Table

3.5 Practical Questions (What-If Analysis)

  1. Use What-If Analysis to forecast future sales if cost and price change.
  2. Create a loan model and test EMI for different loan amounts.
  3. Use Scenario Manager to compare best, average, and worst business cases.
  4. Use a Data Table to test profit based on changing selling price.
  5. Use Goal Seek to calculate required marks to achieve a target percentage.

4. Goal Seek

Goal Seek is used to find the exact input required to achieve a specific output. It works on reverse calculation. Instead of manually guessing a value, Excel automatically finds the value needed.

4.1 Purpose of Goal Seek

  • Find required marks to reach target percentage.
  • Find selling price to reach target profit.
  • Find cost reduction needed for target savings.
  • Find loan amount needed for a fixed EMI.
  • Find discount needed to achieve target sales.

4.2 Goal Seek Parameters

Field Meaning
Set Cell Output or formula cell.
To Value Target output.
By Changing Cell Input cell which Excel adjusts.

4.3 Steps to Use Goal Seek

  1. Go to Data → What-If Analysis → Goal Seek.
  2. Set the formula cell under “Set Cell”.
  3. Enter target value under “To value”.
  4. Enter input cell under “By changing cell”.
  5. Click OK.

4.4 Example

Suppose:
Profit = Sales – Cost
You want Profit = 50,000

Goal Seek:
  • Set Cell → Profit Cell
  • To Value → 50000
  • By Changing → Sales Cell

4.5 Practical Questions (Goal Seek)

  1. Find required selling price to earn 25% profit.
  2. Find marks needed in final exam to score 80% overall.
  3. Calculate required loan amount for EMI = 10,000.
  4. Find discount percentage needed to reach target sales.
  5. Find cost reduction needed to reach required profit.

5. Scenario Manager

Scenario Manager is part of Excel’s What-If Analysis tools that allows you to create and compare multiple versions of data. Each scenario represents a different set of input values. This is useful in business forecasting, budgeting, planning, and decision-making.

5.1 Why Use Scenario Manager?

  • To compare different business situations.
  • To create “Best Case, Worst Case, and Average Case” forecasts.
  • To predict future outcomes based on different assumptions.
  • To change multiple input cells at once.
  • To generate summary reports automatically.

5.2 Requirements for Scenario Manager

  • Only **input cells** can be changed.
  • You can create multiple scenarios.
  • Each scenario can change up to **32 input cells**.

5.3 Steps to Create a Scenario

  1. Go to Data → What-If Analysis → Scenario Manager.
  2. Click Add.
  3. Enter:
    • Scenario Name (e.g., Best Case).
    • Changing Cells — select input cells.
  4. Enter the new values for this scenario.
  5. Click OK.
  6. Repeat for all scenarios.

5.4 Example Scenarios

Scenario Assumption
Best Case Sales ↑, Cost ↓
Worst Case Sales ↓, Cost ↑
Average Case Normal sales and cost trend

5.5 Showing Scenario Summary

  1. Open Scenario Manager.
  2. Click Summary.
  3. Select:
    • Scenario Summary
    • or Scenario PivotTable Report

Excel generates an automatic comparison report.

5.6 Practical Questions (Scenario Manager)

  1. Create Best Case, Worst Case, and Normal Case scenarios for monthly profit.
  2. Change 3 input cells (Price, Cost, Quantity) and compare results.
  3. Create a scenario report comparing 4 different business forecasts.
  4. Model salary changes for 3 different hike percentages.
  5. Prepare a scenario table for future expenses based on different inflation rates.

5. Scenario Manager

Scenario Manager is part of Excel’s What-If Analysis tools that allows you to create and compare multiple versions of data. Each scenario represents a different set of input values. This is useful in business forecasting, budgeting, planning, and decision-making.

5.1 Why Use Scenario Manager?

  • To compare different business situations.
  • To create “Best Case, Worst Case, and Average Case” forecasts.
  • To predict future outcomes based on different assumptions.
  • To change multiple input cells at once.
  • To generate summary reports automatically.

5.2 Requirements for Scenario Manager

  • Only **input cells** can be changed.
  • You can create multiple scenarios.
  • Each scenario can change up to **32 input cells**.

5.3 Steps to Create a Scenario

  1. Go to Data → What-If Analysis → Scenario Manager.
  2. Click Add.
  3. Enter:
    • Scenario Name (e.g., Best Case).
    • Changing Cells — select input cells.
  4. Enter the new values for this scenario.
  5. Click OK.
  6. Repeat for all scenarios.

5.4 Example Scenarios

Scenario Assumption
Best Case Sales ↑, Cost ↓
Worst Case Sales ↓, Cost ↑
Average Case Normal sales and cost trend

5.5 Showing Scenario Summary

  1. Open Scenario Manager.
  2. Click Summary.
  3. Select:
    • Scenario Summary
    • or Scenario PivotTable Report

Excel generates an automatic comparison report.

5.6 Practical Questions (Scenario Manager)

  1. Create Best Case, Worst Case, and Normal Case scenarios for monthly profit.
  2. Change 3 input cells (Price, Cost, Quantity) and compare results.
  3. Create a scenario report comparing 4 different business forecasts.
  4. Model salary changes for 3 different hike percentages.
  5. Prepare a scenario table for future expenses based on different inflation rates.

6. Data Table

A Data Table is a What-If Analysis tool used to analyze how changes in one or two input values affect the output. It is very useful for forecasting, pricing models, loan EMI calculations, and profit simulations.

6.1 Types of Data Tables

Type Meaning
One-Variable Data Table Uses one input and one formula.
Two-Variable Data Table Uses two inputs and one formula.

6.2 Steps for One-Variable Data Table

  1. Write formula on top.
  2. List input values vertically.
  3. Select entire range.
  4. Go to Data → What-If Analysis → Data Table.
  5. Enter value in:
    • Column Input Cell if input list is vertical.
    • Row Input Cell if input list is horizontal.

6.3 Steps for Two-Variable Data Table

  1. Write formula at top-left of the table.
  2. Enter first input horizontally.
  3. Enter second input vertically.
  4. Select full table.
  5. Go to Data Table → Fill:
    • Row Input Cell
    • Column Input Cell

6.4 Real-Life Uses

  • EMI changes with different interest rates.
  • Profit changes based on different selling prices.
  • Loan duration impact on EMI.
  • Sales forecast with multiple price–quantity combinations.

6.5 Practical Questions (Data Table)

  1. Create a one-variable table showing profit for different selling prices.
  2. Create a two-variable table showing EMI for different loan amount and interest rates.
  3. Use Data Table to check net income under different tax percentages.
  4. Make a Data Table comparing total revenue for different quantities.
  5. Use Data Table to check impact of interest rate on total loan repayment.

1. What Makes a Formula Dynamic?

  • It automatically updates when the source data changes.
  • It automatically expands or shrinks based on dataset size.
  • It does not require manual range adjustments.
  • It recalculates results instantly when values change.

Example (Static): =SUM(A1:A5)
Example (Dynamic): =SUM(A:A) or =SUM(Table1[Sales])

2. Key Dynamic Array Functions

Dynamic array functions auto-spill multiple results:

  • FILTER
  • SORT
  • SORTBY
  • UNIQUE
  • SEQUENCE
  • RANDARRAY
  • XLOOKUP
  • XMATCH

3. FILTER Function (Dynamic Extraction)

Syntax:

=FILTER(array, include, [if_empty])

Example:

=FILTER(A2:C20, B2:B20="Delhi")

This instantly extracts rows where the City = Delhi. When new data is added, the filtered result updates automatically.

4. SORT Function (Dynamic Sorting)

Syntax: =SORT(array, [sort_index], [sort_order])

Example:

=SORT(A2:B20, 2, -1)

This sorts the list by column 2 in descending order and updates automatically when new rows are added.

5. UNIQUE Function (Dynamic Unique List)

Syntax: =UNIQUE(array)

Example: =UNIQUE(A2:A100)

This creates a dynamic list of unique values. If new values are added, the list expands automatically.

6. SEQUENCE Function (Dynamic Series)

Syntax: =SEQUENCE(rows, [columns], [start], [step])

Example: =SEQUENCE(10,1,1,1)

Generates a list from 1 to 10 automatically. Useful for dashboards or calendars.

7. Combining Dynamic Functions

Example 1: =UNIQUE(SORT(A2:A200))
This returns a unique, sorted, auto-updating list.

Example 2: =FILTER(A2:C200,(B2:B200="Laptop")*(C2:C200>50000))
Extracts only rows that match multiple conditions.

8. Dynamic Ranges without Using Tables

You can create dynamic ranges using INDEX, OFFSET, and COUNTA.

Example:

=SUM(A1:INDEX(A:A,COUNTA(A:A)))

This sums all values up to the last non-empty row. Very useful for charts and reports.

9. Dynamic Named Ranges

Create through:

  1. Formulas Tab
  2. Name Manager
  3. New
  4. Use formula like: 
    =OFFSET($A$1,0,0,COUNTA($A:$A),1)

Use in formulas:

=SUM(SalesRange)

The named range expands automatically as new data is added.

10. Dynamic Lookup Formulas

XLOOKUP Example:
=XLOOKUP(E2,A2:A100,B2:B100)

INDEX-MATCH Example:

=INDEX(B2:B100, MATCH(E2, A2:A100, 0))

Both update automatically as data expands.

11. Dynamic Formulas for Dashboards

Dynamic formulas can create:

  • Dynamic dropdowns
  • Dynamic charts
  • Dynamic summaries
  • Dynamic reports

Example: Use UNIQUE(CategoryRange) for a dynamic category list.

12. Excel Tables – Easiest Dynamic Method

Convert data to a table using Ctrl + T. Formulas become dynamic automatically.

Example: =SUM(Table1[Sales])

Adding new rows automatically updates formulas and charts.

13. Deep Examples

Example 1: =FILTER(A2:C100, C2:C100>60)

Example 2: =TAKE(A2:C100, -5)

Example 3: =TAKE(SORT(A2:A100,1,-1),3)

Example 4: =FILTER(A2:C100, ISNUMBER(SEARCH(E2, A2:A100)))

14. Advanced Dynamic Formula Example

Dynamic Dependent Dropdown List

Step 1: Create category list
=UNIQUE(A2:A200)

Step 2: Create dependent list
=FILTER(B2:B200, A2:A200=F2)

When the user selects a category in F2, subcategories update automatically.

15. Common Mistakes to Avoid

  • Using fixed ranges like A1:A10 instead of A:A
  • Using static SUMIF ranges
  • Sorting manually instead of SORT function
  • Not converting data into Excel tables

16. Practical Questions

  1. Create a dynamic formula that shows all employees with salary above 50000 using FILTER.
  2. Generate a dynamic unique product category list from 300 rows.
  3. Sort sales values dynamically from highest to lowest.
  4. Extract all rows for the city Mumbai using FILTER.
  5. Create a formula that displays the last 10 entries dynamically.
  6. Combine SORT and UNIQUE to generate a unique sorted list.
  7. Use SEQUENCE to generate numbers from 1 to 50.
  8. Create a dynamic XLOOKUP formula for employee ID.
  9. Write a dynamic range using INDEX + COUNTA.
  10. Create a dynamic dependent dropdown using the FILTER function.

Dynamic Dashboard

A dynamic dashboard is an interactive Excel dashboard that updates automatically when the source data changes. It uses formulas, charts, slicers, pivot tables, and dynamic arrays to give real-time results without manually refreshing formulas or ranges.

1. What is a Dynamic Dashboard?

A dynamic dashboard automatically updates visuals (charts, tables, KPIs) when:

  • New data is added
  • A user selects a different filter or dropdown value
  • Source formulas recalculate
  • Pivot tables or slicers are refreshed

Dynamic dashboards reduce manual work and give accurate, real-time reporting.

2. Key Components of a Dynamic Dashboard

  • Excel Tables (Ctrl + T)
  • Slicers (for PivotTables/Charts)
  • PivotTables and PivotCharts
  • Dynamic Array Functions (FILTER, UNIQUE, SORT)
  • Dropdown Lists (Data Validation)
  • Dynamic Named Ranges
  • Interactive buttons or charts

The combination of all these creates a strong, fully automated dashboard.

3. Step-by-Step: How to Build a Dynamic Dashboard

Step 1: Convert Data into a Table

Insert → Table or press Ctrl + T.

Advantages:

  • Auto-expands with new data
  • Auto-updates PivotTables
  • Auto-updates formulas
  • Better readability and structure

Step 2: Create PivotTables for Summary

Insert → PivotTable → Select Excel Table.

Examples of PivotTables:

  • Total Sales by Product
  • Total Quantity by Region
  • Monthly Sales
  • Top 5 Products

PivotTables are the backbone of dynamic dashboards.

Step 3: Insert PivotCharts

Common dynamic charts:

  • Column Chart
  • Bar Chart
  • Pie Chart
  • Line Chart
  • Map Chart (if using geography data)

Charts update automatically when PivotTable updates.

Step 4: Add Slicers for Dynamic Filtering

Insert → Slicer → Select fields like:

  • City
  • Category
  • Product Name
  • Region
  • Month

Slicers allow dashboard viewers to filter charts instantly.

Step 5: Add Timeline (for Dates)

Insert → Timeline → Select Date Field.

Timelines allow dynamic filtering based on:

  • Years
  • Quarters
  • Months
  • Days

Step 6: Create Dynamic Dropdowns (Data Validation)

Use UNIQUE() function:

=UNIQUE(Table1[Category])

Assign this dynamic list to Data Validation → List.

Now users can select a category dynamically, and other formulas update.

Step 7: Apply Dynamic Formulas

Use:

  • FILTER
  • SORT
  • UNIQUE
  • SEQUENCE
  • XLOOKUP

Example: Dynamic List of Products Based on Selected Category:

=FILTER(Table1[Product], Table1[Category]=F2)

This updates automatically when the user selects a different category in F2.

Step 8: Add KPI Cards

Create KPI Metrics:

  • Total Sales
  • Total Units Sold
  • Total Customers
  • Top Product
  • Top Region

Example: Dynamic Total Sales

=SUM(Table1[Sales])

Format KPIs with:

  • Bold fonts
  • Blue borders
  • Background shading
  • Large numbers

Step 9: Arrange Dashboard Layout

Use a clean grid layout:

  • KPI Cards at top
  • Charts in the middle
  • Slicers on the side
  • Tables or detailed data at bottom

Step 10: Final Formatting

  • Use only 2–3 colors (blue, black, white background)
  • Avoid chart clutter
  • Use bold titles
  • Ensure charts are aligned neatly
  • Use consistent font style and sizes

4. Using Dynamic Array Formulas in Dashboard

Dynamic Top 5 Products

=TAKE(SORT(Table1[Sales],1,-1),5)

Dynamic Last 10 Records

=TAKE(Table1, -10)

Dynamic Employee List with >70% Performance

=FILTER(Table1, Table1[Performance]>0.7)

These dynamic lists can be connected to charts.

5. Advanced Dynamic Dashboard Techniques

A. Dynamic Named Ranges

Example:

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

This auto-updates charts and formulas.

B. Dynamically Switching Charts

Create chart selector using dropdown list.

If F3 contains dropdown (Sales, Profit, Quantity), use:

=IF(F3="Sales", Table1[Sales], IF(F3="Profit", Table1[Profit], Table1[Quantity]))

C. Interactive Search Box

Dynamic Search Formula:

=FILTER(Table1, ISNUMBER(SEARCH(H2, Table1[Product])))

User types a keyword in H2; table updates live.

6. Practical Dynamic Dashboard Example

Suppose your table contains:

  • Date
  • Region
  • Category
  • Product
  • Sales
  • Quantity

You can create:

  • KPI Card: Total Sales
  • KPI Card: Total Quantity
  • Chart: Sales by Region
  • Chart: Category-wise Sales
  • Slicer: Region
  • Slicer: Category
  • Dynamic Product List based on Category dropdown

7. Dynamic Dashboard Practical Questions

  1. Create a dynamic dashboard that updates when new sales data is added into an Excel table.
  2. Add slicers for Region and Category to filter a PivotChart.
  3. Create three KPI cards: Total Sales, Total Orders, Total Quantity.
  4. Create a dynamic dropdown list of product categories using UNIQUE.
  5. Create a dynamic list of products based on selected category using FILTER.
  6. Add a timeline to your dashboard for Year and Month selection.
  7. Use SORT+TAKE to display Top 5 selling products dynamically.
  8. Create a dynamic chart that changes based on a dropdown (Sales, Profit, Quantity).
  9. Design a search box using FILTER + SEARCH that updates the product list dynamically.
  10. Build a dynamic dependent chart using dynamic named ranges.
- No comments:

Tuesday, December 23, 2025

Chapter : - 5 Loops & Events in VBA – Repeat, Respond, Automate

VBA Loops & Events – Smart Coding Essentials 




5.1 What is a Loop?

A loop in VBA is a programming structure that repeats a block of code multiple times until a certain condition is met. Loops help automate repetitive tasks efficiently.

5.2 Types of Loops in VBA

  • For...Next Loop: Repeats code a specific number of times.
  • For Each...Next Loop: Loops through all items in a collection (like worksheets, cells).
  • Do While...Loop: Repeats code while a condition is True.
  • Do Until...Loop: Repeats code until a condition becomes True.
  • While...Wend: Older loop, rarely used.

5.3 Syntax & Examples

Example 1: For...Next Loop 
Sub ForLoopExample()
    Dim i As Integer
    For i = 1 To 5
        MsgBox "Number: " & i
    Next i
End Sub
Example 2: For Each...Next Loop 
Sub ForEachExample()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        MsgBox "Sheet Name: " & ws.Name
    Next ws
End Sub
Example 3: Do While Loop 
Sub DoWhileExample()
    Dim i As Integer
    i = 1
    Do While i <= 5
        MsgBox "Count: " & i
        i = i + 1
    Loop
End Sub
Example 4: Do Until Loop 
Sub DoUntilExample()
    Dim i As Integer
    i = 1
    Do Until i > 5
        MsgBox "Number: " & i
        i = i + 1
    Loop
End Sub

5.4 Real-Life Use Cases

  • Loop through all worksheets to update formatting.
  • Loop through cells to calculate totals or check values.
  • Automate repetitive reports or messages.

Chapter 6 – Workbook & Worksheet Events

6.1 What are Events in VBA?

An event is an action that occurs in Excel, such as opening a workbook, changing a cell, or clicking a button. VBA can respond to these events using event procedures.

6.2 Workbook Events

  • Workbook_Open: Runs code when the workbook is opened.
  • Workbook_BeforeClose: Runs code before the workbook closes.
  • Workbook_SheetChange: Runs code whenever any sheet changes.
Example: Workbook_Open 
Private Sub Workbook_Open()
    MsgBox "Welcome! Workbook is now open."
End Sub

6.3 Worksheet Events

  • Worksheet_Change: Runs code when a cell value changes.
  • Worksheet_SelectionChange: Runs code when the selection changes.
  • Worksheet_BeforeDoubleClick: Runs code before double-clicking a cell.
Example: Worksheet_Change 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value > 100 Then
        MsgBox "Value is greater than 100!"
    End If
End Sub
Example: Worksheet_SelectionChange 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "You selected cell: " & Target.Address
End Sub

6.4 Real-Life Applications

  • Auto-generate reports when workbook opens.
  • Highlight or validate cells automatically when data changes.
  • Display reminders, alerts, or messages on workbook/worksheet actions.

6.5 Practice Exercises

  • Write a Workbook_Open event that shows your name.
  • Create a Worksheet_Change event that alerts if a value exceeds 500.
  • Create a Worksheet_SelectionChange event to display the selected cell address.
- No comments:

Chapter 4: Conditional Statements in VBA – Making Decisions with Code

VBA Conditions – Teach Your Program to Think





Conditional statements allow your VBA program to make decisions. They check a condition and run different blocks of code based on whether the condition is True or False. These are used whenever your program needs to choose between multiple paths.

4.1 What are Conditional Statements?

Conditional statements are used when you want your code to behave differently based on given conditions. Example: If marks are 40 or more, student passes. Otherwise, fails.

4.2 Types of Conditional Statements in VBA

  • If...Then
  • If...Then...Else
  • If...ElseIf...Else
  • Select Case

4.3 If...Then Statement

This runs code only if the condition is True.

Syntax:
If condition Then
   Statement
End If

Example:

Sub IfExample()
    Dim age As Integer
    age = 20

    If age >= 18 Then
        MsgBox "You are eligible to vote."
    End If
End Sub

Output: You are eligible to vote.

4.4 If...Then...Else Statement

This structure runs one block if the condition is True, otherwise another block runs. 

Sub PassFailExample()
    Dim marks As Integer
    marks = 35

    If marks >= 40 Then
        MsgBox "Pass"
    Else
        MsgBox "Fail"
    End If
End Sub

Output: Fail

4.5 If...ElseIf...Else Statement

Used when multiple conditions need to be checked one by one. 

Sub GradeExample()
    Dim marks As Integer
    marks = 85

    If marks >= 90 Then
        MsgBox "Grade A+"
    ElseIf marks >= 75 Then
        MsgBox "Grade A"
    ElseIf marks >= 60 Then
        MsgBox "Grade B"
    Else
        MsgBox "Grade C"
    End If
End Sub

Output: Grade A

4.6 Select Case Statement

Select Case is used when there are multiple clear categories or values. It is cleaner and more readable than multiple ElseIf statements.

Syntax:
Select Case expression
   Case value1
      Statements
   Case value2
      Statements
End Select

Example:

Sub DayExample()
    Dim dayNum As Integer
    dayNum = 3

    Select Case dayNum
        Case 1
            MsgBox "Monday"
        Case 2
            MsgBox "Tuesday"
        Case 3
            MsgBox "Wednesday"
        Case 4
            MsgBox "Thursday"
        Case 5
            MsgBox "Friday"
        Case 6
            MsgBox "Saturday"
        Case 7
            MsgBox "Sunday"
        Case Else
            MsgBox "Invalid Day"
    End Select
End Sub

Output: Wednesday

4.7 Real-Life Uses of Conditional Statements

  • Check if a student passed or failed
  • Calculate discounts based on purchase amount
  • Show salary category based on salary range
  • Identify weekdays and weekends
  • Check login status (successful or failed)

4.8 Practice Exercises

  1. Write a program to check if a number is positive or negative.
  2. Write a program to check if a person is eligible for a driving license (age >= 18).
  3. Write a program to assign grades using If...ElseIf.
  4. Write a program using Select Case to display the month name (1–12).
  5. Write a program to check if a number is even or odd.
- No comments:

Chapter 3: Variables in VBA – The Memory Power of Your Code

  Variables in VBA – Explained for Beginners”

3.1 What are Variables?

A variable is a temporary storage location used in VBA to store values during program execution. It allows your program to remember data and use it later.

Example: If you want to store a student's name, marks, or product price, you must use variables.

3.2 Why Do We Use Variables?

  • To store data temporarily.
  • To use the same value multiple times.
  • To make programs dynamic and flexible.
  • To perform calculations.
  • To improve readability and structure.

3.3 Syntax for Declaring Variables

Examples:

3.4 Rules for Naming Variables

  • Must start with a letter.
  • No spaces allowed (use underscore instead).
  • No special characters like @, #, %, etc.
  • Avoid VBA keywords (If, Loop, Dim etc.).
  • Use meaningful names (marks, totalSalary, studentName).

3.5 Variable Scope (Very Important)

Scope means where the variable can be accessed.

Example: Using Variables

Sub VariableExample()
   Dim name As String
   Dim age As Integer
   Dim salary As Double

   name = "Amit"
   age = 25
   salary = 45000.5

   MsgBox "Name: " & name & vbCrLf & _
      "Age: " & age & vbCrLf & _
      "Salary: " & salary
End Sub

Output:
Name: Amit
Age: 25
Salary: 45000.5


- No comments:
Subscribe to: Posts (Atom)