Dr. Mark Gardener


Managing Data Using Exce, Cover

Managing Data Using Excel

Writer's Bloc

On this page you can find out about my latest writing project. I'll post updates on progress, tables of contents and also some of the R scripts (and possibly Excel spreadsheets) I am developing in support of the new book. I'll try to keep the material reasonably up to date.

The Writer's Bloc homepage contains a table of contents and an index of the pages that contain custom R commands and R scripts.

Managing Data Using Excel:
Organizing, Summarizing and Visualizing Scientific Data

Available now from
Pelagic Publishing

Pelagic Publishing

Get £5 discount when you buy direct from the publisher. Enter voucher code MDUE20 in the shopping basket at Pelagic Publishing

Exploratory Tools:

Pivot Table Slicers

Pivot Tables are a great way to explore your data. The pivot table allows you to arrange your data quickly and efficiently in many different ways, and in doing so you can gain a better understanding of your dataset.

You can use pivot tables in various different ways:

  • Drag and drop fields to different locations.
  • Use Filter tools (drag items to the Report Filter box).

The Slicer tool is a way of using filters to manage a pivot table in a convenient and interactive way. Once you have a pivot table you can create a slicer from the PivotTable Tools > Options menu. You can also use the Insert menu but you have to have clicked on the pivot table first, so you might as well use the Options menu!

Insert Slicer Tools from the Pivot Table Tools Menu in Excel 2010 or Excel 2013


Making and using Slicers

The general process of making and using a slicer is as follows:

Make a pivot table. You do not have to incorporate all the fields into the table in order to use a slicer but the variables do have to be in the PivotTable Field List.

Click the pivot table to activate the PivotTable Tools menus; this opens the Insert Slicers dialog box, where you can choose the fields to use for the Slicer (Figure 6.10).

Insert Slicers Tool Menu
Figure 6.10 The Insert Slicers dialog allows you to select one or more fields from a pivot table to use as filters.

Multiple Slicers can be cascaded to fit on screen.

You can arrange Slicer windows as you like using the mouse.


Use the check boxes to select the fields you want, and then click the OK button. The filters appear as boxes in the worksheet (Figure 6.11). If you selected more than one field the boxes are cascaded.

Multiple Slicer fields are cascaded
Figure 6.11: Multiple slicers appear cascaded on screen. You can move and edit each window independently.

Slicer Tool windows can be customized.


Edit your Slicers

When you click on a slicer window the Slicer Tools menu is activated; this gives you some editing options. The most useful ones are in the Arrange and Buttons sections (Figure 6.12).

The Slicer Tools menu
Figure 6.12: The Slicer Tools menu includes tools that allow you to alter how the slicer windows appear.

Slicer tool windows can be arranged on screen using the mouse.


Arrange your Slicers

Use the buttons in the Arrange section to reorder the windows; you can also simply drag the windows to convenient locations on screen.

Use the Columns button in the Buttons section to set how many field buttons are displayed in the selected slicer window. The default is for one button across; setting to a larger value usually makes it easier to work with the windows (Figure 6.13).

Use the mouse to resize and position the windows (Figure 6.13).

Slicer windows resized and arranged
Figure 6.13: You can resize and arrange slicer windows with the mouse. Multiple columns can make the windows easier to manage.

Click the Slicer buttons to apply filters quickly and simply.


Activate Slicers to filter your data

Click the buttons in the slicer windows to create the filters. By default all items are selected; the first time you click a field it is selected and the others deselected (they will appear lighter, depending on your colour scheme).

Select multiple items using the Ctrl and Shift keys in conjunction with the mouse. This works in the standard Windows way: the Ctrl key selects (or deselects a single item), while the Shift key selects that item and everything between it and the previously selected item.

Clear the slicer from a window using the funnel with a red cross icon.

Delete the slicers you don’t want by clicking on one and using the Delete key on the keyboard.

Slicer tools are easier to manage than multiple report filters.


You can right-click on a slicer window to open a popup menu giving access to various options. The Slicer tool is useful, especially if you have a large and complicated dataset. It is a bit easier to manage your slicers than using multiple report filters (which is essentially what a slicer is).

A Time Slicer is available in Excel 2013


Time Slicer button

If you use Excel 2013 you can use the Time Slicer button. This looks for columns formatted with a date format (day, month and year), and allows you to view chunks of your data based on the date. If you’ve only got the year you’ll need to fool the system by creating a new variable DATE(yr_cell, 1, 1).


Providing training for:

  • Ecology
  • Data analysis
  • Statistics
  • R The statistical programming language
  • Data management
  • Data mining

Follow me...
Facebook Twitter Google+ Linkedin Amazon
Contact DataAnalytics Homepage