warpct.com Working with MS Excel 2003 Workbook courseware by WARP! Computer Training

(1)

Working with MS Excel 2003

Workbook

warpct.com

courseware by WARP! Computer Training

(2)

Welcome!

Thank you for evaluating a portion of this workbook. If you have any questions or comments regarding our training materials please send us email at:

[email protected]

To order your fully printable TrainPack bundle visit our SECURE Order Form:

https://warpct.com/trainpack/order.htm

You’ll be ready to start training within 24 hours.

This workbook may only be used by the organization listed at the top of each page and may only be distributed as a printed hard-copy. Do not distribute the electronic version of this document. WARP! Computer Training assumes no responsibility or liability for any errors or inaccuracies that may appear in this workbook.

Microsoft and Windows are registered trademarks of Microsoft Corporation in the U.S. and other countries.

Microsoft Office, Word, Excel, PowerPoint, Access and Outlook are trademarks of Microsoft Corporation. All other products and brand names are trademarks of their respective owners.

TrainPack: US$595

Purchase now and get every warpct.com workbook! More than 40 courseware titles covering Internet Skills, Basic Windows (XP/2000/98/95), MS Office titles, including Basic and Advanced Word, Basic and Advanced Excel, PowerPoint and Access (2003/XP/2000/97/4.3).

Legacy manuals covering Lotus Smartsuite 97 and Corel WordPerfect Suite 8 are also included.

Available for download as PDF documents directly from our server.

(3)

For evaluation purposes only Do not use for training

CHAPTER 5 - Formulas & Functions

Adding Formulas to a spreadsheet... 27

Introduction to Logical Functions... 30

IF - Logical Function ... 30

Copying and Pasting a Logical Function ... 31

Protecting a worksheet ... 32

CHAPTER 6 - Error Handling Attaching input Validation to a cell... 34

Using the IS validation functions in formulas... 37

Tracing a spreadsheet Error to its source – Formula Auditing ... 37

CHAPTER 7 - Scenario & Goal Seeking Applying a Scenario to a spreadsheet... 39

Adding a new Scenario to a spreadsheet... 39

Using Goal Seek to solve a problem ... 41

CHAPTER 8 - Pivot Tables Summarizing table data using the Pivot Table Wizard... 42

Reorganizing the fields in a Pivot Table and separating data into pages... 46

Changing the way Pivot Table data is summarized ... 47

Displaying Pivot Table pages as separate worksheets ... 48

Generating a Chart from Pivot Table data... 49

CHAPTER 9 - Simple Database Management Sorting a database... 51

Using AutoFilter ... 52

Removing an AutoFilter ... 53

Specifying a Custom AutoFilter ... 53

Exiting from AutoFilter mode ... 54

Using Advanced Filters... 54

Advanced Filters to copy filtered records to another worksheet... 55

Viewing a database with a Form ... 56

Using Subtotals to summarize data... 57

Hiding detailed data using Outlines ... 58

Removing Subtotals from a worksheet... 58

Consolidating data ... 58

CHAPTER 10 - Macro Basics Creating simple Macros ... 61

Keyboard Shortcut ... 61

Recording a Macro ... 61

Running a Macro ... 62

Displaying the Forms toolbar ... 63

Creating a Button and attaching a Macro to it ... 63

Copying and modifying a Button... 64

(5)

For evaluation purposes only Do not use for training

Introduction

Welcome to the WARP! Computer Training Advanced MS-Excel 2003 workbook.

This course is designed for people who have a basic familiarity with Excel 2003, Microsoft’s professional spreadsheet package, and the Windows 2000/XP Operating System. It provides the user with a set of skills necessary to tackle some of the more advanced features of Excel 2003.

The workbook provides a step-by-step tutorial equally suitable for instructor-led training or self-paced learning. New computer skills can sometimes be difficult to master so, no matter how you choose to learn about Excel 2003, we encourage you to spend some time working through these exercises by yourself, outside of any structured training course. You will find that revision will reinforce your new skills, and will help you enormously when the time comes to apply them.

Good luck and happy learning. We hope you enjoy this workbook.

(6)

For evaluation purposes only Do not use for training

1 1

Worksheets, ’books & ’spaces

