Exploring Microsoft Excel 2016 Series Editor Mary Anne

Exploring Microsoft® Excel® 2016 Series Editor Mary Anne Poatsy Mulbery|Davidson Series Created by Dr. Robert T. Grauer

Chapter 10 Imports, XML, and Power Add-Ins Managing Data Copyright © 2017 Pearson Education, Inc.

Objectives • Import Data from External Sources • Manage Connections • Convert Text to Columns • Manipulate Text with Functions • Use Flash Fill Copyright © 2017 Pearson Education, Inc.

Objectives • Understand XML Syntax • Import XML Data into Excel • Use Power Pivot Functionality • Import Data with Power Query • Visualize Data with Power View Copyright © 2017 Pearson Education, Inc.

Objective 1: Import Data from External Sources • Skills: Import a Text File Import an Access Database Table or Query Import Data from Other Sources Copyright © 2017 Pearson Education, Inc.

Import Data from External Sources Copyright © 2017 Pearson Education, Inc.

Import Data from External Sources Copyright © 2017 Pearson Education, Inc.

Import Data from External Sources Delimited Has headers Preview Copyright © 2017 Pearson Education, Inc.

Import Data from External Sources Tab Preview Copyright © 2017 Pearson Education, Inc.

Import Data from External Sources Column data format Preview Copyright © 2017 Pearson Education, Inc.

Import Data from External Sources Select Transactions Select Table Copyright © 2017 Pearson Education, Inc.

Import Data from External Sources Copyright © 2017 Pearson Education, Inc.

Import Data from External Sources Click From Other Sources Copyright © 2017 Pearson Education, Inc.

Objective 2: Manage Connections Skills: Set Connection Properties Copyright © 2017 Pearson Education, Inc.

Manage Connections Copyright © 2017 Pearson Education, Inc.

Objective 3: Convert Text to Columns Skills: Convert Text to Columns Copyright © 2017 Pearson Education, Inc.

Convert Text to Columns Copyright © 2017 Pearson Education, Inc.

Objective 4: Manipulate Text with Functions Skills: Combine Text with CONCATENATE Function Change Text Case with Text Functions Use the SUBSTITUTE Function Use Other Text Functions Copyright © 2017 Pearson Education, Inc.

Manipulate Text with Functions • Excel Text functions: §CONCATENATE—individual text strings into one text string =CONCATENATE(text 1, text 2) §PROPER—capitalizes the first letter of each word in a text string =PROPER(text) §UPPER—converts text strings to uppercase letters =UPPER(text) §LOWER—converts text strings to lowercase =LOWER(text) Copyright © 2017 Pearson Education, Inc.

Manipulate Text with Functions CONCATENATE Copyright © 2017 Pearson Education, Inc.

Manipulate Text with Functions • Excel Text functions: §SUBSTITUTE—replaces new text for old text in a text string =SUBSTITUTE(text, old_text, new_text, instance_num) §LEFT—returns the specified number of characters from the start of a text string LEFT(Text, Num_chars) §RIGHT—returns the specified number of characters from the end of a text string RIGHT(Text, Num_chars) Copyright © 2017 Pearson Education, Inc.

Objective 5: Use Flash Fill Skills: Use Flash Fill Copyright © 2017 Pearson Education, Inc.

Use Flash Fill Copyright © 2017 Pearson Education, Inc.

Objective 6: Understand XML Syntax Skills: Understand XML Syntax Copyright © 2017 Pearson Education, Inc.

Understand XML Syntax 3 bedrooms/2 bathrooms–$1, 000 permonth–(305)555 -1234 <Apartment> <Bedrooms>3</Bedrooms> <Bathrooms>2</Bathrooms> <Rent>$1, 000</Rent> <Telephone>(305) 555 -1234</Telephone> </Apartment> People Computers Copyright © 2017 Pearson Education, Inc.

Understand XML Syntax Copyright © 2017 Pearson Education, Inc.

Objective 7: Import XML Data into Excel Skills: Import XML Data Refresh Imported XML Data Copyright © 2017 Pearson Education, Inc.

Import XML Data into Excel Link options Copyright © 2017 Pearson Education, Inc.

Import XML Data into Excel XML Source pane Worksheet area Copyright © 2017 Pearson Education, Inc.

Objective 8: Use Power Pivot Functionality Skills: Load the Power Pivot Add-In Import Data with Power Pivot Create Relationships with Power Pivot Create a Pivot. Table with Power Pivot Copyright © 2017 Pearson Education, Inc.

Use Power Pivot Functionality • Power Pivot—add-in included in Excel’s standard Pivot. Table options • Key features: § Handling and compressing big data § Identifying and displaying key performance indicators § Ability to import data from a vast array of sources § Ability to create relationships between multiple related data tables Copyright © 2017 Pearson Education, Inc.

Use Power Pivot Functionality Primary table Related name Copyright © 2017 Pearson Education, Inc.

Objective 9: Import Data with Power Query Skills: Import Data with Power Query Copyright © 2017 Pearson Education, Inc.

Import Data with Power Query New Query Close & Load Copyright © 2017 Pearson Education, Inc. Formatting options

Objective 10: Visualize Data with Power View Skills: Visualize Data with Power View Copyright © 2017 Pearson Education, Inc.

Visualize Data with Power View Drag to the desired location on the Power View dashboard Select filters Aggregate calculations Copyright © 2017 Pearson Education, Inc.

Summary Importing data files: §Text §Access §XML Using: §Get External Data §Power Pivot §Power Query Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2017 Pearson Education, Inc.