Creating Templates in Excel

Document Sample
Creating Templates in Excel Powered By Docstoc
					Advanced Excel 97

LOADING EXCEL....................................................................................................................................... 3 TO LOAD AN EXISTING SPREADSHEET............................................................................................. 3 EXCEL SCREEN ......................................................................................................................................... 3 ENTERING A FORMULA.......................................................................................................................... 4 USING THE FUNCTION WIZARD .......................................................................................................... 4 USING AUTOSUM FUNCTION ................................................................................................................ 4 RANGE FINDERS ....................................................................................................................................... 4 MORE WITH FORMULAS ........................................................................................................................ 5 ABSOLUTE VS. RELATIVE REFERENCE ........................................................................................................ 5 TO MAKE A CELL REFERENCE ABSOLUTE .................................................................................................. 5 LOGICAL OPERATORS ................................................................................................................................. 5 DATE AND TIME FORMULAS ....................................................................................................................... 5 UPPER / PROPER TEXT ................................................................................................................................ 5 IF ................................................................................................................................................................ 5 AND / OR .................................................................................................................................................. 6 COUNTIF .................................................................................................................................................. 6 SUMIF ....................................................................................................................................................... 6 COPYING A FORMULA OR INFORMATION TO THE RIGHT OR DOWN.................................... 6 COPYING INFORMATION NOT ADJACENT TO THE CURRENT INFORMATION.................... 6 MOVING INFORMATION BETWEEN CELLS ..................................................................................... 6 MAKING A COLUMN WIDER ................................................................................................................. 7 INSERTING ROWS OR COLUMNS......................................................................................................... 7 PRINTING .................................................................................................................................................... 7 PAGE BREAK PREVIEW .......................................................................................................................... 7 SAVING......................................................................................................................................................... 7 TO CHANGE THE DEFAULT FILE SAVE LOCATION ...................................................................................... 8 TO CHECK WHICH VERSION OF EXCEL YOU HAVE ..................................................................... 8 QUITTING .................................................................................................................................................... 8 ENHANCING YOUR WORKSHEET........................................................................................................ 8 ALIGNING CELL CONTENTS ........................................................................................................................ 8 WRAPPING TEXT IN A CELL ........................................................................................................................ 8 CENTERING HEADINGS ACROSS COLUMNS ................................................................................................. 9 TO SPLIT MERGED CELLS ........................................................................................................................... 9 FORMATTING NUMBERS ............................................................................................................................. 9 TO CHANGE THE FONT & SIZE .................................................................................................................... 9

