Welcome to Washburn University, Topeka, KS



Excel 2007 Up-to-Speed Workshop

Originally prepared by Judy Druse; revised 8/03/07 by Sara Tucker

Getting Started

• Log in to your WUAD account. (We have demo accounts if there is any problem.)

• Open Excel: START> All Programs> Microsoft Office> Microsoft Office Excel.

• Repeat (open 2nd Excel workbook). Use either one in workshop – we’ll need 2 open for one action

The Excel Ribbon and Worksheet

The ribbon replaces all the menus and toolbars. There are seven permanent tabs across the top, each divided into groups that host a series of commands. Further options are displayed when you click the dialog box launcher in the lower right-hand corner of a group.

[pic]

The Office Button

Click the Office button at the top left corner of the Excel program window to save a workbook, create new workbooks, print a worksheet, change program options, and quit Excel.

With the Office Button box open:

Put your cursor over, but do not click on, Save As – examine saving choices.

Put your cursor over Prepare, then click on Properties – see the Document Properties box – when filled out, this makes it much easier to search for a document.

Click on the Excel Options button at the bottom of the Office Button Box – we’ll examine various items.

Workspace Overview

• Zoom slider (on status bar at bottom of screen) - 10% to 400% view

• Full Screen View: Click on View tab, then (in Workbook Views group) Full Screen icon – to close, use Esc (escape) key

• Meet other views – run cursor over unlabeled icons in View tab’s Window group – see what you can do.

• Open/Close ribbon – double click a tab to close; repeat double click to open

• Quick Access Toolbar – right click on action to add – right click toolbar to remove

• The Clipboard Expander keeps track of the last 24 items cut or copied

• Help button (far right top small blue circle with white “?” mark)

Building a Workbook - Put in your practice data

Create data on two practice sheets:

Enter the data below in the cells indicated on Sheet 1:

• Cell Data

A1 Deduction Calculations for Employees

A7 Last Name

A8 Smith B.

A9 Wilson C.

A10 Thompson J.

A11 James D.

B3 Date:

B5 Deduction Rate:

B7 Gross Salary

B8 $45,789.00

B9 $41,245.00

B10 $39,876.00

B11 $43,211.00

C5 6%

C7 Deduction

D7 Net Salary

• Enter the data below in the cells indicated on Sheet 2:

Cell Data

A1 The Cookie Shop 2003 Revenue from Sales

A2 Peanut Butter

A3 Chocolate Chip

A4 Oatmeal Raisin

A5 Lemon

B2 $15,500

B3 $27,589

B4 $24,980

B5 $14,768

Formatting Your Practice Data

Things to remember:

• What starts as an Excel 2003 file is saved as an Excel 2003 file unless you say otherwise

• Double click a cell to edit cell contents directly into the cell

• Single click to select cell – you can then edit its text as it appears in the Formula bar

• Double click a sheet name to rename it

• Wrap text – displays text on multiple lines in a cell

• Alt tab shows Key Tips (shortcuts), but old keyboard shortcuts are still intact

Practice doing these things:

• Applying Styles

o Drag select cells A1 to D1

o In Home tab’s Styles group, click Cell Styles, choose one

o With cells still selected, in Home Alignment group, try out results of Wrap tText and Merge & Center icons

• Changing Font Color

o Drag select cells A7 to D7

o In Home tab’s Font group, click arrow next to Font Color icon, change color

• Changing Data Alignment

o Drag select cells A7 to A11 –

o In Home tabs’ Alignment group, use alignment icons to

• Right align cells A7 to A11

• Center align B7 to B11

• Center align C7 to C11

• Left align D7 to D11

• Formatting Cells

o Click on cell C3

o In Home tab’s Cells group, Click Format>Format Cells

o Click Number tab, choose Date category

