📘 Microsoft Excel Tutorial: Essential Concepts & Features

✅ Formatted Excel Notes

Logical Functions, Shortcuts, Sorting, and Filters

📊 Electronic Spreadsheet

An electronic spreadsheet application accepts data values and makes relationships between them in a tabular format consisting of rows and columns. It allows users to perform calculations and manipulate data in the desired format.

🧮 Excel

Excel is a software program created by Microsoft that uses spreadsheets to organize data using formulas and functions. It is typically used for organizing information and performing financial analysis.

📘 Terminology of Spreadsheet

Workbook: A collection of multiple worksheets. You can store organized information in a single workbook.
Worksheet: A sheet made up of rows & columns used for project planning or financial documentation.
Row: A horizontal block of cells. Rows are numbered from 1 to 1,048,576.
Column: A vertical block of cells. Columns are labeled A to XFD (16,384 columns).
Cell: The intersection of a row and column. Each cell has a unique ID (e.g., A1) and can contain up to 255 characters.
Formula: An equation that performs calculations. It starts with an equal sign =.

⚡ Useful Shortcuts

Fill Series in Column: Alt + E + I + S
Insert New Sheet: Shift + F11
Switch Between Sheets:
➤ Previous Sheet: Ctrl + Pg Up
➤ Next Sheet: Ctrl + Pg Down

🔢 Functions in Excel

Functions are predefined formulas that perform calculations using specific values in a particular order.

➕ SUM()
Adds all the numbers in a range.
Syntax: =SUM(number1, number2, ...)
Example: =SUM(A3, B3, D3) or =SUM(A3:D3)

📉 AVERAGE()
Returns the average of all the numbers in a range.
Syntax: =AVERAGE(number1, number2, ...)
Example: =AVERAGE(A3, B3, D3) or =AVERAGE(A3:D3)

⬆️ MAX()
Returns the largest value in a range.
Syntax: =MAX(number1, number2, ...)
Example: =MAX(A3, B3, D3) or =MAX(A3:D3)

⬇️ MIN()
Returns the smallest value in a range.
Syntax: =MIN(number1, number2, ...)
Example: =MIN(A3, B3, D3) or =MIN(A3:D3)

🤔 IF()
Performs decision-making and conditional calculations or messaging.
Syntax: =IF(condition, true_value, false_value)
Example: =IF(A1>50, "Pass", "Fail")

⬇️📐 Dragging Formula

Downward: Ctrl + D
Rightward: Ctrl + R

🔁 Logical Functions

Logical functions return values based on conditions (TRUE/FALSE). Useful for decisions and comparisons.

AND()
OR()
NOT()
IF()

🧮 Operators

>, <, >=, <=, =

✔️ AND()
Returns TRUE if all conditions are TRUE.
Syntax: =AND(logical1, logical2, ...)
Example: =AND(A1>B1, B1<C1)

🟠 OR()
Returns TRUE if any one of the conditions is TRUE.
Syntax: =OR(logical1, logical2, ...)
Example: =OR(A1>B1, B1<C1)

🚫 NOT()
Inverts the logic. Returns FALSE if TRUE, and TRUE if FALSE.
Syntax: =NOT(condition)
Example: =NOT(A1>B1)

✅ IF()
Returns one value if condition is TRUE, another if FALSE.
Syntax: =IF(logical_test, value_if_true, value_if_false)
Examples:

=IF(AVERAGE>=50, "PASS", "FAIL")
=IF(NUMBER>=0, "POSITIVE", "NEGATIVE")
=IF(AGE>=18, "ELIGIBLE FOR VOTE", "NOT ELIGIBLE")

🧠 Nested IF()
Allows multiple IF conditions within another IF statement.
Syntax: =IF(condition1, result1, IF(condition2, result2, result3))
Example: =IF(AVERAGE>=50,"PASS",IF(AVERAGE>=33,"COMPT","FAIL"))

🔤🔢 Sorting

Used to arrange data in a specific order—such as alphabetical, numerical, or by date.

🔍 Filter

The Filter tool helps isolate specific data based on chosen conditions.

✨ Advanced Filter

A. Create a Database
B. Define Criteria: Field Name + Value
C. Selection must be only one column!

Advantages:

✅ Extract data to another location
✅ Avoids duplication
✅ Apply multiple conditions at once

📊 Statistics Functions

📌 COUNT()
Counts cells with numeric values only.
Syntax: =COUNT(value1, value2, ...)
Example: =COUNT(A1:A9)

📌 COUNTA()
Counts non-empty cells (includes numbers, text, etc.).
Syntax: =COUNTA(value1, value2, ...)
Example: =COUNTA(A1:A9)

📌 COUNTBLANK()
Counts the number of empty cells in a range.
Syntax: =COUNTBLANK(range)
Example: =COUNTBLANK(A1:A9)

📌 COUNTIF()
Counts cells based on a single condition.
Syntax: =COUNTIF(range, criteria)
Examples:
=COUNTIF(A1:A10, "manager")
=COUNTIF(A1:A10, ">30000")

📌 COUNTIFS()
Counts cells based on multiple conditions.
Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)
Example:
=COUNTIFS(D2:D11, "FINANCE", C2:C11, "ASSISTANT")

⚙️ Macros in Excel

Definition: A macro is a recorded set of actions to automate repetitive tasks like filtering, formatting, or inserting values. You can name, save, and reuse it multiple times.

