Introduction

Microsoft excel is an electronic spreadsheet. You can use it to organize your data
into rows and columns. You can also use it to perform mathematical calculations
quickly. This course teaches microsoft excel basics as a prelude to the use of statistical
analysis system (sas) software in carrying out more complex statistical analysis.
Although knowledge of how to navigate in a windows environment is helpful, this
manual is created for the computer novice.

At the end of the course, participants are expected to know how to use microsoft excel
to:

• Enter text and numbers in a spreadsheet

• Enter excel formulas
• Format data
• Create excel functions
• Fill cells automatically
• Print results
• Create charts, and
• Enter advanced excel formulas

Accordingly, the course is divided into the following five (5) sections.

Section 1: Entering text and numbers

Section 2: Entering excel formulas and formatting data
section 3: Creating excel functions, filling cells, and printing
section 4: Creating charts
Section 5: More on entering excel formulas

Section 1: Entering text and numbers

1.1 The microsoft excel window

This section will introduce you to the excel window. To begin this section, start
microsoft excel 2007 as follows:

1. Click on microsoft start button

2. Point the mouse on all programs
3. Click on microsoft office
4. Click on microsoft excel 2007

1
The microsoft excel window appears and your screen looks similar to the one shown
here.

1.2 The microsoft office button

In the upper-left corner of the excel 2007 window is the microsoft office button. When
you click the button, a menu appears. You can use the menu to create a new file, open
an existing file, save a file, print and perform many other tasks.
1.3 The quick access toolbar

Next to the microsoft office button is the quick access toolbar. The quick access
toolbar gives you quick access to commands you frequently use.
1.4 The title bar

Next to the quick access toolbar is the title bar. On the title bar, microsoft excel
displays the name of the workbook you are currently using. At the top of the excel
window, you should see "Book 1 - microsoft excel" Or a similar name.
1.5 The ribbon
In microsoft excel 2007, you use the ribbon to issue commands. The ribbon is located
near the top of the excel window, below the quick access toolbar.

2
1.6 Worksheets

Microsoft excel consists of worksheets. Each worksheet contains columns and rows.
The columns are lettered a to z and then continuing with aa, ab, ac and so on; the rows
are numbered 1 to 1,048,576.
The combination of a column coordinate and a row coordinate make up a cell address.
For example, the cell located in the upper-left corner of the worksheet is cell a1,
meaning column a, row 1. Cell e10 is located under column e on row 10. You enter
your data into the cells on the worksheet.
1.7 The formula bar

If the formula bar is turned on, the cell address of the cell you are in displays in the
name box which is located on the left side of the formula bar. Cell entries display
on the right side of the formula bar.

3
1.8 The status bar

The status bar appears at the very bottom of the excel window and provides such
information as the sum, average, minimum, and maximum value of selected numbers.
1.9 Move around a worksheet
By using the arrow keys, you can move around your worksheet. You can use the
down arrow key to move downward one cell at a time. You can use the up arrow
key to move upward one cell at a time. You can use the tab key to move across the
page to the right, one cell at a time. You can hold down the shift key and then press
the tab key to move to the left, one cell at a time. You can use the right and left arrow
keys to move right or left one cell at a time. The page up and page down keys move up
and down one page at a time. If you hold down the ctrl key and then press the home
key, you move to the beginning of the worksheet.
1.10 Exercise 1
Move around the worksheet using the down and up arrow keys, the right and left
arrow keys, the tab key, the page up and page down keys and the (ctrl) home key.
1.11 Go to cells quickly
The following are shortcuts for moving quickly from one cell in a worksheet to a cell
in a different part of the worksheet.
1.12 Exercise 2
go to -- f5
1. Press f5. The go to dialog box opens.
2. Type j3 in the reference field.
3. Press enter. Excel moves to cell j3.

4
Go to -- ctrl+g
1. Hold down the ctrl key while you press "G" (ctrl+g). The go to dialog box
opens.
2. Type c4 in the reference field.
3. Press enter. Excel moves to cell c4.
Go to -- the name box
You can also use the name box to go to a specific cell. Just type the cell you want to
go to in the name box and then press enter.

