Three Quick Tips For Creating Excel Workbooks for Generating Reports

Microsoft Excel is an absolute powerhouse for data organization and analysis; it is an excellent option for both storing data and report generation. While building an Excel report spreadsheet is not overly complicated, there are certain quick tips you should know that will enhance your Excel reports and allow them to be much easier to use and read, as well as be more secure.

Tip #1: Use Separate Sheet for Data and the Excel Report

Excel reports are all about organization, so my number one tip is to keep all of your data on a different sheet (in the same workbook however) then the one that displays and calculates information (keep everything in the same workbook however) . Keeping all of your raw data away from calculations and formatting protects your raw data from being accidentally manipulated or changed, and keeps your report professional looking. Make cells in your Excel report sheet reference cells in your data, then use formulas to analyze it.

Tip #2: Protect Your Report Sheet and Use Data Validation

It is very easy to delete a formula in Excel by typing a value over it by accident. Do yourself a huge favor and save yourself much re-programming time by locking up your formula cells. After you have a formula entered into a cell, right click it and go to Format cells -> Protection. Now, once you have all of your cells protected go to the Review tab on the ribbon, and select "Protect Sheet" and lock it with an easy to remember password. Now that your sheet is locked, you can be safe from accidental clicks from yourself or anybody else. A similar feature you should take advantage of for your Excel reports is called Data Validation. Data Validation can be found under the Data tab in Excel 2007, it allows you to restrict the type of input a user can insert into it. For example you may want to only have a number between 1-100, or only names stored on a list under another sheet. For more specifics on Data Validation check out the Excel help files.

Tip #3: Learn How to Use the Function VLOOKUP( ), and Embrace it!

The VLOOKUP( ) function is absolutely essential for creating professional and efficient Excel reports. It is used to take the value of a given cell, look it up into a pre-made table, then set the value equal to the corresponding adjacent entry on the table. This may sound complicated, but really its not. For example, if we had a table that lists all employee names in one column, then next to it their phone numbers, we could use VLOOKUP( ) to automatically look-up and display the phone number each time you enter a employees name in the report!

As you can see in the above example, this very simple Excel report takes the name you enter (in blue), then automatically looks up the value of the phone number next to it in the table to the right, then displays it in the orange square. It may seem trivial in this example, but if you had a list of 100 people and needed to display their phone number every time you entered their name in your excel report, you will appreciate this feature. Check out Excel's help files to see how to use VLOOKUP () function, its not difficult!