Excel Tips & Tricks-3: Freezing, Filtering, Sorting etc..
Saturday 30 November 2013

Excel Tips & Tricks-3: Freezing, Filtering, Sorting etc..

This document includes information on:

 

  • Converting xls or cvs Files to Excel 2010
  • Freezing the Top Row of an Excel Worksheet
  • Hiding and Deleting Columns
  • Filtering Data
  • Sorting Data
  • Pinning Documents to the Task Bar

 

1 - Converting xls or cvs Files to Excel 2010

In order to manipulate large quantities of data, you must first convert xls or cvs files to Excel 2010. (PeopleSoft and Data Warehouse use this format to export.)

 

  • In the upper left-hand corner, click File.
  • From the Info tab, click Convert.
    Note: The Convert button will only appear if you need to convert the document to a new format.
    A Microsoft Excel dialog box will appear.
  • Click OK.
    The document is now replaced with an xlsx document.

 

2 - Freezing the Top Row of an Excel Worksheet

It is helpful to freeze the top row of your Excel document in order to view the column headings without scrolling to the top of the document.

 

  • Click somewhere in your data.
  • From the View tab, select Freeze Panes.
  • If you want to freeze the panes above a certain cell of your document, click Freeze Panes.
    OR
    If you want to freeze the top row of the document, click Freeze Top Row

 

3 - Hiding and Deleting Columns

This can be helpful if you want to hide a column so that you do not have to work around it.

 

  • Select the desired column by clicking on the letter of the column.
  • Right-click in the blue area.
  • If you want to hide the column, click Hide
    OR
    If you want to delete the column, click Delete
    Note:If you want to hide multiple columns, highlight one column, and drag the blue highlighted area over the other column(s) you wish to hide.
    If you delete the column, you will be unable to retrieve it.
  • If you want to unhide a column:
    a. Highlight the column letters surrounding the hidden column.
    b. Right-click and select Unhide

 

4 - Filtering Data

It is important to filter unnecessary data in your columns so that you can better view the information in your document.

 

  • Click anywhere in the column you wish to filter.
  • From the Data tab, click Filter.
  • Click the down arrow icon in the heading of the column you wish to filter.
  • Select the box next to Select All.
  • Select the item you wish to view.
  • Click OK.
    Note:You will now only view the items in your data under that subject heading. You can put filters on multiple columns by repeating this process on the desired columns.

 

To clear filters:

 

  • Click the down arrow icon in the heading of the column with the filter you wish to clear.
  • ClickClear Filters.
    OR
    From the Data tab, click Clear

 

5 - Sorting Data

Sorting data is another way in which to organize how you view your information.

 

  • Select all of your information by clicking to the left of ‘A’ and then clicking the triangular icon.
  • From the Home tab, click Sort & Filter
  • Click Custom Sort.
  • Make sure that the My data has headers box is selected.
  • Use the Sort by pull-down menu to select the desired item you wish to sort by.
  • Click OK
  • Optional:If you wish to further sort your data:
    a. From the Home tab, click Sort & Filter
    b. Click Custom Sort.

  • c. Click the Add level button to add another term to sort your data.

 

6 - Pinning Documents to the Task Bar

If you have a document that you need to access on a daily or weekly basis, you can pin this document to your taskbar for ready access.

 

  • Save your Excel document to your desired location.
  • On the toolbar at the bottom of your page, right-click the Excel icon.
  • Highlight the document you wish to pin, and click the pin icon.
  • To open the document, open Excel.
  • Right-click the Excel icon.
  • Select your document.

 

To Unpin a Document:

 

  • On the toolbar at the bottom of your page, right-click the Excel icon.
  • Highlight the document you wish to unpin, and click the unpin icon.