Recently, I posted how one can use the Microsoft Excel WEEKDAY function to help clean-up and provide a tabular report on monthly Office 365 SharePoint Usage. This post is a follow-up where I’ll demonstrate how you actually create the visual component using Excel PivotTable and PivotChart. I’ll also show how you can then surface this report on a page in your Office 365 SharePoint Intranet using a Microsoft Excel Services web part.

There are some other useful pointers in here too, for example: how to change a PivotTable Sum to Average, how to format PivotTable numerical values, and how to alter the default PivotTable date options for row data.

For reference on how to capture and organize the SharePoint Usage Report data using WEEKDAY, see this post.

Microsoft Excel Table Data to PivotTable

As we saw in the previous post linked above, you can export Microsoft SharePoint Usage report data and strip out weekend values using the Microsoft Excel WEEKDAY function. When that’s done, you have something like this:

Creating a PivotTable from here is straightforward. Click any cell within the table, and then click the PivotTable button in the Insert tab of the Microsoft Excel ribbon.

The default Create PivotTable dialog should have acceptable options. Press OK:

You’ll be taken to a new worksheet and will have an empty PivotTable canvas to work with.

Configure the PivotTable Fields

To start, drag “ReportDate” to the “Rows” field area, and then drag “VisitedPageCount” to the “Values” field area:

For my report requirements, the default PivotTable behavior for handling date values and numerical values are not exactly as I need them. What I need is a report by month – I don’t want quarterly data even though I do deliver this report quarterly. I also don’t want the Sum of numerical data, but instead, I want the average.

Group PivotTable Dates to Show Year and Month

Select any set of content related to the dates, right click and then choose Group:

Now the data should look as follows, showing only Year and Month:

Change PivotTable Numerical Sum to Average

In the PivotTable Fields pane, click the drop-down for Sum of VisitedPageCount, choose Value Field Settings. In the subsequent dialog, change Summarize value field by to “Average”. You’ll now see your values as averages instead of count sum:

Convert PivotTable Decimal Values to Whole Numbers

These values will not read well for business users, and they’ll also chart poorly. We don’t need this level of significant digits, so it’s best to convert the decimal values to whole numbers. To do this, right click any one of the decimal values, and choose Number Format. Then, change the Decimal Places setting to “0”.

After this, we see easier to read whole number values, and we’re ready to create the PivotChart:

Create the PivotChart

You can be as creative as you want here. My business team wants an easy to read line chart…which really does work well for this quick-glance summary report.

Highlight your PivotTable, and then click PivotChart in the PivotTable Tools > Analyze section of the ribbon. Again, I’m going to go with a Line Chart:

I’ll change the title on the PivotChart, but as importantly, I need to hide the Field Buttons:

This is done in PivotChart Tools > Analyze > Show/Hide. Click “Field Buttons“, and they will all be hidden:

I usually do a few clean-up steps before I publish this to SharePoint in Office 365. Just to make things cleaner:

• Name your charts and tables.
• Name your tabs.
• Hide the data so it’s not so readily visible to users.

Etc…these kinds of things make a big difference in presentation.

Add the Report to SharePoint using Excel Web Parts

Actually, I’ve already written about this step in detail, including the use of video content. If you took something away from this post, I think this Contract Lifecycle post will be useful in terms of gaining even more knowledge on using Excel as well as finding a very clear demonstration on how to publish Microsoft Excel spreadsheets to SharePoint in Office 365, and then show them on a page with the Excel Services Web part.

Please read that post to continue, and note, if you just want to get to the SharePoint and Excel Services web part portion of the content…it’s at the end of that post.

Thanks for reading.

Categories: Business, Microsoft Excel, Office 365 and O365, SharePoint

Tags: Microsoft Excel, Office, Office 365 and O365, SharePoint