Data is an important resource to any organization and can be used to make business decisions. These decisions can be intelligent and useful if we are able to analyze data properly. For example, if you are maintaining a sales record for your shop then you should know which item is in demand regularly so that you can buy the item in time and avoid lack of sales.
It is difficult for a human to analyze data if it contains several rows and columns. Also during the collection of data, we enter data randomly without giving any consideration to its order. But at the end of the data collection, we need to bring it in proper ascending or descending order. We wish to specify a criteria of our choice to sort the data accordingly.
Microsoft Excel can be the perfect choice for analyzing and presenting random into meaningful bits and pieces. Today we are going to learn how we can apply sorting to our record to make it more readable. And how we can use the built-in Excel options to analyze data records quickly and effectively.
Sorting data in ascending or descending order
Let us start with a simple spreadsheet intended to keep a record of patient’s blood pressure and level of glucose throughout the day. It has six columns date, time, event, systolic, diastolic,heart rate, glucose, level, status.
To apply sorting options:
- Select the first column to be sorted.
Click ‘Sort & Filter’ in the ‘Design’ tab
- Click on the arrow under Sort & Filter and sort the values in ascending/descending order.
Using custom sort option in Excel (Sorting by multiple columns)
Now let us assume that you want to sort the data on the basis of events and then arrange the events by time (in case both events have same value).
Click on the ‘Custom Sort’ from the Sort & Filter menu.
- A dialog box will appear. You can see the selected column already enlisted there.
- You can add as many levels of sorting as you want, by clicking on ‘Add level’ option.
We had selected the ‘Event’ as our first column and then added ‘Time’ column. You can observe the sorting in the image shown below.
Applying filters to data in Excel
The procedure of applying filter to any column is the same as sorting but before that you must know why we need filters. Filters are a useful option that helps us analyze the results by narrowing our search.
Although our sample worksheet have only six rows and six column so data analysis is not difficult here. What if we had a hundred rows and columns? Let’s say you are administering academic records of students and you want a list of students with percentages above 80.
Note that sorting will change the order of your spreadsheet but filters will only present your desired result in front of you removing all other rows that are irrelevant.
You can apply date, text and number filters depending on the type of data in a particular column. Consider the ‘Event’ column, if you apply text filter it will provide you with all possible values (values you have already entered in event column).
Now, let’s say we want to see the blood pressure and glucose level details ‘After Meal’. Un-check all other boxes and click OK. It will filter ‘After Meal’ results for you.
If you want to see every entry that has word ‘Meal’ in it then you can type it in the text box.It will now show ‘Before Meal’ and ‘After Meal’ results.
Clearing filters to bring back all data
You can clear the applied filters by following these steps:
- Click on ‘Sort & Filter’ in the design tab and select ‘Clear’.
- It will undo the filter and will restore the original unfiltered data.
- If you want to keep the filtered results but do not want to apply any other filter,click on the ‘Filter’ again.
- You will notice that drop down arrow will disappear from column headers.
Editor’s note: In Excel 2013, you will be able to access ‘Sort & Filter’ option from the Home tab.