Wednesday, October 27, 2021

Power view in excel on office 365 or in sharepoint server

Important: 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, which you can download for free. You can also easily Import Excel workbooks into Power BI Desktop

When you create an Excel 2013 workbook with Power View sheets, you can save it to Microsoft 365 or to SharePoint Server 2013 on-premises. Workbook readers can view and interact with the Power View sheets in that workbook in either location.

Notes: 

  • You can only edit Power View sheets in Excel 2013 client.

  • You can save Excel workbooks with Power View sheets to Power BI. The Power View sheets become a report you can modify in Power BI

  • The rest of this article applies to Excel workbooks with Power View sheets in Microsoft 365, and not to the new Power BI experience. Try the new Power BI.

Interact with Power View on Microsoft 365 or in SharePoint Server 2013

You can interact with Excel workbooks on Microsoft 365 or on SharePoint Server 2013 in a variety of ways—filtering, sorting, and highlighting data in charts, slicers, and other visualizations.

Highlight and cross-filter a Power View sheet

  1. Click a workbook name on Microsoft 365 or on SharePoint Server 2013.

    The workbook opens in a browser window.

  2. Go to a Power View sheet in the workbook.

  3. Click a value in a visualization in the sheet.

Charts are interactive. Note that as you click values in one chart, it:

  • Highlights that value in that chart.

  • Filters to show only that value in every table, matrix, and set of tiles in the sheet.

  • Highlights only that value in all the other charts in the report.

Filter a Power View sheet

Workbook readers can see and interact with the filters in the Filters Area when viewing an Excel workbook (XLSX file) in the browser.

  1. Open an Excel workbook in a browser window.

  2. Go to a Power View sheet in the workbook.

  3. If the Filters Area isn't visible on the right side, on the Power View tab > Filters Area.

  4. In the Filters Area, click View.

    These are the filters for the whole sheet. If there are filters for View, then you can change them by adding or removing values or switching between basic and advanced filter modes. Note that you can't add a different field, and if there are no filters for View, you can't add them.

  5. Select a visualization on the Power View sheet and in the Filters Area, click Chart (or Table or other type of visualization).

  6. The fields in the visualization are always displayed in the Filters Area, even if they aren't filtered. You can create or modify filters for those fields. These filters affect only the values in the visualization you've selected.

Changes aren't saved

If you make changes to an Excel workbook (XLSX file) in a browser, those changes aren't saved in the file. The same is true for a Power View report (RDLX file) if you make changes in reading or full-screen modes. For example, if you filter or highlight in a visualization in Power View in those modes, those filters aren't saved with the workbook or report.

Top of Page

More in this article

Where Power View sheets are editable or interactive

Workbook is stored

Host configuration is

Workbook is opened in

Power View sheets are

On client computer

--

Excel 2013

Editable and interactive

On premises

In SharePoint view mode (SharePoint Server configured to render workbooks by using Excel Services)

Excel Services

Interactive

On premises

In Office Web Apps Server view mode (SharePoint Server configured to render workbooks by using Office Web Apps Server)

Excel for the web

Not visible

In Microsoft 365

SharePoint Online

Excel for the web

Interactive

On OneDrive

--

Excel for the web

Not visible

Top of Page

Power View in SharePoint view mode or Office Web Apps Server view mode

In an environment that has both SharePoint Server 2013 and Office Web Apps Server 2013, administrators choose whether workbooks are rendered in SharePoint view mode (by using Excel Services in SharePoint Server) or in Office Web Apps Server view mode (Excel for the web in Office Web Apps Server).

Power View in SharePoint view mode

When SharePoint is configured in view mode, then Power View sheets are visible in Excel workbooks in the browser. In Excel client, you can specify parts of the workbook you want other people to see in SharePoint view mode by hiding individual sheets. For example, you could hide all the other sheets in a workbook and leave only the Power View sheets visible.

Power View in Office Web Apps Server view mode

In a SharePoint environment that uses Office Web Apps Server to render workbooks, Power View is not supported.

Top of Page

Power View on Microsoft 365

You can store Excel workbooks that you want to share with others on Microsoft 365. They can view and interact with the Power View sheets in your workbooks on Microsoft 365. Depending on their permissions, they can also open and edit workbooks in Excel 2013 client. The whole workbook is visible on Microsoft 365. You can't select which sheets are visible.

You can't edit an Excel workbook containing Power View sheets in Microsoft 365. You can create a copy of the Excel workbook without the Power View sheets and edit that in Microsoft 365.

Workbook size limitations

You can upload files larger than 10 MB to a Microsoft 365 site. You can open them in Excel on your client machine from the site. You can't open a workbook larger than 10 MB in Microsoft 365.

To learn more, see File size limits for workbooks in SharePoint Online

Top of Page

Excel Web Access Web Parts

Excel Web Access Web Parts are available in Excel Services. Power View sheets are visible in Excel Web Access Web Parts in SharePoint Server and Microsoft 365.

Refresh data in an Excel workbook

You can manually refresh data in an Excel workbook on premises from many external sources, including SQL Azure databases with embedded credentials.

Important: You can't set automatic refresh in SharePoint Server on an Excel workbook that contains Power View sheets.

In an Excel workbook on Microsoft 365, you can't refresh data that comes from an external data source.

Read more about refreshing imported data.

Top of Page

Images in Power View

Images in a Power View sheet in an Excel workbook need to be stored in the workbook. Read more about images in Power View.

Top of Page

OneDrive

Power View sheets in Excel workbooks can't be viewed on OneDrive.

Business intelligence in Excel Services

In Business intelligence capabilities in Excel Services (SharePoint Server 2013), look for the sections about "reports."

Top of Page

No comments:

Post a Comment