To see the written instructions, and more Slicer videos, please visit the Excel Pivot Table Slicers page on my Contextures website. Watch this video to see how to update the files, connect to Slicers, and repair a problem pivot table so it works with the Slicers. See the Steps for Fixing Problem Pivot Tables Try it on a copy of your file, and you might be able to avoid starting from scratch. There are a few steps to the process, but it doesn’t take too long. During the process, Excel makes minor repairs to the pivot table, and it is able to connect to a Slicer. I’ve had some luck with saving the file back to the old format, then re-saving it in the new format. To fix the problem, you can create a new pivot table, or try to repair the old one. Sometime I find a pivot table that isn’t recognized by the Slicers, even after converting to the new format. Then, close and reopen the file, and you should be able to connect any existing pivot table to a Slicer.Open the older files, and convert the files to the newer Excel formats - xlsx, xlsb or xlsm.The window displays data in a worksheet format. The command opens the Power Pivot for Excel window, as shown in Figure 12. Next, select the Add to Data Model command in the Tables group on the Power Pivot tab. It’s easy to create pivot tables in Excel 2010, and connect them to Slicers, but if you want to use Excel 2003 files, you’ll have to update them first. Open the Salesperson worksheet and click anywhere inside the Salesperson table. In the Product Slicer, the bars are shown at the top, and the other products are listed below the bars. In the screen shot below, “Bars” is selected in the product type 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.Īs you can see, my Project Management Dashboard has filtered automatically to show my projects.Slicers were introduced in Excel 2010, and they make it easy to filter one or more pivot tables with a single click. In my case it’s the Project Manager User ID slicer. In the dialog select “Get Filter Values From” in the Connection Type then click “Configure”. 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). Now, all that’s left to do is to link the Current User Filter to the Excel Web Part. 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. You’ll notice that it’s really helpful by showing you an example of the current user name. It’s worthwhile opening up the properties of the Current User Filter at this point. Next, add a Current User Filter to the page. At this point you might like to save your new page to make sure it shows your Excel document in the web part. 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.Ĭonfigure the web part to show your Excel document. Now you can go ahead, save and upload the document to your favourite SharePoint document library. Make sure you select the slicer containing user ids and then click the OK button. In the Browser View Options click the “Parameters” tab and then click the “Add…” button. 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”. You can now try out the slicer to make sure it’s filtering the table as expected. On the “Insert Slicers” dialog I’m going to choose my Project Manager User ID column. I click anywhere in my table and then on the DESIGN tab under TABLE TOOLS I click the “Insert Slicer” button. 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. Note that I have a “Project Manager User ID” column in my sample data that contains the username for each Project Manager. Here’s my test data that I’ve added to a worksheet in Excel and formatted as a table called “ProjectData”: When project managers browse to the report it should default to the project management dashboard showing their projects. I’m going to create a sample project management dashboard with a project manager slicer. Create the Excel Workbookįirst we need to create some data to play with. 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). 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |