Excel2

Intermediate Excel: Data Management Topics

Technical Support Services Office of Information Technology, West Virginia University

OIT Help Desk -- 293-4444 x 1

http://oit.wvu.edu/support/training/classmat/xl/

Instructors: Kathy Fletcher Last revised: March 9, 2005

Table of Contents Managing Workbooks and Worksheets........................................................................... 3 Manage Multiple Worksheets within a Workbook ............................................................ 4 Add a single worksheet................................................................................................ 4 Move or Copy Worksheets from One Workbook to Another ........................................ 4 Delete a Worksheet from a Workbook ......................................................................... 4 Change Worksheet Names.......................................................................................... 5 Change Tab Color ....................................................................................................... 5 Change Worksheet Order within a Workbook.............................................................. 5 Freezing Worksheets................................................................................................... 5 Linking Data .................................................................................................................... 6 Linking data between worksheets................................................................................ 6 Linking between workbooks......................................................................................... 6 AutoFill ............................................................................................................................ 7 Automatically Filling a Range with Data....................................................................... 7 Edit > Fill > Series........................................................................................................ 7 Data Validation................................................................................................................ 8 To limit entry to a list of values: ................................................................................... 8 To restrict data entry to a date or range of values ....................................................... 9 Protecting portion of worksheet from modification......................................................... 10 Create a data entry form ............................................................................................... 13 Import Data ................................................................................................................... 14 Text to Columns......................................................................................................... 15 Export Data ................................................................................................................... 15 Using Excel worksheet as data for Word Mail Merge .................................................... 16 Labels ........................................................................................................................ 18 Sorting Data .................................................................................................................. 19 Filtering Data................................................................................................................. 20 Pivot Tables .................................................................................................................. 21

2

WVU Office of Information Technology

Managing Workbooks and Worksheets Excel uses the term workbook for a file. The term worksheet is Excel’s name for an individual spreadsheet. A workbook can contain multiple worksheets, each with their own tab. It is possible to have many workbooks (xls files) open concurrently. Switch Between Open Workbooks 1. Choose Window from the menu. 2. In the menu that appears, you will see a listing of the workbooks that are open. 3. Click on the file that you want to bring to the foreground from the background. The file that was in the foreground remains open, but is now in the background. View Many Open Workbooks at the One Time 1. Choose Window from the menu. 2. Choose Arrange. 3. Choose Tiled, Horizontal, Vertical, or Cascade, depending on how you prefer to view the files.

Tiled

Cascade

Intermediate Excel XP

3

Manage Multiple Worksheets within a Workbook An Excel workbook (file) can contain more than one worksheet. This allows you to organize related worksheets into a workbook (one file).

Add a single worksheet 1. Choose Insert from the menu. 2. Choose Worksheet.

Add multiple worksheets: Hold down the Shift key and click on the number of worksheet tabs you want to add to the workbook. To add two new worksheets, hold down the Shift key and click on two of the existing worksheet tabs. Choose Insert > Worksheet from the menu.

Move or Copy Worksheets from One Workbook to Another 1. Open the workbook to which you want to add worksheets. 2. Open the workbook(s) containing the worksheet(s) you want to insert into the other workbook. 3. Click on the tab of the worksheet you wish to copy. 4. Choose Edit > Move or Copy Sheet from the menu. 5. The “Move or Copy” window appears. 6. In the “To book:” drop down list, select the workbook into which you want to insert the worksheet(s). 7. Select a location in the "Before sheet:" box. The copied sheet will appear to the left of the item you select. 8. Click OK You can also use this to copy sheets within a single workbook – be sure to check "Create a copy" or the sheet will just move to the new location.

Delete a Worksheet from a Workbook 1. Click on the worksheet(s) that you want to delete. 2. Choose the Edit > Delete Sheet command from the menu. The warning window will appear.

Make sure you have selected only the worksheets you want before clicking on Delete!

4

WVU Office of Information Technology

