Vba Excel Rename Worksheet

Document Sample
Vba Excel Rename Worksheet Powered By Docstoc
					                                                              Excel 2007


Table of Contents
Excel 2007 Lesson 1: Navigate the Excel Screen ........................................................................................... 2
Excel 2007 Lesson 2: Configure Excel to Suit Your Working Needs .............................................................. 6
Excel 2007 Lesson 3: Create Spreadsheets and Enter Data ........................................................................ 13
Excel 2007 Lesson 4: Format Worksheets .................................................................................................. 17
Excel 2007 Lesson 5: Add Graphics and Drawings to Worksheets ............................................................. 22
Excel 2007 Lesson 6: Perform Calculations with Functions ........................................................................ 26
Excel 2007 Lesson 7: Create Formulas to Perform Custom Calculations.................................................... 29
Excel 2007 Lesson 8: Organize Data with Excel Databases ......................................................................... 33
Excel 2007 Lesson 9: Analyze Data using PivotTables and PivotCharts ...................................................... 37
Excel 2007 Lesson 10: Solve Problems by Performing What-If Analysis .................................................... 42
Excel 2007 Lesson 11: Create Effective Charts to Present Data Visually .................................................... 46
Excel 2007 Lesson 12: Use Excel with the Other Office Applications ......................................................... 50
                                 Excel 2007 Lesson 1: Navigate the Excel Screen
                Objectives:
                1. Start Excel and Understand the Excel Screen
                2. Understand Worksheets and Workbooks; Open an Existing Workbook
                3. Navigate in Workbooks and Worksheets; Select Objects
                4. Get Help with Excel

                Excel is a spreadsheet application for organizing, calculating, summarizing and presenting data.
                Excel 2007 is significantly different from earlier versions, however, but similar to Word and
                PowerPoint 2007. When you start Excel, you'll see a new blank workbook containing three
                worksheets. See below for the main elements of the Excel Screen:
            Quick Access Toolbar
                                                                          Column Headings
Office Button


 Ribbon
                                                                                                                      Formula
   Reference                                                                                                          Bar
Area
 Select All

Row Headings




 Active Cell




Worksheet                                                                                                          View
Tabs                                                                                                               buttons,
                                                                                                                   Zoom



                There are still some task panes, for example, Clip Art, Research and the Clipboard, and you can
                undock and move them and/or close them when you are done working with them.
Understand Worksheets and Workbooks; Open an Existing Workbook
A worksheet is Excel's basic unit and is a grid in which you enter data. There are 16,384 columns and
1,048,576 rows in each worksheet (more than the 256 columns and 65,536 rows in earlier versions),
and the intersection of each row and columns is a cell – so there are now 17,179,869,184 cells
instead of only 16,777,216! Columns are letters A to Z then AA to AZ, BA to BZ and so on; then after
ZZ come AAA, until the last column XFD. Rows are numbered 1 to 1048576; thus the first cell
address is A1 and the last is XFD1048576. Excel saves worksheets in workbook files in the .xlsx file
format. Workbooks can contain one or more worksheets; by default, there are three worksheets
but a workbook can contain up to 255 worksheets. Worksheets are named Sheet 1, Sheet 2 and so
on, but you can change the name by right-clicking the worksheet tab and choosing rename. You
might want to put data on different sheets and then link the sheets using formulas – more to come.

You can open existing Excel files in a number of different ways: the Office button menu, the Open
dialog box, a Windows Explorer window or on the Desktop. If you click the Office button, you'll see
the most recently used files listed, and you can click the "pin" to keep that the file on the menu or
unpin it by clicking the pushed-in pin again. To change the number of files listed here, go to Excel
Options – Advanced category; in the display area, change the Show This Number of Recent
Documents list setting, OK. Using the Open dialog box, click the Office button and choose Open to
navigate to the folder that contains the workbook. The Open dialog box also enables you to open
the workshop as a read-only, copy or repair – just drop the Open arrow down. To open a workbook
from Windows Explorer (ex. in My Documents) or on your desktop, just double-click the file's icon –
this is probably the fastest way.

When you open a workbook created in an earlier version of Excel, Excel displays the words
"[Compatibility Mode]" in the title bar after the workbook's name to remind you that the workshop
isn't in Excel's preferred .xlsx format. To update the workbook to 2007, click the office button and
choose Convert. Select the Do not ask me again about converting workbooks check box (unless you
want to see it again), OK. You will be prompted to close and reopen the workbook in order to use
the new features; click Yes to close/reopen or No to continue working in the workbook.


Navigate in Workbooks and Worksheets; Select Objects
You can navigate a worksheet using the mouse and/or the keyboard. Click the worksheet's tab (use
the scroll buttons to make the tab appear if you have many worksheets in the workbook. Here are
some keyboard shortcuts for navigating worksheets: Ctrl+PageDown (move to next worksheet),
Ctrl+PageUp (move to previous worksheet), Ctrl+Shift+PageDown (select current and next
worksheets), Ctrl+Shift+PageUp (select current and previous worksheets).

Click a cell to access it, and use the horizontal and vertical scroll bars to scroll to different areas of
the worksheet. You can also use the arrow keys (up, down, left, right), Home (first cell in row),
Ctrl+Home (first cell used in worksheet), Ctrl+End (last cell used in worksheet), Page Down (one
screen), Page Up (one screen), Alt+Page Down (right one screen), Alt+Page Up (left one screen),
Ctrl+Backspace (scroll workbook to display active cell). You can also navigate to a specific cell by
typing the cell name (upper or lower case) in the Name box then press Enter.

You can select cells and ranges of cells, that is, contiguous (ex. C3:E5) or noncontiguous cells (ex. B3,
B5, B7, B9) to work with them. To select a cell, click it or use the arrows keys to move the active cell
outline to it. Or you can click the corner cell and drag in any direction to select contiguous (a block)
of cells. For multiple cell selections (noncontiguous), make your first selection then hold down the
Ctrl key. To select all the cells in the active sheet, click the Select All button – that's the unmarked
button at the intersection of column and row headings.

You can assign a name to a range so it's easier to Go To, apply formatting, or use in calculations;
follow these steps:
                                                                           S
         elect the cell range, click the
         Formulas tab, Define Names
         group; click Define Name
         button for dialog box
                                                                           T
         ype the name in the Name
         textbox (No Spaces); in
         Scope drop-down, choose
         the scope – workbook is the
         default and the best choice.
                                                                           V
         erify that Refers to is the
         range of cells you want; if
         not, click Collapse dialog
         box to re-select the cells in
         the worksheet, the click
         Collapse Dialog box button again; click OK
                                                                           To delete a range name
         from a workbook, click the Formulas tab, Defined Names group then click Name Manager
         button for dialog box; select the name in the list box, click Delete button and get
         confirmation; OK and Close.
Note that you can also use Go To or Go To Special in Find & Select on the Home ribbon to select
ranges, too.

To select worksheets in a workbook, click the worksheet's tab, Shift+click another worksheet's tab to
                                         select all the worksheets between the currently selected
                                         one and the one you clicked. Ctrl+click another
                                         worksheet's tab to add that worksheet to the selection or
                                         Ctrl+click a selected worksheet's tab to remove it from the
                                         selection. When multiple worksheets are selected, Excel
                                         displays [Group] in the title bar to remind you. Press
                                         Ctrl+Shift+Page Down to select the current and next
                                         worksheets (can extend select by pressing again); press
                                         Ctrl+Shift+Page Up to select current and previous
                                         worksheets (can extend by pressing again).

                                           Get Help with Excel
                                           Press F1 or click the MS Office Excel Help icon at the right
                                           end of the Ribbon (question mark icon). See what topics
                                           are available and click the links in the Browse Excel Help
                                           list. You can also click in the Search box and type one or
more keywords then click Search button (online or offline or both). Click Keep On top button if you
want the Help window to appear on top of other windows; Close when you are finished.
   Excel 2007 Lesson 2: Configure Excel to Suit Your Working Needs
Objectives:
1. Improve Your View with Splits, Extra Windows
2. Set Options to Make Excel Easier to Use
3. Load and Unload Add-Ins
4. Configure AutoCorrect to Save Time and Effort

Improve Your View with Splits, Extra Windows, Hiding, Zooming, and Freezing
You can split a worksheet window into two or four panes so you can see separate parts of the
worksheet at once. The easiest way to apply a two-pane split is to grad the horizontal split box or
the vertical split box to where you want the split to be. To split the window into four panes at once,
position the active cell in the row above and the column to the left of which you want to split the
window. Then choose View – Window – Split to split the window both ways. Drag the horizontal or
vertical split bar to adjust or drag where they cross for both split bars at once. Excel shows shaded
lines to indicate where the spilt will fall when you release the mouse. To remove the split, double-
click the split bar or drag it out of the worksheet window. To remove all splitting, double-click the
split bars where they cross or choose View – Window – Split.




To open two or more windows containing the same worksheet or workbook, choose View –
Window – New Window. Excel names extra windows containing the same workbook by adding a
colon and a number after the filename. You can easily switch from window to window by clicking in
the target window or by choosing View – Window- Switch Windows then selecting the window from
the drop-down menu. You can also Zoom each window independently – more later.

You can arrange workbook windows by using standard techniques to resize and position windows:
maximize, restore down, minimize or drag edges/corners of non-maximized windows to resize. Drag
windows by the title bar to position where you want them, too. To arrange non-minimized
windows, choose View – Window – Arrange All for the dialog box. Select tiled, horizontal, vertical or
cascade; check Windows of active workbook (or not), OK. When all the open windows are
minimized, you can choose View – Window – Arrange All.

Use the Arrange Windows dialog box to position two windows
alongside each other to compare their contents – and you can
synchronize scrolling or not. Activate one of the windows, choose
View – Window – View Side By Side
If you have only two windows open, Excel knows what to do; however,
if you have more than two windows open, Excel displays the Compare
Side By Side dialog box with the list from which you select the second window, OK. At this point,
you can click synchronous scrolling (just below View Side By Side button); when you’ve finished
comparing windows, click View Side By Side to toggle off.

You can Hide and Unhide a window, too: View – Window – Hide/Unhide – the latter gives you a
dialog box to select the window you want to redisplay, OK.

To Zoom in and out from 10% – 400% in increments of 10%, click the Zoom In (+) or Zoom Out (-)
buttons on the Zoom slider in the right end of the status bar, or you can drag it. To zoom to the
largest size at which a particular range of cells fits in the window, select the range then choose View
– Zoom – Zoom to Selection. You can also zoom to a preset or custom percentage: View –Zoom –
Zoom for the dialog box, select option that you want select Custom and enter the exact percentage
in the textbox, OK.

If your worksheet contains more data than will fit on your screen, you’ll often scroll up or down,
back or forth to refer to labels and headings. To reduce scrolling, you can freeze specific rows and
columns so Excel keeps displaying them even though other rows and columns scroll. To freeze just
the top row in the window, choose View – Window – Freeze Panes – Freeze Top Row. To freeze just
the leftmost column, choose View – Window – Freeze Panes = Freeze First Column. These are new
commands for Excel 2007! To freeze rows and columns, select the cell to the right of the column
and below the row you want to freeze then choose View – Window – Freeze Panes – Freeze Panes;
Excel displays a heavier line along the gridlines to show where the frozen section is, and the frozen
section remains in place when you scroll the rest of the worksheet. To remove freezing, choose
View – Window – Freeze Panes – Unfreeze Panes.
    Set Options to Make Excel Easier to Use
                                                                     Click Office Button – Excel
                                                                     Options to make changes to
                                                                     Excel’s look and feel. The top
                                                                     options (Popular) are the
                                                                     following:
                                                                     1. Show Mini Toolbar on
                                                                     selection (yes)
                                                                     2. Enable Live Preview (yes)
                                                                     3. Show Developer Tab in the
                                                                     Ribbon (for VBA, creating
                                                                     macros – more in second
                                                                     semester)
                                                                     4. Color Scheme
                                                                     5. Screen Tip style
                                                                     6. Create lists for use in sorts
                                                                     and fill sequence (lesson 3)

                                                                     When creating new workbooks,
                                                                     you can define the following:
                                                                     1. Use this font
                                                                     2. Font size
                                                                     3. Default view for new sheets
                                                                     4. Include this many sheets

