By Junaid Tahir
I have summarized the most common mistakes professionals do for managing their excel databases at work place. Also I have summarized some suggestions in order to
1- Cells merger is strictly not allowed for professional databases. Merged cells prevent automatic reports generation, formula reference errors, data automation and several other limitations.
2- Cell formatting should be applied on each column. As an example if Column-A is dedicated for dates then no other format (text, number etc) should be allowed. This can be controlled by Drop Down menus or Data Validation Tool of Excel.
3- Control Multiple Entries strictly. If this is unavoidable then use unique naming conventions to clearly identify the second entry. For example Microwave, Microwave_UAE, Microwave_KSA etc. Use Cell Highlight Rules to identify duplicate entries or use Data Validation to generate pop up message for the user.
4- No blank row or column should be left in the database. This ensures datas integrity.
5- Using sensible and logical words. For example under the column PAC status if I write Done or In process it may have multiple meanings such as Permissionapplied Permissionreviewed Permission approved Permissionreviewed
6- Use uniform and symmetrical information. If you want to differentiate two values you can either use / or , but not both. Also swapped information to be avoided such asCopies/Pen/MarkersandMarkers/Pen/CopiesandPen/Markers/Copies
7- Use Trim function to remove extra spaces from your entries.
8- Do not use a lot of colors as it does not look professional specially if it is required to be sent to management
9- File name should be relevant (New tracker.xls, book1.xls, latest update.xls are NOT appropriate words)
Bonus Tips:
1- Pivot Tables is one the most powerful tools of Excel which can generate multi dimensional reports within seconds however it not used by many professionals. Pivot report should be applied right from the start of new database creation. So that the database owner understands its working philosophy and organize the database accordingly. Read this Super Article on Pivots
2- Automation should be done as much as possible. This can be done using Formulae (If, count, sum, vlookup, clean tools of Excel). Data automation reduces the chances of error to a great extent. Also, use data validation to inform the user about what information is required to be filled in.
3- vLookUp: This is one of the most powerful tool to extrat data from multiple columns and files. Read this article on vLookUp
4- Begin with the End in Mind. What reports the management is looking for. Can you generate the reports with-in seconds or you have to do manual work to generate the report?
If you would like to improve your Excel Tips, I highly recommend you join this online forum and in case you want to discuss something about this article, dont hesitate to contact me.
About Author: Junaid Tahir is a passionate blogger. He writes articles on Leadership, Stress Management and Life Enhancement subjects
www.keralites.net          |
To subscribe send a mail to Keralites-subscribe@yahoogroups.com.
Send your posts to Keralites@yahoogroups.com.
Send your suggestions to Keralites-owner@yahoogroups.com.
To unsubscribe send a mail to Keralites-unsubscribe@yahoogroups.com.
Homepage: http://www.keralites.net
No comments:
Post a Comment