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

 

 

 

Speed Up Ms Excel With Auto Options

Speed Up Microsoft Excel With Auto Options

Excel has several Auto options that can speed up basic data entry.

Download the lesson as a pdf : Data Entry Auto options

  • AutoFill
  • Auto Complete
  • Auto Correct

Open a new sheet in Excel, if when you open a new sheet 3 sheets open as below

pic1

 If you only want Excel to open one sheet at a time.

  • Click on Tools from the Title Bar.
  • Click on Options from the drop down menu
  • Click on the General tab and change the amount in Sheets in new workbook to 1.

 

AutoFill

AutoFill can speed up entering data that forms part of a list such as months of the year or days of the week.  The method we will use is called a Filling Series and is accomplished using the Fill Handle found on the bottom right corner cell of the selected range.

Type a month into cell B2, you can use the full name or the first three letters. e.g Jan or January

pic 2pic 3

Select the cell containing the starting data.  Move the mouse pointer to the bottom right hand corner of the cell to display the fill handle. Hold the left mouse button down and drag down the column.  As you drag notice that Excel will show you what part of the series you have reached.  Release the mouse and the series fills.

The AutoFill can be used with other data series.

pic 4 pic 5

Enter Monday in cell  B2 drag the fill handle to fill the series.

AutoFill can also be used with other data series e.g. Week 1 or Project

pic 6pic 7

Auto Correct

This will correct common spelling and typing errors.

For example

accomodate will be corrected to accommodate

Teh will be corrected to The

BUdget will be corrected to Budget

monday will be corrected to Monday

(N.B This does not work for month names!)

For a list of auto corrections select Tools – AutoCorrect

pic 9

 

Auto Complete

If the first few characters you type in a cell match an existing entry in that column, Excel fills in the remaining characters for you.  Excel completes only those entries that contain text or a combination of text and numbers, dates, or times are not completed.

Enter the following names going down one column

pic 11

Now enter “P” : Excel will assume you want to enter Paul

pic 12

To change Paul to Peter, simply over-type the highlighted text.

You can turn off AutoComplete using Tool: Options and deselect AutoComplete within the Edit tab.

You can also select a list of entries already in the column.  To display the list press Alt + Arrow down and then pick from the list.

Have a go yourself

Create the following worksheet using AutoFill to speed up your entries. You should only need your keyboard a few times.

pic 13

TIPS

To enter the today’s date into a cell, select the cell you want to place the date on the keyboard press and hold Ctrl : (Control key + colon)

 To enter the current time press and hold Ctrl Shift : (Control + Shift + colon)

Don’t forget you can print off the training above here download pdf: Data Entry Auto options

Other Lessons