1. Type b10 in the name box.

2. Press enter. Excel moves to cell b10.

1.13 Select cells

If you wish to perform a function on a group of cells, you must first select those cells
by highlighting them. The exercises that follow teach you how to select.

5
1.14 Exercise 3
select cells – f8
To select cells a1 to e7:
1. Go to cell a1.
2. Press the f8 key. This anchors the cursor.
3. Note that "Extend selection" Appears on the status bar in the lower-left corner
of the window. You are in the extend mode.
4. Click in cell e7. Excel highlights cells a1 to e7.
5. Press esc and click anywhere on the worksheet to clear the highlighting.
Alternative method: Select cells by dragging

1. Go to cell a1.
2. Press the left mouse button.
3. While holding down the left mouse button, use the mouse to move from cell
a1 to c5.
4. Release the left mouse button.
5. Hold down the ctrl key until step 9.
6. Using the mouse, place the cursor in cell d7.
7. Press the left mouse button.
8. While holding down the left mouse button, move to cell f10. Release the left
mouse button.
9. Release the ctrl key. Cells a1 to c5 and cells d7 to f10 are selected. 10.press
esc and click anywhere on the worksheet to remove the highlighting.
1.15 Enter data
In this section, you will learn how to enter data into your worksheet. First, place the
cursor in the cell in which you want to start entering data. Type some data, and then
press enter. If you need to delete, press the backspace key to delete one character at a
time.

6
1.16 Exercise 4
enter data

1. Place the cursor in cell a1.

2. Type john jordan. Do not press enter at this time.
1.17 Edit a cell – f2
After you enter data into a cell, you can edit the data by pressing f2 while you are in
the cell you wish to edit.
1.18 Exercise 5
Change "John" To "Jones."
1. Move to cell a1.
2. Press f2.
3. Use the arrow and backspace keys to change john to jones
4. Press enter.

Alternate method: Editing a cell by using the formula bar

You can also edit the cell by using the formula bar. You change "Jones" To "Joker" In
the following exercise.

1. Move the cursor to cell a1.

2. Click in the formula or entries area of the formula bar, and change jones to
joker.
3. Press enter.

7
Alternate method: Edit a cell by double-clicking in the cell
You can change "Joker" To "Johnson" As follows:

1. Move to cell a1.

2. Double-click in cell a1.
3. Use the arrow and backspace keys to change joker to johnson.
4. Press enter.
Change a cell entry
Typing in a cell replaces the old cell entry with the new information you type.
1. Move the cursor to cell a1.
2. Type cathy.
3. Press enter. The name "Cathy" Replaces "Johnson jordan"

1.19 Wrap text

When you type text that is too long to fit in the cell, the text overlaps the next cell. If
you do not want it to overlap the next cell, you can wrap the text.
1.20 Exercise 6

8
 1. Move to cell a2.
2. Type text too long to fit.
3. Press enter.

4. Return to cell a2.

5. Choose the home tab.

6. Click the wrap text button . Excel wraps the text in the cell.
1.21 Delete a cell entry
To delete an entry in a cell or a group of cells, you place the cursor in the cell or
select the group of cells and press delete.
1.22 Exercise 7
delete a cell entry
1. Select cells a1 to a2.
2. Press the delete key.
1.23 Save a file
This is the end of section 1. To save your file:
1. Click the office button. A menu appears.
2. Click save. The save as dialog box appears.
3. Go to the directory in which you want to save your file.
4. Type section1 in the file name field.
5. Click save. Excel saves your file.
1.24 Close excel
Close microsoft excel.
1. Click the office button. A menu appears.
2. Click close. Excel closes.

