Microsoft office excel 2003 training l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 77

Microsoft ® Office Excel ® 2003 Training PowerPoint PPT Presentation

CGI presents: Microsoft ® Office Excel ® 2003 Training Great Excel features Course contents Overview: Five great features Lesson 1: Freeze! Lesson 2: Compare side by side Lesson 3: Sum it up, and more (Continued on next slide.) Course contents, cont’d. Lesson 4: Type less, get more

Download Presentation

Microsoft ® Office Excel ® 2003 Training

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Microsoft office excel 2003 training l.jpg

CGI presents:

Microsoft® Office Excel® 2003 Training

Great Excel features


Course contents l.jpg

Course contents

  • Overview: Five great features

  • Lesson 1: Freeze!

  • Lesson 2: Compare side by side

  • Lesson 3: Sum it up, and more

(Continued on next slide.)

Great Excel features


Course contents cont d l.jpg

Course contents, cont’d.

  • Lesson 4: Type less, get more

  • Lesson 5: Call attention to the good or the bad

Each lesson includes a list of suggested tasks and a set of test questions.

Great Excel features


Overview five great features l.jpg

Overview: Five great features

  • Isn't it great when something is easier than you thought? Like keeping titles in view when scrolling down a worksheet, or adding up numbers just by selecting them?

In this course you’ll learn about five great Microsoft® Office Excel® 2003 features that will help you to work faster and easier.

Great Excel features


Course goals l.jpg

Course goals

  • Freeze the upper and left panes to keep column or row titles in view while you scroll through data.

  • Compare two workbooks at the same time by using the new Compare Side by Side feature in Microsoft Office Excel 2003.

  • Add up numbers just by selecting them.

  • Use the fill handle instead of typing to complete repetitive series of numbers, dates, or text.

  • Make formatting changes automatically when values are at a certain point by using conditional formatting.

Great Excel features


Lesson 1 l.jpg

Lesson 1

Freeze!


Freeze l.jpg

Freeze!

  • Imagine scrolling down the Excel worksheet in the picture. As you scroll, the column titles disappear. Then you wonder, do the numbers in column G refer to Units in Stock or Units on Order?

Column titles disappear upon scrolling.

Great Excel features


Freeze8 l.jpg

Freeze!

  • See the image at left.

  • Column titles in row 1.

  • Column titles disappear after scrolling down one or two rows.

Column titles disappear upon scrolling.

Great Excel features


Divide and conquer l.jpg

Divide and conquer

  • To keep titles in sight:

  • Divide or split the worksheet by freezing the titles in their own pane.

  • The title pane is then locked in place as you scroll through the rest of the worksheet.

Freeze titles in their own pane.

Great Excel features


Divide and conquer10 l.jpg

Divide and conquer

  • You can see the result in the picture. The titles, separated from the worksheet data by a horizontal line, are still visible over the top of row 10.

  • Click Freeze Panes on the Window menu.

  • Column titles are divided from the rest of the worksheet by a horizontal line.

Freeze titles in their own pane.

Great Excel features


Freeze here l.jpg

Freeze here

  • It's not just column titles that you can freeze in place. You can also freeze row titles, or you can freeze both at the same time to keep both column and row titles.

Do not select titles to freeze panes

Great Excel features


Freeze here12 l.jpg

Freeze here

  • To freeze:

  • Column titles, select the first row below the titles.

  • Row titles, select the first column to the right (for example, to keep supplier names in sight as you scroll across the worksheet).

  • Both column and row titles, click the cell that is both just below the column titles and just to the right of the row titles.

Do not select titles to freeze panes

Great Excel features


Suggestions for practice l.jpg

Suggestions for practice

  • Freeze and unfreeze column titles.

  • Freeze and unfreeze row titles.

  • Freeze both column and row titles at the same time.

  • Keep titles in sight when you print.

Online practice (requires Excel 2003)

Great Excel features


Test 1 question 1 l.jpg

Test 1, question 1

  • The Freeze Panes command is on the Format menu. (Pick one answer.)

  • True.

  • False.

Great Excel features


Test 1 question 1 answer l.jpg

Test 1, question 1: Answer

  • False.

Select what you want to freeze, and then click Freeze Panes on the Window menu.

Great Excel features


Test 1 question 2 l.jpg

Test 1, question 2

  • To freeze column titles, you would select: (Pick one answer.)

  • The row beneath the titles.

  • The second cell in the first row beneath the titles.

  • The first column.

Great Excel features


Test 1 question 2 answer l.jpg

Test 1, question 2: Answer

  • The row beneath the titles.