Your username shows, and you can also set your primary language preference for editing.

Click the Formulas category to
control how Excel performs
calculations, displays and uses
formulas and check for errors.
Calculations:
The default is Automatic, and
unless you have very complex
worksheets, this is the best.
Working with Formulas:
1. R1C1 reference style (most
use column/row rather than
Row/Column so uncheck)
2. Formula AutoComplete
3. Use table names in formulas
4. Use GetPivotData functions
for PivtoTable references
(more on Pivot Tables later)

More on Error Checking in
lesson 8
Spelling Options in Proofing




When correcting spelling in MS Office programs:
1. Ignore Words in UPPERCASE (yes)
2. Ignore Words that contain numbers (yes)
3. Ignore Internet and file addresses (yes)
4. Flag repeated words (yes)
5. Enforce accented uppercase in French
6. Suggest from main dictionary only


Choose Save Options
Save workbooks:
1. Save files in this format (drop down list)
2. Save AutoRecover information every X minutes (default is 10)
3. AutoRecover file location
4. Default file location (My Documents)

AutoRecover Exceptions for option – this is only really for workbooks that are so large that recovery
them would take a disruptively long time.
Advanced
Editing Options:
     After pressing Enter,
        move selection
     Automatically insert a
        decimal point (no)
     Enable fill handle and
        cell drag-and-drop
     Allow editing directly
        in cells
     Extend data range
        formats and formulas
     Enable automatic
        percent entry
     Enable AutoComplete
        for cell values
     Zoom on roll with
        IntelliMouse (no)
     Alert the user when a
        potential time
        consuming operation
        occurs
     Use system separators (checked by default)
Cut, copy, and paste (yes to all):
     Show Paste Options
        buttons
     Show Insert Options
        buttons
     Cut, copy, and sort
        inserted objects with
        their parent cells
Display Options:
     Number of document in
        the Recent Documents
        list
     Ruler units
     Show all windows in the
        taskbar
     Show formula bar
     Show function screen
        tips
     Show chart element
        names on hover
     Show data point values
        on hover
     For cells with comments,
        show (choose Indicators
        only, and comments on hover)
Display Options for this workbook (choose all of these):
     Show horizontal scroll bar
     Show vertical scroll bar
     Show sheet tabs
     Group dates in the AutoFilter menu
     For objects, show (choose All)
Display Options for this worksheet:
     Show row and column headers (yes)
     Show formulas in cells instead of their calculated results (no)
     Show page breaks (can
        be useful or distracting)
     Show a zero in cells that
        have zero value (yes)
     Show outline symbols if
        an outline is applies
        (yes)
     Show gridlines (yes)
     Gridline color

Formulas: Enable multi-
threaded calculations (yes, use
defaults)

When calculating this
workbook:
    Update links to other
      documents (yes)
    Set precision as
      displayed (no)
    Use 1904 date system
      (no unless for Mac)
    Save external link
      values (yes)

General: only check Scale content for A4 or 8.5x11” paper sizes
Load and Unload Add-Ins
Once load add-ins when you need them (ex. Solver Add-in) since they take memory
and slow down your computer – so unload them when you are finished. To load or
unload an add-in, click the Office button – Excel Options – Add-Ins category; click the
Manage drop-down and select Excel Add-Ins, then click the Go button for the dialog
box (see right). Select the checkbox for each add-in that you want to load; clear
checkbox for any loaded add-in that you want to unload. Browse for an add-in that
doesn’t appear in the list, OK. The add-in features usually are implemented as extra
groups or tabs on the Ribbon.

Configure AutoCorrect to Save Time and Effort
To open the AutoCorrect dialog box, click the Office button – Excel Options, Proofing
category. Click the AutoCorrect Options button. In AutoCorrect, check the settings:
                                            Show AutoCorrect options buttons
                                            Correct Two Initial Capitals
                                            Capitalize first letter of sentences
                                            Capitalize names of days
                                            Correct accidental use of caPS LOCK key
                                            Replace text as you type
                                      You can create and delete AutoCorrect entries in this screen, too.

                                       In AutoFormat As You Type (all checked by default):
                                            Internet and network paths with hyperlinks
                                            Include new rows and columns in table
                                            Fill formulas in tables to create calculated columns
                                       In Smart Tags: Not necessary to use any of these features.
         Excel 2007 Lesson 3: Create Spreadsheets and Enter Data
Objectives:
1. Create a Workbook
2. Save a Workbook
3. Create and Save a Template
4. Enter Data in Worksheets
5. Use AutoFill to Enter Data Series Quickly
6. Use Find and Replace
7. Recover Your Work in Excel Crashes

Create a New Workbook
Each workbook is based on a
template – either Excel's default
"blank workbook" with basic
settings or more complex designs.
To create a new blank workbook in
Excel, press Ctrl+N or Office Button
– New for the dialog box; see the
templates panel on the left and
Blank and recent in the middle then
select the one you want and click
Create. If you want to create a new
workbook based on a previously
created one select New from
existing in the New Workbook dialog
box (navigate to that file, select it
and click Create New button). You
can also open the original file and
Save As to save a copy under a different name or create a template – more to come.

                                                             To create a new workbook based on
                                                             a template, click the Office button –
                                                             New for the dialog box and click My
                                                             Templates (for your own templates)
                                                             or Installed Templates; select the
                                                             icon for the template (see preview),
                                                             OK or Create. There are also
                                                             templates online that you can
                                                             download from Microsoft. I've never
                                                             been able to get this feature to work,
                                                             however, here's the site for all MS
                                                             Office templates:
                                                             http://office.microsoft.com/en-
                                                             us/templates/default.aspx?ofcresset
                                                             =1
 Scroll down to the Search (by keyword) and Product (ex. Excel).
Save a Workbook
It's a good idea to save your work as soon as you've made changes that
you wouldn't want to lose. When you save a file for the first time, give
the file a name, choose which folder you want to save it in, and choose
the appropriate format (Save As Type drop down). After that, you can
use the Save command (Ctrl+S or F12) or click the Save button on the
Quick Access toolbar or Office button – Save. It's useful to make a copy
of an Excel file by saving under a different name in case you overwrite
the original file with the changes that you've made – and do a Save As
under a different name for Read only files, too. If you are working in
2007 but need to share a file with someone who has 2003, save the
workbook in 97-2003 format.

It's often helpful to enter information about the file in Properties: go to
Office button – Prepare – Properties (see below). For further properties,
click the Document Properties drop down for the dialog box (see right).
The Summary tab has the most helpful areas for identifying a file. Click the X in the right corner of
document properties to close.




Create Your Own Templates
A template is the same as a workbook file except that it uses the .xltx extension instead of .xlsx. You
can base a new workbook on an existing one, so there really isn't any need to create a template
except that it's cleaner and easier than using an existing file and having to replace and change things
– lots of chance for mistakes. Excel comes with application templates and stores the ones that you
create or download. All you have to do to create a template from a file is click the Office button –
Save As for the dialog box, then drop the Save As Type arrow down and select the Excel Template
entry. By default, Excel changes the location to your Templates folder. Type a name in the File
Name textbox; if you want to save a preview picture of the template, select the Save Thumbnail
checkbox then click Save.

Enter Data in Worksheets
There are three ways to enter data in worksheets: by typing the data in
manually, buy using drag and drop to move(four-way arrows) or copy
(Ctrl+drag and drop) existing data and by pasting existing copied data. To
enter data manually, select a cell, type the entry and press Enter or click
Enter on the formula bar (see right). Pressing Enter moves the active cell to the next cell in the
specified direction as set in Excel Options – Advanced category (default is down). You can also click
into a cell, use the arrow keys to navigate, or tab to move right, shift+tab to move left or Shift+Enter
to move the opposition way from Enter. When entering data, you can either work in the cell itself
or in the Formula bar – that's easiest when you are editing a cell.

Tips: To enter the same data in each cell in a range, select the range, type the entry then press
Ctrl+Enter. To enter data in multiple worksheets at the same time, Ctrl-click the worksheet tabs to
select then enter the data.

To delete existing content of a cell or range, select then press Delete – and to replace, just
overwrite. Double-click the cell to edit or move to the cell and press F2 – or click into the Formula
bar. Once you are editing the contents of a cell, use the left and right arrow keys to move the
insertion point then press or click Enter or tab to move to the next cell. Press ESC or click Cancel
button on the Formula bar to cancel changes you've made. You can also press Ctrl+Z or click Undo
on the Quick Access toolbar. Click the drop-down button to undo multiple actions. Press Ctrl+Y to
Redo, or click the Redo button on the Quick Access toolbar – multiple actions available, too!

Tip: If drag and drop doesn't work, check Excel Options – Advanced category and select Enable Fill
handle and Cell Drag-And-Drop, OK.

You can also enter data with copy (flashing border) and paste – Excel
will keep the copied select available to paste again until his press ESC
or edit a cell. Use the Paste Smart Tag to display a menu of paste
options: keep source formatting, use destination theme, match
destination formatting, values and number formatting, keep source
column widths, formatting only, link cells. You can also use Paste
Special dialog box (click Paste drop down in Clipboard group of Home
ribbon) for more options (see right).

Excel also lets you link data across worksheets or even across
workbooks. Open the source and destination workbooks, or if you
are linking one sheet to another in the same workbook, just open
that workbook. In the source workbook copy the relevant cell or
range then display the destination sheet and click the Paste drop
down to select Past Link. Excel updates links with the same workbook automatically and
immediately when you change the data in the source. If the link is between different workbooks
and the source workbook isn't open but contains changes, Excel will prompt you (by default) to
update automatic links when you open the destination workbook. If you want link updates to
happen without prompting, clear the Ask To Update Automatic Links checkbox in the General
section of the Advanced category in Excel Options.

Use AutoFill to Enter Data Series Quickly
Select one, two or more cells that contain the basis for a series then drag the AutoFill handle – that's
the black square at the lower right corner of the last cell selected. AutoFill analyzes the starting
cells, determines what the contents of the other cells should be and enter the information
automatically. Try using AutoFill with the months of the year: type January in A1 and AutoFill to D1;
type 0 in A2 and 5 in A3 then select those cells and drag the AutoFill handle down – see the
                                                               sequence of numbers (Excel adds 5 each
                                                               time)? AutoFill even works for multiple
                                                               series of data as long as the series is in a
                                                               separate column or row (ex. column A has
                                                               months, column B has numbers – select
                                                               both and drag the AutoFill handle to
                                                               extend both series at the same time.)
                                                               Type Monday ia cell B2 then press Ctrl+B
                                                               to bold it; right-drag the AutoFill handle
across to H2 and release. The AutoFill options Smart Tag displays a context menu for options (see
left).
You can create your own AutoFill lists: click the Office button –
Excel Options, Popular category; click the Edit Custom Lists
button for the dialog box. Select NEW LIST in Custom lists then
type your list in the List entries textbox (one to a line) and click
Add. You can also delete a list by selecting it and clicking
Delete. Click OK when you are done adding and/or deleting
your custom lists.


                               Use Find and Replace
                               To find items, choose Home –
                               Editing – Find & Select or press
                               Ctrl+F (Ctrl+H for replace) for
                               the dialog box. This basically functions much the same way as the
                               other Office applications except for the Options (see left). The Format
                               button lets you search for or replace specific types of formatting that
                               you define using the Format Cells dialog box.

Recover Your Work If Excel Crashes
After you save a file for the first time, by default, AutoRecover saves every 10 minutes. You can
change this interval in Excel Options – Save category. Use the Task Manager (right-click the
notification area or open space on the Taskbar and choose Task Manager) to see if the program is
hang up (no responding). End Task then restart Excel; you will probably lose changes since the last
save or auto-save but check the file(s) listed in the Document Recovery task pane. Hover your
mouse over each entry to display a drop-down for the menu for options: Open, Save As, Delete
(AutoRecover versions only), Show Repairs. Once you have decided which recovered file to keep,
Save As with a different name than the original file – in case the recovered file has problems that the
original file does not. Close the Document Recovery task pane; if there are more recovered files
displayed, you will be prompted to make a decision about them: view the next time you start Excel
or remove, OK.
                   Excel 2007 Lesson 4: Format Worksheets
Objectives:
1. Add, Delete and Manipulate Worksheets
2. Format Cell and Ranges

