Excel 2019 Bible

579

C H A P T E R

25

Importing and Cleaning Data

IN THIS CHAPTER

Importing data into Excel

Manipulating and cleaning data

Using Flash Fill to extract and concatenate data

Reviewing a checklist for data cleaning

Exporting data to other formats

D

ata is everywhere. For example, if you run a website, you’re collecting data continually, and

you may not even know it. Every visit to your site generates information that is stored in

a file on your server. This file contains lots of useful information, if you take the time to

examine it.

That’s just one example of data collection. Virtually every automated system collects data and

stores it. Most of the time, the system that collects the data is also equipped to verify and analyze

the data—but not always. And, of course, data is also collected manually. A nonautomated tele-

phone survey is a good example.

Excel is a good tool for analyzing data, and it’s often used to summarize the information and dis-

play it in the form of tables and charts. But often, the data that’s collected isn’t perfect. For one

reason or another, it needs to be cleaned up before it can be analyzed.

One common use for Excel is as a tool to clean up data. Cleaning up data involves getting raw data

into a worksheet and then manipulating it so that it conforms to various requirements. In the pro-

cess, the data will be made consistent so that it can be properly analyzed.

This chapter describes the various ways to get data into a worksheet, and it provides some tips to

help you clean it up.

Importing Data

Before you can do anything with data, you must get it into a worksheet. Excel is able to import

most common text file formats, and it can retrieve data from websites.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.