Chapter 5: Spreadsheet—An Introduction

Unit 1: Fundamentals of Spreadsheet

Learning Scope:

 Introduction to spreadsheets
 Features and advantages of a spreadsheet
 Components of MS Excel Spreadsheet
 Working with Excel (creating, entering, editing data, saving, printing, closing)
 Undo and redo features
 Types of data (number, string, formula)
 Performing calculations using formulas

Introduction:

 Word processors like MS Word are used to create, edit, and print documents.
 Spreadsheets are used for numerical data and mathematical operations.
 A spreadsheet is a computer application designed for entering, manipulating, and
evaluating data.
 It finds applications in various fields like:
o School reports (mark sheet, scoreboards)
o Cash memos
o Annual reports, etc.
 MS Excel is an MS Office application package that allows spreadsheet work.

Features of Spreadsheet (MS Excel):

1. Can handle large amounts of data (numbers, text, date, formulas, etc.).
2. Data can be represented in charts and graphics for better visualization.
3. Allows sorting of data in ascending or descending order.
4. Provides password protection to prevent unauthorized access.
5. Enables printing of specific areas or the entire spreadsheet.

Advantages of Spreadsheet

Some of the advantages of MS Excel (Spreadsheet) are:

1. Easy to Use – Does not require any specialized training.

2. Minimizes Manual Work – Reduces manual tabulation, increasing accuracy.
3. Reliable Calculations – Uses built-in functions for more accurate results.
4. Data Analysis – Can handle large amounts of data with powerful tools.
5. Formatting Options – Provides different ways to format spreadsheets.
6. Increases Efficiency – Users can update entries easily and detect errors.
7. Sorting & Filtering – Helps organize data efficiently.
Starting MS Excel (Spreadsheet)

 MS Office 2016 Excel has more features than its 2013 version, enabling professional
spreadsheet creation.
 You can start MS Excel by performing the following steps:

Step 1: Click Start → Search for Excel Icon

OR

o Type Excel in the search box and click the Excel icon.
OR
o Click the Excel shortcut icon on the desktop (if available).
OR
o Click the Excel icon on the taskbar (if available).
 The default MS Excel window will then appear on the screen.

Step 2: Opening a Blank Workbook

 Select ‘Blank Workbook’ from the available templates.

 A spreadsheet opens with various elements labeled.

Components of MS Excel Spreadsheet

The different components of an MS Excel Spreadsheet are:

1. Worksheet
o Each page of the workbook is called a worksheet.
o The active worksheet is displayed in the window.
o The default worksheet is Sheet1.
2. Workbook
o An Excel file is called a workbook.
o A workbook is a collection of worksheets.
o By default, it contains one worksheet (Sheet1).
o More sheets can be added or removed as needed.
3. Rows
o Run horizontally in a worksheet.
o Represented by numbers (1, 2, 3, etc.).
o A worksheet can have a maximum of 1,048,576 rows (depending on the system).
4. Columns
o Run vertically in a worksheet.
o Represented by letters (A, B, C... Z, then AA, AB, etc.).
o A worksheet can have a maximum of 16,384 columns.
 5. Cell
o The smallest unit of a worksheet.
o Formed at the intersection of a row and a column.
6. Cell Address
o Each cell has a unique reference (address).
o Written as column letter + row number.
o Example: F4 means Column = F, Row = 4.

Additional Components of MS Excel Spreadsheet

1. Active Cell
o The currently selected cell where data is entered or results appear.
o It has a dark boundary around it.
2. Cell Pointer
o A rectangular highlight indicating the active cell.
o Can be moved using arrow keys or mouse.
3. Title Bar
o Displays the file name and application name.
o If no file is open, it shows a default file name.
4. Quick Access Toolbar
o Stores shortcut buttons for frequently used commands.
5. Ribbon
o Displays all frequently used tools in a Graphical User Interface (GUI).
6. Name Box
o Displays the cell address of the active cell.
7. Formula Bar
o Used to enter formulas for mathematical and logical calculations.
8. Row Header
o Shows row numbers (1, 2, 3...) of an active worksheet.
9. Column Header
o Displays column letters (A, B, C...).
10. Sheet Tab
o Shows the active worksheet (Default: Sheet1).
o Helps switch between multiple worksheets.
11. New Sheet Button
o Used to insert more worksheets in a workbook.
12. Vertical Scroll Bar
o Moves the worksheet up and down.
13. Horizontal Scroll Bar
o Moves the worksheet left and right.

Working with Excel

Some basic tasks in Excel:

  Creating a new workbook
 Entering data in a workbook
 Editing data in a workbook
 Saving a workbook
 Performing calculations
 Working with simple formulas
 Printing a workbook
 Closing a workbook

Creating a New Workbook

Step 1: Click the ‘File’ tab from the ribbon.

1. Step 2: Select "New" from the drop-down list.

2. Step 3: Choose "Blank Workbook" from the New window.
o A new blank workbook (BOOK1) will open.

Entering Data in a Worksheet (Example)

Let’s assume you want to create a table with student names and their participation categories for
a "Sit and Draw" competition.

Steps to Enter Data:

1. Step 1:
o Select cell B1, type "Sit and Draw" and press Enter.
2. Step 2:
o Add headings to different columns:
 Select A2, type "Sl. No.", press Enter.
 Select B2, type "Names", press Enter.
 Select C2, type "Category", press Enter.
3. Step 3:
o Enter student details:
 In A3, type "1", then press the Tab key to move to the next cell.
 In B3, type "Anurag Kashyap" and press Tab again.
 In C3, type "Poster Colour" and press Enter.

Note:

 You can also navigate the worksheet using arrow keys.

Step 4: Formatting the Heading

 You can color the heading to make the worksheet more attractive.
  Select the cell, then go to the "Home" tab.
 Choose "Font Color" from the "Font" group.

Editing Data in a Worksheet

You can edit existing data by following these steps:

1. Step 1: Click on the cell you want to edit.

2. Step 2: Type the new data.
3. Step 3: Press Enter.

Example of Editing

 The categories for "Shubham Kumar" and "Tulika Jaiswal" were updated in the table.

Backstage View

 You can access the Backstage View by clicking on the "File" tab.
 This section allows you to perform different tasks such as:
o Creating a new workbook.
o Opening an existing workbook.
o Saving a workbook.
o Printing data.
o Closing the file.

The Backstage View provides several important options:

 Info – Displays information about the workbook.

 New – Opens a new worksheet.
 Open – Opens an existing workbook.
 Save – Saves the current workbook.
 Save As – Saves the workbook with a new name.
 Print – Displays print preview and printing options.
 Share – Allows sharing via cloud or email.
 Export – Converts the file to PDF/XPS.
 Close – Closes the workbook after saving changes.
Saving a Workbook

Follow these steps to save an Excel file:

1. Step 1: Click the "File" tab.

2. Step 2: Select "Save As" from the left panel.
3. Step 3: Choose "This PC" and click "Browse".
4. Step 4: The "Save As" dialog box appears.
5. Step 5: Enter the file name and choose a location.
6. Step 6: Click "Save".

Saving a File in Excel

To save an Excel workbook:

1. Choose the location (e.g., E drive).

2. Enter the file name in the "File Name" box.
3. Click "Save".
4. The file is saved with a .xlsx extension.

Undo and Redo in Excel

 Undo (Ctrl + Z): Reverts the last action.

 Redo (Ctrl + Y): Restores the undone action.

Both options are available in the Quick Access Toolbar, allowing users to switch back and forth
between changes.

Types of Data in MS Excel

1. Number Data – Includes integers and real numbers.

o Used in mathematical operations (e.g., 1500, 34.5, 76.50).
2. String/Text Data – Includes letters, words, and symbols.
o Not used in calculations (e.g., "Name", "Marks", "Pay2021").
o Numbers with single quotes (‘45) are treated as text.
3. Formula Data – Expressions using numbers & operators.
o Begins with an equal sign (=).
o Example:
 =10 + 20 (Adds 10 and 20).
 =A1 + A2 + A3 (Adds values of A1, A2, A3).
Performing Calculations in MS Excel

MS Excel allows simple and complex calculations. Here’s how you can perform basic arithmetic
operations:

1. Addition (+)

To add two or more numbers:

1. Select the cell (e.g., B2).

2. Type the formula, e.g., =45 + 32.
3. Press Enter, and the result appears in the cell.

2. Subtraction (-)

To subtract numbers:

