Paid Cheatsheet for Accenture by Techie CodeBuddy

(YouTube Channel: Click Me)

What is MS Excel?
Microsoft Excel is a spreadsheet program developed by Microsoft, used to store,
organize, calculate, analyze, and visualize data using rows and columns. It allows
you to perform mathematical functions, create charts, and automate tasks using
formulas and functions.
It is widely used for:
• Data analysis
• Financial modeling
• Attendance sheets
• Sales & inventory reports
• Project tracking
• Time management, etc.

MS Excel Versions:

Year Version Key Notes

1985 Excel 1.0 (Mac Only) First Release
1987 Excel 2.0 (Windows) First Windows Version
1990 Excel 3.0 Introduced toolbars & 3D charts
1992 Excel 4.0 Enhanced functions
1993 Excel 5.0 Introduced VBA & Macros
1995 Excel 95 32-bit support
1997 Excel 97 IntelliSense, better UI
2000 Excel 2000 Web integration
2003 Excel 2003 Data recovery, XML support
2007 Excel 2007 Ribbon interface, .xlsx format
2010 Excel 2010 Sparklines, slicers
2013 Excel 2013 Flash Fill, new chart tools
2016 Excel 2016 More functions, improved sharing
2019 Excel 2019 New chart types, better PowerQuery
2021 Excel 2021 Dynamic arrays, LET & XMATCH
2024 Excel (365 latest) AI integration, live collaboration
File Extensions in Excel:
• .xls → Used in Excel 97–2003
• .xlsx → Default since Excel 2007
• .xlsm → Macro-enabled workbook
• .csv → Comma Separated Values (text format for data exchange)

Main Tabs in Excel Ribbon (Latest Version – Microsoft 365 / Excel 2021)

1. Home Tab

The default tab.

Used for:
• Formatting cells
• Copy-paste options
• Font and alignment
• Number formatting
• Insert/delete rows or columns
• Cell styles and basic functions (AutoSum)

2. Insert Tab

Used to insert:
• Tables, charts, pivot tables
• Pictures, shapes, icons
• Sparklines, slicers, maps
• Add-ins and text boxes
 3. Draw Tab (if enabled)

• Provides drawing tools: pens, highlighters

• Useful for touch-screen or stylus users
• Ink to Shape / Ink to Math

4. Page Layout Tab

Controls how your sheet looks when printed:

• Margins, orientation, size
• Print area
• Background
• Gridlines and headings visibility

5. Formulas Tab

All formula-related features:

• Function Library (SUM, AVERAGE, IF, etc.)
• Named Ranges
• Formula auditing tools (trace precedents, error checking)
• Calculation options (manual/auto)

6. Data Tab

For data import, analysis, and cleaning:

• Sort & filter
• Data tools (Text to Columns, Remove Duplicates)
• Data validation
• Get & Transform (Power Query)
 7. Review Tab

Used for:
• Spell check
• Comments/notes
• Workbook protection (passwords)
• Sharing and tracking changes

8. View Tab

Controls how the worksheet is displayed:

• Normal/Page Layout/Page Break views
• Zoom in/out
• Gridlines, headings, formula bar toggle
• Freeze panes & split window
• New Window & Arrange All

9. Help Tab
Access support & feedback options:
• Help articles
• Contact support
• Show training and tips
• What's new in Excel

10. File Tab (Backstage View)

Though not part of the ribbon exactly, it's crucial:
• Save / Save As / Open / Print / Share
• Account & settings
 • Export or publish
• Recent documents

11. Developer Tab (optional, needs to be enabled)

• Used by advanced users to:

o Write VBA macros
o Insert form controls (buttons, checkboxes)
o Record and edit macros
o Access XML commands

12. Power Pivot / Add-Ins Tabs (optional)

• Show up when specific Add-ins like Power Pivot or Power Map are
installed
• Used for data modeling, creating measures, or using 3D maps

All Tabs in Excel – Features & Description

1. Home Tab

Most-used tab for basic operations like formatting, copy-paste, and calculations.
• Clipboard: Cut, Copy, Paste & Format Painter for quick formatting transfer.
• Font: Change font type, size, bold/italic/underline, cell color, border.
• Alignment: Align text left/right/center, wrap text, merge cells, rotate text.
• Number: Format numbers as currency, percent, date, etc.
• Styles: Apply predefined table/cell styles, use conditional formatting.
• Cells: Insert/delete rows/columns/cells, resize them.
• Editing: Autosum, Fill series, Clear content, Sort, Find & Replace
 2. Insert Tab

