 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 you’d like to see the quarterly sales plus totals by sales person, uncheck City and check Sales Person. The report drops in.
4. Next, click Sales Person, click the down arrow, then uncheck Select All in the Sort-Filter Options dialog box. Click four names on the list, click OK, and the filtered report drops in.
The Pivot Tables/database options are endless. There are numerous ways to analyze the data, create and manage sets, group fields, insert slicers and timelines, drill up and down, and import and export data, as well as design reports with custom layouts and styles, create hundreds of colorful charts, then print it all out for distribution.
Charts and styles
To spice up your table before you print it, try adding a chart and/or some colors and style to the table.
To add a chart, highlight the table, select Pivot Table Tools > Analyze > Tools > Pivot Chart, then select a chart from the gallery of charts, and click OK.
To add colors and style, select Pivot Table Tools > Design > Pivot Table Styles and choose a table design from the gallery of styles. Click Banded Rows under the Pivot Table Style Options group to alternate colors and/or shading on the odd and even rows for easier viewing. And that’s all there is to it.
With this new relational database/table feature, this process is so easy that once it's set up in Excel, you can extract specific data and create dozens of reports in minutes.