✅
UNIT 16: SPREADSHEET CONCEPTS
🔹
What is a Spreadsheet?
A software tool to organize, analyze, and calculate data using rows and
columns.
Common tools: MS Excel, Google Sheets, OpenOffice Calc.
16.1 Introduction
Used for home budgets, taxes, sales, accounting, and project reports.
Enables calculations using formulas, charts, and graphs.
16.2 Starting MS Excel
Start Menu → Microsoft Excel OR
Double-click the desktop icon
A new workbook opens as Book1 with 3 sheets by default.
16.3 Excel Screen Layout
Title Bar: Shows filename
Ribbon: Contains tabs (Home, Insert, Page Layout…)
Worksheet Area: Where data is entered
Formula Bar: Displays/edit formulas
Status Bar: Shows summary stats (Sum, Average)
16.4 Excel Menu
Key Tabs:
Home – Formatting tools
Insert – Charts, tables, images
Page Layout – Margins, orientation
Formulas – Insert functions
Data – Sort, filter, data tools
Review – Spelling, comments
View – Zoom, gridlines
16.5 Making Worksheets
16.5.1 Data Handling & Editing
Enter text, numbers, dates
Use copy/paste, undo, redo
16.5.2 Formatting
Change font, size, color
Use bold, italics, borders
16.5.3 Cell Comments
Right-click → Insert Comment
16.5.4 Naming Cells/Ranges
Select cell(s) → Name Box → Type name
16.5.5 Addressing
Relative: A1
Absolute: $A$1
Mixed: A$1 or $A1
16.6 Organizing Charts and Graphs
Use Insert → Charts
Choose chart type (Bar, Line, Pie)
16.7 Project Involving Multiple Sheets
Naming, inserting, deleting, hiding sheets
Group/Ungroup/Reposition sheets
Link data between sheets
Protect workbook
Save file regularly
16.8 Printing Worksheets
File → Print → Choose printer & settings
16.9 Using Excel Help
Press F1 or use Help Tab
✅
UNIT 17: FORMULAS AND FUNCTIONS
17.1 Introduction
MS Excel offers powerful formulas and functions to calculate financial,
statistical, and logical problems.
17.2 Formulas
Begin with =
Example: =A1+B1
17.2.1 Constructing Formulas
Use +, -, *, /, ^ operators
Combine with cell references
17.2.2 Array Formulas
Use Ctrl + Shift + Enter
Perform calculations on multiple cells simultaneously
17.3 Functions
17.3.1 Inserting Functions
Go to Formulas tab → Choose a function
17.3.2 Built-in Functions
Categories: Math, Logical, Financial, Text, Date & Time
17.4 Mathematical Functions
SUM(), ROUND(), ABS(), MOD(), INT()
17.5 Statistical Functions
AVERAGE(), COUNT(), MAX(), MIN()
17.6 Financial Functions
PMT(), NPV(), IRR(), FV()
Used for loan, investment, and interest calculations
17.7 Logical Functions
IF(), AND(), OR(), NOT()
Used for decision-making
17.8 Text & Formatting Functions
LEFT(), RIGHT(), LEN(), CONCATENATE(), CLEAN()
17.9 Date and Time Functions
TODAY(), NOW(), DATE(), TIME(), DATEDIF()
✅
UNIT 18: GRAPHS, CHARTS & PIVOT TABLES
18.1 Introduction
Charts help visualize data trends.
Excel supports a variety of chart types.
18.2 Charts and Its Types
1.
Column Chart
2. Line Chart
3. Pie Chart
4. Bar Chart
5. Area Chart
6. XY Scatter Chart
7. Stock Chart
18.3 Preparing Your Data
Ensure clean, structured data
No empty rows/columns
18.4 Transforming Data into Charts
Insert → Charts → Choose chart type
Customize title, axis, legends
18.5 Cross Tabulation & Pivot Charts
Pivot Table = summary table
Steps:
Insert → Pivot Table → Drag & Drop fields
Pivot Chart: Graphical representation of Pivot Table
✅
UNIT 19: DATA ANALYSIS USING EXCEL
19.1 Introduction
Excel can sort, filter, and analyze data for business decisions.
19.2 Sorting Data
Sort A-Z or Z-A using Home → Sort & Filter
19.3 Filtering Data
Filter by criteria using drop-down menus
19.4 Searching Data
19.4.1 Lookup Functions:
VLOOKUP(), HLOOKUP(), LOOKUP()
19.4.2 Referencing
Use cell referencing to refer data in formulas
19.5 Frequency Distribution (Array Formula)
=FREQUENCY(data_array, bins_array)
Requires Ctrl + Shift + Enter
19.6 Data Analysis ToolPak
Enable from Add-ins
Use for advanced statistics (Regression, t-test, Anova)
19.7 Descriptive Statistics
Mean, Median, Mode, SD, Variance using ToolPak
19.8 Correlation & Regression
Correlation: Measures relation between two variables
Regression: Predicts values using a line of best fit
19.9 Hypothesis Testing
t-Test, z-Test, F-Test for drawing conclusions from sample data
✅
UNIT 20: BUSINESS APPLICATIONS OF EXCEL
20.1 Introduction
Excel is widely used for real-world reporting:
Payroll, Loans, Budgets, Depreciation, Project Management
20.2 Loan & Lease Statements
Use PMT(), FV(), IPMT() to calculate EMI, interest, and balance
20.3 Ratio Analysis
Analyze financial health using ratios:
Current Ratio, Debt-Equity, Gross Profit Margin
20.4 Payroll Statements
Fields: Name, Salary, Deductions, Net Pay
Use functions like SUM(), IF() for calculations
20.5 Capital Budgeting
Techniques: NPV, IRR, Payback Period
Excel functions: NPV(), IRR(), PV()
20.6 Depreciation Accounting
Excel functions: SLN(), DB(), DDB()
Used to calculate asset depreciation over time
