Download T2 Basic Skills Using Excel and more Lecture notes Printing in PDF only on Docsity!
T2-2 * Plug-In T2 Basic Skills Using Excel
P L U G - I N
T
Basic Skills Using Excel
1. Describe how to open, close, and save an Excel workbook.
2. Explain how to insert and delete an Excel worksheet.
3. Describe how to insert, delete, merge, and split cells in an Excel worksheet.
4. Explain how to set up a worksheet in Excel for printing.
5. Describe how to insert and delete rows and columns in an Excel worksheet.
6. Explain how to create and edit formulas in Excel using the formula bar.
7. Describe how to create a chart using Excel.
Introduction to Excel Microsoft Excel is a spreadsheet program that enables you to enter, manipulate, calcu- late, and chart data. An Excel file is referred to as a workbook , which is a collection of worksheets. Each worksheet is comprised of rows and columns of data that you can perform calculations on. It is these calculations that make Excel such a powerful tool. You can use Excel for a wide variety of purposes, from calculating payments for a personal loan, to creating a personal budget, to tracking employee sales and calcu- lating bonuses for your business. This plug-in introduces the basics of using Excel. It is designed to show you the nuts and bolts, along with a few fancy features, to get you off to a good start using the pro- gram. However, you should review the CD, MISource , which accompanies this text for additional material, animated tutorials, and simulated practice files that go beyond what we cover in the text. Figure T2.1 displays all the tasks and lessons that are provid- ed on the MISource CD. This plug-in will focus on the following six areas:
1. Workbooks and worksheets.
2. Working with cells and cell data.
3. Printing worksheets.
4. Formatting worksheets.
5. Formulas.
6. Working with charts and graphics.
LEARNING OUTCOMES
Plug-In T2 Basic Skills Using Excel * T2-
FIGURE T2. MISource Excel Lessons
(Continued)
MISource CD Microsoft Excel Lessons
Introduction to Excel Printing Worksheets
■ Introduction to Excel 2003 ■ Setting up the page for printing ■ Opening and closing workbooks ■ Setting up margins for printing ■ Creating workbooks ■ Setting up the sheet for printing ■ Using templates ■ Setting and clearing the print area ■ Creating folders for saving workbooks ■ Previewing a print area ■ Saving a workbook in a new format ■ Printing selections, worksheets, and ■ Using the research tool workbooks
Managing Workbooks Analyzing Data
■ Inserting worksheets ■ Using Autofilter ■ Deleting worksheets ■ Creating and sorting lists ■ Hiding and unhiding worksheets ■ Adding functions to formulas using the ■ Moving and copying worksheets Autosum button ■ Formatting worksheets ■ Entering formulas ■ Splitting and arranging workbooks ■ Creating and editing formulas using the ■ Arranging and hiding/unhiding formula bar ■ Entering a range in a formula by dragging ■ Using absolute and relative references ■ Using the insert function feature ■ Creating formulas using the SUM function ■ Creating formulas using the MIN and MAX functions ■ Creating formulas using the DATE or NOW function ■ Creating formulas using the PMT function ■ Creating formulas using the IF function
Working with Cells and Cell Data Working with Charts and Graphics
■ Inserting cells ■ Creating a chart using the chart wizard ■ Deleting cells ■ Modifying charts ■ Merging and splitting cells ■ Moving a chart ■ Cutting, copying and pasting cells ■ Adding graphics ■ Moving selected cells using ■ Modifying graphic properties drag-and-drop ■ Positioning graphics ■ Entering text in cells ■ Using the fill handle tool editing text in cells ■ Clearing cell content ■ Finding and replacing cell content ■ Using go to ■ Finding and replacing formatting in cells ■ Changing the look of cells ■ Using format painter ■ Applying number formats ■ Applying styles
close the workbook without saving your latest changes. Click Cancel to keep the workbook open.
CREATING WORKBOOKS USING TEMPLATES
A template is a file with predefined settings that you can use as a starting point for your workbook. An Excel template makes creating a new workbook easy and results in a professional appearance. Some examples of workbook templates are Balance Sheet, Sales Invoice, and Loan Amortization. To create a workbook using a template:
1. Click New... on the File menu.
2. Click On my computer... in the Templates area of the New Workbook task pane.
3. Click the Spreadsheet Solutions tab in the Templates dialog box.
4. Click a template icon to see a preview in the right pane (see Figure T2.3).
5. Click OK.
SAVING A WORKBOOK
Sometimes when you are saving a workbook, you will want to create a new folder, where you can later save other, similar workbooks. You can create this new folder at the same time you save the workbook. To create a new folder:
1. Click Save As... on the File menu.
2. Click the Create New Folder button to the right of the Save in: box.
3. Enter the name for the new folder in the dialog box that appears.
4. Click OK.
5. Enter the name for the file in the File name: box.
6. Click the Save button.
You can also use the Save As dialog box to save the workbook with a new name (refer to Figure T2.4):
1. Click Save As... on the File menu.
2. Click in the File name: box.
3. Type in the new file name.
4. Click the Save button in the Save As dialog box.
Plug-In T2 Basic Skills Using Excel * T2-
FIGURE T2. Workbook Template
Click the Spreadsheet Solutions tab.
Click a template icon to see a preview.
Preview appears here.
INSERTING AND DELETING WORKSHEETS
When you create a new workbook, it contains three worksheets. However, a work- book can contain as many worksheets as you need. To add a worksheet:
1. Right-click on any Sheet tab.
2. Select Insert... from the shortcut menu.
3. To insert a simple worksheet, click the Worksheet icon in the dialog box.
4. To insert a formatted worksheet, click the Spreadsheet Solutions tab, and click
any of the template icons.
5. Click OK (see Figure T2.5).
You can insert more than one worksheet at once. First, select the number of worksheets you want to add. Next, right-click and se- lect Insert... Click the Worksheet icon , and click OK. Sometimes you may need only one worksheet in your workbook. Limiting the worksheets in your work- book to those that contain information can make your workbook appear organized and professional. To delete a worksheet:
1. Right-click on a Sheet tab.
2. Select Delete from the menu.
You can delete more than one worksheet at once. First, select all the sheet tabs you want to remove by holding the CTRL key down and clicking on the Sheet(s) tabs you wish to delete. Next, right-click and select Delete. If a worksheet contains data, Excel will display a dialog box, warning that the sheet may con- tain data and asking if you are sure you want to perma- nently remove it from your workbook.
T2-6 * Plug-In T2 Basic Skills Using Excel
FIGURE T2.
Saving a Workbook
Verify location to save in here.
Type your new name here. Click here to select the format for your file.
FIGURE T2.
Inserting a New Worksheet
Click the Worksheet icon.
Click here to insert the new worksheet.
CUTTING, COPYING, AND PASTING
CELLS
The Cut, Copy, and Paste commands are used to move data and other items within a work- book and between applications. Data that is cut is removed from the document and placed on the Clipboard for later use. The Copy com- mand places a duplicate of the selected data on the Clipboard without changing the workbook. The Paste command is used to insert items from the Clipboard into a workbook. To cut or copy data within a workbook:
1. Select the cell or cells you want to cut or
copy.
2. Click the appropriate toolbar button:
a. Cut or
b. Copy
c. The cell appears with a flashing dotted
line around it.
3. Place the cursor where you want to insert data from the Clipboard.
4. Click the Paste toolbar button (see Figure T2.9).
When you cut or copy items, they are placed on the Clipboard. The Clipboard can store up to 24 items for use in the current document or any other applica- tion. You can view the contents of the Clipboard at any time by opening the Edit menu and selecting Office Clipboard.... The icons in the Clipboard identify the type of document from which each item originated (Word, Excel, Paint, etc.). A short description of an item will appear when you select it or move the cursor over its icon.
ENTERING TEXT IN CELLS
Without text headers, descriptions, and instructions, your workbook would consist of numbers and formulas without any structure. Adding text headers to your rows and columns creates the structure for you to enter data into your workbook. To add text to your workbook:
1. Click in the cell in which you want to add text.
2. Type the text.
3. Click outside the cell to accept your entry.
APPLYING NUMBER FORMATS
Formatting your numbers changes the appearance of the data in your worksheet, but does not change its value. The formatted number is displayed in the cell, and the actual value is displayed in the formula bar. Excel provides several numeric for- mats for you to use in your workbook, including currency, percentage, date, time, and accounting. To format numbers:
1. Select the cells you want to format.
2. Click Cells... on the Format menu, and click the Number tab.
3. In the Category: list, click the format you want to use (see Figure T2.10).
T2-8 * Plug-In T2 Basic Skills Using Excel
FIGURE T2.
Copying and Pasting Cells
Cut Copy Paste
A cell that you have copied displays a dotted line around it.
Under each number category, you can choose pre- defined formatting or create and edit formats of your own. The Formatting toolbar allows you to add default number styles. Select the cell you want to format, and then do one of the following:
■ To add the default currency style, click the Currency Style button.
■ To add the default percent style, click the Percent Style button.
■ To add the default comma style, click the Comma Style button.
APPLYING STYLES
A style is the combination of effects that can be applied at one time. Styles can include formatting such as character effects, background color, typefaces, and number formatting. Excel comes with predefined styles including Currency, Comma, and Percent styles, but also gives you the ability to create your own styles in the Style dialog box. To apply a basic style:
1. Select the cells you want to format.
2. Click Style... on the Format menu.
3. Click the arrow next to the Style name box and select the style you want (see Fig-
ure T2.11).
4. Review the effects included in the style (click the Modify... button to make any
changes to the style).
5. Click OK.
Printing Worksheets
SETTING UP THE PAGE FOR PRINTING
You may find that your worksheet is too wide to print on one sheet of paper, even with landscape orientation. Excel allows you to adjust how your worksheet will print. In the dialog box, you can adjust the scale of your worksheet, making it small- er and forcing it to fit on one page, or you can print your worksheet across multiple pages by changing the Fit to: options. To set up a page to print:
1. Click Page Setup... on the File menu.
■ To print your information on one page, adjust the Scaling option. ■ To print your information across multiple pages, change the Fit to: options.
2. Click the Print Preview button to see what your
printed worksheet will look like (see Figure T2.12).
Plug-In T2 Basic Skills Using Excel * T2-
FIGURE T2. Applying Number Formats
Select a Category on the Number tab.
View a sample of the format.
Click here to apply the format to the selected cells.
FIGURE T2. Applying Styles
The Margins tab also allows you to ad- just the placement of the header and foot- er. Further, you can choose to horizontally and/or vertically center the information on the printed page.
PREVIEWING A PRINT AREA
The Print Preview window shows you a re- duced version of your worksheet as it will appear when printed. Save time and print- ing by always checking your layout in Print Preview before you print. Use this view to see how your information fits on each page and to verify such things as place- ment of page numbers, headers, and col- umn and row labels. To preview your worksheet before print- ing it:
1. Click the Print Preview button on the
standard toolbar.
2. When the Print Preview window opens,
scroll the window to view the pages.
3. Notice that the mouse pointer has
changed to a magnifying glass. Click on a portion of the document to see it full- size (see Figure T2.14).
4. Click again to reverse the magnification.
5. Click the Next and Previous buttons to view the pages of your workbook.
6. To return to Normal view, click Close on the Print Preview toolbar.
You can adjust page breaks, page setup, margins, headers and footers, and other page options from the Print Preview window. You can also print the work- sheet from the Print Preview window by clicking the Print button to bring up the Print dialog box.
PRINTING SELECTIONS, WORKSHEETS, AND WORKBOOKS
Printing a print area and printing your worksheet or workbook operate the same way as other Microsoft Office applications. Use the Print dialog box to check your print settings before printing. Be sure your printer’s name is displayed in the sec- tion, and select the number of pages you want to print in the Print range section. Remember, if you have set a Print Area, then only that part of your worksheet will print. To check your print settings and print:
1. Click Print... on the File menu.
2. Verify that the correct printer name is displayed in the Printer section.
3. Verify that All is selected in the Page range section (see Figure T2.15).
4. Click OK.
In the Print dialog box, you can also specify to print the selection, the entire work- book, or just the active worksheet.
Plug-In T2 Basic Skills Using Excel * T2-
FIGURE T2. Previewing a Print Area
Your page appears in miniature.
Click here to return to normal view.
Click the cursor/magnifying glass to zoom in on an area.
Formatting Worksheets
INSERTING ROWS AND COLUMNS
You may need to add rows or columns of new information into the middle of your workbook. To insert a row:
1. Place your cursor in a cell in the row below where you want the new row.
2. Click Rows on the Insert menu.
To insert a column:
1. Place your cursor in a cell in the column to the right of where you want the new
column.
2. Click Columns on the Insert menu (see Figure T2.16).
When you insert a row or column, a smart tag will appear. Click the smart tag to choose formatting options— Same as left , Same as right , or Clear Formatting.
DELETING ROWS AND COLUMNS
When you delete a row or column, you are removing all of those cells from your workbook. Once you have deleted the row or column, it disappears and the rest of the columns and rows move to replace it. To delete a row or column:
1. Select the row or column you want to delete.
2. Click Delete on the Edit menu (sees Figure T2.17).
Be careful. If you delete a row or column containing data, that data will be lost.
MODIFYING ROW HEIGHTS
When you first enter data in your workbook, Excel automatically sets the rows of your worksheet according to preferences. You may want to make rows a different height from this default setting.
T2-12 * Plug-In T2 Basic Skills Using Excel
FIGURE T2.
Preparing to Print
Verify the correct printer name is displayed here.
Select the number of copies to print here.
Select which pages to print in this section.
MODIFYING COLUMN WIDTHS
When you first enter data in your workbook, Excel automatically sets the widths of the columns. As you type data into multiple columns, you may find that Excel does not display all the text in a cell. You can change the widths of columns in your workbook so that all your information is displayed. To modify column widths:
1. Select the column or columns you
want to change.
2. Drag the boundary until the column is
the width you want (see Figure T2.19). To change all the columns in your work- sheet to the same width, point to Column on the Format menu, then click Standard Width... or click the Select All button (the gray box above Row 1 and to the left of Col- umn A) and then drag the boundary. To make the column automatically fit the contents of the selected cell, double-click the boundary to the right of the column (or select AutoFit Selection from Column on the Format menu).
T2-14 * Plug-In T2 Basic Skills Using Excel
FIGURE T2.
Modifying Row Heights
When the cursor changes to this shape, drag down to desired height.
As you drag, a tooltip indicates the current height.
FIGURE T2.
Modifying Column Widths
What the cursor changes to this shape, drag across to desired width.
As you drag, a tooltip indicates the current width.
Formulas
ENTERING FORMULAS
A formula is an equation that performs calculations between cells in a worksheet or table. A formula always begins with an equal sign. A simple formula may contain cell references and operators. To enter a formula:
1. Click the cell in which you want to enter the formula.
2. Type = (an equal sign).
3. Type the formula.
4. Click outside the cell or press Enter (see Figure T2.20).
If a formula has more than one operator, Excel will perform mathematical oper- ations in this order:
■ Exponentiation
■ Multiplication and division
■ Addition and subtraction
Adding parentheses around an operation will override this order, forcing Excel to perform calculations within the parentheses first.
USING THE FORMULA BAR
To enter a formula in the formula bar:
1. Select the cell in which you want to add the formula.
2. In the formula bar, type an equal sign (=).
Plug-In T2 Basic Skills Using Excel * T2-
FIGURE T2. Entering a Formula
Every formula begins with an equal sign.
Cell references provide the values to be used.
The cell that contains the formula.
Formula bar
Insert Function button
4. Enter the range of cells that you want to add.
5. Click OK (see Figure T2.23).
When you click an argument box, a description of the argument appears below the description of the function. An argument is a name for a value, expres- sion, or cell reference that is passed to the function for its use in calculating an answer. In addition, as you enter arguments, the dialog box will display the results of your formula. If the SUM function is not in your list of most re- cently used functions, click the arrow next to the Or select a category: box, click Math & Trig , and select SUM from that list of functions.
USING THE MIN AND MAX FUNCTIONS
The MIN (minimum) statistical function will give you the smallest value in a range of values. The MAX (max- imum) statistical function will give you the largest value in a range of values. These functions look like this:
MIN function: =MIN(A3:A6) MAX function: =MAX(A3:A6) To use the MIN and MAX functions:
1. Select the cell in which you want to enter the function.
2. Click the Insert Function button.
3. Click MIN or MAX from the list of Most Recently Used or Statistical functions
and click OK.
4. If necessary, enter the range of cells.
5. Click OK (see Figure T2.24).
If the MIN or MAX functions are not in your list of most recently used functions, click the arrow next to the Or select a category: box, click Statistical , and select MIN or MAX from that list of functions. When you click an argument box, a description of the argument appears below the description of the function. Also, as you enter arguments, the dialog box will display the results of your formula. By de- fault, Excel will enter a range of contiguous cells for you.
USING THE DATE OR NOW FUNCTION
Use the Date & Time function or the NOW function to insert the date and time into your workbook. The date and time will be displayed at all times, but will only be updated when the worksheet is calcu- lated. The NOW function looks like this:
=NOW() To use the NOW function:
1. Select the cell in which you want to enter the
function.
2. Click the Insert Function button.
Plug-In T2 Basic Skills Using Excel * T2-
FIGURE T2. Using the SUM Function
Excel computes the formula for you.
SUM function dialog box.
Number values of cells display here.
FIGURE T2. Using the MIN and MAX Function
Excel computes the formula for you.
MIN function dialog box
Number values of cells display here.
3. Click NOW from the list of Most Recently Used or Date
& Time functions and click OK.
4. The NOW function takes no arguments.
5. Click OK (see Figure T2.25).
The NOW function uses the computer’s system clock to determine the date and time.
Working with Charts and
Graphics
CREATING A CHART USING THE CHART WIZARD
A chart is a visual representation of data from your work- book. Charts add a visual element to your workbook and help convey the information in a simple, easy to under- stand manner (see Figure T2.26). The Chart Wizard in Excel walks you through the steps of converting the data in your workbook into a chart. To use the Chart Wizard:
1. Select the data you want to display in your chart.
2. Click the Chart Wizard button on the Standard toolbar.
3. Select the chart type and click Next.
4. Select the data range for your chart and click Next.
5. Add or modify the chart options and click Next.
6. Place the chart , either in a new worksheet or in an existing worksheet.
7. Click the Back button to go back to earlier screens in the wizard and make any ad-
justments you want.
8. When you are satisfied with your chart, click Finish.
The wizard allows you to add titles, legends, labels, and other information to your chart. You can create a chart on its own or as part of a worksheet. To place a chart in its own worksheet, click the As new sheet in: op- tion in the Chart Wizard.
MODIFYING CHARTS
When you modify a chart, you can change any of the op- tions that belong to that chart type. Modifying a chart al- lows you to change the text of the chart and how it appears on the chart. This includes titles, legends, axes, data labels, and data tables (refer to Figure T2.27). To change chart elements:
1. Select the chart you want to modify and select Chart
Options... from the Chart menu. ■ Select the Titles tab to add or change the title of the chart and the titles for the axes. ■ Select the Axes tab to change the display of the axes for the chart. ■ Select the Gridlines tab to change the display of the gridlines in the chart.
T2-18 * Plug-In T2 Basic Skills Using Excel
FIGURE T2.
Using the NOW() Function
Click here to open the NOW dialog box.
The NOW function takes no arguments.
Results of the NOW function display here.
FIGURE T2.
An Excel Chart
Cells A5 : A8 and B4 : B8 are selected.
Cells B5 : B8 display as bars in the chart.
Chart Wizard Button.
Cells A5 : A8 display as bar titles.
d. AutoShapes inserts predefined banners, arrows, and callouts as drawing
objects.
e. WordArt... inserts text effects as drawing objects.
f. Organization Chart inserts a flowchart that you can add text to and modify.
The Clip Art task pane allows you to search for different kinds of clips from many different sources. There are several ways to narrow your search: ■ Click the Search in: arrow to limit the search to your files, office files, or Web files. ■ Click the Results should be: arrow to specify the exact type of media clip to search for. ■ Click the Organize clips... link at the bottom of the task pane to browse through specific collections and organize the clips you use most frequently.
T2-20 * Plug-In T2 Basic Skills Using Excel
FIGURE T2.
Adding Graphics
P L U G - I N S U M M A R Y
M A K I N G B U S I N E S S D E C I S I O N S
Plug-In T2 Basic Skills Using Excel * T2-
M
icrosoft Excel is a general-purpose electronic spreadsheet used to organize, cal- culate, and analyze data. The tasks you can perform with Excel range from prepar- ing a simple invoice to managing an accounting ledger for a business. Six areas in Excel were covered in this plug-in:
1. Workbooks and worksheets.
2. Working with cells and cell data.
3. Printing worksheets.
4. Formatting worksheets.
5. Formulas.
6. Working with charts and graphics.
1. Stock Watcher
Mark Martin has created a basic stock watcher worksheet that he uses to report on gains or losses from when he purchased the stock and the last recorded date and price. Mark has given you a snapshot of his spreadsheet (see Figure T2.29) that you can use to recreate this spreadsheet for yourself. Here are some basic steps to follow:
1. Create a new workbook.
2. Enter all the information provided in Figure T2.29.
3. Apply the currency format to the respective columns.
4. The date should be entered as a function. Hint: Use the NOW() function.
5. Enter a formula for Gain/Loss (%) column. Hint: You should subtract the Last column
from the Purchase column, and then divide by the Purchase column.
6. Format for percent in the Gain/Loss (%) column.
FIGURE T2. Stock Watcher Data
