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
- Can be used to record sales, produce invoices and compile statements.
- Researchers can compile and analyze their results.
- Teachers can compile their students‟ marks and produce overall results.
- 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
- On the office menu, click New
- To create a new blank workbook, click the General tab and then double click the workbook icon.
Saving a workbook
- In the office menu click save as.
- In the save in text box click and specify the location to save in.
- In the file name text box type the name of the file.
- 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
- Select the sheets you want to delete.
- On the home tab select delete sheet.
Rename a sheet
- Double click the sheet tab.
- Type a new name over the current name.
6.4 ENTERING DATA
Enter numbers, text, date, or time
- Click the cell where you want to enter data.
- 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
- Click the cell in which you want to enter the formula.
- Type =(an equal sign)
- Enter the formula.
- Press ENTER.
Enter the same data into several cells at once
- Highlight the cells where you want to enter data. The cells can be adjacent or nonadjacent
- Type the data and press CTRL+ENTER
Fill in a series of numbers, dates or other items
- 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.
- Select the cell or cells that contain the starting values.
- 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
- Select the cells, rows or columns you want to delete.
- 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
- Select a range of existing cells where you want to insert the new blank cells.
- Select the same number of cells as you want to insert.
- On the insert icon under the home tab, click cells.
- Click Shift cells right or shift cells down.
Insert rows
- 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.
- 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
- Select whole cells or the specific text in a single cell that you want to format.
- In the Font box, click the font you want.
- In the Font size box, click the font size you want (on the home tab)
Make selected text or numbers bold, italic or underlined
- Select whole cells or the specific text in a single cell that you want to format.
- 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
- Select whole cells or the specific text in a single cell that you want to format.
- 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
- Click the Border tab in the home tab.
- 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
- Click Cells on the Format icon under the home menu.
- 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
- Select the cells on which the border is displayed.
- 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
- Drag the boundary on the right side of the column heading until the column is the width you want.
- 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.
- 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.
- 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
- Drag the boundary below the row heading until the row is the height you want.
- To change the row height for multiple rows, select the rows you want to change then drag a boundary below a selected row heading.
- 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.
- 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
- Position the mouse pointer right on the block like mark in the bottom right corner of the active cell.
- 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.
- Arithmetic
- Comparison
- Text and
- 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.
- Select the cells that you want to merge.
- 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.
- Select the cells that contain the data that you want appear in the chart.
- Under the insert tab select the kind of chart you want.
- The chart is prepared for you.
Create a chart from non-adjacent selections
- Select the first group that contains the data you want to include.
- While holding down CTRL key, select any additional cell groups you want to include.
- Under the insert tab select the kind of chart you want.
- 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.
- Click the axis or the title you want to format.
- If you clicked an axis, click Axis on the Format menu.
- Click the Alignment tab.
- 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.
