Excel Power View

What is Power View for Excel?

Power View for Excel is a data visualization and reporting tool that allows users to create interactive reports, charts, graphs, and other visualizations to explore and analyze data. It is a part of Microsoft’s business intelligence suite and requires Excel 2013 or later versions.

There are three data analysis tools available in Excel which include Power Pivot, Power Query, and Power View. Power View is an add-in in Excel, and we must enable the add-in to use it.

How to Enable Power View for Excel?

Power View is available as an add-in for Excel. To enable Power View for Excel, users must ensure that they have the Excel 2013 version and that the Power View add-in is enabled. To do this, users need to go to the File menu and select Options.

Step 1: Click on Add-Ins, select the COM Add-Ins option available under the Manage dropdown, and click on the Go button.

Step 2: After selecting Microsoft Power View for Excel, users can enable the add-in option applicable to Power View for Excel.

Step 3: Now users will be able to see the Power View under the Insert tab

Enabling Power View

However, there may be a few scenarios (For example, Excel 2016), where users may not see the Power View option despite enabling it through an add-in option. To enable Power View in such scenarios, follow the below steps.

Step 1: Navigate to File -> Options.

Step 2: Select Customize Ribbon, expand the Insert option under Main Tabs, and click on the New Group button.

Step 3: Select All Commands from the drop-down under Choose commands from option, click on Add >> button, and then click on OK. Ensure “New Group (custom)” is selected while adding the Power View.

Step 4: Navigate to the Insert tab. You will see the Power View option in the New Group section.

Note: Excel Power View is unavailable as a separate download or installation. It is only included as an add-in with certain versions of Excel, such as Excel 2013 or SharePoint 2010/2013.

In subsequent sections, we will see how you can use Power View to create visualizations.

How To Use Power View in Excel?

To use Power View in Excel, you must first ensure that the Excel Power View add-in is enabled, as mentioned in the previous section. You also need a data model in your workbook, which can be created using the Power Pivot add-in. Once the data model is set up, select the Power View button on the Insert tab and build their visualizations.

The steps required to use the Power View in Excel are provided below. For the example below, we have referenced Sales data across various categories of products across different US cities.

Step 1: Navigate to the Insert tab and select the Table option.

Step 2: Select the data range, check the box “My table has headers.”

It would ensure the dataset is formatted into an Excel table and ready for use in Power View in Excel.

Step 3: Select the Power View option under New Group available under the Insert tab.

It shall open a new sheet within the workbook where you would see a table on the left side, a filter pane, and a Power View Fields pane on the right.

You can provide an appropriate title to the view and include the fields as per the analysis or view.

Users can also create a Power View report in Excel. This option provides users to select the type of visualizations, such as tables, charts, maps, etc., they want to opt for using the Power View interface. Users can select the specific data fields from the Power View Fields to create a complete Excel Power View report. Now, this report can be further shared with others by email or through a shared location.

How to Create a Power View Dashboard?

To create a Power View dashboard, users must create multiple visualizations and arrange them on a single sheet. Later, they can add filters and slicers in excel to allow users to interact with the data and customize the layout and design to create a more impactful Excel Power View dashboard.

Let’s understand this concept with the help of examples provided below.

Examples

Example #1 – Sales Dashboard with Charts and Table in Power View

In this example, we will create a sales dashboard using various parameters and segments.

Sales Dashboard as a table

Step 1: Select the applicable dataset and click on the Table option under the DESIGN tab.

Step 2: Select the “Category” and “Sales” fields from the Power View Fields pane to the canvas.

Sales Dashboard as a Column Chart

Step 1: Select the Column Chart option under Switch Visualization in the DESIGN tab.

Step 2: Select the “Category” and “Sales” fields from the Power View Fields pane to the canvas.

Sales Dashboard as a Bar Chart

Step 1: Select the Bar Chart excel option under Switch Visualization in the DESIGN tab.

Step 2: Select the “States” and “Sales” fields from the Power View Fields pane to the canvas. Then, you can also refine the result by using the Sort-by feature.

Sales Dashboard as a Pie Chart

Step 1: Select the Pie Chart in excel from the Other Chart option under Switch Visualization in the DESIGN tab.

Step 2: Select “Segments and “Sales” fields from the Power View Fields pane to the canvas.

Step 3: Add the title to the report as “Sales dashboard,” and the Power View Report Layout will look as shown below.

Example #2 – Chart Visualization for Sales Data

In this example, we shall create various chart visualization in Power View using the Sales data for various periods.

Step 1: Navigate to the Data tab on the Excel ribbon, import the sales data using Get Data – From File – From Excel Workbook or directly using From Text/CSV option.

Step 2: Navigate to the Insert tab on the Excel ribbon and select the Power View option

A Power View report shall be loaded and visible in the same worksheet as shown below.

Step 3: To create a scatter chart, select the Scatter option from the Other Chart drop-down menu, as shown below.

It would create a scatter chart highlighting the Sales across the months through various quarters.

Step 4: To create a line chart in excel on this data, select the Line option from the Other Chart drop-down menu as shown below.

As shown below, it would create a line chart highlighting quarter by month across the dataset.

Step 5: To create a bar chart, select the Clustered Bar option from the Bar Chart drop-down menu, as shown below.

It would create a Bar Chart highlighting the Quarter by Month across the dataset.

Step 6: To create a Column Chart, select the Column Chart option. It would create a column chart highlighting the Quarter by Month across the dataset as shown below.

Important Things to Note

• In Excel for Microsoft 365, and Excel 2021, Power View is removed on October 12, 2021. As an alternative, you can use the interactive visual experience provided by Power BI Desktop (in the Microsoft site), which you can Excel Power View download for free. You can also easily Import Excel workbooks into Power BI Desktop from the Microsoft site.
• To enhance performance, Power View retrieves only those records at a given point in time that are visible in a table even if the table contains millions of underlying records.
• Power View is supported in Microsoft SharePoint Server 2010 and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services Add-in for Microsoft SharePoint Server Enterprise Edition
• Similarly, for Excel 2016, Power View is no more available in the COM Add-ins option and hence Excel 2016 users may not be able to use Power View anymore.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Excel Power View. Here we explain how to enable, use and create a power view dashboard with examples. You can learn more from the following articles –

• Power BI Share Reports
• Power BI Features
• Power BI Dax