MS Excel Complete Notes for Exams (SSC, Bank PO, SBI, IBPS, RRB, UGC NET)
📘 1. Introduction to MS Excel
MS Excel is a spreadsheet application developed by Microsoft.
It is used for data entry, calculations, data analysis, graph plotting, pivot tables, and automation using formulas/macros.File extension:
.xlsx (Excel Workbook), .xls (older version), .xlsm (macro-enabled).📊 2. Important Technical Terms
| Term | Description |
|---|---|
| Workbook | Entire Excel file (contains worksheets). |
| Worksheet | A single spreadsheet page in a workbook. |
| Cell | Intersection of a row and a column (e.g., A1, B3). |
| Cell Range | A group of cells (e.g., A1:B5). |
| Formula | Begins with = (e.g., =A1+B1). |
| Function | Predefined formula (e.g., =SUM(A1:A5)). |
| Row | Horizontal data line (numbered: 1, 2, 3…). |
| Column | Vertical data line (lettered: A, B, C…). |
| Cell Reference | Address of a cell (A1, B2, etc.). |
| Relative Reference | Changes when copied. |
| Absolute Reference | Fixed with $ (e.g., $A$1). |
🖥️ 3. Explanation of Important Icons (MS Excel Ribbon Tabs)
📂 File Tab
-
New, Open, Save, Save As, Print, Share.
🏠 Home Tab
| Icon | Use |
|---|---|
| Clipboard | Cut, Copy, Paste. |
| Font | Bold, Italic, Underline, Font Size, Font Color. |
| Alignment | Align Left, Center, Right, Merge & Center. |
| Number | Change cell format (Currency, %, Date). |
| Styles | Conditional Formatting, Table Styles. |
| Editing | Find & Replace, Sort, Filter, AutoSum. |
✏️ Insert Tab
| Icon | Use |
|---|---|
| Tables | Insert Table or Pivot Table. |
| Charts | Column, Pie, Line, Bar charts. |
| Illustrations | Shapes, Icons, Images. |
| Sparklines | Tiny charts in cells. |
| Text | Insert Text Box, Header, Footer. |
📈 Formulas Tab
| Icon | Use |
|---|---|
| Function Library | SUM, IF, VLOOKUP, HLOOKUP, NOW(), etc. |
| Name Manager | Manage named cell ranges. |
| Formula Auditing | Trace precedents, errors. |
📊 Data Tab
| Icon | Use |
|---|---|
| Sort & Filter | Arrange and filter data. |
| Data Tools | Remove Duplicates, Data Validation. |
| What-If Analysis | Goal Seek, Scenario Manager. |
🔧 Review Tab
| Icon | Use |
|---|---|
| Spelling | Check spelling. |
| Comments | Add, Edit, Delete Comments. |
| Protect | Protect sheet/workbook. |
👁️ View Tab
| Icon | Use |
|---|---|
| Normal | Normal worksheet view. |
| Page Layout | Print Preview view. |
| Zoom | Zoom in/out. |
| Freeze Panes | Lock rows/columns. |
🧠 4. 50+ Most Important MCQs (with Answers)
✅ Q1: What is the extension of Excel 2007-2019 files?
a) .xls
b) .docx
✅ c) .xlsx
d) .xlsm
✅ Q2: Which function is used to calculate average in Excel?
a) Count()
b) Sum()
✅ c) Average()
d) Min()
✅ Q3: Shortcut key to edit a cell is:
a) F4
b) F5
✅ c) F2
d) F3
✅ Q4: Which of the following is not a valid data type in Excel?
a) Number
b) Date
✅ c) Character
d) Text
✅ Q5: What is the default row height in Excel?
a) 10.00
✅ b) 15.00
c) 20.00
d) 12.75
✅ Q6: Shortcut to create a new workbook:
a) Ctrl + S
b) Ctrl + C
✅ c) Ctrl + N
d) Ctrl + V
✅ Q7: SUM(A1:A5) will…
a) Subtract values
✅ b) Add values from A1 to A5
c) Multiply values
d) Divide values
✅ Q8: What does VLOOKUP stand for?
a) Vertical Lookup
b) Value Lookup
✅ c) Vertical Lookup Function
d) Validation Lookup
(More MCQs provided in PDF at the end)
⌨️ 5. Most Useful Shortcut Keys in MS Excel
| Shortcut | Action |
|---|---|
| Ctrl + N | New Workbook |
| Ctrl + O | Open Workbook |
| Ctrl + S | Save Workbook |
| F12 | Save As |
| Ctrl + P | |
| Ctrl + C / Ctrl + V | Copy / Paste |
| Ctrl + Z / Ctrl + Y | Undo / Redo |
| F2 | Edit Active Cell |
| Alt + = | AutoSum |
| Ctrl + Arrow Key | Jump to edge of data |
| Ctrl + Space | Select entire column |
| Shift + Space | Select entire row |
| Ctrl + Shift + L | Add/Remove filter |
| Ctrl + Shift + $ | Format as Currency |
| Ctrl + ` | Show formulas |
| Alt + Enter | Line break in a cell |
📌 6. Commonly Asked PYQs (Previous Year Questions)
-
From SSC CGL, CHSL, Bank PO, SBI Clerk, IBPS, RRB, UGC NET:
Purpose of Pivot Table?
Difference between absolute & relative cell reference?What is concatenation in Excel?
Purpose of Goal Seek?
Function to find maximum value?
Shortcut to insert a chart?
MS Excel MCQs for Competitive Exams
🟩 1 to 10
-
The default extension of Excel 2007 and later is —
a) .xls
✅ b) .xlsx
c) .doc
d) .xlsm -
Which function adds values in Excel?
a) COUNT()
b) AVERAGE()
✅ c) SUM()
d) PRODUCT() -
What is the intersection of a row and a column called?
✅ a) Cell
b) Range
c) Address
d) Value -
Shortcut key to edit a cell is —
a) F4
✅ b) F2
c) F5
d) Ctrl + E -
To insert a chart in Excel, use —
a) Alt + C
b) Ctrl + Shift + C
✅ c) F11
d) Alt + F1 -
What does VLOOKUP do?
✅ a) Searches vertically
b) Searches horizontally
c) Validates data
d) Calculates average -
Which of the following is a valid cell reference?
a) A$2B
✅ b) $A$1
c) 2A$
d) AB12$ -
A group of worksheets is called —
a) Workbook
✅ b) Workbook
c) Cell group
d) Document -
Which symbol is used for absolute reference?
a) @
✅ b) $
c) #
d) % -
Which formula returns the current date?
a) =DATE()
b) =NOW()
✅ c) =TODAY()
d) =CURRENTDATE()
🟩 11 to 20
-
Function to count numeric values in a range —
a) COUNTA()
✅ b) COUNT()
c) SUM()
d) NUM() -
Shortcut to select entire column —
a) Ctrl + R
b) Shift + C
✅ c) Ctrl + Space
d) Alt + C -
Function to find highest value —
a) MAXIMUM()
✅ b) MAX()
c) LARGE()
d) TOP() -
To remove duplicates, go to —
a) Data > Tools
✅ b) Data > Remove Duplicates
c) Edit > Remove
d) Home > Clear -
Which function returns the number of characters?
a) COUNT()
✅ b) LEN()
c) NUM()
d) SIZE() -
To freeze top row —
✅ a) View > Freeze Panes > Freeze Top Row
b) File > Freeze
c) Home > Freeze
d) Insert > Lock -
How many rows are in Excel 2016?
a) 10,000
✅ b) 1,048,576
c) 65,536
d) 256 -
Which function joins text?
a) JOIN()
b) TEXT()
✅ c) CONCATENATE() or CONCAT()
d) STRING() -
Shortcut for Save As —
a) Ctrl + S
b) F11
✅ c) F12
d) Ctrl + Shift + S -
Which chart is best for showing trends?
a) Pie
✅ b) Line
c) Column
d) Bar
🟩 21 to 30
-
To insert a new worksheet —
✅ a) Shift + F11
b) Alt + W
c) Ctrl + N
d) Alt + Insert -
The default number of worksheets in a new workbook is —
✅ a) 1
b) 3
c) 2
d) 5 -
Which of the following is not a chart type?
a) Pie
✅ b) Sheet
c) Bar
d) Line -
To protect a worksheet —
✅ a) Review > Protect Sheet
b) File > Lock
c) View > Lock
d) Insert > Secure -
Shortcut for AutoSum —
a) Alt + Shift + =
✅ b) Alt + =
c) Ctrl + Shift + A
d) Ctrl + = -
Which function gives average of numbers?
a) ADD()
b) MEDIAN()
✅ c) AVERAGE()
d) COUNT() -
Which operator is used for exponentiation?
a) ^
✅ b) ^
c) *
d) % -
To wrap text inside a cell —
✅ a) Home > Wrap Text
b) Format > Text Wrap
c) Layout > Wrap
d) Review > Wrap -
Which feature allows prediction in Excel?
a) Trace Precedents
✅ b) What-If Analysis
c) Data Table
d) Goal Seek -
How to show formulas instead of results?
✅ a) Ctrl + ` (grave accent)
b) Ctrl + F1
c) Ctrl + Shift + F
d) Ctrl + Alt + =
🟩 31 to 50
-
Which tab is used to insert a Pivot Table?
a) Home
✅ b) Insert
c) Data
d) Review -
Default column width is —
a) 10.43
✅ b) 8.43
c) 9.0
d) 12.75 -
Which of these functions is logical?
a) SUM()
✅ b) IF()
c) AVERAGE()
d) COUNT() -
To add a comment —
a) Insert > Note
✅ b) Review > New Comment
c) Data > Comment
d) View > Comment -
To sort data ascending —
✅ a) A to Z
b) Z to A
c) High to Low
d) Group -
The fill handle is used for —
✅ a) Auto Fill
b) Format Cells
c) Sheet Protection
d) Validation -
Which tab contains the Name Manager?
a) Home
b) Insert
✅ c) Formulas
d) Data -
Which function is used for conditional result?
a) COUNT()
✅ b) IF()
c) VLOOKUP()
d) SUM() -
Function to find current time?
✅ a) =NOW()
b) =TIME()
c) =TODAY()
d) =CLOCK() -
To insert a hyperlink —
✅ a) Ctrl + K
b) Alt + H
c) Ctrl + H
d) Ctrl + L -
To delete selected row —
✅ a) Ctrl + – (minus)
b) Ctrl + Shift + D
c) Delete
d) Alt + R -
What is the formula for percentage?
✅ a) =(Part/Total)*100
b) =Part+Total
c) =Part-Total
d) =SUM(%) -
Which is not a data type in Excel?
✅ a) Picture
b) Date
c) Number
d) Text -
Keyboard shortcut to insert current date —
✅ a) Ctrl + ;
b) Alt + ;
c) Ctrl + Shift + D
d) Shift + D -
Which function removes extra spaces?
✅ a) TRIM()
b) CLEAN()
c) REMOVE()
d) FIX() -
To apply currency format —
✅ a) Ctrl + Shift + $
b) Ctrl + Alt + $
c) Alt + $
d) Shift + $ -
Formula to calculate square root —
✅ a) =SQRT(number)
b) =SQR(number)
c) =ROOT(number)
d) =POW(number,2) -
Which tab has Conditional Formatting?
a) Insert
✅ b) Home
c) View
d) Formulas -
To rename a worksheet —
✅ a) Double click the sheet tab
b) Right click > Move
c) File > Rename
d) View > Sheet Rename -
To zoom in/out —
✅ a) Ctrl + Mouse Scroll
b) Alt + +
c) Ctrl + Alt + Z
d) View > Change View -
What is the function of COUNTIF()?
✅ a) Counts cells meeting a condition
b) Adds cells
c) Checks validity
d) Compares sheets
