Office 365

3 Ways to Connect to the Office 365 Reporting Service from Excel

In Generate Office 365 Reports using PowerShell on the Petri IT Knowledgebase, I gave you a quick overview of the reporting capabilities in the Office 365 web portal, along with steps on how to run the same pre-defined reports using Windows PowerShell.

In today’s Ask the Admin, I’ll show you one more option for viewing Office 365 reports using Microsoft Excel, which is a good alternative for organizations that don’t want to write PowerShell code, but need to have a solution that’s a little more flexible than what’s available in the portal.

Using the Office 365 Reporting Service

There are three ways to tap into the Office 365 reporting service: using the Office 365 portal, Windows PowerShell or another language, and Microsoft Excel. Keep in mind that each method has its own shortcomings.

Connect to the Office 365 Reporting Service from Excel
An Office 365 report as a PivotTable in Excel (Image Credit: Russell Smith)

Office 365 Portal

Using the pre-defined reports in the web portal is the quickest and easiest way to get started with Office 365 reporting, but there are some significant limitations:

Sponsored Content

What is “Inside Microsoft Teams”?

“Inside Microsoft Teams” is a webcast series, now in Season 4 for IT pros hosted by Microsoft Product Manager, Stephen Rose. Stephen & his guests comprised of customers, partners, and real-world experts share best practices of planning, deploying, adopting, managing, and securing Teams. You can watch any episode at your convenience, find resources, blogs, reviews of accessories certified for Teams, bonus clips, and information regarding upcoming live broadcasts. Our next episode, “Polaris Inc., and Microsoft Teams- Reinventing how we work and play” will be airing on Oct. 28th from 10-11am PST.

  • Not all available information can be returned in the report results.
  • Some pre-defined reports are only available using PowerShell.
  • You can’t save the results.
  • There are only two options for viewing reports: either as a graph or table.

Windows PowerShell

The most flexible way to generate reports is using PowerShell, and it can be used to create custom reports by connecting to the Office 365 reporting service, allowing administrators to determine the data that’s returned, how it’s grouped and sorted, and also the ability to save reports.

Microsoft Excel

The results of the available pre-defined results can be viewed in Excel, by making a connection to the Office 365 reporting service. Custom reports cannot be accessed this way, and the amount of data returned can’t be customized, grouped, filtered or sorted before the results are returned. But Excel can be used to manipulate the results, giving administrators more flexibility than viewing reports in the Office 365 portal.

Connect to the Office 365 Reporting Service from Excel

To get started, you need Microsoft Excel installed on your local management PC, and an Office 365 subscription. Don’t forget that reporting is not available in all plans, so check out Microsoft’s website to see if reporting is available in your subscription. Additionally, not all reports are available in all plans.

Data feed URL, username and password (Image Credit: Russell Smith)
Data feed URL, username and password (Image Credit: Russell Smith)
  • Open Excel, and click Blank workbook to open a new workbook.
  • Switch to the Data tab on the ribbon at the top of the worksheet.
  • Click Get External Data at the far left of the Office ribbon, and then click From Other Sources.
  • Select From OData Feed from the drop-down menu.
  • In the Data Connection Wizard dialog box, type in the Link or File box under Location of the data feed.
  • Under Log on credentials, select Use this name and password.
  • Enter the credentials of an Office 365 user with permission to generate reports, and click Next.

If you are authenticated successfully, you’ll be presented with the Select Tables page, where each table on this page represents a report.

Select a report to display in Excel (Image Credit: Russell Smith)
Select a report to display in Excel (Image Credit: Russell Smith)
  • Select the table that you’d like to import and click Next.
  • On the Save Data Connection File and Finish page of the wizard, click Finish.

Alternatively, you can change the location of the data connection file, which is saved by default in the My Data Sources folder. It’s also possible to save the password for the data feed in the file, by checking Save password in file.

  • In the Import Data dialog, select PivotTable Report, and then click OK.
  • Once the data has been imported, in the PivotTable Fields panel on the right side of Excel select the fields to display.

Now that the data has been imported into Excel, you can manipulate it as required.

Related Topics:


Don't have a login but want to join the conversation? Sign up for a Petri Account

Comments (0)

Leave a Reply

IT consultant, Contributing Editor @PetriFeed, and trainer @Pluralsight. All about Microsoft, Office 365, Azure, and Windows Server.
External Sharing and Guest User Access in Microsoft 365 and Teams

This eBook will dive into policy considerations you need to make when creating and managing guest user access to your Teams network, as well as the different layers of guest access and the common challenges that accompany a more complicated Microsoft 365 infrastructure.

You will learn:

  • Who should be allowed to be invited as a guest?
  • What type of guests should be able to access files in SharePoint and OneDrive?
  • How should guests be offboarded?
  • How should you determine who has access to sensitive information in your environment?

Sponsored by:

Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: