 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 Relationship dialog box appears. Under Table, click the down arrow and choose Sales from the available tables list. Under Column (Foreign), click the arrow and choose Sales ID from the field list.
4. Remember the Sales ID is the only field that’s in both tables. Under Related Table, choose Master and under Related Column (Primary), choose Sales ID again, then click OK.
Excel makes the connection, then displays the report on the screen: Total Sales by City. Enter a report title in A1, and it’s complete.
Sort, create filters, and select data by other fields
Filters are used to select specific data by fields. To filter the data by city, click anywhere inside the table, then click the city field—notice the small arrow on the right.
1. Click the arrow and the Sort-Filter Options dialog box pops up with selections for Filters and Sorting. If you want to Sort, click Sort A to Z or Sort Z to A, or see the graphic below for the options under Sort More Options.
The Filter options include Label Filters, Value Filters, and Search (or select specified records in the current search field). If you have a huge database with hundreds of records, you can enter a city name (or partial name) in the Search box, then click the hour glass to locate the specified record/city. Excel displays the city in the list below the Search box.
2. If your database is relatively small, first uncheck the Select All button, then scroll down to the city you want, click the box, then click OK. The report now shows total sales for each quarter in that city only.
Label Filters and Value Filters are additional filtering options to help you refine your search. For example, in the Label Filters, if you choose all cities that Begin With “S,” you get San Diego and San Francisco. If you choose all cities Less Than “S,” you get Hollywood and Los Angeles. Numeric fields are filtered the same way most all other databases do it—Less Than, Greater Than, Equals, Between, etc.
3. You can also select a different field and quickly create a new report. For example, if y