Here are some simple tools to use in Excel to better understand and analyze data at a glance:
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)
- 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.
- 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.
- Once you have selected everything, go to ‘Data’ and select ‘Sort’ under the 'Sort & Filter' option (or, Shortcut: press Alt + D + S)
- 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.
DATA BEFORE SORTING
DATA AFTER SORTING
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
- 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.
- 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)
- 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.