By Junaid Tahir I have summarized the most common mistakes professionals do for managing their excel databases at work place. Also...
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 data's 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 "Permission applied" "Permission reviewed" "Permission approved" "Permission reviewed"
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 as Copies/Pen/Markers andMarkers/Pen/Copies and Pen/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
- If you would like to receive the Excel file containing basis elaboration of above points, please drop me a line so I can respond.
- If you would like to receive the Excel file containing basis elaboration of above points, please drop me a line so I can respond.
Some Article You May Like:
