SPREADSHEETS

Spreadsheets are application packages used for manipulation of figures. A spreadsheet usually consists of a series of rows and columns. The figures or text are inserted into cells.  Examples  include  Ms  Excel,  Lotus  1-2-3,Supercalc,  Quattro  Pro.From  the examples given, we shall tackle Ms Excel.

Applications of Spreadsheets

  1. Can be used to record sales, produce invoices and compile statements.
  2. Researchers can compile and analyze their results.
  3. Teachers can compile their students‟ marks and  produce overall  results.
  4. Clerks and secretaries can easily create tables of figures and manipulate.

6.1      MICROSOFT EXCEL START EXCEL

Option 1

Click the start button moves to programs move to Microsoft excel and click

Option 2

Click the excel button on the Microsoft shortcut if only if the option available

6.2      THE EXCEL WINDOW

Window

When you work in Excel, you use workbook files to hold your information. Each workbook consists of several worksheets made up rows and columns of information.

A   workbook   therefore   allows   you   to   organize   various   kinks   of   related information in a single file (or workbook)

Worksheet

A worksheet is one sheet in an Excel workbook. Each worksheet consists of 256 columns and 65,536 rows.

Columns

Columns are the vertical divisions of a worksheet that are identified by letters. The columns begin with A and proceed through the alphabet. The 27th column is AA followed by AB, AC, and this convention for naming continues through the entire alphabet until you end up with the last column (column 256) which is designated IV.

Rows

Rows are the horizontal divisions of a worksheet and are identified by numbers.

Cells

A cell is the intersection of a row and a column. Each cell has an address that consists of the column letter and row number (A1, B3, C5 and so on)

Each cell is capable of containing different types of information e.g. text, number, times, formulas. Excel data basically comes in two varieties: labels and values.

A label is a text entry consisting of alphanumeric characters. It is called a label because it typically provides descriptive information such as the name of a place, person, e.t.c. A label has no numerical significance in Excel.

A value is data that has numerical significance. These include numbers, dates and times that you enter on your worksheet. Values can be acted on by formulas and functions.

The figure below shows the elements of an Excel window

Element                                                                     Description

Formular bar                                    When  you  enter  information  into  a  cell,  it appears in  the  Formula  bar.You can use  the formula bar to edit  the   data   later.   The   cell‟s location also appears.

Column Headings                           The  letters  across  the  top  of  the  worksheet, which identify the columns in the worksheet.

Row Headings                                 The numbers down the side of the worksheet, which Identify the rows in the worksheet.

Cell Selector                                     The dark outline that indicates the active cell. It Highlights the cell you are currently working in.

Worksheet tabs                                These tabs help you move from worksheet to Worksheet within the workbook. The active Worksheet is displayed in bold.

Active cell                                         It indicates the cell in which the typed data will be entered. It is also known as the current cell.

Create a new workbook

Option 1

  1. On the office menu, click New
  2. To create a new blank workbook, click the General tab and then double click the workbook icon.

Saving a workbook

  1. In the office menu click save as.
  2. In the save in text box click and specify the location to save in.
  3. In the file name text box type the name of the file.
  4. Click save.

NB

The  first  time  you  click  „save as‟  from  the  file menu, the  Documents folder  is

displayed by default as the folder to save in.

Close a workbook

On the file menu click close.

6.3      WORKING WITH WORKBOOKS AND WORKSHEETS Moving around in a worksheet

To move between cells on a worksheet, click any cell or use the arrow keys. When you move to a cell, it becomes the active cell (the selected cell in which data is entered when you begin typing. Only one cell is active at a time .A heavy border bound the active cell)

To see the different area of the sheet, use the scroll bars.

Switch to another sheet in a workbook

Click the sheet tab

(A tab near the button of a workbook window that displays the name of a sheet To display a shortcut menu, click a tab with the right mouse button .To scroll through the sheet tabs, use the tab scrolling buttons to the left of the tabs)

Insert a new worksheet

On the home tab, click insert and then chose worksheet

Delete sheets from a workbook

  1. Select the sheets you want to delete.
  2. On the home tab select delete sheet.

Rename a sheet

  1. Double click the sheet tab.
  2. Type a new name over the current name.

6.4      ENTERING DATA

Enter numbers, text, date, or time

  1. Click the cell where you want to enter data.
  2. Type the data and press ENTER or TAB.

Use a slash or a hyphen to separate the parts of a date, for example, type 8/6/99 or jun-99

To enter a time based on the 12-hour clock, type a space and then a or p after the time for example, 9.00 p. Otherwise, Microsoft Excel enters the time as AM.