2
TO ADD BORDERS .................................................................................................................................... 10 TO ADD SHADING ..................................................................................................................................... 10 TO ADD COLOR ........................................................................................................................................ 10 USING AUTOFORMAT ............................................................................................................................... 10 REMOVING GRIDLINES .............................................................................................................................. 10 COPYING FORMATS................................................................................................................................... 10 CONDITIONAL FORMATTING............................................................................................................. 11 CREATING TEMPLATES IN EXCEL ................................................................................................... 11 TO PROTECT A WORKSHEET OR WORKBOOK ............................................................................ 11 UNLOCKING CELLS SO THEY CAN BE CHANGED WHEN A SHEET IS PROTECTED ...................................... 11 CREATING A CUSTOM TOOLBAR...................................................................................................... 11 ADDING OR DELETING BUTTONS FROM A TOOLBAR ............................................................... 12 NAMING CELLS OR RANGES............................................................................................................... 12 USING AUTOFILL .................................................................................................................................... 12 TO COMPLETE A SERIES OF VALUES.......................................................................................................... 12 TO COMPLETE A COMMON SERIES (DAYS, MONTHS, DATES)..................................................................... 12 TO AUTOFILL A SERIES OF WORDS THAT ARE NOT A COMMON SERIES ..................................................... 12 TO SELECT AN AUTOFILL OPTION ............................................................................................................ 12 TO CREATE CUSTOM LISTS FOR AUTOFILL ............................................................................................... 13 TO PROJECT FUTURE VALUES................................................................................................................... 13 NOTES IN CELLS ..................................................................................................................................... 13 ADDING NOTES TO CELLS .......................................................................................................................... 13 DELETING NOTES IN CELLS........................................................................................................................ 13 CREATE A LINK BETWEEN WORKBOOKS OR WORKSHEETS ................................................. 13 WORKING WITH DATABASES............................................................................................................. 14 ENTERING DATA ....................................................................................................................................... 14 USING THE DATA FORM ............................................................................................................................ 14 DELETING AND EDITING RECORDS ........................................................................................................... 14 SEARCHING THE DATABASE USING DATA FORM ....................................................................................... 14 SORTING THE DATABASE .......................................................................................................................... 15 Simple Sort .......................................................................................................................................... 15 Sorting with Multiple Keys .................................................................................................................. 15 Creating the Custom Sort List ............................................................................................................. 15 Performing the sort ............................................................................................................................. 15 SUBTOTALS ............................................................................................................................................... 15 To remove all subtotals ....................................................................................................................... 16 FILTERING THE DATABASE ....................................................................................................................... 16 AutoFilter ............................................................................................................................................ 16 To Show All Records When Filtering .................................................................................................. 16 Top Ten AutoFilter .............................................................................................................................. 16 Custom AutoFilter ............................................................................................................................... 16 To Remove an AutoFilter .................................................................................................................... 17 CREATING A PIVOT TABLE ........................................................................................................................ 17 CREATING A CHART.............................................................................................................................. 17 TO CREATE A CHART ................................................................................................................................. 17

3

Loading Excel
Click the Start a New Document icon on the Office toolbar Double Click the Blank Workbook icon from the General dialog box You can now put numbers, formulas or labels into the cells to create your spreadsheet document. Or… Click on Start in the Task Bar Slide to Programs Slide to Microsoft Excel

To Load an Existing Spreadsheet
Click the Open a Document icon on the Office toolbar Select the correct drive and folder, and Double-click on the document you would like to retrieve. Or if you are already in Excel Click on File on the Menu Bar Slide to Open Select the correct drive and folder, and Double-click on the document you would like to retrieve.

Excel Screen
Menu Bar Title Bar Standard Toolbar

Name Box

Formula Bar

4

Entering a Formula
Formulas tell the spreadsheet what to do with the numbers you enter. When making calculations, Microsoft Excel follows the Order of Operations. 1. Click on the cell where you want to enter a formula 2. Type an equal sign (=) or click on the equal sign on the formula bar 3. Type the formula that you want to use or use the function wizard and select your formula from the list 4. Press Enter or Click the !button next to the formula bar 5. Excel enters the formula in the active cell and displays the resulting value. The formula will still show up in the formula bar so that you can see both the formula and the result simultaneously.

Using the Function Wizard
1. 2. 3. 4. 5. If you'd like to paste a function into a particular cell, Click on the cell that you want to paste a function into, Click the Function Wizard (Fx) button next to the formula bar , Scroll through the choices and find the function you want and click on it, Click OK. Then type the cell coordinates that should be included, use a colon between cell coordinates, type in any other needed information and press OK. For example =Average(A2:A8) would calculate the average for values in cells A2 through A8.
This window is also new in Excel 97 and usually is in the way because it is covering up your information. You can click on it with your mouse and drag it out of the way.

You can also click on this icon to get back to your worksheet to select the area for your function. To get back to this window click the icon again.

Using Autosum Function
Autosum is an icon on the standard toolbar that allows you to automatically add information either directly above or directly to the left of the location you are building the autosum. Click the cell where you want to build the autosum function, on the standard toolbar click the Autosum button (Σ), it will show you the sum function and the cells it Σ wants to add, if this is correct click the ! button or press enter.

Range Finders
If you click on the formula or function in the Formula Bar you will notice that the formula becomes colored and is associated with the colored ranges in the worksheet.