Used to insert tables, charts, images, and interactive objects.

• Tables: Insert Excel tables or PivotTables for data analysis.
• Illustrations: Add shapes, pictures, SmartArt, icons to worksheets.
• Add-ins: Use external tools for advanced features.
• Charts: Add bar, pie, line, scatter, area charts to visualize data.
• Sparklines: Mini charts within a cell to show trends.
• Filters (Slicer/Timeline): Visually filter PivotTables.
• Text: Insert text boxes, headers, footers, WordArt, signatures.

3. Draw Tab

Useful for touchscreen or stylus drawing and annotations.

• Drawing Tools: Use pen, highlighter, pencil to mark freely on the sheet.
• Ink to Shape/Text: Convert hand-drawn content into clean shapes or text.

4. Page Layout Tab

Controls how your sheet looks on paper or when printing.
• Themes: Change overall color, font, and effect scheme.
• Page Setup: Set margins, page size, orientation, background image.
• Scale to Fit: Shrink or expand content to fit page size when printing.
• Sheet Options: Show/hide gridlines and row/column headings.
• Arrange: Align, group, rotate or layer images/shapes in the sheet.
 5. Formulas Tab

Contains built-in formulas and tools to manage calculations.

• Function Library: Use built-in formulas (SUM, IF, VLOOKUP, etc.).
• Defined Names: Assign names to cell ranges for easier use in formulas.
• Formula Auditing: Trace errors or dependent/precedent cells.
• Calculation: Set workbook to auto/manual calculation mode.

6. Data Tab
Used to import, clean, sort, and analyze large datasets.
• Get & Transform: Import data from external sources (web, CSV, DB).
• Queries & Connections: Manage existing linked datasets.
• Sort & Filter: Organize data in ascending/descending order, apply filters.
• Data Tools: Text to Columns, Remove Duplicates, Data Validation, Flash
Fill.
• Forecast: Use Goal Seek, Scenario Manager, Data Tables for projections.
• Outline: Group/ungroup rows or use Subtotals for structured viewing.

7. Review Tab

For proofreading, commenting, and protecting Excel sheets.

• Proofing: Check spelling, use thesaurus.
• Language: Translate or set language preferences.
• Comments: Add, edit, or reply to comments on cells.
• Protect: Password-protect sheet or workbook to restrict editing.
 8. View Tab

Customizes how the sheet appears on your screen.

• Workbook Views: Normal, Page Layout, Page Break Preview.
• Show: Show/hide gridlines, headings, formula bar.
• Zoom: Zoom in/out or fit sheet to screen.
• Window: View multiple sheets/windows side-by-side, freeze panes.
• Macros: Record or run VBA-based macros.

9. Help Tab
Offers support, feedback, and learning resources.
• Help: Access Excel help articles and training tips.
• Feedback: Report problems or share suggestions with Microsoft.

10. File Tab (Backstage View)

Handles file-level actions like saving, printing, exporting.
• Save, Save As, Open, Print, Share, Export, and manage document settings.
• Also shows recent files and lets you adjust account or Excel options.

11. Developer Tab (Needs to be enabled manually)

Used by advanced users for automation and form controls.

• Code: Create/edit macros using Visual Basic (VBA).
• Controls: Insert buttons, checkboxes, dropdowns into the sheet.
• Add-ins & XML: Manage external tools or XML data interactions.
1. Mathematical Functions:

Function Purpose

=SUM() Adds values

=PRODUCT() Multiplies values

=ROUND() Rounds number

=ROUNDUP() Always rounds up

=ROUNDDOWN() Always rounds down

=MOD() Returns remainder

=INT() Removes decimal (floor)

=ABS() Absolute value

=POWER() Raises to a power

=SQRT() Square root

2. Statistical Functions

Function Purpose

=AVERAGE() Mean value

=MEDIAN() Middle value

=[Link]() Most repeated value

=MAX() Largest value

=MIN() Smallest value

=STDEV.S() Standard deviation

=VAR.S() Sample variance

 Function Purpose

=COUNT() Counts numeric cells

=COUNTA() Counts non-empty cells

=COUNTBLANK() Counts blank cells

3. Logical Functions

Function Purpose

=IF() Basic condition check

=IFERROR() Handles error conditions

=AND() TRUE if all conditions are true

=OR() TRUE if any one condition is true

=NOT() Reverses the logic

=IFS() Multiple conditions (Excel 2019+)

=SWITCH() Selects value based on match

=TRUE() Returns TRUE

=FALSE() Returns FALSE

=XOR() Exclusive OR (TRUE if either but not both)