9
Section 2: Entering excel formulas and formatting data
Section 1 familiarized you with the excel 2007 window, taught you how to move
around the window, and how to enter data. A major strength of excel is that you can
perform mathematical calculations and format your data. In this section, you will
learn how to perform basic mathematical calculations and how to format textand
numerical data. To start this section, open excel.
2.1 Perform mathematical calculations
In microsoft excel, you can enter numbers and mathematical formulas into cells.
Whether you enter a number or a formula, you can reference the cell when you
perform mathematical calculations such as addition, subtraction, multiplication, or
division. When entering a mathematical formula, precede the formula with an equal
(=) sign. Use the following to indicate the type of calculation you wish to perform:
+ addition
– subtraction
* multiplication
/ division
^ exponential
In the following exercises, you practice some of the methods you can use to perform
mathematical calculations.
2.2 Exercise 1
2.2.1 Addition, subtraction, multiplication and division of numbers

1. Type: Add, subtract, multiply, and divide in cells a1, b1, c1, and d1
respectively
2. Type: 12, 25, 11 and 75 in cells a2, b2, c2 and d2 respectively
3. Type: 8, 13, 6 and 5 in cells a3, b3, c3 and d3 respectively
4. Type: = a2 + a3 in cell a5 and press enter
5. Type: = b2 + b3 in cell a5 and press enter
6. Type: = c2 + c3 in cell a5 and press enter
7. Type: = d2 + d3 in cell a5 and press enter

10
When creating formulas, you can reference cells and include numbers. All of the
following formulas are valid:
(a) =a2/b2; (b) =a2+12-b3; (c) =a2*b2+12; (d) =24+53/b2

2.2.2 Perform advanced mathematical calculations

When you perform mathematical calculations in excel, be careful of precedence.
Calculations are performed from left to right, with multiplication and division
performed before addition and subtraction.
2.3 Exercise 2
advanced calculations
1. Move to cell a7.
2. Type =3+3+12/2*4.
3. Press enter.
Note: Microsoft excel divides 12 by 2, multiplies the answer by 4, adds 3, and then
adds another 3. The answer, 30, displays in cell a7.

To change the order of calculation, use parentheses. Microsoft excel calculates the
information in parentheses first.
1. Double-click in cell a7.
2. Edit the cell to read =(3+3+12)/2*4.
3. Press enter.
Note: Microsoft excel adds 3 plus 3 plus 12, divides the answer by 2, and then
multiplies the result by 4. The answer, 36, displays in cell a7.

2.4 Autosum

You can use the autosum button on the home tab to automatically add a column
or row of numbers. When you press the autosum button , excel selects the
numbers it thinks you want to add. If you then click the check mark on the formula bar
or press the enter key, excel adds the numbers. If excel's guess as to which numbers
you want to add is wrong, you can select the cells you want.

11
2.5 Exercise 3
autosum
The following illustrates autosum:

1. Go to cell f1.
2. Type 3.
3. Press enter. Excel moves down one cell.
4. Type 3.
5. Press enter. Excel moves down one cell.
6. Type 3.
7. Press enter. Excel moves down one cell to cell f4.
8. Choose the home tab.
9. Click the autosum button in the editing group. Excel selects cells f1
through f3 and enters a formula in cell f4.

10. Press enter. Excel adds cells f1 through f3 and displays the result in cell f4.

Note that you can click on the arrow next to autosum to access other automatic
calculations like average, minimum and maximum values, count numbers, etc.
12
2.6 Align cell entries
When you type text into a cell, by default your entry aligns with the left side of the
cell. When you type numbers into a cell, by default your entry aligns with the right
side of the cell. You can change the cell alignment. You can center, left-align, or right-
align any cell entry. Look at cells a1 to d1. Note that they are aligned with the left side
of the cell.

2.7 Exercise 4
To center cells a1 to d1:

1. Select cells a1 to d1.

2. Choose the home tab.
3. Click the center button in the alignment group. Excel centers each cell's
content.
Note that left and right alignment can be carried out in a similar manner.

2.8 Copy, cut and paste

You can copy or cut data from one area of a worksheet to another.

1. Select cells d9 to d12

2. Choose the home tab.
13
 3. Click the cut button.
4. Move to cell g1.

