There’s hidden value in your data. Somewhere there is something that could lead you to a better decision. There’s information that will help you get where you want to go.

The problem is – how do you find it? How can you spot trends or identify problems when you have to wade through so much data to find an answer?

What if there was a way to organize your data so you could look at it from all angles? What if you could break your data down and filter it to answer important questions? What if you could explore the data to know the right questions to ask?

With the data pilot feature of Calc, you can.

Using the Fictitious Garden Center as an example, we will use the datapilot to summarize sales detail data into several different categories.

For example, in this table the data is broken down by sales rep. For each sales rep, Calc has created a row and a total. You could include grand totals if you like.

In this next example, the data is broken down by sales rep, but we could have just as easily chosen to group by promotion, supplier, category or another field.

You can easily compare the values of two fields by using one as a column field (in this case sales rep) and a different field (like category) as a row field.

Now we can clearly see the relationship between plant categories and sales reps.

Let's start by building a simple data pilot table then we'll take a look at some more advanced things you can do with the data pilot.

Data pilot tables usually start with underlying detail data. In this case we'll use the salesdetail file we imported earlier.

First, we need to select the detail data we want to summarize. Then select data – data pilot – start from the menu.

Calc will then ask whether you want to base your analysis on the current selection or whether you want to base the analysis on another data source such as a database or an external interface.

This example will be based on the currently selected data, leave that option checked and click OK.

The next screen will show a graphical representation of the pivot table. You'll also see buttons representing fields (in this case the fields correspond to the columns in the underlying spreadsheet.)

If we wanted Calc to create a new row for each value of Rep – we would drag and drop the Rep button to the row fields area.

Next we need to tell Calc how to create the totals. We'll drag and drop the amount button to the data fields area.

Notice that the calculation defaults to a sum of the amount field. If you want to create a total other than a sum, double click on the button and choose a different function.

If we were to click the OK button now, our table would look like this. There is a row for every rep, and a total that sums the values from the amount column of the underlying detail data.

What if we wanted to see how promotions affected sales?

We would remove the sales rep field from the column (you can do this by clicking the field and dragging it out if the row field area) then dropping the promotion button in the row field area.

Now if we were to click the OK button we would get a table like this that generates a row and a total for each promotion.

What if you wanted to see total sales by sales rep, but wanted to create a column for each rep instead of a row?

Remove the promotion button from the row field area, then to drop the rep button in the column fields area.

So far we've only created tables that group the data by a single field. Suppose we wanted to analyze the relationship between two fields? For example, What if we wanted to see each promotion broken down by sales rep?

To do this you choose one field to determine the column values and another field to determine the row values.

To expand on the previous example, we have already told Calc to create a column for each sales rep by placing the rep button into the column field area.

Next, we'll add the promotion field to the column field area.

If we were to click the OK button now, the resulting table would look like this.

Each row of the table (representing a promotion in this case) is broken down and totaled into a new column for each sales rep.

This lets you analyze relationships between data elements.