This blog was authored by Y. Karp. Visit www.ykarp.com for some light-hearted blogs from the same author.
One of the easiest ways to find pertinent information in your Excel spreadsheet is to use the sort function. Sorting allows you to display the data in alphabetical or numerical order. While this is an easy solution for some tasks, it is clumsy for most. For example, what if you want to find all transactions in September? If you sort by the month column, you then have to scroll down (or up) to find the data for September. Not very efficient.
But there is a simpler way: filtering.
The filtering feature allows you to sort and group the data in a column to only show that which is relevant to your data analysis. When you click on the down-arrow that appears at the top of the column (when a filter is available), a list appears showing all unique values in that column. You can then select an entry on the list and the column will then display only those records. This is a filter.
Using the above example, let’s say you have a spreadsheet showing bank transactions during the last year and you only want to show data for September. Simply apply an auto filter to your range of data, click on the down arrow at the top of the month column and select September. Only data from that month will display. You can easily show all records again by clicking on “All”.
At the basic level, filtering is handy, but limited in functionality. While you can combine filters from more than one column to even further limit the data displayed, it is difficult to select multiple entries from one filter list like you can in a Pivot Table. To do this using the filter function you may need to set a custom filter (which can be found as an option in the auto filter drop-downs), or even an advanced filter which is a more complex task for more advanced users. Also, it is difficult to tell when your data is filtered or when it is showing all records. Some versions of Excel will color the arrow of the filtered column in blue to indicate when the data is filtered, but this is not always easily visible especially when you have many columns and headings in your range. So remember to select the “All” option to show all records when you are done with the filter, so that when you return to the data all of your business information is immediately available.
If you would like to make the most of your business information and understand what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets and business information models. Or, if you would like to learn more about using Excel and its powerful features, sign up for a tailor-made coaching course for value added learning.
Website: www.easydatasolutions.co.il Email: in**@******************co.il