5. Click the paste button . Excel moves the contents of cells d9 to d12 to
cells g1 to g4.
2.9 Insert and delete columns and rows
You can insert and delete columns and rows. When you delete a column, you delete
everything in the column from the top of the worksheet to the bottom of the
worksheet. When you delete a row, you delete the entire row from left to right.
Inserting a column or row inserts a completely new column or row.
2.10 Exercise 5
Insert and delete columns and rows
To delete columns f and g:

1. Click the column f indicator and drag to column g.

2. Click the down arrow next to delete in the cells group. A menu appears.
3. Click delete sheet columns. Excel deletes the columns you selected.
4. Click anywhere on the worksheet to remove your selection.
14
To delete rows 7 through 12:

1. Click the row 7 indicator and drag to row 12.

2. Click the down arrow next to delete in the cells group. A menu appears.
3. Click delete sheet rows. Excel deletes the rows you selected.
4. Click anywhere on the worksheet to remove your selection.
To insert a column:
1. Click on a to select column a.
2. Click the down arrow next to insert in the cells group. A menu appears.
3. Click insert sheet columns. Excel inserts a new column.
4. Click anywhere on the worksheet to remove your selection.
To insert rows:
1. Click on 1 and then drag down to 2 to select rows 1 and 2.
2. Click the down arrow next to insert in the cells group. A menu appears.
3. Click insert sheet rows. Excel inserts two new rows.
4. Click anywhere on the worksheet to remove your selection.
2.11 Work with long text
Whenever you type text that is too long to fit into a cell, microsoft excel attempts to
display all the text. It left-aligns the text regardless of the alignment you have assigned
to it, and it borrows space from the blank cells to the right. However, a long text
entry will never write over cells that already contain entries—instead, the cells that
contain entries cut off the long text. The following exercise illustrates this.
2.12 Exercise 6
work with long text

15
 1. Move to cell a6.
2. Type now is the time for all good men to go to the aid of their army.
3. Press enter. Everything that does not fit into cell a6 spills over into the adjacent
cell.

4. Move to cell b6.

5. Type test.
6. Press enter. Excel cuts off the entry in cell a6.

7. Move to cell a6.

8. Look at the formula bar. The text is still in the cell.
2.13 Change a column's width
You can increase column widths. Increasing the column width enables you to see
the long text.
2.14 Exercise 7
change column width

1. Make sure you are in any cell under column a.

2. Choose the home tab.
3. Click the down arrow next to format in the cells group.
4. Click column width. The column width dialog box appears.
5. Type 55 in the column width field.
6. Click ok. Column a is set to a width of 55. You should now be able to see all
of the text.

16
Change a column width by dragging
You can also change the column width with the cursor.
1. Place the mouse pointer on the line between the b and c column headings. The
mouse pointer should look like the one displayed here , with two arrows.
2. Move your mouse to the right while holding down the left mouse button. The
width indicator appears on the screen.
3. Release the left mouse button when the width indicator shows approximately
20. Excel increases the column width to 20.
Change a column width by autofit column width
1. Select the column or column you want to change the column width.
2. Choose the home tab.
3. Click the down arrow next to format in the cells group.
4. Click on autofit column width. You should now be able to see all of the text.
2.15 Format numbers
You can format the numbers you enter into microsoft excel. For example, you can add
commas to separate thousands, specify the number of decimal places, place a dollar
sign in front of a number, or display a number as a percent.
2.16 Exercise 8

1. Move to cell b8.

2. Type 1234567.
3. Click the check mark [ √ ] on the formula bar.

4. Choose the home tab.

5. Click the down arrow next to the number format box. A menu appears.
6. Click number. Excel adds two decimal places to the number you typed.

17
 7. Click the comma style button . Excel separates thousands with a comma.
8. Click the accounting number format button . Excel adds a dollar sign to
your number.
9. Click twice on the increase decimal button to change the number format
to four decimal places.
10. Click the decrease decimal button , if you wish to decrease the number of
decimal places.
Change a decimal to a percent.

1. Move to cell b9.

2. Type 0.35 (note the decimal point).
3. Click the check mark [ √ ] on the formula bar.

4. Choose the home tab.

5. Click the percent style button . Excel turns the decimal to a percent.