Change Worksheet Names By default, worksheets are named Sheet1, Sheet2, etc. Worksheet names can be changed to accurately reflect their content. 1. Double click on the worksheet tab for which you want to change the name. It will now appear as reverse text (white letters on black background). 2. Type in the name you prefer for the worksheet. 3. Press the Enter key.

Change Tab Color By default, all worksheet tabs are white. You can change the tab color – the active worksheet’s tab will still appear white with a thin colored underline; the inactive worksheets will display the selected tab colors. 1. Right click on the worksheet tab for which you want to change the tab color. 2. Choose Tab Color from the shortcut menu. 3. Click on OK.

Change Worksheet Order within a Workbook 1. Click on the tab of the worksheet you want to move. 2. Drag this worksheet tab to the right or left. You will notice a down arrow indicating the position of the worksheet. 3. When the down arrow reaches the place you where you want the worksheet to be located, release the mouse button.

Freezing Worksheets When working with large or complex worksheets, scrolling can sometimes become a problem. Freezing panes allows you to keep row and column labels visible as you scroll. 1. To freeze a pane, do one of the following:  The top horizontal pane (column headings): select the row below  The left vertical pane (Id numbers or names): select the column to the right  Both the upper and left panes: click the cell below and to the right of where you want the split to appear (e.g. B2 to freeze row 1 and column A) 2. Choose the Freeze Panes command from the Windows menu. Unfreezing Panes – choose Windows > Unfreeze Panes from the menu.

Intermediate Excel XP

5

Activity : Managing Workbooks and Worksheets Spend a few minutes trying out some of the features mentioned above such as:  Changing views: switch from one workbook to another, select a worksheet…  Moving, copying, inserting/deleting worksheets  Renaming worksheets  Reordering worksheets  Changing worksheet tab colors  Freezing and unfreezing panes and scrolling

Linking Data Linking data between worksheets 1. Open the file containing worksheets that you want to link together. 2. On the destination sheet, click where you want the linked information to appear. 3. Type an equal sign (=) 4. Switch to the source worksheet (one that contains the data or calculation result) 5. Click once on the cell containing the data you want to link. Press Enter 6. Excel returns you to the destination worksheet and the information from the source worksheet appears within the destination file.

Linking between workbooks 1. 2. 3. 4. 5. 6.

Open all of the files containing worksheets that you want to link together. In the destination file, click where you want the linked information to appear. Type an equal sign (=) Switch to the source file (one that contains the data) Click once on the cell containing the data you want to link. Press Enter Excel returns you to the destination file and the information from the source file appears within the destination file. 7. You must SAVE both files in order for this link to remain in effect. If the data is changed for a source cell, it is automatically updated in the destination cell, provided the changes to the source are saved.

6

WVU Office of Information Technology

AutoFill Automatically Filling a Range with Data Excel’s AutoFill feature allows you to automatically fill a range of cells with a series of numbers, dates, or other information (e.g. 1,2,3,4... or January, February, March...) in order to save time from typing the data yourself.

AutoFill Numbers 1. Click in the first cell of the range you want to fill. 2. Type the starting number for the series. 3. Click in the next cell in the range and enter the next number in the series. The difference between the two numbers determines the amount by which the series is incremented. 4. Highlight the cells containing the starting numbers. 5. Place your mouse over the bottom right corner of the highlighted range. At this junction, it will change shape to a smaller dark cross (+), known as the fill handle. 6. Click and drag this fill handle until it includes the entire range that you want to fill.

AutoFill Words 1. Click in the first cell of the range you want to fill. 2. Type the starting word for the series. 3. Highlight the cells containing the word. 4. Put your mouse over the bottom right corner of the highlighted range. At this junction, it will change shape to a smaller dark cross (+), known as the fill handle. 5. Click and drag this fill handle until it includes the entire range that you want to fill.

Edit > Fill > Series

Use this for a more complex situation.

Intermediate Excel XP

7

Activity: AutoFilling cells Spend some time experimenting with the autofill feature. Try the months of the year, days of the week, as well as incrementing numbers (1, 3, 5 and so on). Use it to copy a formula or single value.

