Technology Training

Microsoft Excel
The Basics

• Around the Spreadsheet Window

• Entering and Working with Data
• Visualizing Data: Creating Charts
• Using Excel as a Data Base

Valerie T. Low
Region II Technology Coordinator

1
Microsoft Excel Tutorial Valerie T. Low, RII RTC

Excel Tutorial

I. Basics-Around the spreadsheet window

Columns/labels
o Column headings like fields in data base
Rows/labels
o rows are records
Scroll bars

Formula Bar
Name Box Column Headings Up Scroll Bar

Row Numbers

Worksheet Names Down Scroll Bar

Scroll to Worksheets Left & Right Scroll Bars

Tool bar:

Zoom Control Inserting and deleting

Selecting rows or columns Expanding Columns

2
Microsoft Excel Tutorial Valerie T. Low, RII RTC

Saving Documents
Select File>Save As
The following window is displayed:
The default (typical) folder to save
documents in is the My Documents.

To change this folder, click on the

down-pointing (to the right).
Select a different location, e.g. 3 ½
floppy, if you want to save on a disk.

Note: If you plan to bring the

information from an Excel spreadsheet into another program, e.g. an address book,
Select File>Save As (a second time)
Type a new file name.
Click the arrow next to the Save as type box.
Select Text (Tab delimited)
Click Save.
This file will be able to be imported into different programs.

3
Microsoft Excel Tutorial Valerie T. Low, RII RTC

II. Entering data

Use tab, return or enter, arrow keys to move about spreadsheet
To change an entry, double click in cell, tap delete or backspace; re-type the data.
Formatting cells
Format menu
Tool bar options
Auto format
Format painter
Edit menu Cut, copy, paste
Copying data from other sources
Multiple cut and paste (View> Toolbars> Clipboard
Auto calculate -Right click the area on the status bar
Choose one of the available functions e.g. sum)
result is displayed in Auto calculate area
Expanding rows & using text wrap

Fill
Select Edit>Fill (and Down or Left to add the same number in each cell).

Select Edit>Fill>Series
To add a list which automatically gets larger.
Choose the type-e.g. Date or linear and the ‘step’ value, e.g. 1 to get incrementally larger

4
Microsoft Excel Tutorial Valerie T. Low, RII RTC

