MODULE-III
➢ Electronic Spread Sheet
◆ Introduction to Spread Sheet
◆ Creating & Editing Worksheet
◆ Formatting and Essential Operations
◆ Formulas and Functions
◆ Charts
◆ Advanced features
● Pivot table & Pivot Chart
● Linking and Consolidation
SPREADSHEET
A spreadsheet is a software application used for storing, organizing, analyzing, and
presenting data in a tabular format.
It consists of rows and columns that intersect to form cells where data, formulas, and
functions can be entered.
Spreadsheets are widely used for:
Data entry and storage
Mathematical and statistical calculations
Data analysis and visualization
Budgeting, accounting, and financial planning
Creating charts and reports
Popular spreadsheet-based programs
1. Microsoft Excel
A spreadsheet program by Microsoft used to organize, analyze, and visualize data.
Key Features:
Advanced formulas and functions
Pivot tables for data analysis
Macros to automate tasks
Charting and visualization tools
2. Google Sheets
A free, cloud-based spreadsheet application from Google that allows online editing and
sharing.
Key Features:
Accessible from any device with internet
Real-time collaboration (multiple people can edit at once)
Auto-saving and version history
Integration with Google Drive and other Google apps
3. LibreOffice Calc
Definition: A free, open-source spreadsheet software that works offline and supports Excel
file formats.
Key Features:
Similar to Excel, supports many of the same functions
Free and open-source
Can open and save Excel files (.xls, .xlsx)
4. Apple Numbers
A spreadsheet application from Apple designed for macOS and iOS devices with a focus on
design and simplicity.
Key Features:
Clean, visually appealing interface
Good for making simple, attractive spreadsheets
Seamless syncing across Apple devices
Basic Spreadsheet Concepts
a) Worksheet and Workbook
Worksheet: A single page or sheet in a spreadsheet file that contains rows and
columns.
Workbook: A spreadsheet file that may contain one or more worksheets.
b) Rows, Columns, and Cells
Rows: Horizontal series of cells, numbered (1, 2, 3, …).
Columns: Vertical series of cells, labeled alphabetically (A, B, C, …).
Cell: The intersection of a row and a column (e.g., A1).
c) Cell Address and Cell Range
Cell Address: Unique reference of a cell (e.g., C5).
Cell Range: Group of cells selected together (e.g., A1:A10).
d) Data Types
Labels/Text: Names, titles, headings (e.g., "Name").
Values/Numbers: Numeric data used for calculations.
Date/Time: Recognized date or time values.
Formulas/Functions: Expressions used for calculation.
e) Formula
A user-defined expression to perform calculations.
Always begins with = sign.
Example: =A1+B1 (adds values of cells A1 and B1).
f) Functions
Predefined formulas that make calculations easier.
Examples:
o =SUM(A1:A10) → Adds numbers in range
o =AVERAGE(B1:B5) → Calculates average
o =MAX(C1:C20) → Finds the largest number
g) Features of Spreadsheets
Data Formatting: Bold, italic, cell color, number formats
Sorting & Filtering: Organize and find data easily
Charts & Graphs: Visual representation of data
Pivot Tables: Summarize large datasets
Conditional Formatting: Highlight data based on conditions
Creating, Saving, and Editing a Workbook
1. Creating a Workbook
A workbook in MS Excel is a file that contains one or more worksheets. Each worksheet
has rows, columns, and cells where you can enter data.
Steps to Create a New Workbook:
1. Open Microsoft Excel.
2. From the Start Screen, click on Blank Workbook.
3. A new workbook opens with a default name (e.g., Book1).
4. Start entering data into the cells.
2. Saving a Workbook
Saving ensures that your work is stored on your computer for future use.
Steps to Save a Workbook for the First Time:
1. Click on File in the top-left corner.
2. Select Save As.
3. Choose the location where you want to save (e.g., This PC → Documents).
4. Type a File Name (e.g., MyWorkbook).
5. Select the file format (default is Excel Workbook (*.xlsx)).
6. Click Save.
To Save Changes Later:
Just click File → Save or press Ctrl + S.
This will update the existing file with the latest changes.
[Link] a Workbook in MS Excel
Editing a workbook means making changes to the data, structure, or appearance of a
workbook to keep it accurate, organized, and presentable. Microsoft Excel provides many
tools for easy editing.
1. Entering or Modifying Data
Entering Data:
o Click on the cell where you want to enter data.
o Type numbers, text, or a formula.
o Press Enter to confirm.
Editing Existing Data:
o Double-click the cell to edit directly.
o Make changes and press Enter to apply.
Clearing Data:
o Select the cell(s) → Press Delete key to remove contents.
2. Copying, Cutting, and Pasting
Used to duplicate or move data from one place to another.
Copy: Select cell(s) → Press Ctrl+C
Cut: Select cell(s) → Press Ctrl+X
Paste Options: : Select cell(s) → Press Ctrl+V)
3. Inserting or Deleting
Excel allows adding or removing rows, columns, or worksheets to reorganize data.
Insert Row/Column:
o Right-click on the row/column header → Select Insert.
o A new blank row/column appears.
Delete Row/Column:
o Right-click on the row/column header → Select Delete.
Insert/Delete Cells:
o Right-click a selected cell → choose Insert or Delete.
o Excel will shift cells accordingly.
Insert/Delete Worksheets:
o Click “+” button near sheet tabs to add a new sheet.
o Right-click a sheet tab → Select Delete to remove it.
4. Formatting Cells
Formatting improves the readability and presentation of your data.
You can format from the Home tab in Excel.
Font Formatting: Change font style, size, bold, italic, underline.
Number Formatting: Display data as currency, percentage, date, time, etc.
Cell Borders and Fill: Add borders or background colors.
Alignment: Change text alignment (left, center, right) or rotate text.
Conditional Formatting: Highlight cells based on conditions (e.g., marks below 40 in
red).
5. Renaming a Worksheet
Renaming sheets makes your workbook easier to understand.
Double-click the sheet tab → Type the new name → Press Enter.
Or Right-click sheet tab → Select Rename → Type new name.
6. Renaming the Workbook
If you want to save the same workbook with a new name:
Go to File → Save As.
Enter a new name in the File Name box.
Choose location and click Save.
This keeps the old version safe and creates a new file with your updates.
7. Saving Edited Workbook
After making changes, always save:
Quick Save: Press Ctrl+S or click Save icon on Quick Access Toolbar.
AutoSave (Office 365): Keeps your changes saved automatically if enabled.
Tips for Editing
Use Undo (Ctrl+Z) if you make a mistake.
Use Redo (Ctrl+Y) to restore an undone action.
Keep related data in separate worksheets for better organization.
Inserting and Deleting Worksheets in MS Excel
A worksheet is a single sheet inside an Excel workbook where you can enter and manage
data. A workbook can contain one or many [Link] worksheets by inserting
new ones or deleting unwanted ones helps keep your data organized.
1. Inserting a Worksheet- Using the Plus (+) Button
1. Open your workbook.
2. Look at the Sheet Tabs at the bottom.
3. Click the “+” icon (New Sheet button).
4. A new worksheet will be added, usually named Sheet2, Sheet3, ….
2. Deleting a Worksheet
1. Right-click on the sheet tab you want to delete.
2. Select Delete from the menu.
Entering Data and Formulas in a Cell in MS Excel
In Microsoft Excel, cells are the smallest units where data is stored. Each cell is identified by
a cell address (e.g., A1, B2). Learning how to properly enter data and formulas is essential
for working with spreadsheets effectively.
Types of Data You Can Enter
Excel allows you to enter different types of information:
Text (Labels):
o Used for headings, names, categories (e.g., “Name”, “Subject”).
o Text is automatically left-aligned.
Numbers (Values):
o Used for numeric data (e.g., 100, 55.5).
o Numbers are automatically right-aligned.
Dates/Times:
o Recognized date formats (e.g., 24-09-2025) and time formats (10:30 AM).
o Excel can perform calculations on dates (e.g., difference between two dates).
Logical Values:
o TRUE or FALSE values used in logical operations.
Formulas/Functions:
o Begin with = sign and perform calculations (e.g., =A1+B1).
Entering Data into a Cell
Step-by-Step Process
1. Select the Cell:
Click on the cell where you want to enter data. The selected cell becomes active
(outlined with a border).
2. Type the Data:
o For text or numbers, simply type them.
o For formulas, start with an = sign.
3. Press Enter:
o Press Enter to confirm and move to the next row.
o Press Tab to confirm and move to the next column.
o Or click anywhere outside the cell to finish.
Editing Existing Data
Overwrite: Select the cell and start typing — new data will replace old data.
Edit Within Cell:
o Double-click the cell
o Make changes and press Enter to save.
Entering Formulas in Excel
What is a Formula?
A formula is an expression that performs a calculation on data.
It always starts with an equal sign (=).
Basic Formula Structure:
= Operand1 Operator Operand2
Example:
=A1+B1 → Adds the values in A1 and B1.
=C1*D1 → Multiplies C1 and D1.
Operators in Excel:
Arithmetic Operators:
o + Addition
o - Subtraction
o * Multiplication
o / Division
o ^ Exponentiation (e.g., =2^3 gives 8)
Comparison Operators:
o = Equal to
o <> Not equal to
o > Greater than
o < Less than
o >= Greater than or equal to
o <= Less than or equal to
Text Concatenation:
o & joins two strings (e.g., ="Hello "&"World" → Hello World)
Examples of Formulas
Formula Result
=10+5 15
=A1+B1 Sum of values in A1 and B1
=AVERAGE(C1:C5) Average of values in range C1 to C5
=A1*B1/100 Percentage calculation
=(A1+B1)*C1 Adds A1 & B1 first, then multiplies by C1 (uses order of precedence)
Order of Precedence:
When a formula has multiple operations, Excel follows PEMDAS rules:
1. Parentheses
2. Exponents
3. Multiplication/Division
4. Addition/Subtraction
Example:
=5+3*2 → Excel multiplies first → Result = 11
=(5+3)*2 → Parentheses first → Result = 16
Copying and Moving Data from Selected Cells
When working in Excel, you often need to reuse data (copy) or relocate data (move) from
one place to another. Excel provides multiple methods to copy or move data easily.
1. Copying Data
Copying allows you to duplicate data from one location to another without removing it
from the original location.
Steps to Copy Data
1. Select the Cell(s):
Click and drag to highlight the cell(s) you want to copy.
2. Copy the Data:
o Press Ctrl+C (keyboard shortcut),
o OR Right-click the selection → choose Copy,
3. Select the Destination Cell:
Click on the first cell where you want to place the copied data.
4. Paste the Data:
o Press Ctrl+V,
o OR Right-click → choose Paste,
2. Moving Data
Moving allows you to relocate data to a new location by removing it from the original
place.
Steps to Move Data
1. Select the Cell(s):
Highlight the cell(s) you want to move.
2. Cut the Data:
o Press Ctrl+X (keyboard shortcut),
o OR Right-click → choose Cut,
o OR Click Cut button on the Home tab.
3. Select the Destination Cell:
Click where you want the data to appear.
4. Paste the Data:
o Press Ctrl+V,
o OR Right-click → choose Paste.
(The data will now be removed from the original location and appear in the new
one.)
3. Drag and Drop Method (Alternative for Moving)
You can also move data using the mouse:
1. Select the cell(s).
2. Move the pointer to the edge of the selection until it becomes a four-sided arrow
cursor.
3. Hold down the left mouse button and drag the selection to the new location.
4. Release the mouse button to drop the data.