Microsoft Excel

Complete Study Notes

Basic to Advanced — Full Course

Topics: Interface • Formatting • Formulas • Functions

Charts • Pivot Tables • Data Tools • Shortcuts

Based on: Excel Tutorial For Beginners in Hindi

Source: YouTube | Language: Hindi/English

Prepared with ❤■ for Excel Learners

Microsoft Excel – Complete Study Notes Basic to Advanced

Table of Contents
1. Introduction to Microsoft Excel
Interface, Workbook, Worksheet Basics

2. Formatting & Cell Operations

Fonts, Borders, Merge, Alignment, Styles

3. Formulas & Basic Functions

Arithmetic, SUM, AVERAGE, MIN, MAX, COUNT

4. Logical & Text Functions

IF, AND, OR, CONCATENATE, LEN, TRIM

5. Lookup Functions
VLOOKUP, HLOOKUP, INDEX, MATCH

6. Date & Time Functions

TODAY, NOW, DATE, DATEDIF, EDATE

7. Charts & Graphs

Bar, Pie, Line, Combo, Sparklines

8. Data Management Tools

Sort, Filter, Data Validation, Remove Duplicates

9. Pivot Tables & Pivot Charts

Create, Group, Slicers, Calculated Fields

10. Essential Keyboard Shortcuts

Navigation, Selection, Formatting, Formulas

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 2
Microsoft Excel – Complete Study Notes Basic to Advanced

Introduction to Microsoft Excel

1 Interface • Workbook • Worksheet Basics

What is Microsoft Excel?

Microsoft Excel is a powerful spreadsheet application developed by Microsoft. It organises data in a grid
of rows and columns, enabling users to perform calculations, analyse data, create charts, and automate
tasks using formulas and macros.

Excel Interface – Key Components

Component Description

Title Bar Displays the workbook name and application title.

Ribbon Tabbed toolbar (Home, Insert, Page Layout, Formulas, Data, Review, View).

Name Box Shows the address of the currently selected cell (e.g., A1).

Formula Bar Displays and edits the content of the active cell.

Column Headers Labelled A, B, C … (up to XFD — 16,384 columns).

Row Headers Numbered 1, 2, 3 … (up to 1,048,576 rows).

Cell Intersection of a row and column (e.g., B3).

Sheet Tabs Navigate between worksheets (Sheet1, Sheet2 …).

Status Bar Displays SUM, COUNT, AVERAGE of selected cells at the bottom.

Workbook vs Worksheet
• Workbook — The entire Excel file (.xlsx). One workbook can contain many worksheets.
• Worksheet (Sheet) — A single spreadsheet tab inside the workbook.
• You can add, rename, delete, move, copy or colour-code sheet tabs.
• Right-click on a sheet tab to access all sheet-level options.

Cell Referencing
• Relative Reference (A1) — Changes when copied to another cell.
• Absolute Reference ($A$1) — Stays fixed when copied. Press F4 to toggle.
• Mixed Reference ($A1 or A$1) — Fixes only the column or only the row.

=A1+B1 → Relative (both change on copy)

=$A$1+$B$1 → Absolute (nothing changes on copy)
=$A1+B$1 → Mixed

■ Tip: Press F4 while editing a formula to cycle through all four reference types.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 3
Microsoft Excel – Complete Study Notes Basic to Advanced

Formatting & Cell Operations

2 Fonts • Borders • Merge • Alignment • Styles

Basic Formatting (Home Tab)

Action Shortcut / Location

Bold Ctrl + B

Italic Ctrl + I

Underline Ctrl + U

Font size / family Home → Font group

Cell background colour Home → Fill Color (paint bucket)

Font colour Home → Font Color (A with colour bar)

Borders Home → Borders dropdown

Number format Home → Number group (General, Number, Currency, %)

Increase/Decrease decimal Home → .00 buttons

Merge & Alignment

• Merge & Center: Home → Merge & Center — combines multiple cells into one.
• Wrap Text: Home → Wrap Text — displays long text on multiple lines within the cell.
• Indent: Home → Increase/Decrease Indent.
• Alignment: Left, Centre, Right (horizontal) and Top, Middle, Bottom (vertical).

Column Width & Row Height

• Double-click the column/row border to AutoFit.
• Right-click column header → Column Width to set exact width.
• Right-click row header → Row Height to set exact height.
• Select multiple columns/rows and resize together.