Enter a formula

  1. Click the cell in which you want to enter the formula.
  2. Type =(an equal sign)
  3. Enter the formula.
  4. Press ENTER.

Enter the same data into several cells at once

  1. Highlight the cells where you want to enter data. The cells can be adjacent or nonadjacent
  2. Type the data and press CTRL+ENTER

Fill in a series of numbers, dates or other items

  1. Select the first cell in the range you want to fill and enter the starting value for the series.

To increment the series by a specified amount, select the next cell in the range and enter the next item in the series. The difference between the two starting items determines the amount by which the series is incremented.

  1. Select the cell or cells that contain the starting values.
  2. Drag the fill handle over the range you want to fill.

To fill in increasing order, drag down or to the right.

To fill in decreasing order, drag up or to the left.

Cancel or undo an entry

To cancel an entry before you press ENTER, press ESC.

To undo a complete entry, click Undo button on the standard tool bar.

Tips on entering numbers

To avoid entering a fraction as a date, precede fractions with a 0 (zero); for example, type0 ½ (there is a space between zero and ½)

Precede  negative  numbers with a  minus sign (-),  or  enclose  the  numbers in parentheses ().

Select cells, ranges, rows and columns

To select                                              Do this

A single cell                                      Click the cell, or press the arrow keys to move to the cell.

A range of cells                                  Click the first cell of the range and then drag to the last cell.

All cells on a worksheet                    Click the select All button.

Nonadjacent cells or cell ranges       Select the first cell or range of cells, and thenhold down CTRL and select the other cells or ranges.

A large range of cells                          Click the first cell in the range, and then hold down SHIFT key and click the last cell in the range.

An entire row                                      Click the row heading.

An entire column                                Click the column heading.

Adjacent rows or columns                 Drag across the row or column headings.

Non-adjacent rows or columns          Select the first row or column, and then hold down CTRL and select the other rows or columns.

Select sheets in a workbook

If you select more than one sheet, Microsoft Excel repeats the changes you make to the active sheet on all other selected sheets. These changes may replace data on other sheets.

To select                                                        Do this

A single sheet                                               Click the sheet tab.

Two or more adjacent sheets                     Click the tab for the first sheet and then hold down SHIFT and click the tab for the last sheet.

Two or more nonadjacent sheets  Click the tab for the first sheet and then hold down

CTRL and click the tabs for the other sheets. All sheets in a workbook Right click a sheet tab and then click Select All

Sheets on the shortcut menu.

To cancel a selection of multiple sheets in a workbook, click any unselected sheet. If no unselected sheet is visible, right click the tab of a selected sheet then click Ungroup Sheets on the shortcut menu.

Clear or delete cells, rows or columns

When you delete cells, Microsoft Excel removes them from the worksheet and shifts the surrounding cells to fill the space. When you clear cells, you remove the cell contents (formulas and data), formats or comments but leave the blank cells on the worksheet.

To clear contents, format or comments from cells

1. Select the cells, rows or columns that you want to clear.

2. Press the delete key on your keyuboard.

Delete cells, rows or columns

  1. Select the cells, rows or columns you want to delete.
  2. Home tab, click Delete.

Undo mistakes

To undo recent actions one at a time, click Undo next to the save icon.

To undo several actions at once, click the arrow next to Undo button and select from the list. Microsoft Excel reverses the selected action and all actions above it.

To undo several actions at once, click the arrow next to Undo button on the standard toolbar and select from the list. Microsoft Excel reverses the selected action and all actions above it.

Insert cells, rows or columns

You can insert blank cells, rows and columns and fill them with data.

Insert blank cells

  1. Select a range of existing cells where you want to insert the new blank cells.
  2. Select the same number of cells as you want to insert.
  3. On the insert icon under the home tab, click cells.
  4. Click Shift cells right or shift cells down.

Insert rows

  1. To insert a single row, click a cell in the row immediately below where you want the new row. For example, to insert a new row above Row 5,click a cell in Row 5.

To insert multiple rows, select rows immediately below where you want the new rows, select the same number of rows you want to insert.

  1. On the Insert menu, click Rows.

Insert columns

1.To insert a single column, click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B

To insert multiple columns, select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert.

2. On the Insert icon on the home tab, click Columns.

6.5      FORMATTING A WORKSHEET

Change the size, font, colour, or other text format

You can specify a font, font size and font color by clicking buttons on the home tab.

Change the font or font size

  1. Select whole cells or the specific text in a single cell that you want to format.
  2. In the Font box, click the font you want.
  3. In the Font size box, click the font size you want (on the home tab)

Make selected text or numbers bold, italic or underlined

  1. Select whole cells or the specific text in a single cell that you want to format.
  2. On the home tab, click a button for the format you want.

