CHAPTER 10

1. CHAPTER 10 Automating and Extending Excel

2. 10.1 Using Lookup Tables

3. 10.1.1 Creating a Lookup Formula Figure 10.1 Add-ins dialog box with the Lookup Wizard selected

4. 10.1.1 Creating a Lookup Formula Figure 10.2 Lookup Wizard dialog box: Step 1 of 6

5. 10.1.1 Creating a Lookup Formula Figure 10.3 Lookup Wizard dialog box: Step 2 of 6 Select the column label heading for searching. Select the row label heading for searching.

6. 10.1.1 Creating a Lookup Formula Figure 10.4 Lookup Wizard dialog box: Step 4 of 6

7. Lookup formulas created using the Lookup Wizard utilize the INDEX and MATCH built-in functions. Range Finder uses colors to display the relevant cell ranges in the formula. 10.1.1 Creating a Lookup Formula Figure 10.5 Using Range Finder to view the lookup formula

8. 10.1.1 Creating a Lookup Formula Figure 10.6 Displaying the menu for the Error Checking Options button Use the menu to access context-sensitive help or to use Microsoft Excel’s auditing features.

9. 10.1.2 Using Lookup and Reference Functions Figure 10.7 The Lookup & Reference Functions tab

10. 10.1.2 Using Lookup and Reference Functions Figure 10.8 Entering the INDEX function

11. 10.1.2 Using Lookup and Reference Functions Figure 10.9 Function Arguments dialog box for MATCH

12. 10.1.2 Using Lookup and Reference Functions Figure 10.10 Select Arguments dialog box The INDEX function provides two syntax formats for returning different results. The array syntax returns a cell’s contents, while the reference syntax returns a cell’s address.

13. 10.1.2 Using Lookup and Reference Functions Figure 10.11 Function Arguments dialog box for INDEX

14. 10.1.2 Using Lookup and Reference Functions Figure 10.12 Function Arguments dialog box for VLOOKUP

15. 10.1.2 Using Lookup and Reference Functions Figure 10.13 Completing the Functions tab in the “Lookup Tables” workbook

16. 10.2 Protecting Your Work • At the file level, you can password-protect a workbook so that only authorized users (people given the password) can open, view, and modify its contents. • At the workbook level, you can protect and hide individual tabs containing worksheets, charts, and modules. • At the sheet level, you can protect cells and objects from being modified or deleted.

17. Unprotect the criteria range in cells A2 to G2. Rows 4 and 5 contain database functions that need to be protected. Unprotect the worksheet list, including the rows beneath the active list area. 10.2.1 Protecting Cells, Formulas, and Worksheets Figure 10.14 The Billings worksheet

18. 10.2.1 Protecting Cells, Formulas, and Worksheets Figure 10.15 Format Cells dialog box: Protection tab When the worksheet is protected, you cannot enter or edit data in locked cells. When the worksheet is protected, you cannot view the contents of a hidden cell in the formula bar.

19. Turn protection on or off using this check box. Enter a password to prevent users from removing sheet-level protection. Select the individual options that you want to make available to all users of the worksheet. 10.2.1 Protecting Cells, Formulas, and Worksheets Figure 10.16 The Protect Sheet dialog box

20. 10.2.1 Protecting Cells, Formulas, and Worksheets Figure 10.17 Warning dialog box displayed for protected cell

21. 10.2.1 Protecting Cells, Formulas, and Worksheets Figure 10.18 The Protect Workbook dialog box Protect the individual sheets in a workbook and/or the document window display. Enter a password to prevent users from removing the protection.

22. 10.2.1 Protecting Cells, Formulas, and Worksheets Figure 10.19 Unhiding the Partner Summary tab

23. 10.2.2 Password-Protecting a Workbook Figure 10.20 The Save Options dialog box Enter the password required to open the workbook in the application window. Select to display a recommendation that the user open the workbook in read-only mode. Enter the password required to modify the workbook and update the original disk file.

24. 10.2.2 Password-Protecting a Workbook Figure 10.21 Opening a password-protected workbook Before you can even view the workbook’s contents, the Password dialog box appears.

25. Enter a term for use in grouping similar files together in performing a file search. Provide document information for use in locating this file. Enter words to match in performing a file search. Enter descriptive comments. Select to view the first page of a workbook in the Open dialog box. Enter the base address for all relative hyperlinks entered into the workbook. 10.2.3 Setting Workbook Properties Figure 10.22 Filling in the File Properties for a workbook

