the master table
1. First, double-click the tab at the bottom of the screen (above the green bar line) and type
Master over the tag line Sheet1.
2. In cell A1 type:
Master. In cells A3 through F3 type these column headers:
3. In cells A4 through A13 type the sales ID numbers—in this case,
110. The Sales ID is the unique data value that’s used to create a relationship between your two tables.
4. Enter names, addresses, cities, states, and zip codes in the remaining cells. You can copy the information from this sample worksheet or create your own data. Since we are looking for the highest sales by city, be sure to create multiple cities in your table. For example, we have three salespeople in Los Angeles, two in Hollywood, two in San Francisco, and three in San Diego.
5. Once the data is entered, highlight A3 through F13, including the column headers. From the Styles group, select Format as Table. From the dropdown, choose a color and format you like. A Format As Table dialog box appears with the table range displayed in the white box. Ensure that the My Table Has Headers box is checked, then click OK.
6. With the table still highlighted, select the Design tab under the text that says Table Tools (this option is available only when the table is highlighted). In the Properties group (far left), in the box under Table Name, type
Create the detail table
1. At the bottom of the screen beside the Master tab, click the '+' sign to insert a new sheet. Double-click the tab and type
Sales over the tag line Sheet2.
2. In cell A1, type
Total Sales for 2013. In cells A3 through E3, type
3. In cells A4 through A13 type the sales ID numbers:
4. In B4 through E13, enter 40 random numbers that represent sales dollars or copy the data from this example table.
5. Once the data is entered, highlight cells A3 through E13. From the Styles group, select Format as Table. From the dropdown, choose a color and format you like. A Format As Table dialog box appears with the table range displayed in the white box. Ensure that the My Table Has Headers box is checked, then click OK.
6. With the table still highlighted, select the Design tab under the text that says Table Tools (this option is only available when the table is highlighted). In the Properties group, in the box under Table Name, type
Set relationships in the pivot table report section
The first rule of pivot tables: You must define the table relationships within the Pivot Table report section. Do not attempt to create the relational connections first, because Excel will not recognize them from the Pivot Table reporting section. Also, be sure to select the detail table (Sales) for the “analyze data” table, otherwise it won’t work.
1. Go to the Sales table and highlight cells A1 through E11. Click the Insert tab, then click the Pivot Table button.
2. In the Create Pivot Table dialog box, ensure that the Select a Table or Range > Table Range field says "Sales." If you want to import a table/database from another program such as Word or Access, click the second option, Use an External Data Source.
3. In the second field—Choose Where You Want the Pivot Report placed—click New Worksheet if you want the table on a separate sheet by itself, or click Existing Worksheet if you want the report to drop in beside your Sales table.
4. And for the last field—Choose Whether You Want to Analyze Multiple Tables—click Add this Data to the Data Model, then click OK.
The Pivot Table menus appear with a Help box on the left that says “To build a report, choose fields from the Pivot Table field list.”
1. Under Pivot Table Fields, the Active button is selected because only one table is currently active. Click the boxes Quarter1, Quarter2, Quarter3, and Quarter4 and some numbers appear in a grid on the left.
2. Click the All button, then click the Master table link. The fields from the Master table appear. Click the box beside City. A yellow box appears that says “Relationships between tables may be needed.”
3. This is where you define the relationship between the two tables. Click the Create button and the Create