Agenda

Introduction to excel functions

Formatting
Useful tips
Formulas
Charts
Importing data
Printing excel worksheets
Practicing with reports

1
Intro to Excel

• Excel is used for budgeting, reporting, and data analysis

• Each workbook is made up of worksheets
• Each worksheet is made up of a grid with cells

2
Opening Excel

•CIick on start, programs Microsoft Excel

•CIick on open new document
•A new workbook will appear
•Within each workbook is a worksheet or worksheets

3
Basics of Excel

• An excel wOrKsheet is made up of a grid of cells

• Each cell has distinct coordinates
• the colum ns are indicated by letters
• the rows are indicated by numbers
• One entry per cell
• each cell should contain either numbers or text, not bOth
•ExampIe:

A B
1 100 moun
2 300 timoun
3 4 maman

4
 CONFIDENTIAL DRAFT

Saving your workbook

• Create templates that are saved before entering data so that you
don't have to create a new one each time you need it
• Ensure version control by naming each spreadsheet with name,
date, version number
• Ex. RapportmensuaI_06Jun v1.xls
Cell Formatting

• Number
• Alignment
• Fonts
• Borders

6
 CONFIDENTIAL DRAFT

Inserting comments

• Insert comments into a cell to provide supplementary information

and explanations
• Ex. 1 CD4 machine broken, 2 staff hired in May
e Right click on the cell in which you want to insert a comment
• Click on Insert Comment

7
Formatting
Worksheet Formatting

• Gridlines
• New Worksheet

9
Standard Toolbar

Attachment A to Round 6 Proposal Form - Targets and I dicators Table

I Detail

10
 Standard Toolbar Explanati ons

Email

Save Drawing
Spell Chec Paste

Help

