SPREADSHEETS.

Definition of a Spreadsheet.

A Spreadsheet is a ledger sheet that lets the user enter, edit, and manipulate numerical data.

A Spreadsheet usually consists of a series of rows & columns in which data entries can be made.

Types of Spreadsheets:

There are 2 types of spreadsheets:

• Manual spreadsheet:

A Manual spreadsheet is ledger book with many sheets of papers divided into rows and columns for entering/writing data.

The data is entered manually using a pen or pencil.

• Electronic Spreadsheet:

A computer program that looks like the manual ledger sheet with rows & columns for entering data that can be manipulated mathematically using of formulae.

Advantages of Electronic Spreadsheets over Manual Worksheets.

 

An electronic spreadsheet:

 

1. Has a large worksheet for data entry & manipulation as compared to manual worksheet.

 

2. Has inbuilt formulae called Functions that are non-existent in manual worksheets. These functions enable the user to quickly manipulate mathematical data.

 

3. Uses the power of the computer to quickly carry out operations.

 

4. Has better formatting & editing qualities than the manual worksheet.

 

5. Utilizes the large storage space available on computer storage devices to save & retrieve documents.

 

6. Can easily be modified in its form, while a manual spreadsheet involves a lot of manual calculations & are very difficult to amend.

 

7. The user can very quickly & efficiently perform complicated computations using the information stored in an electronic spreadsheet.

 

8. It is accurate in its calculations & allows automatic recalculation on formulae.

 

I.e., when one value/figure is changed, the result of the formula is automatically adjusted by the computer so as to correspond with the different input.  For a manual spreadsheet, changing one value means rubbing the result & writing the correct one again.

 

9. It offers graphical representation of data leading to comprehensive decisions.

 

10. Replaces the pencil & paper approach of the manual operations of the worksheet.

 

I.e., it enables the user to produce neat work because; all the work is edited on the screen and a final copy is printed.  With a manual spreadsheet, neatness & legibility of the work depends on the writer’s hand-writing skills.

11. It improves on the capabilities & speed of the Calculator.

 

Examples of the commonly used Spreadsheet packages

• VisiCalc – this was the 1^st type of spreadsheet to be developed for PCs.
• Lotus 123 – this is an integrated software with spreadsheet module, graphs, and database.
• Microsoft Excel.
• Corel Quattro-Pro
• Microsoft Works Excel
• Super calculators.
• Vp-Planner.

 

Review Questions.

 

1. Define a Spreadsheet.
2. Differentiate between the traditional analysis ledger sheet and an electronic spreadsheet.
3. Name three commonly spreadsheet packages.

 

COMPONENTS OF A SPREADSHEET

A spreadsheet has 3 main components, namely;

• Graphs / Charts.

 

Workbook:

 

When working in any spreadsheet program, you use workbook files to hold your information.

 

• A Workbook is a file in a spreadsheet package that contains one or more worksheets. The worksheets are made up of rows & columns in which you work and store your data.

 

A Workbook allows the user to organize various kinds of related information in a single file.

 

Database:

 

Spreadsheet programs such as Excel have special features, which can be used to manage data values entered in the cells of the spreadsheet.

These features, which are found on the Data menu, were incorporated in Excel but they belong to Database Management System software.

 

Examples of such features include: Filtering of records, use of Forms, calculating of Subtotals, data validation, Pivot tables and Pivot chart reports.

 

Example:

 

If related data values are entered on the same row, they form a Record.  Hence, a worksheet can be manipulated as a database that has data records entered in it.

 

Graphs/Charts:

 

A Chart is a graphical/pictorial representation of data in a worksheet.  Charts are used to summarize data in a worksheet in a pictorial form.

 

They enable the users to present complex data elements from a worksheet in a simple format that they can understand.

 

Charts make it easy for users to see comparisons, patterns, and trends in data, e.g., instead of having to analyse several columns of a worksheet, one can see at a glance whether sales are falling or rising.

 

Examples of charts are: Pie charts, Line graphs, Bar charts, Histograms, Column charts, etc.

 

Review Questions.

 

1. Name and describe the three components of a spreadsheet.
2. Explain the following terms as used in spreadsheets.

Application areas of spreadsheets (Areas where Spreadsheets are used)

 

1)Spreadsheets provide an easy & streamlined means of financial management.  They are mostly used by Accountants to record their daily transactions & also keep financial records.

 

For example; a spreadsheet can be used to do the following:

 

• Record sales & purchases.
• Calculate profits.
• Produce Invoices, and also compile financial statements.
• Prepare budgets.

 

• Assist the management of an organization to monitor the current state of payments from customers in relation to goods delivered.

 

• Detect aged debtors (i.e., those people who have owed you money for more than the period allowed in your terms of business.

 

• Track the value of assets over time (i.e., Appreciation and Depreciation).

 

Note.  Most spreadsheet programs come with inbuilt functions such as SUM, AVERAGE, PRODUCT, etc, which enable the Accountant to carry out his/her daily accounting tasks easily.

 

2. Data management.

 

A spreadsheet enables information to be produced easily and kept up-to-date.

 

For example;

• It enables the user to create, edit, save, retrieve and print worksheet data & records.
• It enables data to be arranged neatly in tabular structure.
• Related data can be typed on the same worksheet. If the data is on different worksheets, the worksheets can be linked so that the data can be accessed easily.

 

Some of the data management functions include:

 

• Sorting (i.e., arranging worksheet records in a particular order so as to easily access the data items).

 

• Filtering (i.e., displaying only the records that meet a given condition).
• Use of Forms to enter & view records.
• Use of Total/Subtotal

 

3. Scientific Applications.

 

Spreadsheet programs can be used by Scientists & Researchers to compile & analyse their results.

 

4. Statistical analysis / Mathematical operations.

 

Spreadsheets provide a set of data analysis tools that can be used to develop complex statistical analyses.  In addition, some of the tools generate charts.

 

Examples of statistical functions include:

 

• AVERAGE – used to calculate the mean of a set of values.
• MEDIAN – used to give the value in the middle of a set of values.

Such mathematical operations can be used by:

 

• Teachers to compile their students’ marks and produce results.

 

• Clerks & Secretaries to enable them easily create tables of figures and manipulate them quickly as required.

 

5. Forecasting (What if analysis).

 

The automatic recalculation feature makes it possible to use the “What if” analysis technique.

 

What if analysis is a feature in a spreadsheet that is used to find out the effect of changing certain values in a worksheet on other cells.

 

It involves changing the value of one of the arguments in a formula in order to see the difference the change would make on the result of the calculation.

 

This method can be used for financial forecasting, budgeting, cost analysis, etc.

 

Review Questions.

 

1. Explain five application areas where spreadsheet software can be used.
2. Explain the concept of “What if” analysis.

 

Common features of Electronic spreadsheets.

 

The following are the typical facilities provided by electronic spreadsheets:

 

1. Have the ability to create, edit, save & retrieve worksheets.

 

2. Have inbuilt functions & formulae which can be used to perform calculations.

 

3. Allows Automatic recalculation, i.e., when you change one value, the rest of the values in the spreadsheet are automatically recalculated by the computer to correspond with the different input. This enables you to play “what if” games with your system.

 

4. Have the ability to Sort and filter data (i.e., arrange data in a predefined order).

 

5. Have a Data validation facility, which ensures that the correct data is entered into the Spreadsheet.

 

6. Have a Chart facility that can be used to draw line graphs, Bar charts, histograms, etc.

 

7. Have the ability to format data (both text & numeric data) using predefined formats.

 

8. Some Spreadsheets have a SOLVER facility that is used to uncover the best uses of scarce resources so that desired goals can be achieved.

 

9. Have the ability to adjust Column widths & Row heights automatically.

 

10. Have the ability to hide and unhide rows & columns, and also freeze panes.

 

11. They enable printing of worksheets within the shortest time possible.

 

12. They have pre-designed Templates for automating tasks.

 

A Template is a document that acts as a blueprint or outline for other documents of the same type.  It contains the standard text, graphics & formatting that will be used in all documents of this type.

 

This means that, all formulas and formatting for similar workbooks such as Invoices can be saved as templates and then be used to automate the task without having to create the workbook again.

 

13. Have the ability to summarize data using Consolidation and Pivot tables.

 

Consolidation allows the merging of several worksheets into a summary sheet, while still keeping the original worksheets intact.  Consolidation adds together cells with the same co-ordinates in the various worksheets.

 

Pivot tables can be used to cross-tabulate large amounts of data.

 

Differences between an Electronic Spreadsheet and a Calculator

 

An electronic spreadsheet: –

 

1. Has more memory than calculator.
2. Is able to perform complex logical operations, but a calculator cannot.
3. Uses the large storage capacity of the computer that a calculator does not have.
4. Has a large working area that a calculator does not have.

 

Review Questions.

 

1. Describe any five features of a spreadsheet program.
2. State five features of spreadsheets that are useful in financial modelling.
3. How does a spreadsheet differ from a Calculator?

MICROSOFT EXCEL

 

This is a Spreadsheet program that enables users to create electronic worksheets that can be used to perform simple & complex calculations using a computer.

 

Ms-Excel has inbuilt functions that are used as shortcuts for performing mathematical, financial and statistical calculations.

 

Starting Microsoft Excel.

 

1. Click Start, point to Programs, then click Microsoft Excel.

 

–OR–

Click the Microsoft Excel icon, if it is displayed on the desktop.

 

WORKSHEET LAYOUT (Features/ Parts/ Elements of the Ms-Excel Screen).

 

• At the top of the window,
  • Title bar. It has the System /Control menu button on the left & the Resizing buttons on the right.
  • Menu bar.
  • Toolbars, e.g., Standard & Formatting toolbars. They contain the Toolbar buttons.
  • Name box – displays the active cell, e.g., A1.
  • Formula bar: A bar at the top of the Ms-Excel window that is used to enter or edit values or formulas in cells or charts. It also displays the constant value or formula stored in the active cell.

 

To display or hide the formula bar, click Formula bar on the View menu.

 

• Column Identifiers (or Column headers) – Letters that identify the columns.
• Row Identifiers (or Row headers) – numbers that identify the rows.

 

A Worksheet has a total of 256 columns & 65,536 rows.

 

• Active (Current) cell – the cell in which the selection box (Cell pointer) is placed.
• Cell Pointer.
• Gridlines – the thin lines that indicate the cell boundaries in a worksheet.

 

• At the bottom of the window,
  • Status bar – displays different indicators about the current working environment.

To display or hide the Status bar, click Status bar on the View menu.

 

• Sheet tabs – these are names of the sheets that appear at the bottom of the workbook window.
• Tab scrolling buttons – They enable the user to select and use a different sheet.

 

• Scroll bar, scroll box & the scroll arrows.

 

Scroll bars are the shaded bars along the right side and bottom of a window.  To scroll to another part of the file, click the arrows in the scroll bar or drag the scroll box.

THE WORKSHEET:

 

• This is the primary document in a Spreadsheet program that is used to store & work with data.

 

• A Worksheet is a tool that is used for maintaining numeric data in a tabular form, simplifying numerous calculations and presenting numerical data graphically.

 

A worksheet is basically a page stored in a workbook, and acts as the working area.

 

A Worksheet consists of cells that are organized into columns & rows in which data entries are made.

 

Columns: – These are fields that make up the worksheet of a Spreadsheet.  A Column is a vertical arrangement of cells.

 

Rows: – These are records that form a worksheet.  A Row is usually a horizontal arrangement of cells.

 

Cell:

 

• A box formed when a row & a column intersect in a worksheet or a table, where the data is entered.

 

A cell is referred to or identified by use of the column letter heading & the row number heading (e.g., A1 refers to the first cell).

 

A Worksheet can be used in:

 

• A Company Sales Report to show the sales for each item over the year.
• An Employees’ Payroll to calculate the employee’s salaries for each month.
• A Students Progress record to store information on student’s marks and monitor their progress.
• Personal Expenses to maintain a budget of your monthly expenses.
• Mortgage Repayment Calculations to calculate the monthly repayment amount on a mortgage loan.

To Insert a single new worksheet.

1. On the Insert menu, click Worksheet.

 

Selecting worksheets.

 

To select                                                         Do this

 

A single sheet                                                 Click the tab for the sheet.

 

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

When you select a sheet, the color of its tab will change to white.

 

Two or more nonadjacent sheets                    Click the tab for the first sheet, hold down CTRL key, then click the tabs for the other sheets.

 

All sheets in a workbook                                Right-click a sheet tab, then click Select All Sheets on the shortcut menu.

 

Note.  To deselect the sheets, click inside any of the worksheets.

 

To Rename a worksheet (Giving meaningful names to sheets).

 

1. Click the tab for the sheet you want to rename.
2. On the Format menu, point to Sheet, then click Rename.

The current name for that sheet will be selected.

3. Press Backspace or Delete, type a new name, then press ENTER.

 

To Delete worksheets.

 

1. Select the worksheet(s) you want to delete.
2. On the Edit menu, click Delete Sheet.

 

To Hide a worksheet.

 

1. Select the sheet(s) you want to hide.
2. On the Format menu, point to Sheet, then click Hide.

 

To Display a hidden worksheet.

 

1. On the Format menu, point to Sheet, then click Unhide.
2. In the Unhide sheet box, double-click the name of the hidden sheet you want to display.

 

Ms-Excel add-ins – Components that can be installed on your computer to add commands and functions to Excel.  These add-in programs are specific to Excel.

 

CREATING A NEW WORKBOOK.

To create a new, blank workbook.

 

1. On the File menu, click New, then click Blank Workbook on the New Workbook task pane.

 

To create a new workbook based on the default workbook template.

 

1. Click on the New workbook icon on the Standard

 

TYPES OF DATA IN SPREADSHHETS.

 

• Labels (Text).

 

• Labels are texts consisting of alphanumeric characters that can be entered into a cell.

E.g., Item codes such as Salary, Names such as John.

 

• Labels are made up of alphanumeric character strings.

In Excel, Text is any combination of numbers, spaces, & nonnumeric characters.

E.g., 10A19, 27AXY, 12-976, 208 4675.

 

• Values (Numbers).

 

Values consist of numerals & mathematical formulas entered into a cell.

 

In Excel, a number can contain only the following characters: digits 0 to 9 + – ( ) / $ %.

 

• Formulas.

 

• A Formula is a sequence of values, cell references, functions & arithmetic operators whose calculation results to a numeric value.

 

• It is an equation that performs operations on worksheet data.

 

Formulas can perform mathematical operations such as addition, subtraction, division and multiplication.

A Formula is used to tell Ms-Excel how you want a particular value to be computed.

 

• Functions.

 

• It is a special command, which you can type into your formula to perform arithmetic operations.

 

• It is an inbuilt equation that is used for calculations.

 

• A Function is a short predefined (inbuilt) formula used to perform a given specific task.

 

Functions can be used to perform both simple and complex calculations.

 

EDITING CELL CONTENTS.

 

1. Double-click on the cell that contains the data you want to edit.

 

–Or–

Click in the cell, then press F2.

 

2. Edit (make changes to) the cell contents.

E.g., if you had left out a character, use the Left or Right Arrow key to move the insertion point to the position of the correction, then type the character.  If you had typed wrong characters, use the Backspace or Delete to erase them.

3. To enter your changes to the active cell, press ENTER, then use the Arrow keys to move to another cell.

 

SAVING WORKBOOKS.

Purpose.

√    In order to use the worksheet at a later time.

√    If the saving is done periodically, say every 1 minute, it helps prevent data loss in case of power failure.

When you save a workbook for the first time, you assign a file name and indicate where you want to store the file on your computer’s hard disk or in another location.  Each time you subsequently save the workbook, Ms-Excel updates the workbook file with your latest changes.

 

To save a new, unnamed workbook.

 

1. Click the Save button on the Standard

 

–OR–

On the File menu, choose Save (or press CTRL+S) to display the Save As dialog box.

 

2. In the File name box, enter a name for the worksheet.
3. In the Save in list, select the drive and/or folder where you want the worksheet to be saved.
4. Click the Save button.

 

To save a copy of a workbook (or save a workbook with a new name).

 

1. Open the workbook you want to make a copy of.
2. On the File menu, click Save As….
3. In the File name box, enter a new name for the file.

To save the copy in a different folder or drive, click a different location in the Save in list.

4. Click the Save button.

 

To save workbooks automatically as you work.

 

1. On the Tools menu, click Options, click the Save tab, then select the Save AutoRecover info every
2. In the minutes box, enter the interval for how often you want to save files.

 

OPENING A SAVED WORKSHEET.

Purpose.

You can open a saved worksheet (i.e., a worksheet stored on the hard disk of the computer or on a floppy disk) in order to:

√    Continue working on it, if it was saved before completion.

√    View the data it contains.

√    Update it, if the data it contains represents information that changes periodically. For example, A Weekly report.

 

1. Choose Open on the File menu,

 

–OR–

Click the Open button on the Standard toolbar (or press CTRL+O) to display the Open dialog box.

2. In the Look in drop down list, click the drive or folder that contains the file you want to open.
3. In the folder list, locate and open the folder that contains the file.
4. Double-click the file you want to open (or click the file, and then click the Open button).

 

Note. To open a recently opened file, select it from the bottom of the File menu.

Exiting / Quitting Ms-Excel.

Purpose.

√    You exit from Ms-Excel when you have finished working with it.

 

1. On the File menu, click

–OR–

Press ALT+F4),

