Creating a Basic Report in Excel 2010 using Slicers and Publish it to SharePoint

 

Using Slicers in Excel 2010

In previous versions of Excel, one of the most powerful and least understood functions is the Pivot Table and the Pivot Chart. Inside the finance department there usually lurk some very intelligent people who know how and why Pivot's are a good idea, but outside of this environment the knowledge appears to be scarce at best!

I recently started delivering a 2 minute overview of Pivot capabilities in Office 2007 to my customers through the Microsoft Buzz Day program which helps train end-users in our enterprise customers and it seemed to go down rather well across industries and across different user groups.

Slicers are new to Excel 2010 and basically make this functionality even easier to understand and also add a new slant on this technology. Having used popular internet shopping sites before, most people are fairly familiar with the process of "refining" a search using taxonomy or categories from the left of the page. Slicers help us do a similar thing in Excel!

Scenario:

Imagine that you have a whole bunch of sales line items, things like Sales reference number, Sales Person, Sale Amount, Product Area and others as in the example below. One of the most common requirements with this data is to create a single basic report, accessible by everyone, which would reflect on individual performance, or on Sales Manager performance, or Product Area performance as necessary. Below we can follow the steps to see how this could be performed using Pivot Tables and Charts, and made simpler by using Slicers.

Please Note: The better you format your initial data, the easier this is, so make sure that columns containing currencies, numbers, and other data types are correctly applied at this stage!

Step 1 - Create a Pivot Report to show my sales

Things have got a lot easier in Office in the latest releases, so doing this is simply a question of moving to the "Insert" tab and selecting "Pivot, Table" from the Ribbon bar. A box will pop-up asking you what range you would like and should pre-populate with the data range on your sheet. Simply Click "Ok" and you will reach the next screen.

Step 2 - Add the Pivot Fields from the Field List

Firstly, we would probably like to know how much we sold, so simply tick the "Amount" box in the Field list on the right and the system will figure out that these are values and assign it to the correct box in the bottom of the field list. This will give you a figure for the sum of the total sales. Then tick the "Sales Person" box and this again will figure out what corresponding area should be populated. This should give you a screen where you can see the sales attributed to each sales person. Next we might want to know what the breakout of our product sales were for each category, so in this case we would tick the "Product Area" box, and again it will find its way to the right area in the bottom of field list. Finally we might want to know which customer these sales were made to, and again it is as simple as putting a tick in the box and leaving Excel to figure out what you want...

The problem is that by now you are probably looking at a screen similar to the one below, which has run off the first page, and would require a training course for people to read and understand. We want the power, but not the complexity... and to be honest my sample data was only around 90 rows, in the "real world" there are usually hundreds, thousands or millions of rows to this stuff!

So what is the answer ?

Step 3 - Add a slicer to make the information easier to view and manipulate

Well a Slicer might be able to help you with this situation... now that we have our Pivot Table created, simply click the "Slicer" button on the "Insert" ribbon at the top of the screen and Excel will ask you what you want to slice.  Imagine these as category filters on the information that you want to see and you can make some informative choices around which Slicers to add. In this case we will select "Sales Person" and "Product Area" and then re-size the slicers on the screen to see them as shown below.

Now rather than having to go on a training course to understand what to do next, the person looking at the report can simply click on their name in the slicer to see the data relevant to them, and if they would like to narrow their sales down to a Product area, again can apply this filter simply by clicking the slicer button. To clear the filters and see the whole data set again there is a button in the top right of each of the Slicers to accomplish this.

If you have SharePoint 2010 available too:

One of the functions of SharePoint is that it allows us to take content from Microsoft Office Applications and make this widely available around the organisation. If I wanted to make this report available to my team I can simply publish this to my SharePoint Site.

Step 1 - Use the Backstage to upload and share with SharePoint 2010

The Backstage is a new area in Office 2010 Applications that allows us to easily view document properties and interact with the "Backend" systems. This means that we don't have to learn anything about these weird and wonderful IT systems, we just access them directly through the Office interface. In this example I will publish the Excel mini-report that we have just created to SharePoint so that others can see if and use it.

As you can see the Office 2010 backstage stores both the SharePoint locations that the IT people publish to me to use, and also the locations that I have recently visited which is handy, but I also have the option to "Browse for a Location" if the place I want to store this is not already here.

The newer types of Save As dialogue boxes in Windows 7 really help with all of this and list my SharePoint Sites, Libraries and other network areas in a logical and helpful fashion for easy access.

Another new thing that you will notice at this point is the "Open with Excel in the Browser" box. This basically allows other people using my report to be able to view, filter, and change the report without actually downloading it, or even opening Excel. This whole operation can be accomplished within the browser window when working with SharePoint.

Finally we will see the finished published version, viewed in the browser and available for anyone to use. As you will notice from the screen shot, the Slicers are available in the browser window for anyone to have a go with, and make the report very easy to see their own view on the world.

Step 2 - Telling people where it is

One of the most common reasons for people not using collaboration technologies like SharePoint is that it can be difficult to explain to people where things are published and getting them to look at relevant stuff! We can deal with this in a number of ways within the SharePoint environment, so lets see a couple of them here:

• 1. Sending a Link - When you look at your documents on SharePoint, you can drop the menu down on a particular item, and then select "Send To" and then "E-Mail a Link" which will populate a mail in Outlook to send this document to another person. This is simple and works.
• 2. Automatic Alerts - When you store similar and useful content in the same place you can place an "Alert Me" link on the document Library, simply visit the document library and click on the "Alert Me" button in the Library tab on SharePoint. This allows me to receive a digest of the changes automatically as shown below.

• 3. RSS Feeds in Internet Explorer - When you use a lot of reporting in the browser you might actually like to have these linked onto your Internet Explorer Favourites bar for quick and easy access. This uses a thing called RSS which is a common format, used in this case for notifications similar to the Alert Me functionality above. To do this, click the "RSS Feed" button on the library tab in SharePoint and then select "Subscribe to this Feed" in the page that appears. You should get a screen similar to below where you can check the "Add to Favourites Bar" box and then it will appear on your Favourites bar in Internet Explorer. Each time new content is posted the item in the favourites bar will turn Bold and you can click on it to see what this is.