Or…
Auto fill (drag bottom right corner of Cell to the right or
down

Format Menu
Use the format menu to format Cells, Rows, Columns

Use Format>Cells
To change the data type, e.g. to
currency or date.
Also change the font, or add
Borders around cells.

5
Microsoft Excel Tutorial Valerie T. Low, RII RTC

Auto format
Select Format>Auto format
To choose a pre-designed format
for your data.

View Menu
Allows you to display or hide different toolbars
See where page breaks are
Add Headers and Footers
Zoom (instead of using the toolbar icon)

Insert Menu
Highlight a row or column (Click on the row number or
column letter)
Select Insert>Row or column to place a row or column
before the selection.
As in other Office components, insert other items from
this menu option.

6
Microsoft Excel Tutorial Valerie T. Low, RII RTC

Data Menu
Sorting Data:
Select the column to sort
Select Data>Sort
Choose Ascending or Descending

This menu has other options for data

manipulation.

7
Microsoft Excel Tutorial Valerie T. Low, RII RTC

III. Entering formulas

Click in the cell in which the formula is
to go.
Type = in the cell B8
Click the SUM button (Function)
Excel automatically enters the correct
format and cell range.
Click check mark to enter the formula.
Inserting rows or columns keeps
formula.
Drag the right bottom corner of the cell (with the formula) to the right through cells C8, D8,
and E8 to fill those cells with the correct SUM formula.

Or-
Use the Edit>Paste Function
for a wider range of formulas.

Enter the data range in the following window:

8
Microsoft Excel Tutorial Valerie T. Low, RII RTC

IV. More on Worksheets/Workbooks

A worksheet is a working area that can have up to 65,536 rows and 256 columns -
A worksheet can print on several pages
A workbook is a group of worksheets saved in one file
Re-name a worksheet:
o Double click on the worksheet tab. Type a name
Copy a worksheet:
o Select Edit>Move or Copy sheet
o Select the sheet to copy
o Click “create a copy”
o Double click the new worksheet tab to rename the sheet

V. Help and Assistant

Click on the assistant, or on the ? .
Type a question or key word to search.
Topics are displayed from which you can choose.

9
Microsoft Excel Tutorial Valerie T. Low, RII RTC

VI. Creating Charts/Graphs

Using the wizard-Step by step
Highlight the cells to be included in
the chart.
Choose the chart type which is best
for data

Check cell range:

Enter chart title and labels

for the axes:

10
Microsoft Excel Tutorial Valerie T. Low, RII RTC

To show chart on same

page as data:
Select As object in

Select As new sheet, to

have chart on different
sheet.

Your chart should look like the following:

M & M's Graph

6
5
4
Bag 1
3
2 Bag 2

1 Bag 3
0 Bag 4
Red Orange Yellow Green Brown
Color

Try this:
View changes in graph when data is changed

11
 Microsoft Excel Tutorial Valerie T. Low, RII RTC

Formatting the Chart

Double click on any part of the chart, or use the Chart Tool Bar to format the chart.
Changing the Fonts

Formatting Walls and Borders

Click None to remove the shaded Border and

Area around and behind the chart.

12
Digitally signed by Valerie T.
Low

Valerie T. Low
DN: cn=Valerie T. Low,
o=Region II Technology
Coordinator, ou=PGCPS, c=US
Date: 2002.04.14 16:06:24
Signature Not -08'00'
Verified
Microsoft Excel Tutorial Valerie T. Low, RII RTC

Chart Tool Bar

Easy access to all formatting features.

Select chart objects Legend Change chart type

Add data table- By row- By column -Text orientation

Click on the Change Chart Type option to change the chart to one of the following:

More on formatting the chart

Format
o Chart Component colors
o Border and area colors
Modify the chart with:
o the chart toolbar
Click anywhere in chart
Or
Choose Chart >Chart options

Click Scale

Select minimum / maximum for

y axis and intervals

13
Microsoft Excel Tutorial Valerie T. Low, RII RTC

Click Alignment
To set text at a diagonal or vertical orientation.

Experiment with the other chart enhancement options.

14
Microsoft Excel Tutorial Valerie T. Low, RII RTC

VII. Printing
Page Setup
Margins, page layout, headers, footers, page numbers, justification, gridlines
Print Preview

Choose File>Page Setup

15
Microsoft Excel Tutorial Valerie T. Low, RII RTC

VIII. Using Excel as a data base

Type Column labels but use no spaces, ex FirstName
fill in information for each person or thing- each row is a record; each record must be unique
Using a data form to Add Records instead of typing information in cells

1. Select any cell in the database

Choose Data Form new
A form is displayed, with your column labels as fields) at left with data entry boxes next to
each.

Press tab to more from one field box to next (press shift Tab to go to the previous field
Press Enter after typing info in last field to add record to the data base
when all records have been added, click the close box.

16
Microsoft Excel Tutorial Valerie T. Low, RII RTC

Sorting the data base

1. Using multiple criteria:
Choose Data to Sort by:
Select field name to sort by-click
next to the Sort by box.

2. Select Ascending or Descending

3. Select additional fields to sort by.
or…
4. Use the Sort icons on the tool bar.
Click any cell in the target column.
Click either Ascending or Descending icon.

Creating Subtotal Reports

Select Data>Subtotals
The following window is displayed.
In the box At each change in, select the field for
which you want subtotals, e.g. each color.

In the box, Use function, select the type of subtotal,

e.g. Sum.

In the box, Add subtotal to, select the column for

which the subtotal will be calculated. More than
one column/field name can be selected.

Subtotals are displayed for Bag 1, as follows:

Experiment!

17
Microsoft Excel Tutorial Valerie T. Low, RII RTC

AutoFilter

Select Data>Filter>Autofilter
Next to each column heading/field name is a
Click the arrow next to LastName to set the filter. The following window is displayed:

Type s* to display names beginning with s only.

Experiment with the other field names.

To turn off AutoFilter,

Select Data>AutoFilter (it should no longer be selected).

Excel offers endless possibilities:

Test data analysis

Contact information
Addresses
Expenses
Task Analysis
And more….

Good Luck! Nothing’s lost with experimenting…Just save your work 1st!

18