© paradigm publishing, inc. 1. 2 excel 2013 level 2 unit 2managing and integrating data and the...


Excel 2013

Level 2

Unit 2 Managing and Integrating Dataand the Excel Environment

Chapter 8 Importing, Exporting, and Distributing Data


Importing, Exporting, and Distributing Data

Import Data into Excel Export Data from Excel CHECKPOINT 1 Prepare a Workbook for Distribution View Trust Center Settings Distribute Workbooks CHECKPOINT 2

Quick Links to Presentation Contents


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.


Import Data into Excel - continuedTo import an Access table:1. Make active cell at which to

begin import.2. Click DATA tab.3. Click From Access button.4. Navigate to drive and/or

folder.5. Double-click source database

file name.6. If necessary, click desired

table name at Select Table dialog box.

7. Click OK.

continues on next slide…

Select Table dialog box


Import Data into Excel - continued8. At Import Data dialog box,

select desired view format.9. Click OK.

Import Data dialog box


Import Data into Excel - continuedTo import data from a web page:1. Make active cell at which to

begin import.2. Click DATA tab.3. Click From Web button.4. Navigate to desired web

page.5. At New Web Query dialog

box, click arrows next to tables to import.

6. Click Import button.7. Click OK. New Web Query

dialog box


Import Data into Excel - continuedTo import data from a comma separated text file:1. Make active cell at which to

begin import.2. Click DATA tab.3. Click From Text button.4. Double-click .csv file name.5. 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


Import Data into Excel - continued6. At Text Import Wizard –

Step 2 of 3 dialog box, click Comma check box.

7. Click Next.


At the Text Import Wizard – Step 2 of 3 dialog box


Import Data into Excel - continued8. At Text Import Wizard –

Step 3 of 3 dialog box, click Finish.

9. Click OK.

At the Text Import Wizard – Step 3 of 3 dialog box


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.


Export Data from Excel - continuedTo append Excel data to an Access table:1. Select cells.2. Click Copy button.3. Start Access.4. Open database.5. Open table in Datasheet view.6. Click Paste button arrow.7. Click Paste Append option.8. Click Yes.9. Deselect posted range.

Paste Append option


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.


Export Data from Excel - continuedTo embed Excel data in a Word document:1. Select cells.2. Click Copy button.3. Open Word document.4. Position insertion point at

desired location.5. Click Paste button arrow.6. Click Paste Special option.7. Click Microsoft Excel

Worksheet Object option.8. Click OK. Microsoft Excel Worksheet Object

option


Export Data from Excel - continuedTo link Excel data in a Word document:1. Select cells.2. Click Copy button.3. Open Word document.4. Position insertion point at

desired location.5. Click Paste button arrow.6. Click Paste Special option.7. Click Microsoft Excel

Worksheet Object option.8. Click Paste link.9. Click OK.

Paste link option


Export Data from Excel - continuedTo break a link to an Excel object:1. Open document.2. Right-click linked

object.3. Point to Linked

Worksheet Object option.

4. Click Links option.

continues on next slide…Links

option


Export Data from Excel - continued5. At Links dialog box, click Break Link button.6. Click Yes.7. Save document.

Break Link button


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.


Export Data from Excel - continuedTo :Text File Format Option File Extension

Text (tab delimited) .txt

Unicode text .txt

CSV (Comma delimited) .csv

Formatted text (Space delimited) .prn

Text (Macintosh) .txt

Text (MS-DOS) .txt

CSV (Macintosh) .csv

CSV (MS-DOS) .csv


Export Data from Excel - continuedTo export a worksheet as a text file:1. Make desired sheet active.2. Click FILE tab.3. Click Export option.4. Click Change File Type option.5. Click desired text file type in Other File Types section.6. Click Save As button.7. If necessary, navigate to desired drive and/or folder.8. Type file name.9. Click Save button.10. Click OK.11. Click Yes.

Change File Type option

Contents© Paradigm Publishing, Inc. 21

CHECKPOINT 11) This group on the DATA tab

contains buttons used to import data from external sources.a. Get External Datab. Get Internal Datac. Data Extractord. Data Layout

3) Linking Excel data to a Word document means that the source data exists only in this program.a. Wordb. Excelc. PowerPointd. Access

2) The Text Import Wizard contains how many steps?a. 1b. 2c. 3d. 4