1. Select a cell.
2. Type =128 - 91.
3. Press Enter, and Excel will display the result.

3. Multiplication (*)

To multiply two or more numbers:

1. Select a cell.
2. Type =12 * 10 * 5.
3. Press Enter, and Excel will calculate the product.

4. Division (/)

To divide numbers:

1. Select a cell.
2. Type =100 / 4.
3. Press Enter for the result.

💡 Tip: Always start formulas with = for Excel to recognize calculations

Working with Simple Formulae in MS Excel

Excel allows arithmetic operations using cell addresses instead of direct values. Here’s how you
can use formulas with cell references:

1. Addition Using Cell References

To add values from multiple cells (e.g., B2, C2, D2):

1. Select the target cell (e.g., E2).

2. Type the formula:

CopyEdit
=B2+C2+D2

3. Press Enter, and Excel will display the sum.

2. Subtraction Using Cell References

To subtract values using cell references:

1. Select a cell.
2. Type the formula:

CopyEdit
=B2-C2

3. Press Enter, and Excel will compute the difference.

💡 Tip: Using cell references makes formulas dynamic. If you change a cell’s value, the formula
updates automatically! 🚀

More Excel Formulae and Functions

3. Multiplication Using Cell References

To multiply values from different cells:

1. Select a cell.
2. Type the formula:

CopyEdit
=B2*C2

3. Press Enter, and the product will appear.

4. Division Using Cell References

To divide one value by another:

1. Select a cell.
2. Type the formula:

CopyEdit
 =B2/C2

3. Press Enter, and the quotient will appear.

5. Calculating Percentage in Excel

To express a division result as a percentage:

1. Select the cell where the result is stored (e.g., D2).

2. Click on the Home tab.
3. Select the Percentage (%) option from the Number group.
4. The result will now display as a percentage.

💡 Note: You can increase or decrease decimal places using the Increase Decimal (→) or
Decrease Decimal (←) buttons.

Opening and Printing a Workbook in Excel

Opening a Workbook

To open an existing Excel workbook:

1. Click on the File button. This will open the Backstage View.
2. Select Open. This will show three panes:
o The rightmost pane displays recently opened files. Click on the file if it is listed.
o If the file is not listed, choose This PC and manually locate the file.
3. The selected file will open on your screen.

Printing a Workbook

To print an Excel worksheet:

1. Click on the File button.

2. Select the Print option.
3. The Print Window will appear, where you can:
o Choose a printer.
o Adjust settings (like page orientation and number of copies).
4. Click Print to generate a hard copy.

Finalizing Printing and Closing a Workbook in Excel

Final Printing Steps

Before printing, Excel displays a Print Preview along with various settings:

1. Printer Selection – Choose the printer connected to your system.

 2. Settings:
o Print Active Sheet – Prints only the currently active sheets by default. If you
want to print a specific sheet, select it manually.
o Pages – Specify a range of pages to print.
o Page Orientation – Choose Portrait or Landscape mode.

🔹 Knowledge Corner:

 By default, Excel prints one copy of the selected worksheet.

 The default page orientation is Portrait.

Closing a Workbook

To close an Excel workbook:

1. Click on the File button and select Close.

2. If the file is unsaved, a prompt will appear asking whether to save changes:
o Click Save to keep changes.
o Click Don't Save if you don’t need the changes.
o Click Cancel to return to the workbook.

📌 Reminder: Always save your work before closing to avoid losing important data! 😊
Exercise Answers – Computer Studies (Spreadsheet Basics)

I. Choose the correct option:

1. Cell – Formed by the intersection of a row and a column.

2. Sheet 1 – Default worksheet in a workbook.
3. AA – Next column after Z.
4. In the active cell – The value entered appears in the active cell.
5. Ctrl+Z – Used to reverse the last action performed.

II. Fill in the blanks:

1. .XLSX – Default extension of a workbook.

2. File – A file contains multiple worksheets.
3. Cell – The smallest unit of a worksheet.
4. Column letter followed by a row number – A cell is referred this way (e.g., A1).
5. Print – Displays the Print Preview of a worksheet.

III. True/False:

1. False – You can close a workbook without saving changes.

2. True – You can modify the extension before saving.
3. False – Columns are vertical, not horizontal.
4. True – E10 represents column E and row 10.
5. False – "Save As" allows the user to choose a new name/location before saving.