5

More with Formulas
Absolute vs. Relative Reference When copying a formula Excel uses relative reference, meaning that your formula will change based on the location you are copying to. Excel changes the formula relative to the location where the formula started. For instance if the formula in E5 is =C5+D5 and you copy the formula to E6, Excel will change the formula to =C6+D6. Absolute reference is when you want to anchor or lock in the formula or parts of the formula so it doesn’t automatically can as you copy it. You can lock in the column reference, row reference or both. When a column or row is locked it will have a $ in front of its cell reference. For instance, =$C$5+D5 will result in the first part of the formula not changing as you copy the formula so every cell will be added to the value in C5. To Make a Cell Reference Absolute After you have selected the cell to have absolute reference as you are building the formula, press F4. This will place the dollar signs in front of the column and row references. Pressing F4 again will toggle you through locking just the column or just the row reference. Instead of pressing F4 you can just type the dollar sign also. Logical Operators > < = >= <= <>

Greater than Less than Equal to Greater than or equal to Less than or equal to Not equal to

Date and Time Formulas =today() =now() =today() + 45 Upper / Proper Text =upper(A13) =proper(A13) If Requires: a condition, value_if_true, & value_if_false =IF(C1>0,”Postive”,”Negative”) =IF(C1>0,”Positive”,IF(C1<0,”Negative”,”Zero”)) Converts characters in A13 to uppercase. Converts characters in A13 to proper case. Will enter today’s date. Will enter today’s date and time. Will enter 45 days from today’s date.

6 AND / OR Returns True or False =AND(C1>0,C2>0,C3>0) =OR(C1>0,C2>0,C3>0) Will return true value only if all conditions are met. Will return true value only if any conditions are met.

=IF(AND(F3>35,E3<=81),”TOUR”,”NO TOUR”) COUNTIF Requires: range and criteria =COUNTIF(A2:A13,”Male”) =COUNTIF(C2:C13,”>=35”) SUMIF Requires: a range, criteria, and sum range =SUMIF(C2:C13,”<40”,B2:B13) =SUMIF(C2:C13,”<40”,B2:B13)/COUNTIF(C2:C13,”<40”)

Copying a Formula or Information to the Right or Down
1. 2. 3. 4. If you'd like to copy a formula, Click in the first cell that has the formula, Click the fill handle on the bottom right corner of the cell Drag down or to the right to highlight all of the cells that you'd like that same formula to be placed in. The formula will be filled accordingly.

Copying Information not Adjacent to the Current Information
1. Select the information to copy. 2. Click on the Copy button on the Standard Toolbar. 3. Go to the place you want the information to go and Click on the Paste button on the Standard Toolbar.

Moving information between cells
1. Select the information to move. 2. Click on the Cut button on the Standard Toolbar. 3. Go to the place you want the information to go and Click on the Paste button on the Standard Toolbar.

7

Making a Column Wider
Move the cursor to line that divides the column headings and the cursor will change into a double-headed arrow, if you hold down on the mouse button and drag to the right or left it will make the column larger or smaller depending on your needs.

Inserting Rows or Columns
To insert a row or column click the row or column heading where you want to insert, choose Insert from the Menu Bar, and select either Rows or Columns.

Printing
To print an entire spreadsheet choose Page Setup from the File Menu, Choose the appropriate options in terms of horizontal or vertical printing, Click on OK. Go to the File Menu and pull to Print, click the appropriate options. Click on OK. Excel prints your entire spreadsheet document, if the document is too wide to fit on a page, Excel will print the remaining columns on subsequent pages before continuing to print the remaining rows.

Page Break Preview
This command allows you to see how your sheet will print. 1. On the Menu Bar, select View. Slide down and select Page Break Preview. 2. You then can adjust the blue lines that show your page breaks by clicking on them and moving the lines. Depending on how you adjust these lines, it will change the size of your text when it is printed. 3. To get back to the normal view, on the Menu Bar, select View. Slide down and select Normal.

