Excel Scientific and Engineering Cookbook

Chapter 3. Collecting and Cleaning Up Data

Introduction

Chances are you’re going to have to deal with data that’s not already in the form of an Excel file. This data may be standard scientific and engineering data (such as element properties or thermodynamic tables), or it could be the results of other computer simulations (such as computation fluid dynamics results or galaxy formation simulation results), or it could be experimental data obtained from any of a wide variety of scientific and engineering measurements. In many cases such data would be in the form of text files that could be imported into Excel for further analysis. Text files are common because they are universal, they are easy to generate in custom programs, and you don’t have to have specialized software to read them. Still other data that you may want to process in Excel could reside in nontext files such as database files. This chapter will show you how to import data into Excel from these and other sources. Further, I’ll show you how to massage that data once it’s in Excel, making it easier to manipulate and analyze.

3.1. Importing Data from Text Files

Problem

You have some data you’d like to analyze in Excel but it’s located in a separate text file.

Solution

Open the text file in Excel using File → Open....

Discussion

Excel is capable of reading many different file formats, not just Excel spreadsheet files. Opening a text file is very much like opening a spreadsheet file: simply use File → Open... (or use the shortcut ...