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
Jan	5000
Feb	6000

Sheet2 – South Region

Month	Sales
Jan	7000
Feb	5500

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.