USING MICROSOFT EXCEL

Microsoft Excel is a spreadsheet program that is used to record and analyze numerical data. Think
of a spreadsheet as a collection of columns and rows that form a table. Alphabetical letters are
usually assigned to columns and numbers are usually assigned to rows. The point where a column
and a row meet is called a cell. The address of a cell is given by the letter representing the column
and the number representing a row.

Starting (Launching) Excel

To start Excel:
1. Click on the Windows Start button to access the start menu
2. Choose Programs, Microsoft Excel.

The Excel worksheet consists of various elements. In order for you to be able to capture and
manipulate data, it is necessary to be familiar with certain elements found in the worksheet as well
as with some of the terminology associated with spreadsheets.

The Excel Workbook Structure

The workbook window displays the workbook – an Exce l file in which you work and store your
data. A workbook is made up of worksheets. Different types of sheets may appear in a workbook.

The components of the workbook window are the workbook itself, scroll bars, sheet tabs, and stab
scrolling buttons.

Workbook: The default Excel workbook contains 3 worksheet names Sheet1 through
Sheet3. The sheet names appear on tabs at the bottom of the workbook. A new Excel
workbook file can contain up to 255 separate worksheets. The Excel worksheet is a grid of
256 columns by 65,536 rows. Columns are designated by letter running down across the
top of the worksheet, and rows are designated by numbers running down the left border of
the worksheet. Column headings begin with letter “A” an d continue through the letter “Z.”

pg. 1
 After the 26th column (Colu mn Z), headings become double letters, From “AA” to “IV”.
Row headings begin with number 1 and continue through the number 65536.
Cells: the intersection of a column and a row. Cells are typically referred to by their column
and row location. For instance, column A, row 1 is cell A1. You enter data (text or
numbers) directly into any cell that is active. A thick, dark border distinguishes the active
cell.
Formula bar: a bar located near the top of the window that displays the constant value or
formula used in the active cell.
Scroll bars: to the right of and below the worksheet grid. Use the scroll bars to display
different areas of the active worksheet.
Sheet tabs: at the bottom of the workbook. You can click on the sheet tabs to move from
one sheet to another in a workbook.
Tab scrolling buttons: to the left of the sheet tabs. You can click on the tab scrolling
buttons to scroll the display of sheet tabs one at a time, or to display the first or last grouping
of sheet tabs within a workbook.

The other parts that complete the full Excel screen are the standard elements associated with a
“Window”, for example, a title bar, a menu bar, a toolbar, etc. Detailed explanations of these
elements is found in Microsoft word.

ENTERING AND CORRECTING DATA

For Creating a Worksheet, first, consider your objectives for creating a worksheet and the type of
information you want to include. Get some idea of how you want the worksheet to look. For
example, do you want the months to appear across a row or down a column?

You might find it helpful to sketch a draft on a piece of paper to create the structure before you
begin typing data. When you are entering data in an Excel worksheet, the suggested order is:

1. Type the text information to create a structure.

pg. 2
 5. Format the text, numbers, and formulas.

I. Entering Data

To create your own workbook in Excel, you will need to enter data. To enter data:

1. Select, or activate, the cell in which you want to display the data; use the mouse pointer to
point to a cell and then click to select it or use the arrow movement keys on your keyboard
to select a cell.
2. Type the data.
3. Enter the data into the cell by using any of these techniques:
• press the <Enter> key;
• click on the <Enter> button in the formula bar (the boxed checkmark);
• or press any of the keyboard movement keys, such as <Right Arrow> or
<Tab>.

Worksheet cells can contain constant values (text or numbers) or formulas. In a worksheet, text is
used to organize and identify the numerical information. By default, text is left aligned in the cell.

Figure 1: An example of text and number entered in a worksheet

pg. 3
 II. Entering Numbers

By default, numbers are displayed right aligned as you type them.

Saving a File

The File, Save and the File, Save as… Commands

Until it is saved, a workbook exists only in computer memory, which is a temporary storage place.
For permanent storage, a workbook must be saved to a disk. It is important to save your work
frequently (every 10 to 15 minutes).

Although it is not necessary, if you select cell A1 before saving the file, the upper-left corner of
the active worksheet will be displayed when the file is reopened. This helps you to orientate
yourself when you are working with a large worksheet.

Choose File, Save to save changes to an existing workbook.

Choose File, Save As to save a file for the first time, or to save the changes in a file with a different
name (other than the one specified in the title bar), in a different location, or in a different format.

To choose the correct file location when using the Save As dialog box, expand the Save In list
box and select the appropriate folder.

USING FORMULAS

Formulas are instructions that you enter to perform calculations.

