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
the usage of Excel capabilities. Attached excel file contains practical examples for each point mentioned below:
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 and in case you want to discuss something about this article, don't hesitate to contact me.
Some Article You May Like:
Excel Tip: How to create A drop-down list
Microsoft Excel 2007 keyboard shortcuts
10o Rules for Great Project Managers
10 Tips to manage emails effectively in Outlook
27 Email Etiquette Tips for Professionals
About Author: Junaid Tahir is a passionate blogger. He writes articles on Leadership, Stress Management and Life Enhancement subjects at his personal blog
--
{{{{ Rawalpindi-Islamabad Friends}}}}
************************************************************************************
All members are expected to follow these Simple Rules
Abuse of any kind (to the Group, or it's Members) shall not be tolerated
SPAM, Advertisement, and Adult messages are NOT allowed
This is not a Love, Dating Forum so sending PM or Chat Inv will be considered as illegal Act. will be BANED Instantly.
************************************************************************************
You received this message because you are subscribed to the Google
Groups "Pindi-Islamabad Friends" group.
To post to this group, send email to Pindi-Islamabad@googlegroups.com
To unsubscribe from this group, send email to
Pindi-Islamabad+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Pindi-Islamabad?hl=en
---
You received this message because you are subscribed to the Google Groups "Pindi-Islamabad Friends" group.
To unsubscribe from this group and stop receiving emails from it, send an email to Pindi-Islamabad+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
No comments:
Post a Comment