–OR–

Click the Close button on the top right hand corner of the Title bar of the Ms-Excel window.

 

If the workbook was not saved before or the changes made to the open workbook are not saved, Ms-Excel will give you an option to save those changes by displaying the Save As dialog box.  Choose Yes to save the changes, or No to discard the changes.

SELECTING DATA IN CELLS, ROWS OR COLUMNS.

 

To select                                             Do this:

 

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

 

Text in a cell                                       Double-click in the cell (or click in the cell, press F2 to display the Insertion point), then select the text in the cell.

 

A range of cells                                  1. Click the first cell of the range, hold down the left mouse

button, then drag to the last cell in the range.

-OR-

 

Click the first cell in the range, hold down SHIFT key, then click the last cell in the range.

-OR-

 

Click the first cell in the range, hold down SHIFT key, then use the Arrow keys to extend the selection.

 

All cells on a worksheet                     Press CTRL+A (or on the Edit menu, click Select All).

 

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

 

An entire row or column                    Click the row or column heading.

 

 

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

–OR–

Select the first row or column, hold down SHIFT key, then select the last row or column.

 

Nonadjacent rows or columns            Select the first row or column, hold down CTRL & select the other rows or columns.

 

Note. To cancel a selection of cells, click any cell on the worksheet.

 

A Range is any group of cells in a worksheet. The cells in a range can be adjacent or nonadjacent.

 

Clearing cell contents.

 

Clearing cells removes the cell contents (formulas and data), or formats (such as number formats, and borders), but leaves the blank cells on the worksheet.

 

1. Select the cells, rows, or columns you want to clear.
2. On the Edit menu, point to Clear, then click Formats or Contents.

 

Clicking All, will clear formats and contents, and also remove any cell comments and data validation.

 

Note.  If you click a cell and then press the DELETE key, Ms-Excel will remove the cell contents but does not remove comments or cell formats.

 

Inserting blank cells, rows, or columns.

 

Purpose.

 

• You can insert a row or column, to make room for additional information in the worksheet. This may be necessary when some extra information, which was not expected earlier, needs to be included.

 

To Insert new blank cells.

 

1. 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.
2. On the Insert menu, click
3. In the Insert dialog box, click Shift cells right, Shift cells down, Entire row, or Entire column.

 

To Insert Rows.

 

1. To insert a single row, select a row (or click a cell in the row) immediately below where you want the new row to appear.

 

For example, to insert a new row above row 5, click a cell in row 5.  To insert multiple rows, select the same no. of rows as you want to insert.

 

2. On the Insert menu, click

 

To Insert Columns.

 

1. To insert a single column, select a column (or click a cell in the column) immediately to the right of where you want the new column to appear.

 

For example, to insert a new column to the left of column B, click a cell in column B.  To insert multiple columns, select the same no. of columns as you want to insert.

 

2. On the Insert menu, click

 

Deleting cells, rows, or columns

 

Deleting removes the cells from the worksheet and shifts the surrounding cells to fill the space.

 

1. Select the cells, rows, or columns you want to delete.
2. On the Edit menu, click Delete.
3. If you are deleting cells, the Delete Cells dialog box appears. Click Shift cells left, Shift cells up, Entire row, or Entire column.

 

Exercise (a).

 

1. How many columns are there in an Excel worksheet?
2. Identify and explain the FOUR types of data in Spreadsheets.
3. Explain how you would do the following operations on a Worksheet in Microsoft Excel.
   • Rename a worksheet.
   • Delete a worksheet.
   • Insert a single new worksheet.
   • Move from one worksheet to another.
   • Select a cell.
   • Select a range of cells.
   • Select nonadjacent cells or cell ranges.
   • Select everything in a worksheet.
4. Define the following terms as used in Spreadsheets:         (6 marks)
5. What is the difference between Clearing cells and Deleting cells?

 

Exercise (b).

 

1. What is the meaning of each of the following concepts?
2. Distinguish between Labels and Formulae with respect to Spreadsheets.

 

Exercise (c).

 

1. List FOUR types of information that can be entered into a cell.                             (4 marks).
2. Explain THREE cell data types in spreadsheet.        (6 marks).

 

POSITIONING (ALIGNING) DATA IN A CELL.

 

Purpose.

 

• Adjusting the alignment of the cell contents, helps to distinguish different types of information in cells.

 

Usually when you enter text data into a worksheet, it is normally aligned to the left in a cell, while numbers, dates & times are aligned to the right.

 

To center the data, or align data to the left or right in a cell.

 

• Select the cells with the data you want to reposition.
• On the Formatting toolbar, click the appropriate alignment button.
• Click the Align Left button – to align text to the left of the cell.
• Click the Center button – to center text in the cell.
• Click the Align Right button – to align text to the right.

 

Merging cells across columns.

 

You can easily merge headings across the top of a range of cells.  When you merge cells, the selected cells are combined into one cell.  This spreads the content of one cell over many cells.

 