Paste
Print
Preview View
{z

Research

open

New
document

11
Formatting Toolbar

Attachment A to Round 6 Proposal Form - Targets and I dicators Table

I Dctall

12
Formatting Toolbar

Comma
Align Align text
Font right Style
Left
Size

Currency Borders
Sty
Decima I

• 10 <. 0 .00

_ Indent Fo nt

T Background
Cells co or
e

Increa e
Decimal

13
Conditional Formatting

The Conditional Formatting dialog box

is big, wjth enough boxes and buttons to be
intimidating, because bit’s built to take a lot
oT possible conditions. But once
you hnow which boxes and buttons to
Cdmc pay attention to, it's a snap
Sold Sheet First you choose the cell value
31 conditions to trigger the conditional
73 AutoFormat:. Formatting by selecting less than and
12 typing 15.
c«oti»n»l ramp tm... Then you click the Format butlon to pick
95
Stfie. bold red 1ormat for any values less than 15.
124 Now Excel knows that the condition that
triggers bold red formatting is any value
that is less than 15 in column C. See?
It's a snap.

Note You can add up to three

conditions to a cell or a given range ot
cells.

Condoon
a sin
Inserting Headers and Footers

To create headers and footers, if you're looking at the worksheet in print

preview, click Setup. Or in normal view, click the File menu and then
click Page Setup. In the Page Setup dialog box, click the Header/Footer
tab.
Click the arrow next to the Header box or the Footer box and choose
from the list you see. You could select Page 1 and the name of the
worksheet. Or you could enter your name, the page number, and the date.
(If you decide later that you don't want a header or a footer, go back and
select None.) If you want both a header and a footer, click the other
arrow and choose from that list.

15
Useful tips
Freezing
Panes

• Column titles Select the first row below the titles.

• Row titles Select the first column to the right (for example, to
keep supplier names in sight as you scroll across the worksheet).
e Both column and row titles Click the cell that is both just
below
the column titles and just to the right of the row titles.
• If you don't get it right the first time, it's easy to unfreeze and try
again. Just click Unfreeze Panes on the Window menu.
Comparing 2 worksheets at one time

Create a new workbook that you can compare with another file that's
already open by clicking New on the File menu, and then clicking
Blank workbook in the New Workbook task pane. A new workbook,
called ”Book1”, opens.
On the Window menu, click the Compare Side by Side with
Compare side by side1 command.
Scroll in the workbook at the top of the window. See how the workbook
at the bottom of the window scrolls along with you.
Scrolling to the left or right works just the same as scrolling down or up
in the worksheet. Both workbooks scroll together.

18
Viewing 2 worksheets at one time

• Tips
• The worksheet at the top of the window is the one that's in view
when you click the Side by Side command.
e You can navigate from either the top or bottom worksheet. Just
click in the worksheet you want to navigate in to activate the scroll bars
in that sheet.
• You can see data up close by zooming in on both worksheets at the
same time by clicking Zoom on the View window.

19
Summing

Tip The numbers you

Unit select don't have to be lined
up together or in the same row
$22.00
or column. Add up numbers
anywhere on the worksheet by
pressing CTRL and then
selecting each number.

20
Averages, Minimum, Maximum

Need an average? Select the

Un numbers, right-click the status
bar, and then click Average on
00 the shortcut menu, which
gives you the arithmetic mean.
The answer in the status bar
changes from a sum to
Average=$39.23.
If you want to do even more,
just click one of the other
options on the shortcut menu,
such as Max or Min to find the
A erage=G38 maximum or minimum in a
range.
Type less, get more

‹• Pretend that you're typing the first six months of the year for the
umpteenth time. Only this time you'll do it the easy way.
Type "January" so that Excel knows what you want.
Select the January cell, and then position the mouse pointer over the lower-
right corner of the cell until the black cross (+) appears.
‹› Drag the file handle over the range you want to fill. As you drag,
the ScreenTip tells you what will be filled in.
Release the mouse button to fill the series in.

Tips
For some lists you need to type two entries to establish a pattern. For
example, to fill in a series of numbers such as 3, 6, 9, type two numbers,
select both cells, and then drag the fill handle.
You can also drag up or to the left as well as drag down or to the right. Ever
need to type the same word many times, such as Complete in 10 consecutive
rows? Just type the word once, and then drag the fill
handle down rows or across columns to enter the same text without
typing.

22
Formulas
How to create formulas

Entering cell references lets Exœl auæmatically update formula results if cell values are changed. For example:
•Type=C4+C7 in a cell.
•Or type the equal sign (=), dick œll C4, then type the plus sign (+), and finally click cell C7.

Cell references Refer to values in

A10 the cell in column A and row 10

A10,A20 cell A10 and cell A20

A10:A20 the range of cells in column A and rows 10

through 20
B15:E15 the range of cells in row 15 and columns B
through E
A10:E20 the range of cells in columns A through E and
rows 10 through 20
Add, Divide, Multiply, and Subtract

• Type an equal sign (=), use math operators, and then press ENTER.
• =10+5 to add
• =10-5 to subtract
• =10*5 to multiply
• =10/5 to divide
• Formulas are visible in the formula bar when you select a cell that
contains a result. If the formula bar is not visible, on the Tools menu, click
Options. Click the View tab, and select the Formula bar check box.

25
Sum values in a row or column

Use the SUM function, which is a prewritten formula, to add all the
values in a row or column:
Click a cell below the column of values or to the right of the row of
values.
Click the AutoSum button on the Standard toolbar, and then press
ENTER.
To add some of the values in a column or row:
Type an equal sign, type SUM, then type an opening parenthesis. Type
or select the cell references you want to add. A comma (,) separates
individual arguments that tell the function what to
calculate.
Type a closing parenthesis, and then press ENTER. For
example: =SUM(B2:B4,B6) and =SUM(B2,B5,B7)

26
Checking summation formulas to make sure you have not missed any cells

• For example, if you are working with a column that has subtotals by
section,
• in addition to selecting subtotal 1 + subtotal 2 + subtotal 3 +
subtotal 4, you can do a formula for the sum the entire column (the
individual line items and the subtotals) and divide by 2.
Copy a formula instead of creating a new one

B7 d =SUIYI(ED:B6)
A B: C D
1 J Feb
Note You can drag
2 Entertalnment an I the fill handle to copy
formulas only into cells
4 Video reNals 7.il 11,97’
5 Movies 16.ŒI
that are next to each
32.00 other, either horizontally
7 Totsls or vertically.

Drag the black cross from the cell containing the fôrmula to
the œil where the fôrmula will be œpied, then release the fill
handle.
Auto Fill Options button appears but requises no
acöons.

28
References

-C4*8 DQ
-C5” D

Relative references change as they are copied. Absolute

references stay the same as they are copied.

29
Linhing
cells
Create a link between cells in the same worksheet or workbook
e Click the cell that contains the data you want to link to, and then click Copy.
• Click the cell you want to link from, and then click Paste .
• Click Paste Options and then click Link Cells.

Create a link between cells in different worksheets

• Open both the workbook that will contain the link (called the destination (destination file: The file that a
linked or embedded object is inserted into. The source 1iIe contains the information that is used
to create the object. When you change information in a destination file, the information is not
updated in the source file.) workbook), and the workbook that contains the data you want to link to
(called the source (source file: The file that contains information that was used to create a linked or
embedded object. When you update the information in the source file, you can also update the
linked object in the destination file.) workbook).
In the destination workbook, click Save
Select a cell or cells you want to link 1rom.
If you are creating a new formula, type = (an
equal sign).
If you are entering the link elsewhere in the formula, type the operator (operator: A sign or symbol that
speci1ies the type o1 calculation to perform within an expression. There are mathematical, comparison,
logical, and reference operators.) or function that you want to precede the linI‹.
On the Window menu, click the name of the source workbook, and then click the worksheet that
contains the cells you want to link to.
Select the cells you want to link to.
Complete the 1ormula. When you 1inish entering the formula, press ENTER.

30
Break Links

Break a link to a source

Important When you break a link to a source, all formulas that use the
source are converted to their current value. For example, the link
=SUM([Budget.xIs]Annual!C10:C25) would be converted to =45.
Because this action cannot be undone, you may want to save a version
of the file before you start.
On the Edit menu, click Links.
In the Source list, click the link you want to break. To select multiple
linked objects, hold down CTRL and click each linked object.
To select all links, press CTRL+A.
Click Break Link.

31
Replace a formula with its calculated value

• Caution When you replace a formula with its value, Excel permanently
removes the formula. If you accidentally replace a formula with a value and
want to restore the formula, click Undo immediately after you enter or
paste the value.
• Click Copy
• Click Paste
• Click the arrow next to Paste Options
, and then click Values Only.

32
Understand error values

##### The column is not wide enough to display the content. Increase
column width, shrink contents to fit the column, or apply a different
number format.
#REF! A cell reference is not valid. Cells may have been deleted or
pasted over.
#NAME? You may have misspelled a function name.
Cells with errors such as #NAME? may display a color triangle. If you
click the cell, an error button appears to give you some error correction
options. How to use the button is not covered in this course.

33
 100

East 1st Qtr

60
2nd Qtr’
West 3rd Otr
40 • North 4th Qtr

20

1st Qtr 2nd Qtr 3rd Otr 4th Qtr

Creating Charts
180
160
140 70
120
North D East
100 SO
West West
80
East 30 m North

40

1st Otr 2nd Qtr 3rd Qtr 4th Qtr

1st Otr 2nd Otr 3rd Qtr 4th Otr

Importing data
Printing Worksheets
Print Preview

What print preview gives you

• A view of how your worksheet will look
when printed.
Next and Previous buttons to see all the
pages.
A Zoom button to switch between a full-
page view and a magnified partial view.
A Print button to select options and to
start printing.
A Setup button to set up the
appearance of the page.
A Margins button to adjust page
margins, header and footer margins, and
column widths.
A Page Break Preview button to adjust
page breaks. (Whether you see Page Break
Preview or Normal depends on which
view you were in when you clicked Print
Preview.)
A Close button to close print preview.
37
Print Cell Gridllnes

Excel automatically prints

worksheets without celt gridlines.
However, it may be easier for
some readers to view data on
paper with the cell gridlines in
place.
Click on the File menu, click
Page Setup. Click the Sheet
tab. Under Print, select the
Gridllnes check box.
Viewing or Printing Formulas

• It's easy to print formulas

instead of formula results. On the
Tools menu, point to Formula
Auditing, and then click Formula
Auditing Mode. That's all you
{iz3,ss2.ON have to do to see the formulas in
$76,97D.18 I
the worksheet. Then print as you
normally would.

=5UN(BB:B75) =\fAR(BB:B75}
-sU»(B7z:Bi12) AR\Bz6:Bt42)
=5UPI(B14:3:B210) =VAR(Bt13:BZt0)

39
 Page Setup

You can also get more columns on the page by using

the Fit to option. This will temporarily reduce the data
Header/F ter Sheet on the printed page to a smaller size.
Note This option does not change the size of the
Landscape
data on your worksheet. Only the printed data is
smaller.
i r O o n s From print preview, click Setup (or in normal view, on
' Adjust to: . % normal size the File menu, click Page Setup). On the Page tab,
89 select the Fit to option.
page(s) side by
In the pages(s) wide by box, 1 is already entered. This
means that the printed data will be one page wide.
In the tall box, 1 means that the printed data will be one
page long.
The Fit to option is another way to print Click OK to go back to print preview. See if the text is
your data on one page. ' readable. If you're not sure, click Setup again. On the
Leave k in the page(s) wide by box. Page tab, look at the number in the Adjust to bOx.
Leave 1 in the tail box. Depending on your audience, 50% and above should be
readable. In the picture, the data is adjusted \O 89%.

Tip The Zoom command will not affect how a

worksheet is printed. It changes only what you see on
yOur cOmputer. FOr example, changing the
magnification to 75% or 150% will not make the
worksheet print at a smaller or larger percentage.
40
Defining Number of Pages

Imagine that you have a big worksheet with a lot of data. It definitely
will not fit on one printed page, but you'd like to fit the data onto a
specific number of pages. Six pages, say.
Start by clicking Setup in print preview {or in normal view, on the
File menu, click Page Setup). On the Page tab, Click Fit to. Because you
want to have all the columns on each page, you leave 1 in the pages(s)
wide by box.
In the tall box, you enter 6. Click OK to go back to print preview.
Now you see that you have six pages, and how readable they are. Click
Setup to go back to the Page Setup dialog box to see the number in the
normal size box. Any number larger than 50O/o means the
pages will probably be readable.
 Adjust page breaks to control page contents

When a worksheet prints on several pages, Excel

inserts automatic page breaks that divide the
worksheet into separate pages for printing. These
page breaks appear as dotted lines, which you can
see by looking at the worksheet in page break
preview. You can control what appears on a page by
changing those page breaks or creating your own.

To do that, in print preview, click Page Break

Preview (or in normal view, click Page Break
Preview on the View menu).

In page break preview, a big number identifies the page. An If you don't like the page breaks you create, right-
automatic page break appears as a dotted line. A click the worksheet and select Reset All Page
manual page break appears as a solid blue line. Breaks. Or you can remove a page break by
dragging it outside the print area.
 CONFIDENTIAL DRAFT

Choose what data to print

• Say you want to print just part of your data, not the whole
worksheet. Perhaps you want to print cells C7 through C16, or the
results of one calendar quarter, or the products from one supplier.
How do you do that?
• Select the area you want to print. Then, on the File menu, click
Print. Under Print what, click Selection. Then click OK.
• If you expect to print a particular area of a worksheet frequently, it's
convenient to define and save it as a print area.

• To do that, on the View menu, click Page Break Preview. Select the area
that you expect to print often. Next, on the File menu, point to Print Area,
and then click Set Print Area. When you save the workbook, your defined
print area is also saved. You can save only one defined print area at a time on
a worksheet.
• When you're ready to print, on the File menu, click Print. Only the
defined print area will be printed.

4
Printing row and column titles on each page

•On the File menu, select Page

Setup. In the Page Setup
dialog box, click the Sheet
tab and enter the row titles
and column titles you want
Excel to print on every page.

You can print the column titles (months) and the

row titles (company names) on every page.
You can also print the alphabetical column
headings and numerical row headings on
every page.