Notes on Spreadsheet | Extra References > Teacher Training > Digital Literacy | KULLABS.COM

Spreadsheet

  • Note
  • Things to remember
  • Videos
  • Quiz

sac

Microsoft Excel is a popular Windows spreadsheet program that provides worksheets, charts, database and list operations, and application programming all in one software environment. It is used for managing, analyzing and presenting data in a graphical manner.Microsoft ExcelSpreadsheet is an application program that organizes data into rows and columns and allows the user to perform calculations on it. The spreadsheet has rows and columns. Each intersection of a row and a column forms a cell, in which you store data. Examples of popular spreadsheet packages are Microsoft Excel, Lotus 1-2-3 etc.

Advantages of MS-Excel:
  • Ms-Excel is very easy to use.
  • We can easily perform calculations by using different functions available in this program.
  • We can work with two or more than two worksheets at a single time.
  • We can represent data through different charts and graphs.

Starting MS Excel 2007

To start Ms-excel program, we should follow the following steps:

  • Step 1:Click on the start button.
  • Step 2:Move the mouse pointer over All Program Option.
  • Step 3:Move the mouse pointer over Microsoft office option.
  • Step 4:Click on the Microsoft Excel 2007.
    Then the window appears on the screen.
    ee

When Excel is started, a new blank page titled Document1 is opened automatically.

  1. Title Bar - located at the top of the window and states the file name and the program you currently have open.
  2. Menu Bar - has the words Home, Insert, Page Layout, References, Mailings, Review, and View across the top. Each of these tabs open up into its own ribbon when you click on them to show you further options.
  3. Ribbons - rows of buttons that perform various actions used in creating and editing your document.
  4. Ruler - directly below the ribbon is a ruler. You can use the ruler to set tabs, indents, and margins.
  5. Scrollbars - are located on the right side and on the bottom of the screen. By clicking on the arrows at the ends of the scrollbars, you can move up and down or left and right through your document.
  6. Microsoft Office Button – located in the upper left hand corner. It is the menu button where you will find new, open, print, etc.

Create or open a new Excel file:

  1. Click on the Microsoft office button then choose new then if prompted chose blank document. (or you can use ctrl + N as the shortcut)
  2. Click on the Microsoft office button then choose open then if prompted chose the document you want to open. (or you can use ctrl + O as the shortcut)
    s

Save a file:

  1. Click on the Microsoft office button then choose save (if first time saving the document) or save as (if you want to save same file with differnt filename or file type.) (Use ctrl+S as shortcut)
  2. Provide the filename to the document.
  3. Click save.

Common Operating tools (Cut, Copy Paste)

We can cut, copy, paste the text in the document. To do that you can select the text you want to cut or copy then press ctrl+ C (for copy) or ctrl + X (for cut) and place the curser at the place you want to paste the text then press ctrl + V. Cut will remove the text you have selected while copy won't.

df

Cell reference
Each row and column of the electronic spreadsheet has a specific name. Every rows and column are denoted by 1,2,3....and A, B, C.. respectively. A cell is the intersection point of a vertical column and a horizontal row. The name of a cell is given by the combination of row and column such as A1, D15 etc. In Excel, a cell reference identifies the location a cell or group of cells in the worksheet. There are three types of cell reference:
  • Relative cell reference: Relative referencing means that the cell address changes as you copy or move it; i.e. the cell reference is relative to its location.
  • Absolute cell reference: This means the cell reference stays the same if you copy or move the cell to any other cell. This is done by anchoring the row and column, so it does not change when copied or moved.
  • Mixed cell reference: This means you can choose to anchor either the row or the column when you copy or move the cell, so that one change and the other does not. For example, you could anchor the row reference then move a cell down two rows and across four columns and the row reference stays the same.