1. Copy the data you want into the upper-leftmost cell within the range.
2. Select the cells you want to merge.

Warning.  Excel places only the data in the upper-leftmost cell of the selected range into the resulting merged cell. If there is data in other cells, the data is deleted when you merge the cells.

 

3. To merge cells in a row or column and center the cell contents, click the Merge and Center button on the Formatting toolbar,

–Or–

 

On the Format menu, click Cells, click the Alignment tab, then select the Merge cells checkbox.

This will combine the cells and center the heading in the new, wider cell.

 

HIDING ROWS OR COLUMNS.

Purpose.

√    Hiding rows or columns prevents the display and printing of data held in particular rows or columns.  For instance, when your rows or columns contain confidential formulas not necessary in the printed report.

 

1. Select the rows or columns you want to hide.
2. On the Format menu, point to Row or Column, then click Hide.

The selected rows or columns including their headings will not be visible.

To display a hidden row or column.

 

1. To display hidden rows, select the row below and the row above the hidden rows.

 

To display hidden columns, select the column to the left and the column to the right of the hidden columns.  For example, to redisplay hidden column C, select column B & column D.

 

If you want to redisplay noncontiguous columns, say, column C, D & F, select all the columns from B through G, i.e. columns B, E & G.

 

2. On the Format menu, point to Row or Column, then click Unhide.

 

Tip. If the first row or column of a worksheet is hidden, to display it;

 

1. Click Go To on the Edit
2. In the Reference box, type A1, and click OK.
3. On the Format menu, point to Row or Column, then click Unhide.

 

APPLYING BORDERS, SHADINGS & PATTERNS TO A WORKSHEET.

 

You can apply borders to cells, shade cells with a background color, or shade cells with a color pattern in order:

 

Purpose.

√    To distinguish between different types of information in a worksheet.

√    To make the worksheet more appealing to the eye.

√    To draw attention to important data in the worksheet.

 

To Apply a Border to cells.

 

1. Select the range of cells that you want to put a border around.
2. On the Format menu, select In the resulting dialog box, click on the Border tab.
3. Under the Presets section, select the Border style you want, e.g. Outline or Inside borders or both.
4. To change the line style for the border, click a style in the Style list, and then click a button to indicate the border placement.

You can specify the colour of the lines from the Color drop-down list.

5. Click the OK button when you are done.

 

To Apply or Remove cell Shadings.

 

1. Select the cells you want to apply shading to or remove shading from.
2. On the Format menu, click Cells, then click the Patterns
3. In the Cell shading box, click the color you want to shade the cells with.
4. To include a background color with the pattern, click the arrow next to the Pattern box, then click the pattern style and color you want.
5. Click the OK

The borders, shades and patterns that you have specified all apply to the range you had selected.

 

FORMATTING NUMBERS IN A WORKSHEET.

 

Purpose.

Formatting of numbers in a worksheet can make your worksheet much more presentable.

For example,

√    Long numbers without commas are difficult to read.

√    Too many uneven decimal places in a worksheet make it difficult to compare figures.

√    A worksheet that has some figures with six decimal places, others with commas and others with no decimal places has an untidy appearance.

 

To Add or Remove the Thousands separator in a number.

 

Method 1.

 

1. Select the range of cells containing the numbers whose format you want to change.
2. Click the Comma Style (,) button on the Formatting

 

Method 2.

 

1. On the Format menu, click Cells, then click the Number
2. In the Category list, click Number.
3. Select or clear the Use 1000 separator (,)

All numbers in the selected range will be displayed with commas separating the thousands and with two decimal points, e.g., 7,375.00

 

To Change the no. of Decimal places displayed in a number.

 

Method 1.

 

1. Select the range of cells whose decimal places you want to change.
2. To display more digits after the decimal point (to increase the no. of decimal places), click the Increase Decimal button on the Formatting

 

To display fewer digits after the decimal point (to decrease the no. of decimal places), click the Decrease Decimal button.

 

Method 2:

 

1. On the Format menu, click Cells, then click the Number
2. In the Category list, click Currency, Accounting, Percentage, or Scientific.
3. In the Decimal places box, enter the number of decimal places you want to display.

If you want to change the currency symbol, click the symbol you want to use in the Symbol list.

 

To Display numbers as Percentages or Fractions.

 

Method 1.

 

To quickly display nos. as percentages of 100, click the Percent Style (%) button on the Formatting toolbar.

 

Method 2.

 

1. Select the range cells you want to format as percentages.
2. On the Format menu, click Cells, then click the Number

 

3. To display nos. as percentages, click Percentage in the Category In the Decimal places box, enter the number of decimal places you want to display.

 

4. To display nos. as fractions, click Fraction in the Category list, then click the type of fraction you want to use.

 

 

 

 

To Add or Remove a Currency symbol.

 

Method 1.

 

1. Select the range of cells you want to format as currency.
2. Click the Currency Style ($) button on the Formatting

Method 2.

1. On the Format menu, click Cells, then click the Number
2. In the Category list, click Currency.
3. In the Symbol list, select the currency symbol that you want.

 

To remove a currency symbol, click None in the Symbol list.

 

To Display minus signs on negative numbers.

 

1. Select the cells you want to change.
2. On the Format menu, click Cells, then click the Number

For simple numbers, click Number in the Category list.

For currency, click Currency in the Category list.

3. In the Negative numbers box, select the display style for negative numbers.

 

To Reset a number format.

 

1. Select the cells you want to reset to the default number format.
2. On the Format menu, click Cells, then click the Number
3. In the Category list, click General.

 

Exercise.

 

1. Explain step-by-step how you would perform the following operations on a worksheet in Microsoft Excel.
   • Enter data into a worksheet cell.
   • Edit cell contents.
   • Make text in a cell Bold, Italic and double-underlined.
   • Adjust a column width to accommodate the longest cell entry in a range of cells.
2. Explain what are Label prefixes in spreadsheets.                         (10 Mk)
3. A worksheet table has columns A through N. The Chief Accountant doesn’t require the information contained in columns E, F and G.  Give a step-by-step procedure on how to make sure that only the columns with the required information are printed.
4. You have the number 435273.7865 in a cell. How will it look if you format the cell as commas and 2 decimal places?

 

FINDING RECORDS.

 

Purpose.

 

• Suppose you wanted to view records that meet given conditions, you would have to sort the table according to the conditions so as to find out where those records appear in the list. Such conditions are referred to as ‘Criteria’.

 

However, this method will require you to sort the table whenever you want to find something different.

Ms-Excel offers an easier solution to this through the Filter command on the Data menu.

 

1. Click on any cell in the table that contains the records you want to search for.
2. On the Data menu, choose Filter, then select AutoFilter from the submenu.

A downward arrow appears on the right of each field name.

3. Click the down arrow on a column to display the conditions that can be set. Select a condition (Criteria) from that list.

 

Note. If the criteria you want is not in the list or if you want to enter two conditions, then perform the following steps:

• Select the column / field to search by clicking on the down arrow to the right of the field name.

The Custom AutoFilter dialog box appears.

• In the Show rows where: box, select the conditions to use by clicking on the down arrow. The list includes ‘is greater than’; ‘is less than’, etc.
• Enter the Value to compare the contents of the field width. You could type or select it from a drop down list of values available in the database.

 

4. Click the OK button to complete the task.

 

Note.  To redisplay the records, click on the down arrow to the right of the field name that you had selected, and select the option All.

 

Example:

 

	A	B	C	D
1	Date	Person	Item	Amount
2	26-Jan-97	Morris	Bar	95
3	28-Mar-97	Albert	Take Away	136
4	28-Jan-97	Anne	Lunch	53
5	09-Mar-97	Susan	Breakfast	112
6	12-Jun-97	Jane	Snacks	56
7	12-Apr-97	Richard	Snacks	118
8	15-Mar-97	Peter	Bar	114
9	25-Mar-97	Mike	Take Away	80
10	01-Mar-97	James	Bar	167
11	09-Jun-97	Morris	Take Away	71
12	16-Jun-97	Susan	Lunch	80

 

 

Suppose you want to view all the sales that exceed 100 Shillings but are below 150 shillings from a week’s list of sales records.

• The field to use for the search would be Amount. So, click on the down arrow on the right of the Amount field.
• The Operator would be is greater than, and the value would be 100.
• Enter the second criteria, as Amount is less than 150, then click the OK
• All the records whose amount is between 100 and 150 will be displayed (Only the records matching the criteria are displayed).

 

	A	B	C	D
1	Date	Person	Item	Amount
3	28-Mar-97	Albert	Take Away	136
5	09-Mar-97	Susan	Breakfast	112
7	12-Apr-97	Richard	Snacks	118
8	15-Mar-97	Peter	Bar	114
10	01-Mar-97	James	Bar	167

 

PIVOT TABLES.

 

Purpose.

 

• A Pivot table helps us to summarize and analyze large amounts of existing data, from a list or table, using the format and calculation methods of your choice.

 

Suppose we wanted the daily totals for each of the sales persons along with the total sale for each day: A PivotTable can help us get that kind of information much more easily.

 

1. Select any cell in the list or table you want to summarize.
2. On the Data menu, select PivotTable and PivotChart Report…
3. This will activate Step 1 of the PivotTable Wizard. In this step, select the source of data Ms-Excel will use to create the Pivot Table.  From the choices given, select Microsoft Excel list or Database.

Click on the Next button.

4. Step 2 of the Wizard shows you the range containing the list of data detected around the position of your cell pointer of Step 1. If necessary, change the range.

Click on the Next button.

5. Click the Layout… button to specify how you want the PivotTable to appear.
6. On the right hand side of the PivotTable and PivotChart Wizard – Layout dialog box, the Pivot Wizard shows the column titles (field names) found in your list.
   • Drag the field name whose contents you want to summarize downward to the area marked ROW.
   • Drag the field name whose contents you want to summarize across the PivotTable to the area marked COLUMN.
   • Drag the field name that contains the data to be summarized into the area marked DATA.
7. Step 3 of 3 will be displayed again allowing you to specify a convenient location for the PivotTable. The PivotTable may be positioned either as a new worksheet or on the existing worksheet.

If you select Existing worksheet in this dialog box, then you must specify the cell where the top left corner of the PivotTable will be positioned.

8. To specify a name for the PivotTable, click on the Options
9. Click on the Finish Ms-Excel places the Pivot Table in the location you specified.

 

Refreshing Records in the PivotTable.

 

1. Select a cell in the PivotTable.
2. On the PivotTable toolbar, click on the Refresh Data

 

Note.  To refresh the PivotTable whenever you open the workbook, click Options on the PivotTable menu of the PivotTable toolbar.  Under Data source options, select the Refresh on open checkbox.

 

CELL REFERENCES (Cell Addresses).

 

• A Cell reference is the identity of a cell in a worksheet.

 

• A Reference identifies a cell or a range of cells on a worksheet and tells Ms-Excel where to look for the values or data you want to use in a formula.

 

Examples of Cell references.

 

• Single cell reference.
• Mixed cell reference.
• Label cell reference.
• Relative cell reference.
• Absolute cell reference.

 

REFERENCING OF CELLS.

 

A cell is identified by use of the Column letter heading & the Row number heading.

 

To refer to a cell, enter the column letter followed by the row number. For example, B2 refers to the cell at the intersection of column B & row 2.

 