This is the end of section 2. You can save and close your file. See section 1
(subsections 1.23 and 1.24) to learn how to save and close a file.

18
Section 3: Creating excel functions, filling cells, and printing
By using functions, you can quickly and easily make many useful calculations,
such as finding an average, the highest number, the lowest number, and a count of the
number of items in a list. Microsoft excel has many functions that you can use.
3.1 Using reference operators
To use functions, you need to understand reference operators. Reference operators
refer to a cell or a group of cells. There are two types of reference operators: Range
and union.
A range reference refers to all the cells between and including the reference. A range
reference consists of two cell addresses separated by a colon. The reference a1:A3
includes cells a1, a2, and a3. The reference a1:C3 includes cells a1, a2, a3, b1, b2, b3,
c1, c2, and c3.
A union reference includes two or more references. A union reference consists of two
or more numbers, range references, or cell addresses separated by a comma. The
reference a7,b8:B10,c9,10 refers to cells a7, b8 to b10, c9 and the number 10.
3.2 Understanding functions
Functions are prewritten formulas. Functions differ from regular formulas in that you
supply the value but not the operators, such as +, -, *, or /. For example, you can use
the sum function to add. When using a function, remember the following:
1. Use an equal sign to begin a formula.
2. Specify the function name.
3. Enclose arguments within parentheses. Arguments are values on which you
want to perform the calculation. For example, arguments specify the numbers
or cells you want to add.
4. Use a comma to separate arguments.
Here is an example of a function:
=sum(2,13,a1,b2:C7)
In this function, known as the sum function:
1. The equal sign begins the function.
2. Sum is the name of the function.
3. 2, 13, a1, and b2:C7 are the arguments. Parentheses enclose the arguments.
4. Commas separate the arguments.
After you type the first letter of a function name, the autocomplete list appears. You
can double-click on an item in the autocomplete list to complete your entry quickly.
Excel will complete the function name and enter the first parenthesis.

19
3.3 Exercise 1
functions
The sum function adds argument values.

1. Open microsoft excel.

2. Type 12 in cell b1.
3. Press enter.
4. Type 27 in cell b2.
5. Press enter.
6. Type 24 in cell b3.
7. Press enter.
8. Type =sum(b1:B3) in cell a4.
9. Press enter. The sum of cells b1 to b3, which is 63, appears.
Alternate method: Enter a function with the ribbon

1. Type 150 in cell c1.

2. Press enter.
3. Type 85 in cell c2.
4. Press enter.
5. Type 65 in cell c3, and press enter
6. Choose the formulas tab.
7. Click the insert function button. The insert function dialog box appears.
8. Choose math & trig in the or select a category box.
9. Click sum in the select a function box.
10.click ok.
11. The function arguments dialog box appears with c1:C3 displayed in the
number1 field.

20
 12. Type c1:C3 in the number1 field, if it does not automatically appear.
13.click ok. The sum of cells c1 to c3, which is 300, appears.

3.4 Calculate an average

You can use the average function to calculate the average of a series of numbers.

1. Move to cell a6.

2. Type average. Press the right arrow key to move to cell b6.
3. Type =average(b1:B3).
4. Press enter. The average of cells b1 to b3, which is 21, appears.

3.5 Find the lowest number

You can use the min function to find the lowest number in a series of numbers.

1. Move to cell a7.

2. Type min. Press the right arrow key to move to cell b7.
3. Type =min(b1:B3).
4. Press enter. The lowest number in the series, which is 12, appears.
21
 3.6 Find the highest number

You can use the max function to find the highest number in a series of numbers.

1. Move to cell a8.

2. Type max. Press the right arrow key to move to cell b8.
3. Type =max(b1:B3).
4. Press enter. The highest number in the series, which is 27, appears.

3.7 Count the numbers in a series of numbers

You can use the count function to count the number of numbers in a series.

1. Move to cell a9.

2. Type count. Press the right arrow key to move to cell b9.
3. Type =count(b1:B3).
4. Press enter. The number of items in the series, which is 3, appears.