🎬 How to Record a Macro (Example: Mark Sheet Filter)

Type your data in the Excel sheet.
Select the entire table and apply a filter.
Go to View → Macros → Record Macro to begin recording.
Click the filter arrow on the “Result” column.
Select PASS → Click OK.
Stop the macro recording from View → Macros → Stop Recording.

🔘 Creating Buttons for Macros

Go to Insert → Shapes and select any shape.
Place the shape on the sheet and type text (e.g., “Show Pass”).
Right-click on the shape → Assign Macro.
Select the recorded macro from the list to link it to the shape.

✅ Tip: You can create multiple macros for different filter views like “Pass” and “Fail,” and assign each to different buttons for quick analysis.

🔤 Text Functions

1. UPPER() – Converts text to uppercase.
=UPPER("hello") ➡️ HELLO
=UPPER(A3)
2. LOWER() – Converts text to lowercase.
=LOWER("HELLO") ➡️ hello
3. PROPER() – Capitalizes first letter of each word.
=PROPER("hello") ➡️ Hello
4. LEFT() – Returns characters from the beginning of a string.
=LEFT("DELHI", 3) ➡️ DEL
5. RIGHT() – Returns characters from the end of a string.
=RIGHT("DELHI", 3) ➡️ LHI
6. MID() – Returns characters from the middle of the text.
=MID("EXCEL", 2, 3) ➡️ XCE
7. LEN() – Returns number of characters in a string.
=LEN("DELHI") ➡️ 5
8. CONCATENATE() – Joins strings together.
=CONCATENATE("Hello", " ", "World") ➡️ Hello World
9. TEXT() – Formats numbers or values as text.
=TEXT(1234.567, "0.00") ➡️ 1234.57

🔍 Lookup Functions

Definition: Lookup functions search for values within a dataset to fetch matching information. They work row-wise (HLOOKUP) or column-wise (VLOOKUP).

1. VLOOKUP()

Vertical lookup – searches in the first column.

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

2. HLOOKUP()

Horizontal lookup – searches in the first row.

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

3. INDEX()

Returns value from a cell at specified row and column in a range.

=INDEX(A2:E11, 5, 2)

4. MATCH()

Returns the position of a value in a range.

=MATCH("Apple", A2:A11, 0)

🔗 INDEX + MATCH

Powerful combo to replace VLOOKUP with more flexibility.

=INDEX(A2:E11, MATCH(H9, A2:A11, 0), 2)
=INDEX(A2:E11, MATCH(H12, A2:A11, 0), MATCH(G13, A1:E1, 0))

🔗 VLOOKUP + MATCH

Dynamic column referencing with MATCH inside VLOOKUP.

=VLOOKUP(D13, A1:E11, MATCH(C14, A1:E1, 0), FALSE)

📝 Note: Always ensure headings are not included in the data range for INDEX and MATCH to work properly.

✅ Pivot Table

A PivotTable is a powerful tool to calculate, summarize, and analyze data. It helps extract significance from large datasets by summarizing them in a user-friendly format.
Use cases: Summing, averaging, or grouping large data by meaningful fields.

📁 Importing Data (Data Tab ➜ From Text)

Go to Data Tab ➜ Get External Data ➜ From Text
Select the text file ➜ Import
Choose:
- Delimited – fields separated by characters (comma/tab)
- Fixed Width – fields aligned with spaces
Tick “My data has headers” if applicable
Set the delimiter (tab, comma, etc.) ➜ Click Next
Choose the data format (e.g. General, Date) ➜ Finish
Select where to place data ➜ Click OK

🔧 Data Tab Tools

Tool	Description
Advanced Filter	Filter with multiple criteria
Sort	Arrange data by date, text, or number
Text to Column	Split text in one cell into multiple columns
Flash Fill	Auto-fill based on pattern (Shortcut: Ctrl + E)
Get External Data	Import data from external files
Data Validation	Restrict input to defined criteria

✂ Text to Column

Used to split one column into multiple columns based on delimiters.
Steps:

Select the data
Go to Data ➜ Text to Column
Choose Delimited or Fixed Width
Complete the wizard to split data

⚡ Flash Fill

Auto-completes data entry based on detected patterns.
Shortcut: Ctrl + E

✅ Data Validation

Controls the type of data entered into a cell.
Can restrict to:

Only numbers or text
Specific number range
Specific date or time
Fixed text length
Selection from a dropdown list

📊 Consolidate Data

Combine data from multiple sheets into one.
Steps:

Ensure all sheets have compatible data
Go to a new sheet ➜ Data ➜ Consolidate
Add source ranges
Select "Top row", "Left column", and "Link to source data"
Click OK

🧠 What-If Analysis

Allows exploration of different outcomes based on variable inputs in formulas.

🧪 Scenario Manager

Go to Data ➜ What-If Analysis ➜ Scenario Manager
Add a scenario ➜ Define name and changing cells
Enter different values for each scenario
Click Summary ➜ Choose result cells

🎯 Goal Seek

Select the formula cell ➜ Data ➜ What-If Analysis ➜ Goal Seek
Set cell: Cell with formula
To value: Desired output
By changing: Input cell that affects the formula

🔁 Data Table

Try multiple input values to see their effect on formulas. Useful for sensitivity analysis.