You can create formulas by using numbers (for example, 350+450); however, it is preferable to
construct formulas that refer to worksheet cells. This way, the results of the formulas update
automatically when you change numbers in your worksheet. In Excel, you create formulas by
preceding the expression with an equal sign (=). For example, if you were to enter the number 350
in cell B1, the number 450 in B2 and the formula = B1 + B2 in cell, the value 800 would displayed

pg. 4
in cell B3. were you subsequently to change the value in cell B1 or B2, Excel would recalculated
the value of cell B3.

When entering a formula, you can type in cell references or click on the desired cells. Clicking on
the cells may eliminate typing errors and prevent mistakes as you determine the cell reference by
looking at them.

➢ Mathematical Operators

The mathematical operators that are used in an electronic environment differ slightly from those
used manually. Table 1 below compares the symbols used.

Table 1: Mathematical Operators

FUNCTION MANUAL COMPUTER

Division ÷ /

Multiplication X *

Addition + +

Subtraction – –

In the above table the two shaded operators are those that require your special attention. The
symbol used to indicate exponentiation (i.e to the power of) is the caret (Ù) symbol.

Table 2: Exponentiation

MANUAL COMPUTER RESULT

102 10^2 100

103 10^3 1000

To remove the contents of a single cell, select the cell and press <Delete>.

To reverse you last action in Excel, click on the Undo button (or choose Edit, Undo). The speed
key combination <Ctrl + Z will have the same effect.

pg. 5
Note: Not all actions can be undone in Excel. For example, you cannot undo a File, Save.

To close a single file, choose File, Close.

Creating a New Workbook

When you launch Excel, you see a new, blank workbook called Book 1, However, should you
require a new workbook at a later stage you do not have to restart Excel to get a new workbook.
To create a new workbook without restarting Excel, click on the New button, or choose File, New

…. The speed key combination of <Ctrl> + N will have the same result.

Formatting a Worksheet

Formatting changes the way that numbers and text are displayed in the worksheet. For example,
the number ten can appear as 10, 10.00, R10, and so on. Applying a format changes only the
appearance of a number, not its value. You can change the appearance of text on a worksheet in a
number of ways, for example, you can emphasize certain words by applying boldface or
underlining.

Categories of Formatting may be summarized as follows:

Number: Changes the way numbers, dates and times are displayed

Alignment: Governs where data is positioned within a particular cell

Font: Controls the style and typeface of data

Border: Applies and controls the style of lines which are drawn around cells

Patterns: Used to add a shaded colour or pattern to the background of a cell

In addition to the above, setting column-widths and row-heights are also considered part of
formatting a worksheet. On this course we will investigate some of the font style options as well
as number, alignment, border and patterns formatting briefly.

pg. 6
Working with Ranges

A range is a group adjacent cells that forms a rectangular shape. Before you can use a cell or a
range of cells in a worksheet, it is necessary to indicate which cell or range of cell you want to
work with. This process is known as selecting. So far you have only been working with a single

cell at a time. It may be necessary to work with more that one cell at a time, for example when
formatting.

Selecting Cells

There are various methods that may be used to select a range of cells. Two of these are as follows:

➢ With the mouse pointer, point to the centre of the cell in one corner of the range; press and
hold the mouse button while dragging to the opposite corner of the range; and release the
mouse button.
➢ Select one corner of the range; press and hold the <Shift> key; and click on the opposite
corner of the range to select all cells in between.

Using Toolbar Buttons to Format a Worksheet

You can use button on the Formatting toolbar to quickly change the appearance of all or part of a
worksheet. Select the cell or cells whose appearance you would like to change, and then click on
the buttons of your choice. For example, to apply boldface to text in cell B5, select cell B5 and
then click on the Bold button.

Some Formatting button are toggles. To remove the formatting after it has been applied, select the
cell whose formatting you would like to remove, and click on the corresponding button on the
Formatting toolbar.

Adding Border and Colour to Cells

To emphasize data, you can add borderline or colour to cells. A variety of border types, widths,
and colours are available in Excel. To apply borders or colours, select the cells that you want to

pg. 7
affect; click on the drop-down arrow next to the Borders or Fill Colour buttons on the Formatting
toolbar; and selected an option from the palette.

NUMERIC FORMATS

Selecting the Entire Worksheet

You can select the entire worksheet by clicking on the Select All button, which is the intersection
of the row and column headings in the upper-left corner of the worksheet.

Categories of Numeric Formats

Many number formats can be applied by using the Formatting toolbar. Number formats are divided
into several categories, such as Currency, Percentage, Scientific Accounting, and Fraction. The
options not available on the toolbar may be selected through the Format Cells dialog box (accessed
from either Format menu or the shortcut menu).

