Microsoft Xcel Templates - PDF

Document Sample
Microsoft Xcel Templates - PDF Powered By Docstoc
					      Microsoft Excel 2007

                    Excel 2007 File Formats
                                 Using Templates
               Workbooks and Worksheets
                             Excel and the Cell
                        Viewing and Printing

The College of Saint Catherine
Help Desk | 651-690-6402 |
                                          Exploring Microsoft Excel 2007


EXCEL WORKBOOK (*.xlsx). Use when there are no macros or VBA code.

EXCEL MACRO-ENABLED (*xlsm) Save your workbook as this file type when the
  workbook contains macros or VBA code.

EXCEL TEMPLATE (*.xltx). Save your workbook as this file type when you need a

EXCEL 97-EXCEL 2003 WORKBOOK (*.xls). Use when you need to share with some-
  one working in a previous version of Excel.

MICROSOFT EXCEL 5.0/95 WORKBOOK (*.xls). Use when you need to share with
  someone using Microsoft Excel 5.0. (while the earlier versions of Office share
  the same extension - .xls - they are not the same format.

EXCEL MACRO-ENABLED TEMPLATE (*.xltm). Use when you need a template and the
  workbook contains macros or VBA.

EXCEL BINARY WORKBOOK (*.xlsb). Save your workbook as this file type when you
  have an especially large workbook; this file type will open faster than a very
  large Excel Workbook will. You’ll still have the new Excel features with this file
  type, but not XML.

PDF – Microsoft Office has an add-in that allows you to save a file as a PDF docu-
  ment, allowing for cross-compatibility with virtually any operating system or
  office suite.

                                                                                       Page 2
                                              Exploring Microsoft Excel 2007


    Installed are from Microsoft

    My Templates are templates
      that you create and save

    Look for Recently used tem-
      plates for convenience

    Microsoft has templates avail-
      able to download from their
      website. Since our Internet
      connection is so fast, this
      takes no longer than open-
      ing a file saved on your computer or on our server.*

   New                 From Existing allows you to open an already
                      formatted document as a new template, ready
   to delete old data, add new data without having to recreate
   the formatting

*Some templates require validation—follow the prompts to validate. This means that if
you do not have a legally installed version of Office you will not be able to open these
                                                                                           Page 3
                                 Exploring Microsoft Excel 2007

When you start Excel, you
open a file called a work-

Each new workbook comes
with three worksheets.

The Worksheets are where
you enter data. They con-
tain Cells, Columns and

Each worksheet has a name on the sheet tab at the bottom left
of the workbook window.

To add additional worksheets click on the Insert Worksheet Icon,
click on the Home Tab > Cells > Insert > Insert Worksheet or
right-click on one of the worksheet tabs and select Insert

To delete extra worksheets
do the opposite, click on
the Home Tab > Cells > De-
lete Worksheet, or right-
click and select Delete.

To add or delete more than
one tab at a time, hold down control and click on the number of
tabs you want to add or the specific tabs you want to delete.

                                                                   Page 4
                                  Exploring Microsoft Excel 2007

Then right-click on one of the selected tabs and choose Insert or

Excel opens with Sheet1,
Sheet2, and Sheet3. Re-
name the sheet tabs to
make the information on
each sheet easier to iden-

N       ames might play a part in a formula. Keep them simple, and
        don’t use spaces If you need to separate words either make
the first letter capitalized or use an underscore (JanuaryBudget
or January_Budget)

Right-clicking on a tab gives you other options, too, including
adding pre-defined and moving or copying a worksheet to a dif-
                                             ferent place in this
                                             workbook, or to a
                                             different workbook.

                                             You can also make
                                             your worksheets eas-
                                             ier to visualize by
                                             changing the color of
                                             the tab.

                                                                     Page 5
                                         Exploring Microsoft Excel 2007

In the anatomy of the cell displayed above, you see that there are many dif-
ferent components to a cell.

The Cell Contents that area displayed in the
cell are called the value. The value of a cell
can be text, a date, or a number. What you
see in the cell is what is printed.

The value of a cell can also be the result of a
formula that adds, subtracts, or does other
mathematical calculation.

The contents of cell is shown in the formula
bar. Text will display as text,. A formula will
display in the formula bar as the formula. In
the cell you will see the result of the calcula-
tion, the value.

                                                                               Page 6
                                      Exploring Microsoft Excel 2007


  Columns go from top to bottom on the
  worksheet, vertically

  Rows go from left to right on the work-
  sheet, horizontally

  A cell is the place where one column and
  one row meet

  Columns and rows have headings:

  Each column has an alphabetical heading
  at the top.

  Each row has a numeric heading.

  The Cell Row is the identifier of the vertical position, extending across
  the entire range of the sheet from 1 to over 1,000,000!.

  The Cell Column is the identifier of the horizontal position, up and down
  the entire range of the sheet from A to XFD.

  The Cell Location is the coordinates of the cell that is currently the ac-
  tive cell (A3, or XFD10,333).

                                                                               Page 7
                                           Exploring Microsoft Excel 2007


Cells are where you enter data in a worksheet. A cell is a placeholder of data,
numbers, formulas, or nothing.

The alphabetical headings on the columns and
the numerical headings on the rows tell you
where you are in a worksheet when you click a

The active cell is the one with a bold black
frame around it.

When you open a new workbook, the first cell
in the upper-left corner of the worksheet you
see is outlined in black, indicating that any
data you enter will go there.

You can enter data wherever you like by clicking any cell in the worksheet
to select the cell.

When you select any cell, it becomes the active cell.

When a cell is active, it is outlined in black, and the headings for the column
                      and the row in which the cell is located are highlighted.

                                      The Column and Row letters/numbers are

                                      For example, if you select a cell in column
                                      C on row 5:

                                         Column C is highlighted.
                                         Row 5 is highlighted.

                                                                                    Page 8
                                         Exploring Microsoft Excel 2007

         This is named cell C5
The active cell is name is shown in the Name
Box in the upper-left corner of the worksheet..
This is the Cell Reference.

All of these indicators are not too important
when you’re right at the very top of the work-
sheet in the very first few cells. But when you
work further and further down or across the
worksheet, they become necessary.

It’s important to know the cell reference if
you need to tell someone where specific data is located in a worksheet.

Suppose, for example, that your mortgage payment, your bonus, or your
raise is located in cell E35 (or cell BF684).

But, mostly, cell references are what comprises formulas, calculations of
some kind involving numbers and math. Math that you do not have to use a
10-key to add yourself.


From the home tab, in the Number Group, click on
the pull-down arrow next to General. This list
shows the basic number formatting options. Cells
in a worksheet can have many different formats:

Formatting a cell is what defines it as text or num-
ber, or a few other options. If you are using your
data in a specific cell in a calculation, then it must
be formatted as a number.

                                                                            Page 9
                                        Exploring Microsoft Excel 2007

For more Cell Formatting options, click on the Dialog Box
Launcher, or lick on More Number Formats for further op-


                        To configure cells to format entered
                        data as currency select the cells you
                        wish to format and click the $ symbol
                        in the numbers group on the Office
                        Ribbon’s home tab.

The default format for this button is ACCOUNTING, which
aligns the $ to the left of the cell while the number is aligned to the right of
the same cell. If you select CURRENCY the $ is aligned tight against the num-

The decimal point is always two places for Currency or Accounting.

If a negative number is entered in a cell formatted
for accounting it will be displayed in parenthesis, if
entered in a cell formatted for currency, a minus
sign is appended to the front of the data.

                                           Exploring Microsoft Excel 2007

When entering data, it doesn’t ―set‖ it into the cell until you either press
Enter or Tab.
     Enter moves down one row
     Tab moves one cell to the right


To enter a date use a slash or a hy-
phen to separate the parts: 7/16/2008

Excel recognizes this as a date, and as-
sumes the current year, so just enter-
ing 7/16 will actually be entered as
7/16/2008 even if you are only view-
ing the format 16-July.

To change the date format go to the Home Tab > Number and click on the
Pull-down arrow by General.

TIP: To enter today’s date, press CTRL and the semicolon together. To en-
ter the current time, press CTRL and SHIFT and the semicolon all at once.

To enter a time, type the numbers, a space, and then ―a‖ or ―p‖ — for exam-
ple, 9:00 p. If you put in just the number, Excel recognizes a time and en-
ters it as PM.

                                         Exploring Microsoft Excel 2007

                             ENTERING NUMBERS
                             When entering numbers Excel defaults to view-
                             ing the number in the cell without the decimal.
                             To change the number format that is viewed in
                             the cell, go to the Home Tab > Number Group.

                             Change by the command buttons in the group,
                             or use the Dialog Box Launcher for more op-


   To enter fractions, leave a space between the whole
   number and the fraction. For example,
   1 1/8.

   To enter a fraction only, enter a zero first. For example, 0 1/4. If you en-
   ter 1/4 without the zero, or Excel will interpret the number as a date,
   January 4.

   Enter a negative number by enclosing it in parentheses. If you type
   (100), Excel will display the number as -100.

AutoFill. Enter the months of the year, the days of the
week, multiples of 2 or 3, or other data in a series. To
AutoFill, type n January, February. Click on the grab
handle and drag to the number of cells you want to fill.
Release the mouse to enter the series.

                                            Exploring Microsoft Excel 2007

AutoComplete. If the first few letters you type in a cell match an entry
you’ve already made in that column, Excel will fill in the remaining char-
acters for you. Just press ENTER when you see them added.

What happens when you have entered the data, but you need to change

You edit letters or numbers by selecting them and then typing something
different. There are two ways to select the cells for editing:

      Double-click a cell to edit the data in it.

      Click the cell, and then edit the data in the formula bar.

What’s the difference? Your convenience. You may find the formula bar,
or the cell itself, easier to work with.

Whatever you do, when you’re all through, remember to press ENTER or
TAB so that your changes stay in the cell.

Excel doesn’t work like it does in Word. When you copy or delete a cell’s
contents, it deletes just that. The formatting (bold, currency, number) re-

Excel assumes that when you once format a column to number or text,
that is what all content will be.

To remove formatting from a cell you must change it. You cannot delete

                                        Exploring Microsoft Excel 2007


The gray lines you see on a spread sheet do not print. They are called Grid-
lines. In order to print borders, you must format

You can do this a number of ways.

   Select the area you want bordered.

   Right-Click on the selection and choose Format
   Cells. Or,

   Right-Click on the selection and using the Mini-
                             Toolbar, click on the pull-down arrow to
                             quickly pick the border you need.

                             TIP: If you can’t remember how to do almost
                             anything, select it, point at it and Right-Click.
                             That will bring up a short-cut menu with most

                             And, TIP WITHIN A TIP—Many of the options
                             you need for most tasks in Windows and Of-
                             fice, that you need to do are going to be avail-
                             able by Right-Clicking.

                                           Exploring Microsoft Excel 2007

It isn’t enough to just enter data and write formulas. You have to do some-
thing with it, and that something usually in-
volves printing.

Printing in Excel is different than a Word
document where you just click on Print and
away it goes to the printer. In Excel you can
tell it what to print, and how to print.

Say you have a document that has data you
don’t want printer—columns or rows that might have data necessary for
calculations, but aren’t necessary on your pretty spreadsheet.

You can set the Print Area, telling Excel exactly what columns and rows you
want to be on your printed spreadsheet. The settings you need are on the
Page Layout Tab, in the Page Setup and Scale to Fit groups.

If you have too much data to fit on one page, Excel has a Scale to Fit option
that scales the data to fit on the page how you need it (say, 1 page by 1
                                 page), without you having to reformat the
                                 pages manually.

                                 Note that you can also use the Dialog Box
                                 Launcher to open the Page Setup Dialog Box
                                 for this and other options.

                                       Exploring Microsoft Excel 2007

         View > Workbook Views > Page Layout

Page Preview is a great way to see exactly what your page will look like when
it is printed. Looking at a spreadsheet is not going to show you that. All of
the nice gridlines (which do not, print, by the way) are great for showing
you each cell, but it gets in the way when you want to see just the data. Ex-
cel now provides a way to look at your spreadsheet in an entirely new way.

From here you can also add Headers and Footers. When you click in the
―Click to Add Header‖ area, another Tab will appear, Header and Footers

From here there are a whole world of options.

                                            NEXT: Learning Formulas

Description: Microsoft Xcel Templates document sample