Saving
You should save your document every 5 minutes, or when you have typed more than you would like to retype. The first time you save, go to the File Menu and pull to Save, make sure the document is in the correct folder and on the correct drive, type the name for what you want to save it as, and click in the save box. If you'd like to save a revised document with a different name you will use the Save As function. To save a document under a new name, first choose Save As from the File Menu then type a new name for the document, make sure it's in the correct folder and on the correct drive and choose the save button to save the document under it's new name.

8 To Change the Default File Save Location The default file location is the location in which Excel will save files unless you tell it to save in a different location when saving. To change the default location, on the Menu Bar select Tools. Slide down and select Options. Click on the General tab. In the box next to Default file location: type the name of the location where you would like to save your files. (I.e. c:\my documents or m:\user\pgreenle)

To Check Which Version of Excel You Have
On the Menu Bar select Help, slide down and select About Microsoft Excel. The window that appears will tell you what version of Excel you have.

Quitting
To quit the Excel program, first save any of the work done since the last time you saved the current document. When you have finished, choose Exit from the File Menu.

Enhancing Your Worksheet
Aligning Cell Contents Select the cell range you want to have formatted Click Format from the menu bar Click Cells Click the Alignment tab Make your selection(s) from the dialog box Click OK Shortcut 1: Select the cell range you want to have formatted Right Click anywhere in the range Select Format Cells Make sure the Alignment tab is selected Make your selection(s) from the dialog box Click OK The toolbar: Use the formatting buttons on the toolbar for the most common formatting options Wrapping Text in a Cell 1. Select the cell containing the data you want to have wrapped onto multiple lines 2. Click Format from the menu bar 3. Click Cells

9 4. Make sure the Alignment tab is selected 5. Click the Wrap Text option 6. Click OK

Centering Headings across Columns 1. Enter your data into a cell in column A 2. Select the cell containing your data and drag through the cell range you want your heading centered across 3. Click on the Merge and Center icon on the toolbar Once you have done this it makes the whole area you highlighted your A cell (or whatever cell you started with). To Split Merged Cells 1. 2. 3. 4. 5. Have your cursor on the merged cells. Select Format on the Menu Bar. Slide down and select Cells. Click on the Alignment tab. Uncheck the box next to merge cells.

Formatting Numbers 1. Select the cell or range of cells you want to format 2. Click Format from the menu bar 3. Click Cells 4. Make sure the Number tab is selected 5. Make your selection(s) from the dialog box 6. Click OK Shortcut: Use the formatting buttons on the toolbar to select some of the most common formatting options ** if your cells contain pound signs after you format your numbers it means your cell is too narrow to display the changes you made. Making your cell(s) wider will correct this problem. To Change the Font & Size 1. Select the cell or range of cells where you want to make changes 2. Click the Arrow for the font drop-down list on the Formatting toolbar 3. Scroll to find the font you want to use 4. Click the font you want 5. Click the Arrow for the font size drop-down list on the Formatting toolbar 6. Select the font size you want to use

10

To Add Borders 1. Select the cell or cell range you want to place a border around 2. Click on the Arrow for the Border drop-down box on the formatting toolbar 3. Click the border style you wish to use In Excel 95, if you wanted to have a border at the bottom of a row all you had to do was highlight the information and then select the bottom border on the borders icon. This would in essence underline each row that was highlighted. 1. In Excel 97, you must highlight the cells where you want the border and then on the Menu Bar, select Format, and slide down and select Cells. 2. Click on the Borders tab. 3. On the left side of the window, click on the borders you want. For the one the bottom of each cell you must also select the middle border. To Add Shading 1. Select the cell or range of cells you want to have shaded 2. Click on the Arrow for the Shades drop-down list from the toolbar 3. Click on the desired shade option to fill the highlighted cells To Add Color 1. Select cells or range of cells where you want to change the color 2. Click on the Arrow for the Text Color drop-down list from the toolbar 3. Select the color you want to use in your worksheet Using AutoFormat 1. Select the range of cells you want to format 2. Click Format from the menu bar 3. Click AutoFormat 4. Browse through the options in the Table Format list in the dialog box 5. Select the format you want to add to your worksheet 6. Click OK Removing Gridlines 1. Click Tools from the menu bar 2. Click Options 3. Select the View tab from the dialog box 4. Click to deselect the Gridlines option 5. Click OK Copying Formats 1. Select the cell you want to copy 2. Click the Format Painter button 3. Click on the cell(s) where you want to apply the format

