Excel 2013 Level 2 Unit 2 Managing and Integrating Data and the Excel Environment

2. Excel 2013 Level 2 Unit 2Managing and Integrating Data and the Excel Environment Chapter 8Importing, Exporting, and Distributing Data

3. Importing, Exporting, and Distributing Data Quick Links to Presentation Contents • Import Data into Excel • Export Data from Excel • CHECKPOINT 1 • Prepare a Workbook for Distribution • View Trust Center Settings • Distribute Workbooks • CHECKPOINT 2

4. Import Data into Excel • The Get External Data group on the DATA tab contains buttons used to import data from external sources into an Excel worksheet. • During an import or export routine, the program containing the original data is called the source. • The program to which the data is being copied, embedded, or linked is called the destination.

5. Import Data into Excel - continued To import an Access table: • Make active cell at which to begin import. • Click DATA tab. • Click From Access button. • Navigate to drive and/or folder. • Double-click source database file name. • If necessary, click desired table name at Select Table dialog box. • Click OK. continues on next slide… Select Table dialog box

6. Import Data into Excel - continued • At Import Data dialog box, select desired view format. • Click OK. Import Data dialog box

7. Import Data into Excel - continued To import data from a web page: • Make active cell at which to begin import. • Click DATA tab. • Click From Web button. • Navigate to desired web page. • At New Web Query dialog box, click arrows next to tables to import. • Click Import button. • Click OK. New Web Query dialog box

8. Import Data into Excel - continued To import data from a comma separated text file: • Make active cell at which to begin import. • Click DATA tab. • Click From Text button. • Double-click .csv file name. • At Text Import Wizard – Step 1 of 3 dialog box, click Next. continues on next slide… Text Import Wizard – Step 1 of 3 dialog box

9. Import Data into Excel - continued • At Text Import Wizard – Step 2 of 3 dialog box, click Comma check box. • Click Next. continues on next slide… At the Text Import Wizard – Step 2 of 3 dialog box

10. Import Data into Excel - continued • At Text Import Wizard – Step 3 of 3 dialog box, click Finish. • Click OK. At the Text Import Wizard – Step 3 of 3 dialog box

11. Export Data from Excel • Excel data can be exported for use in other programs by copying the cells to the Clipboard task pane and then pasting them into the destination document, or by saving the worksheet as a separate file in another file format.

12. Export Data from Excel - continued To append Excel data to an Access table: • Select cells. • Click Copy button. • Start Access. • Open database. • Open table in Datasheet view. • Click Paste button arrow. • Click Paste Append option. • Click Yes. • Deselect posted range. Paste Append option

13. Export Data from Excel - continued • Use the copy and paste method if the data being brought into Word will not likely be updated or require editing after the source cells are pasted in the Word document. • Copy and embed the data if you want to be able to edit the data in Word using Excel’s editing tools and features. • Copy and link the data if the information being pasted into Word will likely be changed in the future and you want the document in Word updated if the data in the source file changes.

14. Export Data from Excel - continued To embed Excel data in a Word document: • Select cells. • Click Copy button. • Open Word document. • Position insertion point at desired location. • Click Paste button arrow. • Click Paste Special option. • Click Microsoft Excel Worksheet Object option. • Click OK. Microsoft Excel Worksheet Object option

15. Export Data from Excel - continued To link Excel data in a Word document: • Select cells. • Click Copy button. • Open Word document. • Position insertion point at desired location. • Click Paste button arrow. • Click Paste Special option. • Click Microsoft Excel Worksheet Object option. • Click Paste link. • Click OK. Paste link option

16. Export Data from Excel - continued To break a link to an Excel object: • Open document. • Right-click linked object. • Point to Linked Worksheet Object option. • Click Linksoption. continues on next slide… Links option

17. Export Data from Excel - continued • At Links dialog box, click Break Link button. • Click Yes. • Save document. Break Link button

18. Export Data from Excel - continued • PowerPoint presentations often incorporate charts to visually depict numerical data in a graph format that is easy to understand. • Although you can create tables and charts in a PowerPoint slide, you may prefer to use Excel for these tasks and then copy and paste the data in to PowerPoint. • As with Word, you can copy and paste, copy and embed, or copy and link Excel data to slides in a PowerPoint presentation.

19. Export Data from Excel - continued To :

20. Export Data from Excel - continued To export a worksheet as a text file: • Make desired sheet active. • Click FILE tab. • Click Export option. • Click Change File Type option. • Click desired text file type in Other File Types section. • Click Save As button. • If necessary, navigate to desired drive and/or folder. • Type file name. • Click Save button. • Click OK. • Click Yes. Change File Type option

21. CHECKPOINT 1 • This group on the DATA tab contains buttons used to import data from external sources. • Get External Data • Get Internal Data • Data Extractor • Data Layout • Linking Excel data to a Word document means that the source data exists only in this program. • Word • Excel • PowerPoint • Access Answer Answer Next Question Next Question • The Text Import Wizard contains how many steps? • 1 • 2 • 3 • 4 • Presentations often incorporate these to visually depict numerical data. • forms • tables • charts • objects Answer Answer Next Question Next Slide

22. Prepare a Workbook for Distribution • In today’s workplace, you often work as part of a team both within and outside your organization. • Excel workbooks are frequently exchanged between workers via email message attachments; by saving to a shared network folder, a document management server, or a company website; or by other means of electronic distribution.

23. Prepare a Workbook for Distribution - continued • Prior to distributing a workbook electronically to others, you should consider using the Document Inspector feature to scan the workbook for personal or other hidden information that you would not want others to be able to view.

24. Prepare a Workbook for Distribution - continued To use the Document Inspector to remove private information: • Open workbook. • Click FILE tab. • Click Check for Issues button. • Click Inspect Document option. • Clear check boxes for those items that you do not want to scan and remove. • Click Inspect button. continues on next slide… Inspect button

25. Prepare a Workbook for Distribution -continued • Click Remove All button in those sections with items that you want removed. • Click Close button. Remove All button

26. Prepare a Workbook for Distribution - continued • A workbook that will be distributed to others can be marked as final, which means it is protected from additions, deletions, and modifications.

27. Prepare a Workbook for Distribution -continued To mark a workbook as final: • Open workbook. • Click FILE tab. • Click Protect Workbook button. • Click Mark as Final option. • Click OK twice. Mark as Final option

28. Prepare a Workbook for Distribution -continued To check a workbook for compatibility: • Open workbook. • Click FILE tab. • Click Check for Issues button. • Click Check Compatibility option. continues on next slide… Check Compatibility option

29. Prepare a Workbook for Distribution - continued • Read information in Summarylist box. • If desired, click Copy to New Sheet button OR click Close. Summary list box

30. View Trust Center Settings for Excel • The Trust Center maintains a Trusted Locations list that keeps track of locations from which content can be considered trusted. • When you add a location to the Trusted Locations list, Excel will treat any files opened from that location as safe. • Workbooks opened from trusted locations do not cause a security warning to display in the message bar and none of their content will be blocked.

31. View Trust Center Settings for Excel -continued • If a workbook contains macros, the Trust Center checks for a valid and current digital signature from an entity in the Trusted Publishers list before it enables the macros. • The Trusted Publishers list is maintained by you on the computer you are using. • You can add a publisher to the Trusted Publishers list by enabling content from that publisher and then clicking the Trust all content from this publisher option.

32. View Trust Center Settings for Excel -continued To view the Trust Center options: • Click FILE tab. • Click Options. • Click Trust Center option in left pane. • Click Trust Center Settings button. • At Trust Center dialog box, click desired Trust Center category in left pane. • View and/or modify required options. • Click OK twice. Trust Center dialog box

33. View Trust Center Settings for Excel -continued

34. Distribute Workbooks • When several different users need access to a single document, many organizations create a document management server or network share folder from which users can retrieve files. • A popular method of distributing documents that travel over the Internet is to publish the workbook as a PDF or XPS document. • A workbook can also be published as a web page to make the content available on the Internet.

35. Distribute Workbooks - continued To publish a worksheet as a PDF: • Open workbook. • Click FILE tab. • Click Export option. • Click Create PDF/XPS button. • Click Publish button. Create PDF/XPS button

36. Distribute Workbooks - continued To publish a worksheet as XPS: • Open workbook. • Click FILE tab. • Click Export option. • Click Create PDF/XPS button. • Click Save as type button. • Click XPS Document (*.xps) option. • Click Publish button. XPS Document (*.xps) option

37. Distribute Workbooks - continued To publish a worksheet as a web page: • Open workbook. • Click FILE tab. • Click Export option. • Click Change File Type. • Click Save as AnotherFile Type option. • Click Save As button. • Click Save as type option box. • Click Single File Web Page (*.mht; *.mhtml) option. • If necessary, change drive and/or folder and/or file name. continues on next slide… Change Title button

38. Distribute Workbooks - continued • Click Change Title button. • At Publish as Web Page dialog box, type title. • Click OK. • Click Publish button. • Set desired publishing options. • Click Publish. Publish button

39. CHECKPOINT 2 • You should consider using this feature to scan your workbook. • Document Tracker • Document Inspector • Document Scanner • Document Editor • This standard was developed by Adobe and has become a popular choice for sharing files. • XPS • PXS • PFD • PDF Answer Answer Next Question Next Question • A workbook that will be distributed to others can be marked as this. • inspected • standard • final • review • This was developed by Microsoft with the Office 2007 suite. • XPS • PXS • PFD • PDF Answer Answer Next Question Next Slide

40. Importing, Exporting, and Distributing Data Summary of Presentation Concepts • Import data from an Access table, a website, and a text file • Append data from an Excel worksheet to an Access table • Embed and link data in an Excel worksheet to a Word document • Copy and paste data in an Excel worksheet to a PowerPoint presentation • Export data as a text file • Scan and remove private or confidential information from a workbook • Mark a workbook as final • Check a workbook for features incompatible with earlier versions of Excel • View Trust Center settings • Save an Excel worksheet as a PDF or XPS file • Save an Excel worksheet as a Web page