To make text                                        Click

Bold                                                           B

Italic                                                           I

Underlined                                               U

Change the text color

  1. Select whole cells or the specific text in a single cell that you want to format.
  2. To apply the recently most selected color, click Font Color A

To apply a different color, click the arrow next to Font Color A and then click a color on the palette.

6.6      APPLYING BORDERS To apply border styles

  1. Click the Border tab in the home tab.
  2. Click the line style you want and then click a button to indicate the border placement.

To apply borders to selected cells that contain rotated text

  1. Click Cells on the Format icon under the home menu.
  2. Click the  Border  tab  and  then  use  the  Outline  and  Inside  buttons  under Presets.

The borders are applied to the edges of the cells which are rotated to the same degree as the rotated text.

To change the line style of an existing border

  1. Select the cells on which the border is displayed.
  2. On the Border tab (Cells dialog box, Format menu) click the new line style in the style box and then click the border you want to change in the cells diagram under border.

Change column width and row height

You can adjust the width of columns and the height of rows. You can also define the default width of columns for a worksheet. Defining the default column width adjusts all columns to the same width except columns that have previously been changed.

Change column width

Using different options

  1. Drag the  boundary on the  right side  of  the  column heading  until  the column is the width you want.
  2. To change the column width for multiple columns, select the columns you want to change. Then drag a boundary at the right of a selected column heading.
  3. To change the column width for all columns on the worksheet, click the Select All button (at the left edge of the borders) and then drag the boundary of any column heading.
  4. To make the column width fit the contents, double-click the boundary to the right of the column heading

Change row height

Using different options

  1. Drag the boundary below the row heading until the row is the height you want.
  2. To change the row height for multiple rows, select the rows you want to change then drag a boundary below a selected row heading.
  3. To change the row height for all rows on the worksheet, click the Select All button (at the left edge of the borders) and then drag the boundary below any row heading.
  4. To make the row height fit the contents, double-click the boundary below the row heading.

THE FILL HANDLE

The  fill  handle  enables  you  to  extend  a  series.  It  is  also  used  for  copying formulas.

Procedure

  1. Position the mouse pointer right on the block like mark in the bottom right corner of the active cell.
  2. Click and drag to extend a series.

6.7      OPERATORS

Operators are signs or symbols which specify the type of a calculation that you may perform in the elements of a formula.

There are four different types of calculation operators i.e.

  1. Arithmetic
  2. Comparison
  3. Text and
  4. Reference

ARITHMETIC OPERATIONS

They   perform   basic   mathematical   operations   e.g.   addition,   subtraction, multiplication and division.

COMPARISION OPERATORS

They compare two values then produce a logical value i.e. TRUE or FALSE.

OPERATOR                                EXAMPLE

= (Equal to)                                       A1=B1

> (Greater than)                               A1>B1

<  (Less than)                                    A1<B1

>=(Greater or equal to)                               A1>=B1

<=(Less or equal to)                                    A1<=B1

<>(Not equal to)                              A1<>B1

REFERENCE OPERATORS

OPERATOR                                                                                  EXAMPLES

:(COLON) Range operators which

produces one reference for all the cells                                            B5:B15

between two references.

 

,(COMMA) Union operator which combines

multiple references into one.                                                           Sum (B5:B15, D5:D1)

FORMULAS

A formula is an equation that analyses data in a worksheet. Formulas perform operations e.g. addition, multiplication, comparison etc. They can refer to other cells on the same worksheet as well as other sheets in the same workbook or even in other workbooks .A formula must always begin with an equal sign or symbol e.g.=10-5. The result of the formula is then displayed in the cell.

You can use parentheses to change the syntax (structure or order of elements) e.g. in the formula =5+2*3 Excel carries multiplication first .If the parentheses are used, the syntax changes. E.g. =(5+2)*3

FUNCTIONS

This is a special kind of predefined by Excel

The specific arguments required by a function depend on what the function does.

THE SUM FUNCTION

The sum function sums up a range total. This function saves time e.g. instead of creating  a  formula  =  A1+B1+C1+A2+B2+C3+A3+B3+C3  a  sum  function  will make it easy i.e.

=SUM (A1:C3)

Please, note the reference operator :(colon)

RETURN VALUES OF FUNCTIONS

AVERAGE: If cell A1 contains value 12 and B1 value 8,the function =average (A1:B1) returns 10.

MODE: This is the most frequently occurring or repetitive value in an array of data

syntax e.g. =mode (10,3,4,3,5,3,7,3,4)

N/B If a data set has no duplicate values, mode returns the # N/A error value.