4. Text Functions

Function Purpose

=CONCATENATE() / =TEXTJOIN() Joins text

=LEFT() Extracts left characters

=RIGHT() Extracts right characters

=MID() Extracts from middle

=LEN() Number of characters

=TRIM() Removes extra spaces

=UPPER() All caps

=LOWER() All small

=PROPER() First letter capital

=REPLACE() Replaces text in a string

5. Date & Time Functions

Function Purpose

=TODAY() Returns today’s date

=NOW() Returns current date & time

=DAY() Extracts day

=MONTH() Extracts month

=YEAR() Extracts year

=HOUR() Extracts hour

=MINUTE() Extracts minutes

Function Purpose

=SECOND() Extracts seconds

=DATE() Creates a date from day, month, year

=DATEDIF() Difference between two dates

6. Lookup & Reference Functions

Function Purpose

=VLOOKUP() Vertical search

=HLOOKUP() Horizontal search

=INDEX() Value at position

=MATCH() Position of a value

=CHOOSE() Selects value from list

=LOOKUP() Older version of search

=XLOOKUP() New & powerful lookup

=OFFSET() Reference offset

=INDIRECT() Converts text to reference

=ROW() / =COLUMN() Returns row/column number

7. Information Functions

Function Purpose

=ISBLANK() Checks if cell is blank

=ISNUMBER() Is the cell a number

 Function Purpose

=ISTEXT() Is the cell a text

=ISERROR() Any error

=ISERR() Any error except #N/A

=ISNA() Checks #N/A

=ISLOGICAL() Checks TRUE/FALSE

=TYPE() Type of data

=[Link]() Returns error code

=N() Converts value to number

8. Financial Functions

Function Purpose

=PMT() Loan payment (EMI)

=FV() Future value

=PV() Present value

=NPV() Net present value

=RATE() Interest rate

=IRR() Internal Rate of Return

=DURATION() Bond duration

Double declining
=DDB()
depreciation

=SLN() Straight line depreciation

9. Array & Dynamic Array Functions

Function Purpose

=TRANSPOSE() Switch rows and columns

=FREQUENCY() Frequency distribution

=SEQUENCE() Generates sequence of numbers

=SORT() Sort array

=SORTBY() Sort by another array

=UNIQUE() Removes duplicates

=FILTER() Filter array based on criteria

=SINGLE() Returns a single value

=BYCOL() / =BYROW() Apply function by column or row

=ARRAYFORMULA() (Google Sheets) Apply to ranges

Top Priority – Must Know (Used Daily & in MCQs)

Shortcut Function

Ctrl + C Copy selected cells

Ctrl + V Paste copied content

Ctrl + X Cut selected cells

Ctrl + Z Undo last action

Ctrl + Y Redo last undone action

Ctrl + S Save workbook

Select entire sheet (or data

Ctrl + A
region)
Shortcut Function

Ctrl + P Print worksheet

Ctrl + F Find

Ctrl + H Replace

Ctrl + Arrow Keys Jump to edge of data region

Ctrl + Shift + Arrow

Select data in that direction
Keys

Ctrl + Space Select entire column

Shift + Space Select entire row

Paste Special (sequentially

Alt + E + S + V
press)

Ctrl + Shift + L Toggle filter (for tables/data)

Moderate Priority – Helpful in Data Entry, Formatting

Shortcut Function

Ctrl + B Bold text

Ctrl + I Italic

Ctrl + U Underline

Alt + Enter Add new line within a cell

Ctrl + 1 Open Format Cells dialog

F2 Edit selected cell

Ctrl + ; Insert current date

Ctrl + : Insert current time

Shortcut Function

Alt + = AutoSum

Ctrl + Shift + "+" Insert a new row or column

Delete selected row or

Ctrl + "-"
column

Create Table from selected

Ctrl + T
data

