Microsoft Excel Training

1
Microsoft Excel Training

• September 16, 2006
• CHART
• Hinche, Haiti

2
Agenda

• Introduction to excel functions
• Formatting
• Useful tips
• Formulas
• Charts
• Importing data
• Printing excel worksheets
• Practicing with reports

3
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

4
Opening Excel

• Click on start, programs Microsoft Excel
• Click on open new document
• A new workbook will appear
• Within each workbook is a worksheet or worksheets

5
Basics of Excel

• An excel worksheet is made up of a grid of cells
• Each cell has distinct coordinates
• the columns are indicated by letters
• the rows are indicated by numbers
• One entry per cell
• each cell should contain either numbers or text,
  not both
• Example

6
Saving your workbook

• Create templates that are saved before entering
  data so that you dont have to create a new one
  each time you need it
• Ensure version control by naming each spreadsheet
  with name, date, version number
• Ex. Rapportmensual_06Jun_v1.xls

7
Cell Formatting

• Number
• Alignment
• Fonts
• Borders

8
Inserting comments

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

9
Formatting
10
Worksheet Formatting

• Gridlines
• New Worksheet

11
Standard Toolbar
12
Standard Toolbar Explanations
Copy
Email
Permission
Sort
Save
Drawing
Paste
Spell Check
Help
Paste Special
Print Preview
View (Zoom)
Auto Sum
Cut
Chart
Research
Print
open
New document
13
Formatting Toolbar
14
Formatting Toolbar
Comma Style
Align text right
Align text Left
Font Size
More options
Currency Style
Borders
Decrease Decimal
Italics
Align text center
Percent Style
Indent
Font color
Bold
Underline
Font Type
Merge Cells
Background color
Increase Decimal
15
Conditional Formatting

• The Conditional Formatting dialog box is big,
  with enough boxes and buttons to be intimidating,
  because it's built to take in a lot of possible
  conditions. But once you know which boxes and
  buttons to pay attention to, it's a snap
• First you choose the cell value conditions to
  trigger the conditional formatting by selecting
  less than and typing 15.
• Then you click the Format button to pick bold red
  format for any values less than 15.
• 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 of cells.

16
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.

17
Useful tips
18
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).
• 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.

19
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.

20
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.
• 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.

21
Summing

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

22
Averages, Minimum, Maximum

• Need an average? Select the numbers, right-click
  the status bar, and then click Average on the
  shortcut menu, which gives you the arithmetic
  mean. The answer in the status bar changes from a
  sum to Average39.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 maximum or minimum in a
  range.

23
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 fill 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.

24
Formulas
25
How to create formulas

• Entering cell references lets Excel automatically
  update formula results if cell values are
  changed. For example
• TypeC4C7 in a cell.
• Or type the equal sign (), click cell C4, then
  type the plus sign (), and finally click cell
  C7.

26
Add, Divide, Multiply, and Subtract

• Type an equal sign (), use math operators, and
  then press ENTER.
• 105 to add
• 10-5 to subtract
• 105 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.

27
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(B2B4,B6) and SUM(B2,B5,B7)

28
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. 

29
Copy a formula instead of creating a new one

• Note    You can drag the fill handle to copy
  formulas only into cells that are next to each
  other, either horizontally or vertically.

                                                
                                                  
           Drag the black cross from the cell
containing the formula to the cell where the
formula will be copied, then release the fill
handle.        Auto Fill Options button
appears but requires no actions.
30
References
                                                
                                                  
           Relative references change as
they are copied.        Absolute references
stay the same as they are copied.
31
Linking cells

• Create a link between cells in the same worksheet
  or workbook
• 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 file 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 from.
• 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 specifies the type of calculation to
  perform within an expression. There are
  mathematical, comparison, logical, and reference
  operators.) or function that you want to precede
  the link.
• 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 formula. When you finish entering
  the formula, press ENTER.

32
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.xlsAnnual!C10C25) 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 CTRLA.
• Click Break Link.

33
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.

34
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.

35
Creating Charts
36
Importing data
37
Printing Worksheets
38
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.

39
Print Cell Gridlines

• Excel automatically prints worksheets without
  cell 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 Gridlines
  check box.

40
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 have to do to see the formulas in
  the worksheet. Then print as you normally would.

41
Page Setup

• You can also get more columns on the page by
  using the Fit to option. This will temporarily
  reduce the data on the printed page to a smaller
  size.
• Note    This option does not change the size of
  the data on your worksheet. Only the printed data
  is smaller.
• From print preview, click Setup (or in normal
  view, on the File menu, click Page Setup). On the
  Page tab, select the Fit to option.
• 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.
• Click OK to go back to print preview. See if the
  text is readable. If you're not sure, click Setup
  again. On the Page tab, look at the number in the
  Adjust to box. Depending on your audience, 50
  and above should be readable. In the picture, the
  data is adjusted to 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.

                                                
                                                  
    The Fit to option is another way to print
your data on one page.        Leave 1 in the
page(s) wide by box.        Leave 1 in the tall
box.
42
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 50 means the pages will probably be
  readable.

43
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).
• If you don't like the page breaks you create,
  right-click the worksheet and select Reset All
  Page Breaks. Or you can remove a page break by
  dragging it outside the print area.

                                                
                                                  
    In page break preview, a big number
identifies the page.        An automatic page
break appears as a dotted line.        A manual
page break appears as a solid blue line.
44
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.

45
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.