Share this:

CHAPTER 2

 

SPREADSHEETS

 

This chapter introduces the student to what Spreadsheets are, the components, application areas and the practical usage.

 

2.0 Definition of Spreadsheets

2.1 Components of a spreadsheet

2.2Application areas of spreadsheets

2.3 create and edit a Spreadsheet

2.4 explain different cell data types

2.5 Apply cell referencing

2.6Apply functions and formulae

2.7 Apply data management skills

2.8 Apply charting and graphing skills

2.9 Print worksheet and graph

ecolebooks.com

 

 

  1. Definition of Spreadsheets

A spreadsheet is a grid that organizes data into columns and rows. Spreadsheets make it easy to display information, and people can insert formulas to work with the data. The columns are marked by letters and the rows are marked by numbers. For example column A, B, C beyond letter 26 i.e Z, the columns are marked as AA, AB etcetera. The intersection of a row and a column is often known as a cell. Each cell has a name known a cell reference. Each cell is named by the column and the row in which the cell falls under. For example, Cell A3 means that it is on column A and row 3. The cell where the cursor is placed operations is known as an Active cell.

 

Image From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.com

 

 

 

 

 

 

2.1 Components of a Spreadsheet

A spreadsheet is made up of three key components. These are: worksheet, database and graphs.

2.1.1 Worksheet

A spreadsheet is made up of many worksheets as shown above in the sheet tab. The total sum of all the worksheets, make up a workbook. The figure above is a workbook. Each of these worksheets may hold different sets of information. You can add as many worksheets as you wish in a workbook.

2.1.2 Database

A spreadsheet can hold many records properly ordered in a given format. These records are about particular people or things which make up the records, classified under specified fields. This is a database. Such records are related and could be placed together in one worksheet to mean a file of an entity.

 

2.1.3 Graphs

A spreadsheet provides a pictorial representation of data in the form of a graph. This is a key feature of a spreadsheet. Whereas words describe analysed data, graphs provide a quick way to visualize the same output.

 

2.2. Application areas of a spreadsheet

Spreadsheets are often used in the following application areas:

  1. Statistical data analysis
  2. Accounting
  3. Data management
  4. Forecasting(what if analysis)
  5. Scientific application

     

2.2.1 Statistical Analysis

2.2.2 Accounting

2.2.3 Data management

2.2.4 Forecasting (what if analysis)

2.2.5 Scientific application

 

2.3 Creating a worksheet/workbook

2.3.1 Getting started

For purposes of this syllabus we shall use Microsoft Office Excel (MS-Excel).

  • Go to start button
  • Click Microsoft Office Excel
  • The worksheet below will open

 

2.3.2 Worksheet layout

The following worksheet loads when you click on MS-Excel.

The parts of this worksheet has been explained above, section 2.0. The menus will be explained as we move along. The office button, operations button such as exit, minimize, maximize and restore remain the same as in Ms-Word.

 

Image From EcoleBooks.com

 

 

 

 

2.3.3 Running the program

 

Practical Learning: Starting Microsoft Excel

To start Microsoft Excel, from the Taskbar, click
Start -> (All) Programs -> Microsoft Office ->

Microsoft Office Excel
 

Image From EcoleBooks.com

 

 

 

 

 

 

 

 

 

 

The Office Button

 
 

When Microsoft Excel opens, it displays an interface divided

in various sections.

The top section displays a long bar also called the title bar.

The title bar starts on the left side with the Office Button Image From EcoleBooks.com.

If you position the mouse on it, a tool tip would appear:

Image From EcoleBooks.com

The Options of the Office Button

When clicked (with the mouse’s left button), the Office Button

displays a menu:

Image From EcoleBooks.com

As you can see, the menu of the Office Button allows you to

perform the routine Windows operations of a regular application,

including creating a new document, opening an existing file, or

saving a document, etc. We will see these operations in future

lessons.

If you right-click the office button, you would get a short menu:

Image From EcoleBooks.com

We will come back to the options on this menu.

The Quick Access Toolbar

 
 

Introduction

On the right side of the Office Button, there is the Quick Access

Toolbar Image From EcoleBooks.com.

Like a normal toolbar, the Quick Access displays some buttons.

You can right-click the Quick Access toolbar. A menu would

appear:

Image From EcoleBooks.com

If you want to hide the Quick Access toolbar, you can

right-click it and click

Remove Quick Access Toolbar. To know what a button is

used for, you can position the mouse on. A tool tip would

appear. Once you identify the button you want,

you can click it.

Adding a Button to the Quick Access Toolbar

By default, the Quick Access toolbar is equipped with three

buttons: Save,

Undo, and Redo. If you want to add more buttons or more

options, you can right-click the Quick Access toolbar and

click Customize Quick

Access Toolbar… This would display the Excel Options dialog

box:

