Microsoft Spreadsheet
Microsoft Spreadsheet
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.
How do I formulate Times into Hours, when building a spreadsheet, using Microsoft Works?
I am building a spreadsheet. On this spreadsheet there will be times (HH:MM). I want to calculate two specific times into a total number of hours. Does anybody know the formula I need to enter to formate that cell??? I am using Microsoft Works.
EXAMPLE: In Parenthesis is how the cell is formated
CELL B3= Time In (12:00 AM)
CELL C3= Time Out (12:00 AM)
CELL D3= Total Hours (??????)
Start time in B3, finish time in C3. In D3 type
=IF(B3>C3,(1+C3)-B3,C3-B3)) Format all cells as hh:mm and enter times as military time ie 1:00 PM is 13:00.
Microsoft Spreadsheet
Microsoft Excel Tutorial - Making a Basic Spreadsheet in Excel
Datasafexl: a Brief Overview of Microsoft ® Excel Vulnerabilities
In this article, we will provide you with a brief overview of the Microsoft ® Excel security environment and examine in more detail the effectiveness of Microsoft ® Excel's built-in protection features.
Undoubtedly, Microsoft ® Excel's security environment can prove to be cumbersome even for experienced users. In addition, there are numerous methods which an intruder can use to bypass any security aspect of the Microsoft ® Excel application. Without a doubt, the general consensus is that Microsoft ® Excel's security environment is weak.
The following protection measures can be employed in a Microsoft ® Excel spreadsheet
- Password to open the workbook
- Password to protect sheet contents
- Password to protect workbook structure
- Password to protect VBA code
By using a password to open a Microsoft ® Excel file, you are effectively encrypting its contents. Thus, only individuals who know this password can access any of the workbook's contents.
Even so, the default encryption standard in the Microsoft ® Excel application is very weak and commercial password crackers from websites such as Lastbit and LostPassword can be used to obtain the password and access the contents of the file.
In addition, there is nothing that prevents a user from sharing the password with other individuals.
Subsequently, a spreadsheet developer can decide to protect the sheet contents and workbook structure so that users cannot change any formulae or the names of the sheets for example. Again, a free utility can be obtained from a website by the name Straxx to bypass these protection features within seconds.
The abovementioned utilities can also be easily used to break the password for VBA projects and obtain access to the VBA source code.
Another common technique that Microsoft ® Excel developers like to use, is to hide all sheets when the Excel file is closed. This method forces the user to 'Enable Macros' when opening a file, in order to be able to view the hidden sheets.
However, the pitfall with this approach is that users can still open the Excel file by 'Disabling Macros' and use an 'Add-in' to unhide and manipulate all sheets presented in the workbook.
To summarise, users can obtain and modify all the data in a workbook, even in situations where they are required to use a password to access a Microsoft ® Excel file.
This is why DataSafeXL's (http://www.DataSafeXL.com) innovative product, "XLSafe" is imperative for your business and your sensitive data.
About the Author
|
|
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... |
|
|
Excel 5 for Windows: Introductory Skills [VHS] ... |
|
|
Microsoft Works Intermediate Spreadsheet ... |
|
|
Microsoft Works Database,Spreadsheet [VHS] $24.99 ... |
|
|
Microsoft Office Excel 2007: Illustrated Introductory Video Companion $33.99 This video companion is designed to be used in conjunction with a core textbook and is your personal guide to Microsoft Excel 2007. Microsoft Office Excel 2007 Illustrated Introductory Video Companion contains over 60 videos, each approximately 5 minutes in length, that map directly to lessons in the Microsoft Office Excel 2007 Illustrated Introductory textbook. In addition to the Illustrated Seri... |
|
|
Introduction to Microsoft Office Excel 2007 - Part 1 $12.99 ... |
|
|
How to Use Microsoft Excel 2007 ... |
|
|
Dell Latitude D610-1.73 Laptop Wireless Computer (Refurbished) $299.00 Please do not inquire about pre-christmas delivery. We ship withing two business days of your ordering being paid and sent to us by Amazon. We are not responsible for the post office. If you require delivery before Christmas, please do not order from us. Even expedited shipping may not get there before Christmas Thank You and Happy Holidays.... |
|
|
Targus PAUK10U Ultra Mini USB Keypad, Black $14.99 The USB Numeric Keypad with 2-port Hub is ideal for the mobile user either at home or in the office. Offering two products in one it has all the functionality of a standard keypad plus provides two USB 1.1 ports for additional accessories. The low profile keys are comfortable, easy to use and the perfect complement to your notebook for anyone working on spreadsheets, accounting or financial applic... |
|
|
Logitech Mouse M110 (Black) $9.99 Logitech M110 Mouse - Optical Wired - Black 910-001600 Mice... |
Microsoft Spreadsheet