22
3.8 Fill cells automatically
You can use microsoft excel to fill cells automatically with a series. For example, you
can have excel automatically fill your worksheet with days of the week, months of
the year, years, or other types of series.

3.9 Exercise 2
(a) Fill cells automatically
The following demonstrates filling the days of the week:

1. Click the sheet2 tab. Excel moves to sheet2.

2. Move to cell a1.
3. Type sun.
4. Move to cell b1.
5. Type sunday.
6. Select cells a1 to b1.
7. Choose the home tab.
8. Click the bold button . Excel bolds cells a1 to b1.
9. Find the small black square in the lower-right corner of the selected area. The
small black square is called the fill handle.
10. Grab the fill handle and drag with your mouse to fill cells a1 to b14. Note how
the days of the week fill the cells in a series. Also, note that the auto fill options
button appears.

23
(b) Fill times
The following demonstrates filling time:
1. Type 1:00 into cell c1.
2. Grab the fill handle and drag with your mouse to highlight cells c1 to c14.
Note that each cell fills, using military time.
3. Press esc and then click anywhere on the worksheet to remove the
highlighting.
To change the format of the time:
1. Select cells c1 to c14.
2. Choose the home tab.
3. Click the down arrow next to the number format box . A menu
appears.
4. Click time. Excel changes the format of the time.

(c) Fill numbers

You can also fill numbers.

1. Type a 1 in cell d1.

2. Type a 2 in cell d2.
3. Select cells d1:D2
4. Grab the fill handle and drag with your mouse to highlight cells d1 to d14.
5. The cells fill as a series, starting with 1, 2,
3. Here is another interesting fill feature.

1. Go to cell e1.
2. Type section 1.
3. Grab the fill handle and drag with your mouse to highlight cells e1 to e14. The
cells fill in as a series: Section 1, section 2, section 3, and so on.

3.10 Set print options

There are many print options. You set print options on the page layout tab. Among
other things, you can set your margins, set your page orientation, and select your paper
size.
Margins define the amount of white space that appears on the top, bottom, left,
and right edges of your document. The margin option on the page layout tab provides
several standard margin sizes from which you can choose.
Paper comes in a variety of sizes. Most business correspondence uses 8 ½ by 11 paper,
which is the default page size in excel. If you are not using 8 ½ by 11 paper, you can
use the size option on the page layout tab to change the size setting.

24
3.11 Exercise 3
Set the page layout (margins)

1. Choose the page layout tab.

2. Click margins in the page setup group. A menu appears.
3. Click wide. Excel sets your margins to the wide settings.
Set the page orientation

1. Choose the page layout tab.

2. Click orientation in the page setup group. A menu appears.
3. Click landscape. Excel sets your page orientation to landscape.

Set the paper size

1. Choose the page layout tab.

2. Click size in the page setup group. A menu appears.
25
 3. Click the paper size you are using. Excel sets your page size.

3.12 Print
The simplest way to print is to click the office button, highlight print on the menu
that appears, and then click quick print in the preview and print the document pane.
Dotted lines appear on your screen, and your document prints. The dotted lines
indicate the right, left, top, and bottom edges of your printed pages. To print from
microsoft excel, you can proceed as follows:
1. Click on microsoft office button
2. Highlight or point the mouse on print.
3. Click on print.
4. In the name box, under printer option, choose an appropriate printer.
5. Under the print range option, choose appropriate range of pages to be
printed.
6. Under copies option, choose appropriate number of copies of each page to
be printed.
7. Click on ok when ready.

3.13 Exercise 4
print preview

1. Click the office button. A menu appears.

2. Highlight print. The preview and print the document pane appears.

26
 3. Click print preview. The print preview window appears, with your document
in the center.

3.14 Exercie 5
print

1 Click on microsoft office button

2 Highlight or point the mouse on print.
3 Click on print. The print dialog box appears

4. In the name box, under printer option, choose an appropriate printer.

5. Under the print range option, choose appropriate range of pages to be
printed.
6. Under copies option, choose appropriate number of copies of each page to
be printed.
27
 7. Click on ok when ready.
This is the end of section 3. You can save and close your file.

