Sorting
You can sort data in your spreadsheets pretty much anyway you want: by one or more columns, ascending or descending, or even by specific sort orders you set up yourself.
Quick Sort
1. Select all the data you want to sort. This will sort data only by the first column, but you need to select all the columns to be sure the data stays together correctly.
2. Click the A-Z or Z-A quicksort icon.
3. The data will be sorted.
Sorting Using the Sort Window
Here’s the best way to sort basic data. Let’s say you’ve got a big list of names and you’d just like to see them in alphabetical order so it’s easier to use.
- Select all the data you want to sort, including the headings. This will make it easier to specify which columns to sort by. Select just the data to sort, not the whole sheet.
- Choose Data > Sort.
- In the Sort window, select the column to sort by, and whether you want to sort in Ascending (A to Z or 1 to 10) or Descending order.
4. Click the Options tab and mark the Range Contains Column Labels option.
5. Click OK.
Sorting Left to Right
Sometimes the categories you want to sort by are on the vertical axis, in rows, as in this example. The sorting process is similar to the standard top-to-bottom approach; you just need to specify that you’re sorting left to right instead of top to bottom.
- Select the data you want to sort, including the headings.
- Choose Data > Sort.
- In the Sort window, click the Options tab.
- Select the options shown: select Left to Right (Sort Columns) to specify left-to-right sorting, and also mark the Range Contains Row Headers option to indicate that the first column of data is headers, not data to be sorted.
- Click the Sort Criteria tab.
- Select the normal sort options; the row to sort by and Ascending or Descending.
- Click OK.
- The data will be sorted from least to greatest (ascending) total sales.
Sorting Using Two or More Columns as Criteria
Sometimes you need to sort by two or more columns to get the data in the order you need. If you have, for instance, 5000 names of the people in your company, with three locations, you probably want to sort the employees first by location.
However, then you have at least 1000 people at each location, in no particular order. To make the data easier to read, sort first by the location, and then by last name.
- Select the data including headings.
- Choose Data > Sort.
- In the Sort Criteria tab, select to sort by up to three columns
- Click the Options tab and make sure the Range Contains Column Labels is marked.
- Click OK. The data will appear sorted as you specified.
Sorting Using Months and Weeks
You can sort alphabetically or by numbers but you can also sort based on other things that have a particular order like the days of the week or months.
- Select the data including headings.
- Choose Data > Sort.
- Click the Options tab; you’ll see the option to sort by other information.
- Select the Custom Sort Order to use.
- Select the Range Contains Column Labels option.
- Click the Sort Criteria tab and choose to sort by the column containing data corresponding to the sort you chose in the other tab.
- Click OK.
Creating Your Own Sort Order
You can automatically sort by month or day of the week because OpenOffice.org comes with these custom sort orders already set up. You can set up similar sort orders that contain whatever you want. To create your own sort order, see Quickly Entering Months, Days, or Anything You Want With Sort Lists
Filtering
There are a couple ways to do filter out data so that you see only what you want: a quick simple autofilter that’s often good enough as is, and the filter you can define the way you want.
Using the AutoFilter
Here’s what the autofilter looks like.
Let’s say that you’ve got an enormous list of people in a spreadsheet, and you just want to see the ones from a particular town. That’s a good example of when to use the autofilter.
Autofilter lets you pick one value for a column, like “Boulder” for the City column, and view the rows in that spreadsheet with “Boulder” in the City column
- Choose Data > AutoFilter.
- Dropdown arrows will appear at the top of each column. This means you can restrict what you see in the spreadsheet to rows with a particular value.
- Click and hold down the arrow and select a value.
- The spreadsheet will filter out everyone except the rows with that value.
- To go back to showing all, click and hold down on the column’s arrow (blue now) and choose All.
- When you’re done, choose Data > Filter > AutoFilter again to turn off the filter.
Note – If you leave an autofilter selection like just Kalispell within the State column, then make another selection in another column like Last Name or State, you’ll see only records for items that meet all the criteria.
This means you’ll see far fewer records than you want, typically.
Creating a Standard Filter
The autofilter works great for some situations, but sometimes you want something a little more complicated. With the autofilter you can just select one value for a column (or the top 10).
What if you wanted to just show records for a value that is above a particular level say, all the invoices for $100 or more? Or you need to view all the records for invoices you’ve submitted to Frye’s, Best Buy, or Circuit City. You can’t do that with the autofilter, so it’s time to create your own filter.
Let’s say you’re still working with the same spreadsheet as before. You need to narrow down your spreadsheet to people who are from Boulder or from Kalispell, and who have been with the organization at least five years.
- Select all the cells containing data, including the headings.
- Choose Data > Filter > Standard Filter.
- The Standard Filter window will appear.
- Fill in the first row with your first criteria, listing the column, the operator (equal to, greater than, etc.), and the amount to compare values to.
- Enter additional filters, if necessary. Select And or Or, depending on whether they are combined or independent.
- Click OK. The data meeting the criteria will appear in your spreadsheet.
- To remove the filter, choose Data > Filter > Remove Filter