Click the Insert tab, then click the Pivot Table button.Ģ. Go to the Sales table and highlight cells A1 through E11. Also, be sure to select the detail table (Sales) for the “analyze data” table, otherwise it won’t work.ġ. Do not attempt to create the relational connections first, because Excel will not recognize them from the Pivot Table reporting section. The first rule of pivot tables: You must define the table relationships within the Pivot Table report section. Set relationships in the pivot table report section Highlight and name the detail (Sales) table. Open Excel and select a new, blank worksheet. Then, we’ll create a report (or pivot table) that shows which cities had the highest sales. The Sales ID is the relational field that connects the tables. At the end of each year, you need a report that provides the total yearly sales by person, but you also need a report that provides the total sales by city.įor this tutorial, we’ll create a master table with the salespersons’ information and a second table that provides their total sales, by quarter, for the year. Each sales person has 200 products that he/she sells (detail or child table). For example, you have 10 sales people who all have unique, demographic information (master table). To avoid repeating all the master information in every detail table, you create relationships using one unique field, such as the Sales ID, then let Excel do the rest. The data in the detail tables-such as daily sales, product prices, quantities-usually changes constantly. This is called a one-to-many relationship. For every record in the master table, there can be many records in the detail tables (also called slave or child tables) that link back to the master table.
0 kommentar(er)
