Wednesday, December 18, 2013
Layout Best Practices
Back to basics today, and a look at some Leading Best Practices for organizing your data and laying out your Excel workbooks. Although I hesitate to say that any of these guidelines are Absolute Rules, if you consistently follow these practices, you will find you can Do More with Less Effort.
Data on One Worksheet; Information on another Worksheet
Whenever possible, put all of your data in one worksheet, and your reports in another worksheet. The fewer the worksheets you have, the easier it will be for your users (including yourself, of course) to navigate and glean the information you are creating.
No Blank Rows
Avoid blank rows and columns in your data table. Your formulas will work better and your data will have greater integrity.
Deconstruct Your Data
Always try to divide your data down to its minimum components. You will be able to use more powerful functions and search your data much more effectively. For instance, if you have a database of employees, create separate fields for the first, middle, and last names (you can always easily combine them later if you find it necessary).
Format Your Headings
Use a bold font when using headings. Many of the built-in properties in Excel functions will more easily recognize them as headings when they are bolded.
Sort Your Data
This is a good habit to adopt, and certain Lookup functions rely on your data being sorted in a logical order.
Columns are for Fields
Excel obviously has far fewer columns than rows, so keep things simple by using the columns for the fields and the rows for the individual records.
If you follow these guidelines, you will easily be able to use the powerful built-in features that require organized data and a logical layout. Organization = Simplification = Information.
I wish you the best of times during the holiday season! ~Bob