Conditional Formatting
Conditional Formatting automatically applies formats (colours, icons, data bars) based on cell values.
Access via Home → Conditional Formatting.
• Highlight Cell Rules — Greater than, Less than, Equal to, Between, Duplicate Values…
• Top/Bottom Rules — Top 10 items, Above average…
• Data Bars — Inserts a mini bar chart inside the cell.
• Color Scales — Applies a gradient (e.g., red–yellow–green).
• Icon Sets — Adds traffic-light or arrow icons.
• Use Manage Rules to edit or delete existing rules.

■ Note: Conditional Formatting does not change the actual cell value — it only changes the visual
appearance.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 4
Microsoft Excel – Complete Study Notes Basic to Advanced

Formulas & Basic Functions

3 Arithmetic • SUM • AVERAGE • MIN • MAX • COUNT

Formula Basics
Every formula in Excel starts with an equals sign (=). Excel evaluates the expression to the right and
displays the result.
=A1 + B1 Addition
=A1 - B1 Subtraction
=A1 * B1 Multiplication
=A1 / B1 Division
=A1 ^ 2 Exponentiation (A1 squared)
=MOD(A1, 3) Remainder of A1 ÷ 3

Commonly Used Functions

Function Syntax Description

SUM =SUM(A1:A10) Adds all values in a range.

AVERAGE =AVERAGE(A1:A10) Calculates the arithmetic mean.

MIN =MIN(A1:A10) Returns the smallest value.

MAX =MAX(A1:A10) Returns the largest value.

COUNT =COUNT(A1:A10) Counts cells containing numbers.

COUNTA =COUNTA(A1:A10) Counts non-empty cells.

COUNTBLANK =COUNTBLANK(A1:A10) Counts empty cells.

COUNTIF =COUNTIF(A1:A10,">50") Counts cells meeting a criterion.

SUMIF =SUMIF(A1:A10,">50",B1:B10) Sums cells meeting a criterion.

SUMIFS =SUMIFS(sum_range, range1, c1…)Sums with multiple criteria.

PRODUCT =PRODUCT(A1:A10) Multiplies all values.

ROUND =ROUND(A1, 2) Rounds to 2 decimal places.

ROUNDUP =ROUNDUP(A1, 0) Always rounds up.

ROUNDDOWN =ROUNDDOWN(A1, 0) Always rounds down.

ABS =ABS(A1) Returns the absolute value.

SQRT =SQRT(A1) Returns the square root.

INT =INT(A1) Rounds down to nearest integer.

■ Tip: Use AutoSum (Alt + =) to instantly insert =SUM() for the selected range.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 5
Microsoft Excel – Complete Study Notes Basic to Advanced

Logical & Text Functions

4 IF • AND • OR • CONCATENATE • LEN • TRIM

IF Function
The IF function performs a logical test and returns one value if TRUE and another if FALSE.
=IF(logical_test, value_if_true, value_if_false)
=IF(A1>=50, "Pass", "Fail")
=IF(B1>100, B1*0.9, B1) // Apply 10% discount if >100

• Nested IF — Use multiple IF functions inside each other for more conditions:
=IF(A1>=90,"A", IF(A1>=80,"B", IF(A1>=70,"C","F")))

AND / OR / NOT
=AND(A1>50, B1>50) // TRUE only if BOTH are true
=OR(A1>50, B1>50) // TRUE if AT LEAST ONE is true
=NOT(A1>50) // Reverses the logical result
=IF(AND(A1>50,B1>50),"Pass","Fail") // Combined

IFS Function (Excel 2019+)

IFS replaces nested IFs with a cleaner syntax:
=IFS(A1>=90,"A", A1>=80,"B", A1>=70,"C", TRUE,"F")

Text Functions
Function Example Result / Purpose

CONCATENATE =CONCATENATE(A1," ",B1) Joins text strings.

& operator =A1&" "&B1 Same as CONCATENATE.

CONCAT =CONCAT(A1:C1) Joins a range (2019+).

TEXTJOIN =TEXTJOIN(", ",1,A1:A5) Joins with delimiter, skips blanks.

LEN =LEN(A1) Number of characters.

UPPER =UPPER(A1) Converts to UPPERCASE.

