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

This September 1999 help sheet gives information on
• creating new data by transforming existing data
• cell references (relative and absolute)
• selecting or highlighting data
• sorting data
• deleting rows or columns of data

CREATE NEW DATA BY TRANSFORMING EXISTING DATA

Suppose you have open a data set with the following information (cars.xls).

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)
• 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:

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

CELL REFERENCES

Cell reference examples are

• Cell B2 is the entry in column B and row 2.
• Cells B2:C10 are the entries from column B row 2 in the top left to column C row 10 in the bottom right. This is 2 columns times 9 rows yielding 18 entries.
• Cell references are most often relative but can also be absolute. Absolute cell references have the prefix \$. For example, B2:C10 is a relative cell reference while \$B\$2:\$C\$10 is an absolute cell reference.
Relative cell references can change after copying the cell references to a new location.
• For example, if D2 = B2+C2 then if we copy cell D2 to D3 (move down one cell) the new cell is D3 = B3+C3.
Absolute cell references do not change after copying the cell references to a new location.
• For example, if D2 = \$B\$2+\$C\$2 then if we copy cell D2 to D3 (move down one cell) the new cell is D3 = \$B\$2+\$C\$2.
Cell references can be part relative and part absolute
• For example, \$B2 is absolute column B and relative row 2, while B\$2 is relative column B and absolute row 2.
Cell references can be to a different worksheet in the current workbook
• For exazmple, Sheetname!B2:C10 or Sheetname!\$B2:\$B10.
Cell references can be to a different workbook
• For example, [Workbookname]Sheetname!B2:C10 or [Workbookname]Sheetname!\$B2:\$B10.

SELECTING OR HIGHLIGHTING DATA

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

• Click on the first entry in the array.
• Depress the shift key and keep it depressed
• Scroll down to the last entry in the array you want to highlight
• Click on this last entry
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 all the data

• Click on the first entry in the array (upper left corner).
• Depress the shift key and keep it depressed
• Hit CTRL-down arrow
• Hit CTRL-right arrow

SORT DATA

Suppose we wish to order the newly created data in descending order by cars per person.

• Highlight cells A1:C6
• Choose the Data Tab and the Sort and Filter Group and Sort
This opens the Sort Dialog box
• Sort by CARS PER PERSON from largest to smallest.

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://cameron.econ.ucdavis.edu/excel/excel.html