Ms-Excel 2003

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 box.
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 button.
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:
(i). 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.
(ii). 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.
(iii). 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.

- 95 -
 Ms-Excel 2003
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.
1. 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.
(i). Line Chart: A Line graph is used to show trends.
(ii). Bar Chart: - It can be used to show comparison of Sales and Target.
(iii). A Stacked Bar Chart: - It can be used to show the distribution of sales by month and
compare the performance of salesmen.
(iv). A Pie Chart: shows the distribution of sales.
(v). Scatter Charts.
(vi). 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.

- 96 -
 Ms-Excel 2003
4. Select Chart type.
 In the Chart type dialog box, click the Standard Types tab or the Custom Types tab.
 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 button.
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 button.
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 tab. 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 box. Under Placement, click an
option to show where the legend will be placed.
 Click on the Next button.
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 button.

To change the Chart type.

1. Click the chart to activate the Chart menu.
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.
1. Click the chart. On the Chart menu, click Chart Options to display the Chart Options
dialog box.

- 97 -
 Ms-Excel 2003
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 tab.
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 tab.
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 box.
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.

- 98 -
 Ms-Excel 2003
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 menu.
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?

- 99 -
 Ms-Excel 2003
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 tab.
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 box.
 To change the distance from the bottom edge to the footer, enter a new margin size in the
Footer box.
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 tab.
2. Under Orientation, click Portrait or Landscape.
To set the Paper size for printing.
1. Click the Page tab.
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”.

- 100 -
 Ms-Excel 2003
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 tab.
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 tab.
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 tab.
2. Select or clear the Gridlines checkbox.
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 tab.
2. Select the Row and column headings checkbox.
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 box.
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.
- 101 -
 Ms-Excel 2003
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 screen.
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.

- 102 -
 Ms-Excel 2003
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 toolbar.

TYPES OF DATA IN SPREADSHHETS.

(1). 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.
(2). 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 + - ( ) / $ %.
(3). 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.
(4). 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.

- 68 -
 Ms-Excel 2003
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 toolbar.
-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 checkbox.
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.
- 69 -
 Ms-Excel 2003
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 Exit.
-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.

- 70 -