Microsoft Excel
Filtering data based on font, colour and format criteria, down rows or across columns in a Microsoft Excel spreadsheet
When Microsoft Excel is used to manipulate, store and analyse data it can become extremely difficult to manage, let alone efficiently work to produce any meaningful insights. This is because with data sets large and small, the data must be meaningful, logical, structured, internally consistent and clean. This holds true regardless of whether the data has been imported into excel from another system or manually entered.
In this computing age, most people know that for any data set to be useable it must first be relatively structured and clean. A spreadsheet and its table layout naturally encourages data to be somewhat structured, however ensuring data is clean is also difficult.
As a set of general rules data is most useful when things like text fields hold only names as well as meaningful and validated codes, categories and classifications. Text notes and other free form text should be isolated to a dedicated notes field and thus separated from other numeric data. Numeric fields should hold only numeric values (numbers, dates, %’s and in the correct quantum or magnitude with no text prefixes, suffixes, spaces, text elements or text notes present. You must also be careful that numeric data is not stored as text and it should be internally consistent in terms of the correct format so that it can be used in calculations or for comparison and queries. Finally, addresses should be separated out into multiple fields such as street address, town /suburb, state / province, postal code and country to allow for geographic analysis and mail outs if required.
Fixing up a data set to meet these criteria is called data scrubbing, cleansing or massaging. This data cleansing process can be very time consuming even for an experienced Microsoft Excel user, database engineer, business analyst or computer programmer.
So why does data that inevitably finds its way into a Microsoft Excel spreadsheet often suffer from the problems outlined above. The reasons are many. If the data is imported, it may have been sourced from a combination of other spreadsheets, databases, systems, reports, word documents, emails or web pages. If the data has been entered manually it may have been poorly done so by an inexperienced computer users such as administrative or junior staff with a lack of understanding for data structures. Excel is easy to use and widely accessible, so an inexperienced colleague can quite easily update your spreadsheet with a false sense of confidence and inadvertently enter new data incorrectly. And finally, unlike a fully functional software system, data entry in Excel generally has no automatic validating rules, unless carefully setup by the spreadsheet’s creator.
Whilst Excel cannot clean or structure all of your data for you it does come with some useful functionality for manipulating and analysing clean and structured data sets. This in-built functionality includes pivot tables, sorting and filtering.
Filtering alone is a powerful tool and can help to quickly isolate data based on specified criteria. But what happens if your data is clean but not very structured (a common problem). For instance what if you, a client or your team is using colours, fonts or some kind of formatting to classify data in an Excel spreadsheet. In short, you wont be able to filter the data, because Excel’s in-built filtering logic requires rules based on numbers, dates and text only. It will not perform filtering based on formats. In addition Excel filtering only applies down rows. It will not perform filtering across columns.
These common complaints with Microsoft Excel filtering are heard time and time again by engineers, accountants, management consultants, bankers and finance professionals who work with data in Excel spreadsheets on a daily basis. Many spreadsheet users including financial modellers (who seem to be leading the charge) are turning towards Excel Add-ins and software tools that plug into Microsoft Excel to help them improve the in-built filtering logic of Microsoft Excel and thus analyse certain data sets quickly and easily.
Probably the most popular and widely used Excel add-in for this purpose is ‘Filter by Format. An add-in created by the company ‘Spreadsheet Guys’. They have developed a unique add-in which allows for filtering down rows and across columns based on one or more formatting criteria of a target cell within the data range.
If you want to filter based on colours and fonts, or finally filter across columns then ‘Filter by Format’ definitely fills a need and has already cured the frustrations of many Excel spreadsheet users, helping them to more quickly filter data which has been classified using formatting, thus helping them to slice, dice and analyse data in their Excel Spreadsheets.
About the Author
For Filter by Format Visit SpreadsheetGuys.com, they are your online source for software add-ins, templates, downloads, books and resources for Microsoft Excel.
Microsoft Excel Tutorial for Beginners #1 – Overview
|
|
Microsoft® Excel® 2007 Quick Reference Guide – Excel 203: Sorting, Filters & Pivot Tables $7.95 8-page bi-fold. This quick reference guide is a valuable resource for any user looking to master the ultimate data analysis tool in Excel: PivotTables. Also discussed in this guide are sorting features as well as data analysis using filters. Topics include: sorting data using various techniques, using chronological sorting, adding data filters to extract data, applying filters to drill-down and l… |
|
|
New Perspectives On Microsoft Office Excel 2003 … |
|
|
Wine Cellar 2.1 Template for Microsoft® Excel Lets You Easily Track Your Personal Wine Collection Wine Cellar 2.1 Template for Microsoft® Excel lets wine enthusiasts easily track and manage personal wine collections. The template saves people the time of setting up their own system. Data entry is easy and wines can be sorted by varietal, pairing, when to drink, quantity, price and many other categories. The spreadsheet can be customized to match different collections. Please note that the tem… |
|
|
Music of Your Life: Best of Robert Goulet $2.98 Covers Windows, Word, Excel, Access, PowerPoint, Binder, Exchange, Windows 95, The transition from 3.1 to Windows 95… |
|
|
Split Image $10.51 No Description AvailableNo Track Information AvailableMedia Type: CDArtist: EXCELTitle: SPLIT IMAGEStreet Release Date: 11/14/2000… |
|
|
Microsoft Excel 97 [VHS] $14.98 … |
|
|
MacAcademy ~ Excel: Latest Version Tape #2 {Executive Training for the Successful Macintosh User} $4.99 … |
|
|
Microsoft Excel 4.O Windows/Intermedi [VHS] $39.99 … |
|
|
NeatReceipts 4 Plus USB Mobile Office Scanner and Digital Filing System for Windows XP, Vista and 7 PC Users, Includes Travel Case, NeatWorks Version 4.0, PDF Transformer Software and More – Export to Excel, Word, TurboTax and Quickbooks! $143.83 NeatReceipts is a mobile scanner and digital filing system that helps you manage all of your business and personal paperwork on your computer. Package includes the latest version of NeatWorks digital filing software Version 4.0 SP3a which can identify, extract, and automatically organize important information from receipts, business cards, and documents. All scanned documents are searchable by key… |
|
|
Dell Latitude D610 Laptop Computer System Centrino/Pentium M Processor Wireless XP Pro This latitude series has the new modern style silver casing and is extremely fast, thin and lightweight. The Dell Latitude D610 has all of the standard ports and slots required for everyday use…. |
Tagged with: excel • howto • microsoft • microsoft excel download • microsoft excel formulas • microsoft excel help • microsoft excel templates • microsoft excel tutorial • reference • tips
Filed under: Excel Tips
Like this post? Subscribe to my RSS feed and get loads more!
Leave a Reply