1. No category

ac)
M
(
l
e
c
MS Ex
ICT Training
June 2002
Contents
Job Aid
Prepared by Sandrine Ceurstemont
Introduction to Excel for Mac
In this workshop, you will learn the basics of Microsoft Excel,
an electronic spreadsheet program. We will go through the
basic functions immediately followed by an activity where you
will create a spreadsheet for your students’ marks.
How do I open Excel?
Microsoft Excel should be found with the rest of your program
files. Go to the hard drive, select the ‘Applications’ folder and
locate the ‘Microsoft Office’ folder. Double click to open this
folder, then double click on the ‘Word’ icon.
Basics of a spreadsheet
What is a spreadsheet? The terms ‘spreadsheet’ and ‘worksheet’
are generally used interchangeably. To remain consistent with
Microsoft and other publishers, the term worksheet refers to the
row-and-column matrix sheet on which you work upon and the
term spreadsheet refers to this type of computer application. In
addition, the term workbook will refer to the book of pages
that is the standard Excel document. The workbook can contain
worksheets, chart sheets, or macro modules.
CSLP — MS Excel (Mac)
Basics of a
spreadsheet
1
Types of
data
4
Formatting
text
5
Constants
Formulas
Sums
6
Averages
7
Subtracting
7
Multiplying
Dividing
8
Formatting
numbers
8
Copying
formulas
9
Inserting
columns &
rows
10
Charts &
graphs
11
Activities
12
Trainer
Notes
19
Page 1
At the bottom of your screen in Excel, you will see tabs marked
‘Sheet1’, ‘Sheet2’ and ‘Sheet3’. By clicking on these tabs, you can
access the different worksheets in your document. The Excel
worksheet can contain up to 256 sheets, labeled Sheet 1 through
Sheet 256. The initial number of sheets in a workbook, which can
be changed by the user, is 16.
Column
Headings
Row Headings
Cells
Formula Bar
Active Cell
Columns
The columns in your worksheet make up the vertical space, as
illustrated below. Columns are denoted by letters at the top of the
screen. The Excel worksheet contains 256 columns that extend
across the worksheet, lettered A through Z, AA through AZ, BA
through BZ, and continuing to IA through IZ.
CSLP — MS Excel (Mac)
Page 2
Rows
The rows are the horizontal space on your worksheet. In Excel,
numbers on the left hand side of your screen are used to identify
rows, as demonstrated below. The Excel worksheet contains
16,384 rows that extend down the worksheet, numbered 1 through
16,384.
Cells
Cells are the intersection of a column and a row, as illustrated
below. A cell is denoted by a letter and a number corresponding
to the column and the row that intersect. The cell highlighted in
the screen shot below corresponds to the cell B8.
CSLP — MS Excel (Mac)
Page 3
Column width
You will often want to change the width of the columns in your
spreadsheet depending on the amount of text or numbers that you
want in a cell.
To increase the column width:
1. Follow the border to the right of your cell to the top of
worksheet where the letters are written.
2. Once your cursor is on the line between the two letters, your
cursor will turn into a double-sided arrow. Click down on
your mouse and drag the line to the right until the cell is the
appropriate width.
To decrease the column width:
1. Follow the border to the right of your cell to the top of the
worksheet where the letters are written.
2. Once your cursor is on the line between the two letters, your
cursor will turn into a double-sided arrow. Click down on your
mouse and drag the line to the left until the cell is the
appropriate width.
Types of data
There are three types of data that you can enter in an Excel
worksheet: 1) text, 2) numbers/constants, and 3) formulas.
Text
Text is usually used to label the numerical data in your spreadsheet
and to add titles or annotations. The picture above shows some
examples of labels and titles and an example of how you can
organize the data you are entering in your worksheet.
CSLP — MS Excel (Mac)
Page 4
Formatting text
You can choose different fonts, font sizes and styles for your text in
Excel, just like you would in Word. The easiest way to do this is to
use your formatting pallet, which may appear either at the top or
at the side of your screen. If the formatting pallet doesn’t appear
on your screen, you can go to the ‘View’ menu and select
‘Formatting’ pallet, or select ‘View’ then ‘Toolbars’ then
‘Formatting’ (depending on the version of Excel that you are
using).
If you want to format text that you have already typed, click on
the cell with the text that you want to format. You will see that the
text in this cell will also appear at the top of the screen in the
formula bar. Select the text in the formula bar with your mouse.
Now you can change the font, font size, style or other options on
the formatting pallet.
Merging Cells
You will notice that if you type text in a cell which is longer than
the size of the cell, the full text will appear in the formula bar at the
top of the screen, but it will appear truncated in the cell. By
merging the cell with some of its surrounding cells, the full text will
appear in the worksheet screen.
To merge cells, select the cells you want to merge with your mouse.
Go to the ‘Format’ menu and select ‘Cells…’. Now click on the tab
marked ‘Alignment’ and checkmark the box next to ‘Merge cells’.
CSLP — MS Excel (Mac)
Page 5
Now the complete string of text that you typed should appear on
your worksheet as well as in your formula bar. If it does not, then
you may need to merge more cells.
Constants
Constants are numbers, or fixed values, in contrast to formulas.
For example, if you enter a numerical mark, an age, or an amount
of money, you are entering constants. When you enter constants
in Excel, you may want to use these numbers when calculating
formulas in other cells such as totals or averages.
Formulas
In addition to entering numbers/constants in Excel, you can also
enter formulas. You can plug in constants that you entered in
other cells into the formula to calculate values. Here are some
simple mathematical operations and formulas that you should find
useful.
Sums
Before you enter a formula in Excel, you must always type an equal
sign first. This tells Excel that you are about to enter a formula. To
calculate a sum, you must first select the cell where you want to
enter the formula.
1. Select the cell where you want the result of your formula
displayed.
2. In the formula bar, type =sum(
3. Next you must select the range of values in your spreadsheet
that you want to add together. These values should normally
be arranged in a column or a row.
CSLP — MS Excel (Mac)
Page 6
4. Place your cursor on the first value of the column or row and
click on the mouse. Drag your cursor down the row or column
until all the values are selected. The range of values you selected will appear in the cell after the open bracket symbol you
typed, for example, (B3:B8).
5. Type ) to close the bracket.
6. Press ‘Return’.
A numerical value will appear in the cell. This is the sum of the
numbers in the range you selected.
Averages
You can calculate averages in a similar way as calculating sums.
1. Select the cell where you want the result of your formula displayed.
2. In the formula bar, type =average(
3. Next you must select the range of values in your spreadsheet
that you want to add together. These values should normally
be arranged in a column or a row. Place your cursor on the
first value of the column or row and click on the mouse button.
Drag your cursor down the row or column until all the values
are selected. The range of values you selected will appear in the
cell after the open bracket symbol you typed.
4. Type ) to close the bracket.
5. Press ‘Return’.
A numerical value will appear in the cell. This is the average of the
numbers in the range you selected.
Subtracting
If you want to subtract values, either constants or values in your
cells, you simply need to use the minus sign.
1. First you must select the cell where you want the result from
the subtraction to be displayed.
2. Next type the subtraction in the cell.
• If you want to subtract 2 numbers, type the subtraction in the
cell. For example, typing =5-3 and then pressing ‘Return’ will
display the number 2 in the cell.
• You can also subtract the numbers that you entered in other
cells. For example, typing =D3-B2 followed by ‘Return’ will
display the subtraction of the numbers in cells D3 and B2 in the
cell.
CSLP — MS Excel (Mac)
Page 7
Multiplying
You can multiply numbers in a similar way to subtracting numbers.
The multiplication sign in Excel is the asterisk (*).
1. Select the cell where you want the result from the
multiplication to be displayed.
2. Next type the multiplication in the cell.
• If you want to multiply 2 numbers, type the multiplication in
the cell. For example, typing =6*2 and then pressing ‘Return’
will display the number 12 in the cell.
• You can also multiply constants you have entered in other
cells. For example, typing =A5*C8 followed by ‘Return’ will
display the result of the multiplication of the numbers in cells
A5 and C8.
Dividing
You can do divisions in Excel in the same way as subtractions and
multiplications. The operator for divisions in Excel is the forward
slash sign (/).
1. Select the cell where you want the result from the division to be
displayed.
2. Next type the division in the cell.
• If you want to divide 2 numbers, type the division in the cell
where you want the result to be displayed. For example,
typing =12/3 and then pressing ‘Return’ will display the number 4 in the cell.
• You can also divide numbers you have entered in other cells.
For example, typing =C2/B3 followed by ‘Return’ will display
the result of dividing the numbers in cells C2 and B3.
Formatting numbers
You can format numbers in Excel according to the number of
decimal places you want to display, if the number represents
dollars, the date, time etc. To format the number in a cell, you
must first select the cell with your mouse. You can also select a
range of cells if you want to format more than one cell.
1. Go to the ‘Format’ menu and select ‘Cells…’
2. Select the ‘Number’ tab.
3. You will see many options in the ‘Category’ window. By
clicking on the different options, you will see the different
options you have for formatting.
CSLP — MS Excel (Mac)
Page 8
Decimal places
To change the number of decimal places, click on ‘Number’ in the
‘Category’ window. You will see a window with the label ‘Decimal
places’. You can change the number of decimal places by typing the
number of decimal places you want in this window.
Currency
To change numbers to currency, click on ‘Currency’ in the
‘Category’ window. Here you can choose the number of decimal
places and the type of currency that you want.
Date
Click on the ‘Date’ option to select date format. You can choose
from different ways of displaying the date.
Time
Click on ‘Time’ to change to time format. You can choose from
different formats for displaying time.
Fractions
To display numbers as fractions, click on the ‘Fraction’ option.
Here you can choose the type of fraction you want.
Copying formulas
When you want to use the same formula in many places in your
spreadsheet, you can copy and paste the formula instead of
retyping it over and over again.
1. Select the cell where you have already typed the formula.
2. Go to the ‘Edit’ menu and select ‘Copy’.
3. Select the cell or cells where you want the same formula to
appear with your mouse.
4. Go to the ‘Edit’ menu and select ‘Paste’. The formula should
now appear in the cells you selected. You will notice that Excel
changes the range of numbers for which it calculates the
formula. For example, if you copy and paste a formula that
calculated the sum of a column, the formula will now calculate
the sum for the numbers in the column where you pasted the
formula.
CSLP — MS Excel (Mac)
Page 9
Fill down
An even easier way to copy formulas is by using the ‘Fill’ options.
To copy a formula down the cells in a column:
1. Select all the cells in the column where you want the formula
to be copied including the cell with the formula.
2. Go to the ‘Edit’ menu and select ‘Fill’ then ‘Down’. The formula
should now be copied to all the selected cells, and the values in
the formula will be changed accordingly for each row.
Fill right
You can use also use the ‘Fill’ option to copy a formula across a
row.
1. Select all the cells in the row where you want the formula to be
copied including the cell with the formula.
2. Go to the ‘Edit’ menu and select ‘Fill’ then ‘Right’. The formula
should now be copied to all the selected cells, and the values in
the formula will be changed accordingly for each column.
Inserting columns and rows
While you are making a spreadsheet, you may sometimes want to
modify it by adding extra columns or rows. You can easily insert
columns and rows anywhere in your spreadsheet by using the
Insert column and Insert row functions.
Inserting a column
1. Select a cell in the column to the right of where you want to
insert the new column.
2. Go to the ‘Insert’ menu and select ‘Columns’. A new column
should be inserted to the left of the column you had
selected.
Inserting a row
1. Select a cell in the row below the place where you want to
insert the new row.
2. Go to the ‘Insert’ menu and select ‘Rows’. A new row should
now be inserted above the row you had selected.
CSLP — MS Excel (Mac)
Page 10
Charts and graphs
Excel allows you to create charts and graphs for the data you
entered in your spreadsheet. Some of the more common types of
graphs and charts you can produce are bar graphs, line graphs and
pie charts. First, you have to select the data in your spreadsheet
that you want to graph with your mouse. For the purpose of this
workshop, lines and bar graphs will be discussed.
Line and Bar Graphs
1. After you have selected the data you want to graph, go to the
‘Insert’ menu and select ‘Chart…’ Under ‘Chart type:’ click on
‘Line’. In the window under ‘Chart subtype:’ click on the type
of chart or graph that you want to create.
2. Now click on the ‘Next >’ box.
3. If you have selected the right data range you can simply click on
‘Next >’. If you want to make changes to the data you want
to graph, you can type in a different data range in the ‘Data
range:’ box. Click on ‘Next >’.
4. You will now get to select different chart options. Under the
‘Titles’ tab, you can type in a title for your chart as well as labels
for your axes. Under the ‘Gridlines’ tab, you can choose if you
want gridlines to appear on your chart. Under the ‘Legend’ tab,
you can decide if you want a legend to accompany your chart
and where you want it to appear on your graph. When you
are done selecting the options you want, click on ‘Next >’.
5. Now you can decide if you want your chart to be displayed in
the same worksheet you are working on or if you want it to
appear in a separate worksheet in your workbook. You can
scroll through the different sheets in your document by clicking
on the tabs at the bottom of your screen labeled ‘Sheet1’,
‘Sheet2’, etc... Select the option you prefer (‘As new sheet:’ or
‘As object in:’) and then click on ‘Finish’. Your chart should
appear either in your current worksheet or on a new sheet,
depending on the option you selected.
6. To edit the options you chose for your chart, you can left click
on your chart and select ‘Chart Options’.
CSLP — MS Excel (Mac)
Page 11
c)
el (Ma
c
x
E
S
M
Activities
Activity 1: Entering text in Excel
You will now start the first step in creating your class marks
spreadsheet in Excel.
1. Open Excel according to the instructions at the beginning of
this job aid. If it is already open, then go the ‘File’/‘New’ and
click ‘OK’. You will want to type a title near the top of the
worksheet, for example Marks for Grade 6. Format the title
by making the font size bigger and by bolding the text. You
may also want to merge some of the cells as explained on
page 5.
2. Next you will want to add labels to identify the different
assignments/projects for which you will enter marks. You
should have four different assignments/projects. You will want
to put these titles in the same row. The picture below shows
what your worksheet may look like. Do not forget that you
can modify the column width if your text does not fit in the
cells.
3. Next you will enter the names of your students in the column
to the left of the assignment labels. Type in the name of six
students. Here is an example of what your spreadsheet might
look like:
4. You should now save your spreadsheet.
Saving your work
1. Go to the ‘File’ menu and select ‘Save’ or simply click on the
floppy disk icon on the standard toolbar at the top of your
screen.
2. Choose the location where you want to save your document in
the box next to ‘Save in:’ (save your file on your floppy disk if
you have one, otherwise you may want to save your document
on the desktop)
3. Type in a name for your document in the box next to ‘File
name’.
4. Click on the ‘Save’ button.
CSLP — MS Excel (Mac)
Page 13
Activity 2: Entering constants and
calculating sums
To practice using the sum function in Excel, you will first add
another column to your spreadsheet.
1. Add another label to your spreadsheet by typing Books
Borrowed at the end of the row with your titles. Enter the
amounts as illustrated below. Your spreadsheet should now
look something like this:
2. To calculate the total number of books borrowed by all the
students, select the next cell in the column. Type the formula
for calculating sums, selecting the numbers in the Books
Borrowed column as the range of values you want for the sum.
Follow the instructions described for the sum function described
on page 6 & 7. You will put the total in the cell below your
last entry in the Books Borrowed column.
3. Remember to save your work.
CSLP — MS Excel (Mac)
Page 14
Activity 3: Calculating averages
Now you want to calculate the class average for each assignment/
project. First you have to enter the marks for the assignments.
1. In the first column, enter marks out of 10 for the first
assignment.
2. In the second column, enter marks out of 20 for the second
assignment.
3. In the third column, enter marks out of 30 for the third
assignment.
4. In the fourth column, enter marks out of 30 for the fourth
assignment.
5. Now you want to calculate the average class mark for the first
assignment. In the next free cell in the first column that contains
numbers, type the average formula as described in the procedure for averages on page 7. Select the numbers in the first
column as the range. When you are done, your spreadsheet
should look something like this:
5. Remember to save your work.
CSLP — MS Excel (Mac)
Page 15
Activity 4: Copying formulas &
formatting numbers
You have already entered the formula for calculating an average
into your spreadsheet and you have calculated the class average for
the first assignment. Now you want to calculate the average for the
other assignments, and the easiest way to do this is to copy and
paste the formulas or to use the ‘Fill’ option.
1. First you will practice copying and pasting the formula. Select
the cell where you entered the average formula for the first
assignment. Use the method for copying and pasting a formula
described on page 9 and copy this formula for the column with
the marks for the second assignment.
2. Next you will practice using the ‘Fill right’ function. Select the
cell where you calculated the average for the second assignment
along with the two cells to the right of it where you want the
average for the third and fourth assignment to be displayed.
Use the procedure for the ‘Fill right’ function described on page
10 to copy the average formula.
3. You may notice that some of the averages calculated involve
decimals. You will want to format these cells so that only two
decimal places are visible. Use the procedure for formatting
numbers described on page 10 to format any cells with decimals
to two decimal places. Your spreadsheet should now look
something like this:
4. Remember to save your work.
CSLP — MS Excel (Mac)
Page 16
Activity 5: Inserting columns and rows
You will now practice inserting columns and rows. You forgot one of
the students in your class and want to add a row for that
student.
1. Add a row for the student you forgot after the first student you
listed. Use the procedure for inserting rows described on page 10.
2. Now, fill in the row for the new student by typing the student’s
name, marks for the four assignments and books borrowed. You
will notice that the values for the formulas have changed to
account for the new data you just entered. Your spreadsheet
should now look something like this:
3. You realize that you have forgotten one of the assignments you
did with the class. Insert a column after the fourth assignment in
your spreadsheet by using the method for inserting a column
described on page 10.
4. Type the name of the assignment and enter marks for the
assignments. Using one of the methods for copying a formula,
copy the average formula at the end of this column to calculate
the class average for this assignment. Your spreadsheet should
now look something like this:
CSLP — MS Excel (Mac)
Page 17
Activity 6: Creating a line graph
To practice creating graphs, you will create a graph or chart to
visually illustrate the spread of marks in your class on their first
assignment.
1. The first step is to select the column in your spreadsheet where
you typed in the class marks for the first assignment.
2. Go to the ‘Insert’ menu and select ‘Chart…’
3. Follow the instructions for creating a line graph described on
page 11. Type in a name for the chart and label the axes.
Display the chart as an object in your worksheet. Your line
graph should look something like this:
4. Remember to save your work.
CONGRATULATIONS! You have now completed the Excel
workshop. You should now be able to use the basic functions of
Excel. Keep this handout and the spreadsheet you made as they
could be helpful for future reference.
CSLP — MS Excel (Mac)
Page 18
ac)
M
(
l
e
c
MS Ex
Trainer Notes
The key to any workshop’s success is to prepare in advance. Be
sure to introduce only those Excel elements with which you are
comfortable to you class. You can always add more features to
your to do list as the comfort level of the users increases.
What you must do:
•
•
•
•
Familiarize yourself with MS Excel and practice using its
features.
Determine why you want to use Excel, what the final
product will be (a class list, grades, record of money paid for
fieldtrips, math quiz progress charts).
Teach your students basic record keeping with Excel so they
can keep track of their own progress.
Be willing to make mistakes and try new features!
Things you will need for your
workshop:
•
•
•
Handouts for all participants
Projector, screen and a computer for the presenter
Computers for all the participants with MS Excel installed
Resources
An Excel workshop (Rhonda Roark and Agnes Marcum)
http://170.183.222.114/lms/ExclWrk.htm
Microsoft Excel page—see the section at the bottom about tools
to help you work smarter.
http://www.microsoft.com/office/excel/default.asp
Another tutorial that looks pretty good from the University of
South Dakota
http://www.usd.edu/trio/tut/excel/
CSLP — MS Excel (Mac)
Page 19