📊 MS EXCEL

Complete Learning Notes

Theory + Practical + All Formulas | Tab-by-Tab Coverage

This document covers every Excel Tab: Home | Insert | Page Layout | Formulas | Data | Review |
View | Developer — with all formulas, practical tips, and bilingual (English + Hinglish) explanations!
📌 INTRODUCTION TO MS EXCEL
MS Excel is a spreadsheet application developed by Microsoft. It is used for organizing, calculating, and analyzing
data using rows and columns.
💡 Hinglish: Excel ek tarah ka digital register hai jisme hum numbers, text aur formulas use karke data manage
karte hain — jaise ek accountant apni diary mein likhta hai!

Key Terms / Badi Zaruri Baatein

Term Meaning
Workbook The entire Excel file (.xlsx) — ek poori notebook
Worksheet / Sheet Each tab inside a workbook — ek page notebook ka
Cell Intersection of row & column (e.g., A1, B3)
Row Horizontal line — 1, 2, 3... (Total: 1,048,576 rows)
Column Vertical line — A, B, C... (Total: 16,384 columns)
Cell Address / Reference Like A1, B5 — cell ka address
Formula Calculation starting with = sign
Function Built-in formula like =SUM(), =AVERAGE()
Range Group of cells like A1:A10
Ribbon The toolbar at top with all tabs and buttons
Name Box Shows current cell address (top left)
Formula Bar Shows content of active cell (below ribbon)
🏠 TAB 1: HOME TAB
The HOME tab is the most used tab in Excel. It contains basic formatting, editing, and clipboard
tools.
💡 Hinglish: Home Tab = Excel ka sabse important tab. Yahan formatting, copy-paste, aur basic editing ke saare
tools milte hain!

1.1 Clipboard Group

The Clipboard group contains tools to Copy, Cut, and Paste data.
Button Use
Copy (Ctrl+C) Copies selected cells to clipboard
Cut (Ctrl+X) Moves selected cells (delete from original location)
Paste (Ctrl+V) Pastes copied/cut content
Paste Special Paste only values, formats, formulas, etc.
(Ctrl+Alt+V)
Format Painter Copy formatting from one cell and apply to another
💡 Hinglish: Format Painter = ek brush hai jo ek cell ka look doosre cell pe copy kar deta hai — jaisa painting
brush!

1.2 Font Group

Controls the appearance of text in cells.
Option Shortcut / Use
Font Name Change font style (Arial, Calibri, Times New Roman)
Font Size Change text size (8, 10, 12, 14...)
Bold Ctrl+B — Make text bold
Italic Ctrl+I — Make text italic
Underline Ctrl+U — Underline text
Font Color Change text color
Fill Color / Cell Color Highlight a cell with background color
Borders Add lines/borders to cells
Increase / Decrease Font Ctrl+Shift+> or < — resize font quickly

1.3 Alignment Group

Controls how text is positioned inside cells.
Option Use
 Left / Center / Right Align Horizontal text alignment
Top / Middle / Bottom Vertical alignment within cell
Align
Wrap Text Show long text in multiple lines within cell
Merge & Center Combine multiple cells into one (used for headings)
Indent Shift text inside cell
(Increase/Decrease)
Orientation Rotate text at an angle (useful for narrow columns)
💡 Hinglish: Merge & Center = kai cells ko jodke ek heading banana — jaise exam paper ka title center mein
likhna!

1.4 Number Group

Formats how numbers appear in cells.
Format Example / Use
General Default — shows as entered
Number 1234.56 — with decimal places
Currency $1,234.56 — with currency symbol
Accounting Aligns currency symbols — used in finance
Short Date / Long Date 1/1/2024 or January 1, 2024
Time Shows time: 12:30 PM
Percentage (%) 0.5 becomes 50%
Fraction 0.5 becomes 1/2
Scientific Large numbers: 1.23E+06
Text Treats cell as text (numbers stored as text)
Custom Format Create your own format like dd-mmm-yyyy
💡 Hinglish: Number Format = ek hi number ko alag alag dikhana — jaise 0.5 ko 50% ya Rs.1,000 banana.
Actual value same rehti hai!

1.5 Styles Group

Feature Use
Conditional Formatting Automatically color/highlight cells based on rules
Format as Table Convert data range to formatted table with filters
Cell Styles Quick predefined cell formatting styles (Good, Bad, Neutral)

Conditional Formatting — Detail

Conditional Formatting automatically formats cells based on their values.
 • Highlight Cell Rules: Greater than, Less than, Between, Equal to