Ctrl + ` (grave accent) Toggle show formulas

Very Rare / Advanced Usage

Shortcut Function

Ctrl + K Insert hyperlink

Ctrl + F3 Open Name Manager

Alt + F8 Run macro

Alt + F11 Open VBA editor

Ctrl + Shift + U Expand/collapse formula bar

Ctrl + 9 Hide selected rows

Ctrl + 0 Hide selected columns

10 Important Points to Remember – MS Excel

Excel is a Spreadsheet Application

• Used for data entry, analysis, calculations, and visualizations.

• Part of the Microsoft Office Suite.

Worksheets & Workbooks

• A Workbook = complete Excel file (with .xlsx extension).

• A Worksheet = single spreadsheet within the workbook.

Rows, Columns & Cells

• Rows: Horizontal (numbered 1, 2, 3…)
• Columns: Vertical (labeled A, B, C…)
• Cell: Intersection of a row and column (e.g., A1)

Formula Basics
• Every formula starts with an equal sign (=)
• Example: =SUM(A1:A5)

Functions Categories

• Excel offers 400+ built-in functions grouped as:

→ Math (SUM, AVERAGE)
→ Logical (IF, AND, OR)
→ Text (LEFT, RIGHT, LEN)
→ Date/Time (TODAY, NOW)
→ Lookup (VLOOKUP, INDEX, MATCH)
Cell Referencing Types

• Relative (A1): Changes as copied

• Absolute ($A$1): Fixed cell
• Mixed (A$1 or $A1): One fixed, one flexible

Data Analysis Tools

• Use PivotTables, Filters, and Conditional Formatting for advanced data

insights.

Charts & Visuals

• Create Pie, Bar, Line, and Column Charts to present data visually via the
Insert tab.

Excel Shortcuts Boost Speed

• Learn Ctrl + C, Ctrl + V, Ctrl + Z, Ctrl + A, Ctrl + Shift + L, etc. to work faster.

Common File Extensions

• .xlsx – Default Excel Workbook
• .xls – Older version (Excel 97–2003)
• .xlsm – Macro-enabled workbook
• .csv – Comma Separated Values (plain data)
Formula Based Problem:
Different Tabs in Excel
File Tab in Excel – Overview (Backstage View)
The File Tab opens the Backstage View, which deals with file-level operations,
not worksheet content. This is where you manage saving, printing, sharing,
exporting, and settings.

1. Info

• Shows file properties, like file size, author, last modified.

• Options to protect the workbook (with password), inspect for issues,
manage versions.
2. New

• Create a new workbook.

• Choose from blank workbook or available templates (like calendars,
invoices, budgets).

3. Open

• Open recent files, folders, or browse any saved Excel file on your PC or
OneDrive.
• Also allows recovering unsaved workbooks.

4. Save / Save As
• Save: Quickly saves the file if it's already named.
• Save As: Lets you rename the file, change location, or file format (like
.xlsx, .csv, .xlsm).

5. Print
• Shows a print preview of your worksheet.
• Choose printer, page orientation, paper size, margins, and number of
copies.

6. Share
• Share the workbook through email, link, or upload to OneDrive.
• You can also invite others to collaborate in real-time.
 7. Export

• Convert your Excel file into other formats like:

o PDF
o XPS
o Change file type (e.g. CSV, older Excel versions)

8. Close

• Closes the current workbook without exiting Excel application.

9. Account
• Manage your Microsoft account, sign in/out, change theme, and update
license info.

10. Options
• Opens Excel Options window where you can customize:
o Formula settings
o Proofing tools
o AutoSave settings
o Customize ribbon & shortcuts

Summary:

The File Tab is not used for editing data but for managing the workbook itself —
like saving, printing, sharing, and settings.
 Home Tab in MS Excel – Overview

The Home Tab is the most frequently used tab in Excel. It contains basic
commands for editing, formatting, and organizing data. You’ll use it in almost
every Excel task!
The Home Tab is divided into these main groups:

Clipboard

• Cut , Copy , Paste: Basic copy-paste functions.

• Format Painter: Copies formatting from one cell and applies it to another.

Font

• Change font type, size, make text Bold / Italic / Underline.

• Add cell fill color, font color, and borders to highlight data.

Alignment
• Align content left / center / right.
• Merge & Center: Combines multiple cells into one and centers the
content.
• Wrap Text: Keeps all text visible inside a cell by wrapping it.

Number

• Set number formatting: Currency, Percentage, Date, Decimal places.

• Use Increase/Decrease Decimal buttons to show exact values.
 Styles

• Apply Conditional Formatting: Automatically color cells based on value

(e.g., red for less than 50).
• Use Table Styles and Cell Styles for quick formatting.

Cells

• Insert: Add new rows, columns, or cells.

• Delete: Remove selected rows, columns, or cells.
• Format: Set row height, column width, cell protection, and more.

Editing
• AutoSum: Quickly add numbers.
• Use Fill, Clear, Sort & Filter, and Find & Replace features.

Summary:

The Home Tab is your main workspace for formatting, organizing, and
calculating. It’s a core tab for every Excel user!

Insert Tab in MS Excel – Overview

The Insert Tab lets you add new elements into your worksheet — such as tables,
charts, pictures, links, symbols, and more. It’s mostly used for data visualization
and enhancement.

Main Groups in the Insert Tab:

Tables
• Table: Converts a range of cells into a dynamic Excel table with built-in
filters and styles.
• PivotTable: A powerful tool to summarize, analyze, and explore large data
sets.

Illustrations

• Pictures: Insert images from your computer.

• Online Pictures: Add pictures from the web (via Bing image search).
• Shapes: Add arrows, rectangles, circles, and other drawing shapes.
• Icons & 3D Models: Add visuals from Microsoft’s built-in library.
• SmartArt: Create diagrams like processes, hierarchies, cycles, etc.

Add-ins
• Insert third-party or Microsoft add-ins to enhance Excel functionality.

Charts

• Insert Column, Line, Pie, Bar, Area, Scatter, Combo charts to visualize
your data.
• Excel will auto-suggest charts based on selected data.

Sparklines

• Mini charts that fit inside a single cell.

• Great for showing data trends (Line, Column, or Win/Loss format).

Filters

• Slicer: Add visual filters to pivot tables or tables.

• Timeline: Add a time-based filter to your PivotTable.
 Links

• Insert a Hyperlink to connect to a web page, another worksheet, or file.

Text

• Text Box: Add floating text anywhere on the sheet.

• Header & Footer: Insert page-level information for printing.
• WordArt: Add decorative stylized text.
• Signature Line, Object, Equation, Symbols: Add professional or
technical content.

Summary:

The Insert Tab allows you to enhance your sheet with visuals, tables, charts,
and objects — making your data more interactive and presentation-ready.

Draw Tab in MS Excel – Overview

The Draw Tab lets you use drawing tools to mark, highlight, sketch, or annotate
directly on your Excel sheet using your mouse, finger (touchscreen), or stylus
(like Surface Pen).
It’s mostly useful for visual explanation, digital inking, and handwritten
annotations.

Key Tools/Groups in the Draw Tab:

Draw Tools (Pens Section)

• Pen / Pencil / Highlighter:

Choose from multiple drawing tools.
o Pen = fine lines
o Pencil = sketch-style drawing
o Highlighter = transparent color for emphasis
• You can select color, thickness, and customize your pen styles.
 Ink to Shape

• When you draw a shape (like a circle, square), Excel auto-converts it to a

perfect shape.
• Useful when sketching flowcharts or diagrams.

Ink to Math

• Converts your handwritten math equations into typed equations.

• Great for technical users or handwritten formula conversion.

Lasso Select

• Used to select inked (drawn) content by drawing a loop around it —

helpful for editing or moving drawn items.

Erase
• Remove ink strokes using Stroke Eraser or Object Eraser tools.

Action Pen
• In touchscreen devices, this pen lets you write and select at the same
time, acting like a hybrid tool.

Summary:

The Draw Tab is ideal for handwritten annotations, sketches, highlighting, and
working naturally with ink — especially on touch-enabled devices.
 Page Layout Tab in MS Excel – Overview

The Page Layout Tab controls how your worksheet looks on the screen and when
printed. It helps adjust page size, margins, orientation, background, and sheet
settings.

Main Groups & Features in Page Layout:

Themes
• Choose a predefined theme for your sheet (fonts, colors, and effects).
• Includes:
o Themes
o Colors
o Fonts
o Effects
• Great for maintaining a consistent and professional design.

Page Setup
Controls the printed appearance of your worksheet.
• Margins: Set space between sheet content and page edge (Normal,
Narrow, Wide, or custom).
• Orientation: Choose between Portrait (vertical) or Landscape
(horizontal).
• Size: Select paper size like A4, A3, Letter, etc.
• Print Area: Define which part of the sheet will be printed.
• Breaks: Insert or remove page breaks.
• Background: Add a background image to your sheet.
• Print Titles: Repeat row/column headers on every printed page.
 Scale to Fit

Used to adjust worksheet to fit on a page while printing.

• Width: Shrinks or stretches the content to fit within page width.
• Height: Shrinks or stretches content to fit page height.
• Scale: Manually set percentage size (e.g., 80%).

Sheet Options

Controls gridlines and headings on the worksheet and while printing.

• Gridlines:
o View: Show/hide gridlines on screen
o Print: Show/hide gridlines on printed page
• Headings:
o View: Show/hide row/column headings (A, B, C… and 1, 2, 3…)
o Print: Print these headers

Arrange (mostly for visuals)

• Useful when working with charts, images, or shapes.
• Allows to bring forward/backward, align, group, or rotate objects.

Summary:

The Page Layout Tab helps you control how your worksheet appears on paper —
managing margins, orientation, size, titles, and print settings.
 Formulas Tab in MS Excel – Overview

The Formulas Tab is where you manage and insert functions, formulas, named
ranges, and formula auditing tools. It’s the center of all calculations and logic in
Excel.

Main Groups & Features in the Formulas Tab:

Function Library
This section is like Excel's formula bank. It includes:
• AutoSum: Quickly calculates totals for selected cells (SUM, AVERAGE,
COUNT, MIN, MAX).
• Recently Used: Shows functions you've used recently.
• Financial: Functions like PMT, FV, NPV, used for loan, EMI, finance
calculations.
• Logical: Includes IF, AND, OR, NOT for conditional logic.
• Text: Functions like LEFT, RIGHT, LEN, CONCATENATE, etc.
• Date & Time: Use TODAY(), NOW(), DATE(), etc.
• Lookup & Reference: VLOOKUP, HLOOKUP, INDEX, MATCH, etc.
• Math & Trig: Common math functions like ROUND, ABS, SUMPRODUCT.
• More Functions:
o Statistical: Like AVERAGEIF, MEDIAN, STDEV.S
o Engineering, Cube, Information, Compatibility

Defined Names
• Name Manager: View and edit all named ranges in your sheet.
• Define Name: Create custom names for cells or ranges (e.g., name
A1:A10 as "Sales").
• Use in Formula: Insert defined names into formulas.
• Create from Selection: Auto-name ranges using row or column labels.
 Formula Auditing

Helps track and debug formulas.

• Trace Precedents: Show which cells affect the selected cell.
• Trace Dependents: Show which cells depend on the selected cell.
• Remove Arrows: Clear the trace arrows.
• Show Formulas: Toggle between seeing values and formulas in cells.
• Error Checking: Finds and explains formula errors.
• Evaluate Formula: Step-by-step breakdown of how Excel is calculating a
formula.
• Watch Window: Keep an eye on important cells even when working far
away in the sheet.

Calculation
• Calculate Now / Calculate Sheet: Manually force recalculation.
• Calculation Options: Choose between:
o Automatic (default): Excel recalculates when data changes.
o Manual: You choose when to recalculate.

Summary:

The Formulas Tab is the brain of Excel — where you access built-in functions,
name your data, and debug formulas step-by-step.
✓ Data Tab in MS Excel – Overview
The Data Tab helps you manage, sort, filter, connect, clean, and analyze data
from your worksheet or external sources like text files, web, or databases.
It is very important in real-world Excel usage, especially in data handling and
business reports.

Main Groups & Features in the Data Tab:

Get & Transform Data (Power Query)

• Get Data: Import data from external sources (Excel, text, web, database,
etc.).
• Recent Sources: Shows recently used external data sources.
• Existing Connections: Use previously created data connections.
• Used in Power Query and for data automation.

Queries & Connections

• Manage and refresh your Power Queries or data connections.
• View details like source, status, and number of rows.

Sort & Filter

• Sort A-Z / Z-A: Sort data in ascending or descending order.

• Custom Sort: Sort based on multiple levels (e.g., by name then by city).
• Filter: Add dropdown filters to your headers to show only what you want.
• Clear: Remove all filters applied.
 Data Tools

• Text to Columns: Split data in a single cell into multiple columns (useful
for separating names, CSV values).
• Remove Duplicates: Deletes duplicate entries in the selected range.
• Data Validation: Restrict data input (e.g., allow only numbers, dates,
dropdowns).
• Consolidate: Combines data from different ranges using functions like
SUM, AVERAGE.
• Flash Fill: Automatically fills in values by detecting patterns (like
separating first and last names).

Forecast (or Forecast Sheet group)

• What-If Analysis: Try different scenarios using:
o Goal Seek
o Scenario Manager
o Data Table
• Forecast Sheet: Create a forecast chart based on historical data.

Outline

• Group / Ungroup: Collapse or expand rows/columns to organize data

hierarchically.
• Subtotal: Automatically inserts subtotal rows based on groups.

Summary:

The Data Tab is used to import, clean, organize, sort, and analyze data. It’s super
useful when dealing with large datasets or reports.
Excel Topics You Might Not Have Covered Yet (But Are Useful for MCQs & Practice)

1. Freeze Panes (View Tab)

• Locks specific rows/columns in view while scrolling.
• Example: Freeze top row to always see column headers.

2. Split Window (View Tab)

• Splits the sheet into multiple scrollable panes.

• Helpful when comparing two parts of the same sheet.

3. Zoom Control (View Tab)

• Used to zoom in/out for better visibility.
• Zoom to Selection adjusts view to selected cells only.

4. Protect Sheet vs Protect Workbook

• Protect Sheet: Locks specific cells from editing.

• Protect Workbook: Prevents changes like adding/deleting sheets.
• Can be done from Review Tab.

5. Track Changes (Review Tab)

• Used in collaboration to track what edits were made by whom.

• Now called "Show Changes" in latest Excel 365 versions.

6. Workbook Views
• Normal, Page Break Preview, Page Layout View — under View tab.
• Useful for printing and page setting previews.
7. Goal Seek & Scenario Manager (Data > What-If Analysis)

• Goal Seek: Find input value needed to reach a target result.

• Scenario Manager: Save multiple versions of inputs to compare
outcomes.

8. Remove Duplicates (Data Tools)

• Often used in data cleaning.

• Quick and important for MCQs!

9. Flash Fill
• Automatically fills in values when a pattern is recognized.
• Example: Split first name from full name typed earlier.

10. Quick Access Toolbar

• The small toolbar at the top-left corner of Excel window.

• Can be customized to include shortcuts for frequently used commands.

11. Status Bar Insights

• Located at the bottom of Excel.

• Shows quick stats: Average, Count, Sum when cells are selected.
• Often missed but useful in practical questions.
 12. Shortcut Keys Related to Navigation

• Ctrl + Arrow Keys: Jump to edges of data

• Ctrl + Shift + L: Toggle filters
• Ctrl + Space / Shift + Space: Select column/row

Microsoft Excel – 50 MCQs for Placement Exams

MCQs 1–10: Basics & Interface

1. What is the default file extension of a saved Excel workbook in modern

versions?
a) .xls
b) .xlsx
c) .docx
d) .pdf
2. Which tab is used to insert charts, tables, and SmartArt in Excel?
a) Home
b) Review
c) Insert
d) View
3. The horizontal bar above the worksheet that displays formulas is called:
a) Name Box
b) Formula Box
c) Formula Bar
d) Function Panel
4. Which command is used to repeat the last action in Excel?
a) Ctrl + C
b) Ctrl + R
c) F4
d) Ctrl + Z
5. Where can you find the "Text to Columns" feature?
a) Home Tab
b) Insert Tab
c) Data Tab
d) View Tab
6. The intersection of a row and column is called:
a) Box
b) Grid
c) Cell
d) Table
7. Which feature automatically fills values based on a detected pattern?
a) AutoFill
b) Flash Fill
c) Quick Fill
d) Smart Fill
8. The vertical group of cells is known as a:
a) Row
b) Column
c) Range
d) Sheet
9. Which tab allows you to set margins and page orientation for printing?
a) File
b) Page Layout
c) Data
d) Insert
10. To freeze the top row of your Excel sheet, you go to:
a) Review > Freeze
b) Home > View
c) View > Freeze Panes
d) Data > Freeze Panes
MCQs 11–20: Formulas & Functions

11. What function is used to add a range of cells?

a) ADD()
b) SUM()
c) TOTAL()
d) COUNT()
12. Which function checks if a condition is true or false?
a) TEST()
b) CHECK()
c) IF()
d) TRUE()
13. What is the correct formula to find the average of cells A1 to A5?
a) =AVG(A1:A5)
b) =AVERAGE(A1:A5)
c) =MEAN(A1:A5)
d) =ADD(A1:A5)/5
14. Which function will return the current date?
a) TODAY()
b) DATE()
c) NOW()
d) CURRENT()
15. Which lookup function searches vertically in a table?
a) HLOOKUP
b) INDEX
c) VLOOKUP
d) FIND
16. Which function removes extra spaces from text?
a) TRIM()
b) CLEAN()
c) REMOVE()
d) SPACE()
17. Which function joins two text values?
a) CONCAT()
b) TEXTJOIN()
c) CONCATENATE()
d) All of the above
18. Which formula gives you the number of characters in a cell?
a) LENGTH()
b) SIZE()
c) LEN()
d) CHARCOUNT()
19. What does the IF formula =IF(A1>50, "Pass", "Fail") return if A1 =
40?
a) Pass
b) Fail
c) 40
d) Error
20. What is the formula to calculate compound interest using financial
functions?
a) CI()
b) FV()
c) PMT()
d) INT()

MCQs 21–30: Tabs & Commands

21. Where is "Remove Duplicates" located?

a) Review
b) Home
c) Data
d) Insert
22. "Conditional Formatting" can be found under which tab?
a) Data
b) Formulas
 c) Home
d) View
23. Which tab contains the "Name Manager" feature?
a) Data
b) Formulas
c) Developer
d) Page Layout
24. Which tab would you use to insert a slicer or timeline?
a) Data
b) Insert
c) Formulas
d) Page Layout
25. Where do you define a new theme or change font sets for your
sheet?
a) Home
b) Page Layout
c) Design
d) View
26. Which feature allows you to see how Excel calculates a complex
formula step-by-step?
a) Trace Formula
b) Evaluate Formula
c) Audit Trail
d) Show Formula
27. What is the purpose of the Watch Window?
a) Monitor changes
b) View changes in worksheet appearance
c) Watch formula output from anywhere
d) None
28. What happens when you press Ctrl + ; (semicolon)?
a) Inserts current time
b) Inserts current date
 c) Inserts currency symbol
d) Inserts semicolon
29. What is the shortcut for "Insert a new worksheet"?
a) Shift + F11
b) Alt + N
c) Ctrl + Shift + N
d) Ctrl + F11
30. To insert a new comment in a cell, which shortcut is used?
a) Ctrl + Alt + C
b) Alt + R
c) Shift + F2
d) Ctrl + /

MCQs 31–40: Charts, Data Tools, Shortcuts

31. Where can you insert a line chart from?
a) Insert Tab
b) Data Tab
c) View Tab
d) Page Layout
32. What does the shortcut Ctrl + Shift + L do?
a) Locks cell
b) Adds hyperlink
c) Toggles filter
d) Left align
33. What does Flash Fill do?
a) Copies cell format
b) Auto-completes data based on pattern
c) Adds color automatically
d) None
34. Which tool splits a single column into multiple columns?
a) Flash Fill
b) Text to Columns
 c) Split Column
d) Unmerge
35. What is the keyboard shortcut to open Format Cells dialog box?
a) Ctrl + 1
b) Alt + F1
c) Shift + 1
d) Ctrl + F
36. Where would you find the “Group” feature to collapse rows?
a) Insert
b) View
c) Data
d) Page Layout
37. What happens when you press F2?
a) Opens Find box
b) Opens Formula bar
c) Edits active cell
d) Opens Ribbon
38. What does Ctrl + D do in Excel?
a) Deletes the row
b) Fills down
c) Duplicates sheet
d) Defines name
39. Which function helps you count cells that are not empty?
a) COUNT()
b) COUNTA()
c) COUNTBLANK()
d) SUM()
40. The default number of worksheets in a new Excel file is:
a) 1
b) 2
c) 3
d) 5
MCQs 41–50: Advanced & Practical Use

41. The ribbon is located:

a) At the bottom
b) Below the Formula Bar
c) At the top, below the title bar
d) Above the worksheet grid
42. Which view mode is used for preparing print layouts?
a) Normal
b) Page Break Preview
c) Page Layout
d) Full Screen
43. Which tool is used to reverse an incorrect action?
a) Ctrl + Y
b) Ctrl + Z
c) Ctrl + R
d) Ctrl + X
44. The status bar shows which quick values when you select cells?
a) Only Sum
b) Only Count
c) Sum, Count, Average
d) Nothing
45. Which of the following allows you to name a range?
a) Formula Bar
b) Name Box
c) Ribbon
d) Cell Format
46. What is the shortcut to insert the current time?
a) Ctrl + T
b) Ctrl + Shift + :
 c) Ctrl + ;
d) Alt + T
47. What is the maximum number of rows in Excel 2019?
a) 1,04,857
b) 1,00,000
c) 1,048,576
d) 10,48,576
48. How many columns are there in Excel 365?
a) 16,384
b) 1,048,576
c) 65,536
d) 10,000
49. Which chart is best for showing parts of a whole?
a) Bar Chart
b) Pie Chart
c) Line Chart
d) Column Chart
50. What feature should be used to make the first row repeat on each
printed page?
a) Page Breaks
b) Repeat Headers
c) Print Titles
d) Page Setup

Answer Sheet:
1. b 2. c 3. c 4. c 5. c 6. c 7. b 8. b 9. b 10. c
11. b 12. c 13. b 14. a 15. c 16. a 17. d 18. c 19. b 20. b
21. c 22. c 23. b 24. b 25. b 26. b 27. c 28. b 29. a 30. c
31. a 32. c 33. b 34. b 35. a 36. c 37. c 38. b 39. b 40. a
41. c 42. c 43. b 44. c 45. b 46. b 47. c 48. a 49. b 50. c