1

Unit-IV Spread sheet Basics

Q) Understanding rows, columns, cells in tools like MS Excel

In Excel, rows run horizontally and are numbered (1, 2, 3…), columns run vertically and
are lettered (A, B, C…), and a cell is the intersection of a row and column (like A1 or B2).
Cells are the basic units where you enter data.

Rows

Definition: Horizontal arrangement of cells.

Identification: Labelled with numbers (1, 2, 3…).
Limit: Excel supports 1,048,576 rows in a worksheet.
Selection: Click the row number or press Shift + Spacebar to select the entire row.
Size: Controlled by row height
Columns

 Definition: Vertical arrangement of cells.

 Identification: Labeled with letters (A, B, C… up to XFD).
 Limit: Excel supports 16,384 columns in a worksheet.
 Selection: Click the column letter or press Ctrl + Spacebar to select the entire
column.
 Size: Controlled by column width.
Cells

 Definition: The intersection of a row and a column.

 Addressing: Each cell has a unique reference (e.g., A1 = Column A, Row 1).
 Content: Can hold text, numbers, formulas, or formatting.
 Navigation: Use arrow keys or click directly to move between cells.

Example for Clarity:

Imagine a simple Excel sheet:

 Row 2 → Rahul, 25, Delhi

 Column B → Age values (25, 30)
 Cell C3 → "Mumbai"

Q) Explain Workbook and Worksheet

In Excel, a Workbook is the entire Excel file, while a Worksheet is a single page (or
tab) inside that workbook where you enter and organize data.

 Workbook
Definition: The complete Excel file (with extension .xlsx or .xls).

 Structure: Acts like a container that holds one or more worksheets.

 Analogy: Think of it as a notebook — the whole notebook is the workbook.
 Capacity: Can contain many worksheets (limited only by your computer’s memory).
 Usage: Useful for keeping related data together (e.g., sales, expenses, reports in one
file).

Example:
 2

 File name: [Link]

 Inside it: Worksheets like Sales, Inventory, Employees.

 Worksheet
 Definition: A single spreadsheet page inside a workbook.
 Structure: Made up of rows, columns, and cells.
 Analogy: Like a page in the notebook.
 Navigation: You switch between worksheets using the tabs at the bottom of Excel.
 Usage: Each worksheet can hold different datasets, calculations, or charts.
Example:
 Worksheet Sales → Contains monthly sales figures.
 Worksheet Expenses → Contains cost breakdowns.
 Worksheet Summary → Combines data from other sheets.

Q) Cell referencing- Relative, Absolute, Mixed

In Excel, Relative references adjust automatically when formulas are copied,

Absolute references stay fixed using $, and Mixed references lock either the row or
column but not both.

Types of Cell Referencing:

1. Relative Reference
 Format: Example → =A1 + B1
 Behavior: When copied, the reference changes relative to its new position.
 Use Case: Best for repeating formulas across rows/columns.
 Example: Copying =A1+B1 from Row 1 to Row 2 becomes =A2+B2.

2. Absolute Reference
 Format: Example → =$A$1 + $B$1
 Behavior: The reference never changes, no matter where the formula is copied.
 Use Case: Useful when you always want to refer to a fixed cell (like a tax rate or
constant).
 Example: Copying =$A$1+$B$1 anywhere still points to Row 1, Column A and B.

3. Mixed Reference
 Format: Example → =$A1 or =A$1
 Behavior: Locks either the row or the column:
o $A1 → Column A fixed, row changes.
o A$1 → Row 1 fixed, column changes.
 Use Case: Helpful in tables where one dimension (row or column) must remain
constant.
 Example: Copying =$A1 across rows keeps Column A fixed but row adjusts.
 3

Ch: Functions and Formulae

Q) Explain Mathematical, Statistical, Logical functions

In Excel, Mathematical functions perform calculations (like addition, rounding,

powers), Statistical functions analyse data (like averages, counts, distributions), and
Logical functions test conditions (like IF, AND, OR) to return results based on true/false
logic.

 Mathematical Functions
These are used for basic and advanced calculations.
 SUM(range): Adds numbers in a range.
 ROUND(number, digits): Rounds a number to specified decimal places.
 POWER(number, power): Raises a number to a power.
 SQRT(number): Returns the square root.
 ABS(number): Returns the absolute value (ignores sign).

 Statistical Functions
These help in data analysis and summarization.
 AVERAGE(range): Returns the mean of values.
 MEDIAN(range): Returns the middle value.
 MODE(range): Returns the most frequent value.
 COUNT(range): Counts numeric entries.
 MAX(range) / MIN(range): Finds the largest or smallest value.
 STDEV(range): Estimates standard deviation.

 Logical Functions
These are used for decision-making in formulas.
 IF(condition, value_if_true, value_if_false): Returns one value if condition is true,