Examples of cell references.

 

To refer to                                                                                         Type

 

The cell in column A & row 10                                                          A10

 

The range of cells in column A & rows 10 to 20                               A10:A20

 

The range of cells in row 15 & columns B to E                                 B15:E15

 

All cells in row 5                                                                                5:5

 

All cells in rows 5 to 10                                                                      5:10

 

All cells in column H                                                                          H:H

 

All cells in columns H to J                                                                 H:J

 

The range of cells in columns A to E & rows 10 to 20                      A10:E20

 

PERFORMING CALCULATIONS IN MS-EXCEL.

 

USING WORKSHEET FUNCTIONS.

 

• A Function is a special command, which you can type into a formula to perform arithmetic operations.

 

• A Worksheet function is a special inbuilt formula that performs an operation on the values that you provide.

 

• It is an inbuilt equation that is used for calculation.

 

A function performs operations or calculations using specific values, called arguments.  The arguments can be either cell references or values or both.

 

Categories of Functions in Ms-Excel.

 

Functions are grouped into broad categories by some common features particular to the function.

 

1. Financial functions.

 

Analyze investments and securities; determine depreciation, calculate cash flows and loans, e.g. the PMT function.

 

2. Date & Time functions.

 

Calculate values that represent dates and times.

 

E.g., the TODAY function is used to return the current date according to the computer’s internal clock.

 

3. Math & Trigonometry functions.

 

Can be used to perform simple mathematical operations, such as calculating Square roots (SQRT), rounding a number (ROUND), calculating the total value for a range of cells (SUM), etc.

 

They are also used to replace complex trigonometric calculations like Sine (SIN), Cosine (COS), etc.

 

E.g., ABS – gives the Absolute value of a number.  The Absolute value of a number is the number without its sign.

 

Syntax: ABS(number)

 

4. Statistical functions.

 

Perform calculations (statistical analysis) on ranges of data.

E.g., AVERAGE – calculates the average (arithmetic mean) of a set of values in a range.

 

STDEV – Estimates Standard Deviation based on a sample.  Standard Deviation is a measure of how widely values are dispersed from the Average value (Mean).

 

Syntax: STDEV(number1,number2,….)

 

5. Logical functions.

 

Calculate the results of logical formulas.  E.g., the IF Function.

 

6. Look up & Reference functions.

 

Finds or refers to the contents of a cell.  For example, the VLOOKUP function.

 

7. Database functions.

 

Perform statistical calculations and queries on database tables.  For instance, DSUM will find the total of values in a particular field (column).

 

8. Information functions.

 

Return information about cells, ranges, the operating system, and some Ms-Excel tools, or to mark places where information is missing or incorrect.

E.g., CELL – returns information about the formatting, location or contents of a cell.

 

CREATING MS-EXCEL FORMULAS.

 

Purpose.

 

• When you need to perform a calculation in Ms-Excel, you use a formula.

 

You can create simple formulas that can be used; lets say, to add the values in two cells, or you can create complex formulas that can calculate the Standard deviation of certain values.

 

For example, the formula ‘=SUM(D1:D7)’ uses a function to add the values in the range D1:D7.  It gives the same result as the formula ‘=D1+D2+D3+D4+D5+D6+D7’.

 

Before you write your formula in Ms-Excel, it is advisable to do the following;

 

• Decide what you want to be calculated, e.g., the Total Cost of items.
• Note down the values in the worksheet required for the calculation and use them to write down the formula, e.g. Quantity * Price.
• Substitute the values with their cell references, e.g. B3*D3.

 

Calculation operators in formulas.

 

Operator – A sign or a symbol that specifies the type of calculation to perform on the elements of a formula.

 

Excel includes 4 different types of calculation operators:

 

• Arithmetic (Mathematical) operators.
• Logical (Comparison) Logical operators.
• Reference operators.
• Text concatenation operators.

Arithmetic (Mathematical) operators.

 

Performs basic mathematical operations such as Addition, Subtraction, Division or Multiplication.

 

Arithmetic operator                         Meaning                                 Example.

 

+ (Plus sign)                                       Addition                                  3 + 3

– (Minus sign)                                     Subtraction                             3 – 1

* (Asterisk)                                         Multiplication                        3 * 3

/ (Forward slash)                                Division                                  3/3

% (Percent sign)                                Percent                                                20%

 

Logical (Comparison) operators.

 

Compares two values and produces a logical value, either TRUE or FALSE.

 

Comparison                                       Meaning                                 Example.

 

= (equal sign)                                      Equal to                                  A1=B1

> (greater than sign)                           Greater than                            A1>B1

< (less than sign)                                Less than                                 A1<B1

>= (greater than or equal to sign)       Greater than or equal to          A1>=B1

<= (less than or equal to sign)            Less than or equal to               A1<=B1

<> (not equal to sign)                         Not equal to                            A1<>B1

 

 

Reference operators.

 

Combine ranges of cells for calculations.

 

Reference operator               Meaning                                                         Example.

 

: (colon)                                  Range operator; produces one                      B5:B9

reference to all the cells between

two references, including the two

references.

 

, (comma)                               Union operator; combines multiple             SUM(B5:B9,D5:D9)

references into one reference.

 

Text concatenation operators.

 

Use the ampersand (&) to join one or more strings to produce a single piece of text.

 

Text operator                        Meaning                                             Example.

 

& (ampersand)                       Connects two values to produce         “North” & “wind” produces

one continuous text value                   “Northwind”

 

Creating a simple formula.

 

A Formula in Ms-Excel always begins with an equal (=) sign.  Ms-Excel uses this sign to differentiate between a Label (text) and a Formula.  The equal sign tells Ms-Excel that the characters that follow constitute a formula.

 

Following the equal sign are the elements to be calculated called Operands or Arguments.  The Arguments are separated by calculation operators, and enclosed by an opening & closing parentheses (brackets).

 

1. Click on the cell in which you want to enter the formula.
2. Type the = (equal sign) to activate the Formula bar. The equal sign (=) tells Ms-Excel that you are entering a formula in the cell.
3. Type the formula directly into the Formula box.

To tell Ms-Excel where to find the data that will be used in the calculation, type a cell reference.

4. Press the ENTER Ms-Excel immediately calculates & shows the result in the cell, while the formula is displayed in the Formula bar.

 

Notes.

• A Formula can refer to other cells on the same worksheet.

 

• Ms-Excel calculates a formula from left to right, according to a specific order for each operator in the formula.

You can change the order of operations by using parenthesis.  E.g., to calculate B4+25, then divide the result by the sum of the values in cells D5, E5 and F5, the formula would be:  =(B4+25)/SUM(D5:F5)

 

In this example, the parentheses around the first part of the formula forces Ms-Excel to calculate B4+25 first, then divide the result by the sum of the values in cells D5, E5, and F5.

 

Creating a formula that contains a function.

 

1. Click the cell where the result of the formula will be displayed.
2. On the Insert menu, click Function, (or click the Paste Function button on the toolbar).
3. Click a function from the Function Category When you select a function, a description of the function appears in the dialog box.

Click the OK button.

4. Type in the arguments to compute in the parentheses in the formula. To enter a range, use a Colon to separate the first & the last cells in the range, or use a Comma to separate reference to individual cells.
5. After you complete the formula, click the OK button or press the ENTER

 

Note.   The structure of a function begins with an Equal sign (=), followed by the Function name, & the Arguments for the function.  The Arguments are separated by commas or a colon, and enclosed in an opening & closing parenthesis.

 

Performing Common Calculations.

 

ADDING NUMBERS.

 

Method 1.

 

Type the numbers directly into a cell and press the ENTER key to display the results.

 

Example: cells D5, E5 & F5 contain the values 5, 15 & 20.  To add all the values in the range, type:           =D5+E5+F5

 

      -OR-

 

=5+15+20

 

To Add all numbers in a contiguous row or column.

 

You can insert a sum for a range of cells automatically using the AutoSum (Σ) button on the Standard toolbar.

 

1. Click a cell below the column of numbers or to the right of the row of numbers.
2. Click AutoSum on the Standard Ms-Excel suggests a formula.
3. To accept the formula, press the ENTER. To change the suggested formula, select the range before pressing the ENTER key.

 

To Add numbers that are not in a contiguous row or column.

 

Use the SUM function.  SUM adds all the numbers in a range of cells.

 

Syntax: SUM(number1,number2,…..)

 

Number1,number2,… are the arguments for which you want the total value or sum.

 

Example 1: Cells A2, A3 & A4 contain values -5, 15, and 30.

 

To add                                                            Formula                                

 

The numbers in the cells A2 to A4.                =SUM(A2:A4)

The numbers in cells A2:A4, and 15.             =SUM(A2:A4, 15)

The values in cell A2, A4 and 2.                    =SUM(A2,A4, 2)

3 and 2                                                            =SUM(3,2)

 

Example 2:

 

	A	B
1	Salesperson	Invoice
2	Buchanan	15,000
3	Buchanan	9,000
4	Suyama	8,000
5	Suyama	20,000
6	Buchanan	5,000
7	Dodsworth	22,500

 

Formula                                    Adds

 

=SUM(B2:B4)                          the values in cells B2, B3 and B4.

=SUM(B2:B3, B5)                    two invoices from Buchanan, & 1 from Suyama   

=SUM(B2,B5,B7)                     individual invoices from Buchanan, Suyama,& Dodsworth.

 

SUBTRACTING NUMBERS.

 

Type the formula ‘=10-5’ in a cell to display the result 5.

 

Example: cells A2, A3 & A4 contain the values 15,000, 9,000 & -8,000.

 

 

Formula                     Description                                                                            Result

 

=A2-A3                       Subtracts 9,000 from 15,000                                                  6,000

 

=SUM(A2:A4)            Adds all nos. in the list, including negative nos.                    16,000

To calculate a running balance.

You can build a formula in a banking transaction to calculate your running balance.

Example 1.

 

Assume that cell F6 contains the previous balance of 4,000/=, cell D7 contains the first transaction’s deposit subtotal of 190,500, and cell E7 contains any cash-received amount of 50,000.

 

To calculate the current balance for the first transaction, enter the following formula in cell F7:

 

=SUM(F6,D7,-E7)

 

Example 2.

 

	A	B	C
1	Deposits	Withdrawals	Balance
2	$1,000	$625	=SUM(A2,-B2)
3	1000	740	=SUM(C2,A3,-B3)

 

TO INCREASE OR DECREASE A NUMBER BY A PERCENTAGE.

 

Example 1.

Assume that cell F5 contains a numeric value of 30,000.  To increase the value stored in cell F5 by 5 percent;

 

=F5*(1+5%)

 

If the percentage amount is stored in a cell, let say, cell F2:

 

=F5*(1+$F$2)    = (31,500)

 

 

Note. The reference to F2 is an Absolute cell reference so that the formula can be copied to other cells without changing the reference to F2.

 

Example 2.

 

	A	B
1	Number	Percent Increase
2	23	3%

 

Formula                                 Description                                                                            Result

 

=A2*(1+5%)                           Increases the no. in A2 by 5%                                                24.15

=A2*(1+B2)                           Increase the no. in A2 by the percent value in B2: 3%            23.69

 

