Sunday 27 October 2013

[HM:256871] How to Improve Your Excel Database Management Skills



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




--
--
We are also on Face Book, Click on Like to jois us
FB Page: https://www.facebook.com/pages/Hyderabad-Masti/335077553211328
FB Group: https://www.facebook.com/groups/hydmasti/
 
https://groups.google.com/d/msg/hyd-masti/GO9LYiFoudM/TKqvCCq2EbMJ
 
---
You received this message because you are subscribed to the Google Groups "Hyderabad Masti" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hyd-masti+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

No comments:

Post a Comment