• Top/Bottom Rules: Top 10 items, Bottom 10%, Above/Below Average
• Data Bars: Shows a bar inside cell based on value
• Color Scales: Gradient color based on value (green-yellow-red)
• Icon Sets: Show arrows/flags/shapes based on value
• New Rule: Create custom formula-based rules
⭐ EXAM TIP: Conditional Formatting mein formula use kar sakte ho — e.g., =A1>100 — ye practical
question mein aata hai!

1.6 Cells Group

Option Use
Insert Add new rows or columns
Cells/Rows/Columns
Delete Remove rows, columns, or sheets
Cells/Rows/Columns
Format > Row Height Change height of a row
Format > Column Width Change width of a column
Format > Hide/Unhide Hide rows, columns, or sheets
Format > Protect Sheet Lock sheet from editing

1.7 Editing Group

Feature Use
AutoSum (Alt+=) Quickly sum selected cells — =SUM()
Fill Fill series: numbers, dates, days, months
Clear Clear content, formats, comments, or all
Sort & Filter Sort A-Z or filter data
Find & Select (Ctrl+F) Find text, replace (Ctrl+H), or go to special cells
💡 Hinglish: AutoSum = ek click mein column ka total — sabse shortcut cheez Excel mein!
📥 TAB 2: INSERT TAB
The INSERT tab lets you add objects — tables, charts, pictures, shapes, pivot tables, links, and
more.
💡 Hinglish: Insert Tab = Excel mein naya cheez dalna — chart, image, shape, link sab yahan se aata hai!

2.1 Tables Group

PivotTable
PivotTable is the most powerful analysis tool in Excel. It summarizes large data into a compact report.
• Drag fields to Rows, Columns, Values, and Filters area
• Group data by date (month, quarter, year)
• Calculate Sum, Count, Average, Max, Min of data
• Refresh PivotTable when source data changes
💡 Hinglish: PivotTable = bade data ka summary banana — jaise 1000 employees ki salary se department-wise
total nikalna!

Table (Ctrl+T)
Converts a data range into a structured Excel Table with automatic filtering, auto-expand, and table references.
• Table name can be used in formulas: =SUM(Table1[Sales])
• Automatically adds filters to all columns
• New rows added automatically inherit formulas

2.2 Charts Group

Charts visually represent data. Select data first, then Insert a chart.
Chart Type Best Used For
Column Chart Comparing data across categories
Bar Chart Horizontal comparison (for long labels)
Line Chart Trends over time (e.g., monthly sales)
Pie Chart Shows percentage/proportion (one series only)
Area Chart Like line but filled — shows volume over time
Scatter Chart (XY) Relationship between two variables
Bubble Chart 3-variable data comparison
Combo Chart Two chart types combined (e.g., bar + line)
Sparklines Tiny charts inside a cell — for trend visualization
💡 Hinglish: Chart = number ko picture mein dikhana. Examiner ko dikhana ho toh chart best way hai!

⭐ EXAM TIP: Chart elements: Chart Title, Axis Title, Legend, Data Labels, Gridlines, Trendline —
sab insert tab se add hote hain!
2.3 Illustrations Group
Option Use
Pictures Insert image from your computer
Online Pictures Search and insert images from the web
Shapes Draw rectangles, arrows, circles, flowchart shapes
Icons Insert scalable vector icons
3D Models Insert 3D objects (.fbx format)
SmartArt Create organization charts, process diagrams, lists visually
Screenshot Capture and insert screenshots directly

2.4 Add-ins Group

Store — Download extra tools and apps for Excel from Microsoft AppSource.

2.5 Links Group

Option Use
Insert Hyperlink (Ctrl+K) Add clickable link to website, file, email, or cell
Bookmark Navigate within the same document

2.6 Text Group

Option Use
Text Box Insert a floating text area
Header & Footer Add text to top/bottom of printed pages
WordArt Stylized decorative text
Signature Line Add digital signature
Object Embed another file (Word doc, PDF) inside Excel

2.7 Symbols Group

Option Use
Equation Insert mathematical equations
Symbol Insert special characters like ©, ®, ™, ₹, €
📄 TAB 3: PAGE LAYOUT TAB
The PAGE LAYOUT tab controls how your spreadsheet looks when printed — margins, orientation,
paper size, print area, etc.
💡 Hinglish: Page Layout = Excel ko print karne ke liye ready karna — jaise notebook mein margin banana!

3.1 Themes Group

