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!
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!
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.
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 ?
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.
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.
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.
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:
Next time, please skip all the nonsense and get right to the point.
Great post, just what I needed to start my reporting!
THAT IS A GREAT JOB . I USED IT WHEN I WAS AT WORK AND IT REALLY HELPED .
The filter icon in your slicer is not displayed correctly. Notice that when the filter is cleared, the icon remains the same.
Wonderful. Its been a great help