Tech and Retro Gaming Lovers
Tutorials

Excel Auto Filter and Freeze Panes, Work Smart!

Introduction to Excel

Sorting Data, AutoFilter and Freeze panes

 

Download Sorting Data pdf :Autofilter and freeze panes

You can use sorting to organise your spreadsheet data in ascending or descending order.  You are allowed upto three levels of sort for example, if you sort by Surname in ascending order and then First name in ascending order, when Excel comes across two people called “smith”, it would then put them in first name order “Colin” and then “Robert” for example.

  1. Move to a cell in the column you want to sort by.
  2. Click on the Sort Ascending button to sort A-Z, 1-100 or
  3. Click on the Sort Descending button to sort Z-A, 100-1

2014-09-13_14-25-20

 

To Sort data using up to three fields

 

  1. Move to a cell inside your data
  2. Click on the Data menu, and then click on the sort command
  3. The Sort dialogue box will be displayed.

pic2

  1. If your list has text field names, click on the header Row option under the My list has command, if Excel has not selected it automatically.
  2. Under the Sort By command, click on the drop-down arrow, and then click on the label of the column that you want to sort by first.
  3. Click on the Ascending or Descending option.
  4. Under the Then by command, click on the drop-down arrow, and then click on the label of the column that you want to sort by next.
  5. Click on the Ascending or Descending option.
  6. Repeat steps 6 and 7 until you have completed your sort options.
  7. Click on the OK button.

AUTOFILTERING DATA

The AutoFilter command gives you quick and easy access to a great deal of database management power.  It allow you to set criteria, and filters out row of data that do not match the criteria you have specified.

To find specific data using AutoFilter

 

  1. Make sure that you are within your database.
  2. Click on the data menu, click on the Filter command, and then click on the AutoFilter option.
  3. Click on the drop-down arrow next to the field name where you want to set criteria.
  4. Click on the set of criteria you want to view.

2014-09-13_14-33-33

 

Custom Filtering Data

 

Using the AutoFilter is a very quick and easy way to find an exact match to criteria that you specify.  Sometimes though you will need to find a range of answers rather than exact match, amounts between £3000 and £6000 for example, or names beginning with the letter B, for this you can use custom filtering.

 

To create a Custom AutoFilter

 

  1. Make sure that the AutoFilter command is switched on.
  2. Click on the drop-down arrow next to the field name where you want to set a custom filter.
  3. Click on the (Custom …) command.
  4. The Custom AutoFilter dialogue box will be displayed.

pic4

 

  1. Under the Show rows where option, click on the box that contains the word equals.
  2. Type in the box to the right what you want to find, or click on the drop down arrow and choose from the list.
  3. Click on the And option if you want to set more than one criteria.
  4. When you have finished setting your criteria, click on the OK button.

 

 

Freezing Panes

Freezing panes is extremely useful when you have column or row headings which you want to keep in view when you scroll the screen to add more data.

To Freeze Panes

  1. Select the column to the right of the columns you want to freeze, or row beneath the rows to freeze.
  2. Click on the Window menu, and then click on the Freeze Panes command.

To Unfreeze Panes

  1. Click on the Window menu, and then click on the Unfreeze Panes command.

Download Sorting Data pdf :Autofilter and freeze panes

Other Lessons

 

 

 

Related Articles

Quick Start Guide to Creating Excel Formulas

Rob Boyle

Turn Your Photos Into Tilt-Shift & Bokeh Masterpieces

Rob Boyle

Hide Being Seen on Facebook Chat

Rob Boyle