EXCEL 97: Data Manipulation

A. Colin Cameron, Dept. of Economics, Univ. of Calif. - Davis

This September 1999 help sheet gives information on how to


CREATE NEW DATA BY TRANSFORMING EXISTING DATA

Suppose you have open a data set with the following information (cars.xls).
 
CARS HH SIZE
   1    1
   2    2
   2    3
   2    4
   3    5

Now  additionally create data on cars per person in household.
This data will be included in cells C2:C6 under the heading CARS PER PERSON.

To do this in cell C2 type =A2/B2
Then cell C2 is the entry in A2 divided by that in B2.

Now cut and paste to change the remaining entries in column C.
Highlight cell C2 and copy by CTRL-C or by Edit / Copy.
Then highlight cells C3:C6 and paste by CTRL-V or by Edit / Paste.

Finally in cell C1 type CARS PER PERSON.

The spreadsheet cells A1:C6 should look like:
 
CARS HH SIZE CARS PER PERSON
   1    1      1
   2    2      1
   2    3     0.666667
   2    4     0.5
   3    5    0.6

The ability to manipulate data like this is a great attraction of spreadsheets.
 

CELL REFERENCES

Cell reference examples are

SELECTING OR HIGHLIGHTING DATA

Many Excel commands involve selecting or highlighting data. Do this by

For long arrays this can require a lot of scrolling.
CTRL-down arrow moves automatically to the bottom of an array.
CTRL-up arrow moves to the top,. CTRL-right arrow to the right end of the arrow and so on.

Thus to select or highlight a long array


SORT DATA

Suppose we wish to order the newly created data in ascending order by cars per person.
Then select cells A2:C6 and choose Data | Sort and then Sort by CARS PER PERSON in ascending order.
 

DELETE ROWS OR COLUMNS OF DATA

In most cases we wish to delete an entire row or column.
If you just highlight the row or column and hit the delete key then the contents disappear but the row or column (now blank) remains.
Instead click on the shaded row number or column letter and then right-click and choose delete.
Alternatively highlight the row(s) and column(s) to delete and then choose Edit | Delete and select delete all row or delete all column.
 

For further information on how to use Excel go to
   http://www.econ.ucdavis.edu/faculty/cameron