Use of formula and function in Excel
Use of formula and function in Excel
Use of formula and function
A formula in Excel is an entry that consists of values, address or range of cells, functions, and operators. A formula begins with (=) sign and returns the result in the cell where you enter it. The formula appears in the formula bar.
Functions are ready made formulas provided by Excel. They are calculation tools that make our work easy. Sum, Average, Min, Max are some of the examples of functions. Excel provides different types of functions such as arithmetic, logical, string, etc.
Sum () Function
This function adds all the numbers in a range of cells.
Syntax: SUM (number 1, number 2, ......)
Or,
SUM ( cell1, cell2, [cell3:cell4] )
number 1, number 2,...are arguments for which you want the total value or sum. It accepts maximum 30 arguments.
To calculate sum, following steps should be followed:
  • Step 1: Click on the cell in which you want to carry out function.
  • Step 2: Type "=" sign in the same cell.
  • Step 3: Type SUM and '(' and range of the cells which are to be added. Then type ')' and enter to see the result.
  • Step 4: If the same formula is to be used in other cells then click on fill handle and drag the mouse to those cells.
MIN () Function
This function returns the smallest number in a series of numbers.
Syntax: Min(number 1, number 2, ......)
number 1, number 2, ...... are 1 to 30 numbers for which you want to find the minimum value.
Follow the following steps to find the smallest number by using MIN function:
  • Step 1: Click on the cell.
  • Step 2: Type "=" and use MIN function.
  • Step 3: Type MIN and '(' and range of the cells in which you want to find the smallest number. Then type ')' and enter to see the result.
  • Step 4: If the same formula is to be used in other cells then click on fill handle and drag the mouse to those cells.
MAX () Function
MAX () returns the largest value in a set of values.
Syntax: MAX(number 1, number 2, ......)
Follow the following steps to find the maximum value by using MAX function:
  • Step 1: Click on the cell.
  • Step 2: Type "=" and use MAX function.
  • Step 3: Type MAX and '(' and range of the cells in which you want to find the maximum value. Then type ')' and enter to see the result.
  • Step 4: If the same formula is to be used in other cells then click on fill handle and drag the mouse to those cells.
AVERAGE () Function
This returns the average of the number.
Syntax: AVERAGE(number 1, number 2, ......)
Follow the following steps to find the average value by using AVERAGE function:
  • Step 1: Click on the cell.
  • Step 2: Type "=" and use AVERAGE function.
  • Step 3: Type AVERAGE and '(' and range of the cells in which you want to find the average value. Then type ')' and enter to see the result.
  • Step 4: If same formula is to be used in other cells then click on fill handle and drag the mouse in those cells.

Product() function
This returns the product of the supplied set of numerical values.
Syntax: product(number1, [number2],...)
Follow the following steps to find the average value by using PRODUCT function:

  • Step 1: Click on the cell.
  • Step 2: Type "=" and use PRODUCT function.
  • Step 3: Type PRODUCT and '(' and range of the cells in which you want to find the product value. Then type ')' and enter to see the result.
  • Step 4: If same formula is to be used in other cells then click on fill handle and drag the mouse in those cells.

Power() function
This returns the product of the supplied set of numerical values.
Syntax: Power(number1, [number2],...)
Follow the following steps to find the average value by using POWER function:

  • Step 1: Click on the cell.
  • Step 2: Type "=" and use POWER function.
  • Step 3: Type POWER and '(' and range of the cells in which you want to find the power value. Then type ')' and enter to see the result.
  • Step 4: If same formula is to be used in other cells then click on fill handle and drag the mouse in those cells.
IF () Function
If () function checks the condition and returns the TRUE value if the condition you specify is true and the FALSE value if it is false.
Syntax: If (logical_test, value_if_true, value_if_false)
If you want to calculate the result of a student by using If function, then follow following steps:
  • Step 1: Click on the cell in which you want to calculate result.
  • Step 2: Evaluate marks of different subjects by using If function.
  • Step 3: Result is calculated according to marks obtained.
  • Step 4: If the same formula is to be used in other cells then click on fill handle and drag the mouse in those cells.

Formatting

A properly formatted and better layout worksheets makes your information easier to read, more informative and attractive. You can format a cell or range of cells or the entire worksheet in many ways. Formatting includes font name, size of the font, alignment, borders, numbers etc.
The following figure shows the formatting tool bar:
  • Step 1: Select the cell or range of cells where you want to change the font name.
  • Step 2: Select the font name and size from the font group of home menu's ribbon.
