Organize Your Data

Today we are going to look at some important Best Practices for organizing your data and laying out your Excel workbooks. These are not, of course, Absolute Rules, but if you follow these practices, you will find Greater Control and More Options in your Excel adventures.

Data on One Worksheet; Reporting on another Worksheet
Whenever possible, put all of your data in one worksheet, and your reports/information in another worksheet. The fewer the worksheets you have, the easier it will be for your users (peers, employees, executives, and yourself, of course) to navigate and obtain the information they want to see when they want to see it.

No Blank Rows
Avoid blank rows and columns in your data table. Your formulas will work better and your data will have greater integrity.  Just don’t do that.

Deconstruct Your Data for Better Analysis
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 using Concatenation if you find it necessary to do so).

Format Your Headings 
Use a bold font and centering when using headings. Many of the built-in properties in Excel functions will more easily recognize them as headings when they are bolded.  Besides that, they just plain look better!

Sort Your Data 
This is a good habit to adopt, and certain Lookup functions rely on your data being sorted in a logical order.  If it makes logical sense, why not do it…

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.  This may not be as necessary as Excel becomes more powerful, but it will almost always be More Intuitive.

If you follow these guidelines, you will easily be able to use the most powerful built-in Excel features that require organized data and a logical layout. As I have said in the past, Organization = Simplification = Information.  Good Stuff!