4) Presentations often incorporate these to visually depict numerical data.a. formsb. tablesc. chartsd. objects

Next Question

Next Question

Next Question

Next Slide

Answer

Answer

Answer

Answer


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.


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.


Prepare a Workbook for Distribution - continuedTo use the Document Inspector to remove private information:1. Open workbook.2. Click FILE tab.3. Click Check for Issues

button.4. Click Inspect Document

option.5. Clear check boxes for those

items that you do not want to scan and remove.

6. Click Inspect button.continues on next slide…

Inspect button


Prepare a Workbook for Distribution -continued7. Click Remove All button

in those sections with items that you want removed.

8. Click Close button.

Remove All button


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.


Prepare a Workbook for Distribution -continuedTo mark a workbook as final:1. Open workbook.2. Click FILE tab.3. Click Protect Workbook

button.4. Click Mark as Final

option.5. Click OK twice.

Mark as Final option


Prepare a Workbook for Distribution -continuedTo check a workbook for compatibility:1. Open workbook.2. Click FILE tab.3. Click Check for Issues

button.4. Click Check

Compatibility option.


Check Compatibility option


Prepare a Workbook for Distribution - continued5. Read information in

Summary list box.6. If desired, click Copy to

New Sheet button OR click Close.

Summary list box


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.


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.


View Trust Center Settings for Excel -continuedTo view the Trust Center options:1. Click FILE tab.2. Click Options.3. Click Trust Center option in

left pane.4. Click Trust Center Settings

button.5. At Trust Center dialog box,

click desired Trust Center category in left pane.

6. View and/or modify required options.

7. Click OK twice.

Trust Center dialog box


View Trust Center Settings for Excel -continued

Macro Setting Description

Disable all macros without notification

All macros are disabled; security alerts will not appear.

Disable all macros with notification

All macros are disabled; security alert appears with the option to enable content if you trust the source of the file. This is the default setting.

Disable all macros except digitally signed macros

A macro that does not contain a digital signature is disabled; security alerts do not appear. If the macro is digitally signed by a publisher in your Trusted Publishers list, the macro is allowed to run. If the macro is digitally signed by a publisher not in your Trusted Publishers list, a security alert appears.

Enable all macrosAll macros are allowed; security alerts do not appear. This option is not recommended as it can allow dangerous code to run.


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.


Distribute Workbooks - continuedTo publish a worksheet as a PDF:1. Open workbook.2. Click FILE tab.3. Click Export option.4. Click Create

PDF/XPS button.5. Click Publish button.

Create PDF/XPS button


Distribute Workbooks - continuedTo publish a worksheet as XPS:1. Open workbook.2. Click FILE tab.3. Click Export option.4. Click Create PDF/XPS

button.5. Click Save as type button.6. Click XPS Document (*.xps)

option.7. Click Publish button.

XPS Document (*.xps) option


Distribute Workbooks - continuedTo publish a worksheet as a web page:1. Open workbook.2. Click FILE tab.3. Click Export option.4. Click Change File Type.5. Click Save as Another

File Type option.6. Click Save As button.7. Click Save as type option box.8. Click Single File Web Page

(*.mht; *.mhtml) option.9. If necessary, change drive

and/or folder and/or file name.


Change Title button


Distribute Workbooks - continued10. Click Change Title

button.11. At Publish as Web

Page dialog box, type title.

12. Click OK.13. Click Publish button.14. Set desired

publishing options.15. Click Publish.

Publish button

Contents© Paradigm Publishing, Inc. 39

CHECKPOINT 21) You should consider using this

feature to scan your workbook.a. Document Trackerb. Document Inspectorc. Document Scannerd. Document Editor

3) This standard was developed by Adobe and has become a popular choice for sharing files.a. XPSb. PXSc. PFDd. PDF

2) A workbook that will be distributed to others can be marked as this.a. inspectedb. standardc. finald. review

4) This was developed by Microsoft with the Office 2007 suite.a. XPSb. PXSc. PFDd. PDF

Next Question

Next Question

Next Question

Next Slide

Answer

Answer

Answer

Answer


Importing, Exporting, and Distributing Data

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

Summary of Presentation Concepts

© paradigm publishing, inc. 1. 2 excel 2013 level 2 unit 2managing and integrating data and the...

Documents

data import data

excel excel data

excel import data

data slide

excel export data

import data dialog box

data dialog box slide

data tab