DATA TYPES
There are many ways to represent data on a computer. A standard way to represent data and text is to use ascii, shorthand for American Standard Code for Information Interchange. This is often called text form and often has the filename extension .txt or .asc
To be easily read in text data files must be fixed width or delimited
data.
Fixed width data has in every row the same format. For example,
the first data entry is in columns 1-4, the second data entry is in columns
5-6, and so on. So the entry 154879... would be read as first data entry
of 1548, second data entry of 79 and so on.
Delimited data has a special character indicating a break between
one entry and the next.
Programs such as Excel have their own way of representing data and associated information such as column headings and row and column location of the data. Such data is in general not transferrable across programs. One such example of this is that even for Excel, earlier versions of Excel may be unable to open a file saved by a later version of Excel.
Excel organizes it data into worksheets, explained below. Excel is able
to read data from some other formats, notably earlier versions of Excel,
other spreadsheet programs such as Quattro-Pro and Lotus 1-2-3, and data
base programs such as Access and DBase. But Excel cannot read in data from
all other formats.
WORKBOOKS AND WORKSHEETS
Excel saves data in a file called a workbook. Each workbook can contain many worksheets, allowing organization of various kinds of related information in a single file. A worksheet is used to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets. Charts and other data analysis can be placed either on the worksheet with its related data or on a separate chartsheet or worksheet. The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs.
In many cases one will read data into the first worksheet in a new workbook.
METHODS TO INPUT DATA
Common ways to input data into Excel are:
1. Type in data into a new Excel worksheet
Open Excel, for example from Start Menu | Programs.
This immediately puts you into a new Excel worksheet, or prompts you
to ask for a new Excel worksheet.
[If instead you want the new worksheet to be in an existing workbook
then in the existing workbook choose Insert | Worksheet]
Consider entry of the following data on the number of cars in a household
(first column) and the number of persons in the household (second column).
| 1 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
Type the data into cells A1:A5 and cells B1:B5. (Click on cell A1 and enter 1 and then enter, then 2 and enter ....)
Then give headers to the two columns.
Put cursor in cell A1 and choose Insert / Row.
Then in the new blank cell A1 type cars and in the new blank cell A2
type hh size.
Now headers (CARS and HH SIZE) are in row 1 and data are in rows 2 to
6. The final data are:
| CARS | HH SIZE |
| 1 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
Next give the worksheet a name.
Go to the sheet tab at the bottom, which may be called Sheet 1, right-click
and select rename and give the worksheet a name.
Finally save the workbook by using File | Save As if a new workbook
or File | Save is saving an existing workbook.
If typing in a lot of data it is obviously best to save the file a
number of times before typing is finished.
2. Read in data from an existing Excel workbook
This is the easiest.
Open Excel, for example from Start Menu | Programs.
Choose File / Open and give the filename or browse for the file you
want to input.
Usually the filename will have extension .xls
3. Copy data from an existing Excel worksheet
Suppose we are already in an existing worksheet within an existing workbook.
Select Edit | Move or Copy Sheet
If copy is to the existing workbook (the default) then most often one
chooses the (move to end) option in Before Sheet.
If copy is to a new workbook then choose the (new book) option in to
book and type in the name of the new book.
In either case select the Create a Copy option.
Give the new worksheet an appropriate name by going to the sheet tab
at the bottom, right-click and select rename and give the worksheet a name.
4. Read in data from an existing text file
I suppose you first need to obtain the data off the internet.
If you already have the text file with data then skip immediately to
3b.
4a. Obtain text file with data from the internet
Use a web browser to go to the course web-site http://www.econ.ucdavis.edu/faculty/cameron.
Click on Excel and to the heading Excel Basics: Data Example.
Click on Excel Basics: Data Example – this will show the data.
| 1. | 1. |
| 2. | 2. |
| 2. | 3. |
| 2. | 4. |
| 3. | 5. |
Note that the data is both fixed width and that there is at least one
space between each piece of data so that the data is also space-delimited
text data.
The file is a textfile, it is not a spreadsheet file.
Choose File / Save As in Netscape to save the data as cars.asc in directory
of your choosing, say c:temp
You can choose a different directory and/or filename if you prefer.
The extension .asc is short for ascii which is shorthand for american standard
code for information intechange and is the standard way for computers to
represent text in binary code. A more common term is text-file or .txt
4b. Enter the text file with data into Excel
Open Excel, from Start Menu | Programs.
Choose File | Open and the option Files of type | All types to read
in c:/temp/cars.asc.
In text import wizard
- choose delimited if the data are delimited and fixed width if the
data are fixed width
- if there are several lines of text before the data then give the
line number of the first line of data in Start import at row
- hit next
- make appropriate selection such as select space (and deselect tab)
for space delimited data, hit next and then finish.
This should give you 2 columns and 5 rows in cells A1:B5.
Alternatives are that data are delimited by commas or tabs in which case one would instead choose the relevant alternative, and fixed width.
It is a good idea then to save the data as an excel workbook or worksheet
in a workbook, and copy on to your own diskette on the a: drive.
Also add the data headings for CARS and HH SIZE (see 2 above). The
final data is
| CARS | HH SIZE |
| 1 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 5 |
4. Read in data from files in other formats
Excel will read in data from a number of formats including other spreadsheet
programs such as Quattro Pro and Lotus 1-2-3.
For further information on how to use Excel go to
http://www.econ.ucdavis.edu/faculty/cameron