How to Create List Reports in Excel
Every package has its own individual report writer module, or it may incorporate a third-party report writer package that is far too complicated for most users.
But when all else fails, you can resort to that one sure-fire friend and helper: Excel. How would we live without it? Excel can help enormously in reporting. There are two phases in report design: getting the data and formatting it. And Excel is brilliant at formatting. If only you can get the raw data out of the accounts package and into Excel, producing the rest of the report will be straightforward.
Let’s discuss the tools that Excel offers to help you in writing list reports, because these tools don't seem to be very well known. They are the Data Subtotals command, and the Data Import External Data command (also called MS Query).
Before you start you must get your data into Excel in the form of a list. That is, each row of the worksheet contains a record, while each column represents a data field. At the top of each column is the fieldname.
A practical example will make it clear. Download a sample database and load it into Excel.
Let’s say, for example, that each record represents a single line in a sales invoice. So the first two rows come from a two-line sales invoice to our sample company, American Limited, invoice number SIN1001. Let’s also say that the first item on this invoice is a Compaq H16 250mb server, and the second item is an HP Deskjet Printer.
The list is sorted in invoice number order or the INVNO field.
Suppose you wanted to see the total Quantity and Net amounts for each invoice. Simply click anywhere on the data, then from the main menu select: Data Subtotals. The Subtotal box appears. Enter the following:
- At each change in: InvNo
- Use function: Sum
Add Subtotal to: tick the boxes next to Qty and Net. Untick any other boxes and click OK. After every invoice, Excel inserts a line and displays subtotals in the Qty and Net columns.
The Limitations of Sub-Totals
Sub-Total is a handy little command. However, Sub-Total is fairly limited. For example, let’s say you keep a daybook in Excel in which you list all sales invoices as they are sent out. Every so often you want to add them up and see the totals you’re billing. The only problem is that you want to see the totals by week or month. But Excel can only sub-total on what is actually there on the screen, so you have to insert two new columns to the left of each invoice WeekNo. and MonthNo. Whenever you enter an invoice, you must manually enter a week and month number as well. It helps if you always ensure that columns such as WeekNo and MonthNo specified in the At Each Change box are positioned at the far left.
The other limitation is that list reports such as a sales invoice daybook or an outstanding orders report are being updated constantly, and you may need to re-print the report every day. Importing the data into Excel, manually adding extra fields, and creating the finished report is just too long-winded. It would be acceptable for a report you created once a month, but for a daily report you really want to be able to press a button and refresh it automatically.
Now, let’s talk about what you can do with MS Query. These days, almost all accounts packages have ODBC drivers which will allow you to link Excel directly to the data files in your accounts package. The Data Import External Data command allows you to see the data tables, and to pull in the data you need. Once you've pulled in the data, you can save the workbook. When you want to re-run the report a day or two later, simply re-open the workbook, and refresh.
A word of warning: with Query and ODBC you are looking at raw data files that contain numerous traps for the unwary. For example, a recent Pegasus Opera user recently pointed out that Opera stores numbers without any decimal places. And frequently you will find that the format of dates is wrong, or that numbers are stored in double precision format, or both debit and credit values are positive.
So when starting with Query it is a good idea as your first exercise to take a standard report from the accounts package and try to reproduce it. Once the totals on your Query report agree with those on the package report, you can feel confident that you're picking up the right data fields.
The great thing with Excel is that you can correct or amend raw data by calculating a new field. For example, simply open a blank column, then create a new field by dividing the raw data by 100 (e.g. = A2/100 where the raw data is in column A). We put this new field in the middle of the worksheet, then shunted the raw data column out to the far right where nobody would notice.
Hopefully, after following the above steps, you now should be able to create list reports on Excel that will make your job more efficient. Be sure to check out future Excel Tips and other software tips on AccountingWEB.com.