Let’s start by looking at a few of the things you should know before you tackle some of Excel’s more demanding features:

Working with Worksheets

The tabs at the bottom of your Excel window give you access to the different worksheets in your Excel workbook. The number of worksheets you can have in a workbook is limited by your computer’s available memory. You switch between the different worksheets in your workbook by clicking the worksheet tabs.

• Start Excel 2003

• Click the Close button on the Getting Started task pane

Excel will display the default workbook. This is a workbook named Book1 with three empty worksheets:

• Click on the tabs at the bottom of the screen to switch between worksheets

Let’s look at a few essential worksheet management skills:

Exercise Switching between Worksheets in a Workbook

(7)

For evaluation purposes only Do not use for training

Renaming a Worksheet

There are a number of different ways you can give a new name to a worksheet. Double-clicking on the worksheet’s tab is probably the easiest:

• Double-click the worksheet tab named Sheet1. This will highlight the name. (Alternatively, select the Format…

Sheet…Rename menu item.)

• Type in the new name. The example above shows a worksheet being given the name Expenses.

• Hit the [Enter] key on your keyboard

To Rename a worksheet using the shortcut menu:

1. Right-click on the worksheet tab

2. Select Rename from the shortcut menu

Inserting a new Worksheet

Three worksheets will appear every time you open up a new workbook with Excel. You can delete unwanted worksheets and insert new worksheets if you find that you need more or less than three. To Insert a new worksheet into the currently open workbook:

• Select the Insert…Worksheet menu item. A new blank worksheet will be inserted before (to the left of) the currently selected worksheet.

Exercise Renaming a Worksheet

Exercise Inserting a new Worksheet

(8)

For evaluation purposes only Do not use for training

Alternatively, right-click on the Expenses tab and select Insert from the shortcut menu. An insert dialog box will appear:

1. Click on the General tab 2. Click on the Worksheet icon 3. Click the OK button

Deleting an unwanted Worksheet

To Delete an unwanted worksheet:

• Select the unwanted worksheet (Sheet4) by clicking on its

• Select tab the Edit…Delete Sheet menu item

Alternatively, right-click on the worksheet tab and select Delete from the shortcut menu.

Moving and Copying Worksheets

You can change the order in which your worksheet tabs appear at the bottom of your workbook:

• Click and hold the mouse button over the worksheet tab you want to move

Exercise Deleting a Worksheet

Exercise Moving a Worksheet

(9)

For evaluation purposes only Do not use for training

• Change the position of your worksheet (Expenses) by dragging its tab before or after the other worksheet tabs.

A small triangle will appear telling you where your sheet will be inserted:

• Drop the worksheet into its new position by releasing the mouse button

• To Copy a whole worksheet and place the copy in the same workbook, simply hold the [Ctrl] key while you drag-and-drop. You will notice that a small + sign will be added to the move pointer, telling you that you are now moving a copy, and not the original worksheet.

To Move or Copy a worksheet using the shortcut menu, right-click on the worksheet tab and select Move or Copy from the shortcut menu.

1. Select where the worksheet will be situated with reference to the other worksheets from the Before sheet: box 2. Click the OK button if you are moving a

worksheet

3. If you are copying a worksheet, click the Create a copy check box and then click the OK button

Exercise Copying a Worksheet

(10)

For evaluation purposes only Do not use for training

Selecting more than one Worksheet

You can make the above changes, as well as other editing and formatting changes, to more than one worksheet at a time. For example, you can delete several sheets simultaneously or enter the same information onto multiple worksheets.

To select more than one individual worksheet:

• Click on a worksheet tab

• Hold down the [Ctrl] key on your keyboard and click on the other worksheet tabs

To select a block of consecutive worksheets:

• Click on a worksheet tab

• Hold down the [Shift] key on your keyboard and click on the worksheet tab at the end of the block

To select all of the worksheets:

• Right-click on a worksheet tab

• Select Select All Sheets from the shortcut menu To deselect (or ungroup) any of the above selections:

• Right-click on a worksheet tab

• Select Ungroup Sheets from the shortcut menu OR

• Simply click on another worksheet tab

Changing the default number of blank Worksheets