Data Validation To limit entry to a list of values: 1. Ahead of time, enter the possible values on the same worksheet but far away from possible row deletions. 2. Click in the cell where you want to control what gets entered. 3. Menu command: Data > Validation

4. Under the Settings tab, select List under the Allow item. 5. Enter or select the source for those possible responses. 6. Under Input Message, you can provide a prompt to assist during data entry.

7. Under Error Alert, you can provide remedial support to encourage the correct selection. You can also choose the make the error only a warning instead of refusing to take their value. 8. Click on OK to finalize your choices.

8

WVU Office of Information Technology

9. You can copy this cell to the cells below it. •

Select the cells that need to use the validation.

•

Use Edit > Paste Special and select Validation.

To restrict data entry to a date or range of values Use the same steps as listed earlier, except under Settings tab, select one of the numeric choices.

Keyboard shortcuts        

Enter today’s date: Ctrl ; (hold control key down and press the semi-colon key) Enter the data item appearing in the row above: Ctrl ‘ (Ctrl & single quote key) Copy: Ctrl C - Paste: Ctrl V Save: Ctrl S Beginning of line: Home A1: Ctrl Home Undo the last action: Ctrl Z Cancel changes in a cell you are editing: Esc

Intermediate Excel XP

9

Protecting portion of worksheet from modification 1. Tools > Protection > Allow Users to Edit Ranges

2. Click on the New button to define areas for data entry or modification. 3. Supply a title for the area, enter or select a range, and supply a password. 4. Click on the Permissions button. (steps continued on next page)

10

WVU Office of Information Technology

5. Click on the Add button. 6. In the Select Users or Groups dialog, click on the Advanced button. 7. Click on the Find Now button to retrieve a list of possible users. 8. In our example today, we want to give Everyone access to this area. Find "Everyone" in the list and select it.

9. Click on OK to close the Select Users dialog. 10. Put a check mark under "Allow"

Intermediate Excel XP

11

11. Click on OK to close the Permissions dialog

12. Click on OK to close the Allow Users to Edit Ranges dialog 13. Go to Tools > Protection > Protect Sheet

14. Make sure "Select locked" and "Select unlocked cells" are both checked. Supply a password that you can remember and click on OK. 12

WVU Office of Information Technology

Tips and Reminders   





  

Supply a password for both the unprotected range and for the worksheet when you protect it - otherwise the data entry person can undo what you have done. Allow locked cells in addition to unlocked cells to be selected when you protect the sheet Make sure you click on the add button under the Security tab when adding permissions. Make sure there is a checkmark in "edit range without password" before clicking on ok. You can create and set up one sheet with permissions, copy the sheet, then go back and protect each sheet at the end … instead of copying the sheet, editing it, then going back to each sheet to set up its permissions. Click outside the unprotected range before using Tools > Protection > Protect Sheet command. Do not use the Protect Sheet button inside the "Allow Users to Edit Ranges" dialog. You have to protect each worksheet separately. You can paste permission info into a new workbook. If the person tries to enter or change information anywhere else on the sheet, they will get a warning message:

Create a data entry form Choose Data > Form from the menu. You can now browse each record one at a time, search for records based on a criteria, or delete existing records (rows) You can also use this form to enter new records.

Intermediate Excel XP

13

Import Data You can bring in ASCII data that is saved in a text-only file. If the file is .csv (commas between items), it will automatically fill the rows and columns correctly. If the file is plain text, a Text Import Wizard will kick in. 1. File > Open 2. Tell Excel to look for *.txt files, not Excel files. Select the desired file. The Wizard will begin.

