« Using Google to find a book by its color | Main | It's all downhill from here »

Excel 2007: How to sort data in a spreadsheet

Ever wanted to sort a subset of data in a “Long in Transit" report or some other big spreadsheet?  Not including printing, it's a four-step process.E_icon

Backup the original spreadsheet:
Before sorting, be sure to make a backup copy of the original spreadsheet.  That way if things go astray, you have an easy way out.

Freeze the column headers:
If the spreadsheet spans more than a single screen, you may want to ‘freeze’ the column headers before you begin.  That way as you scroll down the spreadsheet, the column headers will be preserved.

  1. Select the View tab from the ribbon.
  2. In the Window subsection, click the down arrow associated with Freeze Panes.
  3. Select Freeze Top Row.
  4. A black bar should appear beneath the first row in the worksheet.  This will indicate that the area above the bar has been 'frozen'.

An example sort:
For this example, let’s sort the Date sent column associated with POR’s data in a ‘Long in transit…’ report (for POR’s data only).

Highlight all of POR’s data:
This will involve a Shift-Click keyboard-mouse sequence.

  1. Visually determine the 1st (in this example: 1,089) and last rows (in this example: 1,122) that contain POR data.First Row
  2. Click the mouse in the first cell of POR’s data, A1089.
  3. Depress the left Shift key and leave it depressed.
  4. Depress the left mouse button and while keeping it depressed, drag it to the end of the row, column ‘J’.
  5. At this point, all the data between A1089 and J1089 should be hightlighted.
  6. Still with the Shift key and the left mouse button depressed, scroll down the spreadsheet until row J1122 is highlighted (the last cell of the POR data).Last Row
  7. Release both the Shift key and the left mouse button.

Sort POR’s ‘Date Sent’ column:

  1. Click on the Data tab on the Ribbon.
  2. In the Sort and Filter subsection, click the Sort option.
  3. In the Sort window:
  • Remove the check mark from My data has headers.
  • Click the down arrow associated with Sort by and select Column ‘H’ (the 'Date Sent’ column).
  • Click the OK button. Your data will now be sorted. Sort by Date Sent

Print the sorted results and fit it on one page:
There’s a really easy way to print out only the subset of data that you just sorted.

  1. Click the Page Layout tab on the Ribbon.
  2. In the Page Setup subsection, select Print Area.
  3. Select Set Print Area.  This will print what’s been highlighted previously.
  4. Also, in the Page Setup Area, select Print Titles. Click the Page tab and then click the radio button for Fit to under the Scaling section.  This will compress all the data onto one page when printing.
  5. Click OK.
  6. Print you sorted data by whatever mechanism you normally print.

FYI: Excel is capable of sorting more than one column of data at a time.



Feed You can follow this conversation by subscribing to the comment feed for this post.

The comments to this entry are closed.