o Choose “March 14, 2001” style – note result (may get only “#######” appearing in cell)

o If so, widen cell by grabbing and dragging right border of “C” column header

• Name a Cell to Simply Formulas

o Click on cell C5

o Click on the Name Box (to the left of the Function Bar)

o Type rate

o Press Enter (this is important, to “fix” name change)

• Conditional Formatting and Autofill

o Select cells B8 to B11. Create Conditional Formatting to highlight the cell with data less than $40,000. In Home tab, Styles group, click Conditional Formatting> Highlight Cells Rules> Less Than, fill in $40,000

o Click on cell C8. Type in the formula = B8 * rate (the * is the mathematical operator for multiplication). Press Enter

o Apply similar formulas for similar entries. Move the mouse pointer over the fill handle in the bottom right corner of the active cell. When the pointer changes to a black plus sign, click the mouse pointer and drag the fill handle down to cell C11. Not only is the deduction automatically calculated, but the cells are formatted based upon the rule that was set. Click on cell C11. The formula in the Function Bar now reads = B11 * rate and the 6% deduction has been automatically calculated in cells C9 to C11.

o Use Sum icon. Click on cell B12 to select it. In Home> Editing Group, click the ∑ (Sum) icon.. Press Enter.

o Click Sum>More Functions to open the Insert Function dialog box. Note search function, etc.

• Create a Data Table

o Drag select cells A7 to D11

o Home> Styles Group, click Format as Table. Click to Select one look.

o In Format As Table box that appears, Check “my table has headers”. Click OK.

o Notice that a Table Tools Design tab has appeared at the end of the ribbon.

o Click down arrow next to Last Name column header; choose “Sort A-Z”. Do the same for Gross Salary, choose “Sort Largest to Smallest”.

• Insert a Hyperlink

o Can be done for another file, to a Web page, to an e-mail address. Select cell A-13, key in “For further information, email “ then in Insert tab, click Hyperlink icon. Click E-mail Address (lower left corner of Insert Hyperlink box). Put your name into “Text to Display” slot and your email address into “E-mail address” slot.

Create a Pie Chart

(Note: One change in creating a pie chart in Excel 2007 is that the Chart Wizard is no longer available. It has been replaced by chart options listed under the Insert ribbon.)

• Open Sheet 2. Select cells A2 to B5.

• Insert>Pie>3-D Pie. A basic pie chart is created and placed on your worksheet.

• Notice that a Chart Tools tab has appeared at the end of the ribbon. When you click on a chart, three tabs – the Design, Layout, and Format tabs are added to the ribbon under the title of Chart Tools.

• Click on the Design tab. Choose a style.

• Click on the Peanut Butter slice until you see handles only around that slice. Click and drag the Peanut Butter slice out from the pie chart to explode it.

• Click on the Layout tab. Click on Chart Title in the Labels group. Select the option Above Chart. Type in “The Cookie Shop 2003 Revenue from Sales.”

• Drag select the chart title. Click on the Format ribbon tab. Choose Text Effects>Shadow>Offset Right.

• Layout>Legend>to remove or move the legend.

• Layout>Data Labels>Best Fit to add data labels

• Layout>Data Labels> More Data Label Options to bring up the Format Data Labels dialog box.

• Remove the check marks from Value and Show Leader Lines under Label Options in the right-hand window.

• Check off Category Name and Percentage under Label Options in the right-hand window.

• Click on the chart background. Click on the Format ribbon tab. Choose Shape Fill>Gradient – explore options

• To bevel the chart edge, click on the chart background. Click on the Format ribbon tab. Choose Shape Effects>Bevel>Divot.

• Before you exit Excel 2007, try to cut and paste the chart into a Word document and a PowerPoint slide.

Online Help

>

Help and How-To Tab

Get Started with the 2007 Release

Basic Training for the 2007 Release



Excel Help and How-To

Excel 2007 Help

Tucker webpage of useful Microsoft Office 2007 online help links:

FYI: Overview of What’s New in Excel

• Over a million rows and 16000 columns

• Improved ability to open corrupt files

• Compressed files – 50% to 75% smaller

• XML based data so it is easier to integrate with other data sets

• Galleries - You can apply formats to worksheets, charts, and other objects by selecting the appearance you want from a gallery.

• Colors - When you assign a design theme to a workbook, you can have as many different colors in the workbook as you like.

• Shortcut Menu and Mini Toolbar - Click the right mouse button over any cell to display the shortcut menu. A mini toolbar sits at the top of the shortcut menu.

• Page Layout View – make adjustments to worksheet before printing

• New Functions

o AVERAGEIF, which lets you find the average value of cells in a range for cells that meet a single criterion

o AVERAGEIFS, which enables you to find the average value of cells in a range for cells that meet multiple criteria

o SUMIFS, an extension of the SUMIF function, which enables you to find the sum of cells in a range for cells that meet multiple criteria

o COUNTIFS, an extension of the COUNTIF function, which enables you to count the number of cells in a range that meet multiple criteria

o IFERROR, an extension of the IF function, which lets you tell Excel what to do in case a cell’s formula generates an error (as well as what to do if the formula works as expected).

• Arrange Data into Tables

• Conditional Formats

o You can create an unlimited number of conditions (previously, 3 was the limit).

o You can apply several new types of conditional data formats: data bars, which create a horizontal bar across a cell indicating how large the value is; color gradients, which change a cell’s fill color to indicate how large the value is; and icon sets, which display one of the available icons depending on the guidelines you establish.

• Rules - You can have as many rules as you like, apply several rules to a single data value, choose to stop evaluating rules after a particular rule has been applied, and change the order in which the rules are evaluated without having to delete and re-create the rules you change.

• More attractive charts

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download