Add, Delete and Manipulate Worksheets
To add a worksheet, just click the Insert Worksheet button that appears after the last worksheet tab
and drag it to whatever position you wish. Alternatively, you can right-click a worksheet tab and
choose Insert for the dialog box; select Worksheet on the General tab, OK. If you want to use the
Ribbon, select a worksheet tab and go to Home – Cells – Insert – Sheet.




To delete a sheet, right-click its tab and choose Delete or select it and go to Home – Cells – Delete –
Sheet. If the sheet contains data, you will be prompted to Delete or Cancel. You can hide a
worksheet by right-clicking the tab, too, and to unhide, right-click any worksheet tab and choose
Unhide for the dialog box. Select the sheet, OK.

Right-click a worksheet tab to Move or Copy; Rename or Tab Color are also choices – press Enter to
click away when you are done.


Format Cells and Ranges
A cell can have one of four values: text, numerical, formula or blank – and all can be formatted in a
variety of ways, such as, alignment, background color and gridlines. The most basic formatting
controls the way in which Excel displays the data of the cell. For example, Excel usually displays the
literal contents of a cell by default except with formulas when the result of the formula is usually
displayed. Make the cell active and see what it actually contains by looking in the Formula bar;
when you edit, both the cell and the Formula bar display the literal contents of the cell. Also, Excel
may change the contents of a cell for display purposes, for instance, rounding a long number to fit
into a cell – but the underlying long number remains unaffected.

There are two Mini Toolbars available in Excel: the smaller one displays when you select text, and
the larger one displays when you right-click a cell or selection. Note the additional features of
Excel's larger Mini toolbar: Accounting Number Format ($), Percent Style (%),
Comma Style (,), Merge and Center, Decrease and Increase Decimal, Fill Color
and Borders.

The Home Ribbon contains many formatting options in the Font, Alignment and Number groups:
The Format Cells dialog box includes the full range of cell
formatting on six tabs; to access press Ctrl+1 or right-click a cell or
selection and choose Format Cells or click the dialog box launcher
for either Font, Alignment or Number groups on the Home tab of
the Ribbon. If you prefer to work with the Ribbon minimized, you
can put the commands you need on the Quick Access toolbar.

    To make Excel display the contents of a cell in the way you
    intend, apply the appropriate number format. You can apply
    manually, but Excel also applies number formats automatically
    when you enter text that matches one of Excel's triggers.

    The Number group on the Home tab of the Ribbon contains
    button for the most widely used number formats and a drop-down list that lets you access the other
    number formats (and you can also apply number formats from the Number tab of the Format Cells
    dialog box (see above). There are 12 categories of built-in formats:
        1. General Number Format is the default format for all cells on a new worksheet; displays up
            to 11 digits per cell and doesn't use thousands separators.
        2. Number Formats let you specify the number of decimal places to display (0-30 with 2 as
            default) whether to display a thousands separator, and how to represent negative numbers.
        3. Currency Formats (ex. $) let you specify the number of decimal places to display (like
            Number), which currency symbol to display (if any) and how to represent negative numbers.
        4. Accounting Formats let you specify the number of decimal places to display and which
            currency symbol to display (if any). The currency symbol appears flush left with the cell
            border, separated from the figures. Accounting formats represent negative numbers with
            parentheses around them. You can apply this format by clicking the Currency Style button.
        5. Date Formats offer a variety of date formats based on the locale you choose. It's easiest to
            use Excel conventional formats; Excel will automatically convert to dates any entry that
            contains a hyphen (-) or forward slash (/). If you don't specify the year, Excel assumes that
            you mean the current year.
        6. Time Formats offer a variety of time formats based on 12-hour and 24-hour clocks. You can
            make Excel automatically format an entry with a time format by entering a number that
            contains a color (ex. 12:00) or a number followed by a space and an uppercase or lowercase
            a or p (ex. 1 P or 11 a).
        7. Percentage Format displays the value in the cell with a percent sign and with your choice of
            number of decimal places (default is 2). You can make Excel automatically format an entry
            with the Percent format by entering a percent sign after the number.
        8. Fraction Format: Excel stores fractions as their decimal equivalents (ex. ¼ as 0.25) so make
            sure that you specify Fraction format if that's what you want. Also, be careful not to enter
            fractions in a way that Excel will confuse for dates.
        9. Scientific format displays numbers in an exponential form; you can make Excel apply this
            format by entering a number that contains an e in any position except the ends.
        10. Text format is for values that you want to force Excel to treat as text so as to avoid having
            Excel automatically apply another format (ex. entry that starts with 0). Excel left-aligns text-
            formatted entries, and you can make Excel format a numeric entry with Text format by
            entering a space before the number.
        11. Special formats provide a locale-specific range of formatting choices, ex. zip code, phone
            number, social security number. Once formatted, Excel will automatically apply the
        formatting, ex. if you type 987654321 in a cell with Social Security Number format, Excel will
        display 987-65-4321.
    12. Custom Format enables you to define your own formats for needs that none of the built-in
        formats cover.

There are several options for applying formatting to worksheets to make them more readable:
      Apply a Theme: a suite of formatting applied to an entire workbook rather than to individual
        worksheets, ranges or cells. To change the "look and feel", check out the 12 colors (4 for
        text and backgrounds, 6 accent colors for graphics, and 2 for hyperlinks), 2 fonts (heading
        and body), and graphical effects (ex. for AutoShapes). See the Themes group on the Page
        Layout tab of the Ribbon. Hover your mouse over the panels for live previews. You can
        create new theme colors and new theme fonts (see buttons at bottom of panels).
      Font Formatting (Home tab of Ribbon or on Mini Toolbars) allows you to quickly change
        fonts, font size, font style (regular, bold, italic, bold italic), underline, and color. For more
        options, see the Font tab of the Format Cells dialog box.
      Text Alignment (horizontal, vertical, indentation) and
        Orientation can be adjusted on the Alignment tab of the
        Format Cells dialog box. Text control (wrap text, shrink to
        fit and merge cells) are also useful, but be careful of
        Shrink to Fit since it can resize the display of some cells to
        make contents fit the column but leaves other cells at full
        size – better to resize columns or fonts.
      Border Formatting on the Mini toolbar and Font panel of
        the Home tab let you quickly apply standard borders. For
        more options including many choices of border weights
        and colors, work on the Border tab of the Format Cells
        dialog box.
Tip: Use Format Painter to copy formatting from one range to
another.
      Fill Formatting lets you apply solid shades of color or
        colored patterns to add emphasis or create a design. Use
        the Fill tab on the Format Cells dialog box.
      Protection Formatting enables you to lock or hide particular cells.

In most worksheets, you need to change some columns from their standard widths to widths
better suited to the data entered in their cells – and similarly for row height. The fastest way
to change width of column or height of row is by using Excel's AutoFit feature which resizes a
column to just wider than its widest entry and resizes a row to just high enough for its tallest
character or object. Double-click the right border bar of a column header or bottom border
bar of a row header, or you can select the column or widest cell and choose
Home – Cells – Format – AutoFit Columns Width (or Row Height). You can
also change column width and row height manually by dragging the
appropriate column or row border bars. Excel displays a ScreenTip showing
the size to which you've currently dragged. Use the Cells group Format
drop down (see right) to hide or unhide columns or rows – or you can just
right-click the column or row headings.
Excel also has Conditional Formatting, that is, formatting that Excel uses
    only when specified conditions are met. You can use this formatting to draw attention to missing
    data, highlight values that are atypical or to pick out the top 10 or bottom 10 scores in a list. Select
    the item (range of cells, worksheet, table) you want to affect by making it active, then choose Home
    – Styles – Conditional Formatting to display the panel. Choose the category you want (ex.
    Top/Bottom Rules) then select the item you want (ex. Top 10 Items) for the dialog box. Use the
    controls in the dialog box to specify the condition and how to format the cells that meet it, OK.

                                             You can also create a rule (click New Rule for dialog box):
                                             select the item you want to edit, use the controls to change
                                             the rule (change colors, types, values of cells) – then repeat
                                             until you have defined the rule as necessary, OK.

                                             If you apply multiple rules to the same cells, you might be
                                             confused and need to find out which conditional formatting
                                             rules have been applied. Select the cells you want to affect
                                             then choose Home – Styles – Conditional Formatting –
                                             Manage Rules. Examine the rules in the main list box and
                                             change them as needed: change order, delete, edit, create
                                             new rule, etc., OK.
                                             You can also clear Conditional Formatting Rules from a range
                                             or worksheet by choosing that option on the Conditional
Formatting drop down.

Excel also offers preset formatting options for tables: go to the Format As Table drop-down panel in the
Styles group on the Home tab – more on table formatting in lesson 8.

Like Word, Excel workbooks and templates
include many built-in styles that you can use to
apply predefined sets of formatting quickly and
easily. And you can modify the styles and create
styles of you own, even copy styles from one
workbook to template to another. In the Cell
Styles panel, there are
      Good, Bad, Neutral (conditional format)
      Data and Model
      Titles and Headings
      Themed Cell Styles (for emphasis)
      Number Format (5 number styles as
         discussed earlier in this lesson)
To create a style, apply formatting to a cell then select that cell and click
the Cell Styles button and choose New Cell Style for the dialog box. Type
the name of the new style then clear any check boxes you don't want to
include in the style. Click the Format button and work in the Format Cells
dialog box then click Ok, OK. To modify the style, display the Styles panel
again and right-click your style and choose Modify to display the Style
dialog box again. If you no longer need a style, you can delete it – just
display the Cell Styles panel and right-click the style – Delete. You can
delete any style except Normal – that's the default that Excel protects.

To merge styles from one workbook to another, open the source
workbook or template and the destination workbook or template. Activate the destination
workbook (click in it) then choose Home – Styles – Cell Styles – Merge Styles for the dialog box.
Select the source workbook and click OK. Excel closes the Merge Styles dialog box and merges the
styles into the destination workbook.
        Excel 2007 Lesson 5: Add Graphics and Drawings to Worksheets
    Objectives:
    1. Understand How Excel Handles Graphical Objects
    2. Insert Clip Art, Photographs, Movies and Sounds in Worksheets
    3. Work with Shapes, AutoShapes and WordArt
    4. Add Pictures to Worksheets
    5. Add SmartArt to Worksheets


Understand How Excel Handles Graphical Objects
To give worksheets more visual impact or to make them more comprehensible, you often need to add
pictures, shapes, diagrams or other objects. Excel worksheets appear to be flat but essentially have two
layers: text (containing cells contents) and drawing. The layers are transparent until you start to work
with them, and then you can change the order or group objects. On the drawing layer, you can create
as many sub-layers as you wish and arrange them separately or overlapping. Remember that when you
work with graphics, contextual tab of the Ribbon will display with tools to help you design and format
objects.

Insert Clip Art, Photographs, Movies and Sounds in Worksheets
To insert clip art, photographs, movies and sounds to worksheets, try to use restraint since these
objects, especially clip art, can be clichés. Select the cell at whose upper-left corner you want to position
the upper-left corner of the item (you can move the object later.) Choose Insert – Illustrations – Clip Art
to display the task pane. Use the Search For box, Search In drop-down list and Results Should Be drop-
down list then click Go. View the thumbnail matches and click the drop-down button of the one you
want. Choose one of the actions: Insert, Copy are the more common choices. You can also organize
your clips (adding clips and entering/editing keywords for search) by clicking that feature at the bottom
of the task pane.

Work with Shapes, AutoShapes and WordArt
Excel provides two types of tools for creating drawing objects: Shapes (simple and complex) and
WordArt (pictures made by applying effects to text). To add shapes, choose Insert – Illustrations –
Shapes to display the panel. Click the one you want (mouse pointer changes to crosshair) and draw
(click and drag) on the worksheet starting at one corner to the size that you wish then release the
mouse. If you want to center the shape, hold down the Ctrl as you click and drag; to constrain it (ex.
rectangle as square or ellipse as circle), hold down the Shift key – or hold down both to both features!
To create multiple shapes of the same type, right-click the tool then choose Lock Drawing Mode; when
you release the mouse after creating a shape, the tool remains active so you can create another shape
of the same type. Press ESC to toggle the tool off or click another tool.

WordArt is an Office applet for creating text-based designs such as logos or decorations. Choose Insert
– Text – WordArt to display the panel; type the text you want and resize by dragging one of the corner
handles. To slant the text, drag the pink slant handle to the left or right and stop when you get the
effect you want. To rotate, move the mouse pointer over the green rotation handle then drag left for
counterclockwise or right for clockwise. Try this: to format some of the text with a different style, select
that text by dragging through it then click Quick Style button on the Format tab of the Drawing Tool
section of the Ribbon.
Choose the style in the
Applies To Selected Text
area. To apply a fill, outline
or effect to the text, click
the appropriate buttons:
Text Fill, Text Outline or
Text Effects. If you want to
adjust further, click the
Format Text Effects button
(dialog box launcher for
WordArt Styles – see right).

To add text to a shape,
right-click the shapes and
choose Edit Text (insertion
point in shape). Type the
text in the AutoShape and
apply formatting (lesson 4).
Click elsewhere or right-click
and choose Exit Edit Text when you are done.

You can format a selected drawing object by using the controls on the Format tab of the Drawing Tools
section of the Ribbon or by using the Format dialog box for the shape – generally, the Format tab is
better for makes major changes while the dialog box is best for fine adjustments. To display the dialog
box, right-click the object and click the Format command (for pictures, shape, etc.)

The best way to start is to give the drawing object a style then adjust fill, outline and effects. Select the
drawing object (Drawing Tools tab of the Ribbon displays) and select the Format tab. Click the Shape
Styles drop-down button and choose a style either from the panel or from Other Theme Fills panel. In
the Shape Styles group, use the Shape Fill, Shape Outline and/or Shape Effects buttons for further
formatting. You can also apply WordArt style to any text inside the shape by selecting the style from the
WordArt Styles group.

Resize a drawing object by dragging the sizing handles, selecting the object and using the Height and
Width control in the Size group on the Format tab or click the Size dialog box launcher. Work on the Size
tab of the dialog box, and remember to Lock Aspect Ratio so object's height and width changes
correspond rather than distort. Crop From and Reset button will only work for objects such as pictures.
When you position an object in a worksheet, Excel positions it relate to cells; so if you move or resize
cells, Excel moves or resizes the object to match. If you want to prevent this, use the Properties tab of
the dialog box to set Object positioning, Print Object (or not), Lock (or not) and/or Lock text (or not).
There is also an Alt (alternative) Text tab to specify what will be displayed while a web browser is
loading the picture.

To position drawing objects, drag, nudge (use arrow keys), or use the Size and Properties dialog; use
Picture Tools – Format – Arrange to set alignment (snap to grid, snap to shape, view gridlines). To align
an object relative to another, select the object then hold down the Shift key and click to select the other
objects. Choose Picture Tools – Format – Arrange – Align for center, middle, horizontal, vertical, etc.
You can also group objects by Shift or Ctrl clicking then choosing the Picture Tools – Format – Arrange –
Group command or right-click one of the objects and choose Group – Group. Similarly, you can ungroup
or regroup.
To adjust the layer order in which drawing objects appear, click the object that you want to affect then
click the Format tab of the Drawing Tools section or Picture Tools tab of the Ribbon. In the Arrange
group, choose the action you want, for example, bring to front, bring forward, send to back, send
backward. You can also right-click an object for these commands. It might be hard to select objects that
are partly or wholly overlaid by others, so click the Selection Pane button in the Arrange group then click
the object in the Selection And Visibility pane and use the Reorder buttons to move an object up or
down the stack of objects or clear the box next to the object to hide it temporarily – and you can also
Hide All then Show All.

                                   Remember that you can wrap text in a cell, but increasing the depth
                                   of a cell increases the entire row. A textbox might give you more
                                   flexibility. Choose Insert – Text – Text Box (mouse pointer changes to
                                   downward-pointing arrow). Click the worksheet where you want to
                                   place one corner of the textbox then drag diagonally to the size you
                                   want. Excel displays the textbox as a blank area surrounded by a
                                   dotted border and sizing handles with a green rotation handle above.
                                   Type the text creating paragraphs by pressing Enter – or you can
                                   paste text from another source; format the
                                   text if you wish using the Font group of the
                                   Home tab of the Ribbon or right-click and
                                   choose Font for the dialog box (see upper
                                   left). To apply paragraph formatting, right-
                                   click the paragraph you want to affect and
                                   choose Paragraph (see lower left). Use the
                                   options in the Shape Styles group on the
                                   Format tab to choose style, fill, outline or
                                   any effects – but be careful about
                                   readability. To format internal margins or
                                   alignment in a textbox, right-click and
                                   choose Format Shape for the dialog box,
                                   then click Text box (see below). You can
                                   even set up columns within a textbox!

Add Pictures to Worksheets
To insert a picture, select the cell where you want the upper-left corner of the picture to appear then
choose Insert – Illustrations – Picture for the dialog box. Navigate to the picture you want, select it and
click Insert. With the picture selected, use the Format tab on the Picture Tools tab of the Ribbon to crop
(in Size group) using handles at each corner and at midpoints; Shift-drag a corner handle to crop
proportionally, Ctrl-drag a crop handle to crop from both sides simultaneously and Ctrl+Shift-drag to
crop proportionally from both sides. You can also choose Format – Size – Size and Properties to use the
Crop From controls on the Size tab of the dialog box for more precision.

You can also format a picture using the Picture Tools tab: change the brightness, contrast, colors using
those panels – and click the Reset Picture button to restore the picture to its original state if you don't
like the effect. Click Change Picture to choose another picture but retain the picture's position, size and
formatting – or delete and start from scratch. Remember to compress your pictures to reduce file size:
click Compress Pictures button in the Adjust group on the Format tab for the dialog box. Click the
Options button to select the Automatically Perform Basic Compression On Save and Delete Cropped
Areas of Pictures – and for Target Output, choose Print, Ok, OK.

Add SmartArt to Worksheets
For diagrams and organizational charts, click the cell in which you want to place the upper-left corner of
the graphic (you can move later) then choose Insert – Illustrations – SmartArt for the dialog box for
                                                                   choices (see left). In the left panel,
                                                                   choose the category that you want
                                                                   then click the diagram that you want
                                                                   (see preview on right), OK. Excel
                                                                   inserts the diagram and displays the
                                                                   SmartArt Tools section of the Ribbon
                                                                   containing both design and format
                                                                   tabs. The Text pane contains
                                                                   paragraph that map to the shapes of
                                                                   the SmartArt and lets you work on the
                                                                   text separately from the layout (see
                                                                   below left). Enter text by clicking a
                                                                   paragraph in the Text pane then typing
                                                                   the text. To add a shape to the
                                                                   diagram, click the paragraph or shape
                                                                   to which the new item will be related
                                                                   and click Add Shape button in Create
                                                                   Graphic group then choose, ex. Add
                                                                     Shape After, Before, Above, Below,
                                                                     etc. You can change the layout of the
                                                                     diagram, too, without losing the data
                                                                     you've entered, although you might
                                                                     need to rearrange it to suit the new
                                                                     layout. Format the SmartArt diagram
                                                                     on the Format tab. Change the shape
                                                                     of an individual shape in the diagram
                                                                     (click the shape, click Change Shape
                                                                     button in Shapes group then choose
                                                                     from the panel.) Change the size of
                                                                     an individual shape using larger
and/or smaller buttons in the Shapes group. Click the Shape Styles drop-down to choose a graphical
style from the panel, and/or use the Shape Fill, Outline and/or Effects panels. You can also apply
WordArt style to the text in SmartArt shapes by using the controls in the WordArt Styles group – but
again, watch readability. To change the size of your diagram, either drag the sizing handle or use the
Height and Width controls in the Size group.
         Excel 2007 Lesson 6: Perform Calculations with Functions
Objectives:
1. Understand Functions
2. Understand the Components of a Function
3. Enter Functions in Worksheets
4. Nest One Function Inside Another Function
5. Edit a Function in a Worksheet
6. Monitor Calculations with the Watch Window
7. Examples of Functions in Action

Understand Functions
To manipulate data and perform calculations with Excel, you use formulas (lesson 7) and functions.
A formula is a set of instructions for performing a calculation; a function is a predefined formula for
a standard calculation. Excel includes a large number of functions ranging from everyday to highly
specialized.

Understand the Components of a Function
Each function has a name entered in capitals and followed by a pair of parentheses, ex. SUM().
Almost all functions have one or more arguments which specify the elements and type of
information you give them in order to get a valid result. Some functions, ex. =Now(), require no
arguments, but these are exceptions. The rules that govern the types of information a function
needs are called syntax. Excel shows required arguments in boldface, optional arguments in regular
font and an ellipsis to indicate where you can use further arguments of the same type; for example,
SUM(number1,number2,…) In this case, number 1 is a required argument – you can't have a sum
without a number; number 2 is an optional argument (there might be a second number or there
might not be); and the ellipsis (…) indicates that you can use further arguments (number3,
number4…) as necessary to tell the function to include further numbers in the calculation.

Enter Functions in Worksheets
You can enter a function in the active cell in four ways:
    1. Type a function directly into a cell
    2. Click the  drop down in the Editing group on the Home tab of the Ribbon (more functions
        for the Insert Function dialog box)
    3. Choose a function from one of the drop-down panels in the Function Library group on the
        Formulas tab of the Ribbon (then use the Function Arguments dialog box to specify any
        arguments the function needs)
    4. Click the Insert Function button in the Function Library group then use the Insert Function
        dialog box.

The most straightforward way to enter a function is to type it and its arguments into the cells – and
once you've entered enough of the function, Excel displays a ScreenTip to show you the syntax then
tracks your progress. Ex: type 34 in cell A1, type 66 in A2; in cell A3 type =s (see ScreenTip with s
possibilities) then finish typing sum( and again you'll be prompted with the ScreenTip to remind
you of the syntax for the function that Excel now recognizes. (=SUM(number1, *number2+…) Type
A1 after the open parenthesis (Excel recognizes and applies blue outline to the cell to help you make
    sure that it's the correct cell) then type , (a comma, and Excel removes boldface from number1 and
    applies it to number2). Click cell A2 to enter it as the second argument in the function (Excel applies
    marching ants to the cell and enters address in green. Type closing parenthesis for the function
    then click the Enter button (button with check mark) in the Formula bar. Excel enters the function in
    the cell and displays its result.

    Let's look at the Function Library drop-downs (either in Editing group of Home tab or in the Function
    Library group on the Formulas tab.) Here you can do the following:
         Insert a common function (AutoSum, Sum, Average, Count, Max, Min and More Functions)
         Insert a function you've used recently (Recently used drop-down containing last 10
             functions that you've used)
         Pick a function by category (ex. Date & Time then choose function from the panel)

                                                Just select the cell in which you want to enter the
                                                function, click the  (AutoSum) button if you want to
                                                enter the SUM() function or click the drop-down button
                                                and select another. Enter enters the function in the
                                                active cell, and if Excel detects numeric entries in the
                                                cells above or to the left of the active cell, it selects the
                                                cells as a suggestion for what you want to enter in the
                                                function. Edit the selection as necessary (you can drag
                                                to correct or select another range, or you can type the
                                                start and enter cell address into the function) then press
                                                Enter.

                                                For any of the other categories in the Function Library
                                                group (Recently Used, Financial, Logical, Text, Date &
                                                Time, Lookup & Reference, Math and Trig, More
                                                Functions), click to display the panel then click the
                                                function you want for the Function Arguments dialog
                                                box showing the arguments required by the function
                                                (remember My First Car?). Enter each required
                                                argument (click the collapse dialog button to select the
                                                correct cell then click it again to open the dialog box
                                                fully), OK.

To use the Insert Function Dialog Box, select the cell in which you want
to enter the function then click The Insert Function button (fx button
on Formula bar) to display the dialog box. Select the function you
want (search and/or select a category then click the function – and
check the description), OK. Enter the data in each argument box, OK.

Nest One Function Inside Another Function
You can also nest on function inside another function - Excel supports
up to 64 functions. Usually, when you use multiple functions in
sequence, you'll enter a function I one cell then use another function
in another cell to work on the result of that function.
Edit a Function in a Worksheet; Monitor
To edit a function, select the cell that contains the formula then click the Insert Function button o the
Formula boar to display the Function Arguments dialog box again.

Calculations with the Watch Window
To monitor calculations, use the Watch Window: Formulas – Formula Auditing – Watch Window. Click
the Add Watch button for the dialog box and type the address or name of the cell (or click Collapse
Dialog button, select the cell, then click Collapse Dialog button again). Click Add button to add the
watch item to the Watch window – and you can delete a watch cell here, too, then close.

Examples of Functions in Action
Excel has 11 categories of built-in functions: Cube, Database, Data & Time, Engineering, Financial,
Logical, Information, Lookup & Reference, Math & Trig, Statistical, and Text. Access all of these in the
Function Library group (including More Functions for Statistical, Engineering, Cube, Information). Take a
look at each of these categories and their functions, then check out a few of them by reading their
descriptions – isn't it amazing what you can do?!
    Excel 2007 Lesson 7: Create Formulas to Perform Custom Calculations
    Objectives:
    1. Understand Formula Components
    2. Understand How Excel Handles Numbers
    3. Refer to Cells and Ranges in Formulas
    4. Refer to Other Worksheets and Other Workbooks in Formulas
    5. Try Entering a Formula
    6. Use Range Names and Table Names in Formulas
    7. Use Absolute, Relative and Mixed References in Formulas
    8. Work with Array Formulas
    9. Display Formulas in a Worksheet
    10. Hide Formulas from Other Users
    11. Troubleshoot Formulas

Understand Formula Components
Excel's functions are great for performing a wide variety of standard calculations, but sometimes you
need to calculate something that functions don't cover. Thus, you create custom formulas. A formula is
a set of instructions for performing a calculation. In a formula, you use operands to tell Excel which
items to use and operators to specify which operation or operations to perform on them. A formula can
contain up to 64 nested levels of functions, enough for highly complex calculations! Each formula begins
with an equal sign, so the standard way to start a formula is to type an equal sign. When you start a
formula by typing a + or -, however, Excel automatically enters the equal sign.

Operands in a formula specify the data you want to calculate; it can be a constant value entered into a
formula (ex. =8*12) or in a cell (ex. =B1*8) OR a cell or range address or range name OR a worksheet
function.

Operators in a formula specify the operation you want to perform on the operands:
    Arithmetic + - * / % ^ (exponentiation)
    Comparison = <> > >= < <=
    Reference : (range of contiguous cells A1:C16) , (range of non-contiguous cells A1, B2)
    Text & (joins specified values, ex. A1 contains 50 and A2 contains 50, =A1&A2 returns 5050

When a formula contains two or more different operators, Excel performs them in this order: negation,
percentage, exponential, multiplication/division, addition/subtraction, concatenation (&), comparison.
If the operations are together (ex. addition/subtraction), Excel evaluates the operators from left to right.
You can change the operator precedence by using parentheses to indicate which items you want to
calculate first, ex. =(1000-100)*5 – Excel subtracts then multiples. When you nest multiple items, Excel
evaluates the most deeply nested item first, ex. =(100-(10*5))/20, Excel evaluates 10*5 first – so if you
forget the order of operator precedence, just use parentheses. When you are editing a formula, Excel
displays differently nested parentheses in different colors to help you keep track of which parenthesis is
paired with which. When you use and  to move through a formula, Excel flashes the paired
parenthesis for each move; if you omit a parenthesis in a formula, Excel warns you and tries to identify
where the missing parenthesis should go.
Tips: If you prefer, break complex calculations down into a sequence f steps that you perform in
separate cells. Also, if you don't want automatic calculations (to slow down the process), go to Excel
Options, Formulas category; in the Calculations Options area, select the Manuel option button and make
sure Recalculate Workbook Before Saving checkbox is selected. If this feature is turned off, Excel
displays Calculate near the left end of the status bar when the workbook contains uncalculated
calculations. To force calculation manually for a worksheet, press Shift+F9 or choose Formulas –
Calculation – Calculate Sheet; to force calculation for a workbook, press F9 or choose Formulas –
Calculation – Calculate Now.

Understand How Excel Handles Numbers
Numbers can be up to 15 digits long; these 15 digits can appear on either side of the decimal point.
Beyond the 15th digit, Excel changes all digits to 0

Refer to Cells and Ranges in Formulas
Enter the cell or range address by typing or using the mouse. When you use the mouse, Excel displays
marching ants around the border to indicate the selected cell or range. When a formula includes two or
more ranges, Excel uses different-colored borders to help you keep them straight. To refer to an entire
column, specify the letter as the beginning and end of the range, ex. =C:C. Similarly, to refer to an entire
row, specify its number as the beginning and end of the range, ex. 4:4. Refer to a set of columns, ex. A:D
or refer to a set of rows, ex. 1:2.

Refer to Other Worksheets and Other Workbooks in Formulas
To refer to another worksheet in the same workbook in a formula, enter the worksheet name (in single
quotes if the name includes one or more spaces) and an exclamation point (!) before the cell address or
range address. You can type the name, but most find it easier to click the worksheet tab then select the
cell or range with the mouse. Ex. ='My First Car'!F12 Note: If you rename a worksheet, Excel
automatically changes the sheet name in all formulas with that references. A formula can also refer to a
worksheet in another workbook, but be careful not to move the referenced workbook or the formula
will stop working unless you revise that formula, too.

Try Entering a Formula
1. Enter 2000 in cell A1, 4000 in cell A2, 2 in cell B1.
2. Select cell B2 and type =(
3. Click cell A1, type + then click cell A2
4. Type )/ then click cell B1 and press Enter or click the Enter button.

Did you get 3000 as your result? Remember, you can copy a formula by using Copy/Paste or Ctrl-drag
and drop or Home – Editing – Fill panel or by dragging the AutoFill handle.

Use Range Names and Table Names in Formulas
Remember lesson 1: an easy way to refer to a cell or range is to define a name for it. You can then use
the range name in formulas instead of specifying the cell or range address. This is especially helpful
when you are referring to cells and ranges on other worksheets in a workbook. Be careful about
deleting range names, however, since any formula that references the deleted range will display a
#NAME? error.

If you create a table (see next lesson), you can refer to all or part of it by name; and if you change the
table (add or delete rows or columns), Excel adjusts accordingly.
Use Absolute, Relative, and Mixed References in Formulas
An absolute reference always refers to the same cell, even if you move or copy the formula to another
cell. A dollar sign before a column designation means that the column is absolute - otherwise (no dollar
sign), the column is relative (see below). A dollar sign before a row number means that the row is
absolute – otherwise (no dollar sign), the row is relative.

A relative reference refers to a cell's position relative to the cell that contains the formula. This is the
default.

A mixed reference is a mixture of an absolute reference and a relative reference.

Examples (Remember Laptop Camp, M&M's percentages?)
    $A$1 is absolute
    A1 is relative
    $A1 is mixed (column absolute)
    A$1 is mixed (row absolute)


Work with Array Formulas
An array (range of cells) formula performs multiple
calculations that generate either a single or multiple
results. To enter an array formula, create the formula;
then press Ctrl+Shift+Enter so Excel displays braces {}
around the array formula. Excel has to do this – you can't
achieve the same effect by typing the braces manually.

Ex: The formula {=SUM(IF($B$2:B8=B8,$C$2:C8))}

Display Formulas in a Worksheet
When editing or troubleshooting the formulas, you may benefit from displaying the formulas
themselves rather than their results in the cells that contain them. To toggle the display between
formula results and formulas, press Ctrl+` (that's a slanted accent not an apostrophe). You can also
change the default to show formulas in Excel Options, Advanced category, Display Options For This
Worksheet area.

                                    Hide Formulas from Other Users
                                    You can prevent other users from examining or
                                    editing your formulas by formatting the relevant cells
                                    as hidden then protecting the worksheet(s). Select
                                    the cell or range that contain formulas, choose Home
                                    – Cells – Format – Format Cells (or right-click the
                                    selection – Format Cells) for the dialog box (left),
                                    Protection tab. Select Hidden checkbox (make sure
                                    Locked is also checked – that's the default), OK.
                                    Choose Home – Cells – Format – Protect Sheet for the dialog box
                                    above right. Make sure that Protect worksheet and contents of
                                    locked cells is checked; type the password for protecting the
                                    worksheet, OK. Excel closes the dialog box an displays the Confirm
password dialog box. Type the password again, OK. Be careful – if you forget the password, you won't
be able to make any changes to the items that you protected!

Troubleshoot Formulas
If you make a mistake while entering a formula, Excel displays a dialog box (The formula you typed
contains an error.) Click the Help button to display information about correcting the problem(s). If you
click the OK button instead, Excel displays the formula for editing but won't enter it in the cell until
you've fixed the problem. Below are the most common errors:
      #### (need widen the cell)
      #NAME? (formula has misspelled function name or name of nonexistent range
      #N/A (no valid value)
      #REF! (formula has invalid cell or range reference)
      #DIV/0! (formula is attempting to divide by zero)
      #VALUE! (formula has invalid argument, ex. text instead of number)
      #NULL! (specified two ranges have no intersection)
      #NUM! (number isn't valid for function or formula)

Another thing to check is cell formatting, ex. the result cell is formatted to display no decimal places so
2/3 gives you a result of 1 – Excel rounded 0.6667 to 1!

Another thing to check: changes to ranges to which the formula refers.

Excel's Formula AutoCorrect features watches as
you enter formulas and tries to identify errors if
you create them, ex. B3;G3 instead of B3:G3 (see
right). Click Yes to accept the suggestion and No
to return to the cell to edit manually.

Configure error-checking in Excel Options, Formulas category (see below). When Excel identifies an
error that contravenes a rule that's selected, it displays a green triangle in the upper-left corner
                                                                                     of the affected
                                                                                     cell. Select the
                                                                                     cell to display the
                                                                                     Smart Tag then
                                                                                     click it to display
                                                                                     a menu that
                                                                                     explains the problem
                                                                                     and offers possible
                                                                                     solutions.
             Excel 2007 Lesson 8: Organize Data with Excel Databases
    Objectives:
    1. Understand What an Excel Table Is
    2. Create a Table
    3. Rename a Table
    4. Choose Table Styles and Style Options
    5. Enter Data in a Table
    6. Sort a Table
    7. Remove Duplicates from a Table
    8. Find and Replace Data in a Table
    9. Filter a Table to Find Records That Match Criteria
    10. Use Conditional Formatting with Tables
    11. Convert a Table Back to a Range


Understand What an Excel Table Is
A table in Excel is an organized collection of data. Each row is a data record and each column is a field.
Enter the names of the fields in the header row (so you can filter) and make each label unique so Excel
can distinguish each field from the others. Keep column labels concise for viewing ease, and MOST
important: make sure the table area doesn't contain any blank rows or columns because these can
interfere with Excel's sorting and searching.

Create a Table
Type the headings for the table along with at least one
row of data. Select the range that you want to turn into
a table then choose Insert – Tables – Table for the
dialog box. Check Where is the data for your table to
confirm cell address, and make sure My table has
headers is checked, OK. Excel creates the table, gives it
an automatic name, creates headers and applies
banded shading – then displays Table Tools on the
Ribbon. The top row of the table are headers that you
can use to sort the data. The shading colors come from the theme applied to the workbook – you can
change the shading especially if it's hard to read. Also, once you've created a table, Excel keeps the
headings visible even when you scroll down so you don't need to freeze the panes.
Rename the Table
The default name is Table 1, Table 2, etc, so it's best to rename by clicking anywhere in the table to
display Table Tools – Design on the Ribbon and click the Table Name textbox in the Properties group.
Type the new name (make sure it's unique, starts with a letter or underscore, NO spaces!) then press
Enter. Note that Excel will warn you if you use an invalid name.

Choose Table Styles and Style Options
You can leave the style that Excel applies to your table or change it by applying Excel's built-in styles or
custom styles that you create yourself. Click in the table and choose a style from the Table Styles panel
on the Table Tools Design tab: drop the arrow down for Light, Medium, Dark live previews or click New
Table Style for the dialog box. Type the name of the style in the Name textbox (this name will appear
when you hover your mouse over the style's name in the Table Styles panel). In the Table Element list
box, select the element you want to change then click the format button to display a reduced version of
the Format Cells dialog box (only Font, Border, Fill tabs); specify the formatting then click OK. Repeat
the process for each element that you want to change – see the preview to see how the table will look.
The Table Element list box shows the names of items you've changed in boldface; you can remove
formatting that you've applied by clicking an element in this list then clicking the Clear button. If you
want Excel to apply this table style automatically to new tables that you create, select the Set As Default
Table Quick Style For This Document checkbox, OK.

To decide which table elements to display, select or clear the checkboxes in the Table Style Options
group on the Design tab: Header Row, Total Row, First Column, Last Column, Banded Rows, Banded
Columns.

Enter Data in a Table
Use standard techniques (type directly into cell or use copy/paste, AutoFill,
AutoComplete) or enter and edit data with data entry forms. Click the Customize
Quick Access Toolbar drop-down button and choose More Commands. In the
Choose Commands From drop-down list, select the Commands Not In The Ribbon
item. In the left list box, click the Form command then click Add, OK.
Click the Forms button on the Quick Access toolbar; Excel generates the data form
from the table's column headings and displays the data from the first record in the
table. Use the Find Prev and Find Next buttons to navigate to other records. If you
make changes to a record, press Enter; click Restore to undo changes to the current record – but this
only works before you press Enter. Click New to add a record to the table then press Enter – the new
record is added at the end of the table. Click Delete to delete the current record (warning message, OK);
note that you will not be able to recover this record (no undo for deletion) unless you close the
workbook without saving changes in which case you will lose all changes since the last save. Click the
Criteria button to search for records that match specified criteria: Excel clears the data form and displays
Criteria above the New button. Specify the criteria in the appropriate boxes and click Find Next or Find
Prev buttons. Click the Form button to return to regular form view.

You can resize a table by adding or deleting rows and/or columns – Excel adjusts accordingly. You can
also resize manually by dragging the triangle in the lower-right corner of the lower-right cell in the table.
If there is an object linked to the table (ex. a chart), Excel will automatically update the object. To
select a part of a table, hover your mouse over a column or row to get the black down or right-pointing
arrow and select. To select the whole table, hover your mouse over the upper-left corner of the upper-
left cell in the table to display an arrow pointing down and to the right then click.
Sort a Table
After entering data in the table, you'll probably need to sort it so you can view related records together.
If you want to return the table to its original form, however, add a column to the table and name it Sort
Order then enter the appropriate number in each cell (ex. start with 1 in the first cell then AutoFill
down. Once you've done this, you'll b e able to sort the table by this column to restore its records to the
original order.

The easiest type of sort is a quick sort which sorts data by a single field in ascending order (A to Z, lowest
to highest numbers) or descending order (Z to A, highest to lowest numbers). Activate a cell in the
column that contains the field you want to sort and choose Sort & Filter in the Editing group of the
Home tab of the Ribbon OR Data – Sort & Filter OR right click – Sort.

To sort by multiple fields at the same time, choose
Data – Sort & Filter – Sort for the dialog box. In the
first row of controls, set up the first sort criteria:
sort by, sort on, order; click Options button to
apply case-sensitive sorting. Click the Add Level
icon to add another row of controls. Click Ok when
you are done; Excel performs the sort.

Remove Duplicates from a Table
Click the table (Excel displays Table Tools Design tab), then choose Design Tools – Remove Duplicates for
the dialog box. Click Unselect All button to clear all checkboxes, then select only the ones you want. Be
careful to search only columns that have unique values so Excel doesn't remove apparent duplicates
which are really important data. If this happens, click Undo immediately to recover data before you do
anything else! Click OK to start the search and removal; Excel displays a message box telling you what it
found, OK.
                                               Find and Replace Data in a Table
                                               For Find, choose Home – Editing – Find & Select – Find or
                                               press Ctrl+F. For Replace, choose Home – Editing – Find &
                                               Select – Replace or press Ctrl+H. Be careful using the
                                               replace feature if you have large table: mistakes can make
                                               it hard to track down later. More likely, you'll want to
                                               identify all records that match criteria: filtering!

                                              Filter a Table to Find Records That Match Criteria
                                              Filters work by hiding all records that don't match the
                                              criteria – you see only the records that do. To perform a
                                              quick filter, use Excel's AutoFilter feature. This is good for
                                              filtering on the fly, but you can't store the results because
                                              once you turn off AutoFilter, the full table is restored. If
                                              the filter buttons are not at the top of each column of your
                                              table, click in the table then choose Data – Sort & Filter –
                                              Filter. Click the drop-down arrow on the column by which
                                              you want to filter the table; choose the item by which you
                                              want to filter the table: Sort (smallest to largest, largest to
smallest, by color), Text OR Number Filters (See left for the ellipsis options. Note: If you choose any of
these features, you'll get the Custom AutoFilter dialog box; make your choices then click OK.), Items in
the List Box (entries for each unique item column; select the checkboxes of items you want to display),
OK. To display all entries again, click the drop-down arrow again and choose clear Filter; to remove all
filtering

You can create custom filters by using a criteria range - a range of rows outside the table that include
the criteria for filtering the table. Note: This example assumes that your table is positioned at the top of
the worksheet – that's why you insert the rows for criteria. First, activate the worksheet that contains
the table you want to filter and select cells in the top five rows of the table. Choose Home – Cells –
Insert – Insert Sheet Rows to insert five new blank rows above the selected rows – these will contain the
filtering criteria. Click the row heading for the column headings to select ht row then Copy; click the row
heading for row 1 and Paste. In row 2, enter the criteria for the first condition that you want to
implement (use AND, OR, text, wildcards, comparisons such as > or <=). Use rows 3 through 5 to specify
further conditions if necessary. After entering criteria, select the criteria range (including criteria
headers) up to the last row you've used (no blank rows) then choose Formulas – Named Cells – Name A
Range to create an easy name for the criteria range (not essential but helpful). Click a cell in the table
again (so criteria range isn't selected) then choose Data – Sort & Filter – Advanced for the dialog box.
Check the list range then enter the criteria range, check unique records only to suppress duplicate
entries in results) then click OK. To remove filtering form the table, choose Data – Sort & Filter – Filter.

Use Conditional Formatting with Tables
As you remember from lesson 4, Excel can automatically apply formatting to cells that have particular
values. Conditional formatting can be especially useful in tables since it lets you quickly identify values.

Convert a Table Back to a Range
Click anywhere in the table to activate the Table Tools Design tab then choose Design – Tools – Convert
To Range to display a confirmation message box. Click Yes, and Excel converts the table back to a range
and removes the column headers and removes any table shading that was applied.
    Excel 2007 Lesson 9: Analyze Data using PivotTables and PivotCharts
    Objectives:
    1. Understand PivotTables
    2. Create a PivotTable Framework Using the PivotTable and PivotChart Wizard
    3. Create the PivotTable on the Framework
    4. Change, Format and Configure the PivotTable
    5. Create PivotCharts from PivotTables
    6. Create a Conventional Chart from PivotTable Data

Understand PivotTables
A PivotTable is a form of report that works by rearranging the fields and records in a table into a
different format. You can rotate (pivot) the columns in a PivotTable to display data summarized in
different ways, easily sort the table in various ways, filter data, and collapse and expand the level of
information displayed. The PivotTable creates a PivotTable field from each field (column) in the table;
each PivotTable field contains items that summarize the rows of information that contain a particular
entry. PivotTables don’t change the contents or layout of the table, so you can experiment with you
data without corrupting or needing to restore the table. Until you start to use a PivotTable, the features
and benefits might seem difficult to grasp, but if you've created PivotTables or PivotCharts in earlier
versions of Excel and like the Wizard (rather than the Ribbon tab), you'll find the Wizard in the
Commands Not In The Ribbon list in the Customize category of Excel Options and add it to your Quick
Access toolbar.

Create a PivotTable Framework Using the PivotTable and PivotChart Wizard
     Open the workbook that contains the table or data you want to manipulate and display the
        worksheet that contains the table or data (in-class assignment will work). Tell Excel which table
        or data range you want to use: to use a named table, click a cell in the table (Table Tools Design
        tab displays) or use a data range by selecting that range.
     Open the Create PivotTable dialog box; for named table,
        choose Design – Tools – Summarize With Pivot OR for data
        ranged, choose Insert – Tables – PivotTable for the dialog
        box. Make sure that Select a table or range option button
        is selected and the range is correct.
     In Choose where you want the PivotTable report to be
        placed, select New Worksheet (although you can locate on
        existing worksheet, too), OK. A blank PivotTable is created
        and the PivotTable Field List pane along with a blank
        framework for the PivotTable are displayed – and the PivotTable Tools Options and Design tabs.
Create the PivotTable on the Framework
Create your PivotTable by selecting the appropriate checkboxes in the Choose fields to add to report list
in the task pane. When you select a checkbox, excel automatically adds the field to the appropriate area
of the PivotTable. You can click and drag fields between Report Filter, Column Labels, Row Labels
and/or Values sections.

Change, Format and Configure the PivotTable
You'll probably want to give your PivotTable a more descriptive name than the default, so click the
Options tab – PivotTable group and click the PivotTable name textbox to type a new name then press
Enter. You can change a PivotTable by dragging and dropping the fields to different locations, clear a
category (uncheck) to remove the field, or clicking the field's drop down for more options (ex. move
down, move up, remove field, etc.)

                                                                       To make a PivotTable look the
                                                                       way you want, start by applying a
                                                                       PivotTable style – either built-in
                                                                       or custom then choose options to
                                                                       vary the look within the style.
                                                                       When you create a PivotTable,
                                                                       Excel automatically applies a style
                                                                       for you; to change to another
                                                                       style, choose from the PivotTable
                                                                       Styles group panel on the Design
                                                                       tab (preset Light, Medium, Dark
                                                                       categories). To create your own
                                                                       style, click any cell in the
                                                                       PivotTable and choose PivotTable
                                                                       Tools Design – PivotTable Styles –
New PivotTable Style for the dialog box. Type the name of the style (name will appear when you hover
the mouse over the styles name in the panel) then select the element you want to change in the Table
Elements list box. Click the Format button to specify font, border and fill for the element, OK. Shading is
the most effective formatting feature so work with the Fill tab first. Repeat for each element that you
want to change – and you can clear formatting from any element by clicking the Clear button. If you
want Excel to apply this PivotTable style automatically to new PivotTables, select Set as default
PivotTable quick style for this document, OK.

Once you've chosen a PivotTable style, you can use the PivotTable Style Options: Row Headers, Column
Headers, Banded Rows, Banded Columns. The Layout group on the Design tab lets you choose whether
or not to display subtotals, grand totals, switch among three types of report layouts (compact, outline,
tabular) and insert a blank row after each item to make the PivotTable easier to read.

In the Show/Hide group on the Options tabs, you can control which parts of the PivotTable Excel
displays: Field List (for task pane), +/- Buttons (expand/collapse), Show Field Headers. To change the
function used for summarizing the data area in a PivotTable, click in the field itself or data that belongs
to a cell containing the field (Active Field textbox in Active Field group shows the field's name), click the
Field Settings button for the dialog box. On the Subtotals & Filters tab, select the Custom option button
then choose the function in the Select one or more functions list box, OK.




Once you've got your PivotTable looking mostly as you want it to
look, you can adjust further using PivotTable options: Options tab –
PivotTable group – Options button (not drop-down) for dialog box:
     Layout & Format (see right)
     Totals & Filters (below)
     Display (below)
     Printing (below)
     Data (below)
To sort a PivotTable, click the field by which you want to sort, go the the Sort group on the Options tab
and click the appropriate button: ascending, descending or click the Sort button for any other sort
(manual, ascending by, descending by, smallest to largest, largest to smallest). Click More Options for
AutoSort, First key sort order and/or Sort By area, OK.

To filter items in a PivotTable, use the same techniques described in lesson 8: click one of the field drop-
down buttons to produce a panel of ptions. Filter by Label or by Value; select or clear checkboxes in the
list box, clear filter to remove.

To help identify the data you need to work with in a PivotTable, you can group items:
     Numeric field (click numeric field, Options – Group – Group Field for dialog box; enter starging
         and ending numbers and type how many items you want to group together, OK)
     Date or time field (click date or time field, Options – Group – Group Field for dialog box; check
         starting and ending and enter dates then select a time period, OK)
     Selected items (select the items you want to group, Options – Group – Group Selection
To upgroup grouped items, select the items then choose Options – Group – Upgroup.
You can change the data source for the PivotTabel (Options – Data – Change Date Source for dialog box)
or move the PivotTable to another location: click anywhere in the PivotTable then choose Options –
PivotTable Options – Move PivotTable for the dialog box, OK.

Create PivotCharts from PivotTables
The advantage of a PivotChart over a regular chart is
that you can drag fields to different locations in the
chart layout to display different levels of detail or
different views of the data. Create a PivotTable then
click anywhere in the PivotTable and choose Options
– Tools –PivotChart for the Insert Chart dialog box.
Select the chart of PivotChart that you want, OK; Excel
creates a Pivotchart as an object in the worksheet
with the PivotTable, adds PivotChart Tools (Design,
Layout, Format, Analyze) to the Ribbon, and displays
the task pane. You can move the PivotChart to its
own worksheet (Design – Location – Move Chart) and
arrange the data on the PivotChart so it makes more
sense. Drag the field to the appropriate palces in the
TivotTable Filed List pane and/or add or remove field to pivot the chart.

Create a Conventional Chart from PivotTable Data
Select the data in the PivotTable then Copy; select a cell in a blank area of the same or different
worksheet, right-click and choose Paste Special. Select the Values option button, OK, then choose Insert
– Charts. More on charts in lesson 11.
Excel 2007 Lesson 10: Solve Problems by Performing What-If Analysis
Objectives:
1. Create Data Tables to Assess the Impact of Variable
2. Explore Alternative Data Sets with Scenarios
3. Solve Problems with Goal Seek
4. Use the Solver to Manipulate Two or More Values


Create Data Tables to Assess the Impact of Variables
If you need to assess the impact of a single variable or two variables on a calculation, the tool to use
is a data table: an automated way of entering an array formula in a range of cells to display the
results of using different values in one or multiple formulas. The easiest type of data table to create
is a single-variable data table. Layout a single-variable data table so the input values (the values you
want to test) either run down a column or across a row (you can't place input values in a range of
cells that spans multiple rows AND columns.) Excel feeds input values to a data table through a cell
called the input cell. You enter the input cell in the formula (or formulas) in place of one of the
values or references for which you want to test the input values. The input cell must be blank
(otherwise, Excel uses the cell's value in the formula, which defeats the point of the exercise), and
can be anywhere on the worksheet. In most cases, using a cell adjacent to the range that contains
the input values is clearest and least confusing. We're going to use the Financial =DB() Function to
calculate the depreciation of an asset (like a car) over a specified year in it life by using the fixed-
declining balance method. Enter the following data into Excel A1:B4 then leave a blank row and one
blank column before the first input value in a row. Entering the input values down a column creates
a column-oriented data-table (across a row is row-oriented) – this will be column-oriented.




Initial Cost                                                                                $25,000.00
Salvage Value                                                                                $3,000.00
Asset Life (Years)                                                                                   6
Period                                                                                               2

Input Values
                         1
                         2
                         3
                         4
                         5
                         6
    Enter the formula in cell B6 (immediately above
    the first input value in the next column to the
    right): =db(B1,B2,B3,A5) – this is DB(cost,
    salvage, life, period, [month]). You'll get #NUM!
    error because the input cell is blank (zero
    value). Select the range of cells that contains
    the formula and input values (A6:B12) and
    choose Data – Data Tools – What-If Analysis –
    Data Table for the dialog box. For column-
    oriented data table, enter the cell reference in
    the Column Input Cell textbox; in this example,
    center cell reference $A$5 by clicking cell A5, OK. Excel creates the data
    table, entering the array formula {=TABLE(,A5)} in each results cell;
    #NUM! error remains but results in range B7:B12 display correctly
    (format cells as currency).

    You can use two or more formulas in a single-variable data table; for
    column-oriented, enter formulas in cells to the right of the first formula
    and for row-oriented, enter formulas in cells below the first formula.
    Let's add a second formula, DDB, to the data table – that's depreciation
                       using double-declining balance method so resulting
                       data table lets you compare depreciation in each year
                       of the asset's life using each depreciation method.
                       Enter the formula =DDB(B1,B2,B3,A5) in cell C6 then select the range that
                       contains the input range and the two formulas (A6:C12) and choose Data – Data
                       Tools – What-If Analysis – Data Table for the dialog box. Enter the cell reference
                       for the same input cell as for the previous formula (in this case, $A$5) in the
                       Column Input Cell textbox, OK. Excel adds the second formula to the data table
                       and displays the results with array formula {=TABLE(,A5)}.

     Sometimes you'll need to assess what happens when two pieces of information change, for
     example, when calculating the depreciation of an asset both over time and also for different salvage
     values. To create a two-variable data table, enter the second set of input data in the other
     dimension from the first set: if the first set of input data is in a column, enter the second set of input
     data in a row across the top of the results area and vice versa. Place the formula at the intersection
     of the input data row and input data column.
                                                                   Copy A1:B4 to a new worksheet and type
                                                                   Row Input in cell A6 and Column Input in
                                                                   cells A7. Enter the first series of input data
                                                                   (1,2,3,4,5,6) in the range D4:D9 – this
                                                                   range will be linked to the column input
                                                                   cell. Enter the second series of input data
                                                                   ($1,000, $2,000, $3,000, $4,000, $5,000,
                                                                   $6,000) in the range E3:J3 – this range will
be linked to the row input cell. Enter the formula =DB(B1,B6,B3,B7) in cell D3 at the intersection of the
input column and input row; B6 is the row input cell, and B7 is the column input cell – and you'll get the
same error result, of course. Select D3:J6 then choose Data – Data Tools – What-If Analysis – Data Table
for the dialog box; enter B6 in Row input Cell textbox and B7 in Column Input Cell textbox, OK. Excel
creates the data table entering the array formula {=Table(B6,B7)} in results cell.

Once you've created a data table, you can manipulate its contents only by changing the input values or
the formula; you can't directly change the contenst of any results cells because Excel implements the
data table as an array formula. To change the data table contents, clear the data table: select the range
of cells that contains the values then choose Home – Editing – Clear – Clear Contents or press Delete but
don't select any formula cells! To clear a data table entirely, select every cell in the range including cells
with formulas then choose Home – Editing – Clear – Clear All. You can copy the results of the data table
to a different location using Copy/Paste: Excel copies not the array formulas but the results. You can
also move a data table using drag and drop; Excel changes the references in the formulas but otherwise
leaves the array formulas intact.

Explore Alternative Data Sets with Scenarios
Excel's scenarios features lets you define and use alternative data sets within the same workbook.
Instead of creating a separate version of a workbook and using it to experiment with different values or
different formulas, you can use scenarios to experiment more comfortably without damaging your main
workbook. Create the worksheet you want to manipulate and define names for the cells whose values
will be manipulated in the scenarios. You can open you’re My First Car file from laptop camp and get a
general idea of the features. Choose Data – Data Tools – What-If Analysis – Scenario Manager for the
dialog box. Click Add for that dialog box, give the scenario a name and select the change cell range –
comment is optional but your username and current date is default. Select or clear prevent changes
and/or hide, OK for the Scenario Values dialog box. You can create additional scenarios using the
Scenario Manager dialog box as
well as Edit existing, Delete; to
switch scenarios, select the one
you want in the list box and click
Show to display the scenario in
the workbook the close. Note
that Merge is for sharing
workbooks with colleagues. The
Summary button allows you to
crate either a summary or
PivotTable report from scenarios.


Solve Problems with Goal Seek
If you ever find yourself trying to work backwards from the result you want to achieve, Goal Seek is a
very helpful feature. Using My First Car, select the cell that contains the formula you're interested in
then choose Data – Data Tools – What-If Analysis – Goal Seek for the dialog box. Let's say that you have
                                                       $500 budgeted for a car payment and want to see
                                                       how much you can afford to pay for a car. Click the
                                                       monthly payment cell (set cell), type 500 in To
                                                       value textbox and in the By changing cell textbox,
                                                       click the price of the car cell, OK. Goal Seek
                                                       automatically enters the target value it achieved.
                                                       Click Ok to accept or cancel to reject.
Use the Solver to Manipulate Two or More Values
               If you need to work backward by manipulating two or more values, you'll need to use
               Solver instead of Goal Seek. Solver is an Excel add-in rather than a built-in component;
               click the Office button – Excel Options – Add-Ins category. In the Manage drop-down list,
               select Excel Add-Ins and click Go for the Add-Ins dialog box. Select Solver Add-in, OK.
               Note: You must be online for it to install properly.

               To use Solver, select the cell that contains the formula you've interested in then choose
               Data – Analysis – Solver for the dialog box (see below). The cell that you select appears
               in the Set Target Cell box; in the Equal to area, select Max, Min or Value of and type the
               value you want. In By Changing Cells textbox, enter the references for the cells whose
               value you want Solver to manipulate. You can also apply constraints using the controls
               beside the Subject to the Constraints box to add, change or delete constraints. Click the
               Solver button to start computing the solution; the Solver Results dialog box displays.
Select Keep Solver Solution or Restore Original Values option button, and you can generate one or more
reports by select the ones you want in the Reports list (Solver inserts each report on a new worksheet),
OK.
    Excel 2007 Lesson 11: Create Effective Charts to Present Data Visually
    Objectives:
    1. Understand the Basics of Excel Charts
    2. Create a Chart
    3. Layout and Format a Chart
    4. Copy Formatting from One Chart to Another
    5. Print Charts
    6. Create Custom Chart Types for Easy Reuse

Understand the Basics of Excel Charts
Excel can create two types of charts:
    1. Embedded (positioned on a worksheet along with the data)
    2. Chart on chart sheet (a separate sheet giving more space to create a detailed chart)
You can switch one to the other. All charts typically have the following components:
     X-axis (usually horizontal)
     Y-axis (series, vertical)
     Z-axis (value axis, depth axis of chart for 3-D only)
     Axis titles
     Chart title
     Data series (set or sets of data from which the chart is created)
     Data marker (a point in a data series)
     Data labels (text that identifies points in the data series)
     Legend (color, pattern used to distinguish each data series)
     Gridlines (reference lines drawn across the chart from axes to help see values)
     Categories (distinct items in data series)
     Chart area (area occupied by the entire chart including legend, labels, title, etc.)
         Plot area (area occupied by data plotted in the chart)

Create a Chart
1. Select the range of data from which you want to create
the chart (hold down the Ctrl key to select non-contiguous
range)
2. Choose the chart type: Insert – Charts, click drop-downs
for Column, Line, Pie, Bar, Area, Scatter, Others – or click
the Charts dialog box launcher and hover mouse to see
ScreenTip of chart subtype; click the one you want, OK )
3. Use the Chart Tools controls on the Ribbon (Design,
Layout, Format) to format and arrange the chart or you can
right-click an individual element for commands.

To give the chart a name (to distinguish it from others),
select the chart and choose Layout – Properties – Chart
Name; type the name for the chart then press Enter.
To change a chart from embedded to being on its own sheet (or vice versa), select the chart then choose
Design – Location – Move Chart for the dialog box. Choose the appropriate option button (new sheet or
object in), OK.

You can move the chart by moving the mouse pointer over an area of the border that's not one of the
dotted handles then dragging the chart where you want it. And you can resize the chart by moving the
mouse pointer over one of the dotted handles in the chart's border (pointer changes to double-headed
arrow) then drag the border to resize in only one dimension. For example, drag the bottom handle
down to make the chart deeper without affecting its width; drag a corner handle to resize the chart in
both dimensions at once and Shift-drag a corner handle to resize the chart proportionally in both
dimensions.

You can select objects in a chart by clicking with the mouse easiest for larger objects) or click the Layout
tab or Format tab and go to the Current Selection group to open the Chart Elements drop-down list
(good for smaller objects). If you're working on a worksheet that contains textboxes, shapes or pictures
as well as a chart, you might need to use the Selection And Visibility pane to turn off objects that are in
the way. Select the chart then choose Format – Arrange – Selection Pane then choose what you want to
do: Select an item, Hide an item, Display an item again, Hide all items, Show all items, Reorder the items
in the front-to-back stack.




Layout and Format a Chart
You can easily change the chart if you've chosen the wrong type for your data: select the chart and
choose Chart Tools Design – Type – Change Chart Type for the dialog box (same as Insert Chart); choose
the chart type and subtype, OK. Even when you've chosen the chart type, you may need to change the
layout: select the chart and choose Chart Tools Design – Chart Layouts – More (click the down arrow on
the right) then click the layout you want. Similarly, you can change the chart's style: select the chart and
choose Chart Tools Design – Chart Styles – More to display the panel then click the style you want. If
you want to change the Chart's Source data, select the chart and
choose Chart Tools Design – Data – Select Data for the dialog box (see
right); move it out of the way of your data, drag in the worksheet to
select the data range you want to use, OK. You can also switch the
rows and columns (choose Design – Data – Switch Row/Column) or
                 include hidden and empty cells: click the Hidden and
                 Empty Cells button in the Select Data Source dialog
                 box to display that dialog box (see left). Select the
                 appropriate option button for Show empty cells (gaps,
                 zero, connect data points with line) and check Show
data in hidden rows and columns if you wish, OK.
To change the data series in the chart but not the data source, choose Design – Data – Select Data for
the dialog box; in the Legend Entries (Series) list box, select the column you want to affect then click the
appropriate button: Add, Edit, Remove, Move Up and Move Down, OK.
Tip: If Excel isn't displaying ScreenTips for Chart Elements, check Excel Options – Advanced category,
Display section and select Show Chart Elements Names On Hover and Show Data Points Values On
Hover checkboxes, OK.

Add Labels to a chart to make it easier to read and more visually effective:
click the Layout tab to add a Chart title (centered overlay, above chart, etc),
Axis titles, Legend (shows color or pattern representing data series), Data
labels, even Data table (show data from which the chart is drawn). When
you choose More Options from the bottom of the panel, you can work with
the resulting Format dialog box to apply border, shadow or 3-D format to
many elements (see right for an example).

                                 To change the scale of an axis from its
                                 default settings, select the chart and choose
                                 Chart Tools Layout – Axes – Axes then
                                 choose the axis from the panel: Primary
                                 Horizontal or Primary Vertical (see left) for
                                 options or click More for the Format Axis
                                 dialog box. Choose the options you want
                                 (these will differ depending on chart type
                                 and which axis) then Close. See below left
                                 for Horizontal axis example; see below right
                                 for Vertical axis example.

                                Note: A text axis is an axis that has the data
                                points evenly spaced out, whereas a data
                                axis is an axis that has the dates arranged in
                            chronological order according to standard intervals
                            (years, months, days).

                            To liven up charts, use fills consisting of colors,
                            gradients, textures or pictures. Select the chart,
                            choose the object which you want to apply the fill
                            (you can also right click to access the Format dialog
                            box – see example below and not options.) You can
                            also use the features in the Background group of
                            the Layout tab.

                            Use the Analysis group of the Layout tab to apply
                            trendlines (linear, logarithmic, exponential, power,
                            polynomial, moving average), drop lines or high-low
                            lines, up/down bars and error bars (to show margin
                            of error: minus or plus and/or specific error
                            amount).
You can even format different data series using different chart types, for example, a column chart for
one data series and a line for the other. You'll want to experiment to see which nets you the most
striking and comprehensible results. Create the chart as usual and format it using the chart type that
you want to apply to most of the chart. Right-click the data series you want to affect then choose
Change Series Chart Type from the context menu for the dialog box. Select the chart type and subtype
as usual, OK. You can also use different formatting to a data series, for instance, real figures and
projected (future) figures.

Copy Formatting from One Chart to Another
Once you've applied custom formatting to a chart, you can quickly copy it to another chart: select the
chart area of the source chart, Copy, select the destination chart, Paste Special for the dialog box. Select
the Formats option button, OK.

Print Charts
Before printing, always use Print Preview to see how it will
look. Note that you can print a chart in draft quality or black
and white: Page Layout – Page Setup – Size – More Paper
Sizes, Chart tab (see right).


Create Custom Chart Types for Easy Reuse
Create the chart and apply formatting as needed, select the
chart (either the chart sheet tab or the embedded chart),
then choose Design – Type – Save As Template for the dialog
box. You can save in the Excel Templates\Charts folder
(default) or in another folder if you want to keep it separate
from Excel. Type the name of the chart template in the file
name textbox then click Save.
       Excel 2007 Lesson 12: Use Excel with the Other Office Applications
    Objectives:
    1. Transfer Data using the Clipboard and Office Clipboard
    2. Embed and Link Objects
    3. Insert Excel Objects in Word Documents
    4. Insert Excel Objects in PowerPoint Presentations
    5. Insert Word Objects in Worksheets
    6. Insert PowerPoint Objects in Worksheets


Transfer Data Using the Clipboard and Office Clipboard
Office Excel 2007 is thoroughly integrated with the other applications in Office: Word, PowerPoint,
Outlook and Access. The Clipboard is an easy way to copy and move data either within an application or
between applications. Here are some tips:
     The Windows Clipboard can hold several different types of data including text and graphics but
        can only hole one item of each type at once. Copying an item will overwrite the contents of the
        Clipboard for that data type.
     The Office Clipboard can hold up to 24 items of the same or different types; to display the task
        pane, choose Home – Clipboard and click the dialog box launcher.
     If you choose Home – Clipboard – Paste – Paste Special, the resulting dialog box will allow you to
        control the format in which the object is pasted.
     You can also just use the Paste command and the Smart Tag to change the format.

Embed and Link Objects
Excel and other Office applications support three different ways of including an object created in one
application in a file created in another application: embedding, linking and inserting. An object (ex.
Excel chart, Word table, PowerPoint slide) is a component of a file that can be handled separately.
Inserting (ex. a graphic) is relatively straightforward: when you insert an object, the file contains neither
the information for editing the object in place nor the link to the source file – the object simply appears
in the file in the place you specify.

Embedding is the basic means of inserting an object created in another application into a file (ex.
WordArt, chart in PowerPoint slide). When you embed an object in a file, the file contains a full copy of
that object, so embedding can greatly increase the file size. The copy is independent of the original, and
you can edit it separately, but you can't update the copy directly from the original – you would have to
replace it to update. This is why linking, although a more complex method of inserting an object, might
be the better option. When you link an object to a file, the file displays the current information for that
object but stores only the link that describes the object including where it's located. Storing this
information is more compact that storing the actual data, so the file size hardly changes. When you
need to edit a linked object, you do so at the source.

Thus, the advantage of embedding is that the object remains available even if you move the file or if the
source file is not longer available. The disadvantages are the increased file size and not easy way to
update the object. The advantages of linking is the small impact of the file size and ease of update; the
disadvantage is if the source file isn't available, the object doesn't appear. So when deciding whether to
embed or link, think about whether you will need to edit (yes, then embed), keep the file size down (yes,
then link), will different people need to work on the file (yes, then link).
You can't tell immediately by looking at an object in a document if it's linked or embedded, but if you
select the object and check the readout in the reference area, embedded objects' readouts start with
=EMBED and linked objects' readouts contain reference to a file by name (ex. =Word.Document.12…..)
In Word, PowerPoint and Outlook, you can right-click the object and see if Update Link command is on
the shortcut menu.

The easiest way to embed or link an existing object is to use
the Paste Special dialog box. In the object's source
application, select the object and Copy. Activate the
destination application and select the location where you want
to embed or link the object. Choose Home – Clipboard – Paste
– Paste Special (click the Paste button down arrow) for the
dialog box. Choose the format you want to embed or link the
object (choices depend on the type of object you copied and
the destination application). Select the paste option mutton
to embed the object or the Paste link option button to link the
object. If Display as icon checkbox is available, you can select
it to make the application display not as the object itself by an
icon representing it, OK.

                                      You can also embed a new object that you create: in the
                                      destination application, position the insertion point or selection
                                      where you want the new object to appear then choose Insert –
                                      Text – Object for the dialog box. On the Create New tab (left),
                                      select the type of object you want to create and embed, OK. Note
                                      that by using the Create From File tab of this dialog box (below
                                      left), you can embed or link an object that consists of the entire
                                      contents of an already existing file: browse and navigate to the
                                      file and select then click Insert. Select the Link To file checkbox if
                                      you want to link rather than embed, and you can display as icon,
                                      too, OK. Note that if you select this option, the Change icon
                                      button appears for you to click for more options including caption.

                                      How you edit an embedded object depends on the object type and
                                      the application whose document you've embedded it in. Usually,
                                      you can right-click the object and select Object – Edit to activate
                                      that section of the Ribbon. Remember that editing embedded
                                      objects will not change the source object since there is not link
                                      between the embedded and source objects. Another caution: in
                                      order to edit an embedded object, the application that created the
                                      object must be installed on your computer.

                                      You edit a linked object in its source rather than in the destination
                                      application. Right click the object and issue an Edit command.
                                      When you close the object in the source application, the linked
                                      object in the destination application is updated.
To work with links in an Excel workbook, choose Data –
Connections – Edit Links for the dialog box (right). Here you
can Update Values to force an update of the link, Change the
source, Open the Source, Break the link, Check the status of
the link, or switch between automatic or manual update.

You can control whether Excel prompts the user to update
links at startup in Excel Options – Advanced category,
General area (Ask to update automatic links checkbox). Also, in the
Edit Links dialog box, you can click the Startup Prompt button for more
choices (below right). If you get a Security Warning (ex. Automatic
update of links has been disabled), click the Options button next to the
warning and select Enable this content, OK.


Insert Excel Objects in Word Documents
When you paste an Excel chart into a Word document, the
default is to paste it as a chart linked to Excel data. This is
         usually the best way of handling the chart since you
         can resize, edit and update easily. Sometimes, you
         might want to handle it differently; click the Paste
         Options button for more options (see left). You can
         also use Paste Special for the options in that dialog box
         (right). WMF is the standard graphical format for
Windows and is the best option; EMF offers more but some
applications don’t support it. Bitmap is also standard and
stores details of information contained in each pixel
(uncompressed so it takes more file space). PNG (portable
network graphics) is a newer format developed for Internet use; it compressed without discarding any of
the detail – a good option when available. JPEG (joint photographic experts group) is widely used on the
web but compression discards some detail. GIF (graphics interchange format) is also widely used on the
web and compression doesn't lose details but file sizes are larger than PNG.

                  You can paste a range of cells from an
                  Excel worksheet into a Word document;
                  by default, Word pastes as a Word table
                  retaining as much formatting as possible.
                  See left for the Paste Options. Again,
                  Paste Special offers more choices (right).

You can use an Excel Table as the data source for a Word
Mail Merge – we will do this with Excel or Access as data
sources using the Word Mail Merge tab/wizard during a
tutorial in February.
Insert Excel Objects in PowerPoint Presentations
When you paste a chart into a slide, PowerPoint pastes the chart
         linked to the Excel data. As with pasting into Word, this is a
         good option because you get a chart that you can edit in
         PowerPoint, resize and update from the original data in
         Excel. To change the format, click the Paste Options button
         (left). Use Paste Special if you want a different graphics format (above right).

You can also insert a range of cells in a PowerPoint slide;
PowerPoint creates a PowerPoint table using HTML formatting to
retain the formatting applied to the range – sometimes this works
and sometimes it's a disaster! You can edit the table as a
PowerPoint object but not as an Excel object. If it's a mess, Undo
and use Paste Special options (right and note that formatted RTF
and unformatted text options are also available when you scroll
down).

Insert Word Objects in Worksheets
You might want to insert part or all of a Word document in an Excel worksheet, however, it's NOT best
to simply copy/paste since Excel pastes data from Word in HTML format by default rather than creating
                                          an embedded object. You can use the Paste Options, but it's
                                          better to use Paste Special choices (left). Note that you can use
                                          Paste Link, but Paste Special gives you the range of options.

                                         Insert PowerPoint Objects in Worksheets
                                         The Paste Special dialog box lets you paste or paste-link a slide
                                         as a PowerPoint slide object or as a picture in any of the five
                                         formats (PNG, JPEG, GIF, EMF, or Bitmap). With the Paste
                                         option button selected, the Microsoft PowerPoint Slide Object
                                         option inserts the whole slide so you can edit in place.

                                        Using the Create from file tab of the Object dialog box, you can
                                        insert an entire presentation in to an Excel worksheet so you can
run the presentation directly from the worksheet. This might be useful to have the presentation's data
saved in the workbook, but, as you can imagine, the file size increases greatly. Double-click the
embedded presentation to run.

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:321
posted:11/14/2010
language:English
pages:53
Description: Vba Excel Rename Worksheet document sample