To freeze titles, select the row below, not the actual titles.

Great Excel features


Test 1 question 3 l.jpg

Test 1, question 3

  • To freeze both column and row titles, you would select: (Pick one answer.)

  • The second column.

  • The third column.

  • The cell that is below the column titles and to the right of the row titles.

Great Excel features


Test 1 question 3 answer l.jpg

Test 1, question 3: Answer

  • The cell that is below the column titles and to the right of the row titles.

Great Excel features


Lesson 2 l.jpg

Lesson 2

Compare side by side


Compare side by side l.jpg

Compare side by side

  • Have you ever wanted to compare the content in two different workbooks at the same time?

  • This lesson shows you how to use the new Compare Side by Side feature to compare budgets for two departments in two workbooks.

Comparing two workbooks

Great Excel features


See both workbooks at the same time l.jpg

See both workbooks at the same time

  • Imagine that you have two workbooks. One is the budget for the Sales department and the other is the budget for the Marketing department.

  • You'd like to compare both workbooks to see the differences in projected expenses between the two departments.

Compare two workbooks at the same time.

Great Excel features


See both workbooks at the same time23 l.jpg

See both workbooks at the same time

  • In the picture on the left, the Window menu shows that both workbooks are already open. Because Marketing is in view, the name of the second worksheet, Sales, is listed after the Side by Side command.

  • To see both workbooks at the same time, you click Compare Side by Side with Sales.

Compare two workbooks at the same time.

Great Excel features


Scroll through workbooks at the same time l.jpg

Scroll through workbooks at the same time

  • Worksheets from both workbooks will open, with one at the top of the window and the other in the bottom of the window.

That's right, in Excel “side by side” means one on top of the other, but you can change the orientation from one on top of the other to one next to the other if you want.

Animation: Right-click, and click Play.

Great Excel features


Scroll through workbooks at the same time25 l.jpg

Scroll through workbooks at the same time

  • Worksheets from both workbooks will open, with one at the top of the window and the other in the bottom of the window.

That's right, in Excel “side by side” means one on top of the other, but you can change the orientation from one on top of the other to one next to the other if you want.

Great Excel features


Scroll through workbooks at the same time26 l.jpg

Scroll through workbooks at the same time

  • As you scroll through the first worksheet, the second worksheet scrolls right along, keeping pace with you and making it easy to compare the differences between the two department budgets.

Great Excel features


Suggestions for practice27 l.jpg

Suggestions for practice

  • Compare two workbooks at the same time.

  • Change side by side orientation.

  • Close the side by side view.

Online practice (requires Excel 2003)

Great Excel features


Test 2 question 1 l.jpg

Test 2, question 1

  • Where is the Compare Side by Side command located? (Pick one answer.)

  • On the Window menu.

  • On the Data menu.

  • On the View menu.

Great Excel features


Test 2 question 1 answer l.jpg

Test 2, question 1: Answer

  • On the Window menu.

You can see two workbooks clearly now.

Great Excel features


Test 2 question 2 l.jpg

Test 2, question 2

  • What does side by side mean in Excel? (Pick one answer.)

  • One next to the other.

  • Facing the music together.

  • One on top of the other.

Great Excel features


Test 2 question 2 answer l.jpg

Test 2, question 2: Answer

  • One on top of the other.

That's how Excel sees it.

Great Excel features


Test 2 question 3 l.jpg

Test 2, question 3

  • You can navigate only from the top worksheet. (Pick one answer.)

  • True.

  • False.

Great Excel features


Test 2 question 3 answer l.jpg

Test 2, question 3: Answer

  • False.

You can navigate from either the top or bottom worksheet. Click in the worksheet you want to navigate in to activate the scroll bars in that sheet.

Great Excel features


Lesson 3 l.jpg

Lesson 3

Sum it up, and more


Sum it up and more l.jpg

Sum it up, and more

  • Quick: What's the sum of the selected numbers in the picture? Even if you're very fast at doing math in your head, Excel can probably get the answer before you do.

What's the total of the selected numbers?

Great Excel features


And the total is l.jpg

And the total is

  • All you have to do is...wait, the total is already in the status bar at the bottom of the window: Sum=$235.35.

As you select the numbers, Excel automatically adds them up and displays the total in the status bar.

Great Excel features


And the total is37 l.jpg

And the total is

  • See the image at left.

  • Selected numbers.

  • Total in the status bar at the bottom of the window.

Great Excel features


Want more l.jpg