MEDIAN: This is the middle value or the number in the middle of a set of numbers

Syntax e.g. =median (2,4,6,8,10) Numbers in the middle e.g.

=Median (1,2,3,4,5) equal 3

=Median (1,2,3,4,5,6,) equals to 3.5 and this is the average of 3+4

 

Excel Error Values

The various types of errors you may encounter as you use formulas are:

Error                          Description

#DIV/0!                    The formula is attempting to divide by zero. Check the cell references for blanks or zeros that may have resulted if you deleted a cell referenced by the formula.

#N/A                        The formula refers to a cell with a #N/A entry or a cell that

contains no value. This error warns you that not all the data referenced by a formula is available.

#NAME?                   Excel  doesn‟t  recognize a name  you  entered  in a formula.

Verify that all names in the formula exist and define any missing names.

If applicable, verify that you used the correct function name.

#NULL!                     The formula specifies  two areas that don‟t intersect. Check to see

if  you  entered   the  cell  or  range  reference  incorrectly.

Remember to use commas (not spaces) between function arguments.

#NUM!                      There  is  a  problem  with  a  number  used  in  the  formula.

Check for the correct use of function arguments.

#REF!                         A  cell  reference  in  the  formula  is  incorrect.  Check  for changes to cell reference caused by deleting cells, rows or columns referenced by the formula.

#VALUE!                  The  formula  contains  the  wrong  type  of  argument  or operator. Check for the correct syntax of the formula.

Merge cells to span several columns or rows

Merging combines two or more selected adjacent cells to create a single cell. The resulting merged cell contains the upper left-most data in the selection which is centered within the cell. The cell reference for a merged cell is the upper-left cell in the original selected range.

  1. Select the cells that you want to merge.
  2. To merge cells in a row and center the cell contents, click Merge and Center.

To merge any selection of cells within a row or column, click merge cells on the home tab.

6.9      WORKING WITH CHARTS

You can display Microsoft Excel data graphically in a chart. Charts are linked to the worksheet data they are created from and are updated when you change the worksheet data. You can create charts from cells or ranges that are not next to one another.

  1. Select the cells that contain the data that you want appear in the chart.
  2. Under the insert tab select the kind of chart you want.
  3. The chart is prepared for you.

Create a chart from non-adjacent selections

  1. Select the first group that contains the data you want to include.
  2. While holding down CTRL key, select any additional cell groups you want to include.
  3. Under the insert tab select the kind of chart you want.
  4. The chart is prepared for you.

Move and resize chart items by the use of the mouse

You can use the mouse to resize and move the chart area, the plot area and the legend. Microsoft Excel automatically sizes titles to accommodate their text. You can move titles with the mouse but not resize them.

1.Click the chart item.

2.To move a chart item, point to the item and then drag it to another location.

To resize a chart item, point to a sizing handle.

When the mouse pointer changes to a double-headed arrow, drag the sizing handle until the item is the size you want.

Rotate text in a chart title or along an axis

You  can  rotate   or  “angle” text  in  a  chart  or  along  an  axis.  However, you cannot rotate legend text.

  1. Click the axis or the title you want to format.
  2. If you clicked an axis, click Axis on the Format menu.
  3. Click the Alignment tab.
  4. To rotate text, under Orientation, click a degree point or drag the indicator to the position you want.

About using a list as a database

In Microsoft Excel, you can easily use a list as a database. When you perform database tasks such as finding, sorting or subtotaling data, Microsoft Excel automatically recognizes the list as a database and uses the following list elements to organize the data.

The columns in the list are the fields in the database.

The columns labels in the list are the fields names in the database.

Each row in the list is a record in the database.

Sorting a list

You can rearrange the rows or columns of a list based on the values in the list by sorting. When you sort, Microsoft Excel rearranges rows, columns or individual cells by using the sort order that you specify. You can sort lists in ascending (1 to 9,A to Z) or descending (9 to 1,Z to A) order and sort based on the contents of one or more columns.

Sort in ascending or descending order                                                       A               Z Z    A

1.Click a cell in the column you would like data sort.

2.Click Sort ascending or descending button on the standard toolbar.

Sort columns based on the contents of rows

1.Click a cell in the list you want to sort.

2.On the Data tab, click sort.

3.Click Options.

4.Under Orientation, click sort left to right and then click Ok.

5.In the Sort by and Then by boxes, click the rows you want to sort.

6.Click Ok.

Using apply filter

Autofilter

Displays only those rows that match the value in the active cell and inserts

Autofilter arrows to the right of each column label.

Show all

Displays all of the rows in a filtered list.

 

(Visited 186 times, 1 visits today)
Share this: