Excel 2010 Level 2 Unit 2 Managing and Integrating Data and the Excel Environment Chapter 8 Importing, Exporting, a

2. Excel 2010 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 • Distribute Workbooks • CHECKPOINT 2

4. Import Data into Excel • The Get External Data group in 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 the cell at which to begin the import. • Click the Data tab. • Click the From Access button in the Get External Data group. • Navigate to the drive and/or folder. • Double-click the source database file name. • If necessary, click the desired table name at the Select Table dialog box. • Click OK. continues on next slide… Select Table dialog box

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

7. Import Data into Excel…continued To import data from a web page: • Make active the cell at which to begin the import. • Click the Data tab. • Click the From Web button in the Get External Data group. • Navigate to the desired web page. • At the New Web Query dialog box, click the arrows next to the tables to import. • Click the 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 the cell at which to begin the import. • Click the Data tab. • Click the From Text button in the Get External Data group. • Double-click the .csv file name. • At the 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 the Text Import Wizard – Step 2 of 3 dialog box, click the 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 the 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 and pasting 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 the cells. • Click the Copy button. • Start Access. • Open the database. • Open the table in Datasheet view. • Click the Paste button arrow. • Click the Paste Append option. • Click Yes. • Deselect the posted range. Paste Append option

13. Export Data from Excel…continued • Use the copy and paste method if the data being brought into Word is not likely to require updating or editing. • Copy and embed the data being brought into Word if you want to have the ability to edit it using Excel’s editing tools and features. • Copy and link the data being brought into Word if the source file is likely to be changed in the future and you want the data to be updated in Word as well.

14. Export Data from Excel…continued To embed Excel data in a Word document: • Select the cells. • Click the Copy button. • Open the Word document. • Position the insertion point at the desired location. • Click the Paste button arrow. • Click the Paste Special option. • Click the 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 the cells. • Click the Copy button. • Open the Word document. • Position the insertion point at the desired location. • Click the Paste button arrow. • Click the Paste Special option. • Click the Microsoft Excel Worksheet Object option. • Click Paste link. • Click OK. Paste link

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

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

18. Export Data from Excel…continued • PowerPoint presentations often incorporate charts to visually depict numerical data and make it easier to understand. • Although you can create tables and charts directly in a PowerPoint presentation, you can also create them in Excel and then copy and paste the data 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 the desired sheet active. • Click the File tab. • Click the Save & Send tab. • Click the Change File Type option. • Click the desired text file type in the Other File Types section. • Click the Save As button. • If necessary, navigate to the desired drive and/or folder. • Type the file name. • Click the Save button. • Click OK. • Click Yes. Change File Type option

21. CHECKPOINT 1 • This group in 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 the workbook. • Click the File tab. • Click the Check for Issues button. • Click the Inspect Document option. • Clear the check boxes for those items that you do not want to scan and remove. • Click the Inspect button. continues on next slide… Inspect button

25. Prepare a Workbook for Distribution…continued • Click the Remove All button in those sections with items that you want removed. • Click the 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 no additions, deletions, or modifications can be made to the cells.

27. Prepare a Workbook for Distribution…continued To mark a workbook as final: • Open the workbook. • Click the File tab. • Click the Protect Workbook button. • Click the 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 the workbook. • Click the File tab. • Click the Check for Issues button. • Click the Check Compatibility option. continues on next slide… Check Compatibility option

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

30. 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.

31. Distribute Workbooks…continued To publish a worksheet as a PDF: • Open the workbook. • Click the File tab. • Click the Save & Send tab. • Click the Create PDF/XPS Document option. • Click the Create PDF/XPS button. • Click the Publish button. Create PDF/XPS button

32. Distribute Workbooks…continued To publish a worksheet as XPS: • Open the workbook. • Click the File tab. • Click the Save & Send tab. • Click the Create PDF/XPS Document option. • Click the Create PDF/XPS button. • Click the Save as type button. • Click the XPS Document (*.xps) option. • Click the Publish button. XPS Document (*.xps) option

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

34. Distribute Workbooks…continued • At the Publish as Web Page dialog box, type the title. • Click OK. • Click the Publish button. • Set the desired publishing options. • Click Publish. Publish button

35. Distribute Workbooks…continued To send a workbook via email: • Open the workbook. • Click the File tab. • Click the Save & Send tab. • Click the Send as Attachment button. • Type the recipient’s email address in the To text box. • If necessary, edit the Subject text. • Type the message in the message window. • Click Send. Send as Attachment button

36. Distribute Workbooks…continued To save a workbook to SkyDrive: • Open the workbook. • Click the File tab. • Click the Save & Send tab. • Click Save to Web. • Click the Sign In button. • Type a Windows Live email address. • Press tab. • Type the Windows Live password. • Click OK. • If necessary, select the desired folder name. • Click the Save As button. • Click the Save button. Sign In button

37. 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

38. 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 • Save an Excel worksheet as a PDF or XPS file • Save an Excel worksheet as a Web page • Send an Excel worksheet via an email message • Save an Excel worksheet to SkyDrive