Here’s the scenario: you’ve created a lovely pivot table report in Excel which contains a slicer allowing users in your organization to filter based on their username. You want to publish and share this Excel report to SharePoint but wouldn’t it be great if the report slicer defaulted to the current user in SharePoint? I’m pleased to tell you this is possible using the Excel web part and the current user filter web part (in both SharePoint 2013 and Online).

Create the Excel Workbook

First we need to create some data to play with. I’m going to create a sample project management dashboard with a project manager slicer. When project managers browse to the report it should default to the project management dashboard showing their projects. Here’s my test data that I’ve added to a worksheet in Excel and formatted as a table called “ProjectData”:

Note that I have a “Project Manager User ID” column in my sample data that contains the username for each Project Manager. I’m now going to insert a slicer on the Project Manager User ID column that we can use to filter the rows in the table. I click anywhere in my table and then on the DESIGN tab under TABLE TOOLS I click the “Insert Slicer” button.

On the “Insert Slicers” dialog I’m going to choose my Project Manager User ID column.

You can now try out the slicer to make sure it’s filtering the table as expected.

We’re almost ready to upload our Excel document but before we do we need to head to the File tab (in Excel 2013 at least) and click the “Browser View Options”.

In the Browser View Options click the “Parameters” tab and then click the “Add…” button.

Make sure you select the slicer containing user ids and then click the OK button.

Now you can go ahead, save and upload the document to your favourite SharePoint document library.

SharePoint

The first thing you need to do in SharePoint is create a new page (or edit an existing one) and add the Excel Web Part.

Configure the web part to show your Excel document. At this point you might like to save your new page to make sure it shows your Excel document in the web part.

Next, add a Current User Filter to the page.

It’s worthwhile opening up the properties of the Current User Filter at this point.

You’ll notice that it’s really helpful by showing you an example of the current user name. Also, whilst we’re here it’s interesting to see that you are also able to filter on SharePoint current user profile values and there is a long list of these available in the drop down.

Now, all that’s left to do is to link the Current User Filter to the Excel Web Part. In the Current User Filter web part menu select Connections -> Send Filter Values To -> Excel Web Access (you may need to open the Current User Filter properties in order to see the web part menu).

In the dialog select “Get Filter Values From” in the Connection Type then click “Configure”.

Then select your filtered parameter. In my case it’s the Project Manager User ID slicer.

Save and close the page and if all’s well you should see your Excel pivot table automatically filtered based on the current user.

As you can see, my Project Management Dashboard has filtered automatically to show my projects.