Microsoft Office/Working with Data

Editing, Moving, and Copying Data

To edit data, select the cell(s) to edit then either type data in the active cell or click the I-beam cursor in the cell to edit.

To move or copy data, select the cell or cells that you want to copy or move. From the toolbar or the Edit menu choose Copy or Paste.

To transfer the formatting from one cell to another cell, use Paste Special. For example, after using a formula, you may want to copy the numeric result rather than the formula. To do this, simply click on the cell that you want to copy and select Edit > Copy. Then, click on the cell where you would like the number pasted and click Edit > Paste Special. A box will pop up with a number of different options. Under the Paste section, click on the bubble that says Values and then click OK.

Inserting, Deleting, and Clearing Data

In Excel, always select then do. Select the cell you want the data to go into and then enter the data (or the formula).

• To delete or clear data, select the cell or the row or column and press Delete on your keyboard.
• To delete a whole row or column, click on the row number or column letter and Edit > Delete.

Using Autofill

Autofill allows you to quickly fill data in a series (e.g. months, days of the week, or a numeric series) into adjacent cells.

Using Autofill with a text series

To use the Autofill function with text, type in the first word of the series (e.g. January), rest your mouse on the bottom right corner of that cell, and you should see the Autofill cross (right).

Click and drag the series down or across the appropriate number of cells.

Excel knows how to Autofill months (January or Jan) and days of the week (Monday or Mon). You can teach Excel to Autofill other text series by going to the Tools menu, choosing Options, and then clicking on Custom Lists and defining your own text series.

Using Autofill with a numeric series

You can also use Autofill to quickly enter numeric patterns (e.g. 1 2 3 or 10 20 30) into adjacent cells.

To use Autofill with numeric patterns, enter the first two values in the series, one value in one cell and the next in the cell immediately below or to the right. Now select (highlight) both cells, release your mouse button, then rest your cursor on the bottom right corner of the selected area so you see the Autofill cross.

When you see the Autofill cross, click and drag down or across the cells you want to fill, then release the mouse button. Double-clicking the Autofill cross will result in an automatic filling of the cells below until it reaches a blank row.

Autofill becomes important again when constructing formulas.

Controlling Your View of the Data

Auto Size: You may have noticed that sometimes data extends beyond the width of the column. In order to be able to see all of the data, you must widen the column. To do this, go to the header row and place your mouse in between the columns (e.g. on the line that separates the letters A and B). You will notice that your mouse becomes a black double arrow. If you double click on this arrow, the column will automatically size itself to fit the longest string of data in a cell. However, this does not adjust automatically so you may have to Auto Size again after adding more data.

Freeze Panes: Freeze Panes is a useful feature when you are working with a large document that has many rows. By freezing a certain row (usually the header), you make that row visible wherever you are in the document. For example, if you have a document with 100 rows, you can’t see the header row when you’re at cell A100. To solve that problem, click on the row below the one that you would like to freeze. To freeze the header row, click on the cell in the second row and first column. Then go to Window > Freeze Panes. [It’s important to note you can freeze columns as well as rows. If you just click at an arbitrary point in the second row, not only will you freeze the top row, you’ll also freeze all columns to the left of the cell you’ve selected.]

Reveal Formulas: If you want to see and/or print all of the formulas in a spreadsheet (as opposed to the values) there is a really handy shortcut. Simply press Ctrl + ~. To return the view to displaying values, press Ctrl + ~ once again.

Sorting Data

You can order your data from the Sort window in ascending or descending order as well as based on multiple header values.

Sorting data is simply a way of automatically re-ordering rows on a spreadsheet to put them in a more useful order. For instance, you might sort an address book alphabetically by last name, or a list of items you’d like to buy from most expensive to least expensive.

Start by selecting the data you want to sort. It’s important to select all the columns in the data, not just the column you want to sort by. For instance, if you have a list of items in one column, with the prices in the next column, you would select both columns before running the sort. If your sheet has a “header row” at the top with labels for your columns (like “name,” or “price”) it’s a good idea to include that row in your selection as well, as you’ll see in a moment.

Once you have your data selected, click Data > Sort. You’ll see the sort window, pictured at right. If your data has a header row, be sure the “header row” bubble is filled in at the bottom. Excel will then use your labels in the “Sort by” boxes instead of the usual (unhelpful) “Column A,” “Column B,” etc. Now you can use the “Sort by” box to select the column you’d like to sort the data by (if that column contains text, it will sort alphabetically; if the data is numeric it is sorted in number order). The two “Then by” boxes select what column to use next if there is a tie in the “Sort by” column.

But what if you want to sort by more than three columns? Excel only allows sorting by (up to) three columns at a time, but fortunately preserves the logic of the previous sorts if a new sort is applied, with the most recent sort setting the precedent. Let's say that we have a spreadsheet which contains 20 columns and we want to sort it by 10 columns, with Sort Column 1 being the broadest sort and each succeeding Sort Column organizing the data strictly within the subsets defined by its predecessor. Here are our 10 Sort Columns, presented in logical order:

    Apple
    Banana
    Clementine
    Dangleberry
    Elderberry
    Fig
    Grape
    Hazelnut
    Ichang
    Jicama

The first step is to divide this set of 10 into groups of three, counting from the top:

    Apple
    Banana
    Clementine
    ----------------
    Dangleberry
    Elderberry
    Fig
    ----------------
    Grape
    Hazelnut
    Ichang
    ----------------
    Jicama

We're left with 4 groups. In order for the final sorting to reflect a logical flow from Apple through Jicama, we need to work backwards. The first time we select the Data > Sort option, we will enter the LAST group starting from its TOPMOST value:

    Sort by: Jicama
    Then by: leave blank
    Then by: leave blank
    ---------------------------
    Click ""OK""

Next, begin the with the 2nd-to-last group:

    Sort by: Grape
    Then by: Hazelnut
    Then by: Ichang

Next, begin the with the 3rd-to-last group:

    Sort by: Dangleberry
    Then by: Elderberry
    Then by: Fig

And finally:

    Sort by: Apple
    Then by: Banana
    Then by: Clementine

This type of sort is frequently used to present detailed hierarchies in a comprehensible manner.

Alternatively, you can use formulas to CONCATENATE (join) your groups together into a single field. For example, if you had Index1 in column A, Index2 in column B, Index3 in column C, and Index4 in column D, you could create a field called "Sort" in column E using this formula: =A2&B2&C2&D2

Filtering Data

Another way to organize data is to filter it. A filter only displays data that meet a certain criteria, such as all records for a certain day. To do this, go to Data > Filter > AutoFilter. A dropdown arrow will appear in the header cell of each column of your worksheet. Click on the arrow and select the criteria that you would like to use as a filter (e.g. April 10th). The worksheet will now display only the records from that day. You can also use the AutoFilter tool to display the top or bottom 10 numbers in a column, only records with blank spaces in that particular column, records that have a value greater than a certain number in the column that you are filtering, etc. These options are all available under the dropdown arrow that you will see after going to Data > Filter > AutoFilter.

An alternative to filtering is to use IF/THEN functions/formulas to select records or groups of records. For example, if column E contains information on "Country", you could write a formula such as =IF(E1="Mexico","Spanish","English") to fill column F with information on "Language".