Image From EcoleBooks.com

To add a button to the Quick Access toolbar, on the left list of

Add, click an option and click Add. After making the selections,

click OK.

To remove a button from the Quick Access toolbar, right-click

it on the Quick Access toolbar and click Remove From Quick

Access Toolbar.

The Quick Access Button

On the right side of the Quick Access toolbar, there is the

Customize button with a down-pointing arrow. If you click or

right-click this button, a menu would appear:

Image From EcoleBooks.com

The role of this button is to manage some aspects of the top

section of Microsoft Excel, such as deciding what buttons

to display on the Quick Access toolbar. For example,

instead of using the Customize Quick Access

Toolbar menu item as we saw previously, you can click an

option from that menu and its corresponding button would

be added to the Quick Access toolbar. If the options on the menu are nor enough, you can

click either Customize Quick Access Toolbar or More

Commands…

This would open the Excel Options dialog box.

The main or middle area of the top section displays the name

of the application: Microsoft Excel. You can right-click the

title bar to display a menu that is managed by the operating

system.

On the right side of the title bar, there are three system buttons

that allow you to minimize, maximize, restore, or close

Microsoft Access.

Under the title bar, there is another bar with a Help button on

the right side.

The Ribbon

 
 

Introduction

Under the title bar, Microsoft Excel displays a long bar called the Ribbon:

Image From EcoleBooks.com

Minimizing the Ribbon

By default, the Ribbon displays completely in the top section

of Microsoft Excel under the title bar. One option is to show

it the way the main menu appeared in previous versions of

Microsoft Excel. To do this:

  • Right-click the Office Button, the Quick Access toolbar,

    or the Ribbon itself, and click Minimize the Ribbon

  • Click or right-click the button on the right side of the

    Quick Access toolbar:

Image From EcoleBooks.com

This would display the Ribbon like a main menu:

Image From EcoleBooks.com

To show the whole Ribbon again:

  • Right-click the Office Button, the Quick Access toolbar,

    or one

    of the Ribbon menu items, and click Minimize the

    Ribbon to remove the check mark on it

  • Click or right-click the button on the right side of the

    Quick Access toolbar and click Minimize the Ribbon to

    remove the check mark on it

  • Double-click one of the menu items of the Ribbon

Changing the Location of the Ribbon

By default, the Quick Access toolbar displays on the title bar

and the Ribbon displays under it. If you want, you can switch

their locations.

To do that, right-click the Office Button, the Quick Access

toolbar, or the Ribbon, and click Show Quick Access Toolbar

Below the Ribbon:

Image From EcoleBooks.com

To put them back to the default locations, right-click the Office

Button, the Quick Access toolbar, or the Ribbon, and click

Show Quick Access

Toolbar Above the Ribbon.

The Tabs of the Ribbon

The ribbon is a type of property sheet made of various property

pages.

Each page is represented with a tab. To access a tab:

  • You can click its label or button, such as Home or Create
  • Image From EcoleBooks.comYou can press Alt or F10. This would display the access key of each tab:
     
    • To access a tab, you can press its corresponding letter

      on The keyboard. For example, when the access keys

      display, if you press Home, the Home tab would display

    • If your mouse has a wheel, you can position the mouse

      anywhere on the ribbon, and role the wheel. If you role

      the wheel down, the next tab on the right side would

      be selected. If you role the wheel up, the previous tab

      on the left would be selected. You can keep rolling

      the wheel until the desired tab is selected

To identify each tab of the Ribbon, we will refer to them by

their names.

The Sections of a Tab

Each tab of the ribbon is divided in various sections, each

delimited by visible borders of vertical lines on the left and

right.Each section displays

a title in its bottom side. In our lessons, we will refer to

each section by

that title. For example, if the title displays Font, we will call

that section,

“The Font Section”.

Some sections of the Ribbon display a button Image From EcoleBooks.com. If you see such

a button,

you can click it. This would open a dialog box or a window.

The Buttons of the Ribbon

Since there are various buttons and sometimes they are

unpredictable,

to know what a particular button is used for, you can

position your mouse on it. A small box would appear to let

you know what that particular button is used for; that

small box is called a tool tip:

Image From EcoleBooks.com

 

You can also use context sensitive help in some cases to

get information about an item.

You can add a button from a section of the Ribbon to the

Quick Access toolbar. To do that, right-click the button on

the Ribbon and click Add to Quick Access Toolbar:

Image From EcoleBooks.com

Remember that, to remove a button from the Quick Access

toolbar, right-click it on the Quick Access toolbar and click

Remove From Quick

Access Toolbar.

The More Buttons of the Ribbon

In some sections of the Ribbon, on the lower-right section,

there is a button:

Image From EcoleBooks.com

That button is used to display an intermediary dialog box for

some action.

We will see various examples as we move on.