Want more?

  • Need an average? Select the numbers, and right-click the status bar.

  • Click Average on the shortcut menu, which gives you the arithmetic mean.

  • The answer in the status bar changes from a sum to Average=$39.23.

Great Excel features


Suggestions for practice39 l.jpg

Suggestions for practice

  • Select numbers and see the sum.

  • Add numbers that are not consecutive.

  • Do more than sum.

Online practice (requires Excel 2003)

Great Excel features


Test 3 question 1 l.jpg

Test 3, question 1

  • How do you select numbers to add up that are scattered in different rows or columns? (Pick one answer.)

  • Press and hold down CTRL while you select each number.

  • Press and hold down SHIFT while you select each number.

  • Press and hold down CTRL+SHIFT while you select each number.

Great Excel features


Test 3 question 1 answer l.jpg

Test 3, question 1: Answer

  • Press and hold down CTRL while you select each number.

Great Excel features


Test 3 question 2 l.jpg

Test 3, question 2

  • How do you change the result from Sum to Average? (Pick one answer.)

  • Click Validation on the Data menu.

  • Right-click the status bar.

  • Click Formula Auditing on the Tools menu.

Great Excel features


Test 3 question 2 answer l.jpg

Test 3, question 2: Answer

  • Right-click the status bar.

Great Excel features


Test 3 question 3 l.jpg

Test 3, question 3

  • If you do not see the sum in the status bar, click Status Bar on the _____ menu to turn the feature on. (Pick one answer.)

  • Edit

  • Tools

  • View

Great Excel features


Test 3 question 3 answer l.jpg

Test 3, question 3: Answer

  • View.

That's where you'll find the Status Bar command.

Great Excel features


Lesson 4 l.jpg

Lesson 4

Type less, get more


Type less get more l.jpg

Type less, get more

  • Tired of typing the same lists over and over again, or copying and pasting lists from one place to another?

  • Then stop. Instead, let Excel fill in the months of the year, days of the week, and even your company's sales regions or employee names.

Don't type the same lists over and over again.

Great Excel features


Fill it up l.jpg

Fill it up

  • Imagine that you're typing the first six months of the year for the umpteenth time. Only this time you'll do it the easy way:

  • Type "January" so that Excel knows what you want.

  • Select the January cell, and then position the mouse pointer over the lower-right corner of the cell until the black cross (+) appears.

Drag the fill handle to fill in the list.

Great Excel features


Fill it up49 l.jpg

Fill it up

  • Imagine that you're typing the first six months of the year for the umpteenth time. Only this time you'll do it the easy way:

  • Drag the fill handle over the range you want to fill. As you drag, the ScreenTip tells you what will be filled in.

Drag the fill handle to fill in the list.

  • Release the mouse button to fill in the series.

Great Excel features


Make your own list for excel to complete l.jpg

Make your own list for Excel to complete

  • Excel has all sorts of lists you can complete with the fill handle: 1, 2, 3; Mon, Tues; 9:00, 10:00; Quarter 1, Quarter 2; or 15-Jan, 15-Feb.

  • But Excel can also fill in custom lists that you create, such as a list of employees at your company.

Fill in a list instead of typing the list.

Great Excel features


Make your own list for excel to complete51 l.jpg

Make your own list for Excel to complete

  • Enter the employee names once.

  • Select the names.

  • In the Options dialog box on the Tools menu, open Custom Lists.

  • Click Import.

Fill in a list instead of typing the list.

The next time you need to enter the list, type the first name, and then use the fill handle to complete the list.

Great Excel features


Suggestions for practice52 l.jpg

Suggestions for practice

  • Fill in the months of the year.

  • Fill in a list of numbers.

  • Make a new list for Excel to complete.

  • Use the fill handle to enter the same text more than once.

Online practice (requires Excel 2003)

Great Excel features


Test 4 question 1 l.jpg

Test 4, question 1

  • To use the fill handle, first select the cell, and then position the pointer over the lower-right corner of the cell until: (Pick one answer.)

  • A white cross appears.

  • The black cross (+) appears.

  • A ScreenTip tells you to drag the fill handle.

Great Excel features


Test 4 question 1 answer l.jpg

Test 4, question 1: Answer

  • The black cross (+) appears.

Great Excel features


Test 4 question 2 l.jpg

Test 4, question 2

  • You can fill up or to the left as well as down or to the right. (Pick one answer.)

  • True.

  • False.

Great Excel features


Test 4 question 2 answer l.jpg

Test 4, question 2: Answer

  • True.

You can fill in more than one direction.

Great Excel features


Test 4 question 3 l.jpg

