Excel as Reporting Program

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

Excel Chapter 7 of 24: Excel as Reporting Program

The report is a communication tool. The purpose of a report is to inform, to highlight the most important conclusions of an analysis. Reports are designed with the reader in mind to help him focus and to make sure he is interested in what is reported. Because of the variability in what needs to be reported about and of the readers themselves the best reporting progam to acheive these goals is Excel. Only with Excel can you design exactly what you want and automate the reports without complex training and at a reasonable cost.

There are many reporting programs on the market (Crystal Report). All databases, accounting and other specialized programs and, ERP systems include a reporting program but they are usually complex and limited. To use these programs you nee to train a certain number of people and you create a bottle neck in reporting. Everybody depends on these "specialists" who are not necessarily the most knowledgeable people about the data and the business. You end up compromising and not getting the best communication tools.

The general approach of our  reporting applications at www.excel-vba.com is the "Data Sheet" approach. The data is entered manually, copied/pasted or imported on a sheet that is recognized by Excel as a database. The report or reports are then designed to be fed from the raw data using mainly the SUMPRODUCT function.

Excel is  only "Word +" to create reports for a lot of users. But Excel really becomes the best  reporting application when you adopt the " Datasheet Approach" . From then on no need to spend a fortune to buy and get trained on so called reporting programs (Crystal Report) or any reporting application within large databases (Oracle, SAP), manufacturing programs  (JDE), sales or accounting programs (PeopleSoft and others).

The "Datasheet Approach" consist of creating a sheet for the data and other sheets for reports. SUMPRODUCT formulas carry the data from the data sheet to the report. The data sheet receives the data imported from all kinds of databases, manufacturing, sales or accounting programs. It can also be an Excel database with data entered manually.

As a first step you validate the data on the data sheet using the database functionalities. Then you validate the reports using the calculator.

Once the data and the reports are validated you can then print them or publish them.

Dynamic Reports

Using drop-down lists (see chapter  1B) you can create a single report for 50 departments and by changing the value in the drop-down list you show the numbers for each department. Then you print it or extract the sheet and send it to the appropriate recipient. See the workbook "excel-example-dynamic-report.xls".

Drop Power Point

In Excel you can add a map of USA and when you click on one of the States you are taken to the sheet that shows the table of data for the selected State. It is called image map. See it at work in the downloadable workbook "excel-exampe-imagemap.xls". From then on you can forget about learning Power Point. You can develop a real show with Excel and because it is in Excel it looks more credible than in Power Point.

Extracting Reports

To extract the report from a workbook developed with the "Datasheet Approach" you right click on the tab of the report sheet. The following window appears:

Excel move sheet

DO NOT FORGET to check the "Create a copy" check box. In the "To book:" text box select (new book) and click "OK" . The in this new workbook you want to replace all the formulas by values to avoid having outside links. Select all the cells by clicking on the small square between the row numbers and the columns letters:

Excel Select all

Click on the "Copy" icon then go to the menu "Edit/Paste Special" Select "Values" and click "OK" . Select cell A1 and save the new workbook with an appropriate name.

Printing Reports

One of the great functionality in Excel allows you to set the size of your report so that anybody with any printer will get the same output. Go to "File/Page Setup/Page" :

Excel Page Set up

Use the "Fit to:" section. You can force the report to print on a single page (1-1) or on many pages (1-22). You can also use the "Adjust to:" . No other program offers you this flexibility.

You develop perfect reports with Excel but to make sure that you have a great on screen presentation you spend hours redoing the same report in Power Point. With Image Map you can have a map of New England in Excel and when you click on Maine, or New Hampshire or any other Sate the data and chart for the State come on screen.

See it with step by step explanations in "excel-example-image-map.xls"

 

Next Chapter: Conditional Formatting

Excel Table of Contents
or use the quick links below

 

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Excel as Reporting Program