Microsoft Excel XP

SPREADSHEETS

A spreadsheet allows you to enter and store data in a ‘grid’ format on a computer system. It will provide you with a means of performing numerical and statistical calculations.

  • A worksheet is a grid of rows and columns.
  • Each cell is an intersection of a row and a column.
  • Selected cells appear highlighted on your screen.

ENTERING & EDITING DATA

What structure does it have?

  • It is divided into columns and rows, which form boxes called cells.

What can it contain?

  • The Spreadsheet can work as a word processor. The main difference is that the Spreadsheet can carry out calculations. Everything you type has to go in a cell: LABELS (Headings & Messages), VALUES (Numerical entries) and FORMULAS.

Why use a spreadsheet?

  • To carry out calculations, once you have entered the appropriate formulae.
  • To work out & display the new answers automatically if any data entry is changed, including numeric data and formulae. You won’t have to recalculate them yourself!
  • They can be used to present a wide variety of information such as budgets, cash records, weather information, etc.

TERMINOLOGY

Here are a few terms you should become familiar with:

  • COLUMN: Aline of cells down the spreadsheet, which has a reference letter.

(e.g.: A, B, ..., G, F...IV).

  • ROW: Line of cells across the spreadsheet, which has a reference number

(e.g.: 1, 2, ....,65536).

  • CELL: A single box in the spreadsheet. (e.g.: A4).
  • CELLADDRESS: Is the identification of the cell, given by the column and the row it is in. (e.g.: C2 means column C, row 2).
  • RANGE: All the cells that lie within a specified area of the sheet. (e.g.: A4:D5, means the cells: A4, A5, B4, B5, C4, C5, D4, D5)
  • FORMULAE: Used to calculate the value of a cell from the contents of other cells. They allow you to multiply, divide, add & subtract any cells or numbers. Formulae always start with an = sign. The formula contains the address of each cell used in the calculation and any fixed numbers you need. E.g.:

F4 = B4 + C4 + D4 + E4

Spreadsheets use these arithmetic signs:

+addition

--subtraction

*multiplication

/division

  • FUNCTIONS: Instructions to the system to carry out a particular process. Each function has a word the system recognizes such as SUM, AVE, MIN, MAX. For example: SUM (B1:B5) will add all the numeric values stored in cells B1, B2, B3, B4 and B5.

LABELS, VALUES OR FORMULAS

TO INPUT

  • Select the cell you want to write in.
  • Type the heading, message, numerical entry or formula.
  • Press <ENTER>.

TO MODIFY

  • Double-click the cell you want to modify; the insertion point appears flashing in the cell)
  • Make your changes.
  • Press <ENTER>.

TO DELETE

  • Click the cell you want to delete.
  • Press the Delete or Backspace keys on the keyboard.

BUILDING FORMULAS TO CALCULATE VALUES

A formula in Microsoft Excel always starts with an equal sign (=).

On paper you might add two numbers like this: / On a worksheet you add two numbers like this:
1
+ 2
------
3 / A
1 1
2 2
3 =SUM (A1: A2)

HOW TO USE THE AutoSum BUTTON

  • Click the cell where you want the answer.
  • Click the AutoSum button.

(Microsoft Excel guesses what you want to add)

  • Press <ENTER> to accept the proposed formula. If not highlight the cells you want to add and then press <ENTER>.

HOW TO COPY A FORMULA:

  • Click in the cell with the formula you wish to copy.
  • Take the mouse to the bottom right corner until it becomes a black cross.
  • Press the left button of the mouse and drag it through the cell you wish to copy.

HOW TO WRAP TEXT

  • Click the cell you wish to wrap.
  • Select Format from the Main Menu
  • Select Cells
  • Select Alignment
  • Select Wrap Text.

SELECTING CELLS

Selections are always rectangular blocks of cells.

To selectDo this

Any amount of textDrag over the text.

A wordDouble-click the word.

A graphicClick the graphic.

To select nonadjacent cells or rangesHold down CTRL

FORMATING DATA:

TO ALIGN THE TITLE ACROSS COLUMNS

  • Select the title & range of columns where you want to make the alignment.
  • Click the Merge and Centre button.

HOW TO FORMAT A TABLE USING THE AutoFormat COMMAND

  • Select any cell within the table.
  • Select Format from the Main Menu.
  • Select AutoFormat.
  • Complete the dialogue box. Select an option in the Table Format box.
  • Choose the O.K. button.

HOW TO FORMAT CELLS

  • Select the range of cells.
  • Select Format from the Menu bar.
  • Select Cells.
  • Select the Number Tab.

  • Click on the corresponding format (general, number, currency, accounting, date, time, percentage, fraction, scientific, etc.)