LOWER =LOWER(A1) Converts to lowercase.

PROPER =PROPER(A1) First Letter Capitalised.

TRIM =TRIM(A1) Removes extra spaces.

LEFT =LEFT(A1, 3) First 3 characters.

RIGHT =RIGHT(A1, 3) Last 3 characters.

MID =MID(A1, 2, 4) 4 chars starting at position 2.

FIND =FIND("@",A1) Position of a character.

SUBSTITUTE =SUBSTITUTE(A1,"old","new") Replace text.

TEXT =TEXT(A1,"DD-MM-YYYY") Format number as text.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 6
Microsoft Excel – Complete Study Notes Basic to Advanced

Lookup Functions
5 VLOOKUP • HLOOKUP • INDEX • MATCH

VLOOKUP
VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value from a
specified column in the same row.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(A2, $D$2:$F$100, 2, 0)

Argument Description

lookup_value The value to search for (e.g., Employee ID).

table_array The range containing the data (use absolute ref: $D$2:$F$100).

col_index_num Column number to return (1 = first column, 2 = second…).

range_lookup 0 or FALSE = Exact match (recommended). 1 or TRUE = Approximate.

■ Note: VLOOKUP can only look to the RIGHT. If your lookup column is not the first column, use
INDEX+MATCH instead.

HLOOKUP
HLOOKUP (Horizontal Lookup) works like VLOOKUP but searches across rows instead of columns.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

INDEX & MATCH — The Powerful Combo

INDEX returns the value at a given row and column position in a range.
=INDEX(array, row_num, [col_num])
=INDEX(B2:B100, 5) // Returns the 5th value in B2:B100

MATCH returns the relative position of a value within a range.

=MATCH(lookup_value, lookup_array, [match_type])
=MATCH("Alice", A2:A100, 0) // Position of "Alice" in the list

Combined INDEX+MATCH — More flexible than VLOOKUP:

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

■ Tip: INDEX+MATCH can look LEFT, doesn't break when columns are inserted, and is faster on large
datasets.

XLOOKUP (Excel 365 / 2021)

XLOOKUP is the modern replacement for VLOOKUP/HLOOKUP — simpler and more powerful.
=XLOOKUP(lookup_value, lookup_array, return_array, [not_found], [match_mode])
=XLOOKUP(A2, D2:D100, E2:E100, "Not Found", 0)

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 7
Microsoft Excel – Complete Study Notes Basic to Advanced

Date & Time Functions

6 TODAY • NOW • DATE • DATEDIF • EDATE

Function Syntax / Example Description

TODAY =TODAY() Returns today's date (dynamic).

NOW =NOW() Returns current date and time.

DATE =DATE(2024, 12, 25) Creates a date from year/month/day.

YEAR =YEAR(A1) Extracts the year.

MONTH =MONTH(A1) Extracts the month number.

DAY =DAY(A1) Extracts the day number.

WEEKDAY =WEEKDAY(A1, 2) Day of the week (1=Mon).

WEEKNUM =WEEKNUM(A1) Week number of the year.

DATEDIF =DATEDIF(A1,B1,"Y") Difference in Years/Months/Days.

EDATE =EDATE(A1, 3) Date 3 months from A1.

EOMONTH =EOMONTH(A1, 0) Last day of the same month.

NETWORKDAYS =NETWORKDAYS(A1,B1) Working days between two dates.

WORKDAY =WORKDAY(A1, 10) Date 10 working days later.

TEXT (date) =TEXT(A1,"DD MMM YYYY") Display date as formatted text.

DATEDIF — Calculate Age / Duration

DATEDIF is a hidden function (not in autocomplete but works). It calculates the difference between two
dates in different units.
=DATEDIF(start_date, end_date, unit)
"Y" → Complete years
"M" → Complete months
"D" → Total days
"YM" → Months ignoring years
"MD" → Days ignoring months and years

Age Calculation Example:

=DATEDIF(B2, TODAY(), "Y") & " years, " &
DATEDIF(B2, TODAY(), "YM") & " months"

■ Note: Excel stores dates as serial numbers (1 = 1 Jan 1900). You can add/subtract days directly: =A1+7
gives the date 7 days later.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 8
Microsoft Excel – Complete Study Notes Basic to Advanced

Charts & Graphs