26. 10.3 Working with Templates • A template is a special type of workbook file that you can use as a model from which to create new workbooks. • You can also use a template to customize various workbook and worksheet settings, including: • the number of sheets displayed in a new workbook • page margins, print orientation, and headers and footers • cell formatting characteristics and protection attributes • cell contents, such as titles, headings, and formulas • custom toolbars and menu commands, macros, and VBA modules

27. Click to display a new blank workbook based on the “Book.xlt” template. Click to display the built-in and custom templates in the Templates dialog box. Click to retrieve templates stored on intranet or Internet Web servers. Click to view the templates available on the Microsoft.com Web site 10.3.1 Creating and Applying Templates Figure 10.23 Displaying the New Workbook task pane

28. Each workbook created using this template will display this information as a starting point. Each cell is formatted to display using a particular font, font size, and column width. This workbook template contains a single worksheet tab. 10.3.1 Creating and Applying Templates Figure 10.24 Creating a template

29. 10.3.1 Creating and Applying Templates Figure 10.25 Viewing the location of the Templates folder Click here to toggle the display of the drop-down list. On this computer, the Templates folder is nested in the Microsoft and Application Data folders, and all are located under the Windows folder.

30. 10.3.1 Creating and Applying Templates Figure 10.26 Displaying a custom template

31. 10.3.1 Creating and Applying Templates Figure 10.27 Accessing the Microsoft Office XP template Gallery on Microsoft.com

32. 10.3.2 Editing Templates Figure 10.28 Editing and formatting a template

33. 10.3.2 Editing Templates Figure 10.29 Insert dialog box for inserting a new worksheet

34. 10.4 Importing and Exporting Data • The import and export functions of Excel all you to: • Facilitate the sharing of data among different systems and programs • Import data from text files • Import data from other spreadsheet programs

35. 10.4.1 Saving and Exporting Structured Data Figure 10.30 Opening the EX1041 workbook

36. 10.4.1 Saving and Exporting Structured Data Figure 10.31 Warning dialog box during export to a Text file

37. 10.4.1 Saving and Exporting Structured Data Figure 10.32 Information dialog box during export to a Text file

38. 10.4.1 Saving and Exporting Structured Data Figure 10.33 Displaying an exported worksheet using Notepad

39. 10.4.2 Importing Data Using File Type Converters Figure 10.34 Opening a Lotus 1-2-3 spreadsheet file

40. 10.4.3 Importing Data Using the Text Import Wizard Figure 10.35 Text Import Wizard: Step 1 of 3 Text files exported from other programs typically use commas, quotation marks, and tabs as delimiters between fields. Location of the text file to be imported Preview of the file contents to be imported

41. 10.4.3 Importing Data Using the Text Import Wizard Figure 10.36 Text Import Wizard: Step 2 of 3 Select the delimiter used to separate the columns of data.

42. 10.4.3 Importing Data Using the Text Import Wizard Figure 10.37 Text Import Wizard: Step 3 of 3 Click a column in the Data preview area and then select a data format.

43. 10.4.3 Importing Data Using the Text Import Wizard Figure 10.38 Importing a text file into a workbook

44. 10.4.4 Importing Data from External Sources Figure 10.39 Select Data Source dialog box Launch the Data Connection Wizard to establish a connection to an external data source.

45. 10.4.4 Importing Data from External Sources Figure 10.40 Import Data dialog box Specify the external data range in the worksheet. Edit the data connection query parameters before importing the data. This is an advanced feature. Specify options for refreshing (re-importing) and formatting the data.

46. 10.4.4 Importing Data from External Sources Figure 10.41 Importing a Microsoft Access table object The external data range begins at cell A1 and extends down and to the right until all records and fields are imported.

47. You must save the query definition so that Microsoft Excel knows how to refresh the data in the worksheet. Specify when to run the query definition and refresh the data. As specified using these options, the data is imported with field names in row 1 and columns are formatted to their best-fit width. Use these options to specify how data is to be entered into the worksheet when refreshed. 10.4.4 Importing Data from External Sources Figure 10.42 External Data Range Properties dialog box

48. Microsoft Office Web Components are used to display the results of the ODC data file connection. The ODC file is stored in the My Data Sources folder, beneath My Documents, and can be viewed using the File, Open command. 10.4.4 Importing Data from External Sources Figure 10.43 Opening a data connection in Microsoft Internet Explorer

49. 10.4.5 Sharing Data in Microsoft Office XP Figure 10.44 Opening the EX1045 workbook

50. Microsoft Word’s Menu bar and toolbars are similar to those found in Microsoft Excel. The New Document task pane is used to create and open documents. A blank document appears in the work area when Microsoft Word is started. 10.4.5 Sharing Data in Microsoft Office XP Figure 10.45 Microsoft Word application window