If this symbol appears (###) its because the columns are too narrow to display the full number, therefore you need to make the column wider to display the complete number.

.

CREATING A CHART

Charts are visual representations of worksheet data. (bar chart, pie chart, line bar etc..)

The ChartWizard is an interactive charting assistant.

  • Select the range of cells that contains the labels for the chart followed by the range of values.

(Hold down CTRL while you select non-consecutive values.)

  • Click the ChartWizard button.
  • Click and drag the pointer underneath the table to the size of chart you want. (The ChartWizard dialogue box appears.)
  • Click the Next button.
  • Select the Chart type and then click the Next button.
  • Select the Chart format and then click the Next button.
  • Click the Next button to continue.
  • Complete the Chart Title box and then clickthe Finish button.

PRINTING

TO HIDE THE GRIDLINES WHEN PRINTING

  • Select File from the Menu bar.
  • Select Page Setup.
  • Select the Sheet tab.
  • Under Print, clear the Gridlines check box.

HOW TO PREVIEW WHAT WE PRINT

  • Select File from the Menu bar.
  • Select Print Preview.
  • Choose the Print button.

SORTING

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

Sort rows in Ascending or Descending order based on the contents of one column.

If you previously sorted a list on the same worksheet, Microsoft Excel uses the same sorting options unless you change them.

  • Click a cell in the column by which you would like to sort.
  • Click Sort Ascending or Click Sort Descending.

FILL IN A SERIES OF NUMBERS

  • Select the first cell in the range you want to fill, and enter the starting value for the series.
  • Select the next cell in the range and enter the next item in the series.

The difference between the two starting items determines the amount by which the series is incremented.

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

TO CREATE A NEW LIST

  • Select Tools from the Main Menu
  • Select Options
  • Select Custom Lists
  • Click in List Entries and type the each item of the list and press enter.
  • Choose the Add button.


USE OF FUNCTIONS

Spreadsheet used to calculate half-yearly sales or income for a clothes department.

Select Insert from the Main Menu
Select Function
  • Select O.K.

IF FUNCTION:

Example:

A / B / C / D / E / F
1 / Mark 1 / Mark 2 / Mark3 / Total Mark / PASS or FAIL
2 / History / 9 / 5 / 6 / =AVERAGE(B2:D2) / =IF (E2 > 7, “Pass”, “Fail”)
3 / Geography / 2 / 6 / 6
IT / 8 / 8 / 9


Other functions:

SUM=SUM(B2:D2) Adds the range of cells from B2 to D2.

AVERAGE=AVERAGE(B2:D2) Calculates the average of the range of cells.

IF=IF(G2>500,”GOOD SALE”,”BAD SALE”)
Specifies a logical test. If the value or expression is true then the first message is printed, if not the second message is printed.

MAX=MAX(B2:D2) Displays the highest value in a range of cells.

MIN=MIN(B2:D2) Displays the lowest value in a range of cells.

COUNT=COUNT(B2:D2) Counts how many numbers are in a range of cells.

MODE=MODE(B2:D2) Returns the most common value in the range of cells.

MEDIAN=MEDIAN(B2:D2) Returns the median of the values in the range of cells.

HOW TO SHOW THE FORMULAS

  • Select Tools from the Menu bar.
  • Select Options.
  • Click View
  • Select Formulas

WHAT IS A WORKBOOK?

In Microsoft Excel the file in which you work and store your data is called workbook. Each workbook may contain many sheets. The sheet names appear on tabs at the bottom of the workbook window. By clicking on the tabs you can move from sheet to sheet within a workbook.

MOVING AROUND IN A WORKBOOK:


TO RENAME A SPREADSHEET:

  • Double click the word Sheet1 at the bottom of the page and then type the new name.

TO INSERT A NEW SHEET:

  • Click the right button of the mouse on a Sheet tab.
  • Select Insert
  • Select Worksheet
  • Select OK.

TO SELECT MORE THAN ONE SHEET AND FORMAT IT:

  • Click on Sheet 1.
  • While you press Control, select the other sheets. (Sheet 1, 2 and 3)

Write the labels in the table (these will be repeated automatically)

DISPLAY A SUBSET OF ROWS IN A LIST BY USING FILTERS

  • Clicka cell in the list you want to filter.
  • Select Data from the Main Menu
  • Click Filter
  • Click AutoFilter.
  • To display only the rows that contain a specific value, click the arrow in the column that contains the data you want to display.
  • Click the value.

HOW TO REMOVE FILTERS

  • Select again Data from the Main Menu
  • Click Filter
  • Click AutoFilter.

RELATIVE & ABSOLUTE REFERENCE:

Formulas can be constructed with relative reference to other cells, so they refer to different cells when replicated in other locations, or absolutereferences that don’t change when copied elsewhere. To have an absolute reference in a formula you must press F4 when building it.


Page (1)