Test 4, question 3

  • After you create a custom list of your employee names, how do you enter the entire list? (Pick one answer.)

  • Type the first name on the list, and then use the fill handle to complete it.

  • Click Custom Lists on the Tools menu.

  • Click Sort on the Data menu.

Great Excel features


Test 4 question 3 answer l.jpg

Test 4, question 3: Answer

  • Type the first name on the list, and then use the fill handle to complete it.

That's the easy (and best) way to fill a list.

Great Excel features


Test 4 question 4 l.jpg

Test 4, question 4

  • You can use the fill handle to enter the exact same text more than once. (Pick one answer.)

  • True.

  • False.

Great Excel features


Test 4 question 4 answer l.jpg

Test 4, question 4: Answer

  • True.

Great Excel features


Lesson 5 l.jpg

Lesson 5

Call attention to the good or the bad


Call attention to the good or the bad l.jpg

Call attention to the good or the bad

  • Have you ever found it hard to spot the data you're looking for because you couldn't see the forest for the trees?

  • In this lesson you'll learn how to use conditional formatting to make certain values jump out at you when the conditions are right. Then you can quickly see what you're looking for.

Conditional formatting

Great Excel features


Get ready l.jpg

Get ready

  • Imagine you want book sales under 15 copies to be formatted in bold red to really make underachieving sales stand out.

  • It's probably easy for you to spot the sale under 15 in the picture.

The Conditional Formatting command

Great Excel features


Get ready64 l.jpg

Get ready

  • Select column C (which contains the book sales numbers).

  • Click Conditional Formatting on the Format menu.

But if this workbook had lots of other rows, the other under-15 entries could get lost in the rest of the data:

The Conditional Formatting command

Great Excel features


State your conditions l.jpg

State your conditions

  • The Conditional Formatting dialog box is big, with enough boxes and buttons to be intimidating, because it's built to take in a lot of possible conditions.

The Conditional Formatting dialog box.

Great Excel features


State your conditions66 l.jpg

State your conditions

  • First, choose the cell value conditions to trigger the conditional formatting by selecting less than, and typing 15.

  • Then, click the Format button to pick bold red format for any values less than 15.

The Conditional Formatting dialog box.

Great Excel features


See the results l.jpg

See the results

  • The bold red stands out, highlighting every number that is less than 15.

  • Whether it's good or bad, rising or falling sales figures, or anything else you want to be sure to notice, call attention to cell values by using conditional formatting.

Bold red formatting stands out.

Great Excel features


Suggestions for practice68 l.jpg

Suggestions for practice

  • Make text turn red under the right condition.

  • Add another condition.

  • Get rid of conditional formatting.

Online practice (requires Excel 2003)

Great Excel features


Test 5 question 1 l.jpg

Test 5, question 1

  • The first conditional formatting step is to select data before setting up your conditions. (Pick one answer.)

  • True.

  • False.

Great Excel features


Test 5 question 1 answer l.jpg

Test 5, question 1: Answer

  • True.

This is an important step that's easy to forget. For example, if the data that you want to apply conditional formatting to is in column C, select the cells in column C before you start.

Great Excel features


Test 5 question 2 l.jpg

Test 5, question 2

  • Where is the Conditional Formatting command located? (Pick one answer.)

  • On the Format menu.

  • On the View menu.

  • On the Tools menu.

Great Excel features


Test 5 question 2 answer l.jpg

Test 5, question 2: Answer

  • On the Format menu.

Great Excel features


Test 5 question 3 l.jpg

Test 5, question 3

  • You can use up to two conditions in a cell or a given range of cells. (Pick one answer.)

  • True.

  • False.

Great Excel features


Test 5 question 3 answer l.jpg

Test 5, question 3: Answer

  • False.

Click the Add button in the Conditional Formatting dialog box to add up to two additional conditions to the original conditional formatting.

Great Excel features


Test 3 question 4 l.jpg

Test 3, question 4

  • To get rid of conditional formatting, select the column it is in, and then: (Pick one answer.)

  • On the Format menu, click Conditional Formatting.

  • Right-click, and then select Conditional Formatting.

  • Right-click, and then select Delete Conditional Formatting.

Great Excel features


Test 3 question 4 answer l.jpg

Test 3, question 4: Answer

  • On the Format menu, click Conditional Formatting.

Then click Delete, and select the check boxes for the conditions you want to delete.

Great Excel features


Quick reference card l.jpg

Quick Reference Card

  • For a summary of the tasks covered in this course, view the Quick Reference Card.

Great Excel features


  • Login