7 Bar • Pie • Line • Combo • Sparklines

Creating a Chart
• Select your data range (include headers).
• Go to Insert tab → Charts group.
• Choose the chart type and sub-type.
• The chart is inserted as an embedded object — drag to position it.
• Use Chart Design and Format contextual tabs to customise.

Chart Type Best Used For

Column / Bar Comparing categories side by side.

Line Showing trends over time.

Pie / Doughnut Showing proportions of a whole.

Area Cumulative totals over time.

Scatter (XY) Showing correlation between two variables.

Bubble Scatter with a third variable shown as bubble size.

Combo Combining two chart types (e.g., bar + line).

Histogram Distribution of data (frequency).

Waterfall Cumulative effect of sequential values (Finance).

Funnel Stages of a process (Sales pipeline).

Map Chart Geographic data (Excel 365).

Chart Customisation
• Chart Title — Click on the title text to edit it.
• Axis Titles — Chart Design → Add Chart Element → Axis Titles.
• Data Labels — Add Chart Element → Data Labels (shows values on bars/points).
• Legend — Describes what each colour/series represents.
• Gridlines — Can be added or removed for clarity.
• Change Chart Type — Right-click the chart → Change Chart Type.
• Switch Row/Column — Chart Design → Switch Row/Column.

Sparklines — Mini Charts in Cells

Sparklines are tiny charts embedded inside a single cell — great for dashboards.
• Select the output cell. Go to Insert → Sparklines.
• Choose Line, Column, or Win/Loss.
• Enter the data range. Customise via the Sparkline Design tab.

■ Tip: Right-click a chart → Save as Template to reuse your chart design later.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 9
Microsoft Excel – Complete Study Notes Basic to Advanced

Data Management Tools

8 Sort • Filter • Data Validation • Remove Duplicates

Sorting Data
• Select any cell in the column → Data → Sort A to Z (ascending) or Z to A (descending).
• For multi-level sort: Data → Sort → Add Level → specify column and order for each level.
• Sort by Cell Color, Font Color, or Cell Icon (from Conditional Formatting).
• Use Custom List to sort by a custom order (e.g., Jan, Feb, Mar…).

AutoFilter
• Select any cell in your data → Data → Filter (or Ctrl+Shift+L).
• Drop-down arrows appear in the header row. Click to filter by value, colour, or condition.
• Text Filters: Contains, Begins With, Ends With, Does Not Contain…
• Number Filters: Greater Than, Between, Top 10…
• Date Filters: This Week, Last Month, Between…
• Click the filter arrow and untick Select All, then pick specific values.

■ Tip: To clear all filters at once: Data → Clear (or Ctrl+Shift+L to toggle filter off/on).

Remove Duplicates
• Select the data range → Data → Remove Duplicates.
• Choose which columns to check for duplicates → Click OK.
• Excel reports how many duplicate rows were removed.
■ Note: Remove Duplicates permanently deletes rows. Make a backup copy first!

Data Validation
Data Validation restricts what can be entered in a cell — great for creating dropdown lists and enforcing
data integrity.
• Select cells → Data → Data Validation.
• Whole Number — Only integers within a range.
• Decimal — Decimal numbers within limits.
• List — Dropdown from a range or comma-separated values.
• Date / Time — Restrict to date/time ranges.
• Text Length — Limit number of characters.
• Custom — Enter any formula that returns TRUE/FALSE.

Use the Input Message tab to show a tooltip when the cell is selected. Use the Error Alert tab to show a
custom error message on invalid entry.

Text to Columns
Splits a column of text into multiple columns — useful for cleaning imported data.
• Select the column → Data → Text to Columns.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 10
Microsoft Excel – Complete Study Notes Basic to Advanced

• Delimited — Split by comma, space, tab, semicolon, or custom character.

• Fixed Width — Split at specific character positions.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 11
Microsoft Excel – Complete Study Notes Basic to Advanced

Pivot Tables & Pivot Charts

9 Create • Group • Slicers • Calculated Fields

What is a Pivot Table?

A Pivot Table is an interactive summary tool that lets you quickly group, count, sum, and analyse large
datasets without writing any formulas. It 'pivots' (rotates) data to view it from different angles.

Creating a Pivot Table