=A2*(1-B2)                            Decrease the no. in A2 by the percent value in B2: 3%            22.31

 

MULTIPLYING NUMBERS.

 

Use the asterisk (*) operator or the PRODUCT function.

 

PRODUCT multiplies all the nos. given as arguments and returns the product.

 

Syntax: PRODUCT(number1,number2,…..)

 

Number1,number2,… are the numbers you want to multiply.

 

Formula                                 Description                                                                            Result

 

=5*10                                                                                                                                      50

=A2*B2                                  multiplies the contents in cells A2 and B2.

=(5+2)*3                                 adds 5 and 2 together then multiplies the result by 3             21

 

 

Example: Using the values shown in the worksheet below, calculate the cost of the milk.

 

	A	B	C	D
1	Item	Quantity	Price	Total cost
2	Milk	26 Litres	15.00
3	Sugar	19 Kgs	48.90

 

 

The Total cost of the milk will be given by, Quantity* Price.  Therefore, in cell D2, type the formula; =B2*C2.

 

Note.  If you omit ‘=’ symbol before B2, Ms-Excel will not recognize it as a formula and what you have typed will literally appear as ‘B2*C2’, which will not yield the expected result.

 

To Multiply numbers in different cells using a formula.

 

Cells A2, A3 & A4 contain the values 5, 15, 30.

 

To multiply                                                                Formula

 

The numbers in cells A2 & A3                                   =A2*A3

All the numbers in the range                                      =PRODUCT(A2:A4)

All the numbers in the range, and 2.                          =PRODUCT(A2:A4,2)

 

DIVIDING NUMBERS.

 

Type a formula such as =10/5 in a cell to display the result 2.

 

Example.

 

Cells A2 & A3 contain values 15,000 and 12.

 

Formula                                 Description                            Result

 

=A2/A3                                   Divides 15,000 by 12              1,250

 

GETTING THE SQUARE ROOT OF A NUMBER.

 

Use the SQRT function.  SQRT gives a positive square root of a specific number.

 

Syntax: SQRT(number)

 

Number – is the number for which you want the square root. If number is negative, SQRT returns the #NUM! error value.

 

Example 1: Cell B2 contains the number 215; to get the square root, type:

 

=SQRT(215)                          -OR-                     =SQRT(B2)                           

 

Examples 2:

 

Formula                                 Description                            Result

 

= SQRT(16)                                                                           4

=SQRT(-16)                                                                           #NUM!

 

RAISING A NUMBER TO A POWER.

 

Use the POWER function.  POWER gives the result of a no. raised to a power.

 

Syntax: POWER(number,power)

 

Number – is the base no. It can be any real number.

 

Power – is the exponent to which the base no. is raised.

 

Note.  The up carat (^) operator can be used instead of POWER to indicate to what power the base no. is to be raised.

 

Description                                   Formula                                                                 Result

 

5 squared                                       =5^2           -OR-   =POWER(5,2)                     25

5 cubed                                          =5^3          -OR-    =POWER(5,3)                     125

4 raised to the power of 5/4           =4^05/4      -OR-   =POWER(4,5/4)                  5.656854

98.6 raised to the power of 3.2      =98.6^3.2   -OR-   =POWER(98.6,3.2)             2401077

 

 

The PMT Function

 

PMT calculates the payment on a loan (principal) at a given interest rate for a specified no. of payment periods (term).

 

Syntax: =PMT(Principal – Amount,Interest-Rate,Term)

 

Principal and Terms are values.  Interest is a decimal or percentage value greater than -1.

 

Example;

A businessman took out a Ksh. 800,000 loan for 4 years at an annual Interest Rate of 26%, compounded monthly.  If the monthly installments are paid on the last day of each month, determine the installment to be paid.

 

=PMT(800000,0.26/12,48)

 

The Monthly installment is 26,973.85

 

Note.  The Interest is divided by 12 in order to get the monthly rate, and term (in years) is multiplied by 12 to convert it into months.

 

The FV Function

 

 

FV calculates the future value of a fixed investment earning a fixed interest over a specified period.

E.g., let’s say, you want to plan for your retirement in 20 years and decide to invest Ksh. 20,000 each year.  If the investment pays 20% interest compounded annually, the formula would be:

 

=FV(20%,20,-20000)

 

You would collect Ksh. 3,733,760 after 20 years.

ROUNDING OF NUMBERS.

 

The ROUND function rounds a given number to a specified no. of digits.

 

Syntax: ROUND(number,num_digits)

 

Number –is the no. you want to round.

 

num_digits – specifies the no. of digits to which you want to round the no.

 

Notes.

• If num_digits is greater than 0 (zero), then number is rounded to the specified no. of decimal places.

 

• If num_digits is 0 (zero), the number is rounded to the nearest Integer (whole no.).
• If num_digits is less than 0 (zero), then number is rounded to the left of the decimal point.

 

 

Example 1:

 

Formula                                 Description                                                                           Result

 

=ROUND(2.15,1)                                                                                                                  2.2

=ROUND(2.149,1)                                                                                                                2.1

=ROUND(21.5,-1)                 num_digits is less than 0 (zero),                                             20

 

Example 2: Cells A2:A4 contain values 20.3, 5.9 and -5.9.

 

Formula                     Description                                                                                       Result

 

=ROUND(A2,0)         Rounds 20.3 down, because the fractional part is less than .5             20

 

=ROUND(A3,0)         Rounds 5.9 up, because the fractional part is greater than .5             6

 

=ROUND(A4,0)         Rounds -5.9 down, because the fractional part is less than -.5             -6

=ROUND(-1.475,2)                                                                                                                -1.48

 

To Round a number to a near fraction.

 

For Example, cells A2 & A3 contain values 1.25 and 30.452.

 

Formula                     Description                                                                                        Result

 

=ROUND(A2,1)         Rounds the no. to the nearest 1 decimal place.

Because the portion to be rounded is 0.05 or greater,

the no. is rounded up                                                                          1.3

 

=ROUND(A3,2)         Rounds the no. to the nearest 2 decimal places.

Because the portion to be rounded, 0.002, is less than 0.005,

the no. is rounded down                                                                      30.45

GETTING THE LOGARITHM OF A NUMBER.

 

LOG – Gives the logarithm of a number to the base you specify.

 

Syntax: LOG(number,base)

 

Number -is the positive real no. for which you want the logarithm.

 

Base -is the base of the logarithm. If base is omitted, it is assumed to be 10.

 

 

Formula                                                                     Result

 

=LOG(10)                                                                   1

=LOG(8, 2)                                                                 3

=LOG(86, 2.7182818)                                                4.454347

GETTING THE BASE-10 LOGARITHM OF A NUMBER.

 

LOG10 – Gives the base-10 logarithm of a number.

 

Syntax: LOG10(number)

 

Number –is the positive real number for which you want the base-10 logarithm.

 

 

Formula                     Description                                                    Result

 

=LOG10(86)               Base-10 logarithm of 86                                 1.934498451

=LOG10(10)               Base-10 logarithm of 10                                 1

=LOG10(10^5)           Base-10 logarithm of 10 power 5                   5

 

MOD – Gives the remainder after a number is divided by a divisor.

 

Usually, the result has the same sign as the divisor.

 

Syntax: MOD(number,divisor)

 

Number -is the number for which you want to find the remainder.

 

If divisor is 0, MOD returns the #DIV/0! error value.

 

Description                            Formula                                             Result

 

Remainder of 3/2                    =MOD(3, 2)                                        1

Remainder of -3/2                  =MOD(-3, 2)                                      1

Remainder of 3/-2                  =MOD(3, -2)                                      -1

Remainder of -3/-2                 =MOD(-3, -2)                                     -1

 

COS – Gives the cosine of the given angle.

 

Syntax: COS(number)

 

Number – is the angle in radians for which you want the cosine.  If the angle is in degrees, multiply it by PI()/180 to convert it to radians.

 

Description                            Formula                                 Result in radians.

 

Cosine of 1.047 radians          =COS(1.047)                          0.500171

Cosine of 60 degrees              =COS(60*PI()/180)                0.5

 

SIN – Gives the sine of the given angle.

 

Syntax: SIN(number)

 

Number -is the angle in radians for which you want the sine.  If your argument is in degrees, multiply it by PI()/180 to convert it to radians.

 

Description                            Formula                                 Result in radians.

 

Sine of pi/2 radians                =SIN(PI()/2)                           1

Sine of 30 degrees                  =SIN(30*PI()/180)                 0.5

 

TAN – Gives the tangent of the given angle.

 

Syntax: TAN(number)

 

Number– is the angle in radians for which you want the tangent.

 

Description                            Formula                                 Result in radians.

 

Tangent of 0.785 radians        =TAN(0.785)                          0.99920

Tangent of 45 degrees            =TAN(45*PI()/180)                1

 

TRUNC – Truncates (shortens) a number to an Integer by removing the fractional part of the number.

 

Syntax:  TRUNC(number,num_digits)

 

Number -is the number you want to truncate.

 

Num_digits -is a number specifying the precision of the truncation.

 

Description                            Formula                                 Result

 

Integer part of 8.9                   =TRUNC(8.9)                         8

Integer part of -8.9                  =TRUNC(-8.9)                       -8

 

CALCULATING THE AVERAGE (ARITHMETIC MEAN) OF NUMBERS.

 

Use the AVERAGE function.  AVERAGE  gives the average (arithmetic mean) of the arguments provided.

 

Syntax: AVERAGE(number1,number2,…)

 

Number1, number2, … are numeric arguments for which you want the average.

 

Note.  The arguments must numbers or references that contain numbers.  If a reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

 

Example: Cells A2:A6 contain values 10, 7, 9, 27, & 4.

 

Description                                                    Formula                                             Result

 

Average all of nos. in the list                          =AVERAGE(A2:A6)                          11.4

Average cells A2 to A4 and A6                      =AVERAGE(A2:A4,A6)                    7.5

Average the nos. in cell A2 to A5, and 5       =AVERAGE(A2:A5, 5)                     11.6

 

CALCULATE THE SMALLEST OR LARGEST NUMBER IN A RANGE.

 

Use the MIN or  MAX functions.

 

MAX- Gives the largest value in a set of values.

 

MIN – Gives the smallest number in a set of values.

 

Syntax: MAX or MIN(number1,number2,…)

 

 

Number1, number2, .. are nos. for which you want to find the maximum or minimum value.

 

Notes. If the arguments in the reference contain no numbers, MAX or MIN returns 0 (zero).  Empty cells, logical values, or text in the reference are ignored.

 

 

 

Example: Cells A2:A6 contain values 10, 7, 9, 27 & 2.

 

Description                                                                Formula                                 Result

 

Largest number in the range                                       =MAX(A2:A6)                       27

Smallest number in the range                                     =MIN(A2:A6)                         2

Largest of the numbers in cells A2:A6, and 30          =MAX(A2:A6, 30)

Smallest of the numbers given, and 0                         =MIN(A2:A6,0)

 

CALCULATE THE MEDIAN OF A GROUP OF NUMBERS.

 

Median is the value at the center of an ordered range of nos.

 

Use the MEDIAN function.  MEDIAN gives the number in the middle of a set of numbers.

 

Syntax: MEDIAN(number1,number2,…)

 

