60-Day Excel Mastery Course by RUDRA | Complete Formula Database

01

Excel Interface & Basic Navigation

Current

Understanding the Excel Interface

Microsoft Excel is the world's most powerful spreadsheet application, used by over 750 million people worldwide. Before diving into formulas and functions, it's crucial to understand the interface thoroughly.

The Ribbon Interface: Introduced in Excel 2007, the Ribbon replaced the traditional menu system. It's organized into tabs (Home, Insert, Page Layout, Formulas, Data, Review, View) with each tab containing groups of related commands.

The Workbook Structure: An Excel file is called a Workbook. Each workbook can contain multiple Worksheets. Think of a workbook as a book and worksheets as individual pages.

Cell Addressing System: Excel uses a grid system where columns are labeled with letters (A, B, C... XFD) and rows with numbers (1 to 1,048,576). Each cell has a unique address combining its column letter and row number.

Essential Keyboard Shortcuts

Shortcut	Action	Description
Ctrl + C	Copy	Copies selected cells to clipboard
Ctrl + V	Paste	Pastes clipboard content
Ctrl + Z	Undo	Reverses the last action
Ctrl + S	Save	Saves the current workbook
F2	Edit cell	Enters edit mode for the active cell
Ctrl + Home	Go to A1	Jumps to cell A1 instantly

💡 Pro Tips