The Size of the Ribbon

When Microsoft Excel is occupying a big area or the whole

area of the monitor, most buttons of the Ribbon appear

with text. Sometimes you may need to use only part of the screen.

That is, you may need to narrow the

Microsoft Excel interface. If you do, some of the buttons may

display part of their appearance and some would display only

an icon. Consider the difference in the following three

screenshots:

Image From EcoleBooks.com

Image From EcoleBooks.com

Image From EcoleBooks.com

In this case, when you need to access an object, you can

still click it or click its arrow. If the item is supposed to have

many objects, a new window may appear and display those

objects:

From this:

Image From EcoleBooks.com

To this:

Image From EcoleBooks.com

The Work Area

 
 

The Name Box

Under the Ribbon, there is a white box displaying a name like

A1(it may not display A1…), that small box is called the Name

Box:

Image From EcoleBooks.com

The Insert Function Button

On the right side of the Name box, there is a gray box with

an fx button. That fx button is called the Insert Function button.

The Formula Bar

On the right side of the Insert Function button is a long empty

white box or section called the Formula Bar:

Image From EcoleBooks.com

You can hide or show the Formula Bar anytime. To do this,

on the Ribbon,

click View. In the Show/Hide section:

  • To hide the Formula Bar, remove the check mark on the

    Formula Bar check box

  • To show the Formula Bar, check the Formula Bar check

    box

The Column Headers

Under the Name Box and the Formula bar, you see the column

headers. The columns are labeled A, B, C, etc:

Image From EcoleBooks.com

There are 255 of columns.

The Row Headers

On the left side of the main window, there are small boxes

called row

headers. Each row header is labeled with a number, starting

at 1 on top, then 2, and so on:

Image From EcoleBooks.com

The Cells

The main area of Microsoft Excel is made of cells. A cell is the

intersection of a column and a row:

Image From EcoleBooks.com

A cell is identified by its name and every cell has a name.

By default,Microsoft Excel appends the name of a row to the

name of a column to identify a cell. Therefore, the top-left

cell is named A1. You can checkthe name of the cell in

the Name Box.

Practical Learning: Using Cells

  1. Click anywhere in the work area and type A
    (It doesn’t matter where you click and type)
  2. Click another part of the worksheet and type 42XL
  3. Click again another place on the worksheet type

     

    Fundamentals

    and press Enter

The Scroll Bars

On the right side of the cells area, there is a vertical scroll bar

that allows you to scroll up and down in case your document

cannot display everything at a time:

Image From EcoleBooks.com

In the lower right section of the main window, there is a

horizontal scroll bar that allows you to scroll left and right

if your worksheet

has more items than can be displayed all at once:

Image From EcoleBooks.com

Sometimes the horizontal scroll bar will appear too long or

too narrow

for you. If you want, you can narrow or enlarge it. To do this,

click and drag the button on the left side of the horizontal

scroll bar:

Image From EcoleBooks.com

The Sheet Tabs

On the left side of the horizontal scrollbar, there are the

worksheet tabs:

Image From EcoleBooks.com

By default, Microsoft Excel provides three worksheets to start

with. You can work with any of them and switch to another

at any time by clicking its tab.

The Navigation Buttons

On the left side of the worksheet tabs, there are four navigation

buttons:

Image From EcoleBooks.com

If you happen to use a lot of worksheets or the worksheet names

Are using too much space, which would result in some

worksheets being

hidden under the horizontal scroll bar, you can use the

navigation buttons to move from one worksheet to another.

The Status Bar

Under the navigation buttons and the worksheet tabs, the

Status Bar provides a lot of information about the job that

is going on.

Microsoft Excel File Operations

 
 

Saving a File

A Microsoft Excel file gets saved like any traditional Windows

file.

To save a file:

  • You can press Ctrl + S
  • On the Quick Access Toolbar, you can click the Save

    button Image From EcoleBooks.com

  • You can click the Office Button and click Save Image From EcoleBooks.com

Two issues are important. Whenever you decide to save a

file for the first time, you need to provide a file name and

a location. The file name helps the computer identify that

particular file and register it.

A file name can consist of up to 255 characters, you can

Include spaces and dashes in a name. Although there are

many characters you can use in a name (such as

exclamation points, etc), try to avoid fancy names.

Give your file a name that is easily recognizable, a little

explicit. For example such names as Time Sheets,

Employee’s Time

Sheets, GlobalEX First Invoice are explicit enough. Like any

file of the Microsoft Windows operating systems, a Microsoft

Excel file has an extension, which is .xls but you don’t have

to type it in the name.

The second important piece of information you should pay

attention to when saving your file is the location. The location

is the drive and/or the folder where the file will be saved.

By default, Microsoft Excel saves its files in the My

Documents folder. You can change that in the Save As

dialog box. Just click the arrow of the Save In combo box