Number1, number2, … are numbers for which you want the median.

 

Notes.

• Cells with the value zero are included.
• If there is an even number of nos. in the set, then MEDIAN calculates the average of the two numbers in the middle.

 

 

Example: Cells A2:A7 contain values 1, 2, 3, 4, 5 and 6.

 

Description                                                                                        Formula

 

Median of the first 5 nos. in the list                                                   =MEDIAN(A2:A6)

Median of all the nos. given, or the average of 3 and 4                     =MEDIAN(A2:A7)

 

MODE – Gives the most frequently occurring, or repetitive, value in a range of data.

 

Syntax: MODE(number1,number2,…)

 

Number1, number2, … are the  arguments for which you want to calculate the mode.

 

Note.

• Cells with the value zero are included.
• If the data set contains no duplicate data points, MODE returns the #N/A error value.

 

Example: Cells A2:A7 contain the values 5.6, 4, 4, 3, 2, and 4.

 

Description                                                                            Formula

 

Mode, or most frequently occurring no. in the list                 =MODE(A2:A7)

 

Tip. In a set of values, the Mode is the most frequently occurring value; the Median is the middle value; and the Mean is the average value.

 

COUNTING CELLS THAT CONTAIN NUMBERS.

 

COUNT – Counts the no. of cells that contain numbers within the list of arguments.

 

Syntax: COUNT(value1,value2,…)

 

Value1, value2, … are the arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

 

Note. Empty cells, logical values, text, or error values in the reference are ignored.

 

Example 1.

 

	A
1	Data
2	Sales
3	12/8/2007
4
5	19
6	22.44

 

Formula                          Description                                                           Result

 

=COUNT(A2:A6)            Counts no. of cells that contain nos. in the list      3 (date is a number).

 

=COUNT(A2:A3,A6)      Counts no. of cells that contain nos. in cells         2

A2:A3, & A6

 

Example 2.

 

 

	A
1	Sales
2	12/8/90
3
4	19
5	22.24
6	TRUE
7	#DIV/0!

 

Formula                     Description                                                                                          Result

=COUNT(A1:A7)       Counts the no. of cells that contain nos. in the list                               3

=COUNT(A5:A7)       Counts the no. of cells that contain nos. in the last 3 rows

of the list                                                                                             1

=COUNT(A1:A7,2)    Counts the no. of cells that contain numbers in the list,

and the value 2                                                                                      4

 

LOGICAL FUNCTIONS

 

Logical functions can be used either to test whether a condition is TRUE or FALSE or to check for multiple conditions.

 

For example, use the IF function to determine whether a condition is true or false.  One value is returned if the condition is TRUE, and a different value is returned if the condition is FALSE.

 

NOT –Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

If logical is FALSE, NOT returns TRUE; if logical is TRUE, NOT returns FALSE.

 

Syntax: NOT(logical)

 

Logical – is a value or expression that can be evaluated to TRUE or FALSE.

 

Formula                     Description                                                                Result

 

=NOT(FALSE)           Reverses FALSE                                                        TRUE

=NOT(1+1=2)             Reverses an equation that evaluates to TRUE           FALSE

 

AND – Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

 

Syntax: AND(logical1,logical2, …)

 

Logical1, logical2, .. are the conditions you want to test that can be either TRUE or FALSE.

 

Note.  The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be references that contain logical values.   If the specified range contains no logical values, AND returns the #VALUE! error value.

 

Example 1.

 

Formula                                             Description                                        Result                        

 

=AND(TRUE, TRUE)                        All arguments are TRUE                    TRUE

=AND(TRUE, FALSE)                      One argument is FALSE                     FALSE

 

=AND(2+2=4, 2+3=5)                       All arguments evaluate to TRUE        TRUE

 

 

 

 

Example 2.  Cells A2:A3 contain values 50 and 104.

 

If A2 contains a number between 1 and 100, then:

 

 

Formula                                                                                             Result                        

 

=AND(1<A2,A2<100)                                                                       TRUE

 

Suppose you want to display A3 if it contains a number strictly between 1 and 100, and you want to display a message if it is not.  If A3 contains 104, then:

 

Formula                                                                                             Result                        

 

=IF(AND(1<A3,A3<100), A3,”The value is out of range.”)         “The value is out of range”.

 

If A3 contains 50, then:

 

Formula                                             Description                                        Result                        

 

=IF(AND(1<A2, A2<100), A2, “The value is out of range.”)                   50, because A3 it between 1 & 100.

 

OR – Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

 

Syntax: OR(logical1,logical2,…)

 

Logical1,logical2,… are conditions you want to test that can be either TRUE or FALSE.

 

Notes.

• The arguments must evaluate to logical values such as TRUE or FALSE, or references that contain logical values. If the specified range contains no logical values, OR returns the #VALUE! error value.
• If a reference argument contains text or empty cells, those values are ignored.

 

Formula                                             Description                                                    Result

 

=OR(TRUE)                                       One argument is TRUE                                  TRUE

=OR(1+1=1,2+2=5)                            All arguments evaluate to FALSE                  FALSE

=OR(TRUE,FALSE,TRUE)               At least one argument is TRUE                      TRUE

 

Example:

 

Cells A2, A3 & A4 contain the values 15, 9, and 8.

 

Formula                                             Description                                                    Result

 

=AND(A2>A3, A2<A4)                     Is 15 greater than 9 and less than 8?               FALSE

 

=OR(A2>A3, A2<A4)                        Is 15 greater than 9 or less than 8?                 TRUE

 

=NOT(A2+A3=24)                             Is 15 plus 9 not equal to 24?                           FALSE

 

THE “IF” function.

 

IF is used to conduct conditional tests on values and formulas.

 

It evaluates a condition and returns one of two values, depending on the result of the evaluation.

 

If the condition is TRUE, IF returns one value.  If the condition is FALSE, IF returns the other value.

 

Format: IF(Condition, Action-to-be-taken when condition is true, Action-to-be-taken when condition is false)

 

Note. The condition is usually a logical formula.

E.g., A5=100 is a logical expression; if the value in cell A5 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.

 

Example 1.

 

Cell A2 contains the value 50.

 

On a budget sheet, cell A5 contains a formula to calculate the current budget.

 

 =IF(A2<=100,”Within budget”,”Over budget”)

 

If the result of the formula in A5 is less than or equal to 100, then the function displays “Within budget”.  Otherwise, the function displays “Over budget”.

 

Example 2.

 

=IF(A2=100,SUM(B5:B15),””)

 

In this example, if the value in cell A2 is 100, then the condition is TRUE, and the total value for the range B5:B15 is calculated.  Otherwise, condition is FALSE, an empty text (“”) is returned that leaves the cell that contains the IF function blank.

 

Example 3.

 

Suppose an expense worksheet contains in A2:A4 the data for “Actual Expenses” for January, February, and March: 1,500, 500 and 500.  Cells B2:B4 contains the data for “Predicted Expenses” for the same periods: 900, 900, and 925.

 

	A	B	C
1	Actual Expenses	Predicted Expenses
2	1500	900
3	500	900
4	500	925

 

You can write a formula in cell C2:C4 to check whether you are over budget for a particular month, generating text for a message:

 

Formula                                             Description                                                    Result

 

=IF(A2>B2,”Over Budget”,”OK”)     Checks whether the 1^st row is over budget            Over Budget

=IF(A3>B3,”Over Budget”,”OK”)     Checks whether the 2^nd row is over budget    OK

 

To check if a number is greater than or less than another number.

 

Cell A2, A3 & A4 contain the values 15,000; 9,000 and 8,000.

 

Formula                                             Description                                                    Result

 

=A2>A3                                              Is A2 greater than no. in A3?                         TRUE

 

=IF(A3<=A4, “OK”,”Not OK”)         Is A3 less than or equal to the no. in A4?       Not OK

 

NESTING FUNCTIONS WITHIN FUNCTIONS.

 

You can use a function as one of the arguments of another function.

 

Note. When a function is used as an argument, it must return the same type of value that the argument uses.

For example, if the argument returns a TRUE or FALSE value, then the nested function must return a TRUE or FALSE.  If it doesn’t, Ms-Excel displays a #VALUE error value.

 

Example 1.

 

=IF(SUM(K10:K19)>=5000,10%,5%)

 

In this example, the SUM function is being nested.  Assume that the result of the SUM function is 935.  Since the sum of the range K10:K19 is not greater than or equal to 5000, the result is 5%.

 

 

Example 2.  Cells A2, A3 and A4 contain the values 45, 90, 78.

 

Suppose you want to assign letter grades to numbers referenced by the name AverageScore.  See the table below.

 

If AverageScore is                                         Then return

 

Greater than 89                                               A

From 80 to 89                                                 B

From 70 to 79                                                 C

From 60 to 69                                                 D

Less than 60                                                    F

 

Formula                                                         Description                                                    Result

 

=IF(A2>89,”A”,IF(A2>79,”B”,                      Assigns a letter grade to the first score            F

IF(A2>69,”C”,IF(A2>59,”D”,”F”))))

 

=IF(A3>89,”A”,IF(A3>79,”B”,                      Assigns a letter grade to the second score            A

IF(A3>69,”C”,IF(A3>59,”D”,”F”))))

 

=IF(A4>89,”A”,IF(A4>79,”B”,                      Assigns a letter grade to the third score            C

IF(A4>69,”C”,IF(A4>59,”D”,”F”))))

 

In the above formula, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement.

For example, if the first logical_test / condition (AverageScore>89) is TRUE, “A” is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

 

EDIT/ CHANGE A FORMULA.

 

1. Select the cell containing the formula you want to edit.
2. Click in the Formula bar, make the changes to the formula, then press the ENTER

 

Note. If formulas are not used, there will be no automatic recalculation when any of the numbers change.

 

Automatic recalculation:

 

This means that, Spreadsheets are able to calculate values such as SUM, AVERAGES, PERCENTAGES, etc automatically without requiring the intervention of the user (or without putting the user into the hard task of thinking).

 

RELATIVE REFERENCES

 

When you create a formula, cells or ranges of cells will be referred to based on their position relative to the cell that contains the formula.  If cell B6 contains the formula =A5; Ms-Excel finds the value one cell above and one cell to the left of B6.

If the position of the cell that contains the formula changes, the reference is changed.

 

A Relative reference is a cell reference, which changes automatically when the formula is copied to another cell or range.  It describes the location of a cell in terms of its distance (in rows and columns) from another cell.

 

Note. When you copy a formula containing relative references down or across from one cell to another, Ms-Excel adjusts the references in the pasted formula automatically to refer to a different cell that is the same no. of rows & columns away from the formula.

 

Example 1:

 

If the formula in cell B6 (i.e., =A5) (which is one cell above & one cell to the left of B6) is copied to cell B7.  Ms-Excel will adjust the formula in cell B7 to =A6, which refers to the cell that is one cell above and one cell to the left of cell B7.

 

Example 2:

 

If cell A3 contains the formula =A1+A2, and you copy cell A3 to cell B3, the formula in cell B3 becomes =B1+B2.

 

ABSOLUTE REFERENCES.

 

In a formula, an Absolute cell reference is the exact address of a cell, regardless of the position of the cell that contains the formula.

 

