OpenOffice Calc 3.x Guide
Previous Page	Home	Next Page

A typical introductory example in courses and books for beginners with spreadsheets is a simple sales volume overview.

This example demonstrates the basic way to use the user interface and how to insert text and numbers into cells. Useful aids like AutoFill and drag and drop have been demonstrated in other chapters. The most important part is the connection between cells through formulas, for example addition with the plus operator or the SUM function.

This small exercise might be useful for a first contact with the program, but it shows only a very small fraction of the tasks in an office. To create such a sales overview, you also need the orginal data. That is, before you can use a spreadsheet for creating the sales overview, you need to add many single purchases from different lists and then enter the sums into the relevant fields C5 to F7.

Practical problems and questions

1. For displaying additional values (for May, June, July, and so on) over time, you need to add extra columns. This means that you have to change the structure of the calculation sheet. This is not only somewhat inefficient from a workflow point of view, it also adds some practical questions: How do references react, if you add more columns or rows to the sum formulas?
2. The layout, where the timeline is displayed horizontally, might be less convenient if you add more months. For using space more efficiently, a vertical layout might be better. How can the table then be transposed? Do you have to enter everything again?
3. Assume that the management asks unexpected questions or for an additional subdivision for the different sales regions or a constraint of the sales for a given employee. In this case you again have to manually add all the sums and create different tables in many variations.
4. Is it really acceptable and realistic to create such an overview by adding the different values manually? This is really a lot of work and extremely error-prone!

Solution

The most important part of your task in the example is the addition of the sales per month and field. This had to be done manually. To do this automatically with the program, just get the data into Calc. You can enter the single numbers by hand or you can import a file from your bookkeeping software. In any case we assume a continuous table, that keeps track of all sales in a somewhat primitive form.

You can create the sales volume overview by following these instructions:

1. Select the cell A1 (or any other single cell within the list).
2. Select Data > DataPilot > Start and click OK.
3. The DataPilot dialog (Figure 3) has four layout areas (large white fields) and five fields that look similar to buttons. These small fields are the titles of the different columns of your list.
• Move the field date into the Layout area Column fields.
• Move the field sales into the Layout area Data fields.
• Move the field category into the Layout area Row fields.
4. Click More, to see more options in the lower part of the dialog.
5. Choose – new sheet – for Results to.
6. Click OK.

DataPilot dialog
7. The result appears on a new sheet. It has the wanted structure, but the columns are not yet grouped into months.

DataPilot result without grouping
8. To group the columns. select cell B4 or any other cell that contains a date. Then select Data > Group and Outline > Group and click OK. The result is now grouped for months.

Grouping on months

DataPilot result grouped for months

In this result you will recognize the beginners’ example. It is very easy to produce, without any further knowledge about the spreadsheet. (You do not have to enter any formulas.)

Advantages

1. No manual adding of single values necessary. No manual entering of the values. Less work and fewer errors.
2. The layout is very flexible: months horizontal and fields vertical or vice versa: two mouse clicks.
3. Additional differentiating factors are immediately available.
4. Many evaluation possibilities, for example: number or average instead of sum, accumulated values, comparisons and more.

We will now demonstrate some of these advantages.

Starting with the result above, move the Date field to the left of the Category field. Now the summary is as shown below.

To transpose the table completely, just move the field Category above the area of the displayed values, to C3.

In contrast to the beginners’ example, it is now very simple to view or add different aspects of the underlying data. For example, to see the values for different regions just do the following:

1. Select the cell A3 (or any other single cell that is part of the DataPilot result).
2. Select Data > DataPilot > Start, to start the DataPilot again. Drag the field Region into the layout area Row fields. Depending on the order you choose for the row fields, you get in the result either regions with date subdivisions or vice versa.
3. Click OK.

Additional subdivision into regions, added later

In another variation you want to add the mentioned employees.

1. Select the cell A3 (or any other single cell that is part of the DataPilot result).
2. Select Data > DataPilot > Start, to start the DataPilot again.
   • You do not need the field Region in this case. Drag it out of the layout area.
   • Drag the field Employee into the layout area Page fields.
3. Click OK.

Fields that you use as page fields are placed in the result above the summary with the name Filter. You then have a drop-down list that you can use to show only the sums of a given employee:

Up to here we have not seen the most powerful features of the DataPilot. The following examples will show you more.

OpenOffice Calc 3.x Guide
Previous Page	Home	Next Page