Double-click the column border between headers to auto-fit column width
Use Ctrl+` (backtick) to toggle formula view
Right-click tabs to access sheet options quickly

📝 Day 1 Assessment

You need at least 3 correct answers to unlock the next day!

1. What is the maximum number of rows in an Excel worksheet?

A 65,536

B 1,048,576

C 16,384

D 256

2. Which shortcut opens the Go To dialog?

A Ctrl + F

B Ctrl + H

C Ctrl + G

D Ctrl + T

3. What is the last column in Excel?

A ZZ

B XFD

C IV

D AAA

4. Which key enters edit mode for a cell?

A F2

B F4

C Enter

D Tab

5. What does Ctrl + Home do?

A Goes to last cell

B Goes to cell A1

C Opens Home tab

D Inserts new row

02

Basic Formulas & Arithmetic Operations

Locked

Introduction to Formulas

Every formula in Excel begins with an equals sign (=). This tells Excel that what follows is a calculation, not just text.

Excel follows mathematical order of operations (PEMDAS/BODMAS): Parentheses, Exponents, Multiplication/Division, Addition/Subtraction.

SUM

=SUM(number1, [number2], ...)

Adds all numbers in a range. Example: =SUM(A1:A10) adds all values from A1 to A10.

AVERAGE

=AVERAGE(number1, [number2], ...)

Returns the average (arithmetic mean) of the arguments.

Example: Basic Calculations

=SUM(A1:A10)

Result: Adds all values from A1 to A10

📝 Day 2 Assessment

You need at least 3 correct answers to unlock the next day!

1. What character must start every Excel formula?

A +

B =

C @

D #

2. Which function finds the average of a range?

A MEAN

B AVERAGE

C AVG

D MED

3. What is the result of =2+3*4?

A 20

B 14

C 24

D 10

4. Which operator is used for division?

A \

B /

C ÷

D :

5. What does =MAX(1,5,3) return?

A 1

B 5

C 3

D 9

03

Text Functions & String Manipulation

Locked

Working with Text

Excel isn't just for numbers - it's a powerful text manipulation tool. Text functions help you extract, combine, and transform text data.

CONCATENATE / &

=CONCATENATE(text1, text2, ...)

Joins text strings together. The & operator can also be used: =A1 & " " & B1

LEFT / RIGHT / MID

=LEFT(text, num_chars) / =RIGHT(text, num_chars) / =MID(text, start, num_chars)

Extracts characters from the left, right, or middle of a text string.

📝 Day 3 Assessment

You need at least 3 correct answers to unlock the next day!

1. What does =LEFT("Microsoft", 5) return?

A Micro

B osoft

C Micros

D Microsoft

2. Which function removes extra spaces from text?

A CLEAN

B TRIM

C STRIP

D REMOVE

3. What is the difference between FIND and SEARCH?

A FIND is faster

B SEARCH is case-insensitive

C FIND works with numbers only

D No difference

4. What does =LEN("Hello World") return?

A 10

B 11

C 12

D 2

5. Which function converts "hello" to "HELLO"?

A PROPER

B UPPER

C CAPS

D CAPITALIZE

04

IF Statements - Logical Decision Making

Locked

Introduction to IF Statements

The IF function is one of Excel's most powerful logical functions. It allows you to make decisions based on conditions, returning different values depending on whether a condition is TRUE or FALSE.

IF statements are the foundation of conditional logic in Excel. They help automate decision-making processes, data validation, and dynamic calculations.

IF Function

=IF(logical_test, value_if_true, value_if_false)

Tests a condition and returns one value if TRUE, another if FALSE.

📊 Practical Example - Pass/Fail Grading

=IF(A1>=60, "Pass", "Fail")

If cell A1 contains 75, the result is "Pass"

Comparison Operators

= (equal), <> (not equal), > (greater), < (less), >= (greater or equal), <= (less or equal)

Used within IF statements to compare values.

💡 Pro Tips

Use quotation marks for text results: =IF(A1>10, "High", "Low")
Numbers don't need quotes: =IF(A1>10, 100, 50)
Leave value_if_false empty for blank: =IF(A1>10, "Yes", "")

📝 Day 4 Assessment

You need at least 3 correct answers to unlock the next day!

1. What does =IF(10>5, "Yes", "No") return?

A No

B Yes

C TRUE

D FALSE

2. Which operator means "not equal to"?

A <>

B !=

C =/=

D !!

3. What does =IF(A1="", "Empty", "Has Data") check for?

A If A1 equals zero

B If A1 contains a space

C If A1 is blank/empty

D If A1 has an error

4. How many arguments does the IF function have?

A 2

B 3

C 4

D 1

5. What does =IF(5>10, 100, 200) return?

A 200

B 100

C FALSE

D Error

05

Nested IF & IFS Functions

Locked

Advanced Conditional Logic

When you need to test multiple conditions, you can nest IF functions inside each other. Excel 2016+ also introduced the IFS function for cleaner multi-condition logic.

Nested IF

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, default)))

Chain multiple IF statements to test several conditions in sequence.

📊 Grade Calculator Example

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

Assigns letter grades based on numeric scores

IFS Function (Excel 2016+)

=IFS(condition1, result1, condition2, result2, ..., TRUE, default)

Cleaner alternative to nested IFs. Tests conditions in order and returns the first TRUE result.

AND / OR Functions

=IF(AND(A1>10, B1<5), "Yes", "No") / =IF(OR(A1>10, B1<5), "Yes", "No")

Combine multiple conditions - AND requires all true, OR requires at least one true.

💡 Pro Tips

In IFS, use TRUE as the last condition for a default value
Excel allows up to 64 nested IF functions (but don't!)
Consider SWITCH function for exact value matching

📝 Day 5 Assessment

You need at least 3 correct answers to unlock the next day!

1. What is the maximum number of nested IF functions in Excel?

A 7

B 64

C 255

D Unlimited

2. What does =IF(AND(5>3, 10>8), "Yes", "No") return?

A No

B TRUE

C Yes

D Error

3. In IFS, what should you use as the last condition for a default value?

A TRUE

B ELSE

C DEFAULT

D OTHERWISE

4. What does =IF(OR(1>5, 3>2), "Yes", "No") return?

A No

B Yes

C Error

D FALSE

5. Which function is a cleaner alternative to nested IFs in Excel 2016+?

A CHOOSE

B SWITCH

C IFS

D CASE

06

VLOOKUP Fundamentals

Locked

Introduction to VLOOKUP

VLOOKUP (Vertical Lookup) is one of Excel's most popular functions. It searches for a value in the first column of a range and returns a value from a specified column in the same row.

VLOOKUP is essential for data retrieval, connecting data from different tables, and building dynamic reports.

VLOOKUP Function

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

lookup_value: Value to search for
table_array: Range containing your data
col_index_num: Column number to return (1, 2, 3...)
range_lookup: FALSE for exact match, TRUE for approximate

📊 Employee Lookup Example

=VLOOKUP("E001", A2:D100, 3, FALSE)

Finds employee ID "E001" and returns the value from the 3rd column (e.g., Department)

💡 Pro Tips

Always use FALSE for exact match in most cases
The lookup value must be in the FIRST column of your table
VLOOKUP only searches LEFT to RIGHT, never backwards
Use absolute references ($A$2:$D$100) when copying formulas

📝 Day 6 Assessment

You need at least 3 correct answers to unlock the next day!

1. What does the "V" in VLOOKUP stand for?

A Value

B Variable

C Vertical

D Vector

2. What should range_lookup be for an exact match?

A TRUE

B FALSE

C 1

D EXACT

3. Which column must contain the lookup value in VLOOKUP?

A First column

B Last column

C Any column

D Middle column

4. How many arguments does VLOOKUP have?

A 2

B 3

C 5

D 4

5. What error does VLOOKUP return when it can't find the value?

A #VALUE!

B #N/A

C #REF!

D #NULL!

07

Advanced VLOOKUP & HLOOKUP

Locked

Taking VLOOKUP Further

Master advanced techniques like approximate matching, combining VLOOKUP with other functions, and using HLOOKUP for horizontal data.

HLOOKUP Function

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

Like VLOOKUP but searches horizontally across rows instead of vertically down columns.

VLOOKUP with IFERROR

=IFERROR(VLOOKUP(A1, B:D, 2, FALSE), "Not Found")

Handles #N/A errors gracefully by returning a custom message.

📊 Approximate Match for Tax Brackets

=VLOOKUP(75000, TaxTable, 2, TRUE)

Finds the tax rate for income of $75,000 using approximate match (data must be sorted)

💡 Pro Tips

For approximate match (TRUE), data MUST be sorted in ascending order
Use XLOOKUP in Excel 365 - it's more powerful and flexible
Combine with MATCH for dynamic column selection

📝 Day 7 Assessment

You need at least 3 correct answers to unlock the next day!

1. What does HLOOKUP search?

A Vertically down columns

B Horizontally across rows

C Diagonally

D In all directions

2. For approximate match (TRUE) to work correctly, the data must be:

A Unsorted

B Sorted descending

C Sorted ascending

D Alphabetical only

3. What function handles VLOOKUP errors gracefully?

A IFERROR

B ISERROR

C ERROR

D NOERROR

4. Which is more powerful and flexible than VLOOKUP in Excel 365?

A LOOKUP

B XLOOKUP

C HLOOKUP

D SEARCH

5. In HLOOKUP, row_index_num refers to:

A Column number to return

B Absolute row number

C Number of rows to skip

D Row number to return

08

Conditional Formatting Basics

Locked

Visual Data Analysis

Conditional Formatting automatically applies formatting (colors, icons, data bars) to cells based on their values. It's a powerful tool for visual data analysis and spotting trends.

Access it via Home → Conditional Formatting or use keyboard shortcut Alt + H + L

Built-in Rules

Rule Type	Use Case
Highlight Cells Rules	Greater than, Less than, Between, Equal to, Text contains
Top/Bottom Rules	Top 10 items, Bottom 10%, Above/Below average
Data Bars	Visual bars showing relative values
Color Scales	Gradient colors based on value ranges
Icon Sets	Arrows, flags, ratings to show trends

💡 Pro Tips

Use "Manage Rules" to see all formatting rules on a sheet
Rules are processed in order - first match wins
Clear rules with Home → Conditional Formatting → Clear Rules

📝 Day 8 Assessment

You need at least 3 correct answers to unlock the next day!

1. Where is Conditional Formatting found in the Ribbon?

A Insert tab

B Home tab

C Data tab

D View tab

2. Which option shows visual bars inside cells?

A Data Bars

B Icon Sets

C Color Scales

D Sparklines

3. What does "Color Scales" do?

A Adds icons to cells

B Draws bars in cells

C Applies gradient colors based on values

D Changes font size

4. How do you view all conditional formatting rules on a sheet?

A View → Rules

B Conditional Formatting → Manage Rules

C Format → Show Rules

D Data → Format Rules

5. Icon Sets are useful for showing:

A Trends and ratings

B Exact values

C Formulas

D Cell references

09

Advanced Conditional Formatting

Locked

Custom Formulas in Conditional Formatting

The real power of conditional formatting comes from using custom formulas. This allows you to create complex rules based on any logic you can express in a formula.

Formula-Based Rule

=A1>AVERAGE($A$1:$A$100)

Highlight cells above average. The formula must return TRUE or FALSE.

📊 Highlight Entire Row Based on Status

=($D1="Complete")

Apply to entire row range - highlights rows where column D says "Complete"

📊 Highlight Duplicates

=COUNTIF($A$1:$A$100, A1)>1

Highlights all cells that appear more than once in the range

💡 Pro Tips

Use $ for absolute references when you want the same cell checked for all rows
Use mixed references ($A1) to lock column but allow row to change
The formula should be written for the FIRST cell in your selection
Test your formula in a cell first to ensure it returns TRUE/FALSE

📝 Day 9 Assessment

You need at least 3 correct answers to unlock the next day!

1. What must a conditional formatting formula return?

A A number

B Text

C TRUE or FALSE

D A color code

2. To highlight an entire row based on one column, you use:

A Relative reference (A1)

B Mixed reference ($A1)

C Absolute reference ($A$1)

D No reference needed

3. =COUNTIF($A$1:$A$100, A1)>1 highlights:

A Duplicate values

B Unique values

C Empty cells

D Error values

4. The formula in a rule should be written for:

A The last cell in selection

B Any cell in selection

C The middle cell

D The first cell in selection

5. Which formula highlights values above average?

A =A1>SUM($A$1:$A$10)

B =A1>AVERAGE($A$1:$A$10)

C =A1>MAX($A$1:$A$10)

D =A1>COUNT($A$1:$A$10)

10

INDEX & MATCH - The VLOOKUP Alternative

Locked

Why INDEX & MATCH?

INDEX & MATCH is a powerful combination that overcomes VLOOKUP's limitations. It can look up values in any direction and is more flexible and efficient.

MATCH Function

=MATCH(lookup_value, lookup_array, [match_type])

Returns the position (row or column number) of a value in a range. Use 0 for exact match.

INDEX Function

=INDEX(array, row_num, [column_num])

Returns the value at a specific position in a range.

INDEX & MATCH Combined

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

MATCH finds the position, INDEX returns the value from that position.

📊 Left Lookup Example (Impossible with VLOOKUP)

=INDEX(A2:A100, MATCH("Product X", C2:C100, 0))

Looks up "Product X" in column C and returns the value from column A (to the left!)

💡 Pro Tips

INDEX MATCH can look up in any direction - left, right, up, or down
It's generally faster than VLOOKUP on large datasets
Column insertions don't break INDEX MATCH formulas
Use MATCH with 0 for exact match, 1 for less than, -1 for greater than

📝 Day 10 Assessment

You need at least 3 correct answers to unlock the next day!

1. What does the MATCH function return?

A The value found

B The position of the value

C TRUE or FALSE

D The cell address

2. What match_type value should you use for exact match in MATCH?

A 1

B -1

C 0

D FALSE

3. What is a major advantage of INDEX MATCH over VLOOKUP?

A Can look up values to the left

B Shorter formula

C Easier to remember

D Works without a table

4. In INDEX(A1:A10, 5), what does 5 represent?

A The value to find

B The row number to return

C The column number

D The number of results

5. Why is INDEX MATCH more robust when columns are inserted?

A It doesn't use column numbers

B It auto-updates

C It references specific ranges, not relative column positions

D It ignores new columns

11

PivotTable Fundamentals

Locked

Introduction to PivotTables

PivotTables are one of Excel's most powerful features for data analysis. They allow you to summarize, analyze, and explore large datasets with just a few clicks.

With PivotTables, you can quickly transform thousands of rows of data into meaningful summaries, calculate totals, averages, counts, and much more without writing any formulas.

Creating a PivotTable

Step	Action
1	Select any cell in your data range
2	Go to Insert → PivotTable
3	Choose where to place the PivotTable
4	Drag fields to Rows, Columns, Values, or Filters

💡 Pro Tips

Your data should have headers in the first row
Avoid blank rows or columns in your source data
Use Alt + N + V to quickly insert a PivotTable
Right-click on values to access summarize options

📝 Day 11 Assessment

You need at least 3 correct answers to unlock the next day!

1. Where do you find the PivotTable option in Excel?

A Home tab

B Insert tab

C Data tab

D View tab

2. What should your data have in the first row for PivotTables?

A Numbers

B Formulas

C Headers

D Blank cells

3. Which area do you drag numeric fields to for calculations?

A Values

B Rows

C Columns

D Filters

4. What should you avoid in source data for PivotTables?

A Numbers

B Blank rows or columns

C Text data

D Dates

5. How do you access summarize options for values?

A Double-click

B Press F2

C Press Enter

D Right-click

12

Advanced PivotTables

Locked

Grouping & Calculated Fields

Take your PivotTables to the next level with grouping, calculated fields, and slicers. These features let you customize your analysis without modifying source data.

Key Advanced Features

Feature	Purpose
Grouping	Group dates by month/quarter/year, or numbers by ranges
Calculated Fields	Create new fields using formulas on existing fields
Slicers	Visual filters for easy interactive analysis
Timelines	Filter date data with an interactive timeline
Show Values As	Display as % of total, running total, difference, etc.

💡 Pro Tips

Use Insert → Slicer to add visual filters
Group dates: Right-click a date → Group
Calculated Field: PivotTable Analyze → Fields, Items & Sets
Refresh PivotTable with Alt + F5

📝 Day 12 Assessment

You need at least 3 correct answers to unlock the next day!

1. What is a Slicer used for?

A Creating charts

B Formatting cells

C Visual filtering

D Sorting data

2. How do you group dates in a PivotTable?

A Double-click the date

B Right-click → Group

C Use the DATE function

D Press Ctrl+G

3. Where do you create a Calculated Field?

A PivotTable Analyze → Fields, Items & Sets

B Insert → Calculated Field

C Data → New Field

D Home → Format

4. What shortcut refreshes a PivotTable?

A Ctrl + R

B F5

C Ctrl + F5

D Alt + F5

5. "Show Values As" can display data as:

A Only raw numbers

B % of total, running total, difference

C Only text

D Colors only

13

Charts Fundamentals

Locked

Data Visualization with Charts

Charts transform raw data into visual stories. Excel offers numerous chart types, each suited for different kinds of data and messages you want to convey.

Common Chart Types

Chart Type	Best Used For
Column/Bar	Comparing categories or showing changes over time
Line	Showing trends over time
Pie/Doughnut	Showing parts of a whole (percentages)
Scatter	Showing correlation between two variables
Area	Showing cumulative totals over time

💡 Pro Tips

Select data first, then press Alt + F1 for instant chart
Use F11 to create a chart on a new sheet
Limit pie charts to 5-7 slices for readability
Right-click chart elements to format them

📝 Day 13 Assessment

You need at least 3 correct answers to unlock the next day!

1. Which chart type is best for showing trends over time?

A Pie chart

B Line chart

C Scatter chart

D Doughnut chart

2. What shortcut creates an instant chart?

A Alt + F1

B Ctrl + C

C Ctrl + F1

D F5

3. Pie charts are best for showing:

A Trends over time

B Correlation between variables

C Parts of a whole

D Large datasets

4. What does F11 do with selected data?

A Deletes the data

B Creates a chart on a new sheet

C Opens the Help menu

D Prints the worksheet

5. Scatter charts are best for showing:

A Correlation between two variables

B Parts of a whole

C Category comparisons

D Text data

14

Advanced Charts & Formatting

Locked

Professional Chart Design

Learn to create professional-quality charts with combo charts, sparklines, trendlines, and advanced formatting options.

Advanced Chart Features

Feature	Description
Combo Charts	Combine two chart types (e.g., column + line)
Secondary Axis	Display data with different scales
Trendlines	Show patterns and forecast future values
Sparklines	Mini charts that fit in a single cell
Data Labels	Show values directly on chart elements

💡 Pro Tips

Add Trendline: Right-click data series → Add Trendline
Sparklines are found in Insert → Sparklines
Use Chart Templates to save and reuse formatting
Double-click any chart element to format it

📝 Day 14 Assessment

You need at least 3 correct answers to unlock the next day!

1. What is a Sparkline?

A A large dashboard chart

B A 3D chart effect

C A mini chart in a single cell

D An animated chart

2. How do you add a Trendline to a chart?

A Insert → Trendline

B Right-click data series → Add Trendline

C Data → Add Trendline

D Format → Trendline

3. When do you need a Secondary Axis?

A When data has different scales

B When making pie charts

C Always

D For 3D effects

4. Where are Sparklines found in Excel?

A Home tab

B Data tab

C View tab

D Insert tab

5. What is a Combo Chart?

A Two separate charts

B A chart that combines two chart types

C A chart with multiple colors

D A linked chart

15

Data Validation

Locked

Controlling Data Entry

Data Validation restricts the type of data users can enter into cells. It helps maintain data integrity and prevents errors at the source.

Validation Types

Type	Use Case
List	Create dropdown menus with predefined options
Whole Number	Allow only integers within a range
Decimal	Allow decimal numbers within a range
Date	Restrict to dates within a range
Text Length	Limit the number of characters
Custom	Use a formula for complex validation

💡 Pro Tips

Access via Data → Data Validation
Add Input Message to guide users
Add Error Alert for invalid entries
Use named ranges for dynamic dropdown lists

📝 Day 15 Assessment

You need at least 3 correct answers to unlock the next day!

1. Where is Data Validation found?

A Home tab

B Data tab

C Insert tab

D View tab

2. Which validation type creates dropdown menus?

A Whole Number

B Custom

C List

D Text Length

3. What does "Input Message" do in Data Validation?

A Shows guidance when cell is selected

B Shows error when data is invalid

C Deletes invalid data

D Formats the cell

4. Which type allows using formulas for validation?

A List

B Custom

C Date

D Whole Number

5. For dynamic dropdowns, you should use:

A Direct cell references

B Typed values

C Numbers only

D Named ranges

16

SUMIF, COUNTIF & AVERAGEIF

Locked

Conditional Aggregation Functions

These powerful functions let you sum, count, or average values based on criteria. They combine the power of IF logic with aggregation.

SUMIF

=SUMIF(range, criteria, [sum_range])

Sums values where the criteria is met. E.g., =SUMIF(A:A, "Apple", B:B) sums column B where column A contains "Apple".

COUNTIF

=COUNTIF(range, criteria)

Counts cells matching the criteria. E.g., =COUNTIF(A:A, ">100") counts cells greater than 100.

AVERAGEIF

=AVERAGEIF(range, criteria, [average_range])

Averages values where the criteria is met.

💡 Pro Tips

Use wildcards: * (any characters), ? (single character)
Criteria with operators need quotes: ">100", "<>0"
SUMIFS, COUNTIFS, AVERAGEIFS handle multiple criteria

📝 Day 16 Assessment

You need at least 3 correct answers to unlock the next day!

1. What does =COUNTIF(A:A, "Apple") do?

A Sums cells containing "Apple"

B Averages cells containing "Apple"

C Counts cells containing "Apple"

D Finds cells containing "Apple"

2. How do you write criteria for "greater than 100"?

A >100

B ">100"

C GT(100)

D GREATER(100)

3. What wildcard matches any number of characters?

A * (asterisk)

B ? (question mark)

C # (hash)

D % (percent)

4. Which function handles multiple criteria?

A SUMIF

B SUMIFS

C SUMALL

D MULTISUM

5. In SUMIF, what is the third argument?

A The criteria

B The criteria range

C The sum range

D The output cell

17

Sorting & Filtering Data

Locked

Organizing Your Data

Sorting and Filtering are essential skills for analyzing data. Sort to reorder data; filter to show only what you need.

Sorting Options

Method	Description
Quick Sort	A-Z or Z-A buttons on Data tab
Custom Sort	Sort by multiple columns with custom order
Sort by Color	Sort by cell color or font color

AutoFilter

Enable AutoFilter with Ctrl + Shift + L or Data → Filter. Click dropdown arrows to filter data.

💡 Pro Tips

Always include headers when sorting
Use Custom Sort for multi-level sorting
Filter by color, text, or number conditions
Clear filters: Data → Clear or Alt + A + C

📝 Day 17 Assessment

You need at least 3 correct answers to unlock the next day!

1. What shortcut enables AutoFilter?

A Ctrl + F

B Ctrl + Shift + L

C Ctrl + L

D Alt + F

2. Custom Sort allows you to:

A Sort by multiple columns

B Sort only one column

C Delete duplicate rows

D Merge cells

3. Where is the Filter option located?

A Home tab

B Insert tab

C Data tab

D View tab

4. When sorting data, you should always:

A Exclude headers

B Include headers

C Delete headers first

D Select only numbers

5. You can sort by:

A Cell color and font color

B Only values

C Only text

D Only dates

18

Advanced Filtering

Locked

Beyond Basic Filters

Advanced Filter offers more complex filtering with criteria ranges, extracting unique values, and copying filtered data to another location.

Advanced Filter Features

Feature	Purpose
Criteria Range	Define complex AND/OR conditions
Copy to Location	Extract filtered data to another area
Unique Records	Filter to show only unique values
Formula Criteria	Use formulas in criteria range

💡 Pro Tips

Access via Data → Advanced
Criteria on same row = AND; different rows = OR
Use "Unique records only" to remove duplicates
The criteria range headers must match data headers exactly

📝 Day 18 Assessment

You need at least 3 correct answers to unlock the next day!

1. Where is Advanced Filter located?

A Home → Filter

B Insert → Advanced

C Data → Advanced

D View → Filter

2. In criteria range, conditions on the same row represent:

A OR logic

B AND logic

C NOT logic

D XOR logic

3. How do you extract only unique values?

A Check "Unique records only"

B Use UNIQUE function

C Delete duplicates first

D Sort and filter

4. The criteria range headers must:

A Be numbered

B Be different from data headers

C Be blank

D Match data headers exactly

5. Conditions on different rows in criteria range represent:

A AND logic

B OR logic

C NOT logic

D No logic applied

19

Statistical Functions

Locked

Data Analysis with Statistics

Excel offers a comprehensive suite of statistical functions for analyzing data distributions, trends, and relationships.

Central Tendency

=AVERAGE(range), =MEDIAN(range), =MODE.SNGL(range)

AVERAGE: arithmetic mean. MEDIAN: middle value. MODE: most frequent value.

Dispersion

=STDEV.S(range), =VAR.S(range), =RANGE(MAX-MIN)

Standard deviation and variance measure data spread around the mean.

Other Useful Functions

=LARGE(range, k), =SMALL(range, k), =PERCENTILE(range, k)

LARGE/SMALL: kth largest/smallest value. PERCENTILE: value at a given percentile.

💡 Pro Tips

STDEV.S for sample, STDEV.P for population
=LARGE(A:A, 2) gives the 2nd largest value
=PERCENTILE(A:A, 0.9) gives the 90th percentile
Use RANK.EQ to rank values in a list

📝 Day 19 Assessment

You need at least 3 correct answers to unlock the next day!

1. Which function finds the middle value in a dataset?

A AVERAGE

B MEDIAN

C MODE

D MID

2. What does =LARGE(A:A, 3) return?

A The largest value

B The smallest value

C The 3rd largest value

D 3 times the largest

3. STDEV.S is used for:

A Sample standard deviation

B Population standard deviation

C Simple deviation

D String deviation

4. What does =PERCENTILE(A:A, 0.9) return?

A 9% of the values

B Value at the 90th percentile

C 90% of the maximum

D The 9th value

5. MODE returns:

A The average value

B The middle value

C The largest value

D The most frequent value

20

Data Analysis Tools

Locked

Excel Data Analysis Toolpack

Excel's Data Analysis Toolpak provides advanced statistical analysis including regression, correlation, t-tests, and more.

Built-in Analysis Tools

Tool	Use Case
Goal Seek	Find input value to achieve desired result
Scenario Manager	Compare multiple what-if scenarios
Data Tables	See how changes affect formula results
Solver	Optimize with constraints (add-in)
Descriptive Statistics	Generate summary statistics

💡 Pro Tips

Enable Toolpak: File → Options → Add-ins → Analysis Toolpak
Goal Seek: Data → What-If Analysis → Goal Seek
Scenario Manager is great for budgeting and forecasting
Use Data Tables for sensitivity analysis

📝 Day 20 Assessment

You need at least 3 correct answers to unlock the next day!

1. What does Goal Seek do?

A Creates charts

B Sorts data

C Finds input value to achieve desired result

D Filters data

2. Where is Goal Seek located?

A Home → Goal Seek

B Data → What-If Analysis → Goal Seek

C Insert → Goal Seek

D View → Goal Seek

3. How do you enable the Data Analysis Toolpak?

A File → Options → Add-ins

B Data → Enable Toolpak

C It's always enabled

D View → Add-ins

4. Scenario Manager is useful for:

A Creating presentations

B Comparing multiple what-if scenarios

C Sending emails

D Printing reports

5. Data Tables are used for:

A Storing data

B Creating databases

C Sensitivity analysis

D Formatting cells

Day 21

Introduction to Macros

Locked 🔒

📖 What You'll Learn

Understanding what macros are and their purpose
Recording your first macro
Running and managing macros
Macro security settings
Saving macro-enabled workbooks

⌨️ Essential Shortcuts

Alt + F8 Open Macro dialog

Alt + F11 Open VBA Editor

Ctrl + Shift + R Record Macro (custom)

💡 Key Concepts

Macro: A recorded sequence of actions that can be replayed automatically

Recording a Macro: View → Macros → Record Macro → Perform actions → Stop Recording

Macro-Enabled Workbook: Save as .xlsm to preserve macros

📝 Examples

Recording a Formatting Macro:

1. Click "Record Macro" and name it "FormatHeader"

2. Select row 1, apply bold, background color, and borders

3. Stop recording - now you can apply this formatting with one click!

💡 Pro Tip: Use relative references when recording if you want the macro to work on any selection, not just specific cells.

🎯 Day 21 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. What is a macro in Excel?

A A type of chart

B A recorded sequence of actions

C A formula

D A cell format

2. What keyboard shortcut opens the Macro dialog?

A Ctrl + M

B Alt + F8

C F5

D Ctrl + F8

3. What file extension preserves macros?

A .xlsx

B .xls

C .xlsm

D .csv

4. What shortcut opens the VBA Editor?

A Alt + F11

B Ctrl + V

C F12

D Alt + V

5. Why use relative references when recording?

A To make macros faster

B To work on any selection

C To reduce file size

D To improve security

Day 22

VBA Editor Basics

Locked 🔒

📖 What You'll Learn

Navigating the VBA Editor interface
Understanding modules and procedures
The Project Explorer and Properties Window
Writing your first Sub procedure
Running code from the editor

⌨️ VBA Editor Shortcuts

F5 Run procedure

F8 Step through code

Ctrl + G Immediate Window

Ctrl + R Project Explorer

💡 Code Structure

Sub Procedure:

Sub MacroName()
  ' Your code here
End Sub

Function:

Function FunctionName() As DataType
  ' Code
  FunctionName = result
End Function

📝 Examples

Simple Hello World:

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

💡 Pro Tip: Use the Immediate Window (Ctrl+G) to test small pieces of code without running a full macro.

🎯 Day 22 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. What does VBA stand for?

A Visual Basic Application

B Visual Basic for Applications

C Very Basic Automation

D Virtual Business Application

2. What key runs a VBA procedure?

A F5

B F1

C Enter

D F12

3. How does a Sub procedure end?

A Stop Sub

B End Sub

C Close Sub

D Exit Sub

4. What opens the Immediate Window?

A Ctrl + I

B Ctrl + W

C Ctrl + G

D Ctrl + M

5. MsgBox is used to:

A Display a message to the user

B Create a new worksheet

C Save the workbook

D Delete cells

Day 23

Variables and Data Types

Locked 🔒

📖 What You'll Learn

Declaring variables with Dim
Common data types (String, Integer, Long, Double, Boolean)
Variable naming conventions
Using Option Explicit
Constants and their uses

💡 Data Types

String: Text values - Dim name As String

Integer: Whole numbers (-32,768 to 32,767) - Dim count As Integer

Long: Large whole numbers - Dim bigNum As Long

Double: Decimal numbers - Dim price As Double

Boolean: True/False - Dim isValid As Boolean

📝 Examples

Variable Declaration:

Sub UseVariables()
  Dim customerName As String
  Dim quantity As Integer
  Dim unitPrice As Double
  
  customerName = "John Doe"
  quantity = 5
  unitPrice = 19.99
  
  MsgBox customerName & " ordered " & quantity & " items"
End Sub

💡 Pro Tip: Always add "Option Explicit" at the top of your modules to force variable declaration and catch typos.

🎯 Day 23 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. What keyword declares a variable?

A Var

B Dim

C Let

D Set

2. Which data type stores text?

A Integer

B Double

C String

D Boolean

3. What does Option Explicit do?

A Forces variable declaration

B Runs code faster

C Enables macros

D Creates backups

4. Boolean stores what values?

A Numbers only

B True or False

C Text only

D Dates only

5. Double is used for:

A Text values

B Whole numbers only

C Decimal numbers

D Dates

Day 24

Working with Ranges in VBA

Locked 🔒

📖 What You'll Learn

The Range object and its properties
Selecting and referencing cells
Reading and writing cell values
Using Cells property
Working with multiple ranges

💡 Range Syntax

Single Cell: Range("A1") or Cells(1, 1)

Range of Cells: Range("A1:B10")

Set Value: Range("A1").Value = "Hello"

Read Value: myVar = Range("A1").Value

📝 Examples

Working with Ranges:

Sub RangeExamples()
  Range("A1").Value = "Name"
  Range("B1").Value = "Score"
  Range("A2:A5").Value = "Student"
  Cells(2, 2).Value = 95
  
  ' Copy range
  Range("A1:B5").Copy Range("D1")
End Sub

💡 Pro Tip: Use Cells(row, column) when you need to reference cells dynamically in loops.

🎯 Day 24 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. How do you reference cell A1?

A Cell("A1")

B Range("A1")

C Select("A1")

D Get("A1")

2. What does Cells(3, 2) reference?

A Cell C2

B Cell B3

C Cell A1

D Cell D5

3. How do you set a cell's value?

A Range("A1").Text = "Hello"

B Range("A1").Set = "Hello"

C Range("A1").Value = "Hello"

D Range("A1").Content = "Hello"

4. Range("A1:B5") selects how many cells?

A 5 cells

B 10 cells

C 2 cells

D 7 cells

5. When is Cells() better than Range()?

A For formatting

B In loops with dynamic references

C For copying

D For deleting

Day 25

Loops in VBA

Locked 🔒

📖 What You'll Learn

For...Next loops
For Each loops for collections
Do While and Do Until loops
Exiting loops early
Nested loops

💡 Loop Structures

For...Next:

For i = 1 To 10
  ' Code
Next i

For Each:

For Each cell In Range("A1:A10")
  ' Code
Next cell

Do While:

Do While condition
  ' Code
Loop

📝 Examples

Fill Cells with Loop:

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

💡 Pro Tip: Use "Exit For" or "Exit Do" to break out of a loop early when a condition is met.

🎯 Day 25 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. How many times does "For i = 1 To 5" loop?

A 4 times

B 5 times

C 6 times

D 1 time

2. For Each is best used with:

A Numbers only

B Collections like ranges

C Text only

D Single cells

3. How do you end a For loop?

A End For

B Next

C Loop

D Stop

4. What exits a loop early?

A Break

B Stop

C Exit For

D Return

5. Do While loops run:

A A fixed number of times

B While condition is True

C Only once

D Forever

Day 26

Conditional Statements in VBA

Locked 🔒

📖 What You'll Learn

If...Then...Else statements
ElseIf for multiple conditions
Select Case statements
Comparison operators
Logical operators (And, Or, Not)

💡 Conditional Syntax

If...Then...Else:

If score >= 90 Then
  grade = "A"
ElseIf score >= 80 Then
  grade = "B"
Else
  grade = "C"
End If

Select Case:

Select Case value
  Case 1
    ' Action
  Case 2, 3
    ' Action
  Case Else
    ' Default
End Select

📝 Examples

Grade Calculator:

Sub AssignGrade()
  Dim score As Integer
  score = Range("A1").Value
  
  If score >= 90 Then
    Range("B1").Value = "A"
  ElseIf score >= 80 Then
    Range("B1").Value = "B"
  Else
    Range("B1").Value = "C"
  End If
End Sub

💡 Pro Tip: Use Select Case when checking a single variable against multiple values - it's cleaner than multiple ElseIf statements.

🎯 Day 26 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. How do you end an If statement?

A End

B End If

C Stop If

D Close If

2. Select Case is best for:

A Loops

B Multiple value checks

C Error handling

D File operations

3. What operator checks "not equal"?

A !=

B Not=

C <>

D =/=

4. ElseIf is used for:

A Ending the If

B Additional conditions

C Default action

D Loops

5. "And" operator requires:

A At least one condition True

B All conditions True

C All conditions False

D No conditions

Day 27

Error Handling in VBA

Locked 🔒

📖 What You'll Learn

On Error statements
Error handling strategies
The Err object
Resume statements
Creating user-friendly error messages

💡 Error Handling Syntax

On Error GoTo: On Error GoTo ErrorHandler

On Error Resume Next: Continues execution after error

Err Object: Err.Number, Err.Description

📝 Examples

Error Handler:

Sub SafeDivision()
  On Error GoTo ErrorHandler
  Dim result As Double
  result = 10 / 0 ' Causes error
  Exit Sub

ErrorHandler:
  MsgBox "Error: " & Err.Description
End Sub

💡 Pro Tip: Always include error handling in production macros to prevent crashes and provide useful feedback to users.

🎯 Day 27 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. What redirects code when an error occurs?

A If Error

B On Error GoTo

C Try Catch

D Error Handle

2. Err.Description contains:

A Error number

B Error message text

C Line number

D File name

3. On Error Resume Next does what?

A Stops the macro

B Restarts the macro

C Continues after error

D Shows error dialog

4. Resume Next continues at:

A The error line

B The line after the error

C The beginning

D The end

5. Exit Sub is used to:

A Close Excel

B Exit the procedure before error handler

C Delete code

D Save the workbook

Day 28

UserForms Basics

Locked 🔒

📖 What You'll Learn

Creating UserForms
Adding controls (TextBox, Label, Button)
Writing control event code
Showing and hiding forms
Collecting user input

💡 Common Controls

TextBox: For user text input

Label: Display text (not editable)

CommandButton: Clickable button for actions

ComboBox: Dropdown selection list

📝 Examples

Button Click Event:

Private Sub cmdSubmit_Click()
  Dim userName As String
  userName = txtName.Value
  Range("A1").Value = userName
  Me.Hide
End Sub

💡 Pro Tip: Use Me.Hide instead of Unload Me if you want to preserve form data for later use.

🎯 Day 28 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. How do you display a UserForm?

A UserForm1.Display

B UserForm1.Show

C UserForm1.Open

D UserForm1.Run

2. TextBox is used for:

A Displaying images

B User text input

C Running macros

D Creating charts

3. What does Me.Hide do?

A Deletes the form

B Hides but preserves data

C Minimizes the form

D Saves the form

4. ComboBox provides:

A Dropdown selection

B Text display only

C Image display

D Button functionality

5. _Click() event runs when:

A Form loads

B User clicks the control

C Form closes

D Data changes

Day 29

Automating Workbook Tasks

Locked 🔒

📖 What You'll Learn

Working with Workbook and Worksheet objects
Opening and closing workbooks
Adding and deleting worksheets
Copying data between workbooks
Saving workbooks programmatically

💡 Key Objects

ThisWorkbook: The workbook containing the macro

ActiveWorkbook: Currently active workbook

Worksheets("Sheet1"): Reference specific sheet

ActiveSheet: Currently active worksheet

📝 Examples

Add and Rename Sheet:

Sub AddNewSheet()
  Dim ws As Worksheet
  Set ws = Worksheets.Add
  ws.Name = "Report_" & Format(Date, "yyyymmdd")
End Sub

💡 Pro Tip: Always use ThisWorkbook when referencing the workbook containing your macro to avoid confusion with other open workbooks.

🎯 Day 29 Quiz

Answer at least 3 questions correctly to unlock the next day.

1. ThisWorkbook refers to:

A Any open workbook

B The workbook containing the macro

C The newest workbook

D A template workbook

2. How do you add a new worksheet?

A Worksheets.New

B Worksheets.Create

C Worksheets.Add

D Worksheets.Insert

3. To save a workbook:

A Workbook.Store

B Workbook.Save or SaveAs

C Workbook.Write

D Workbook.Export

4. ActiveSheet is:

A Always Sheet1

B The currently visible sheet

C The last sheet

D A hidden sheet

5. To close a workbook without saving:

A Workbook.Close

B Workbook.Close SaveChanges:=False

C Workbook.Exit

D Workbook.Quit

Day 30

Building Complete Automation Projects

Locked 🔒

📖 What You'll Learn

Planning automation projects
Combining VBA concepts
Creating report generators
Data import/export automation
Best practices for maintainable code

💡 Project Planning Steps

1. Define Requirements: What should the automation do?

2. Break into Tasks: Split into smaller sub-procedures

3. Build Incrementally: Test each piece before combining

4. Add Error Handling: Make it robust

📝 Example Project

Monthly Report Generator:

Sub GenerateMonthlyReport()
  Application.ScreenUpdating = False
  
  ' Clear old data
  Call ClearReportSheet
  ' Import new data
  Call ImportData
  ' Calculate summaries
  Call CalculateTotals
  ' Format report
  Call ApplyFormatting
  ' Save report
  Call SaveReport
  
  Application.ScreenUpdating = True
  MsgBox "Report generated successfully!"
End Sub

💡 Pro Tip: Use Application.ScreenUpdating = False at the start to speed up macros, and set it back to True at the end.

🎯 Day 30 Quiz

Answer at least 3 questions correctly to complete the Macros & VBA section!

1. Application.ScreenUpdating = False does what?

A Closes Excel

B Speeds up macros by hiding updates

C Deletes data

D Saves the file

2. Why break code into sub-procedures?

A To make files larger

B For easier testing and maintenance

C To slow down execution

D To confuse users

3. Call keyword is used to:

A Run another procedure

B Create a variable

C Send an email

D Delete data

4. Best practice: Always add what to production code?

A More loops

B Error handling

C More variables

D More comments only

5. After disabling ScreenUpdating, you should:

A Leave it disabled

B Set it back to True

C Restart Excel

D Delete the setting

Day 31

Introduction to Power Query

Locked 🔒

📖 What You'll Learn

What is Power Query (ETL tool)
Accessing via Data → Get Data
M Language basics
Close & Load to output results

🎯 Day 31 Quiz

Answer at least 3 correctly.

1. ETL stands for?

AExcel Transform Logic

BExtract, Transform, Load

CEdit Tables Locally

DExternal Table Link

2. Access Power Query via?

AHome → Query

BData → Get Data

CInsert → Query

DView → Tools

3. Power Query uses what language?

AVBA

BSQL

CM Language

DPython

4. Close & Load does what?

ADeletes query

BOutputs data to Excel

CCloses Excel

DSaves file

5. Power Query can combine files from?

AOnly Excel

BA folder

COnly CSV

DOne file only

Day 32

Power Query Transformations

Locked 🔒

📖 Topics

Remove/reorder columns
Filter rows & change data types
Split/merge columns
Applied Steps pane

🎯 Quiz

1. Remove column?

ADelete key

BRight-click → Remove

CEdit → Clear

DFormat → Hide

2. Steps recorded in?

AFormula bar

BApplied Steps

CProperties

DSettings

3. Split by delimiter?

AText to Columns

BSplit Column by Delimiter

CTEXTSPLIT

DCopy paste

4. Why change types?

ASmaller files

BCorrect sorting/calc

CChange colors

DEncrypt

5. Undo a step?

ANo

BDelete from Applied Steps

COnly last

DCtrl+Z only

Day 33

Merging & Appending Queries

Locked 🔒

📖 Topics

Merge (like VLOOKUP)
Join types: Left, Inner, Full
Append (stack rows)

🎯 Quiz

1. Merge is like?

ASUM

BVLOOKUP/JOIN

CCONCAT

DIF

2. Left Outer returns?

AMatches only

BAll left + matches

CAll right

DNothing

3. Append does?

AAdds columns

BStacks rows

CDeletes dupes

DCreates formulas

4. Inner join returns?

AAll rows

BOnly matches

CUnmatched

DRandom

5. After merge?

ADelete tables

BExpand columns

CRestart

DAutomatic

Day 34

External Data Connections

Locked 🔒

📖 Topics

Connect to databases & web
Import CSV/text files
Refresh with Ctrl+Alt+F5

🎯 Quiz

1. Refresh all?

AF5

BCtrl+Alt+F5

CCtrl+R

DAlt+F4

2. From Web imports?

ALocal only

BWebsite tables

CEmails

DCloud only

3. Manage connections?

AFile → Options

BData → Queries

CView → Connections

DHome → Settings

4. CSV means?

AComma-Separated Values

BComplex Structured

CCoded System

DCompressed Storage

5. Auto-refresh?

AManual only

BEvery X min or on open

COnce daily

DNever

Day 35

Advanced Text Functions

Locked 🔒

📖 Topics

TEXTJOIN, CONCAT
TEXTSPLIT, SUBSTITUTE

🎯 Quiz

1. TEXTJOIN 2nd arg?

ADelimiter

BIgnore empty

CText

DCount

2. TEXTSPLIT does?

AJoins

BParse into cells

CDelete

DFormat

3. SUBSTITUTE?

ABy position

BText with new text

CNumbers

DCells

4. CONCAT vs TEXTJOIN?

AFaster

BNo delimiter

CNumbers only

DCase-sensitive

5. =TEXTJOIN("-",TRUE,"A","","B")?

AA--B

BA-B

CAB

DError

Day 36

Advanced Lookup: XLOOKUP

Locked 🔒

📖 Topics

XLOOKUP & XMATCH
Can look left, built-in errors
Wildcard mode = 2

🎯 Quiz

1. XLOOKUP advantage?

AFaster

BLook left + error handling

CLess memory

DNumbers only

2. XLOOKUP 4th arg?

ALookup value

BIf not found

CMatch mode

DSearch dir

3. XMATCH returns?

AValue

BPosition

CTRUE/FALSE

DAddress

4. Wildcard mode?

A0

B1

C2

D-1

5. Search -1 means?

AFirst to last

BLast to first

CBinary asc

DNo search

Day 37

Dynamic Arrays

Locked 🔒

📖 Topics

Spill ranges & # reference
FILTER, SORT, UNIQUE, SEQUENCE

🎯 Quiz

1. Spill range?

AError

BAuto-expand results

CNamed range

DPrint area

2. FILTER 2nd arg?

ASort order

BBoolean condition

CColumn

DCount

3. =SEQUENCE(5)?

A5 columns

B1-5 column

CNumber 5

DError

4. UNIQUE removes?

AEmpty

BDuplicates

CErrors

DFormatting

5. # symbol refs?

ASingle cell

BEntire spill

CNamed range

DComment

Day 38

LET and LAMBDA

Locked 🔒

📖 Topics

LET for variables in formulas
LAMBDA for custom functions
Name LAMBDAs for reuse

🎯 Quiz

1. LET is for?

ACharts

BVariables in formula

CFormatting

DDelete

2. LAMBDA creates?

ACustom function

BMacro

CWorksheet

DConnection

3. Reuse LAMBDA?

ACopy paste

BSave as named range

CVBA

DExport

4. LET improves perf by?

ALess memory

BCalc once

CCompress

DRemove format

5. LAMBDA last arg?

AParameter

BCalculation

CError handler

DDefault

Day 39

BYROW, BYCOL, MAP

Locked 🔒

📖 Topics

BYROW/BYCOL for row/col ops
MAP for element-wise
REDUCE for aggregating

🎯 Quiz

1. BYROW processes?

AOne cell

BOne row

COne column

DEntire array

2. MAP applies to?

ARows

BEach element

CColumns

DWhole range

3. REDUCE is for?

ADelete rows

BShrink file

CAggregate to single

DFilter

4. BYCOL LAMBDA accepts?

ASingle cell

BColumn array

CTwo params

DNone

5. =BYROW(A1:C5,LAMBDA(r,MAX(r)))?

A5 vals (max/row)

B3 vals (max/col)

C1 val

D15 vals

Day 40

Advanced Formula Techniques

Locked 🔒

📖 Topics

Combining array functions
TAKE, IFNA, Evaluate Formula
Performance tips

🎯 Quiz

1. TAKE is for?

ADelete

BGet first/last N

CCopy

DScreenshot

2. Evaluate Formula?

AWrite new

BDebug step-through

CDelete

DFormat

3. IFNA handles?

AAll errors

BOnly #N/A

C#VALUE!

D#REF!

4. Avoid for perf?

ANamed ranges

BVolatile funcs & col refs

CTables

DSimple formulas

5. =SORT(UNIQUE(A:A))?

ASorts then unique

BUnique then sorts

CDeletes dupes

DError

Day 41

Financial Functions Basics

Locked 🔒

📖 Topics

PMT - Calculate loan payments
PV - Present value of investments
FV - Future value calculations
RATE - Find interest rate
NPER - Number of periods

💡 Key Formulas

PMT: =PMT(rate, nper, pv, [fv], [type])

FV: =FV(rate, nper, pmt, [pv], [type])

🎯 Quiz

1. PMT calculates?

AInterest rate

BPeriodic payment

CTotal cost

DTax amount

2. FV stands for?

AFinal Value

BFuture Value

CFixed Variable

DFull Version

3. PV calculates?

APast value

BPresent value

CProjected value

DPrincipal value

4. NPER returns?

AInterest rate

BNumber of periods

CPayment amount

DNet present

5. For monthly payments, annual rate?

AUse as is

BDivide by 12

CMultiply by 12

DSquare it

Day 42

Advanced Financial Functions

Locked 🔒

📖 Topics

NPV - Net Present Value
IRR - Internal Rate of Return
XNPV/XIRR - Irregular cash flows
PPMT/IPMT - Principal/Interest portions

🎯 Quiz

1. NPV evaluates?

ALoan payments

BInvestment profitability

CTax deductions

DDepreciation

2. IRR finds the rate where NPV?

AIs maximum

BEquals zero

CIs negative

DDoubles

3. XIRR vs IRR?

ASame function

BHandles irregular dates

COnly for stocks

DFaster calculation

4. IPMT returns?

ATotal payment

BInterest portion

CPrincipal portion

DTax portion

5. PPMT returns?

AInterest portion

BPrincipal portion

CTotal payment

DPeriod number

Day 43

Date Functions

Locked 🔒

📖 Topics

TODAY, NOW - Current date/time
DATE, YEAR, MONTH, DAY
DATEDIF - Difference between dates
EOMONTH - End of month
WORKDAY, NETWORKDAYS

🎯 Quiz

1. TODAY() returns?

ADate and time

BCurrent date only

CTime only

DYear only

2. EOMONTH returns?

AFirst of month

BEnd of month

CMiddle of month

DQuarter end

3. NETWORKDAYS excludes?

AAll days

BWeekends & holidays

CWeekdays

DNothing

4. =YEAR("2024-06-15")?

A6

B2024

C15

DJune

5. DATEDIF calculates?

ACurrent date

BDifference between dates

CFuture date

DDate format

Day 44

Time Functions

Locked 🔒

📖 Topics

TIME, HOUR, MINUTE, SECOND
Calculating time differences
Converting time formats
Time arithmetic

🎯 Quiz

1. NOW() returns?

ADate only

BDate and time

CTime only

DSeconds only

2. 1 day in Excel =?

A24

B1

C1440

D86400

3. =HOUR("14:30")?

A30

B14

C2

D1430

4. To add 2 hours?

A+2

B+TIME(2,0,0)

C*2

D+120

5. 1 hour = what decimal?

A0.5

B1/24

C0.1

D1/60

Day 45

Dashboard Design Principles

Locked 🔒

📖 Topics

Dashboard planning & layout
Choosing the right chart types
Color schemes & consistency
Data-ink ratio principles
KPI design best practices

🎯 Quiz

1. Good dashboards are?

AComplex and detailed

BSimple and focused

CColorful everywhere

DText-heavy

2. For trends over time, use?

APie chart

BLine chart

CRadar chart

DScatter plot

3. KPI stands for?

AKeep Progress Internal

BKey Performance Indicator

CKnown Process Input

DKey Product Info

4. Data-ink ratio means?

AUse more colors

BMaximize data, minimize decoration

CPrint quality

DScreen resolution

5. For part-to-whole?

ALine chart

BPie/donut chart

CScatter plot

DHistogram

Day 46

Interactive Dashboard Elements

Locked 🔒

📖 Topics

Slicers for filtering
Timeline controls
Form controls (dropdowns, checkboxes)
Linking controls to cells
Dynamic chart titles

🎯 Quiz

1. Slicers are used for?

AFormatting

BVisual filtering

CCalculations

DPrinting

2. Timeline works with?

AText data

BDate fields

CNumbers only

DFormulas

3. Form controls are in?

AHome tab

BDeveloper tab

CData tab

DView tab

4. Cell link stores?

ASelected value/index

BFormatting

CChart data

DMacro code

5. Dynamic chart titles use?

AStatic text

BCell references

CVBA only

DImages

Day 47

Sparklines & In-Cell Charts

Locked 🔒

📖 Topics

Line, Column, Win/Loss sparklines
Sparkline formatting options
Data bars in cells
Icon sets for visual indicators

🎯 Quiz

1. Sparklines fit in?

ASeparate sheet

BA single cell

CMultiple cells

DChart sheet

2. Win/Loss sparkline shows?

AExact values

BPositive/negative only

CPercentages

DDates

3. Data bars are?

ACharts

BConditional formatting

CSparklines

DTables

4. Icon sets show?

AExact numbers

BVisual indicators

CText labels

DLinks

5. Sparklines are found in?

AHome tab

BInsert tab

CData tab

DView tab

Day 48

Named Ranges for Dashboards

Locked 🔒

📖 Topics

Creating and managing named ranges
Dynamic named ranges with OFFSET/INDEX
Using names in formulas
Name Manager tips

🎯 Quiz

1. Named ranges improve?

AFile size

BFormula readability

CPrint quality

DChart colors

2. Name Manager shortcut?

ACtrl+N

BCtrl+F3

CAlt+N

DF3

3. Dynamic ranges auto-?

ADelete

BExpand/contract

CFormat

DPrint

4. Quick name creation?

AType in cell

BUse Name Box

CRight-click

DF2

5. Names can include?

ASpaces

BUnderscores

CNumbers first

DSpecial chars

Day 49

Dashboard Data Sources

Locked 🔒

📖 Topics

Tables vs ranges for dashboards
Structured references
Linking multiple data sources
Data refresh strategies

🎯 Quiz

1. Tables auto-expand when?

ANever

BAdding new rows

CCopying

DPrinting

2. Structured reference example?

AA1:B10

BTable1[Sales]

CSalesRange

D$A$1

3. [@Column] refers to?

AAll rows

BSame row value

CHeader only

DTotal row

4. Create table shortcut?

ACtrl+T

BCtrl+N

CAlt+T

DF4

5. Tables advantage?

ASmaller file

BAuto formulas & formatting

CFaster printing

DNo formulas

Day 50

Building Your First Dashboard

Locked 🔒

📖 Topics

Dashboard layout planning
Combining charts, KPIs, and filters
Formatting for presentation
Testing and optimization
Sharing and protecting

🎯 Project: Create a sales dashboard with KPIs, trend chart, category breakdown, and slicer filters!

🎯 Quiz

1. Dashboard should fit?

AMultiple pages

BOne screen

CPrintout only

DEmail body

2. Hide gridlines via?

AHome tab

BView tab

CData tab

DInsert tab

3. Protect sheet allows?

AEdit everything

BControl what users can change

CDelete all

DPrint only

4. Best KPI placement?

ABottom right

BTop of dashboard

CHidden sheet

DFooter

5. Before sharing, always?

ADelete data

BTest and review

CAdd more charts

DChange fonts

Day 51

Excel Collaboration Basics

Locked 🔒

📖 Topics

Sharing workbooks in OneDrive/SharePoint
Co-authoring in real-time
Comments and @mentions
Track Changes feature
Version history

🎯 Quiz

1. Real-time co-authoring requires?

ALocal save

BCloud storage (OneDrive/SharePoint)

CEmail attachment

DUSB drive

2. @mentions notify via?

ASMS

BEmail notification

CPhone call

DNo notification

3. Version history allows?

ADelete all versions

BRestore previous saves

CChange file type

DPrint history

4. Track Changes shows?

AWho changed what and when

BOnly deletions

CFile size

DPrint preview

5. Insert comment shortcut?

ACtrl+C

BShift+F2 or Ctrl+Shift+M

CAlt+C

DF1

Day 52

Workbook Protection & Security

Locked 🔒

📖 Topics

Protect Sheet vs Protect Workbook
Password-protecting files
Allowing specific edits with protection
Hiding formulas while protecting
Digital signatures

🎯 Quiz

1. Protect Sheet prevents?

AOpening file

BEditing locked cells

CViewing data

DPrinting

2. To hide formulas?

ADelete them

BCheck Hidden + Protect Sheet

CUse VBA

DChange font color

3. Protect Workbook prevents?

ACell editing

BAdding/deleting/moving sheets

COpening file

DCopying data

4. Allow users to edit ranges?

ANot possible

BReview → Allow Edit Ranges

CFormat cells

DPrint settings

5. Excel password encryption is?

AUnbreakable

BGood for basic protection

CMilitary-grade

DNot available

Day 53

Creating Excel Templates

Locked 🔒

📖 Topics

Saving as Excel Template (.xltx)
Template design best practices
Using built-in templates
Creating template libraries
Default workbook templates

🎯 Quiz

1. Template file extension?

A.xlsx

B.xltx

C.xlsm

D.csv

2. Opening a template creates?

AOpens original

BNew workbook based on it

CRead-only file

DBackup copy

3. Default template location?

ADesktop

BXLSTART folder

CDocuments

DProgram Files

4. Template with macros uses?

A.xltx

B.xltm

C.xlsm

D.xlsx

5. Good template includes?

ASample data only

BFormat, formulas, placeholders

CNo formatting

DOnly charts

Day 54

Advanced Data Validation

Locked 🔒

📖 Topics

Custom validation formulas
Dependent dropdown lists
Circle invalid data
Input messages and error alerts
Validation with INDIRECT

🎯 Quiz

1. Custom validation uses?

AText only

BFormula returning TRUE/FALSE

CNumbers only

DVBA required

2. Dependent dropdowns need?

AMacros

BINDIRECT with named ranges

CExternal database

DAdd-in

3. Circle Invalid Data is in?

AHome tab

BData tab → Data Validation

CView tab

DInsert tab

4. Input message shows?

AAfter error

BWhen cell is selected

COn print

DNever

5. Error alert styles include?

AOnly Stop

BStop, Warning, Information

CRed only

DNo customization

Day 55

Excel Productivity Tips

Locked 🔒

📖 Topics

Quick Analysis tool (Ctrl+Q)
Flash Fill for patterns
Ideas/Analyze Data feature
Custom Views
Freeze panes and split

🎯 Quiz

1. Quick Analysis shortcut?

ACtrl+A

BCtrl+Q

CCtrl+E

DCtrl+W

2. Flash Fill shortcut?

ACtrl+F

BCtrl+E

CCtrl+D

DCtrl+G

3. Freeze Panes keeps?

AData frozen

BRows/columns visible while scrolling

CFile read-only

DFormulas locked

4. Ideas feature provides?

AVBA code

BAI-powered insights

CTemplates

DPrint options

5. Custom Views save?

AData only

BDisplay settings, filters, hidden rows

CFormulas

DCharts only

Day 56

Error Handling Mastery

Locked 🔒

📖 Topics

IFERROR, IFNA functions
ERROR.TYPE function
Common Excel errors explained
Debugging formulas
ISERROR, ISNA, ISBLANK

🎯 Quiz

1. IFERROR catches?

AOnly #N/A

BAll error types

COnly #VALUE!

DOnly #REF!

2. #DIV/0! means?

AInvalid reference

BDivision by zero

CWrong type

DNot found

3. #REF! indicates?

AValue not found

BInvalid cell reference

CWrong number

DNull value

4. ISBLANK returns TRUE for?

AZero

BEmpty cells

CSpaces

DFormulas

5. Debug formula with?

ADelete and retry

BEvaluate Formula tool

CPrint preview

DFormat cells

Day 57

Advanced Printing & Page Layout

Locked 🔒

📖 Topics

Page Break Preview
Print Titles (repeat rows/columns)
Headers and footers
Print Area settings
Scaling options

🎯 Quiz

1. Repeat header rows on each page?

ACopy paste

BPage Layout → Print Titles

CFreeze panes

DNot possible

2. Page Break Preview shows?

AFormulas

BWhere pages will break

CCell formats

DComments

3. &[Page] in header shows?

AFile name

BCurrent page number

CDate

DSheet name

4. Fit sheet on one page?

AReduce font

BScaling → Fit to 1 page

CDelete columns

DChange paper size

5. Print Area sets?

APaper color

BWhich cells to print

CPrinter name

DNumber of copies

Day 58

Excel Integration with Other Apps

Locked 🔒

📖 Topics

Linking Excel to PowerPoint
Mail merge with Word
Exporting to PDF
Importing from Access
Power Automate basics

🎯 Quiz

1. Paste Link to PowerPoint?

ACtrl+V

BPaste Special → Link

CCopy as image

DExport

2. Mail merge data from Excel needs?

AMacros

BHeaders in first row

CVBA code

DPassword

3. Export to PDF via?

ACopy paste

BFile → Save As/Export

CPrint only

DEmail

4. Power Automate can?

AOnly format cells

BAutomate workflows between apps

CReplace VBA

DOnly print

5. Linked data updates?

ANever

BWhen source changes & refreshed

COnly manually

DOnce daily

Day 59

Excel Best Practices

Locked 🔒

📖 Topics

File organization strategies
Documentation and comments
Backup strategies
Performance optimization
Audit and error checking

🎯 Quiz

1. Good file naming includes?

ARandom numbers

BDate, version, description

CSpaces only

DSingle letter

2. Document formulas with?

ADelete them

BComments and named ranges

CHide sheets

DPrint preview

3. Avoid volatile functions for?

ABetter formatting

BBetter performance

CSmaller files

DFewer sheets

4. Error Checking is in?

AHome tab

BFormulas tab

CData tab

DView tab

5. Regular backups prevent?

AFormatting issues

BData loss

CPrint errors

DSlow files

Day 60

Course Finale & Your Excel Journey

Locked 🔒

📖 Topics

Review of all 60 days
Building your portfolio
Excel certifications (MOS, etc.)
Continuing education resources
Real-world project ideas

🎉 Congratulations! You've completed the entire 60-Day Excel Mastery Course! You now have skills from basic formulas to VBA, Power Query, and dashboards. Keep practicing and building!

🎯 Final Quiz

1. MOS stands for?

AMicrosoft Open Source

BMicrosoft Office Specialist

CMaster of Spreadsheets

DMonthly Office Subscription

2. Best way to learn more Excel?

AMemorize formulas

BPractice with real projects

COnly watch videos

DRead documentation only

3. A portfolio should include?

AEmpty files

BDiverse projects showing skills

COnly screenshots

DOne simple spreadsheet

4. Stay updated on Excel via?

AOld textbooks

BMicrosoft blogs, forums, communities

CIgnore updates

DNever learn new features

5. What makes an Excel expert?

AKnowing every function

BProblem-solving & continuous learning

CFastest typing

DUsing only one feature

📚 Study Notes & Cheat Sheets

239 Excel Keyboard Shortcuts - Master Cheatsheet

📊 Your Progress Dashboard

🏅 Achievements Unlocked

📈 Quiz Score History

Excel Interface & Basic Navigation

Understanding the Excel Interface

Essential Keyboard Shortcuts

💡 Pro Tips

📝 Day 1 Assessment

Basic Formulas & Arithmetic Operations

Introduction to Formulas

📝 Day 2 Assessment

Text Functions & String Manipulation

Working with Text

📝 Day 3 Assessment

IF Statements - Logical Decision Making

Introduction to IF Statements

💡 Pro Tips

📝 Day 4 Assessment

Nested IF & IFS Functions

Advanced Conditional Logic

💡 Pro Tips

📝 Day 5 Assessment

VLOOKUP Fundamentals

Introduction to VLOOKUP

💡 Pro Tips

📝 Day 6 Assessment

Advanced VLOOKUP & HLOOKUP

Taking VLOOKUP Further

💡 Pro Tips

📝 Day 7 Assessment

Conditional Formatting Basics

Visual Data Analysis

Built-in Rules

💡 Pro Tips

📝 Day 8 Assessment

Advanced Conditional Formatting

Custom Formulas in Conditional Formatting

💡 Pro Tips

📝 Day 9 Assessment

INDEX & MATCH - The VLOOKUP Alternative

Why INDEX & MATCH?

💡 Pro Tips

📝 Day 10 Assessment

PivotTable Fundamentals

Introduction to PivotTables

Creating a PivotTable

💡 Pro Tips

📝 Day 11 Assessment

Advanced PivotTables

Grouping & Calculated Fields

Key Advanced Features

💡 Pro Tips

📝 Day 12 Assessment

Charts Fundamentals

Data Visualization with Charts

Common Chart Types

💡 Pro Tips

📝 Day 13 Assessment

Advanced Charts & Formatting

Professional Chart Design

Advanced Chart Features

💡 Pro Tips

📝 Day 14 Assessment

Data Validation

Controlling Data Entry

Validation Types

💡 Pro Tips

📝 Day 15 Assessment

SUMIF, COUNTIF & AVERAGEIF

Conditional Aggregation Functions

💡 Pro Tips

📝 Day 16 Assessment

Sorting & Filtering Data

Organizing Your Data

Sorting Options

AutoFilter

💡 Pro Tips

📝 Day 17 Assessment