Sort and Filter
A large data set, a list of all loans in your portfolio from your core processor for example, can be very intimidating. However, Excel provides many tools to help you organize and display your data.
One of the most powerful tools in Excel for organizing any data set is the sort and filter function. After setting up your data (see the Excel Tip from the November newsletter for help setting up your data), click on the Data tab and click the Filter button.
Each column should now have a small button with a triangle in it on the top row. Choose the Column that you want to analyze, and click on the button in the top row. You will get a drop down menu with several options to either Sort or filter your data.
Sorting your data will organize all of the rows based on the column that you choose either lowest to highest/ highest to lowest, alphabetical/reverse Alphabetical, oldest to newest/newest to oldest depending if you have numbers, letters, or dates in the column.
Filtering your data will hide all rows that don’t meet the criteria that you choose.
You can also use the sort and filter options together. For example, you can sort the loans by loan balance and then filter by the loan type auto. This provides you with a list of the Auto loans from lowest to highest balance.