Excel Sort & Filter / Freeze Planes (MS-Excel)

This article addresses two very simple but very useful concepts for organizing small data sets within Excel.

Freezing Panes

When dealing with sheets that contain numerous column variables that contain large numbers of observations, it can be quite easy to scroll downwards, and in doing so, forget exactly what variable each column represents.

The “Freeze Panes” option exists as a way to remove this ambiguity in order to more easily keep track of column variables. This option can be enabled within the “View” Ribbon.

Typically, you will be selecting the first option which freezes the first row of all columns. This allows the user to scroll downwards while being able to reference the variable name represented in each column. To disable the option, simply select it again from the “View” Ribbon once it has already been enabled.

Sort & Filter

Referring again to column headers, let’s say that you wanted to easily sort by a particular variable in each column and view the observational (row) data that pertains to the variable selection. An easy to accomplish this is to utilize the “Sort & Filter” option within Excel. This option can be found within the “Home” Ribbon.

First, select the column headers (variables) of the Excel sheet that you wish to sort by.

Next, select “Sort & Filter” from the “Home” Ribbon Menu within Excel. From the drop down, select “Filter”.

Once this has been accomplished, each variable should have a drop down menu associated with it.

Clicking on these menus allows you to select the variable data that you wish to sort by. In our example, we will sort by individuals who utilize Python as their favorite programming language.

The funnel icon in the upper right hand corner of the drop down variable menu indicates that the data has been sorted. If necessary, multiple sorts can be performed within the sheet by selecting multiple variables to sort by. In the case of our example, we will be sorting by individuals who prefer Python as their favorite programming language and also prefer Red as their favorite color. 

The result of this sort will resemble:

To reset the sorts, click “(Select All)” in the selection box of each drop down that has a sort currently enabled. 

Two aspects to be aware of, data sets that are on the larger side will be slow to sort and could potentially crash Excel when the option is utilized. Also, sorting by multiple variations of the same variable is also possible, meaning, that multiple observation variations within the same variable can be selected at once. For example, you could sort by individuals who either prefer the programming languages Python or Java. Then, from the data sorted within that query, you could sort by favorite color. 

The Sort & Filter feature of Excel is one of its most useful options, make sure that you do not overlook the potential of this particular feature when working with Excel data.