another if false.
 AND(condition1, condition2,…): Returns TRUE if all conditions are true.
 OR(condition1, condition2,…): Returns TRUE if at least one condition is true.
 NOT(condition): Reverses the logical value (TRUE → FALSE, FALSE → TRUE).

Q) Explain Text, Date and Time, Financial functions

 Text Functions
 Used to manipulate, format, or analyze text data.
 They can extract parts of text, change case (uppercase/lowercase), count characters, or
join multiple text strings together.
 Helpful for cleaning data, preparing reports, or standardizing text entries.
Ex

 LEFT(text, num_chars): Returns leftmost characters.

Example: =LEFT("Excel",2) → "Ex"
 RIGHT(text, num_chars): Returns rightmost characters.
Example: =RIGHT("Excel",3) → "cel"
 LEN(text): Counts characters.
Example: =LEN("Excel") → 5
 4

 Date & Time Functions

 Designed to work with dates and times in calculations.
 They can return the current date or time, extract specific parts (day, month, year,
hour), or perform operations like adding/subtracting days.
 Useful for scheduling, tracking deadlines, and building time-based reports.
Ex:

 DAY(date): Extracts day.

Example: =DAY("23-Nov-2025") → 23
 MONTH(date): Extracts month.
Example: =MONTH("23-Nov-2025") → 11
 YEAR(date): Extracts year.
Example: =YEAR("23-Nov-2025") → 2025

 Financial Functions
 Focused on loan, investment, and financial calculations.
 They calculate payments, interest rates, future values, or net present values.
 Essential for budgeting, forecasting, and analysing financial scenarios.
Ex:

 RATE(nper, pmt, pv): Interest rate per period.

Example: =RATE(12*2,-500,20000) → Calculates interest rate for loan.

Q) Explain Lookup and Reference

Excel’s lookup and reference functions help you find and return data from tables.
VLOOKUP searches vertically, HLOOKUP searches horizontally, XLOOKUP is the modern
flexible replacement, while INDEX and MATCH combine to give powerful, customizable
lookups

🔹 VLOOKUP (Vertical Lookup)

 Searches for a value in the first column of a table.
 Returns a value from another column in the same row.
 Limitation: Can only look to the right of the lookup column.

🔹 HLOOKUP (Horizontal Lookup)

 Searches for a value in the first row of a table.
 Returns a value from another row in the same column.
 Limitation: Can only look downward from the lookup row.

🔹 XLOOKUP (Modern Lookup)

 Replaces both VLOOKUP and HLOOKUP.
 Can search both vertically and horizontally.
 Works in any direction (left, right, up, down).
 Returns exact matches by default and supports multiple outputs.
 More reliable and flexible than older lookup functions.

🔹 INDEX
 Returns the value of a cell at a given row and column position in a range.
 5

 By itself, it’s like pointing to a specific cell inside a table.

🔹 MATCH
 Returns the position of a value in a row or column.
 Often used with INDEX to make dynamic lookups.

🔹 INDEX + MATCH Combo

 MATCH finds the position of the lookup value.
 INDEX uses that position to return the actual value.
 More powerful than VLOOKUP because it can look left or right and is less error-prone.

Ch: Visual representations

Q) Define Chart? Explain creating a chart

A chart in MS Excel is a visual representation of data that helps you analyse,

compare, and communicate information more effectively than raw numbers

 Purpose: Charts transform rows and columns of data into graphs or visuals, making
complex information easier to understand.
 Location: Found in the Insert tab under the Charts group.

🧩 Steps to Create a Chart

1. Prepare your data
o Arrange data in rows or columns (e.g., months vs. sales).
o Ensure headings are present for clarity.
2. Select the data range
o Highlight the cells you want to visualize.
o Include labels (like months, categories) for better chart readability.
3. Go to the Insert tab
o On the Ribbon, click Insert.
o In the Charts group, you’ll see different chart options.
4. Choose a chart type
o Click on the chart icon (Column, Line, Pie, etc.).
o Excel will instantly create a chart based on your selection.
5. Customize the chart
o Use Chart Tools (Design & Format tabs) to:
o Add chart title, axis labels, and legends.
o Change colors, styles, or layout.
o Switch rows/columns if needed.
6. Move or resize the chart
o Drag the chart to a new position.
o Resize by pulling the corners.

Q) Explain chart types.

Excel offers many chart types such as Column, Bar, Line, Pie, Scatter,
Histogram. Each type is designed to visualize data in a specific way.

📊 Common Chart Types in Excel

 6

🔹 Column Chart
 Displays data as vertical bars.
 Best for comparing values across categories.
 Categories are shown on the horizontal axis, values on the
vertical axis.
 Easy to highlight differences between groups.
 Commonly used for sales, marks, or survey results.

🔹 Bar Chart
1. Similar to column chart but uses horizontal bars.
2. Useful when category names are long.
3. Categories appear on the vertical axis, values on the
horizontal axis.
4. Good for side-by-side comparisons.
5. Often used in ranking or comparison reports.