Option Use
Themes Apply a complete color/font theme to the workbook
Colors Change the color palette of the theme
Fonts Change heading and body fonts
Effects Change visual effects for shapes and charts

3.2 Page Setup Group

Option Use
Margins Set Top, Bottom, Left, Right margins (Normal/Wide/Narrow)
Orientation Portrait (tall) or Landscape (wide) printing
Size Paper size: A4, Letter, Legal etc.
Print Area Select which cells to print
Breaks Insert page break to control where new page starts
Background Set a background image for the sheet
Print Titles Repeat row/column headings on every printed page
💡 Hinglish: Print Titles = ek baar heading set karo, har page pe automatically aayegi — jaise register mein pehle
page ka format!

3.3 Scale to Fit Group

Option Use
Width Fit all columns in 1 page (or set number of pages)
Height Fit all rows in specified pages
Scale Zoom in/out percentage for printing

3.4 Sheet Options Group

Option Use
Gridlines > View Show/hide grid lines on screen
Gridlines > Print Include/exclude gridlines in print
Headings > View Show/hide row numbers and column letters
Headings > Print Print row/column headers

3.5 Arrange Group

Option Use
Bring Forward / Send Layer order of shapes/images
Backward
Align Align multiple objects left/right/center/top/bottom
Group / Ungroup Group multiple objects together
Rotate Rotate selected object
🔢 TAB 4: FORMULAS TAB
The FORMULAS tab is where you find all Excel functions organized by category. This is the MOST
IMPORTANT tab for calculations!
💡 Hinglish: Formulas Tab = Excel ki brain. Yahan sab functions milte hain — math se leke text, date, lookup tak
sab!

4.1 How Formulas Work

A formula always starts with = sign. Excel then calculates based on the formula.
=A1+B1 Adds values of A1 and B1
=SUM(A1:A10) Sum of all cells from A1 to A10
=A1*B1-C1 Multiply A1 by B1, then subtract C1

Cell References
Type Example & Meaning
Relative Reference A1 — changes when formula is copied
Absolute Reference $A$1 — does NOT change when formula copied (use F4)
Mixed Reference $A1 or A$1 — only row or column is fixed
💡 Hinglish: $A$1 = lock kar diya — copy karo toh bhi nahi badlega. Jaise rate card fixed rehta hai!

⭐ EXAM TIP: F4 key press karo cell reference mein — A1 → $A$1 → A$1 → $A1 → A1 cycle karta
hai!

4.2 Math & Trig Functions

=SUM(A1:A10) Total/sum of a range
=SUMIF(range,criteria, Sum cells that meet a condition
sum_range)
=SUMIFS(sum_range,r1,c Sum with multiple conditions
1,r2,c2)
=PRODUCT(A1:A5) Multiply all values in range
=SQRT(A1) Square root of A1
=POWER(A1,3) A1 to the power of 3 (A1³)
=MOD(A1,B1) Remainder when A1 is divided by B1
=INT(A1) Rounds down to nearest integer
=ROUND(A1,2) Round A1 to 2 decimal places
=ROUNDUP(A1,0) Always round up
 =ROUNDDOWN(A1,0) Always round down
=ABS(A1) Absolute value (removes negative sign)
=RAND() Random number between 0 and 1
=RANDBETWEEN(1,100) Random integer between 1 and 100
=CEILING(A1,5) Round up to nearest multiple of 5
=FLOOR(A1,5) Round down to nearest multiple of 5
=PI() Returns value of π (3.14159...)

4.3 Statistical Functions

=AVERAGE(A1:A10) Mean/average of values
=AVERAGEIF(range,crite Average of cells meeting one condition
ria,avg_range)
=AVERAGEIFS(...) Average with multiple conditions
=COUNT(A1:A10) Count cells with numbers
=COUNTA(A1:A10) Count non-empty cells (any data)
=COUNTBLANK(A1:A10) Count empty/blank cells
=COUNTIF(range,criteri Count cells meeting a condition
a)
=COUNTIFS(r1,c1,r2,c2) Count with multiple conditions
=MAX(A1:A10) Highest/maximum value
=MIN(A1:A10) Lowest/minimum value
=LARGE(A1:A10,3) 3rd largest value in range
=SMALL(A1:A10,2) 2nd smallest value in range
=MEDIAN(A1:A10) Middle value (50th percentile)
=MODE(A1:A10) Most frequently occurring value
=STDEV(A1:A10) Standard deviation of sample
=VAR(A1:A10) Variance of sample
=RANK(A1,A1:A10,0) Rank of A1 — 0=descending, 1=ascending
=PERCENTILE(A1:A10,0.9 90th percentile value
)