• Click anywhere inside your data.
• Insert → PivotTable. Choose New Worksheet or Existing Worksheet.
• The PivotTable Field List appears on the right.
• Drag fields to the four areas: Filters, Columns, Rows, Values.
• Values area defaults to SUM for numbers and COUNT for text — right-click → Value Field Settings to
change.

Area Purpose

Filters Adds a page-level filter above the table (e.g., filter by Year).

Rows Row labels on the left side (e.g., Product Name).

Columns Column labels across the top (e.g., Month).

Values Numbers to summarise: SUM, COUNT, AVERAGE, MAX, MIN, etc.

Grouping in Pivot Tables

• Right-click a date field in the Pivot Table → Group.
• Group by Days, Months, Quarters, Years — or custom intervals for numbers.
• This is essential for monthly/quarterly sales summaries.

Slicers — Visual Filters

• Select the Pivot Table → PivotTable Analyze → Insert Slicer.
• Choose the field(s) to create slicers for.
• Click slicer buttons to filter the Pivot Table interactively.
• Slicers can be connected to multiple Pivot Tables (right-click → Report Connections).

Calculated Fields
• PivotTable Analyze → Fields, Items & Sets → Calculated Field.
• Enter a name and a formula using existing fields (e.g., =Revenue - Cost).
• The new field appears in the Values area like any other field.

■ Tip: Refresh a Pivot Table after data changes: Right-click → Refresh, or PivotTable Analyze → Refresh.

Pivot Charts
A Pivot Chart is directly linked to a Pivot Table — filtering one filters the other.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 12
Microsoft Excel – Complete Study Notes Basic to Advanced

• Select the Pivot Table → PivotTable Analyze → PivotChart.

• Choose chart type. The chart updates automatically when the Pivot Table changes.
• Slicers applied to the Pivot Table also affect the Pivot Chart.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 13
Microsoft Excel – Complete Study Notes Basic to Advanced

Essential Keyboard Shortcuts

10 Navigation • Selection • Formatting • Formulas

Navigation & Selection

Shortcut Action

Ctrl + Home Go to cell A1.

Ctrl + End Go to the last used cell.

Ctrl + Arrow Key Jump to the last/first filled cell in a direction.

Ctrl + Shift + End Select from current cell to last used cell.

Ctrl + Shift + * Select the current data region.

Ctrl + A Select all cells (press twice for whole sheet).

Ctrl + Space Select entire column.

Shift + Space Select entire row.

F5 / Ctrl + G Go To dialog — jump to a named range or cell address.

Editing & General

Shortcut Action

Ctrl + C / X / V Copy / Cut / Paste.

Ctrl + Z / Y Undo / Redo.

Ctrl + D Fill Down (copy cell above into selected range).

Ctrl + R Fill Right.

Delete Clear cell content.

F2 Enter Edit mode for active cell.

Escape Cancel editing without saving changes.

Alt + Enter New line within a cell (line break).

Ctrl + ; Insert today's date.

Ctrl + Shift + ; Insert current time.

Ctrl + ' Copy formula from cell above.

Ctrl + ` Toggle show formulas / values.

Formatting Shortcuts
Shortcut Action

Ctrl + B Bold.

Ctrl + I Italic.

Ctrl + U Underline.

Ctrl + 1 Open Format Cells dialog.

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 14
Microsoft Excel – Complete Study Notes Basic to Advanced

Ctrl + Shift + $ Apply Currency format.

Ctrl + Shift + % Apply Percentage format.

Ctrl + Shift + # Apply Date format (DD-MMM-YY).

Alt + H + H Fill colour (highlight cells).

Alt + H + B Borders menu.

Formula Shortcuts
Shortcut Action

Alt + = AutoSum the selection.

F4 Toggle absolute/relative reference ($A$1).

Ctrl + Shift + Enter Enter as an Array Formula (legacy CSE).

F9 Calculate/evaluate selected formula part.

Ctrl + [ Go to cells that the formula refers to (precedents).

Ctrl + ] Go to cells that use this cell (dependents).

Shift + F3 Insert Function dialog.

Ctrl + F3 Name Manager (create/edit named ranges).

Ctrl + Shift + F3 Create names from selection.

© Excel Study Notes — Based on YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial (Basic to
Advanced)

YouTube: Excel Tutorial For Beginners in Hindi | Complete Microsoft Excel Tutorial Page 15