and select the

folder you want.

Microsoft Excel allows you to save its files in a type of your

choice.

To save a file in another format:

  • Press F12 or Shift + F12
  • You can click the Office Button and position the mouse

    on Save As and select the desired option:
     
    Image From EcoleBooks.com

  • On the Quick Access Toolbar, you can click the Save button Image From EcoleBooks.com.

    Then, in the Save As dialog box, click the arrow of the Save As

    Type combo box and select a format of your choice

There are other things you can do in the Save As dialog box:

Image From EcoleBooks.com

Practical Learning: Saving a File

  1. To save the current document, on the Quick Access

    Toolbar,

  2. click the Save button Image From EcoleBooks.com
  3. Type Fundamentals
  4. Click the Save button

     

Saving under a Different Name and New Folder

You can save a file under a different name or in another

location,

this gives you the ability to work on a copy of the file while

the original is intact.

There are two primary techniques you can use to get a file in two

names or the same file in two locations. When the file is not

being used by any application, in Windows Explorer

(or in My Computer, or in My Network Places, locate the file,

right-click it and choose Copy.

To save the file in a different name, right-click

in the same folder and choose Paste. The new file will be named

Copy Of… You can keep that name or rename the new file with

a different name (recommended).

To save the file in a different location, right-click in the

appropriate folder and click Paste; in this case, the file will

keep its name.

In Microsoft Excel, you can use the Save As dialog box to

save a file in

a different name or save the file with the same name

(or a different name) in another folder. The Save As dialog

box also allows you to create a new folder while you are

saving your file (you can even use this technique to create

a folder from the application even if you are not

saving it; all you have to do is create the folder, click

OK to register the folder, and click Cancel on the Save As

dialog box).

Practical Learning: Save a File With Different Settings

  1. To save this file using a different name, click the Office

    Button, position the mouse on Save As, and click

    Excel 97-2003 Workbook

  2. Change the name of the file to

    Employment Application

  3. On the toolbar of the Save As dialog box, click the
  4. Create New

    Folder button (if you have a hard time finding it,

    press Alt + 5

  5. Type My Workbooks and press Enter. The My Files

    folder should now display in the Save In combo box.

    If you clicked Cancel or pressed Esc now to dismiss the

    Save As dialog box, the computer

    would still keep the folder

  6. After making sure that the My Files folder displays in

    the Save In

    combo box, click the Save button

Opening a File

The files you use could be created by you or someone else.

They could be residing on your computer, on another

medium, or on a network. Once one of them is accessible, you can

open it in your application.

You can open a document either by double-clicking its icon in

Windows Explorer, in My Computer, from the Find Files Or

Folders

window, in My Network Places, or by locating it in the Open

dialog box.

To access the open dialog box, on the main menu,

click File -> Open…

You can also click the Open button on the Standard toolbar.

A shortcut to call the Open dialog box is Ctrl + O.

2.4 Cell Data Types

The data that you enter in a cell can take any of the

following for types:

Labels- these are data which are in text form. For example

titles on top of the columns or at the start of each row.

Values- these are the result from a calculation. They are

often in the form of numbers, currencies, dates and so on.

Formulae- these are data that uses operators such as

+,-,/,*, Brackets.

Functions- these are the cell data types which contains the

Formulae which the spreadsheet has memorized and is

available in the library. A function usually contains an

equal sign(=),function name such as sum, average, maximum

and so on, values to be used with the function. Example

of a function in a cell would be: =sum(A2:A4).

2.4 Cell referencing

2.4.1 Cell Addressing

A cell must have a cell address. The name of the cell. The

Cells are addressed using column letter and row number.

For example cell A3, means a cell positioned on column A

and row 3. Microsoft Excel, just like any other spreadsheets,

work with cell references instead of values. This is because

the references provide an easy way to amend contents of a

cell instead of the values.

Example of use of a cell reference:

The first uses cell references to add the two numbers. When

this happens, you can easily change the contents of any of

the two cells, and immediately the answer will change to

correspond with the change you have made. In the second

formula, even if you change the content of the formula,

the answer will not change at all.

Image From EcoleBooks.comImage From EcoleBooks.comImage From EcoleBooks.com

Image From EcoleBooks.com

 

2.4.2 Absolute Referencing

A cell whose contents need to remain constant

Throughout the calculation period is referred to as

Absolute reference. It is usually denoted by a dollar sign

thus, $B$2, this can be done simply by highlighting

the cell reference and then pressing F4 on the Keyboard

to add the dollar signs.

 

 

 

 


 

 

 

 


 




Share this:


EcoleBooks | Computer Studies Form 2 Notes : CHAPTER 2 - SPREADSHEETS

subscriber

Leave a Reply

Your email address will not be published. Required fields are marked *

Accept Our Privacy Terms.*