• create new data by transforming existing data
• cell references
• selecting or highlighting data
• sort data
• delete 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).
 

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

• 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, e.g. $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, e.g. Sheetname!B2:C10 or Sheetname!$B2:$B10.
• Cell references can be to a different workbook, e.g. [Workbookname]Sheetname!B2:C10 or [Workbookname]Sheetname!$B2:$B10.

 

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

Thus to select or highlight a long array

• Click on the first entry in the array.
• Depress the shift key and keep it depressed
• Hit CTRL-down arrow

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