An absolute cell reference takes the form $A$1, $B$1, etc.

 

 

 

 

The table below shows the different types of references.

 

Reference:	Effect on a cell reference
A1	Relative reference.
$A$1	Both rows and column references are absolute
A$1, B$1	Absolute row reference, i.e., only the row reference is absolute.
$A1, $B1	Absolute column reference, i.e., only the column reference is absolute.

 

Unlike relative references, Absolute references don’t automatically adjust when you copy formulas across rows and down columns.  For example, if you copy an absolute reference in cell B2 to cell B3, it stays the same in both cells.

 

Therefore, if you don’t want Ms-Excel to adjust references when you copy a formula to a different cell, i.e., if a formula refers to a particular cell and you would like to copy it such that the subsequent copies of cell references still refer to that same cell reference, you must use Absolute referencing.

 

For example,

 

If your formula multiplies cell A5 with cell C1 (=A5*C1), you can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that you don’t want them to change.

 

To create an absolute reference to cell C1, for instance, add dollar signs to the formula as follows:  =A5*$C$1.

Note.  To enter the dollar sign in a cell reference; move the cell pointer in the cell reference to be made absolute, then press the function key F4 or the keyboard combination SHIFT+4.

 

Worked Example:

 

	A	B	C	D	E	F	G	H
1	ABC Company Sales Performance Report
2
3	Salesman	Target	Qtr1	Qtr2	Qtr3	Qtr4	Total	Commission
4	Albert	750	148	156	171	140	615	=G4*$A$13
5	Carl	650	122	131	153	118	524	=G5*$A$13
6	Cornell	800	211	243	246	250	950	=G6*$A$13
7	Edwin	700	129	150	92	218	589	=G7*$A$13
8	Francis	1,000	311	270	247	322	1,150	=G8*$A$13
9
10	Totals	3,900	921	950	909	1,048	7,728	=G10*$A$13
11
12	Commission Rate
13	15% (or 0.15)

 

To get the commission for each salesperson, the formula =G4*$A$13 is entered in cell H4.  The dollar sign ($) indicates an absolute reference to the cell A13.  This means that, whenever the commission rate formula is copied, it always refers to cell A13.

 

When the formula =G4*$A$13 is copied down to H10, the results will be as shown.

 

1. Determine which cell reference is to be absolute, e.g., in the example above cell A13.
2. Type the dollar sign ($) just before the part of the cell reference that you want to remain exactly the same when you copy the formula to another cell.
3. Copy the formula to the rest of the cells.

 

FORMULAS AND ERROR VALUES.

 

If a formula cannot properly evaluate a result, Ms-Excel will display an error value.

 

For example, error values can be as a result of using text where a formula expects a numeric value, deleting a cell that is referenced by a formula, or using a cell that is not wide enough to display the result.

 

1. #####

Causes.

 

• This error value occurs when the cell contains a number, date or time that is wider than the cell.

-OR-

• It occurs when the cell contains a date or a time formula that produces a negative result.

 

Suggested action.

 

• Increase the width of the column by dragging the boundary between the column headings.

 

• Apply a different number format in the cell to make the number fit within the existing cell width. g., decrease the no. of decimal places after the decimal point.

 

• Ensure that the data and time formulas are correct. When you subtract dates and times, make sure you build the formula correctly.

 

If a formula has a result of a negative value, you can display the value by formatting the cell with a format that is not a date or time format.

 

1. Click Cells on the Format menu, click the Number tab, then select a format that is not a date or time format.

 

 

2. #N/A

 

The #NA error value occurs when a value is not available to a function or formula.

 

Possible cause                                                                  Suggested action

• Omitting 1 or more arguments in a function           Enter all arguments in the function.

 

• Using a custom worksheet function that is Make sure the function is working

not available                                                                         properly

 

3. #DIV/0!

 

This error value occurs when you enter a formula that contains a division by zero (0).

E.g., =5/0.  It may also occur when you divide a cell by another cell that is blank.

 

Suggested action.

• Change the divisor to a number other than zero.

 

4. #NULL!

 

The #NULL! error value occurs when you specify an intersection of two areas that do not intersect. i.e., using an incorrect range operator or using an incorrect cell reference.

 

Suggested action.

 

• Use the correct range operator. For instance, to refer to two areas that don’t intersect, use the Comma.

E.g. if the formula sums two ranges, separate the two ranges with a comma.

 

=SUM(A1:A9,C1:C6).

 

• Check for typing errors in the reference to the ranges.

 

5. #NAME?

 

The #NAME? error value occurs when Ms-Excel doesn’t recognize text in a formula.

 

 

Possible cause                                                                  Suggested action

• Deleting a name used in the formula, or                 Make sure the name exists.

using a name that does not exist

 

• Misspelling the name of a function Correct the spelling.

 

• Omitting a colon (:) in a range reference Make sure all range references in the

formula use a colon.

E.g. =SUM(A1:A5)

 

• Entering text in a formula without                          Enclose text in the formula in double

enclosing the text in double quotation marks (“).   quotation marks.

Ms-Excel tries to interpret your entry as a name     E.g. the following formula joins a

even though you intended it to be used as text.       piece of text “The total amount is”

with the value in cell B5:

=”The total amount is” & B5

6. #NUM!

 

This occurs when a problem occurs with a number in a formula or function.  E.g., Entering a formula that produces a number to a number that is too large or too small to be represented in Ms-Excel.

 

Suggested action

• Change the formula so that its result is between – 1*10³⁰⁷ and 1*10³⁰⁷.

 

7. #VALUE!

 

The #VALUE error value occurs when the wrong type of argument or operand is used.

 

8. #REF!

 

The #REF error value occurs when a cell reference is not valid.  For instance, deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas.

 

Suggested action

• Change the formulas, or restore the cells on the worksheet by clicking Undo immediately after you delete or paste the cells.

 

Exercise.

 

1. You have entered a formula to add the contents of B5 and C4 in cell F5. What will it become when you copy it to cell H8?

 

Explain the reason for your answer.

2. What causes the following error messages in Microsoft Excel. Show how we can solve them.

• #####
• #VALUE!
• #DIV/0!
• #NAME?

3. The first column in the table below contains formulas as entered into the cell D46. In the second column, enter the formulas as they would appear when copied to B56.

 

Formula in D46	Formula when copied to B56
=D1
=F5*C10
=H$46+J40
=$E12-D$14*$F$2

 

DATA SORTING.

Sorting is the process of arranging data within a range in a particular order.

Purpose.

√    Sorting helps in arranging data in some order of priority, i.e., from lowest to highest or from highest to lowest.

√    It also helps to quickly locate the highest or lowest value in a list.

 

When you sort, Ms-Excel rearranges rows, columns, or individual cells by using the sort order that you specify.  You can sort a list in Ascending (1-9, A-Z) or Descending order (9-1, Z-A).  You can perform a sort based on the contents of one or more columns.

 

Note. The data is sorted in reference to columns.

 

To sort rows in ascending or descending order based on the contents of one column.

 

1. Click a cell in the column by which you want to sort. The column on which the list is arranged is known as the Key.
2. To arrange the data from lowest to highest, click on the Sort Ascending button on the toolbar. To arrange the data from highest to lowest, click on the Sort Descending button on the toolbar.

-Or-

On the Data menu, click Sort.  In the Sort by box, click the column you want to sort and then choose the sort order.

 

To sort rows based on the contents of two or more columns.

 

1. Click a cell in the list you want to sort.
2. On the Data menu, click Sort to display the Sort dialog box.
3. Under Sort By, specify the first column by which you want to sort, then choose the sort order by clicking on the Ascending or Descending
4. You can add up to two keys in the Then By boxes according to your need.

 

Assume that you need to sort by more than 3 columns, i.e., your list contains employee information and you need to organize it by Department, Title, Last Name, and First Name, sort the list twice.  Click First Name in the first Sort by box and then sort the list.  Click Department in the second Sort by box, click Title in the first Then by box, and click Last Name in the second Then by box, and then sort the list.

 

5. Select any other sort options you want, then click OK to perform the sort.

 

Sort columns based on the contents of rows.

 

1. Click a cell in the list you want to sort.
2. On the Data menu, click Sort.
3. Click the Options
4. Under Orientation, click Sort left to right, then click OK.
5. In the Sort by and Then by, click the rows you want to sort.

 

Examples:

 

• A teacher may arrange pupil’s records according to the marks scored in a test, starting with the highest to the lowest in order to assign class positions.
• An Accountant may arrange a list of financial records according to the date of the transaction and customer name in order for him to be able to quickly locate any record using the transaction date and name of customer.
• Arranging a telephone list according to alphabetical order of last name in order to easily locate a name and phone number. If there are several people with similar last names, you can specify two keys such that the records are arranged in order of first name as well.

 

LINKING WORKSHEETS.

 

Purpose.

 

• Sheets are normally independent. If a change in a value in one sheet is intended to affect other values in different sheets, it is advisable to link the sheets so that Ms-Excel will automatically update the affected values if you make any changes.

 

Alternatively, you can calculate the new values and manually make the changes to all the other sheets.  This would be unreliable & cumbersome especially if this is to be done for many values in many large worksheets.

 

1. When typing in a formula that refers to a cell in another sheet, include the name of the sheet before that particular cell reference separated by a colon.

 

Example 1:

 

The formula =G6*Sheet1!B6 (instead of =G6*B6) will refer to B6 in Sheet 1 rather than in the current sheet.

 

Example 2:

 

Typing the formula =Salesinfo!A10 in cell A10 of Sheet2 will cause the contents of cell A10 in the Sheet named Salesinfo to be also the contents of A10 in Sheet2.

 

Exercise.

 

• How would you display the contents of the cell B45 of worksheet named Price in the cell B5 of worksheet named Sales such that the two cells always display the same value?

 

CREATING CHARTS.

 

Purpose.

√    Charts are used to present data effectively.  They make relationships among numbers easy for users to see because they turn numbers into shapes that can be compared to one another.

For instance, rather than having to analyze several columns on worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales.

 

Different Types of Charts and their uses.

 

• Line Chart: A Line graph is used to show trends.

 

• Bar Chart: – It can be used to show comparison of Sales and Target.

 

• A Stacked Bar Chart: – It can be used to show the distribution of sales by month and compare the performance of salesmen.

 

• A Pie Chart: shows the distribution of sales.

 

• Scatter

 

• Column Charts.

 

Steps required when creating a simple chart.

 

1. Enter the data you want to be represented in the chart on the worksheet.
2. Select the cells or range that contains the data you want to be represented in chart.

 

If the cells you want to select for your chart are not in a continuous range,

 

• Select the first group of cells that contain the data you want to include.
• Hold down the CTRL, then select any additional cell groups you want to include. The nonadjacent selections must form a rectangle.

 

3. On the Insert menu, click Chart (or click the Chart Wizard button on the Standard toolbar). Then use the Chart Wizard to help you through the process of choosing the chart type and the various chart options.

 

4. Select Chart type.

 

• In the Chart type dialog box, click the Standard Types tab or the Custom Types
• Under Chart Type, click the chart category you want to use, then select the type of chart under Chart subtypes on the right.

A brief description of the chart selected appears below the sub-types.  This helps you decide whether the type suits the data you have selected.  You may also view a sample of the chart that will be produced.

 

