Applies ToExcel for Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

One of the advantages of using the Power Pivot add-in to refine a Data Model is the ability to add perspectives. Perspectives provide custom views that you define for a particular user group or business scenario, making it easier to navigate large data sets.

You can include any combination of tables, columns, and measures (including KPIs) in a perspective, and you can create multiple perspectives for various reporting constituencies in your organization.

Perspectives can be used as a data source for other PivotTables and reports, including Power View reports. When you connect to a workbook that includes perspectives, you can choose a particular perspective on the Select Tables and Views page of the Data Connection Wizard.

  1. Make sure you have SharePoint with Excel Services. You’ll need SharePoint to host the workbook. Perspectives can only be used as a data source when the workbook is on a server.

  2. Start with a workbook that contains a Data Model and the Power Pivot add-in. For details, see Create a Data Model in Excel and Start the Power Pivot add-in for Excel.

  3. In the Power Pivot window, click Advanced > Create and Manage.

  4. In Perspectives, click New Perspective.

  5. Name the perspective, and then check the fields you want to include.

  6. Save the workbook to SharePoint.

    Notes: 

    • Always include at least one field; otherwise someone using the perspective will see an empty field list.

    • Choose only those fields that are useful in a report. You don’t need to select keys or columns for navigational or computational purposes. Excel can use the entire model, even if the perspective does not include specific elements.

    • To modify a perspective, check and uncheck fields in the perspective’s column, which adds and removes fields from the perspective

    • When you hover over any cell of a perspective, buttons appear that let you delete, rename, or copy the perspective.

See Also

Power Pivot: Powerful data analysis and data modeling in Excel

Power View: Explore, visualize, and present your data

Power View and Power Pivot videos

Top of Page

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.