How to customize a Microsoft Office 365 license cost report

How to customize a Microsoft Office 365 license cost report

When budget time comes around, IT admins may be asked to create a report that shows the Microsoft Office 365 license cost to the organization. Depending on the organization, this may need to be reported by department, per user or by geographic region which will result in some customization.  

The challenge facing IT admins is that the Microsoft Admin Center provides a list of user accounts and other related information, but the data must be filtered to create a report that is categorized the way you want it. The Admin Center is unable to export a filtered view so you will have to export your unfiltered data to Excel.  

Once you get your data into an Excel spreadsheet with the Microsoft Office 365 license costs, they will need to be manually filtered and reconciled in Excel which will take some Excel expertise. Another alternative would be to use PowerShell, but again, that requires a level of expertise that not all Microsoft IT admins possess. A final alternative is to use a third-party software tool to get the job done. Let’s look at each method in more detail. 

Build a report from the Admin center

Unfortunately, as mentioned above, there is no direct and easy way to export a list of users with assigned licenses from the main Microsoft 365 Admin Center.

Although a filter exists to view licensed users, exporting that view is not available. As the image below shows, filters must be removed before exporting. You’ll need to manually filter after exporting your information to a csv file.

The Microsoft 365 Admin Center does not allow exporting view of unlicensed user accounts

Similarly, in the Entra Admin Center, you can export all the data, or you are limited to exporting users by each assigned license. This filter does not offer the ability to filter out unlicensed user accounts. Using more than one license assignment filter results in combining them in an “AND” operation instead of an “OR” operation.

Filtering license assignments is available only per license in the Microsoft Entra Admin Center

In both cases, you end up with a csv file which you must convert to Excel before adding reference formulas for license costs and organizing your data (you’ll also need to clean up unnecessary properties that were exported by default).

This is a very tedious process that can introduce errors due to manual data reconciliation.

Export a Microsoft Office 365 license cost report with Microsoft Graph PowerShell

Getting a list of users, their properties and their licenses assigned is straightforward with Microsoft Graph PowerShell.

For example, this cmdlet gets a list of users with specified properties display name, department and assigned licenses:

Get-MgUser -Filter ‘assignedLicenses/$count ne 0’ -ConsistencyLevel eventual -CountVariable licensedUserCount -All -Select DisplayName,department,AssignedLicenses | Format-Table -Property DisplayName,department,AssignedLicenses

There are more examples on this page.

PowerShell cmdlet retrieving a list of users, their department and their assigned Microsoft 365 licenses

Note however that only license SKUs are returned instead of the names of the licenses, which means you’ll need to translate these for your report before you can assign an Office 365 license cost.

To include the equivalent license names and license costs for each user or department, you’ll need a more complex script. And just like exporting from the admin portal, the csv results may require further processing to be report-worthy.

Create a custom report of Microsoft Office 365 license costs by department in sapio365

Using a third-party software tool like sapio365 can save you a lot of time and effort. With sapio365, you avoid all pagination issues or the need to aggregate information. It gives you a full and extensive view of all your data in one place. With this information at your fingertips, you can then filter, sort and organize the data easily with a few clicks. Unlike PowerShell, you can preview any changes before you commit to them so the risk of unexpected errors is eliminated.

The first step is to associate a cost to each type of Microsoft Office 365 license in your environment. This is done by retrieving a list of purchased licenses by clicking Licenses and Services on the main page of sapio365.

Then you can select the licenses for which you want to set the unit cost, or toggle ‘List all purchased licenses’ to True. Click on the pencil icon to set the cost.

Set a unit cost for each Microsoft 365 license in sapio365

You should be aware that if you manage several tenants, the amounts you set are specific to each tenant.

Next, click on All Users in the sapio365 homepage, and you’ll see the complete list of user accounts, the Microsoft Office 365 licenses they have assigned and the unit cost for each.

Get a complete list of users in sapio365

Add the department or any other user property to the view.

Adding more user properties to the view in sapio365

Next, filter out the unlicensed accounts and then group the data by department by dragging the column header to the top left of the grid, then by the user.

Filtering out unlicensed user accounts and categorizing them by department

Next, you will add license totals for each category by selecting ‘Sum’ under Totals.

You can collapse all the categories, or you can expand only 1 level to include the license costs per user for a more detailed report.

Adding a sum for license costs for each category

Now you can export the grid view to an Excel file.

Export sapio365 grid view to an Excel file

sapio365 also allows you to save this grid view to apply it to your data at a later time, or you can schedule a report from it using one of sapio365’s many automated jobs. This will save you time further down the road if you think you will need to produce this report on a regular basis. Once you have your report, you may want to read more about how to audit Microsoft Office 365 license assignments here.

As you can see, there are a few ways to get your Microsoft Office 365 license cost report, but not all of them are easy, and some will take longer than others. sapio365 is a great software tool for customized reporting that is quick and easy to use, and the bonus is that you can use it to accomplish many other tasks just as quickly and easily. You can read how one customer saved a lot of money on licenses by identifying inactive and under-utilized licenses here.

Discover how using sapio365 to handle typical Microsoft 365 administration can save you hours of time every single day.

Eric Houvenaghel

Eric’s passion is solving problems. It’s why he founded his company Ytria – so he could solve problems people had that they didn't even realize they had. With over 2 decades of experience as a software developer, Eric honed his expertise by crafting innovative solutions tailored to the needs of IT teams in both Microsoft 365 and Notes/Domino environments.

How to audit Microsoft office 365 license assignments

How to audit Microsoft Office 365 license assignments

When it’s time to do Microsoft Office 365 license reporting, some IT admins may be asked to find…

How to create an Office 365 login report

How to create a Microsoft Office 365 Login Report

As an IT admin, you may have to create a Microsoft Office 354 login report to find out which user…

How to find users with an SMTP forwarding email

How to List All Users with a Configured SMTP Forwarding Email

Have you ever had to find all the users who have configured SMTP forwarding emails only to realize…

microsoft teams usage report

How to See Microsoft Teams Usage Report on Frequency by Department

When companies buy Microsoft Teams licenses, they want to make sure that the licenses are being put…