4.4 Logical Functions

=IF(condition, Basic IF — if condition true, show one value, else another
value_if_true,
value_if_false)
 =IF(A1>50,"Pass","Fail If A1 > 50 show Pass else Fail
")
=AND(A1>0, B1>0) TRUE only if ALL conditions are true
=OR(A1>0, B1>0) TRUE if ANY condition is true
=NOT(A1>0) Reverses TRUE/FALSE
=IFS(c1,v1,c2,v2,TRUE, Multiple conditions — no nested IF needed
"else")
=IFERROR(formula, Show custom message if formula gives error
"Error!")
=IFNA(formula, "Not Show custom message only for #N/A error
Found")
=SWITCH(A1,1,"One",2," Match a value to one of many options
Two","Other")

Nested IF Example
=IF(A1>=90,"A Grade",IF(A1>=75,"B Grade",IF(A1>=60,"C Grade","Fail")))
💡 Hinglish: Nested IF = ek IF ke andar aur IF — jaise grade system: 90+ = A, 75+ = B, 60+ = C, baaki Fail!

⭐ EXAM TIP: IFS function nested IF se aasaan hai — directly likh sakte hain multiple conditions ek
line mein!

4.5 Lookup & Reference Functions

=VLOOKUP(lookup_val, Vertical lookup — searches first column
table, col_num, 0)
=HLOOKUP(lookup_val, Horizontal lookup — searches first row
table, row_num, 0)
=INDEX(array, row_num, Returns value at given row/column position
col_num)
=MATCH(lookup_val, Returns position number of a value in array
lookup_array, 0)
=INDEX(A1:A10,MATCH(D1 INDEX+MATCH — better than VLOOKUP
,B1:B10,0))
=XLOOKUP(lookup,lookup Modern replacement for VLOOKUP (Excel 365)
_array,return_array)
=CHOOSE(index_num, v1, Choose from list based on number
v2, v3)
=OFFSET(A1, rows, Returns cell/range offset from reference
cols, height, width)
=INDIRECT("A"&B1) Returns value from cell address in text form
=ROW(A1) Row number of A1 (returns 1)
 =COLUMN(B1) Column number of B1 (returns 2)
=ROWS(A1:A10) Number of rows in range (returns 10)
=COLUMNS(A1:D1) Number of columns in range (returns 4)
=TRANSPOSE(A1:D4) Converts rows to columns (array formula)

VLOOKUP — Detailed Explanation

=VLOOKUP(D2, A2:C100, 3, 0) → Look for value in D2, in the table A2:C100, return 3rd column,
exact match (0=exact, 1=approximate)
💡 Hinglish: VLOOKUP = ek student ka roll number dalao — uski naam, marks, grade sab mil jaye. 0 exact
match ke liye hamesha!

⭐ EXAM TIP: VLOOKUP can only look RIGHT (from first column). INDEX+MATCH can look in any
direction!

4.6 Text Functions

=LEN(A1) Count characters in cell
=LEFT(A1, 5) Extract 5 characters from left
=RIGHT(A1, 3) Extract 3 characters from right
=MID(A1, 3, 4) Extract 4 characters starting from position 3
=FIND("@",A1) Find position of character (case-sensitive)
=SEARCH("excel",A1) Find position (case-insensitive)
=UPPER(A1) Convert to UPPERCASE
=LOWER(A1) Convert to lowercase
=PROPER(A1) Capitalize First Letter Of Each Word
=TRIM(A1) Remove extra spaces from text
=CLEAN(A1) Remove non-printable characters
=CONCATENATE(A1," Join text from multiple cells
",B1)
=A1&" "&B1 Simpler way to join text (& operator)
=CONCAT(A1:A5) Concatenate a range of cells
=TEXTJOIN(", Join text with separator, ignore blanks
",TRUE,A1:A5)
=TEXT(A1,"dd-mmm- Format a number/date as text
yyyy")
=VALUE(A1) Convert text number to actual number
=SUBSTITUTE(A1,"old"," Replace specific text in a cell
new")
 =REPLACE(A1,1,3,"XXX") Replace characters by position
=REPT("*",5) Repeat character 5 times: *****
=EXACT(A1,B1) Check if two texts are exactly equal (TRUE/FALSE)

4.7 Date & Time Functions

=TODAY() Today's date (updates automatically)
=NOW() Current date and time
=DATE(year, month, Create a date from year, month, day
day)
=YEAR(A1) Extract year from date
=MONTH(A1) Extract month number (1-12)
=DAY(A1) Extract day number (1-31)
=HOUR(A1) Extract hour from time
=MINUTE(A1) Extract minutes
=SECOND(A1) Extract seconds
=WEEKDAY(A1,2) Day of week: 1=Mon...7=Sun (type 2)
=WEEKNUM(A1) Week number of the year
=DATEDIF(A1,B1,"Y") Difference between dates in Y/M/D
=DAYS(B1,A1) Number of days between two dates
=NETWORKDAYS(A1,B1) Working days between dates (excludes weekends)
=WORKDAY(A1,10) Date after 10 working days
=EOMONTH(A1,0) Last day of current month
=EDATE(A1,3) Date exactly 3 months later
=TEXT(TODAY(),"dddd") Today's day name (Monday, Tuesday...)

4.8 Financial Functions

=PMT(rate, nper, pv) Monthly loan payment (EMI calculator)
=PV(rate, nper, pmt) Present Value of investment
=FV(rate, nper, pmt) Future Value of investment
=NPV(rate, v1, Net Present Value of cash flows
v2, ...)
=IRR(values) Internal Rate of Return
=RATE(nper, pmt, pv) Interest rate per period
=NPER(rate, pmt, pv) Number of payment periods
 =SLN(cost, salvage, Straight-line depreciation per year
life)
=DB(cost, salvage, Declining Balance depreciation
life, period)
=DDB(cost, salvage, Double Declining Balance depreciation
life, period)
💡 Hinglish: PMT formula = Home loan ya car loan ka EMI nikalna — rate per month mein daalo, months mein
nper, loan amount pv mein!

4.9 Information Functions

=ISNUMBER(A1) TRUE if cell contains a number
=ISTEXT(A1) TRUE if cell contains text
=ISBLANK(A1) TRUE if cell is empty
=ISERROR(A1) TRUE if cell has any error
=ISNA(A1) TRUE if cell has #N/A error
=ISODD(A1) TRUE if number is odd
=ISEVEN(A1) TRUE if number is even
=CELL("type",A1) Returns info about a cell
=TYPE(A1) Returns type: 1=number, 2=text, 4=logical, 16=error

4.10 Array Formulas (Ctrl+Shift+Enter)

Array formulas perform calculations on multiple cells at once. Press Ctrl+Shift+Enter (not just Enter).
They show in {curly braces}.
{=SUM(A1:A10*B1:B10)} Sum of A*B for each row (array formula)
{=MAX(IF(A1:A10>0,A1:A Max value only for positive numbers
10))}
=SUMPRODUCT(A1:A10,B1: SUMPRODUCT is like array formula but no Ctrl+Shift+Enter needed
B10)

⭐ EXAM TIP: SUMPRODUCT is very powerful — use

=SUMPRODUCT((condition1)*(condition2)*values) for conditional sums!

4.11 Formula Auditing Group

Tool Use
Trace Precedents Show arrows to cells that feed INTO current cell
Trace Dependents Show arrows to cells that USE current cell
Error Checking Find and fix errors in formulas
Evaluate Formula Step-by-step formula calculation — very useful for debugging
Watch Window Monitor specific cells while working elsewhere
Show Formulas (Ctrl+`) Show all formulas in sheet instead of values
Calculation Options Automatic/Manual calculation mode

4.12 Common Excel Errors

Error Cause & Fix
#DIV/0! Division by zero — use =IFERROR(A1/B1,0)
#VALUE! Wrong data type in formula — check if text used as number
#REF! Invalid cell reference — usually from deleted rows/cols
#NAME? Function name misspelled — check spelling
#N/A Value not found (in VLOOKUP) — use IFERROR or IFNA
#NULL! Space used instead of colon/comma in range
#NUM! Invalid numeric value — like SQRT(-1)
##### (hash) Column too narrow to show value — widen the column
📊 TAB 5: DATA TAB
The DATA tab is for managing, sorting, filtering, validating, and analyzing data. Essential for
database-style operations!
💡 Hinglish: Data Tab = bade data ko manage karna — sort karo, filter karo, validate karo, connections banao!

5.1 Get & Transform Data (Power Query)

Source Use
From Excel Import data from another Excel file
From CSV/Text Import .csv or .txt data files
From Web Scrape data from a website URL
From Database Connect to SQL Server, Access etc.
From Other Sources PDF, SharePoint, OData, Hadoop etc.
Recent Sources Quick access to recent connections
Power Query is a powerful ETL tool — Extract, Transform, Load. It cleans and reshapes data before loading into
Excel.

5.2 Sort & Filter

Feature Use
Sort A to Z / Z to A Sort text alphabetically or numbers low-to-high
Custom Sort Sort by multiple columns with different orders
Filter (Ctrl+Shift+L) Show only rows matching selected criteria
Advanced Filter Complex filter criteria — can filter to another location
Clear Filter Remove all filters and show all data
💡 Hinglish: Filter = data mein se specific rows dhundna — jaise class mein se only Pass students dikhana!

5.3 Data Tools

Data Validation
Controls what type of data can be entered in a cell.
Validation Type Example
Whole Number Allow only integers between 1 and 100
Decimal Allow numbers with decimals
List Create a dropdown list (YES/NO, or range values)
Date Allow only dates in a range
 Time Allow only times
Text Length Limit characters (e.g., max 10 characters)
Custom Use a formula as validation rule
💡 Hinglish: Data Validation = form ka rule banana — jaise DOB field mein sirf date allow karna, text nahi!

Text to Columns
Splits a single column into multiple columns based on a delimiter (comma, space, tab, etc.)
💡 Hinglish: Text to Columns = ek cell mein 'Saif Khan' hai — First Name aur Last Name alag karna!

Remove Duplicates
Finds and removes duplicate rows from selected columns.

Flash Fill (Ctrl+E)

Automatically detects pattern and fills data. E.g., extract first name from full name column automatically.
💡 Hinglish: Flash Fill = magic tool — pattern samajhke data automatically fill kar deta hai!

What-If Analysis
Tool Use
Goal Seek Find input needed to achieve a target result — reverse calculation
Scenario Manager Save and compare multiple sets of values
Data Table (1 or 2 See how formula changes with different input values
variable)
💡 Hinglish: Goal Seek = ulta calculation — target result pata hai, input dhundna hai. Jaise desired profit ke liye
kitna sale chahiye!

⭐ EXAM TIP: Goal Seek: Data > What-If Analysis > Goal Seek. Set Cell = formula cell, To Value =
target, By Changing = input cell

Consolidate
Combines data from multiple ranges/sheets into one summary using Sum, Average, Count, etc.

5.4 Outline Group

Feature Use
Group Collapse/expand rows or columns (subtotal rows)
Ungroup Remove grouping
Subtotal Automatically add subtotals at group breaks
Auto Outline Automatically detect and create outline structure
✅ TAB 6: REVIEW TAB
The REVIEW tab handles proofreading, comments, tracking changes, and protecting
sheets/workbooks.
💡 Hinglish: Review Tab = document check karna aur protect karna — jaise exam paper review karna submit
karne se pehle!

6.1 Proofing Group

Feature Use
Spelling (F7) Check spelling of text in spreadsheet
Research Look up words/definitions in reference books
Thesaurus Find synonyms for selected word
Smart Lookup Search the web for selected text

6.2 Accessibility
Check Accessibility: Finds issues for people with disabilities (screen readers etc.)

6.3 Language Group

Feature Use
Translate Translate selected text to another language
Language Set proofing language for spelling check

6.4 Comments Group

Feature Use
New Comment Add a note/comment to a cell (shows as red triangle)
Edit Comment Modify existing comment
Delete Comment Remove comment from cell
Previous/Next Comment Navigate between comments
Show/Hide Comments Toggle visibility of all comments
Show All Comments Make all comments visible at once
Notes vs Threaded Notes = old style pop-up, Comments = threaded discussion (Excel 365)
Comments
💡 Hinglish: Comments = sticky note ki tarah — cell pe note chipkao bina original data change kiye!
6.5 Protect Group
Feature Use
Protect Sheet Lock sheet with password — prevent editing
Protect Workbook Protect workbook structure (no add/delete sheets)
Allow Edit Ranges Allow specific cells to be edited even with sheet protection
Share Workbook (legacy) Allow multiple users to edit simultaneously
Track Changes Record all edits made to the file (who, what, when)
💡 Hinglish: Protect Sheet = sheet ko lock karna — important data accidentally change na ho jaye!

⭐ EXAM TIP: Protect Sheet mein password optional hai. Selected cells ko unlock karne ke liye:
Format Cells > Protection > uncheck Locked, THEN protect the sheet.
 TAB 7: VIEW TAB
The VIEW tab controls how you see the spreadsheet on screen — zoom, freeze panes, split view,
multiple windows.
💡 Hinglish: View Tab = Excel ka display control — screen kaise dikhega, kya freeze karna hai, zoom kitna —
sab yahan!

7.1 Workbook Views

View Use
Normal Default view for editing
Page Break Preview See where pages will break when printed — drag to adjust
Page Layout View WYSIWYG view — see exactly how it will print
Custom Views Save specific zoom, print area settings as a named view

7.2 Show Group

Option Use
Ruler Show/hide ruler in Page Layout view
Gridlines Show/hide cell border lines on screen
Formula Bar Show/hide formula bar
Headings Show/hide row numbers and column letters
Message Bar Show security warnings bar

7.3 Zoom Group

Option Use
Zoom Set custom zoom percentage
100% Reset to default 100% zoom
Zoom to Selection Zoom in to fit selected cells on screen

7.4 Window Group — Very Important!

Feature Use
New Window Open same file in two separate windows
Arrange All Tile multiple Excel windows side by side
 Freeze Panes Keep rows/columns visible while scrolling
Split Divide screen into 2 or 4 panes for the same sheet
Hide / Unhide Window Hide a workbook window
View Side by Side Compare two workbooks simultaneously
Synchronous Scrolling Scroll both workbooks together
Switch Windows Switch between open Excel files

Freeze Panes — Detailed

Option Use
Freeze Top Row Keep first row visible while scrolling down
Freeze First Column Keep first column visible while scrolling right
Freeze Panes Freeze rows above AND columns to left of selected cell
💡 Hinglish: Freeze Panes = header ko jagah pe rokna — jaise 1000 row ka data scroll karo toh bhi column name
dikhta rahe!

⭐ EXAM TIP: Freeze Panes ke liye: Click on the cell BELOW the row and RIGHT of the column you
want frozen, THEN click Freeze Panes.

7.5 Macros Group

Feature Use
View Macros See list of recorded macros
Record Macro Record your actions as a reusable macro
Use Relative References Record macro with relative cell positions
💡 Hinglish: Macro = ek baar actions record karo, bar bar repeat karo. Jaise photocopy machine — ek original,
baaki copies!
⚙️TAB 8: DEVELOPER TAB
The DEVELOPER tab is hidden by default. Enable it from: File > Options > Customize Ribbon >
check Developer. It's used for macros, VBA, and form controls.
💡 Hinglish: Developer Tab = advanced users ke liye — VBA coding, macros, aur forms banana. Pehle enable
karna padta hai!

8.1 Code Group

Feature Use
Visual Basic (Alt+F11) Open VBA editor — write/edit macro code
Macros (Alt+F8) View, run, edit, or delete macros
Record Macro Record user actions as VBA code
Macro Security Set security level for running macros

8.2 Add-ins Group

Feature Use
Excel Add-ins Enable/disable Excel Add-ins (like Solver, Analysis ToolPak)
COM Add-ins Third-party add-ins
Insert Add-in Get new add-ins from store
Analysis ToolPak — Important add-in for statistical analysis (Descriptive Statistics, Regression, Histogram, etc.)

8.3 Controls Group

Control Use
Button Create a clickable button that runs a macro
Combo Box / List Box Dropdown or list selector in the sheet
Check Box Tick/untick box (returns TRUE/FALSE)
Option Button (Radio) Select one from multiple options
Spin Button Increment/decrement a number
Scroll Bar Slider to change cell value
Label / Text Box Static text or editable text area
Toggle Button On/Off button (returns TRUE/FALSE)
8.4 XML Group
Tools for importing, exporting, and mapping XML data in Excel — used in enterprise/data integration scenarios.
⌨️ESSENTIAL KEYBOARD SHORTCUTS
Memorize these shortcuts — they save HUGE time in Excel! Examiner bhi impressed hoga!

Navigation Shortcuts
Shortcut Action
Ctrl+Home Go to cell A1
Ctrl+End Go to last used cell
Ctrl+Arrow Keys Jump to last filled cell in direction
Ctrl+Page Up/Down Switch between sheets
F5 or Ctrl+G Go To (navigate to specific cell)
Alt+Page Up/Down Scroll left/right one screen

Editing Shortcuts
Shortcut Action
F2 Edit current cell
Escape (Esc) Cancel editing — restore original value
Delete Clear cell content (keeps format)
Ctrl+Z Undo last action
Ctrl+Y Redo
Ctrl+D Fill Down (copy cell above to selected cells)
Ctrl+R Fill Right
Ctrl+; Insert today's date
Ctrl+Shift+: Insert current time
Alt+Enter New line within same cell
Ctrl+Shift+Plus Insert row/column
Ctrl+Minus Delete row/column
Ctrl+1 Open Format Cells dialog

Selection Shortcuts
Shortcut Action
Ctrl+A Select all cells
Ctrl+Shift+End Select from current cell to last used cell
Ctrl+Shift+Home Select from current cell to A1
Shift+Arrow Keys Extend selection one cell at a time
Ctrl+Space Select entire column
Shift+Space Select entire row

Formula Shortcuts
Shortcut Action
Alt+= AutoSum selected range
F4 Toggle absolute/relative reference ($A$1)
Ctrl+` Toggle show formulas / show values
Ctrl+Shift+Enter Enter array formula
F9 Calculate all worksheets
Shift+F9 Calculate active worksheet only
PRACTICAL TIPS & BEST PRACTICES
Ye tips follow karo toh Excel mein koi bhi problem solve ho jayegi!

Data Entry Best Practices

• Always keep one row of headers — do not merge cells in data area
• No blank rows/columns within data — breaks sorting, filtering, PivotTables
• Use consistent date formats — dd/mm/yyyy throughout
• Store dates as actual dates, not text — check with =ISNUMBER() function
• Use Data Validation for data entry control
• Color-code inputs (blue) vs formulas (black) — professional practice

Formula Best Practices

• Use named ranges instead of hard-coded references (Ctrl+F3 to define names)
• Use IFERROR to handle errors gracefully
• Document complex formulas with Comments
• Use absolute references ($) when copying formulas that point to fixed cells
• Avoid circular references (formula referring to its own cell)

Printing Best Practices

• Set Print Area before printing (Page Layout > Print Area > Set Print Area)
• Use Print Titles to repeat headers on every page
• Use Page Break Preview to control page breaks
• Check Print Preview before printing (Ctrl+P)
• Use Fit to Page: 1 page wide x automatic for single-column reports

Chart Best Practices

• Always add Chart Title and Axis Labels
• Use Data Labels to show values on bars/lines
• Choose chart type based on data — pie for proportions, line for trends
• Use Combo Chart to show two measures with different scales
• Move chart to its own sheet for large presentations
⚡ QUICK REVISION SHEET
Last minute revision ke liye — ye page sabse important hai!

Top 20 Most Important Functions

# Function & Purpose
1 =SUM(A1:A10) — Add values
2 =AVERAGE(A1:A10) — Calculate mean
3 =COUNT(A1:A10) — Count numbers
4 =COUNTA(A1:A10) — Count non-blank
5 =IF(condition,true,false) — Conditional logic
6 =VLOOKUP(val,table,col,0) — Lookup data
7 =INDEX(array,row,col) — Return value by position
8 =MATCH(val,range,0) — Find position of value
9 =COUNTIF(range,criteria) — Count with condition
10 =SUMIF(range,criteria,sum) — Sum with condition
11 =IFERROR(formula,alt) — Handle errors
12 =TODAY() — Current date
13 =TEXT(val,format) — Format as text
14 =CONCATENATE(A1,B1) — Join text
15 =LEFT/RIGHT/MID — Extract text
16 =LEN(A1) — Count characters
17 =TRIM(A1) — Remove extra spaces
18 =ROUND(A1,2) — Round to decimals
19 =MAX/MIN(range) — Highest/Lowest value
20 =PMT(rate,nper,pv) — Loan EMI calculator

Key Excel Concepts — One Line Summary

Concept One Line
PivotTable Summarize large data with drag-and-drop — most powerful tool
VLOOKUP Find value in first column, return value from another column
Conditional Formatting Auto-color cells based on rules — visual data analysis
Data Validation Control what is entered — dropdown, number range, date
Goal Seek Reverse calculation — set target, find required input
Freeze Panes Lock row/column headers while scrolling
Absolute Reference $ Fix cell in formula so it doesn't move when copied
Array Formula Calculate on multiple cells — press Ctrl+Shift+Enter
SUMPRODUCT Multiply arrays and sum — no array formula needed
Flash Fill Auto-fill pattern detected by Excel — Ctrl+E
Power Query ETL tool — import, clean, transform data from any source
Macro/VBA Automate repetitive tasks with recorded/coded actions

ALL THE BEST! 🎯 Excel sikhna ek skill hai jo har field mein kaam aati hai — commerce, business,
finance, HR sab jagah! Practice karte raho!