• Click on the Next

 

5. Specify the range of cells to include in the chart.

 

This gives you a chance to select your range again if necessary.

 

In the Chart Source data dialog box,

• Click the Data range tab, then confirm the selected range or enter a new range.
• Under Series in, click an option to change the way in which the data should be plotted; across Rows or down Columns. To help you decide the right option, the sample chart changes according to the selection you have made.
• If you are sure about the range you have selected and the sample chart is what is desired, click on the Next

 

6. Select the Chart options.

 

In this step, there are several chart options as indicated by the various categories at the top of the Chart Options dialog box.

 

• Click the Titles Click in the Chart Title area and type in the title for your chart.  Type in the titles for axes in their respective places.
• Click the Legend tab, and then select the Show Legend Under Placement, click an option to show where the legend will be placed.
• Click on the Next

Legend – A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.  A legend indicates which color (pattern) represents what data item.

 

Axis – A line that borders one side of the plot area, providing a frame of reference for measurement or comparison in a chart.

For most charts, data values are plotted along the value axis, which is usually vertical (Y-axis), and categories are plotted along the category axis, which is usually horizontal (X-axis).

 

Note. Ms-Excel creates the axis values from the worksheet data.

 

7. Select the Location of Chart placement.

 

This step involves placing the chart you have created.

You can create a chart as an embedded object on the sheet you are working on or on its own sheet.

 

• Under Place chart, click an option either to insert the chart as a new sheet or as an object in the current data sheet.

 

8. Click on the Finish

 

To change the Chart type.

 

1. Click the chart to activate the Chart
2. On the Chart menu, click Chart Type.
3. Click the Standard Types tab, click the inbuilt chart type you want to use, then click the OK button

 

Changing Chart Options.

 

• Click the chart. On the Chart menu, click Chart Options to display the Chart Options dialog box.

 

To add or change the Chart Title.

 

1. Click the Titles tab.
2. Click in the Chart title box, and then type the text for the title.

 

To add a Legend to a chart.

 

1. Click the Legend
2. Select the Show legend check box.
3. Under Placement, click the option you want.

 

Note.  When you click one of the Placement options, the legend moves, and the Plot Area (area bounded by the axes) automatically adjusts to accommodate it.

 

To change Data series names or the Legend text.

1. Click the chart. On the Chart menu, click Source Data.
2. On the Series tab, click the data series name you want to change.
3. In the Name box, specify the worksheet cell you want to use as the legend text or data series name. You can also type the name you want to use.

 

To edit the Chart title and the Axes titles.

 

1. On the chart, click the title you want to change.
2. Type the new text you want.
3. Press the ENTER key.

 

How worksheet data is represented in a chart.

 

A chart is linked to the worksheet data it’s created from and is updated automatically when you change the worksheet data.

 

To change the Cell range used to create a chart.

 

1. Click the chart.
2. On the Chart menu, click Source Data, then click the Data Range
3. Make sure the entire reference in the Data range box is selected.
4. On the worksheet, select the cells that contain the data you want to appear in the chart.

If you want the column and row labels to appear in the chart, include the cells that contain them in the selection.

To include a new range into an existing chart.

This helps to add information not already in the chart.

1. Select the range you want to add in the chart.
2. Position the mouse pointer along the edge of the selected range until it changes into an arrow.
3. Drag the range into the chart.

Ms-Excel will automatically update the chart so that it includes the new range.

To change the Placement of a chart.

 

1. Click the chart. On the Chart menu, click Location.
2. To place the chart on a new chart sheet, click As new sheet, then type a name for the new chart sheet in the As new sheet

 

To place the chart as an embedded object on a worksheet, click As object in, click a sheet name in the As object in box, and then click the OK button.

 

Drag the embedded chart where you want it on the worksheet.

 

To Move and resize chart items by using the Mouse.

 

1. Click the chart item you want to move or resize.

 

To move an item, point to the item, then drag it to another location of the sheet.

 

To resize a chart item, point to a Sizing handle.  When the pointer changes to a double-headed arrow, drag the sizing handle until the item is the size you want.

 

Delete data from a chart

To delete data from both the worksheet and the chart.

 

1. Delete the data from the worksheet. The chart will be updated automatically.

 

To delete data from the chart only.

 

1. Click the data series you want to delete.
2. Press the DELETE key.

 

To delete data labels, titles, or legends in a chart.

 

1. Click the chart item you want to delete.
2. Press the DELETE key.

 

To change the Font, font Size, font Colour of text in a chart or make the text Bold, Italic or Underlined.

 

1. Click the chart text, or select the individual characters you want to format.
2. On the Formatting toolbar, click a button for the format you want.

 

Setting up a chart for printing.

 

You can adjust where the chart will print on the page by sizing and moving the chart with the mouse in Page break view.

 

1. Click the worksheet outside of the chart area.
2. On the View menu, click Page Break Preview.
3. To set printing options for a chart sheet, click Page Setup on the File
4. Click the Chart tab, and then select the options you want.

 

Tip. To print an embedded chart without its associated worksheet data, click the embedded chart to select it, and then follow the above instructions for chart sheets.

 

Chart area – the entire chart and all its elements.

 

To zoom or size the display of a chart sheet.

 

1. Click the tab for the chart sheet.
2. Click Zoom on the View menu, then click the option you want.

To size the chart sheet so that it fills the entire workbook window, click Sized with Window on the View menu.  When a chart sheet is sized with the window, you cannot zoom in or out of it.

 

To view an embedded chart in a separate window.

 

1. Click the embedded chart you want to see in its own window.
2. On the View menu, click Chart Window.

 

Printing the chart.

 

Purpose.

• To create a paper copy of the chart to present to other people.
• To maintain a paper filing system alongside the computer filing system.

 

1. To print both the worksheet and the chart, click on the Print button on the toolbar.

To print the chart only, select the chart by clicking on it, then select Print on the File menu.

2. Under Print what in the resulting dialog box, click on Selected Chart, and then choose OK.

 

Exercise.

 

1. (a). What is a Chart?

(b). What are the steps required when creating a simple chart?

2. The chart has ‘Thousands’ displayed along the Y-axis, yet the figures are in Millions of Kenya Shillings. How do you change the chart such that it displays ‘Millions of Kenya Shillings’ instead?
3. You want the legends to become the X-axis titles and the X-axis titles to be used as the legends. How do you implement this?

 

PRINTING A WORKSHEET.

 

Purpose.

√    Whenever you need a paper copy of the worksheet to present to other people.

√    If you maintain a paper filing system alongside the computer filing system.

 

PAGE SETUP.

 

You can control the appearance or layout of printed worksheets by changing options in the Page Setup dialog box.

 

Purpose.

• To define where one page ends and another page starts.
• To print a large worksheet to fit on a single page.
• To add descriptive information to be printed with your worksheet.
• To define rows and columns you want to print on each page of the output.
• To change the order in which various worksheets should print.

Setting the Page Margins.

 

1. Select the worksheet you want to print.
2. On the File menu, click Page Setup, then click the Margins
3. In the Top, Bottom, Left, and Right boxes, enter the margin size you want.

 

You can also tell Ms-Excel to automatically position your worksheet at the center of the page, both horizontally (across) and vertically (downwards) by clicking the options under Center on Page.

 

To set Header or Footer margins.

 

• To change the distance from the top edge to the header, enter a new margin size in the Header

 

• To change the distance from the bottom edge to the footer, enter a new margin size in the Footer

These settings should be smaller than your top and bottom margin settings.

 

Tip.   To see how the margins will affect the printed document, click Print Preview before the document is printed.

 

Setting the paper Orientation.

 

Orientation specifies how the worksheet will be printed on a page.

 

1. On the Page Setup dialog box, click the Page
2. Under Orientation, click Portrait or Landscape.

 

To set the Paper size for printing.

 

1. Click the Page
2. In the Paper size box, select the size of paper you want to use from the resulting drop down list.
3. If you want Ms-Excel to fit your worksheet on one page; under Scaling, click on Fit to and make sure that the specification is “Fit to: 1 page(s) wide by 1 tall”. 

Setting the print Quality.

 

You can speed up the time it takes to print a worksheet by temporarily changing the printing quality.

 

1. Click in the worksheet.
2. On the File menu, click Page Setup, then click the Page
3. In the Print quality box, click the resolution you want to use.

 

To print in Draft quality,

 

• Click Draft in the Print quality box.

 

Note. Draft quality increases printing speed by ignoring formatting and most graphics.

 

To add header and/or footer comments to be printed with your worksheet.

 

1. On the Page Setup dialog box, click the Header/Footer
2. Under Header, type in the information you want to appear at the top of each page.

Under Footer, type in the information you want to appear at the bottom of each page.

Alternatively, you can click on the arrow on the right of the Header or Footer to reveal a list of preset headers and footers and then select one of them.

 

To print with or without Cell gridlines.

 

1. On the Page Setup dialog box, click the Sheet
2. Select or clear the Gridlines

 

Note. Worksheets print faster if you print without gridlines.

 

To print the Row and Column headings.

 

Row headings are the row numbers to the left of the worksheet.  Column headings are the letters that appear at the top of the columns on a worksheet.

 

1. On the Page Setup dialog box, click the Sheet
2. Select the Row and column headings

To specify which areas of the sheet you want to print, click in the Print Area box, then drag through the worksheet the areas that you want to print.

 

Choosing the printer.

 

There are many types of printers and Ms-Excel communicates with each one differently.  Therefore, you have to tell Ms-Excel which printer is connected in order to get the right results.

 

1. On the File menu, select Print to display the Print dialog box.
2. Select the printer you want to use from the list of printers shown in the Name

 

Note.  If the wrong printer has been selected in the Printer Setup, the printer will produce funny characters (garbage) when you order Ms-Excel to print the worksheet.

 

To define what part of the worksheet to print.

 

Purpose.

• To select a certain portion of the worksheet for printing.

 

To print a selected area of a worksheet.

 

1. On the View menu, click Page Break Preview.
2. Select the area you want to be printed.
3. Right-click a cell within the selection (or on the File menu, point to Print Area), then click Set Print Area.

When you save the document, your print area selection is also saved.

Note. Page break preview – shows you what data will go on each page so you can adjust the print area and page breaks.

To print a selection, or the active worksheet(s).

 

1. To print a specific selection, select the range of cells to print.
2. On the File menu, click Print.
3. Under Print what, select an option to print, i.e., Selection or Active sheet(s).

When you choose Selection, Ms-Excel prints the selection and ignores any print area defined on for printing on the worksheet.

 

To print more than one copy at a time.

 

1. In the Number of copies box, enter the number of copies you want to print.

 

Preview a page before printing

 

1. Click Print Preview on the File menu (or on the Standard toolbar) to see a picture of how your worksheet will look when it is printed on a paper.
2. Use the buttons on the toolbar to look over the page or make adjustments before printing.
3. If what you see in the Print Preview screen satisfies you, click the Print button on the toolbar to start printing.
4. To return to the normal Ms-Excel screen, click the Close button in the Print Preview

 

Print preview displays the printed page so you can adjust columns and margins. The way pages appear in the preview window depends on the available fonts, the resolution of the printer, and the available colors.