🔹 Line Chart
1. Shows trends over time.
2. Connects data points with lines.
3. Ideal for continuous data (e.g., monthly growth,
temperature).
4. Highlights patterns, rises, and falls clearly.
5. Common in financial, scientific, or progress tracking.

🔹 Pie Chart
1. Represents proportions of a whole.
2. Each slice shows percentage contribution.
3. Best for small datasets with few categories.
4. Easy to visualize market share or distribution.
5. Not suitable for large or complex data.

🔹 Scatter Chart
1. Plots data points on X and Y axes.
2. Shows relationships or correlations between variables.
3. Useful for scientific, statistical, or experimental data.
4. Can reveal clusters, trends, or outliers.
5. Often used in regression analysis or research.

🔹 Histogram
1. Displays frequency distribution of data.
2. Groups values into bins or intervals.
3. Shows how often data falls within ranges.
4. Helps analyze patterns in large datasets.
5. Common in statistics, quality control, and surveys.
 7

Q) What is Sorting?
Sorting in Excel means arranging data in a specific order — either alphabetically, numerically, or
by date — to make it easier to analyse and present.

🔹 Types of Sorting
1. Ascending Order
o Numbers → Smallest to Largest
o Text → A to Z
o Dates → Oldest to Newest
2. Descending Order
o Numbers → Largest to Smallest
o Text → Z to A
o Dates → Newest to Oldest
3. Custom Sort
o Sort by multiple columns (e.g., first by Department, then by Name).
o Define your own order (e.g., sort by “High, Medium, Low”).

🔹 Steps to Sort Data

1. Select the data range (include column headings).
2. Go to the Data tab → Sort & Filter group.
3. Choose Sort Ascending (A→Z) or Sort Descending (Z→A).
4. For advanced options, click Sort… to open the dialog box.
5. Pick the column(s) to sort by and set the order.

Key Points
 Sorting does not change the data, only the order of display.
 You can sort by cell color, font color, or icon (conditional formatting).

Q) Explain Filtering data, Grouping Data

Filtering Data:
Filtering means showing only the rows that meet certain criteria while hiding the rest.

Key Points:
1. Purpose: Helps focus on specific information (e.g., only “Pass” students, only “Sales > 5000”).
2. How to Apply:
o Select your data → Go to Data tab → Filter.
o Small drop-down arrows appear in column headers.
o Choose criteria (e.g., text, number, date filters).
3. Types of Filters:
o Text filters (e.g., “Begins with”, “Contains”).
o Number filters (e.g., “Greater than”, “Top 10”).
o Date filters (e.g., “This Month”, “Next Quarter”).
4. Multiple Filters: You can filter across several columns at once.
5. Result: Only matching rows are displayed; others are hidden but not deleted.

Grouping Data:
Grouping means organizing rows or columns into collapsible sections for easier viewing.

Key Points:
1. Purpose: Helps summarize and manage large datasets (e.g., monthly sales grouped by quarters).
 8

2. How to Apply:
o Select rows/columns → Go to Data tab → Group.
o A “+” or “–” sign appears to expand/collapse the group.
3. Ungroup: Use Data tab → Ungroup to remove grouping.
4. Outline Feature: Excel can automatically group data using Subtotal or PivotTable.
5. Result: Data becomes structured, easier to navigate, and can be collapsed/expanded for clarity.

Q) Conditional formatting: Data Bars, Color Scales, Icon Sets, Custom Formulas.

Conditional Formatting is a feature that automatically changes the appearance of cells (colors,
icons, bars) based on the values they contain. It helps highlight important information, trends, or exceptions
in your data.

Types of Conditional Formatting

Data Bars
1. Select the range of cells you want to format.
2. Go to the Home tab → Conditional Formatting → Data Bars.
3. Choose either Gradient Fill or Solid Fill.
4. Excel adds bars inside cells, with length proportional to the values.
5. Adjust settings (colors, direction) via Manage Rules if needed.

🔹 Color Scales
1. Highlight the data range.
2. Go to Home → Conditional Formatting → Color Scales.
3. Pick a 2-color or 3-color scale (e.g., green-yellow-red).
4. Excel shades cells based on their relative values.
5. Customize the scale (minimum, midpoint, maximum colors) in Manage Rules.

🔹 Icon Sets
1. Select the cells to apply icons.
2. Go to Home → Conditional Formatting → Icon Sets.
3. Choose an icon style (arrows, flags, traffic lights, etc.).
4. Icons appear based on value ranges (high, medium, low).
5. Modify thresholds or icon rules in Manage Rules for precision.

🔹 Custom Formulas
1. Select the range of cells.
2. Go to Home → Conditional Formatting → New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter your formula (e.g., =A1>5000 to highlight sales above 5000).
5. Set formatting (color, font, border) and apply.