Creating CrossTab reports
You can use CrossTab reports to analyze and organize data located in notebooks and databases. For example, you might maintain a database with addresses of customers in different cities, including information on customer sales. The database effectively stores the information but does not allow you to analyze the data in a convenient or precise manner. By building a CrossTab report, you can analyze, organize, and summarize the data into a constantly updated report on area sales. The same database can also be analyzed to determine sales by salesperson for each product that the business sells.
Data does not have to be on the same spreadsheet of a notebook for you to create a CrossTab report. You can connect two or more spreadsheets that contain data and produce a CrossTab report to summarize the data.
Planning and interpreting your data analysis
Before you produce a CrossTab report from a data source like a database, you must plan which data fields you want to analyze. The CrossTab report feature analyzes data by using selected data fields as row or column labels and numeric data fields as the data being analyzed. The fields in the row and column areas are used as selection criteria to determine which values from the database to include at intersections of the row and column field labels. For example, assume a database contains product sales information (data). In the CrossTab report, place the field containing the names of the company’s salespeople along the left of the spreadsheet (rows), and the field containing the products you sell along the top of the spreadsheet (columns). The intersection of the labels “Salesperson A” and “Products” shows the total number of products that Salesperson A sold.
To build a CrossTab report |  |
|
1.
|
|
Click a spreadsheet cell containing data.
|
|
2.
|
|
Click Tools
 Data tools CrossTab Report. |
|
3.
|
|
Drag any fields from the list in the Fields area into any of the following positions of the Layout area:
|
|
•
|
Rows
|
|
•
|
Columns
|
|
•
|
Data
|
|
•
|
Pages
|
|
4.
|
|
Type the report destination in the Destination box.
|
|
Unless you select a destination, the CrossTab report will be placed in A1 of the next available, unprotected spreadsheet.
|
|
•
|
|
Dragging a field to the Pages position is optional. Fields placed in the Pages position appear in the upper-left corner of the CrossTab report as items in a list box so that you can select the desired field item to view. If fields are placed in the Pages position, you can expand the report. For more information about expanding CrossTab reports, see “Expanding CrossTab reports.”
|
|
•
|
|
You can drag more than one field into each position of the Layout area, but fields cannot appear more than once in each position.
|
To create a CrossTab report by connecting multiple spreadsheets | |
|
1.
|
|
Click a spreadsheet cell containing data.
|
|
2.
|
|
Click Tools
Data tools CrossTab Report. |
|
3.
|
|
Click the Range picker
 in the Source data type box. |
|
4.
|
|
Press Shift, and click the tabs of the spreadsheets where the data is stored.
|
|
You must click the spreadsheet tabs in ascending order.
|
|
5.
|
|
Click Maximize on the CrossTab report title bar.
|