Here are some simple tools to use in Excel to better understand and analyze data at a glance:

1. Sort

In Excel 2010, it is possible to sort data in the following ways:

-  alphabetically (A to Z or Z to A)
-  numerically (smallest to largest or largest to smallest)
-  by dates and times (oldest to newest and newest to oldest)
-  by custom format (by cell color or font color)
-  by a custom list (such as Large, Medium, and Small)

Steps: 

  1. Select a cell in the column you want to sort and press Ctrl + A, to Select All. Check that the selected area covers all of the data you want to sort. The range can also include headers that you might have created to identify columns or rows.
  2. Check the selected area, to make sure that all the data is included. It is important to note that if there is a hidden column E, or a blank space, and columns at the end are not selected, they will get left out of the sort, and will not match your sorted data. 
  3. Once you have selected everything, go to ‘Data’ and select ‘Sort’ under the 'Sort & Filter' option (or, Shortcut: press Alt + D + S)
  4. You can then choose what you want to 'Sort By', 'Sort On', and the 'Order' -  you can choose several levels to sort on as well.  

    Example:

    DATA BEFORE SORTING                                                                                                    

                    

DATA AFTER SORTING

 

2. Auto-Filtering

Auto-filter is a very handy tool if you have large amounts of data – this can hide all the records except for the ones that you want to view. 

How it works: 

  • It displays a subset of the a data table 
  • It temporarily hides rows that don't match criteria that you set
  • It does not rearrange or delete data in any manner
  • It provides an easy way to break down your data in ways that you might like to see it

Steps: 

  1. As above, select a cell in the column you want to sort and press Ctrl + A, to Select All. Check that the selected area covers all of the data you want to filter. 
  2. Once you have selected everything, go to ‘Data’ and select ‘Filter’ under the 'Sort & Filter' option (or, Shortcut: press Alt + D + F + F/ Ctrl + Shift + L)
  3. Click the filter arrow beside the column heading for the column you want to filter. Excel will then display a drop-down list, which includes one of each unique entry from the selected column as well as options on the type of search you would like performed. Based on whether the data in the column is a number or text you will see different Number or Text Filters. 
 
(1) You can use the search bar to look for numbers or text which you want to filter on
(2) Remove the check mark from Select All.  Check and uncheck boxes based on the values that you want to see (these values that are shown are what are found in the column of data)
(3) Use these to create Custom filters that you might like to see (For example: Numbers that are greater than 10, Text that contains ".mp3", etc.)

4. Choose a condition and then select or enter criteria. Click the And button to combine criteria (that is, two or more criteria that must both be met), and the Or button to require only one of multiple conditions to be met.
 5. To remove filters, just click the Filter button once more, or the shortcut and it will undo the Filter. 
Note: If you want to copy the subset, you can just select the area you want (when filters are on), hit Copy (Ctrl + C) and paste (Ctrl + V) in a new tab. This should give you only the filtered values. 

Example:

If we chose to display only those that were Less Than or Equal to 30 in the Age Filter:
         

With Filter:



  • No labels