Slicers

In the first article we looked at the Filters area. Fields added to the Filter area allow the user to select individual records or multiple records which are used to filter the visualisation objects in the main Power View pane. It’s also possible to turn a simple table within the Power View main pane into a Slicer. We can drag a text or date field from the Field list into the Power View pane and it will become a simple table. We can then go to the Design ribbon and choose Slicer to change the table into a Slicer. A Power View Slicer is not quite the awesome beast that is a PivotTable Slicer. You can only select with clicks and Control+clicks rather than being able to user drags and click and Shiftclick:

Sorting

One of the most effective changes to make to a chart is often just to sort it in order of amount. When a chart is selected in the Power View pane, sorting options appear above the top left-hand corner. You can select which field to sort by and whether to sort in Ascending or Descending order:

Table views

Depending on the fields included in a Table, different presentations can be chosen from the Tables dropdown. The Card view can be sorted in the same way as a chart

For Tables with different fields it is possible to choose how you can drill down to the different levels:

Play time

As good as the new Timeline Slicer is in Excel 2013, it does lack one feature that would have make it even more powerful – you can’t ‘play’ a Timeline to animate a chart based on a PivotTable in a similar way to the famous Hans Rosling bubble charts. However, there is just such a feature hidden in the depths of Power View. If you select a Scatter chart from the Other Charts dropdown in the Switch Visualisation group of the Design ribbon, the chart field list includes a Play Axis option. A field placed there can be ‘played’ to run through the records in the field one by one. The obvious type of field to use as the Play Axis would be a date field so that playing it would show how the bubbles move over time. For such a chart to make much sense, you need to choose the components carefully. A cumulative value makes for a good X Axis as the bubbles will move from left to right as time passes rather than jiggling about confusingly. You can also choose a Y Axis value and a value for the size of each bubble as well as displaying bubbles coloured to reflect different records in a field, in our case the different Salespeople:

As well as using the Play button to work through the records automatically, you can drag the slider to move to a particular position manually.

Other Office tips

Simon's book, Essential Excel for Accountants (and others), is now available as a PDF: www.tkb.co.uk/towiee.htm (the hard copy version was formerly known as Maximising the Impact of Accounting and Financial Spreadsheets for Finance Users). Simon has also included many useful Excel tips in the ‘Lunchtime Learning’ section of his website: www.tkb.co.uk/lunchlearn.htm .