Unit 4: Electronic Spreadsheet
Spreadsheet (Workbook)
It is program (software) that is used to do calculation and store the records. It allows you to store , organize, calculate and manipulate the saved or available data in a tabular format. It consists rows and columns. It provides inbuilt features and data analysis tools that make it easier to work with the large amount of data.
Some Important facts or points related to MS Excel (Spreadsheet) Program
- A Excel File is also known as workbook or spreadsheet program
- A sheet of an excel file is known as worksheet
- A combination of worksheets is known as workbook
- There are 3 worksheets available in Excel 2007 by default
- By default, the name of Excel file is Book1
- The smallest unit of MS Excel file is a cell
- A cell is an intersection of a row and column
- Horizontal series of cells is known as row
- Vertical series of cells is known as column
- In spreadsheet A, B, C, D…… termed as column name
- In spreadsheet 1, 2, 3, 4…… termed as row number
- Section A, B, C, D…..are known as column header
- Section 1, 2, 3, 4…..are known as row header
- Home, Insert, View etc. are Tabs
- Bold, Italic, Underline, Font size etc. are Functions or Tools
- Clipboard, Paragraph etc. are Groups
- Topmost bar of an Excel file is known as Title Bar
- Last column name – XFD (16384)
- Last row number – 1048576
- To select the adjacent row number or column name, we press SHIFT and Arrow key
- To select the random row number or column name, we press CTRL and Arrow key
- Some popular spreadsheet software are:
- Microsoft Excel
- Google Sheets (part of Google docs)
- LibreOffice
- Apache OpenOffice Calc
Important Shortcut Keys of MS Excel
| Description | Shortcut Key |
| TO OPEN AN EXISTING FILE | CTRL + O |
| TO SAVE A FILE | CTRL + S |
| TO CREATE A NEW FILE | CTRL + N |
| TO CLOSE A FILE | CTRL + W |
| TO CLOSE AN APPLICATION | ALT +F4 |
| TO PRINT | CTRL + P |
| TO UNDO THE CHANGES | CTRL + Z |
| TO MOVE TO PREVIOUS STAGE (REDO) | CTRL + Y |
| TO SELECT ANY TOOL FROM RIBBON | ALT + DESIRED CHARACTER(S) KEY |
| TO OPEN SAVE AS DIALOG BOX | F12 |
| TO OPEN THE HELP PANE | F1 |
| TO SEARCH IN SPREADSHEET | CTRL + F |
| TO MOVE TO THE NEXT SHEET | CTRL + PAGE DOWN KEY (pg dn) |
| TO MOVE TO THE PREVIOUS SHEET | CTRL + PAGE UP KEY (pg up) |
| TO MINIMIZE THE WORKBOOK WINDOW | CTRL + F9 |
| TO CREATE A NEW WORKSHEET | SHIFT + F11 |
| TO GET TOTAL OF ADJACENT CELLS | ALT + EQUAL KEY (=) i.e. [AUTOSUM] |
| TO DISPLAY THE INSERT DIALOG BOX | CTRL + SHIFT + PLUS KEY (+) |
| TO COPY ABOVE CELL DATA | CTRL + D |
| TO APPLY FILTER | CTRL + SHIFT + L |
| REPEAT THE MOST COMMANDS AND ACTIONS | F4 |
| INSERT NEW ROW/COLUMN | CTRL + SHIFT + = |
| HIDE THE COLUMN | CTRL + 0 (ZERO) |
| HIDE THE ROW | CTRL + 9 (NINE) |
| UNHIDE THE COLUMN | CTRL + SHIFT + ) |
| UNHIDE THE ROW | CTRL + SHIFT + ( |
| SELECT AN ENTIRE ROW | SHIFT + SPACE |
| SELECT AN ENTIRE COLUMN | CTRL + SPACE |
| TO DISPLAY THE INSERT DIALOG BOX FOR INSERTING BLANK CELLS | CTRL + SHIFT + = |
Session 1: Introduction to Spreadsheet
5 Features of an Excel Program
- Functions and Formulas: In MS Excel, there are many built-in functions and formulas which are used to perform action over the sheet and calculation respectively. Some calculation based formulas are: Sum, Average, Max etc.
- Formatting options: These options are used to enhance the appearance of stored data.
- Auto-Calculation: The data is automatically recalculated in the whole worksheet if any changes are made in different cells or one cell.
- Fast Searching: In this a user can search data fast as well as replace or edit instantly.
- Sort and Filter Tool: These tools are used to arrange data in systematic way. We use sort to arrange in alphabetical order and filter is used to search or find any specific data.
Difference between a Row and a Column
Row is the horizontal series of cells in Excel. Rows have numbers in Excel like 1,2,3,4,5… whereas Coulmn is the vertical series of celss in Excel. Columns have names in Excel like A, B, C….. AA, AB……
What is a formula bar? How is it different from name box?
Formula bar is used to apply the formula on active cell. It is located to the right side of the Name box whereas the Name box is located left to the formula bar and it displays the name of active cell.
Session 2: Manage a Workbook
Difference between a Workbook and a Worksheet
- Workbook:
- A workbook is a file that contains one or more worksheets or sheets.
- It’s like a container for your data, charts, and other elements.
- You can think of a workbook as a single Excel or spreadsheet file. When you open Excel or a similar program, you are typically working within a workbook.
- Worksheet:
- A worksheet is a single tab or page within a workbook.
- It’s where you enter and manipulate your data, perform calculations, create charts, and so on.
- Worksheets are organized in a tabbed interface at the bottom of the workbook window, and you can switch between them to work on different sets of data or calculations within the same workbook.
Different ways of Renaming a Worksheet
Ans: To rename a worksheet.
- Method 1: Right click on sheet name. A context menu will appear. Select rename option. Write desired name.
- Method 2: Double click on sheet name and write new one.
Session 3: Enter Data in a Spreadsheet
Kind of data can be entered into worksheets
Text, Numbers, Formulas, Symbols, Date and Time, Money
How are brackets used to specify negative numbers in worksheet?
Use minus sign directly with the number or using bracket like (-34) or -(34) or -34
What is a formula?
In MS Excel, Formula must begin with “=” symbol followed by cell references and operators. It is a symbolic representation of numbers, cell-addresses, operators and parenthesis (brackets) in the form of expression to solve mathematical problems.
Session 4: Basic Calculations – Addition, Subtraction, Multiplication and Division
Steps to find the Sum of Different Numbers in Different Cells of MS Excel.
Step to find sum of numbers in cell B3.
- Step 1: Click on Cell B3 and type “=” sign.
- Step 2: Write the function “sum” or directly use cell addresses and + operator.
- Step 3: Use =A1+B1+C1 in Cell B3. Press Enter.
What do you understand by a function?
Functions are pre-defined and designed formulas to perform simple and complex calculations. Functions eliminate the chance to write wrong formula. They accept Arguments and return Values
=SUM(A1,B1)
Here, “SUM” is a formula/function and A1, B1 are argumentsArguments – Arguments are input values to functions. Arguments can be numbers, text, logical values such as True or False, range of cell reference, or formulas that are enclosed within parenthesis.
Structure – The structure of a function defines its basic skeleton.
=FUNCTION NAME (argument 1, argument 2, argument 3……)Functions in MS Excel
| SUM() | Processing | Output |
| =SUM(76,45,90) | 76+45+90 | 211 |
| =SUM(A1:A3) | A1+A2+A3 | As per data |
| =SUM(6,7,TRUE) | 6+7+1(TRUE) | 14 |
| =SUM(A1:A3,15) | A1+A2+A3+15 | As per data |
| =SUM(“4”,15,TRUE) | 4+15+1(TRUE) | 20 |
| AVERAGE() | Processing | Output |
| =AVERAGE(1,2,3) | (1+2+3)/3 | 2 |
| =AVERAGE(A1:A3) | (A1+A2+A3)/3 | As per data |
| =AVERAGE(6,5,TRUE) | (6+5+1)/3 | 4 |
| =AVERAGE(A1,A2,A5) | (A1+A2+A5)/3 | As per data |
| MAX() | Processing | Output |
| =MAX(3,4,5) | GREATEST NO. | 5 |
| =MAX(A1:B3) | GREATEST NO. FROM A1,A2,A3,B1,B2,B3 | As per data |
| =MAX(A1:A3,2000) | GREATEST NO. FROM A1,A2,A3,2000 | As per data |
| MIN() | Processing | Output |
| =MIN(3,4,5) | LOWEST NO. | 3 |
| =MIN(A1:B3) | LOWEST NO. FROM A1,A2,A3,B1,B2,B3 | As per data |
| =MIN(A1:A3,2000) | LOWEST NO. FROM A1,A2,A3,2000 | As per data |
| TODAY() | Processing | Output |
| =TODAY() | TODAY’S DATE | As per system |
| =MONTH(TODAY()) | TODAY’S MONTH | As per system |
| =DAY(TODAY()) | TODAY’S DATE (NUMERIC) | As per system |
| =TEXT(TODAY(),”DDDD”) | DAY IN TEXT | As per system |
| =TEXT(TODAY(),”MMMM”) | MONTH IN TEXT | As per system |
| IF() | Processing | Output |
| =IF(A1>50,”PASS”,”FAIL”) | Check condition | As per condition |
If the logical condition is false it returns the false value (i.e. FAIL)
Formula or Function to multiply the Number of two Cells
- Step 1: In particular cell, type = (equal) sign
- Step 2: Write formula like = a1*b1. (*) is termed as asterisk sign.
- Step 3: Press Enter.
or use
=PRODUCT(A1,B1)Session 5: Insert Column and Row
Different Options available in the Insert Dialog Box
In Insert dialog box, there are 4 options available.
- Shift Cells Right
- Shift Cells Down
- Entire Row
- Entire Column
Different methods of Inserting Blank Rows or Columns?
Method 1: Using Shortcut Keys
Press CTRL + SHIFT + =Method 2: Using Ribbon Bar
- Step 1: Select Insert option in the Cells group of Home Tab.
- Step 2: Select “Insert Sheet Row/Column” option to insert Row/Column.
Method 3: Using mouse (Right Click)
- Step1: Right click on Row number or Column name.
- Step 2: Select an Insert option.
- Step 3: New row or column will be inserted.
Session 6: Format Cell and its Contents
Use of formatting Feature in MS Excel
In MS Excel the formatting feature is used to beautify the data and make the worksheet presentable. It enhance the overall look of the workbook. We can format numbers, text, tables etc in MS Excel.
Formatting Features available in Spreadsheet
- BOLD: A formatting style that makes text appear thicker and more pronounced.
- ITALIC: A text style that slants characters to the right for emphasis or differentiation.
- UNDERLINE: A style that adds a line beneath text to highlight or emphasize it.
- FONT SIZE: The specified dimension of a typeface, determining how large or small text appears.
- FONT COLOR: The hue applied to text, allowing customization of its visual appearance.
- FILL COLOR (BG COLOR OF FONT): The background color applied to the area behind text.
- ALIGNMENTS: The arrangement of text or objects relative to a defined reference point or line.
- MERGE: The action of combining multiple cells or objects into a single unit in documents or spreadsheets.
- ORIENTATION: The direction in which text or objects are positioned, often referring to horizontal or vertical alignment.
- BORDER: A visible line or outline around the edge of a text box, cell, or object for emphasis or separation.
- WRAP-TEXT: A formatting option that adjusts text layout to fit within a defined boundary, preventing overflow.
What is the purpose of wrapping-text?
Wrap text option is used to display long text in a single cell without overflowing it to the other cells. When text exceeds a column width, it is possible to wrap the text either manually or automatically.
Difference between ‘basic formulas’ and ‘compound formulas’?
Basic formulas involve only one operator in a formula.
Example: =a1+a2, here '+' is an operator.Compound formulas are used when we need more than one operator.
Example: =P*R*T/100Row header and Column header
Row header: Each row in Excel is represented by a specific number which is present on the left hand side of a worksheet. This number is known as a Row header. Row headers are numbered from 1 to 10,48,576.
Column header: Alphabets across the top border of a worksheet represent column heading, starting with A to Z, AA…AZ, BA…. BZ….XFD. Every worksheet in Excel contains 16,384 columns.