Details of Numeric Formats

You can use Excel’s built-in formats to change the appearance of numbers. In the Format Cells
dialog box, select the Number tab and choose the options you would like to use and click on OK.
Some of the commonly used numeric formatting options may also be accessed using the formatting
toolbar.

The figure below indicate the button available on the Formatting toolbar by default.

Currency Comma Decrease Decimal

pg. 8
 Percentage Increase Decimal

Figure 2: Formatting Tools

The numeric formatting buttons as listed in Figure 1, are used to produce the following formats:

Currency

Applies a currency symbol to a cell or range of cells.

Percentage

Multiplies the cell contents by 100 and place a % symbol to the right of the number

Comma

Enable two formatting features. Firstly, all negatives will be displayed in brackets. Secondly, a
comma (or space – depending o n the settings of your computer) will appear as a separator between
millions and thousands. For example, 1000000 will be displayed as 1,000,000 or 1 000

000.

Increase Decimals

Each time you click on the Increase Decimal button, an extra decimal place is displayed to the
right of the decimal point.

Decrease Decimals

Each time you click on the Decrease Decimal button, one decimal place fewer is displayed.

Text and Number Alignment

1. Aligning Cell Contents

pg. 9
You can change the alignment of text or numbers in a cell. Right aligned, left aligned, and centered
are the most common alignments. To align the contents of a cell, select the cell and then click on
the desired Formatting toolbar button.

By default, text entries are left aligned while numeric entries and calculated results are right
aligned. This may lead to data that does not appear neatly aligned within your spreadsheet. When
attempting to correct this, the most favourable option would be to re-align the label entries, not the
values.

Column Widths

If the number in the active cell is too wide for the column, the column width will adjust
automatically so that the number is displayed. You can also change the width of worksheet
columns manually. There are several ways, including the following:

• Place the mouse pointer on the boundary to the right of a column heading, and drag the
divider to the right (to expand the column width) or to the left ( to shrink the column width).
• Place the mouse pointer on the boundary to the right of a column heading and double-click
the mouse button. The column width is calculated to accommodate the longest entry in the
column.

When the total (number of characters of numeric data is greater than the column width, number
signs (# # # # #), commonly referred to as “hash” symbols, are displayed in the cell if you have
manually changed the column width. To display the number, you can change the formatting or
enlarge the width of the column.

Row Heights

Just as the default column width may not always be suitable, so too you will find that the default
row height may not always be to your liking. Although the row height will adjust automatically to
accommodate the font size of a particular cell, you may wish to change the height of worksheet
rows manually. There are several ways, including the following:

pg. 10
 • Place the mouse pointer on the boundary below the row heading, and drag the divider down
(to increase the row height) or up (to decrease the row height).
• Place the mouse pointer on the boundary below the row heading and double-click the
mouse button. The row height is calculated to accommodate the highest entry in the row.

CREATING CHARTS

Creating a Basic Chart

After you have created a worksheet, you can graphically represent the worksheet data by creating
a chart. Charts often make worksheet data clearer and easier to understand.

What is a chart? It is information presented in the form of a table, graph or diagram. When using
the term chart in Excel, we are specifically referring to the graph element of charting. For the
duration of this course, these two terms will be used interchangeably.

What exactly is meant by the term graph? A graph is a diagram showing the relation between
variable quantities, usually of two, each measured along one of a pair of axes at right angles. Pie,
Doughnut and Radar graphs are a few of the exceptions where the axes are either displayed in an
unusual format, or not at all.

In summary, a graph is a “pictorial” presentation o f a series of values. It is not possible to plot a

Excel enables you to create charts sheets, which are separate sheets of a workbook file that contain
only charts. Excel also you to create embedded charts, which are displayed on the same sheet as
the worksheet.

pg. 11
Chart sheets enable you to print a chart that is separate from data. You can use page setup options
to control how the chart will print. Creating a chart on a separate sheet is useful when you want to
show overhead projections of your charts as part of a presentation or a slide show.

Use an embedded chart when you want to print a worksheet and a chart on a single sheet of paper.
Embedded charts enable you to print a chart and its associated data side by side on the same printed
page. It is easier to compare actual worksheet data to the graphical representation of that data when
you use an embedded chart.

To create a chart on a chart sheet, you can either use the Chart Wizard or press the <F11> key.
The Chart Wizard feature leads you through a step- by-step process to create a chart.

When you use the <F11> key to create a chart, Excel applies the default chart format the new
chart. The default chart format is a column chart with a legend displayed and some formatting
applied.

To create a chart using the <F11> function key, the following process applies:

1. Create or Open the file containing the data that is to be plotted

pg. 12