Excel is the tool of choice for getting data ready for import. Data is brought into Excel directly from the legacy program, or it is imported as a delimited file created by another application.
Once the data is in Excel there are several strategies to prepare it for import. This article shows how easy it is to filter data with just the click of a mouse.
Below is a sample worksheet. I have selected a cell in column E, then with a right click (or left click if you are left handed) I selected Filter > Filter by Selected Cell’s Value.
Let’s look at the changes to the worksheet. A few very helpful changes have taken place. First, and most obvious is that only cells with the value of “Hawaii” have been selected. Perhaps the least obvious is the blue color change to the row numbers. This represents that records have been filtered and rows are now hidden.
The header row (row 1) indicates that the data has been filtered in the worksheet, specifically column E. The other columns have a drop down arrow. We can select an additional filter in those columns as well. Let’s look at the dropdown in Column A.
Of the filtered data, there are two names that we can select to narrow our selection.
Let’s go back to Column E, and click on the filter icon.
A list of all data that is available to select from will appear. ‘Select All’ can be checked and other states can be checked for a quick scan to look for errors in typing. Other data analysis can be pursued from this quick view as well.
Further up on that same menu window is a dynamic search box for that specific column. As you type it, it will select data that matches your criteria. For example if you type “Co” it will select “Connecticut” and “Colorado”– placing a check in each box.
Also on that same menu window is a filter icon with a red x. Select this to “Clear Filter From ‘State’”.
An advanced Data Analyst may choose the Text Filter Option. This option offers a menu of choices that can help you dig deeper into your data. These include easy access to filters for Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain, and Custom Filter.
The ellipsis indicate that another window will open.
As you can see, an easy to use filter wizard has some preset information filled in. To filter for data that “does not equal” Alabama in Column E, select Alabama and click OK.
The Custom AutoFilter box is shown below. As you can see, many more options are available for data analysis.
You can choose any filter from any column. With the added options in the Custom filter window you will find yourself filtering, and finding information with just a simple click of your mouse!
Want more information on analyzing data? Want to speak to a consultant? Contact KTL Solutions by filing out our form or calling 301.360.0001.
Cathy Goetz | Business Software Consultant
Cathy is responsible for supporting clients with their Microsoft Dynamics GP solutions. From the beginning, she helps her clients determine the scope of their project and provides software demonstrations. Her expertise in gathering requirements will produce a carefully planned system for successful implementation and configuration.
Cathy holds a Bachelor of Arts in English and Elementary Education from Jamestown College in Jamestown, ND. Cathy has over 28 years and a wide range of industry experience in Agriculture, Retail Logistics, Construction, Contracting, Education, Finance, Food, Gaming, Real Estate, Property Management, Legal, Government and Nonprofit. Her roles included management positions, database analyst, application developer, software consultant and trainer, business owner, founder, teacher, and senior accountant. She has been involved with several ERP implementation projects, including Dynamics GP. Her specialties include data migration, Microsoft Access application development, data integration, data cleansing, database normalizing, and developing relational databases from legacy systems. She is highly skilled in report writing, documentation, developing training materials, and presentations. Cathy is certified as a Microsoft Certified Master Instructor for Microsoft Office and has recently completed an accounting program at Frederick Community College. She is currently preparing for the CPA examination.