Whenever you start Excel, a new workbook with three blank worksheets will be created. This also occurs when you click the New button on the toolbar (selecting the File…New menu item opens the New Workbook task pane). To change the default number of worksheets in a new workbook:

• Select the Tools…Options menu item

• Click on the General tab

• Change the value in the Sheets in new workbook: box

• Click the OK button

Every new workbook will then include the specified number of blank worksheets.

Exercise Selecting more than one Worksheet

Exercise Changing the default number of Worksheets in a new Workbook

(11)

For evaluation purposes only Do not use for training

Working with Workbooks

You can have two or more Workbooks open at any one time whilst using Excel. You can reference one or more cells in other workbooks in much the same way as you reference cells in other worksheets. Try this exercise:

• Close down the workbook named Book1 (do not save the changes)

• Insert your sample disk into the A:\ floppy disk drive

• Click the Open button on the Excel Standard toolbar

• Open the Scenario.xls workbook from the A:\ floppy drive

• Repeat the above procedure to open the bodystats.xls workbook from the A:\ floppy drive

You now have two workbooks open at the same time. This is sometimes useful if you have information you want to exchange between spreadsheets which are stored in separate workbook files. To switch between workbooks:

• Select the Window menu item The names of currently open Workbooks appear as menu items in the Window menu. The

workbook being viewed at the moment has a tick beside it.

• Switch to another workbook by clicking on its name

Working with Workspaces

Imagine that you are constructing a spreadsheet, and find that the information you need to include is stored in two or more workbook files.

You may think that every time you want to work on your spreadsheet you will need to open up each workbook file separately. You do not need to do this if you save your spreadsheet as a Workspace.

Workspaces are files that simply group the names of all currently open workbooks. As an example, let’s save both the Scenario.xls and bodystats.xls spreadsheets as a workspace:

Exercise Opening multiple Workbooks

Exercise Switching between open Workbooks

(12)

For evaluation purposes only Do not use for training

• Select the File…Save Workspace menu item

• Call your workspace file My Project by typing this name into the File name: box

• Save this file to your A:\ floppy disk

• Close the two workbooks Scenario.xls and bodystats.xls

The next time you want to work on your project you can open up the Workspace file named My Project.xlw, which will automatically open the workbooks you chose to group. This saves you the task of opening up these workbooks individually.

• Open My Project.xlw from your disk

Workspaces are saved with the file extension .xlw. It is important to realize that Saving a Workspace DOES NOT save new information in any of its workbooks. You need to save your workbooks whether they are included in a workspace or not.

• Close the two workbooks Scenario.xls and bodystats.xls

Exercise Saving a Workspace

(13)

For evaluation purposes only Do not use for training

2 2

Cell References

Relative and Absolute cell references

You have used Relative cell references in your formulas up until now (in the Introduction to Excel workbook). Relative cell references look like this: A1. Let’s also have a look at Absolute cell references, which look like this: $A$1.

Relative cell references change when the formulas they are in are copied or moved.

• Click the New button on the toolbar to open a new blank workbook

• Hit the [Ctrl] and [ ` ] keys on your keyboard (the [ ` ] key is to the left of the number [1] key). This will toggle your worksheet between displaying the formulas in the cells and the results of those formulas.

• Type =A1 into cell B2 and hit the [Enter] key on your keyboard

• Copy cell B2 by highlighting it, hitting the [Ctrl] and [C]

keys together on your keyboard, moving down one cell (to B3) and then hitting the [Enter] key

• Use the above method to copy the formula into cells C2 and C3

Notice how the formula changes. Moving it down one row will result in the row reference being increased by one. Moving it across one column will result in the column reference being increased by one.

Absolute cell references refer to the cell in that EXACT location.

Absolute cell references in Excel are specified by prefacing both the column letter and the row number by the dollar sign $.

• Change cell B2 to read =$A$1

• Copy this formula into cells B3, C2 and C3

Notice how the formula does not change. This is very useful when you want to copy a formula and keep the original cell references intact.

Exercise Watching how a Relative Cell Reference changes when it is moved

Exercise Absolute Cell References

(14)

For evaluation purposes only Do not use for training

You can also reference cells using a combination of Relative and Absolute reference styles. These would then be called Mixed cell references:

Mixed cell references, like A$1 and $A1, keep either the row or column part of the reference absolute, and the other part relative.

