Manipulating Data II (Mac)


Sorting can be extremely useful when working with data sets. A basic Sort will sort information in columns from smallest to biggest or biggest to smallest (or in the case of text from A to Z or Z to A). As an example, let's assume you are interested in the Census Tracts that showed the largest population percent change between 2000 and 2010. Since the file is organized by Census Tract it is difficult to tell which areas showed the largest percent of growth.

We can sort the Percent Change column (H) from biggest to smallest.


Sorting a Single Column

Click on a cell in the column we want to sort (H) under the headers (headers or headings are essentially the titles in the columns). Under the Data tab select the Decending option in the Sort group in the Ribbon.

You can also sort using the Sort Dialogue Box, found under Data in the Main Drop Down Menu:

Sorted data will look like this:

The cells with '-' were created since the 2000 census and so there is no way to measure the population change in that particular area. You could do a Find & Replace if you would prefer to change those (go to the Manipulating Data I page to find out how). When we scroll down we find the areas that demonstrated the largest amunt of growth between 2000 and 2010. You'll also notice that the boroughs are intermixed:

Data can also be sorted using the Sort dialogue box which you can get to by clicking on the arrow next to Sort in the Sort & Filter group under the Data tab and select Custom Sort.

Using the dialogue box you can specify if your data does or does not have headers. You can specify the column under Column, the sorting criterion under Sort On, and the sorting order under Order. Notice that the to-be-sorted data is selected- this happens automatically IF you have selected a cell in the dataset. You can also highlight the range that you want to sort.


Sorting Multiple Columns

You can sort multiple columns simultaneously. For example, say you wanted to look at the largest percent change as before but you wanted it sorted by borough, so that you could see the largest percent change in the Bronx, and then scroll down to Brooklyn, and so forth. In the Sort dialogue box fill in Sort by with Column A (borough names), Sort On with Values, and Order with Largest to Smallest. Then click on the '+' button and fill in the second row: Then by with Column H, Sort On with Values, and Order with Largest to Smallest.

Then select OK. In the example, we've scrolled down so that you can see where the Bronx ends and Brooklyn begins. You'll notice that the since the percent change is sorted from largest to smallest, we are looking at the largest decrease at the end of the Bronx data and the largest increase at the beginning of the Brooklyn data.


Some things to note about Sort

  1. Make sure all of the items being sorted are of the same data type. If you have numbers stored as text and numbers stored as numbers (see Formatting Cells for more about data types) then the numbers stored as numbers will be sorted before the numbers stored as text.
  2. Also be sure to remove any spaces preceeding information in the cells. Sometimes data sets will have 'leading spaces' in which there is a space preceeding the number in the cell. This can occur if the original data file was not an Excel spreadsheet.
  3. Be careful when sorting items that include formulas (more on that soon) because the return values could change.
  4. Keep column headers in one row (otherwise, you can't sort by the full column header). In our current example data, we were sorting by column names rather than headers. That is because the headers covered 3 rows. If the header is in a single row, then you will select the header title of the column instead of the column letter.
  5. Also note that any hidden columns (or rows) will not move until you sort, thereby changing your data set. More on hiding and unhiding below.

Auto Fill & Series Fill

One of the great features of Excel is the Fill option. Instead of re-typing the same data or formula, you can let Excel copy it into the cells (AutoFill). Or instead of typing the numbers 1-100, you can set Excel to fill them in (Series Fill).

Auto Fill

Let's say you wanted to reformat the example data so that the terms referring to the 5 boroughs were represented as numbers. Since the Bronx is first, you give it number 1. But instead of typing the number 1 for all the data points for the Bronx (and continuing with all the other boroughs), you can use Auto Fill. Type the number 1 in the first two cells.

Select the two cells. Move the cursor over the bottom right corner of the selection. It will turn into a small black plus sign. When it does, click and hold down the mouse key and drag it down to cover all of the cells that you want to have the number 1 in. You'll notice that the selection border turns gray and that there is a small box with a 1 in it below and to the right of the plus sign. This tells you what is being input into the cell.

When you release the mouse key, all of the selected cells will have a 1.

Why did you have to enter the 1 in 2 cells?  Read on to find out!


Series Fill

Say you wanted to count the number Census tracks in the Bronx and you don't completely trust your subtraction skills (remember, there is data in row 9 so you can't simply subtract 9 from the final row number of the Bronx data). But if you used a Series fill, then Excel would tell you how many Census tracks there are for the Bronx by counting each one. Type the number 1 in I9.

Click on the cell (I9) and move the cursor over the bottom right corner of the cell. When the cursor turns into a small black plus sign, click down the mouse key and drag it down until the end of the Bronx data.

Another way that you can do a Series Fill is by going to Fill in the Editing group in the Home tab. You’ll first need to select all of the cells that you want filled in and type the first number you want the series to start with in I9. Then click on Fill. Select Series.

In the dialogue box that comes up you can change the parameters of the series. For example, if you wanted to increase it by 2 numbers instead of one, you would change the Step Value from 1 to 2. You can also set the Stop value or alter the Type (note that you can get to AutoFill here by selecting it under Type).


Hiding & Unhiding

There are times when you want to keep information in the worksheet, but you do not necessarily want to be looking at it. In which case, you can hide it. And anything hidden will at some point need to be unhidden (otherwise, you could just delete it).


Hiding Rows or Columns (or worksheets)

Select the row or column that you would like to hide. CTRL+ click the mouse and select Hide at the bottom of the list.

The selected row(s) or column(s) will disappear. Notice that not only does the information in the column or row disappear, but the row column letter/ row number disappears too. In this case, rows 14, 15, 16, 17 and 18 are hidden. This is how you can tell if there are hidden rows or columns in the dataset (notice the blue numbers and line).

You can also hide rows or columns from the Format button in the Cells group on the Home tab. Select Hide & Unhide and then select if you want to hide rows, columns, or even the entire worksheet! To hide an entire worksheet, CTRL+click on the worksheet name and select 'Hide.'



You may want to revisit hidden information again. Also, sort will not include hidden rows or columns, so if you want to preserve the information you should unhide rows or columns before sorting.

To unhide a row or column that is not the first row (row 1) or column (column A) select the surround rows or columns. So if you are interested in unhiding rows 14, 15, 16, 17 and 18, select rows 13 and 19. CTRL+ click and scroll down to select Unhide.

The hidden rows or columns (in this case rows) will reappear.  As before you can also unhide columns or rows from the Format button in the Cells group on the Home tab. Select Hide & Unhide and then select if you want to unhide rows, columns, or the entire worksheet (Again, to unhide a worksheet, you have to CTRL+click on one of the visible worksheet tabs and select 'Unhide').

When you select to unhide a worksheet, you will get a dialogue box listing the hidden worksheets for you to pick the one that you want to unhide.

If you have hidden the first row (row 1) or column (column A) you cannot select the surround rows or columns (because what comes before row 1 or column A?). One way you can unhide them is to select all of the columns/rows. Then unhide the rows/columns from the Format button in the Cells group on the Home tab and selecting Hide & Unhide.  Note that this will unhide every row and column. 

To unhide ONLY the first row or column, type A1 into the Name Box next to the Formula bar. Then from the Format button in the Cells group on the Home tab, select Unhide Columns (or Unhide Rows). The missing first column (or row) will return.



<< Back to Manipulating Data I                   Continue to Creating Charts >>


Return to Table of Contents


Questions, concerns, comments?
Send an email to the Empirical Reasoning Lab
Or drop by during the Lab's open hours.