Section 4: Creating charts

In microsoft excel, you can represent numbers in a chart. On the insert tab, you can
choose from a variety of chart types, including column, line, pie, bar, area, and scatter.
The basic procedure for creating a chart is the same no matter what type of chart you
choose. As you change your data, your chart will automatically update.
You select a chart type by choosing an option from the insert tab's chart group.
After you choose a chart type, such as column, line, or bar, you choose a chart sub-
type. For example, after you choose column chart, you can choose to have your
chart represented as a two-dimensional chart, a three-dimensional chart, a cylinder
chart, a cone chart, or a pyramid chart. There are further sub-types within each of
these categories. As you roll your mouse pointer over each option, excel supplies a
brief description of each chart sub-type.

4.1 Create a chart

To create the column chart shown above, start by creating the worksheet below
exactly as shown.

28
 After you have created the worksheet, you are ready to create your chart.
4.2 Exercise 1
create a column chart

.
1. Select cells a3 to d6. You must select all the cells containing the data you
want in your chart. You should also include the data labels.
2. Choose the insert tab.
3. Click the column button in the charts group. A list of column chart sub-types
types appears.
4. Click the clustered column chart sub-type. Excel creates a clustered column
chart and the chart tools context tabs appear.
4.3 Apply a chart layout
Context tabs are tabs that only appear when you need them. Called chart tools, there
are three chart context tabs: Design, layout, and format. The tabs become available
when you create a new chart or when you click on a chart. You can use these tabs to
customize your chart.

29
 4.4 Exercise 2
apply a chart layout

1. Click your chart. The chart tools become available.

2. Choose the design tab.
3. Click the quick layout button in the chart layout group. A list of chart layouts
appears.
4. Click layout 5. Excel applies the layout to your chart.

4.5 Change the style of a chart

A style is a set of formatting options. You can use a style to change the color and
format of your chart. Excel 2007 has several predefined styles that you can use. They
are numbered from left to right, starting with 1, which is located in the upper- left
corner.
4.6 Exercise 3
Change the style of a chart

1. Click your chart. The chart tools become available.

2. Choose the design tab.
3. Click the more button in the chart styles group. The chart styles appear.

30
 4. Click style 42. Excel applies the style to your chart.

5.1 The sumif function

syntax:
sumif(range,criteria,sum_range)

Example

Property Value Commission

100,000 7,000
200,000 14,000
300,000 21,000
400,000 28,000
Formula Description (Result)
=SUMIF(A2:A5,">160000",B2:B5) Sum of the commissions for
property values over 160,000
(63,000)
=SUMIF(A2:A5,">160000") Sum of the property values over
160,000 (900,000)
=SUMIF(A2:A5,"=300000",B2:B3) Sum of the commissions for
property values equal to 300,000
(21,000)

31
5.2 The averageif function

Returns the average (arithmetic mean) of all the cells in a range that meet a given
criteria.

Syntax-averageif(range,criteria,average_range)

Example: Averaging profits from regional offices

5.3 The countif function

Counts the number of cells within a range that meet the given criteria.

Syntax: Countif(range,criteria)

Example 1: Common countif formulas

5.4

5.5

5.6 The if function

32
5.7 The if function

Returns one value if a condition you specify evaluates to true and another value if it
evaluates to false.
Syntax
if(logical_test,value_if_true,value_if_false)

Example

5.8 The and function

Returns true if all its arguments are true; returns false if one or more argument is
false.

Syntax
and(logical1,logical2, ..)

Logical1, logical2, ... Are 1 to 255 conditions you want to test that can be either
true or false.

Example

33
Using pivottables and charts

Pivottables are extremely powerful tools for data analysis and

pivot charts allow the visualization of that analysis. You will
create a pivot table and chart of the data collected from a web
survey. This will allow you to find useful information about the
patterns in the data and communicate those findings through a
graphical means. An example of the use of a pivottable would
involve the financial data at your institution. The income and
expenses are stored in a table with each record or entry having
an amount, a payee/payor, an account, and a month. To evaluate
the year to date budget, a pivot table could be created. The
month would become the column headings, the account would
become the row headings and the amounts would become the
value items for each row-column intersection. The result would
show the dollar volume for each account on a month-by-month
basis. Obviously, this is a very useful way to organize the data.
For the trustees meeting, we could create a pivotchart of the data
allowing the graphical display of the financial data. Remember, a
picture is worth a thousand words.