• Change the formula in cell B2 to read =$A1

• Copy this formula into cells B3, C2 and C3

Notice how the column part of the reference stays constant, while the row part changes. The $ sign has indicated that the columns are absolute while the rows should be treated as relative.

Changing the cell reference types automatically

Excel includes a feature that lets you change the cell references automatically. Try this exercise:

• Type =A1 into cell B2

• Highlight the formula in this cell. One way to do this is by dragging over the formula in the Formula Bar.

• Hit the [F4] function key on your keyboard

When a formula is highlighted, hitting [F4] toggles its cell references between the four different combinations of Absolute, Relative and Mixed.

Defining Cell Ranges and Cell Unions in formulas

You have, no doubt, specified Cell Ranges in formulas a number of times while using Excel. Take the task of summing a column of figures, for example:

• Type some figures into a column and, in the final row, click the AutoSum button on the toolbar

Notice how the Sum formula specifies a Cell Range to act on. In the example, A1:A6 specifies the range of cells between A1 and A6 inclusive.

Exercise Mixed Cell References

Exercise Changing the Cell Reference Types in a formula automatically

Exercise Specifying a Cell Range

(15)

For evaluation purposes only Do not use for training

Cell Ranges are specified in formulas by including the first and last members of a range, separated by a Colon : All of the cells between the two extremes will be selected.

Cell Unions, on the other hand, are used to include specific cells or cell ranges. This is very useful for skipping over spreadsheet data you do not want included in a formula:

• In cell A7, type =sum(a1, a3:a5) This will include cells A1, A3, A4 and A5.

• Click on this formula to see which cells are included (click once in Formula Auditing mode [Ctrl] + [ ` ] or double-click when in normal Values mode).

Cell Unions are specified by including all the cells and cell ranges you want to work with, separated by Commas ,

3-D References

References like $A$1 and C4:F7 are known as two-dimensional references because they deal with the Columns and Rows of one worksheet at a time. 3-Dimensional references allow you to reference

cells, ranges and unions in other worksheets and workbooks.

3-D References are extremely powerful. Try this exercise:

• Open the workbook file named Scenario.xls from your floppy disk on A:

• Open the workbook file named Tax.xls from your floppy disk on A:

The Tax.xls workbook is an example of a spreadsheet which calculates a person’s income tax.

• Type a figure into cell B2

Excel will put the number into a suitable tax bracket and will calculate the Tax Payable and Net Income.

Exercise Specifying a Cell Union

(16)

For evaluation purposes only Do not use for training

Let’s find the tax payable on Operating Income from the Scenario.xls workbook:

• In the Tax.xls workbook, type = into cell B2

• Switch to the Scenario.xls workbook by clicking on its name under the Window menu (alternatively, click on its button on the Windows Taskbar)

• Click on cell C13 (Operating Income)

• Hit the [Enter] key on your keyboard

The formula in cell B2 is a 3D Reference which points to the absolute cell reference $C$13 in the worksheet named Sheet1 which is in the workbook named Scenario.xls.

The format of this type of External Reference is as follows:

[Workbook.xls]worksheet!$A$1

Note that the name of the referenced workbook appears in square brackets and that the cell reference is separated from the name of the worksheet by an exclamation mark !

To reference cells, ranges and unions on a worksheet in the same workbook is a simple matter of removing the name of the workbook file:

worksheet!$A$1

• Close the open workbooks (do not save the changes) Exercise

Specifying a 3D Reference to a cell in another workbook (External Reference)

warpct.com Working with MS Excel 2003 Workbook courseware by WARP! Computer Training

Working with MS Excel 2003

Workbook

warpct.com

courseware by WARP! Computer Training

Welcome!

Thank you for evaluating a portion of this workbook. If you have any questions or comments regarding our training materials please send us email at:

[email protected]

To order your fully printable TrainPack bundle visit our SECURE Order Form:

https://warpct.com/trainpack/order.htm

TrainPack: US$595

For evaluation purposes only Do not use for training

Contents

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training

Introduction

For evaluation purposes only Do not use for training

1 1

Worksheets, ’books & ’spaces

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training

2 2

Cell References

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training

For evaluation purposes only Do not use for training