3. Step One: Your file will either be delimited (items separated by spaces, commas, etc or it will have fixed width columns. Choose the correct option (don't just let Excel guess for you). Click on Next. 4. Step Two: If you chose Delimited, step 2 will ask you to select the character(s) to be used to divide the items into columns. If you chose Fixed Width, you will click to create lines to divide the items in appropriate locations.

14

WVU Office of Information Technology

5. Verify that the column breaks are where you want them to be. Establish data types if necessary. 6. Click on Finish.

Text to Columns If you have entered or copied some text into a single column, you can use the Data > Text to Columns command to portion it out into adjacent columns (make sure they are blank before proceeding.

Export Data You can save an Excel worksheet using a variety of text formats:  .txt – tab delimited  .csv – comma delimited  .prn – formatted text, space delimited  .dif – data interchange format

Other software such as Microsoft Access, Microsoft Word, SAS, or SPSS allow you to import, link to, or insert an Excel file.

Intermediate Excel XP

15

Using Excel worksheet as data for Word Mail Merge Mail Merge is a part of the Microsoft Word that allows you insert data from all office products into a Word document by field. The Mail Merge feature imports data from a table or spreadsheet onto each individual document. 1. Create a spreadsheet with the field names in row 1. Make sure that you create descriptive headers at the top of each column. This allows the mail merge program to access the data according to field names. 2. Once you have created the table, then go to Tools > Letters & Mailings > Mail Merge Wizard 3. After you have chosen Mail Merge Wizard, you will see "Select document type" in the right hand pane. 4. Choose the type of document from the list: ƒ Letters ƒ E-mail messages ƒ ƒ ƒ

Envelopes Labels Directory.

5. Click on the Next button at the bottom of the pane. You will see "Select starting document": 6. Since we started Mail Merge from a blank new document, select Use the current document. 7. Click on Next at the bottom of the pane 8. Continue on to Step 3: "Select recipients" This time we are going to choose Use an existing list.

9. Click on Next at the bottom of the pane

16

WVU Office of Information Technology

10. You then need to choose the file that has the table of your data (namelist.xls).

a. b. c.

Click on the column name to sort the entries by that field Click on the down triangle at the top of a column to create a filter to select a subset of your records to use. Uncheck any records you do not want to print.

11. Click on OK. 12. Click on Next to go on to create the letter or label. 13. Click on More items…

14. Select the fields. Either insert them all and then add text and line breaks OR go back and forth between the document and this dialog. 15. Click on Next – preview your document. 16. Finally, click on Next – complete the merge 17. Choose All, Current record, or a range of record numbers. Click on OK 18. Choose Print to send the letters or labels to the print or click on Edit if you want to tweak them or save them to a file.

Intermediate Excel XP

17

Word: Mailing Labels 1.

In a blank word document, go to Tools > Letters & Mailings > Mail Merge Wizard

2.

Choose Labels as the type of document from the list

3.

Choose "Change document layout"

4.

Click on Label options

5.

Choose the type of labels. For example: 5260 Avery

6.

Click on OK.

7.

Select recipients from an existing list: your xls file.

8.

Click on Next to arrange your labels

9.

Use the More items choice to add fields to your first label.

10. When you are done with that label, click on Update all labels. Your final layout will look something like: 11. Preview your labels 12. Complete the merge 13. Save and/or print the file

18

WVU Office of Information Technology

Sorting Data Information can be sorted into alphabetical or numerical order, ascending or descending. You are advised to save your spreadsheet before you sort! 1. Click in the column that you wish to sort by. 2. If you want the sort to be performed based on the information that is in the first column of the highlighted range, then click on either: the Sort Ascending button, or the Sort Descending button If you want the sort to be performed based on information that is in a column other than the first one in the highlighted range, do the following: 3. Choose Data from the menu. 4. Choose Sort from the top of the list. 5. In the Sort Window that appears, indicate the column by which you want to sort. 6. Click on Ascending or Descending. 7. You can sort by more than one columnn using the menu. 8. If you included column headings when you highlighted the database, make sure that the button for “My list has Header row” is selected. 9. Click on OK.

Intermediate Excel XP

19

Filtering Data You can filter out information to select records that match specific criteria. 1. Click anywhere on the row that contains column headings 2. Choose Data from the menu 3. Select Filter 4. Select AutoFilter

5. Downward pointing triangle buttons [] are inserted into the worksheet on the column headings. Click on a triangle and choose from the list that appears the criteria for which you want to find data that matches. You can select criteria for more than one column heading. 6. The Status Bar at the bottom of the worksheet will indicate how many records fulfill the criteria. The triangle and those records' row numbers will turn blue. 7. When you are finished with AutoFilter, return the spreadsheet to its regular appearance: 8. Choose Data > Filter > AutoFilter (this should uncheck this menu item and the triangle buttons will disappear). You can do a Custom AutoFilter by selecting (Custom) from the down triangle button:

20

WVU Office of Information Technology

Pivot Tables A PivotTable report is an interactive table that you can use to quickly summarize large amounts of data. You can rotate its rows and columns to see different summaries of the source data, filter the data by displaying different pages, or display the details for areas of interest. Use a PivotTable report when you want to compare related totals, especially when you have a long list of figures to summarize and you want to compare several facts about each figure. Use PivotTable reports when you want Microsoft Excel to do the sorting, subtotaling, and totaling for you. Because a PivotTable report is interactive, you or other users can change the view of the data to see more details or calculate different summaries. Creating a PivotTable can seem a daunting task, and because of that perception, Excel has provided a PivotTable and PivotChart Wizard as a guide to locate and specify the source data you want to analyze and to create the report framework.

To create a Pivot Table 1. Open the workbook where you want to create the PivotTable report. 2. Find the data that you are going to use to create the report. Click somewhere in the data. 3. On the Data menu, click PivotTable and PivotChart Report. 4. Step 1 asks where the data is coming from. In this example, select Microsoft Excel list or database. It also wants to know what kind of report you wish to create. Select PivotTable, and click Next. 5. Step 2 asks where the data is. If you clicked in the data as suggested, Excel will have selected the data it thinks you need. If it is incorrect, you can simply select the range. Once you have the range you need, click Next. 6. Step 3 needs to know where the Pivot Table is going to be located. Select a new sheet and click Finish. At this point you will see a table with areas waiting for fields to be dropped. You should also see the PivotTable and PivotChart toolbar showing the fields that were in the data you selected. As you drag the fields to the areas, you’ll see the report begin to build.

Intermediate Excel XP

21

Before

After the fields are added One of the advantages of creating the pivot table is that you can drag the fields to different locations and see what the results are immediately. You also can click on the drop-down arrows to make changes to the data you wish to view. To help you work with the Pivot Table, the PivotTable and PivotChart toolbar is available. It is comforting to know that any revisions of data that you make to the PivotTable are not reflected in your original table of data in its own worksheet. This allows you to be creative and experiment with the data, without the concern that data may be overwritten or altered permanently. If you change the underlying data, update the pivot table by clicking on the Refresh Data button (it looks like a red exclamation point). Another Pivot Table: Sales Reps and their Maximum Sales

22

WVU Office of Information Technology

Modifying a Pivot Table By default, the Pivot Table calculates Sums. If you want frequency counts instead, right click on top of A3 (Sum of fieldname) and choose Field Settings from the shortcut menu. Or you can double click on A3.

You can change Sum to Count (or Average, Max, Min, etc) in the Summarize by box. Click on OK. You can create a Pivot Chart from this pivot table summary by clicking on the chart wizard button on the pivot table toolbar.

The Pivot Chart is automatically created on its own sheet as a bar chart (might be stacked bars depending on what you have in your underlying pivot table). You can change what fields are used as if you were editing the pivot table. When you have finished, you can edit the chart appearance (if you change the fields, all appearance changes revert back to defaults … so change appearance last).

Appearance Tip: From the PivotChart menu on the floating toolbar, you can choose to Hide PivotChart Field Buttons

You can filter the chart by unselecting certain values.

Intermediate Excel XP

23

Pivot Chart for Region by Month, Sum of Sales Description (All)

30000

Sum of Sale

25000

20000

Month April-04 March-04 February-04

15000

10000

5000

0 East

Midwest

North

South

West

Region

Pivot Chart for Sales Staff, Max Sale, Field Buttons and Legend suppressed Total 14000

12000

10000

8000

6000

4000

2000

0 Anderson, Jeanne

24

Bickle, Peter Cashel, Kaitlin

Davis, Jim

Fuller, Paula

Gregg, Jamie

Hall, Michal

Smith, Ian

Thompson, Dara

WVU Office of Information Technology