To create a pivottable

1. Click on tab named sheet 3 to switch to that sheet.

2. Rename the sheet by right clicking on the tab and

selecting rename. Type in the name of survey

3. On the data menu select get external data and click import text file.

4. Navigate to the file you wish to import (results.txt for the lab).

5. Double click on the file

-or-
Click once on the file and click import.

6. The import text wizard will begin. Click next to accept

a delimited text file and start importing at row 1

7. On step two the delimiter should be a comma and the

data should be organized and readable in the data
preview box. Click next.

34
 8. Click finish on step three to accept the general
data format and complete the wizard.

9. On the import data dialog, click ok to put the data

on the existing worksheet.

10. Select from a1 to h21 on the worksheet and click

pivottable and pivotchart report from the data
menu.

11. Click next, to accept the analysis of excel data using a pivottable.

12. You can click next on step 2, because you selected the range
for the pivottable before starting the wizard.

13. Click finish, to place the pivottable in a new worksheet.

14. Rename the worksheet to analysis.

35
15. Drag and drop gender from the pivottable dialog to the drop column
fields here range.

16. Drag and drop age from the pivottable dialog to the drop row fields here
range.

17. Drag and drop ownpc from the pivottable dialog to the drop data items
here range.

18. Right click on the age button in cell a4 select group and outline and
select group.

19. Enter 15 in the starting at box and 75 in the ending at box.

20. Make sure 10 is in the by box and click ok. This will group the ages
with ten years in each division.

Your pivot table now shows the number of women and men by age
group that own computers. You also wish to quantify the
percentage of women that own computers at each age group. A
few simple calculations will display the answer to this question for
you as well.

21. Select cell e3 and type % females that.

22. Select cell e4 and type own pcs.

23. In cell e5 type in

=b5/countif(analysis!$d$2:$d$21,"F"). This formula
calculates the percentage by dividing the number of
36
 women in a particular age group (b5) by the total
number of women (countif(analysis!$d$2:$d$21,"F").

24. Click on the fill handle in cell e5 and drag it to cell e11
and drop it. This replicates the formula for the rest of the
rows.

25. Format the borders of the new column to

match those of the pivottable.
You may drag and drop items on and off the pivottable to re-
pivot the table thereby answering another question. For instance,
you may wish to know what major would people be more likely
to own a computer or how old are the computers that women own
versus the computers that men own. You really get complex data
analysis without a great deal of work.

To display analyzed data with a pivotchart

1. Click on the chart wizard button in the pivottable

dialog and the pivotchart will be instantly created.

2. The wizard makes a best guess as to the chart type and the
formatting characteristics. Right click on the chart and select
chart type to bring up the chart type dialog.

3. You may select different chart types and then press the press
and hold to view sample button to see what your data would
look like in that format.

4. Click ok when you have made your selection.

You
may

37
 customize the pivotchart as you see fit. Keep in mind that you may also
change the underlying pivottable by dragging and dropping fields from
the pivottable dialog on to or off of the chart. Any fields that appear on
the chart with a drop down arrow on them will allow you to turn off the
display of individual data items in them. You could have this chart
show just the women that owned pcs using this feature.

To save pivot table to web

1. With the appropriate sheet active select save as web page from the File menu.

2. Select desktop as the saving location and type in the name for
your page in the file name box.

3. Select the selection radio button and check the add interactivity check
box.

4. Click the save button to complete the process.

5. Minimize all the open applications on your system and double

click on the .htm file you created on the desktop. (it will be
named by the file name you entered in step 2.)

Interactivity in a pivotchart saved as a web page allows you to

expand and contract the columns of data to see the individual
record for each item.
Notice, however, that the groupings you applied are not
retained when the chart is saved as a web page

38