v

Sorting

Sorting refers to the arrangement of the data records in the ascending or descending order on the basis of numeric or alphabetic fields.
Follow the given steps for data sorting:
  • Step 1: Select the data which needs to be sorted.
  • Step 2: Click on Data tab.
  • Step 3: Click on Sort button.
  • Step 4: Type criteria on the window.
  • Step 5: If the selected data are header then tick on check box 'my data has header'.
  • Step 6: Click on the sorting option A to Z or Z to A from the Sort & Filter group of Data menu's Ribbon.

d


Filling Series
In Excel, fill handle is a command that lets you fill data into your spreadsheet cells, that is based on a pattern you establish. For example, you can use this command to continue a series of numbers, text combinations, or dates.
Steps for filling series:
  • Step 1: Type two numbers in the cell from the sequential numbers.
  • Step 2: Select both cells.
  • Step 3: When you drag the cell to the right corner, the cursor changes into "+" symbol. This is called fill handle. If u click and drag the fill handle then you can get the numbers serially in different cells.

s


How to merge cells?

Merge cell is a function in the database software that allows multiple adjacent cells to combine into a single cell. This is done by selecting all cells to be merged and choosing the "Merge Cells" command. We should follow following steps to merge cells:

  • Step 1: Select two or more adjacent cells that you want to merge.
  • Step 2: Click on Merge & Center button situated at Home tab.
sc



Very Short Questions

0%
  • _________ is the popular Windows spreadsheet program that provides worksheets, charts, database and list operations, and application programming all in one software environmen

    Ms-Excel
    Ms-PoerPoint
    Ms-Acess
    Ms-word
  • Which of the following is not the advantage of Ms-EXCEL?

    We can represent data through different charts and graphs.
    We can work with two or more than two worksheet at a single time.
    We can easily perform calculations by using different functions available in this program.
    It is a software that allows us to create , edit, view, format and print text document in the computer.
  • ______ is the intersection point of a vertical column and a horizontal row.

    Cell
    Query
    None of the answers are correct
    Reference
  • _________ means the cell reference stays the same if you copy or move the cell to any other cell.

    Mixed cell reference
    Single cell reference
    Absolute cell reference
    Relative cell reference
  • ________ is a function in database software that allows multiple adjacent cells to combine into a single cell.

    Merge cells
    Formatting
    Filling series
    Adding text
  • ______ is a command that lets you to fill data in to your spreadsheet cells, that is based on a pattern you establish.

    Command Handle
    File Handle
    Page Handle
    Cell Handle
  • ________ is an entry that consists of values, address or range of cells.

    Function
    Formula
    Command
    Code
  • __________ refers to the arrangement of the data records in the ascending or descending order on the basis of numeric or alphabetic fields.

    Merging
    Adding
    Formatting
    Sorting
  • Ms Excel supports pictorial presentation of your data entered on a worksheet which is called:

    Tool
    Bar Graph
    Chart
    None of the answers are correct
  • When you insert an Excel file into a Word document, the data are

    Hyperlinked


    Linked


    Embedded


    Placed in a word table


  • Each excel file is called a workbook because _______.

    it can contain many sheets including worksheets and chart sheets


    it can be modified


    it can contain text and data


    you have to work hard to create it


  • How are data organized in a spreadsheet?

    Rows and columns


    Height and width


    Lines and spaces


    Layers and planes


  • Which of the following is not an option in the spelling dialog box?

    Ignore all


    Ignore


    Change


    Edit


  • If you press _______, the cell accepts your typing as its contents.

    Enter


    TAB


    Insert


    Ctrl + Enter


  • What is the keyboard shortcut (button or buttons to be pressed) for creating a chart from the selected cells?

    F5


    F7


    F3


    F11


  • Excel is a _________.

    spreadsheet


    none of these


    word processor


    graphic program


  • You scored /16


    Take test again

ASK ANY QUESTION ON Spreadsheet

No discussion on this note yet. Be first to comment on this note