Submitted by: Craig Brody, Microsoft Office Training/Consulting/Programming Instructor, Brody Associates

Excel’s Power Pivot and Power View commands provide powerful ways to see summaries of important data.  Imagine being able to see a mix of professionally formatted reports, charts and maps all linked together on one screen.   And to be able to click to see more detail of this outputs.

Let’s suppose you have entered thousands of records of customer product purchases in a database. One database table might contain each customer’s address and contact information while another table might list their transactions.

Now suppose you would like to summarize 10 years of this data in an Excel sheet and distribute it to your management team over your Intranet.  One part of the sheet might display a small report that totals sales in different states.   Another area of the same sheet might show a map of the United States with circles indicating total sales.  Still another area might compare state-by-state total sales in a bar chart.

The first step would be to use Excel’s Power Pivot tool to import the tables into an Excel file and then link the two tables together.   You establish a relationship between tables by selecting a common field in both tables, such as Customer ID.   Doing this allows you to pull relevant cross-referenced information from both tables.

The second step would be to insert a Power View sheet into your Excel file. (Found under the Insert tab in Excel). To insert a summary report, chart and map involves just checking off categories or fields from the related tables.   Therefore, by checking Customer Name and Sales fields you show a product sales summary report for each customer.   You can then add another level such as product category to show total sales per customer per category.   Then you select another area of the sheet and check off Customer Name, State, and Sales fields to add a second report.  Once the second report appears, just switch the output to a chart using a design option.  Finally select a third area of the sheet and choose Customer, State and Sales fields again switching the output display to a map that would display circles in each state indicating state sales totals.

Add to this mix powerful filter buttons.  You might want to set up tabs for each product category.   Just click the relevant category tab to change report, chart and map simultaneously.    Then add formatting commands to make it more visually attractive.

Excel is an incredible business tool.   Please contact the author if you would like to receive a sample Power View output along with more specific procedures.  Or to just learn more about what Excel can do to increase your productivity!

===================================

Brody Associates
Microsoft Office Training/Consulting/Programming
cell: 267-872-7410
email: cbrody@outlook.com
web: www.cbrodyassociates.com
linkedin: https://www.linkedin.com/in/craigbrody