| When Microsoft Excel is used to manipulate, store | | | | colleague can quite easily update your |
| and analyse data it can become extremely | | | | spreadsheet with a false sense of confidence and |
| difficult to manage, let alone efficiently work to | | | | inadvertently enter new data incorrectly. And |
| produce any meaningful insights. This is because | | | | finally, unlike a fully functional software system, |
| with data sets large and small, the data must be | | | | data entry in Excel generally has no automatic |
| meaningful, logical, structured, internally consistent | | | | validating rules, unless carefully setup by the |
| and clean. This holds true regardless of whether | | | | spreadsheet’s creator. |
| the data has been imported into excel from | | | | Whilst Excel cannot clean or structure all of your |
| another system or manually entered. | | | | data for you it does come with some useful |
| In this computing age, most people know that for | | | | functionality for manipulating and analysing clean |
| any data set to be useable it must first be | | | | and structured data sets. This in-built functionality |
| relatively structured and clean. A spreadsheet and | | | | includes pivot tables, sorting and filtering. |
| its table layout naturally encourages data to be | | | | Filtering alone is a powerful tool and can help to |
| somewhat structured, however ensuring data is | | | | quickly isolate data based on specified criteria. But |
| clean is also difficult. | | | | what happens if your data is clean but not very |
| As a set of general rules data is most useful | | | | structured (a common problem). For instance |
| when things like text fields hold only names as | | | | what if you, a client or your team is using colours, |
| well as meaningful and validated codes, categories | | | | fonts or some kind of formatting to classify data |
| and classifications. Text notes and other free | | | | in an Excel spreadsheet. In short, you wont be |
| form text should be isolated to a dedicated notes | | | | able to filter the data, because Excel’s in-built |
| field and thus separated from other numeric data. | | | | filtering logic requires rules based on numbers, |
| Numeric fields should hold only numeric values | | | | dates and text only. It will not perform filtering |
| (numbers, dates, %’s and in the correct | | | | based on formats. In addition Excel filtering only |
| quantum or magnitude with no text prefixes, | | | | applies down rows. It will not perform filtering |
| suffixes, spaces, text elements or text notes | | | | across columns. |
| present. You must also be careful that numeric | | | | These common complaints with Microsoft Excel |
| data is not stored as text and it should be | | | | filtering are heard time and time again by |
| internally consistent in terms of the correct | | | | engineers, accountants, management consultants, |
| format so that it can be used in calculations or for | | | | bankers and finance professionals who work with |
| comparison and queries. Finally, addresses should | | | | data in Excel spreadsheets on a daily basis. Many |
| be separated out into multiple fields such as | | | | spreadsheet users including financial modellers |
| street address, town /suburb, state / province, | | | | (who seem to be leading the charge) are turning |
| postal code and country to allow for geographic | | | | towards Excel Add-ins and software tools that |
| analysis and mail outs if required. | | | | plug into Microsoft Excel to help them improve |
| Fixing up a data set to meet these criteria is | | | | the in-built filtering logic of Microsoft Excel and |
| called data scrubbing, cleansing or massaging. This | | | | thus analyse certain data sets quickly and easily. |
| data cleansing process can be very time | | | | Probably the most popular and widely used Excel |
| consuming even for an experienced Microsoft | | | | add-in for this purpose is ‘Filter by Format. An |
| Excel user, database engineer, business analyst or | | | | add-in created by the company ‘Spreadsheet |
| computer programmer. | | | | Guys’. They have developed a unique add-in |
| So why does data that inevitably finds its way | | | | which allows for filtering down rows and across |
| into a Microsoft Excel spreadsheet often suffer | | | | columns based on one or more formatting criteria |
| from the problems outlined above. The reasons | | | | of a target cell within the data range. |
| are many. If the data is imported, it may have | | | | If you want to filter based on colours and fonts, |
| been sourced from a combination of other | | | | or finally filter across columns then ‘Filter by |
| spreadsheets, databases, systems, reports, word | | | | Format’ definitely fills a need and has already |
| documents, emails or web pages. If the data has | | | | cured the frustrations of many Excel spreadsheet |
| been entered manually it may have been poorly | | | | users, helping them to more quickly filter data |
| done so by an inexperienced computer users | | | | which has been classified using formatting, thus |
| such as administrative or junior staff with a lack | | | | helping them to slice, dice and analyse data in their |
| of understanding for data structures. Excel is easy | | | | Excel Spreadsheets. |
| to use and widely accessible, so an inexperienced | | | | |