EXCEL 2007: Data Manipulation
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