IV. Name the following components of a spreadsheet:

1. Quick Access Toolbar – Stores shortcut buttons frequently used.

2. File Tab – Displays frequently used tasks.
3. Formula Bar – Used to write formulas for operations.
4. Sheet Tab – Allows inserting new worksheets.
5. Formula Bar – Used for mathematical and logical formulas.

Solved Section

1. (a) Name the large sheet that appears when an MS Excel file is opened.
o Answer: (i) Worksheet
2. (b) What is the name of the rectangular pointer that appears in an MS Excel
worksheet?
o Answer: (ii) Cell pointer
3. (c) When an MS Excel file is opened, which cell remains active by default?
o Answer: (i) A1
4. (d) A cell can be referred by using:
o Answer: (ii) A column name followed by a row number
Unsolved Section (Formulas and Answers)

1. Task (1): Formula: =A2+B2/C2

o Given values: A2 = 30, B2 = 12, C2 = 4
o Calculation: 30+124=30+3=3330 + \frac{12}{4} = 30 + 3 = 3330+412
=30+3=33
o Answer: 33
2. Task (2): Formula: =A6/B6
o Given values: A6 = 6, B6 = 4
o Calculation: 6/4=1.56 / 4 = 1.56/4=1.5
o Answer: 1.5
3. Task (3): Formula: =A4/B4*C4
o Given values: A4 = 45, B4 = 9, C4 = 2
o Calculation: (45/9)∗2=5∗2=10(45 / 9) * 2 = 5 * 2 = 10(45/9)∗2=5∗2=10
o Answer: 10

1. Task (4): Formula: =A4/B4*C4

o Given values: A4 = 15, B4 = 10, C4 = 8
o Calculation: (15/10)∗8=1.5∗8=12(15 / 10) * 8 = 1.5 * 8 =
12(15/10)∗8=1.5∗8=12
o Answer: 12

Subjective Questions - Short Answers

1. What is a spreadsheet?
o Answer: Refer to Page 82 (as written in the image).
2. What is the significance of Undo and Redo buttons?
o Answer: Refer to Page 77.
3. Define the following terms:
o Worksheet: Refer to Page 72, 73.
o Workbook: Refer to Page 72, 73.
o Cell: Refer to Page 72, 73.
o Active cell: Refer to Page 72, 73.
o Formula bar: Refer to Page 72, 73.

Arithmetic Tasks in a Spreadsheet

1. To find the product of the first five odd numbers:

o Formula: =1*3*5*7*9
2. To find the sum of 87, 89, 91, 66:
o Formula: =87+89+91+66
 3. To subtract 999 from 1000:
o Formula: =1000-999
4. To show the result of dividing 612 by 6:
o Formula: =612/6
5. To display the sum of the first three prime numbers:
o Formula: =2+3+5

III. Spreadsheet - Formulas

1. To find the sum of cell values A2 to A5:

o Formula: =A2+A3+A4+A5
2. To subtract the cell value D5 from the sum of cell values D3 and D4:
o Formula: =(D3+D4)-D5
3. To add cell value B5 with the product of cell values B2 to B4:
o Formula: =B5+(B2*B3*B4)
4. To divide the sum of cell values of E1 and E2 by E4:
o Formula: =(E1+E2)/E4

IV. Long Answer Questions

1. What are the advantages of an electronic spreadsheet?

o Answer: Refer to Page 71.
2. Enlist the features of an electronic spreadsheet.
o Answer: Refer to Page 70 (or any other page mentioned in the book).
3. Write down the main steps to perform the following tasks with reference to MS
Excel:
o (a) To create a new workbook → Refer to Page 77
o (b) To save a workbook → Refer to Page 77
o (c) To print a workbook → Refer to Page 77
4. What are the different types of data used in MS Excel? Explain.
o Answer: Refer to Page 78.
5. How will you perform simple addition and multiplication of numbers in a cell of a
worksheet? Explain.
o Answer: Refer to Page 78.
6. Using the concept of formula in MS Excel, write down all the steps:
o (a) To calculate the product of values available in the cells A2, B2, C2, and
D2:
 Formula: =A2*B2*C2*D2
o (b) To find the result when the sum of cells A4 and A5 is divided by A6:
 Formula: =(A4+A5)/A6