11

Conditional Formatting
Applies formatting automatically to a cell if its value meets criteria you specify. 1. Highlight the cells you want to have the conditional formatting. 2. On the Menu Bar, select Format. Slide down and select Conditional Formatting. 3. Entry criteria.

Creating Templates in Excel
To create new workbooks that already have your preferred formatting, you can base the new workbooks on a template. A template is a workbook that contains specific content and formatting that you can use as a model for other similar workbooks. A template can include standardized text such as page headers and row and column labels, Visual Basic macros, and custom toolbars. 1. Create the workbook with the desired features. 2. On the menu bar select File, slide down and select Save As. 3. In the Save In box navigate to the Templates folder. (For Office 97 it is on the C drive, Program Files Folder, Microsoft Office folder. For Office 95 it is on the C drive, MS Office Folder.) 4. In the Save As Type box select Template (*.xlt). 5. Click Save.

To Protect A Worksheet or Workbook
This prevents people from making changes to the sheet. 1. On the menu bar select Tools, slide down and select Protection, then either pick worksheet or workbook. 2. Give a password if you want, if selecting worksheet make sure all three choices are checked, and then click OK. Unlocking Cells so They Can Be Changed When a Sheet is Protected 1. Make sure Protection in not on. 2. Select the cells to be unlocked. 3. On the menu bar select Format, select Cells, click on the Protection tab, and make sure the Locked box is not selected, click OK.

Creating a Custom Toolbar
On the View menu, click Toolbars. In the Toolbar Name box, type a name for the new toolbar. Click New. In the Categories box, click the category that contains the buttons you want to add to the new toolbar. 5. From the Buttons area, drag the button or other item to the new toolbar. 1. 2. 3. 4.

12

Adding or Deleting Buttons from a Toolbar
1. Display the toolbar you want to change, and then click Toolbars on the View menu. 2. Click Customize. 3. To add a button, click the name of the category in the Categories box, and then drag the button or item from the Buttons area to the displayed toolbar. 4. To delete a button, drag it off the toolbar.

Naming Cells or Ranges
You can label or name different parts of your worksheet to make it easy to select the information or create formulas. 1. 2. 3. 4. Highlight the information to be named Click in the Name Box. Type a Name for the selection. Press enter.

