Mad Marv’s Place

Sorting

Let’s say you have the following table

   A	        B       C        D        E         F       G

1 TICKER   EPS	 ROE     D/E	P/E	 P/S     YIELD
2 HIYLD	    $0.75  12%    0.1	20.0	  2.0	  5%
3 NOGRW	 $2.00    7%	0.8   12.0	1.5	2%
4 BKRPT	 $(1.50) -35%  -5.0    N/A	0.8	0%
5 BGBKS	  $3.25   27%	 0.0   20.0	3.0	0%
6 TASUX	   $0.01    2%	  0.3	85.0	  7.0	  0%
7 LOWBV	  $0.25   30%	0.0    16.0	1.7	1%

Say you want to sort this table by ticker symbol. Highlight the entire table (A1:G7) and start the sort command (Data –> Sort…). A popup window will appear and Excel should have anticipated that you have a header row (Row A). Excel will then ask by which column you wish to sort by. Excel, at least in the ‘97 version, allows up to three levels of sorting (i.e. start w/ TICKER, then any duplicates by EPS, and then a third tier of duplicates by ROE).

Each sorting criteria can sort by ascending or descending entries. If your data was entered such that you wanted to sort the rows, the pop-up window has an Options button that allows you to change the methodology from sorting columns to sorting rows.

When Excel sorts numeric and text data at the same time (for example if you wanted to sort the above table by P/E), Excel will list the numeric data first then the text.

Filtering

Using the same example table, let’s try filtering it. Highlight A1:G7 and start the Autofilter command (Data –> Filter –> Autofilter). The header row will become a drop down box. You may filter the table using any column’s drop down box. You may choose to filter for:

1. A specific entry in the column.
2. The top X entries. By default, the X is 10, but it is customizable.
3. A custom filter to search through the data that meets specific criteria.

Say you wanted to filter for all ROE > 10%. Click on the drop down box in cell C1 and choose (Custom…). This will bring up a pop-up window. Choose “Is greater than…” in the first drop down box and type 10% in the second box. Click enter to execute the custom filter. Notice that Excel has not deleted any of the data, it has merely hidden the rows that do not meet the filter criteria. If you wish to remove any filter, click on the appropriate header drop down box and choose (All).

To remove all filtering use Data –> Filter –> Show All.

Importing Data
Excel can open more than spreadsheet files. It can also read in text files, word documents, HTML, etc. However, Excel may have trouble integrating the data into the cell structure. When Excel has such a problem, it will ask how it should parse the data. There are two ways that the data can be split:

1. Delimited - Excel will look for a special character in the text and break the text into a new column whenever it encounters the charcter. These are usually tabs, commas, or spaces but Excel allows just about any delimiter if it is instructed to do so.

2. Fixed width - Excel will count a fixed number of characters and put them into a column regardless of where that break will fall in the text file. Excel generates a preview of the document and shows where the fixed width column break will cut the text.

Generally if you can control how the data file is created, it is easier to use Delimited to separate the data into columns.

Tags: Investing //