Using AutoFill
As you may know using AutoFill is a quick way to copy formulas down or to the right. You can use AutoFill to complete a series of numbers, common series names (days, months, dates, or linear regression based on the trend line. To complete a Series of Values 1. Type the first number of the series in the first cell, then the second number in either the cell below or to the right of the first cell. 2. Highlight the series. 3. Use the fill handle (bottom right-hand corner of the cell) to complete the range. To complete a Common Series (days, months, dates) 1. Type the first name or date in the series. 2. Use the fill handle to complete the range. To AutoFill a series of words that are not a Common Series 1. Type the group of words in the series. 2. Highlight the series. 3. Use the fill handle to complete the range. To Select an AutoFill Option 1. Drag the fill handle with the right mouse button instead of the left. One thing this will do is to allow you to use just weekdays instead of all dates.

13 To create Custom Lists for AutoFill 1. 2. 3. 4. 5. On the menu bar, select Tools, and slide down and select Options. Click the Customs Lists tab. In the box on the right (List Entries), type the word in the series. Click Add. Click OK.

To Project Future Values You can fill in a series of values that fit a simple linear trend or an exponential growth trend. The projected values are extrapolated from starting values that you select on a worksheet. In a linear series, Microsoft Excel increases or decreases values by a constant value that is based on the difference between the selected starting values. In a growth series, Microsoft Excel multiplies values by a constant value. 1. Type the values. 2. Highlight the values. 3. Use fill handles to fill series.

Notes in Cells
Adding notes to cells 1. 2. 3. 4. Click the cell to which you want to add the note. On the menu bar select the Insert menu, click Comments. In the Text Note box, enter the text you want for the note. Click outside of the Text Note box. Or… 1. Right mouse click on the cell where you want the note. 2. Slide down and select Insert Comment. 3. Continue with the above steps. Deleting notes in cells 1. Click the cell that contains the note you want to remove. 2. On the menu bar select the Edit menu, select Clear, and then click Comments. Or… 1. Right mouse click on the cell with the comment. 2. Slide down and select Delete Comment.

Create A Link between Workbooks Or Worksheets
1. 2. 3. 4. In the source workbook, select the cells you want to refer to. Click Copy Icon. Switch to the workbook you want to paste the linked data into. Select the cell or the upper-left corner of the range in which you want the linked data to appear.

14 5. On the Edit menu, click Paste Special. 6. To paste the link into the selected cells, click Paste Link.

Working with Databases
Entering Data 1. Approximate the number of records you plan to enter into your database and highlight that area. 2. Enter data into the first cell of the range 3. Press the Tab key to advance to the next cell 4. Continue to enter data and press Tab to automatically move to the next line when you are finished entering a record Using the Data Form 1. Select one cell in the database to make it active 2. Click the Data from the menu bar 3. Click Form 4. Click the New button to clear the text boxes for the new record entry 5. Press the Tab key to move to the next box 6. Click the New button to add the record to the database and clear the text boxes for the next record 7. Click Close after you have entered the last record Deleting and Editing Records 1. Select a cell in the record you want to delete 2. Click the Edit from the menu bar 3. Click Delete 4. Click Entire Row If you want to delete or edit a specific part of a record Click the cell and make the desired changes. Searching the Database using Data Form Allows you to locate a record quickly to make changes, etc. 1. 2. 3. 4. 5. 6. Click any cell in the database to make it active Click Data from the menu bar Click Form Click the Criteria button Type your search criteria in the Item text box Click the Find Next button

15 Sorting the Database Simple Sort 1. Select any cell in the database to make it active 2. Click the Sort Ascending or Sort Descending button on the toolbar Sorting with Multiple Keys 1. Select any cell in the database to make it active 2. Click the Data from the menu bar 3. Click Sort 4. Click the Arrow next to the Sort By drop-down list to display the field names 5. Select the field you want to sort by. 6. Click the Arrow next to the Then By drop-down list and click the next field you want to sort by 7. Click OK to perform the sort Creating the Custom Sort List Custom Sort—allows you to sort by something that is not A-Z, 0-9, Z-A, or 9-0. (For instance high, medium, low.) 1. In a blank column outside of your database, type the sort order (ex. high, medium, low) 2. Highlight the sort order list 3. Click Tools from the menu bar 4. Click Options 5. Make sure the Custom Lists tab is selected 6. In the bottom of the window it says Import List from Cells, if the range there is correct select the Import button to the right of it 7. Click OK Performing the sort 1. Click Data from the menu bar 2. Click Sort 3. Indicate the column to sort by, select Options button on the right 4. Select the appropriate custom list 5. Click OK 6. Click OK Subtotals Allows you to subtotal groups of data in a database. 1. Be in the database where you want to add subtotals. Sort the database by the column where you want subtotals. 2. Click Data from the menu bar 3. Click Subtotals 4. In the first box indicate which field (column) has the groups

16 5. 6. 7. 8. In the next box indicate which function to use (I.e. sum) In the following box indicate which column will have the function performed on it Make any other desired changes Click OK

You can add another subtotal function by following the above process again, however before clicking OK uncheck Remove Current Subtotal. To remove all subtotals 1. Click Data from the menu bar 2. Click Subtotals 3. Click Remove All Filtering the Database Allows you to pull out a group of related records. AutoFilter 1. Click on a cell in your database to make it active 2. Choose Data from the menu bar 3. Select Filter, select AutoFilter from the resulting list 4. Click the drop-down arrow next to the field you want to search 5. Select search criteria It is important to show all records before performing another filter. To Show All Records When Filtering 1. Choose Data from the menu bar 2. Select Filter, select Show All from the resulting list Top Ten AutoFilter Allows you to pick out the top (bottom) few items or a percentage of the value in the cell. 1. Click on a cell in your database to make it active 2. Choose Data from the menu bar 3. Select Filter, select AutoFilter from the resulting list 4. Click the drop-down arrow next to the field you want to search 5. Select (Top Ten…) 6. Complete the information in the Top Ten Dialog Box Custom AutoFilter Allows you to customize your filter. 1. Click on a cell in the column (field) you want to do a custom filter in your database to make it active Drop-down Arrows will appear after

17 Choose Data from the menu bar Select Filter, select AutoFilter from the resulting list Click the drop-down arrow next to the field (column) you want to search Select (Custom) Enter Operator (I.e. =,>) Enter Criteria (can be a typed criteria) If you have a second criteria decide either And or Or (with And both criteria must be true) 9. Enter 2nd Operator 10. Enter 2nd Criteria 11. Click OK 2. 3. 4. 5. 6. 7. 8. To Remove an AutoFilter 1. Choose Data from the menu bar 2. You will see that AutoFilter has a check mark by it 3. Select Filter, select AutoFilter again to unselect it to turn it off Creating a Pivot Table Allows you to do cross tabulation results. It is a two-sided matrix. 1. 2. 3. 4. 5. 6. Make sure that your cursor is someplace on the list, don’t have it in a blank cell. Click Data on the menu bar. Select PivotTable, this will take you to the PivotTable Wizard. In the first window, indicate where you are getting the information from, click Next. In the second window, it should have the database range already selected. In the next window, drag the field name that you want to be the rows to the appropriate place, drag the field name that you want to be the columns to the appropriate place, and then in the data area indicate the values you want to summarize. 7. To change the function that will be performed double click on that button in the data area, click Next. 8. In the last window, you can change the options you want if you don’t select a PivotTable starting cell it will create a new sheet for the PivotTable, click Finish. 9. To change the PivotTable, click Data on the menu bar again, and select PivotTable.

Creating A Chart
Most charts include a title, a legend to help clarify what each data series represents, a yaxis, and an x-axis. Typically, values are plotted along the vertical plane (y-axis) and categories are plotted along the horizontal plane (x-axis). To create a chart Select the data you want included in the chart from an existing worksheet Click the Chart Wizard icon from the toolbar A dialog wizard box will appear Step 1 Click on the Chart type you would like to use

18 Click on the Next button to move to Step 2 • In the Data Range tab check to make sure the selected range is correct (the $ in front of each column letter and row number indicates that these are absolute references). A series can be either the information in rows or columns, switch between the two to see how it changes your chart preview. • In the Series tab you can choose to add or delete a series. The name box shows which information will be in the legend for a series. The values show which values will be displayed for a series. Click the Next button to move to Step 3 In this step you can make sure your charted data looks the way you want it (make changes as needed) • Titles tab—enter the information you would like for your titles. • Axis tab—you can decide to view axis titles or not. • Gridlines tab—Add or delete horizontal and vertical gridlines. • Legend tab—you can decide to whether to show the legend or not and the location of it. • Data Labels tab—this is where you can give each piece of data a label. • Data Table tab—you can select to have a table of the information placed with your chart. Click on the Next button to move to Step 4 Decide if you want the chart to be on the same sheet as the data or on a separate sheet Click Finish.

Last Modified 10/26/00