Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Pivottable Slicers > Configuring Slicers

Configuring your Slicers

Slicer options on the ribbon

There is a host of settings you can apply to your slicers. The Ribbon shows most of them:

Excel 2010 ribbon for slicer editing
Ribbon (cut in two halves so it fits on this page) showing the slicer options tab.

The Slicer Tools contextual ribbon tab houses a number of groups to change your slicer's appearance and behavior. Let me discuss them here.

Slicer group

This group houses three important controls. You can change the name of a slicer here (Slicer Caption), change some settings controlling which items are listed in a slicer and how they are sorted and filtered (Slicer Settings). Also, you can set up which Pivottables connect to your slicer (I explain that below).

Slicer Styles Group

A quick way to format your slicers is by applying one of the built-in slicer styles. Like any style in Excel 2010, these adhere to the theme you've selected. This makes it easy to keep your workbook design tidy and consistent.

Like cell styles you can create your own slicer styles too, by right-clicking one of them and choosing "Duplicate", followed by right-clicking the newly duplicated style and choosing "Modify".

Buttons group

Just like you can easily change the colors of your slicers by applying a different style to the slicer it is simple to modify the button layout and appearance, just adjust the numbers in that group of the ribbon.

Buttons group
These three controls enable you to change the button layout and size

Size group

And last but not least a small group which lets you edit the button sizes.

Commands not in ribbon

Unfortunately, not everything you need is on the ribbon. The missing options are quickly accessible through the slicer's right-click menu however. Select the "Size and Properties" option to get there. Here you can control whether or not the slicer must be printed and should move/size with cells just like any other shape on a sheet. And you can turn off resizing and moving to prevent your users from messing with your layout.

Changing a Slicer's font size

If you've scurried through all slicer dialogs like I have, you'll have noticed there is no way to change the font size of a slicer.

Or is there? Of course there is, but is has been cunningly hidden!

The basic idea is that you must edit the slicer's style. But since you can only edit custom styles, the first step is to add a custom style. Pick one you like best and right-click it, then select Duplicate...:

Right-click a slicer style
Right-click a slicer style and select "Duplicate"

The second step is editing the new custom style. Right-click that style and select "Modify...":

Right-click a slicer style
Right-click a slicer style and select "Modify".

In the dialog that pops up, select "Whole Slicer" end then click the Format button:

The Modify slicer Quick style dialog
The Modify slicer Quick style dialog.

As you can see in the screenshot below, now you can edit the slicer's Font.

The Format Slicer Element dialog
Time to select a nicer font for your slicer!.

After you OK this dialog, probably nothing appears to happen to your slicer. Don't worry, all that is needed now is applying your new custom style to the slicer and you're done!

Controlling which pivots are handled by your slicers.

A very useful option of slicers is that you can tie them to more than one Pivottable and/or -chart.

The way to handle this is by selecting the slicer in question and then clicking the Pivottable Connections button in the Slicer Settings group on the Slicer Tools contextual ribbon tab:

the Pivottable Connections button
the Pivottable Connections button in the Slicer Settings group on the Slicer Tools contextual ribbon tab.

This brings up the following tiny dialog:

the Pivottable Connections dialog
the Pivottable Connections dialog.

As you can see, Excel has listed a couple of Pivottables in your file to tie the slicer to.

Luckily, Excel is smart enough to ensure that only Pivottables using the same cache as the selected slicer is tied to are in the list.

If you have more than one slicer on a particular field (e.g. a slicer on "City" on each sheet with a Pivottable), then you must take care when selecting which tables the slicer relates to. As soon as you check more than one check box, the slicers working on the checked Pivottables will be permanently tied together. The only way to make the slicers work independently again is by using the Undo button.

Next: Slicers and VBA


 


Comments

All comments about this page:


Comment by: Vishwanath Nayak (1/17/2011 11:22:11 PM)

Dear, I recently purchased a new Dell optiplex 380 with a MS excel non commercial use,, but i dont see this Slicers ribbon/options, tools in my excel while playing with pivots....How should i debug this ?

Regards,
Vn

 


Comment by: Jan Karel Pieterse (1/18/2011 1:04:14 AM)

Hi Vishwanath,

What Office version are you using? SLicers were introduced with Excel 2010 (=Office 2010) and are unavailable in Excel 2007 and older.

If you do have 2010, make sure you select a cell inside a pivot table so that the Pivot tools tab is visible on the ribbon. The slicer button is on that tab.

 


Comment by: Pradeep (2/10/2011 3:51:34 PM)

Hi,
I am using excel 2010 but can not find slicer option on ribbon. Evenif i select a cell in piot table it doesnot show slicer option in pivot table tab.

Do i have to download it as and addin.

Pradeep

 


Comment by: Jan Karel Pieterse (2/11/2011 3:59:08 AM)

Hi Pradeep,

It is located in the Sort & FIlter group, on the Options tab of the Pivottable Tools contextual tab of the ribbon. Note that this option is disabled if you are in a pivot table in a file that was saved in Excel 97/2003 format. It has to be a "normal" Excel 2007/2010 format workbook.

 


Comment by: Guy Brown (3/3/2011 9:52:15 AM)

Slicers are the best part of Excel 2010 - especially the ease with which one can connect multiple pivot tables with a single slicer.
Unfortunately there appears to be not backward compatibility with Excel 2007 at all (I'm surprised Microsoft have not come up with a "compatibility pack") - even in xlsm format.
Is there a way of maintaining the pivot connections when opening a "slicered" 2010 workbook in 2007? (though in 2007 I know the old style "check box" filters would have to be used)

 


Comment by: Jan Karel Pieterse (3/4/2011 2:03:47 AM)

Hi Guy,

Not sure what is supposed to happen. The information I had so far was that 2010 objects are ignored by Excel 2007, but still included in a save. So I was expecting them to carry over unscathed. Are you saying they don't?
If so, it may be useful to send me the workbook (the version before 2007 and after 2007 has touched it) so I can issue a bug report.

 


Comment by: Jason (4/29/2011 7:42:54 AM)

I think the question may be slightly misrepresenting the issue. If you have already created a slicer, the Slicers ribbon/options, tools is a contextual tab/ribbon option that shows when you select the slicer itself. It will show up as the right most ribbon option when the slicer is selected.

 


Comment by: jason (4/29/2011 7:43:50 AM)

I'm interested to know what "hovered" means in the slicer element section of of the above image.

Thanks

 


Comment by: Jan Karel Pieterse (5/1/2011 9:25:50 PM)

Hi Jason,

Hovered means the format the slicer item gets when the mouse hovers over it.

 


Comment by: Jason Parrish (5/2/2011 5:48:16 AM)

Thanks Jan.

 


Comment by: Trisha (8/27/2011 9:23:46 AM)

My slicer function is missing also. My spreadsheet is saved in .xlsx version, but still missing. Any suggestions?

 


Comment by: Jan Karel Pieterse (8/29/2011 1:32:27 AM)

Hi Trisha,

If you add a new pivot table to the xlsx file, does that new Pivot table allow inserting of a slicer?

 


Comment by: Vivek Gokhale (11/3/2011 6:16:56 PM)

Hi!!!

This article was great.

Is it possible to wrap text data in the slicer button???

Would really help.

Thanks

 


Comment by: Jan Karel Pieterse (11/4/2011 5:07:14 AM)

Hi Vivek,

I'm afraid not!

 


Comment by: s hartman (1/18/2012 1:56:22 PM)

How can I use one slicer when I have multiple pivotcaches (and multiple pivot tables)?

 


Comment by: Jan Karel Pieterse (1/18/2012 11:38:18 PM)

Hi Seana,

I'm afraid you cannot tie one (set of) slicer(s) to more than one Pivot cache. However, you can use a bit of VBA code to mimick that behaviour. Find some examples in the comments of the next page:

http://www.jkp-ads.com/Articles/slicers03.asp?AllComments=True

 


Comment by: Dipti (1/20/2012 9:21:40 AM)

Can i put slicer on the same page as the Pivot table?. I am trying to make it look like how we select from the drop down and get the related information in pivot table..

 


Comment by: Jan Karel Pieterse (1/22/2012 10:24:18 PM)

Hi Dipti,

Of course you can!

 


Comment by: Jason Bisulco (1/25/2012 6:13:50 AM)

Can you group the data in a slicer? (e.g. in number incremeents say 1 - 20 or group monday - wednesday if a date.

 


Comment by: Jan Karel Pieterse (1/25/2012 7:38:59 AM)

Hi Jason,

No, but you can group the appropriate field of the pivot table and add a slicer for the grouped field.

 


Comment by: Andre (1/27/2012 10:45:01 AM)

Hi Jan Karel,

Is it possible to add value filters, i.e. values greater than 1,000,000 to the slicers?

If so, what about multiple value filters?

Cheers,
Andre

 


Comment by: Jan Karel Pieterse (1/27/2012 11:11:42 AM)

Hi Andre,

I don't think ou can do that, no.

 


Comment by: Kajsa-Stina Ohlström (2/3/2012 7:40:48 AM)

When i should connect my slicer to more then one of my pivot tables, there comes a message that it "the slicer can not currenty be created or connected to the pivot table". What's wrong ?

 


Comment by: Kajsa-Stina (2/5/2012 11:46:01 PM)

Hi
When I try to add more than one pivot table connections to my slicer, I got a message that "An error occured and the slicer can not currently be created or connected to pivot tables". What's wrong ?

 


Comment by: Jan Karel Pieterse (2/6/2012 4:57:24 AM)

Hi Kajsa-Stina,

You can only add pivot tables form the same pivot table cache to one slicer, perhaps the pivot tables you are trying to combine are independently tied to their own source?

 


Comment by: Kajsa-Stina (2/13/2012 7:41:02 AM)

Thank you for the answer.
All pivot tables i try to connect to my slicer are using an area that I've named "rawdata" as datasource. This area is just in another sheet in the same workbook.
When checking name manager, I can see that "rawdata" is right defined, however, my slicers are also listed in the name manager, but they have no reference...
/Kajsa-Stina

 


Comment by: Jan Karel Pieterse (2/13/2012 8:12:58 AM)

Slicers do not have a reference in Name Manager, that is "by design". How many Pivot caches does your workbook have?
Alt+F11 to the VBA editor
control+g to the immediate window
type:

?ActiveWorkbook.PivotCaches.Count

press enter with your cursor on that line.

 


Comment by: Kajsa-Stina (2/14/2012 12:48:22 AM)

I've just 1 Pivot Cache (getting that after doing as you told)

 


Comment by: Jan Karel Pieterse (2/14/2012 3:00:29 AM)

Hi Kajsa-Stina,

In that case I truly have no idea why it does not work.

Perhaps you should try to recreate the pivot tables in a blank workbook just to check if it does work?

 


Comment by: Kajsa-Stina (2/14/2012 4:45:44 AM)

I've solved my problem by "remaking" all pivot-tables by copy/paste for all tables that should be connected to the same slicer. Now it works.

 


Comment by: Jan Karel Pieterse (2/14/2012 7:03:37 AM)

Hi Kajsa-Stina,

Excellent, glad you were able to solve the problem!

 


Comment by: Aaron (2/20/2012 2:01:12 PM)

Once you've created a custom slicer template, is it possible to save it and access it on another workbook? I've created my own slicer template (color, font, shading, etc) that I want to use in different workbooks, but the "created" templates are only available within that one workbook.
Cheers,
Aaron

 


Comment by: Jan Karel Pieterse (2/20/2012 10:54:55 PM)

Hi Aaron,

A quick way is to open the workbook with the custom slicer style applied to a slicer (not sure of that is necesary) and copy the slicer and paste it into the other workbook. The slicer style is copied with it.

 


Comment by: Aaron (2/22/2012 3:36:32 PM)

Cheers! That is quite simple and works perfectly!

 


Comment by: Kathryn (5/3/2012 10:08:53 AM)

How do I change the date format in a slicer button? My table data has the date as 6/1/2011, but I've formatted it to display Jun-11. However, the slicer button still displays 6/1/2011.

 


Comment by: Jan Karel Pieterse (5/3/2012 11:18:35 AM)

Hi Kathryn,

I just tried this and it looks like the slicer adheres to the date format as set in the source data table.

 


Comment by: Rob (5/16/2012 6:20:02 AM)

I have slicers on one tab and pivot tables on another.

When I copy these tabs to a new workbook, my slicers do not copy (or macro buttons for that matter). They used to copy fine when I went through this process, but I must have changed something along the way.

Is there a way to reset this function ?

Thanks,
Rob

 


Comment by: Jan Karel Pieterse (5/16/2012 7:53:58 AM)

Hi Rob,

To be honest, I have no idea! Sounds like your file may be experiencing problems?

Are you copying all sheets that belong to each other in one go?

 


Comment by: Rob (5/17/2012 4:03:16 AM)

Hi Jan,

I have tried the copy process using all the tabs and just single tabs. I have also tried it using the tab drag process from one open workbook to another and still lose my slicers and buttons.

May have create a different work around....

 


Comment by: T Pearce (5/22/2012 2:30:49 PM)

I am using slicers in a financial application I created and it works great filtering the data, but when you drill down to see the details, it does not filter the data at all. Can you give me some insight on how to prevent this from happening. I don't understand why it does not filter the detail data.

 


Comment by: Jan Karel Pieterse (5/23/2012 11:14:45 PM)

Hi T.,

This is one of those situations where a sample file is needed to see what is going on. Please use the email address below to send me one (if possible).

 


Comment by: katia (5/25/2012 2:24:56 PM)

Hi Eileen,

I am preparing for my next class. In my Pivot Table -> Options -> Sort, the "Insert Slicer" is not shown at all.

Can you help me?

Thank you so very much in advance.

 


Comment by: Jan Karel Pieterse (5/26/2012 2:56:53 AM)

Hi Katia,

Eileen is not my name :-), it is the name of a forum where you can ask Excel questions.

To your question: Do you have Excel 2010? Slicers are not available in older Excel versions.

 


Comment by: Lizzie (6/7/2012 11:11:14 PM)

I have created my slicer and formatted it to match my table but I need to display only months and not the full date so that I can sort my table by month. How do I modify my date so that it only shows the month and not the full date? Thanks

 


Comment by: Jan Karel Pieterse (6/8/2012 11:58:19 AM)

Hi Lizzie,

I expect you can do this by first grouping your date field in the pivot table by months and then inserting a new slicer for the month.

 


Comment by: Hank Buonforte (6/23/2012 1:09:17 AM)

Does anyone know how I can get a slicer search bar? I want to be able to type something into the search bar and press enter and have it select what I typed in the slicer.

 


Comment by: Jan Karel Pieterse (6/25/2012 7:08:25 AM)

Hi Hank,

Unfortunately, no there isn't one. Would have been a great addition!

 


Comment by: Anwaar (7/2/2012 6:55:00 AM)

Hi

When I tried to add more than one pivot table connections to my slicer, I get a message stating "An error occured and the slicer can not currently be created or connected to pivot tables". Why I am I unable to connect? I've just 1 Pivot Cache (checked as per your instructions above.

I am facing the same problem as Kajsa-Stina (2/5/2012 11:46:01 PM). I was curious to know if you have found a work around to it instead of re-creating all the tables.

Please advise.

 


Comment by: Jan Karel Pieterse (7/2/2012 11:11:26 AM)

Hi Anwaar,

Perhaps Excel thinks that the pivot table was created in an older version of Excel (I have seen that happen to files which were created in Excel 2007). Try recreating the pivot table. Also: make sure the file is NOT saved in Excel 97-2003 file format.

 


Comment by: Steve (7/5/2012 2:25:19 PM)

My fiscal year runs from July to June. After creating my pivot table and inserting a slicer for the months, the slicer automatically sorts it from January to December. I cannot find an option to customize the sort order of the slicer.

Thanks for any help you can give me.

 


Comment by: Jan Karel Pieterse (7/5/2012 7:28:37 PM)

Hi Steve,

Perhaps you can add a custom sort order in the custom lists item in Excel Options?

 


Comment by: Steve (7/12/2012 4:59:57 PM)

Having created a custom slicer style in a workbook, how do you export that style to be used in another workbook?.

Thanks


 


Comment by: Jan Karel Pieterse (8/7/2012 11:37:48 AM)

Hi STeve,

You can copy a slicer with that style to the new workbook and after that delete the slicer (I think).

 


Comment by: David (8/15/2012 1:22:42 PM)

I am trying to use slice, but when I go to set it up the slice icon is dimmed in the tool bar. Am I missing something? How do i get the slice icon to enable?
David

 


Comment by: Jan Karel Pieterse (8/15/2012 6:29:15 PM)

Hi David,

The pivot table you are trying to connect to a slicer must be in a file create by Excel 2010 and saved in the 2010 file format (xlsb, xlsx or xlsm). Pivot tables created in earlier versions or in files of older Excel versions cannot be combined with a slicer.

 


Comment by: Meggan (8/17/2012 12:22:42 AM)

I have Excel 2010 with Win 7 -- I can neither modify nor delete custom styles (either Pivot Table or Slicer) and it's driving me CRAZY! The options are available for me to click, but nothing happens after I do. Seen it? Got a fix?

Thanks!

 


Comment by: Jan Karel Pieterse (8/17/2012 11:53:06 AM)

Hi Meggan,

Odd indeed. Perhaps there are slicers/tables that have the custom style applied to them, making Excel refuse to remove the style?

 


Comment by: Carlos (9/8/2012 12:52:17 AM)

I have a pivot table in which I use two fields as report filters. The first field is a set of item groups, and the second field is a set of items contained in an item group. The various item groups have differrent sets of items. I would like to set up two slicers (item group, and item) such that when I pick a value in the item group slicer, the item slicer only shows the values appropriate for the selected item group. Can this be done, and if so how. I appreciate your help. Thanks.

 


Comment by: Jan Karel Pieterse (9/10/2012 10:02:04 AM)

Hi Carlos,

Slicers offer that option by default, but how they behave when items are not applicable is something you can control in the slicer options dialog, which is accessible through a right-click on the slicer and select "Slicer settings".

However, you cannot set it to not show non-applicable items, you can only make sure those are shown at the bottom of the list and in a different color.

 


Comment by: Jacob Jørgensen (9/12/2012 11:39:17 AM)

Hi,

I have designed a pivottable, and I want to use the slicer tools. My idea is to use a togglebutton, so if it is activated it will show the slicer, and deactivated it will not appear. Is that possible?

Hope you understand.

Kind regards

Jacob

 


Comment by: Jan Karel Pieterse (9/13/2012 9:48:16 AM)

Hi Jacob,

Sure that is possible, but it requires a bit of VBA which you tie to the toggle button (though I would probably a checkbox instead).
I added a checkbox from the forms toolbar to a worksheet. The checkbox's name is "Check Box 1". I assigned this macro to the checkbox:

Sub CheckBox1_Click()
    Dim oSl As Slicer
    Dim oSc As SlicerCache
    For Each oSc In ThisWorkbook.SlicerCaches
        For Each oSl In oSc.Slicers
            If oSl.Parent.Name = ActiveSheet.Name Then
                If ActiveSheet.CheckBoxes("Check box 1").Value = -4146 Then
                    oSl.Height = 0
                Else
                    oSl.Height = 100
                End If
            End If
        Next
    Next
End Sub


Note that you cannot hide a slicer, only set its heigh or width to zero. This means if you want to show the slicer again, you must remember what its original height was. I did not include any logic in this macro to handle that though.

 


Comment by: Mark (9/25/2012 11:10:26 AM)

I am trying to copy a slicer from one workbook to another. The slicer i pasted is no longer linked to the pivotable on the original file i copied from. How can i do it?

 


Comment by: Jan Karel Pieterse (9/25/2012 1:24:06 PM)

Hi Mark,

Looks like a slicer cannot be tied to a pivotcache in a different workbook.

 


Comment by: Michelle (9/26/2012 3:53:51 PM)

Hi,

One of my slicers contains a significantly long list of names. I realize that if I need to do a search I can do so in the pivot table itself. However, is there a way I can give the user the ability to do this in the slicer?

By the way, using Excel 2010.

Thanks in advance!

 


Comment by: Jan Karel Pieterse (9/26/2012 7:15:07 PM)

Hi Michelle,

I know, that is a PITA. The only way is the one you wrote about.

 


Comment by: Rebecca Haack (10/17/2012 8:20:01 PM)

Can you get a total for the slicer's selection? For example, If I have the slicer set for January, can I get a total for all the amounts the slicer has selected for January?

 


Comment by: Jan Karel Pieterse (10/18/2012 11:03:02 AM)

Hi Rebecca,

The pivot table shows the total for the slicer's selection, provided you have included totals in your pivot table of course.

 


Comment by: Ricardo Madaleno (10/18/2012 3:04:53 PM)

Hi,

I have an issue. Whenever I select one of the filters in my slicer, it changes the height of my header rows...

Is there a way to fix this?

Thanks

 


Comment by: Jan Karel Pieterse (10/19/2012 12:00:22 PM)

Hi Ricardo,

Perhaps you can fix this by changing the pivot table's settings regarding keeping formatting and adjusting column widths?

 


Comment by: Ricardo Madaleno (10/19/2012 1:48:20 PM)

Hi Jan,

no, that's not it... i have my pivot with the option to autofit unchecked.

this is very strange... can't seem to fix it.

 


Comment by: Jan Karel Pieterse (10/19/2012 7:08:39 PM)

Hi Ricardo,

If the slicer is filtering the page filter, perhaps the column where the page filter is located is too narrow for certain values If that cell has word wrap on, it will force Excel to increase row height to display the full value.

 


Comment by: Anne (10/30/2012 5:45:19 AM)

Hi!

I have been using slicers as a great way to indicate what areas of our business that needs attention. I want to do this once a month. But when I try to update the pivot table/ refresh, nothing changes in the slicers. Help would be much appropriated :) Anne

 


Comment by: Jan Karel PIeterse (10/30/2012 8:57:14 AM)

Hi Anne,

There should be an option in the slicer settings that allows you to indicate whether or not to have the slicer include items which are no longer in the pivotcache. Is that what you are after?

 


Comment by: Anne (10/30/2012 11:04:05 AM)

Thank you for your reply.

This is the message I get when I try to update my with the slicers; "The data source of a PivotTable connected to the slicers that are also connected to the Pivot Tabel cannot be changed. To change the data source first disconnet the slizers from this and other pivottables".

So my question to you is, is there any way to get around this? I would really like to keep my slizers and not have to do them wach time i update my pivottable.

Thanks again!

 


Comment by: Jan Karel PIeterse (10/30/2012 12:06:14 PM)

Hi Anne,

Odd, I just created a PT bound to an Access database, with one slicer connected to it. If I change the PT source (by editing the connection properties), it just lets me do that without complaining.

 


Comment by: Oz (11/13/2012 11:05:40 AM)

Hi Jan,

Like Ricardo I'm having problems with my slicers resizing my pivot table, though mine are changing the column width:

-My table is set to not resize on update.
-The values in my table are never more than 2 digits.
-The row labels are abbreviated month names.

-It doesn't matter where on the sheet the slicer is.
-It doesn't help if I stretch the columns out to allow some extra room.
-Whenever I change the months the row label column resizes.

I think it may be seeing the full name of the month even though it shows the abbreviated name, but if I stretch it to allow room for this it'll shrink for shorter month names.

Thanks for your help

 


Comment by: Jan Karel Pieterse (11/13/2012 11:35:51 AM)

Hi Oz,

Can you perhaps email that workbook to me (pls refer to this page when doing so so I won't discard that email as spam)?

 


Comment by: Ram (11/13/2012 4:15:34 PM)

Hi Jan,

Awesome job... I read through every post. Its really a task to keep replying.. But We all truly appreciate.

I read through all the post but may have missed this.
I have a slicer and a pivot chart which is a combo with line and bar. Whenever I select an option from slicers it changes the line chart back to bar. Can you help to resolve this.
Thanks again.

 


Comment by: Oz (11/13/2012 4:26:01 PM)

Hi Jan,

Thanks for getting back to me so quickly.

I'm afraid I can't send it to you as it contains sensitive company data. I tried to remove all the personal data and give you a version with just the sheets in question, and what do you know; the problem went away!

Still there on the original though :(

 


Comment by: Oz (11/13/2012 4:29:43 PM)

Well that's embarrasing;

I had a feeling the gentleman who created this workbook might be behind the fault so I looked through the page and found, right at the bottom, another pivot table, with autofit on!

Well, at least its solved, and I've got another horror story to share with the wife :)

Thanks again Jan

 


Comment by: Rebecca (11/13/2012 4:34:12 PM)

Hi Jan,

Did you find a solution to the problem posted by T Pierce on 5/22/12? I am having the same problem. I want to have slicers in a worksheet that are linked to multiple pivot tables and charts, and be able to drill into the details for just the results filtered by the slicers. Thanks!

 


Comment by: Jan Karel Pieterse (11/13/2012 7:07:52 PM)

@Oz: Thanks for letting me know, another mystery solved.

@Rebecca: No, but perhaps you can ask your question over at Eileen's lounge?

@Ram: I think the only way is to add a bit of VBA code that resets the chart after filtering.

 


Comment by: Mark Fay (12/7/2012 5:55:29 PM)

one big problem I can't get past with slicers is that that deleting or modifying a custom slicer does not work. The only way I have of modifying a customer slicer is to duplicate it, the modify box then pops up. I now have 11 custom slicers in various stages and cannot delete any. Is there some trick to get modify and delete to work.

The prompt dialogue boxes come up but don't actually do anything.

 


Comment by: Jan Karel Pieterse (12/10/2012 8:33:16 AM)

I expect you are referring to a custom slicer style?
Some important things to know:

1. You can only modify a custom slicer style. Built-in styles cannot be modified.

2. (and I think this is the one you need!) After duplicating a built-in style and modifying it, you have to apply the new custom slicer style to the slicer in question. Only then do your modifications appear.

 


Comment by: Mark Fay (12/10/2012 8:56:05 AM)

thanks Jan, yes it is a custom style. I can modify after duplicating but then cannot modify it again unless I duplicate again. Also cannot delete it. I want to modify a style check it then make more modifications until I have what I want.

Any ideas?

 


Comment by: Jan Karel Pieterse (12/10/2012 9:00:13 AM)

Hi Mark,

I can modify them as much as I like after creating a custom one. I can also delete them, even though they are in use on a slicer.
Perhaps your worksheet/workbook is protected?

 


Comment by: Obwan (1/11/2013 5:54:19 AM)

Hi
I saved my Excel 2010 workbook with 10 pivot tables connected to 1 slicer. Works perfectly. Now I have added additional pivot tables but cannot get them to list in the slicer connections. Is there a limit to the number of connections or is there a trick I am missing. Thank you in advance.

 


Comment by: Jan Karel Pieterse (1/11/2013 4:30:48 PM)

Hi Obwan,

The trick is that the pivot tables must all share the same pivot cache.

 


Comment by: Amir (1/17/2013 6:01:31 PM)

Hi
I am using pivot tables and slicers to create dashboard reports. The info you have provided on this web page is fantastic and very well put together.

I have a large pivot table with fields such as cost centres, cost, date and product code. I have created slicers for cost centres and product code.

The individual cost centres are grouped into North Division and South Division.

My question is can I create slicers for individual cost centres, all North Division cost centres, all South Division cost centres and finally whole Group cost centres? I hope this makes sense! Would very much appreciate your thoughts or advice.

 


Comment by: Jan Karel Pieterse (1/18/2013 4:33:52 PM)

Hi Amir,

No, all you can do is put a slicer up there that filters on Division. The cost center slicer will then (if properly setup) show the relevant cost centers at the top and the other ones greyed out near the bottom.

 


Comment by: Narayan (1/30/2013 7:32:51 AM)

Can I connect two slicers (having the same dataset)?
My requirement is to change the data in slicer2 depending on the input from Slicer1. Accordingly the data in the pivot tables change.

 


Comment by: Jan Karel Pieterse (1/30/2013 12:51:52 PM)

Hi Narayan,

I'm not sure what you mean by "connecting" them, the slicers are connected and do reflect filters of other slicers (of you have set the properties accordingly).

 


Comment by: Stephen (2/9/2013 12:06:03 AM)

Is there any way to save your custom slicer styles so they are available across workbooks?

 


Comment by: Jan Karel Pieterse (2/10/2013 8:34:21 PM)

Hi Stephen,

If you copy a slicer with that slicer style applied to a fresh workbook and then save-as that workbook as book.xltx (or m) to your xlstart folder the slicerstyle should become available to each new workbook you start with control+n.

 


Comment by: Beth (2/13/2013 1:53:52 AM)

I am sending out a pivot table to several folks and I'm using my first slicer. Lovely tool. However, I typically protect the sheet to protect those folks from themselves. :)
You know, accidentally changing up the columns, etc. When I do that, the slicer stops allowing the user to select a different item, even tho the users of the pivot table can still change the sort or perform additional filtering within the pivot table itself. Is the slicer not considered part of the pivot table when I select my protection options?

 


Comment by: Jan Karel Pieterse (2/13/2013 8:54:14 AM)

Hi Beth,

This can be done, but it is tricky:

Go to the “size and properties” dialog and uncheck the “locked” property for the slicer, THEN protect the sheet with “use PivotTables” (if you’re using pivot slicer) or “use autofilter” (if you’re using table slicer in Excel 2013).

 


Comment by: Arjo (2/15/2013 10:19:30 AM)

Hi,

I use slicers a lot. I used to be able to copy them around my excel file.
Today I was able to create a slicer at the pivottable sheet, but I could not copy paste it to my pivotgraph sheet. It was pasted as an image.

I also do not have the opportunity to add a slicer when I have the graph selected. Something which normally is possible.

Is there something I can do to fix this?


I need the slicers a lot for creating a dashboard but I have to be able to move the slicers around my excel file.

thanks!

 


Comment by: Jan Karel Pieterse (2/15/2013 3:53:10 PM)

Hi Arjo,

I haven't heard of that problem before. Is the chart a chart embedded on a sheet or on a chartsheet? Perhaps chartsheets cannot hold slicers?
Perhaps the file/sheet is protected?

 


Comment by: Arjo (2/18/2013 11:03:13 AM)

I am not 100% sure, but I believe that chart sheets cannot hold slicers.

I placed the graph on a regular sheet and everything worked fine.

Although it would be nice to use slicers with multiple data sheets, I know this is not going to happen any soon. So for this moment I will have to create multiple slicers.

 


Comment by: Jan Karel Pieterse (2/18/2013 11:54:28 AM)

Hi Arjo,

What do you mean by using slicers with multiple data sheets?

 


Comment by: Arjo (2/18/2013 1:28:22 PM)

I'd like to connect 1 slicer to pivottables from different data sources. something which is not possible...

for example: I have 2 data sources. data sheet 1 and data sheet 2. In both the field 'account' is available and in both the values are the same.
I want to have 1 slicer to with which I can filter the account value in all the connected pivottables.

 


Comment by: Jan Karel Pieterse (2/18/2013 5:07:33 PM)

Hi Arjo,

Check out this page, in the comments there should be some code to tie two slicers together.

http://www.jkp-ads.com/Articles/slicers03.asp

 


Comment by: Arjo (3/29/2013 10:20:29 AM)

is there a function or script that automatically (re)connects (all) slicers in a workbook, to the pivots that are available for these slicers?

 


Comment by: Jan Karel Pieterse (3/29/2013 11:45:53 AM)

Hi Arjo,

I have no "ready-made" code available. I could write some code for you commercially if you like?

 


Comment by: Martina (5/9/2013 5:52:31 PM)

Hi,
I saw someone asked the same question but i can't find the answer. I have a slicer and a pivot chart which is a combo with line and bar. Whenever I select an option from slicers it changes the line chart back to bar. Can you help to resolve this?? Thanks

 


Comment by: Jan Karel Pieterse (5/10/2013 12:14:53 PM)

Hi Martina,

I expect you can only achieve that by writing VBA code.

 


Comment by: martina (5/10/2013 6:23:30 PM)

Any examples on how to write this VBA code?

 


Comment by: JoeJoe (5/10/2013 6:39:37 PM)

I have pivot table with Name sort in order of "Z, D, H, I, F" ; a custom order. When I do slicer, I ask for "use custom list when sorting". but there is no way to disable the radial dial for "ascending" / "decending"; so the items all listed different from the above order. How can I have slicer item listed is a specific order?

 


Comment by: Jan Karel Pieterse (5/13/2013 11:41:21 AM)

Hi Martina,

The easiest would be something like this:
- Change the slicer filter
- Start recording a macro
- Correct the chart so it displays how you like it
- Stop recording
- Put a button on the sheet that holds the slicer, which you attach to the recorded macro.
- Instruct user to push button after changning filter.

It is possible to run a macro after a pivot table has been refreshed automatically, but it is a bit harder to explain :-)

 


Comment by: Jan Karel Pieterse (5/13/2013 11:47:37 AM)

Hi JoeJoe,

Odd. I just tried with a short table containing only the letters you mentioned, each in a separate cell in the order you mention. I added that table to my custom sort lists. Then I created a pivot table based on the same list and added that field as a slicer. The order was identical to the order of the list and changing the Ascending to descending just reversed the order, as expected.

 


Comment by: Martina (5/16/2013 11:14:09 PM)

I used a modified version of your recording a macro idea and it worked out nicely! Thank you!!!

 


Comment by: Gary Gray (5/28/2013 2:34:26 PM)

I want to insert a slicer from a database of over 1000 items but only want to include 11 items from that list in my slicer, is that possible if so how?

 


Comment by: Jan Karel Pieterse (5/28/2013 3:45:24 PM)

Hi Gary,

The only way I can see you do that is by filtering the database on the field that determines which items to show. Even then, the slicer will show the other values, but the ones that do not apply are colored in a lighter fill.

 


Comment by: Malinda (6/27/2013 5:08:04 PM)

I'm using exel 2010. File saved in xx.xlsx . No slicer. No options is displaced in sort & filter option. Tried a solutions to fix this in wed..... Nothing works....pls help....

 


Comment by: Jan Karel Pieterse (6/28/2013 11:52:04 AM)

Hi Malinda,

You do have a cell selected in a Pivot table?

 


Comment by: Shweta (8/2/2013 8:52:45 AM)

i read above lots of people facing difficulties in deleting a slicer setting...i just found a solution!!

..i had lots of protected sheets in my file...i had to unhide all the sheets in the workbook (even the very unhidden ones) and UNprotect them all ...i had to also UNprotect my workbook

BINGO!! ...post this i was able to modify / delete my slicer settings!!

:-)

 


Comment by: Iza (10/16/2013 10:16:55 AM)

I would like to set manually order of the list items inside slicer...at the moment I can only see the option of alphabetical order under "slicer settings"....is there any possibility to set them manually in the order I want? thanks for yoru answer, Iza

 


Comment by: Jan Karel Pieterse (10/16/2013 10:57:15 AM)

Hi Iza,

If you don't set it to be sorted, it will use the order as they appear in the data, but that does not allways seem to work somehow.

So I'm afraid I am unable to answer your question!

 


Comment by: Andrew (10/26/2013 9:05:08 PM)

For those people who cannot modify or delete slicer styles, a simple solution.

If ANY of the worksheets in your workbook are protected, the modify and delete commands don't work.

It seems like a bug, but it's easy enough to work around.

Hope this helps.

 


Comment by: Kathy (11/2/2013 1:11:43 AM)

I have a list of records. Each record has a date. I want a slicer that selects records with dates which fall in a range between two dates. How can I do that.

 


Comment by: Jan Karel Pieterse (11/2/2013 8:52:27 PM)

Hi Kathy,

I'm not sure I understand what you want to achieve?

 


Comment by: Greg (11/5/2013 11:15:28 PM)

Kathy, create a slicer against the date field. On the slicer, select the date for the beginning of the range. While holding the <shift> key, select the date for the end of your desired range. It will select records w/dates between the two dates. (holding <cntl> will allow you to select/de-select noncontiguous selections)

 


Comment by: Kathy (11/8/2013 1:10:34 AM)

We actually have excel files tied to a SQL database and are creating pivot tables off the database. I want to put a slicer on those pivot tables. So we want the slicer to look ahead -- that is I want to set it up to find records that haven't been created yet. We have a weekly report and on Monday I want the slicer set to capture all events entered thru the end of the week. That is why we want to create a date range. But the slicers will only let me select dates for which records exist. Any ideas?

 


Comment by: Jan Karel Pieterse (11/8/2013 7:35:19 PM)

Hi Kathy,

Not sure why you'd do that, a slicer lets you filter data of a PT, so there islittle point in showing entries which aren't in its source data.

 


Comment by: Jody (12/17/2013 6:00:00 PM)

Is it possible for a pivot chart to have a text box or header which lists the option chosen in a slicer? For example, slicer lists different products sold A, B C,etc., chart lists # of products sold, can chart include the name of the products choosen in the slier box in its heading?

 


Comment by: Jan Karel Pieterse (12/17/2013 6:16:44 PM)

Hi Jody,

Not easily. If the PT is built off of a powerpivot model then you can use the CUBERANKEDMEMBER function like so (array-entered into as many cells as needed):

=CUBERANKEDMEMBER("PowerPivot Data",Slicer_ShipCountry1,ROW())

And then make sure that table is nicely located next to your chart.

 


Comment by: Kelvin (1/18/2014 2:33:13 PM)

Excel 2010 default slicer layout is vertical. How to change to horizontal slicer?
Thanks

 


Comment by: Jan Karel Pieterse (1/19/2014 12:44:11 PM)

Hi Kelvin,

I'm not sure what you men by a horizontal slicer. DAre you referring to the timeline slicer? This slicer type was introduced with Office 2013 and has no Office 2010 equivalent I'm afraid.

 


Comment by: skvg (1/23/2014 3:37:23 PM)

hi Kelvin,
I think you are trying to have the display horizotally, right?
right click on the slicer, select size & properties, position and layout and increas the number of columns. this will arrange the items in the slicer horizontally into the number of columns

 


Comment by: David (1/24/2014 5:38:20 AM)

Am I able to add a "select all" to the slicer instead of holding down the control key?
Thanks

 


Comment by: Jan Karel Pieterse (1/24/2014 7:06:00 AM)

Hi David,

The control that removes the filter is in fact the same as select all.

 


Comment by: David (1/24/2014 7:15:33 AM)

Thanks Jan -

 


Comment by: T Ugot (1/31/2014 12:58:13 PM)

Dear Jan,
I created a pivot table on Excel 2010 and I noticed the field list also generates provisions for Month, Quarter and Year (i think it pulls this from the date column in the source table with format e.g. 2 Jan, 2013). This makes it easy to create a slice with the year and dates.
The problem is, I often get feedback form users that the slicers have 'disappeared' on refresh of the pivot table. I realise it's because the month, quarter and year are now missing from the field list. How do i reverse this please?

Thanks, T

 


Comment by: Jan Karel Pieterse (1/31/2014 2:06:22 PM)

Hi T,

This is probably because there are records in the source without a date entry in them. If that happens, the date grouping is removed.

 


Comment by: T Ugot (1/31/2014 3:02:16 PM)

Hello Jan,
I realised this and had corrected the date errors. I was hoping a refresh of the pivot table would correct bring back the months and Year. I even created another pivot table from the same report but the field list still did not contain the options for month and year.
Please advise!

Thanks, T

 


Comment by: Hi T, (1/31/2014 5:13:28 PM)

YOu will have to re-group the date field to get the year and month back.
Alternatively, just add a calculated year and month column to your pivot table's source table. Make sure the formulas in question cater for missing dates (otherwise the year will show as 1900)

 


Comment by: IAN JACKSON (3/10/2014 4:08:07 PM)

I have numerous pivot table charts with clustered columns which reflect a list of upto 10 numbered variables, each is a different error code. I want the columns reflecting each error code to have a fixed colour but I dont seem able to achieve this. When the pivot tables are amended with additional data for a new month the columns change colour.

 


Comment by: Jan Karel Pieterse (3/10/2014 5:38:19 PM)

Hi Ian,

Unfortunately, PivotCharts don't always retain formatting modifications when they are refreshed. What is retained depends on the Excel version you are using. Older versions retain less and less.
Your best bet is to record a macro whilst setting up everything so you can run the macro after a refresh.

 


Comment by: Tarik Bazzy (6/8/2014 6:37:20 PM)

Hi,

I am working on a project at work where I am looking at the same data for multiple months, each month is its own file and contains identical tabs.

I created a new file to use as my dashboard, with the tabs 1) Dashboard, 2) Jan Pivots 3) Feb Pivots, etc...

I started with the original data sets and created three pivot tables for each months data (Date, Grade, Yes/No). I then copy and pasted them into my new file's corresponding tab (all three jan pivots were pasted in my jan pivots tab.)

I have linked all of the cells on my Dashboard tab through VlookUps to my other tabs. Now I would like to use a slicer (if possible) to update every months data on my Dashboard. The problem I am running into is that each pivot table if I were to try and add a slicer to it only recognizes one connection (to its own pivot table). Is there an EASY uncomplicated way (I have no clue how to do anything with code or VBAs) of using slicer to update my dashboard? Hopefully I have included all the details necessary to identify where I may be going wrong or creating problems for myself with the way I set it up? I don't know any help would be greatly appreciated.

 


Comment by: Jan Karel Pieterse (6/10/2014 8:42:43 AM)

Hi Tarik,

Can't your data be combined into one larger table so you can draw pivottables off of that? Your current setup sounds overly complex.

 


Comment by: Ken (8/22/2014 4:49:16 PM)

In my new dashboard I creating , I have 5 charts all with individual tab Pivots and 1 tab with all the data.

I build Cell site

What I'm trying to accomplish is to show which Cell Sites are completed (MS108) yet pending Transport (MS149/150). I select ALL of MS108 fields and only select the PENDING on the MS149 which pulls out all site pending Transport. The Remaining Milestones(MS), End to End - Fiber to MPOE; Room Ready, and Site walk and select on those that are pending. Each Milestone has it's own Pie Chart. so when I click on a certain Market; I.e. Ohip/WPA all charts work in synch and change they're data labels.

I have two issues:

1)All the data Labels are the same when I click on a Slicer (could this be because I have all the Pivot connection selected ?

2) Each time I select another slicer, my individual Pivot change and it's difficult to get back to square one where nothing is selected.

 


Comment by: Viv (1/20/2015 1:56:13 PM)

Hi,

I have a pivot with a country field which has values such as
1. AU
2. US
3. AU; IN; TH; JP
and so on.

Is it possible to have a slicer or data validation list where the user can just type 'AU' and the pivot automatically selects all those records which have 'AU' in the country field?
Thanks in advance.

 


Comment by: Jan Karel Pieterse (1/20/2015 3:00:11 PM)

Hi Viv,

Unfortunately you can only do that using the "normal" pivotfield filter dropdowns.

 


Comment by: Viv (1/21/2015 7:33:32 AM)

Thanks Jan, for your reply. If that's the case where it can be done only by the "normal" pivotfield filter dropdowns, is there a way to capture what was typed? For example, if the user types "AU" in cell B1 in the filter drop down, it shows as "(Multiple Items)", but can I then have "AU" populated in cell C1, based on what was entered?

 


Comment by: Jan Karel Pieterse (1/21/2015 11:13:11 AM)

Hi Viv,

No, I'm afraid that is not possible. You could mimick the behavior using VBA and a dropdown box from the developer tab perhaps.

 


Comment by: Joe Van Nel (2/3/2015 10:06:22 AM)

Hi Jan

I have a workbook where I have a slicer connected to a small table (the table just shows the Last 2 months of a period)- eg: Dec and Jan

The problem I have is that when the month changes and the pivot table updates to Jan and Feb - the slicer still keeps "Dec" even though it is not in the table anymore

How can I prevent this from happening ?

Thanks

Joe

 


Comment by: Joe Van Nel (2/3/2015 10:17:16 AM)

Hi Jan

I have found the solution to my question on the slicer showing old data. Please ignore my previous question.

In the slicer settings, you can untick the box that says "shows items deleted from data source"

Thanks

Joe

 


Comment by: Jan Karel Pieterse (2/3/2015 11:33:48 AM)

Hi Joe,

Good to hear you found your solution.

 


Comment by: John (2/18/2015 12:01:20 AM)

I'm having a bit of an issue with my slicers and was hoping to find a solution.

I am trying to create a dashboard so that certain responsible persons can verify and confirm a payroll. Our policies are such that pay can be very different from one check to the next for any number of codes. I have pulled in two pivot tables based on a report that looks at all of the different earning codes on the check run that needs to be confirmed, and the previous check run, which has already been paid.

One pivot table summarizes the data by earning code and each check run is a separate column. For example, Regular earning for 2/4 is $350k and for 2/18 it is $345k. Training pay for 2/4 is zero and for 2/18 it is $5k.

The other pivot table summarizes the data by person and again each check run is a separate column. The difference on this, is that I have the data to display the difference from the previous check run. So for Rick, the total pay for 2/4 is blank (because it is the basis for comparison) and for 2/18 it is $1000 because he earned $1000 more on 2/18.

I have a slicer for earning codes and it links to both pivot tables. If I click on the training pay code in the slicer, the pivot tables act in the following fashion. The first pivot table, the one that summarizes codes, hides the 2/4 column and only shows $5k under the 2/18 column. The second pivot table hides the 2/4 column and for the 2/18 column goes blank. It no longer shows the difference from the previous pay date. Is this because it is hidden so the 2/18 now becomes the basis for comparison? If so, is there a way to force columns on pivot tables to remain visible even if there is no data?

It's nice that the second table only shows the employees who have training pay, but since it doesn't show any amounts next to them, it's not as useful as it could be.

Any help would be greatly appreciated!

 


Comment by: Jan Karel Pieterse (2/18/2015 6:49:16 AM)

Hi John,

I expect the problem is not caused by the slicers, but rather by the setup of the pivottables. If you have fields that are calculated based off of the previous value, filtering affects the results. I would need to see your workbook in order to give a more useful advice though.

 


Comment by: Mary (3/6/2015 5:21:58 PM)

I have a Slicer that I want to be able to put them in the correct order. The only option I see is Alpha.
My segments are U50, 50-99,100-499,500-999,1000-2999, 3000+. When I put them in a slicer the order is wrong. How do I fix that?

 


Comment by: Jan Karel Pieterse (3/6/2015 7:40:14 PM)

Hi Mary,

You should be able to add a custom list to Excel containing the needed order and then the slicer should sort using that list.

Select File, Options, Advanced. Scroll down to bottom of screen and click "Edit custom lists". Click in the box next to "Import" and select the range of cells which have the list in the order you need.

 


Comment by: Samm (3/25/2015 9:52:53 PM)

Is there a way of dynamically ordering the data in the slicer so that it always displays the most populous to least populous in the data table?

 


Comment by: Jan Karel Pieterse (3/26/2015 9:43:00 AM)

Hi Samm,

I don't think you can do that I'm afraid.

 


Comment by: Sunil (4/13/2015 12:21:01 PM)

Hi Samm,
I have a question around the number formatting of the entries that show up in the slicer window. The column (field) that I wish to add to the slicer is a currency column with 2 decimal places. For e.g. $1.00, $1.05, etc. which indicate the exchange rates. When I add this column to a slicer, the entries in the slicer window (where you choose from) show up as 1, 1.05, 1.1, etc. i.e. the formatting reverts back to general. Would you know how to fix this?

 


Comment by: Tom (5/27/2015 9:09:45 PM)

This question is directly related, how can I edit my slicers once they are completely filtered?

 


Comment by: Jan Karel Pieterse (5/28/2015 9:24:28 AM)

Hi Tom,

I'm not sure what you mean by editing the slicer?

 


Comment by: Christine Rufkahr (6/12/2015 11:26:25 PM)

How can I make a Slicer Style that I have created available for all spreadsheets or at least for all future spreadsheets?

 


Comment by: Jan Karel Pieterse (6/13/2015 7:14:36 PM)

Hi Christine,

Create a workbook with a custom slicerstyle. When done and happy with it, copy a slicer with tht style applied to a new empty workbook.
Delete the sycer, the style should stay in the workbook.
Save-as the workbook as book.xltx to your XLSTART folder.

 


Comment by: Alliesong (8/6/2015 10:17:36 AM)

How to get slicers to stay in one place on the sheet and not move when filters are selected and the pivot table below changes size,eg, has less columns

 


Comment by: Jan Karel Pieterse (8/6/2015 11:02:42 AM)

Hi Allie,

I would expect changing the property so the slicer does not move, nor sizes with the cells should do the trick?

 


Comment by: Rakesh (9/29/2015 8:09:27 PM)

Is there a known problem using slicers for pivot tables with calculated items? Quick google search didn't reveal anything.

I have 3 slicers. Usually if I choose a value from the 1st slicer; the 2nd and 3rd change such that items with no data are shaded out (per the rules of the style). This was working but as soon as I added a calculated field it believes that there is always data and hence it is not greyed out.

 


Comment by: Jan Karel Pieterse (9/30/2015 11:46:14 AM)

Hi Rakesh,

That is new to me, I never heard of that problem. That does not mean the problem doesn't exist though :-)

 


Comment by: Tanya (10/1/2015 5:45:57 PM)

If trying to create a slicer that will expand and reduce columns through a horizontal filter... is that possible? Slicers usually function as vertical filters, but what if I want a slicer that will specifically select the headers that will be in my pivot? Essentially this would be a horizontal filter on the column headings. Is that possible without using power pivots?

 


Comment by: Jan Karel Pieterse (10/2/2015 3:00:23 PM)

Hi Tanya,

Yes, that should work. You can add any field of your source data as a slicer.

 


Comment by: Gerald Vaughan (10/30/2015 3:14:14 PM)

Is it possible to edit slicer names to accommodate using a prior spreadsheet for a new project?

 


Comment by: Jan Karel Pieterse (10/30/2015 4:29:33 PM)

Hi Gerald,

I don't think you can edit the name to use in formulas. You can edit a slicers name in the slicer settings dialog however.

 


Comment by: Joe G (12/10/2015 6:46:59 AM)

Any way in 2013 to have no fill color surrounding the slicer button? Deleting th box lines works, but I find no way to delete the fill color.

 


Comment by: Jan Karel Pieterse (12/10/2015 11:02:05 PM)

Hi Joe,

I don't get the point I'm afraid, how will you be able to see what the slicer is filtered on without the fill color?

 


Comment by: Joe G (12/10/2015 11:54:41 PM)

Re my previous, I want to remove the fill of the box thatsurrounding the slicers. I am using a photographic background for the page and want just the slicer buttons to appear (no visible box (fill) around them. How do I get a transparent option?

 


Comment by: Jan Karel Pieterse (12/11/2015 12:03:17 PM)

Hi Joe,

Aha, now I understand.

I'm sorry to say you cannot set a slicers background to transparent.

 


Comment by: Joe G (12/29/2015 2:48:56 AM)

Re my previous, I want to remove the fill of the box thatsurrounding the slicers. I am using a photographic background for the page and want just the slicer buttons to appear (no visible box (fill) around them. How do I get a transparent option?

 


Comment by: Jan Karel Pieterse (12/29/2015 11:53:16 AM)

Hi Joe,

This cannot be done with slicers.

 


Comment by: Bjørnar K. (1/14/2016 2:11:50 PM)

Hi there,

Is it possible to have settings that excludes the slicer when printing out the excel sheet?

best regards

Bjørnar

 


Comment by: Jan Karel Pieterse (1/14/2016 2:34:13 PM)

Hi Bjørnar,

Sure, right-click the slicer and select Size and Properties. Click the Properties tab and uncheck the box.

 


Comment by: Amber J (1/14/2016 8:28:43 PM)

Hi Jan,

Do you know of a way to sync a timeline slicer with another slicer. I have a pivot table that has dates and locations. Not all locations are used in each month range which is why I was wondering if it is possible for only the locations valid for the selected month will appear in the slicer.

Thank you,
Amber J

 


Comment by: Jan Karel Pieterse (1/15/2016 11:40:42 AM)

Hi Amber,

Slicers are supposed to handle that situation out of the box already, no need for any programming.

 


Comment by: Laura (2/12/2016 9:02:13 PM)

Hi Jan - I read through all the comments, appreciating the effort you put into helping people! However, I don't see this problem:
I have slicers on a Pivot Chart (Power Pivot actually) in Excel 2010. I was able to format the width of buttons, number of columns, position on the sheet, etc on some of the slicers at some time, but not all of them. Today, two of the 3 slicers will not behave - I can't move them nor resize them - they keep autoformatting to an unacceptable appearance.
Any thoughts?
thanks a bunch

 


Comment by: Jan Karel Pieterse (2/13/2016 3:56:01 PM)

Hi Laura,

I can't say I recognize this. Perhaps it helps to simply get rid of them and add them back?

 


Comment by: Jacques Major (3/1/2016 8:40:15 PM)

Hi, I've found the issue regarding the drilldown not being filtered when using a slicer. The field used in the slicer must either be a part of the pivot table or a filter on the pivot table for it to work. If it isn't in the pivot table either as a field or filter, the filter will not work and will show all of the data instead. I have 8 different slicers tied to several pivot table and having the filters showing on all the pivot tables just makes it confusing. Is there a way to work around this?

 


Comment by: Jan Karel Pieterse (3/1/2016 9:25:16 PM)

Hi Jacques,

I'm afraid there isn't much you can do other then e.g. hiding the rows with the page filters in question or by placing slicers on top of them.

 


Comment by: Jacques Major (3/1/2016 9:28:12 PM)

Thanks for looking into Jan. That was the solution I went with. It's too bad Microsoft hasn't seen fit to tie the drill to the filters applied in the segment. It's a little confusing for users seeing a lot more data than shown in the pivot table when they drill down.

 


Comment by: Reuben Cofie (3/4/2016 4:58:21 PM)

This is an excellent site. It has given answer to a problem I have been searching for a couple of days now.

I need a help to create an ACCOUNTABILITY DASHBOARD for a health organization.
This dasboard is intended to tack accountability of officers who have been given responsibilities to accomplish. The proposed indicators for the dashboard are as follows:
ACTIVITY TYPE, PROBLEMS REPORTED, REPORTED BY, REPORTED TO, DECISIONS TAKEN, PERSON RESPONSIBLE FOR ACTION, TIMELINE, ACTION TAKEN/NOT.

Please I would like to know if combos could be created for the slicers to select the options (if so how?)
instead of the default which displays all the options on the slicers occupying space.

Thanks and kind regards.

 


Comment by: Jan Karel Pieterse (3/4/2016 8:53:40 PM)

Hi Reuben,

It is a bit hard to advise without an idea about the data.
Perhaps you'd better ask this question at http://www.eileenslounge.com as there you can upload example files and have more people available for help.

 


Comment by: Nicolas (3/8/2016 11:32:51 AM)

Hello Jan,

I think I'm encountering the same problem as T. (5/22/2012 2:30:49 PM)but having read the whole forum I couldn't find the answer. Let me present it my way.
I use Excel 2010 Reports filtered by several slicers to then create graphics off a PivotTable located in Access 2010.

When some figures in the graphics are unexpected, I'd like to go to the underlying report double click on these figures to get the pivot table entries the the number is based.

Unfortunately, when I do that I get a new sheet with a list of entries that is much larger than what is synthesized by the number I was investigating. I'm tempted to say that the list on that sheet does not take into consideration the slicers. Would you by any chance have a way around that ?
I ' be very thankful.
Thank You for this great reference page

 


Comment by: Jan Karel Pieterse (3/11/2016 10:32:51 AM)

Hi Nicolas,

If a slicer filters a field that is NOT in the pivot table, the filter settings of that slicer are ignored when you drill down. The only way around that I know of is either by adding that field to the PT (as a page field perhaps) or by using a VBA event to filter the data after the drilldown.

 


Comment by: Nicolas (3/11/2016 11:28:56 AM)

Thank you Jan !

Indeed, coupling the slicer with the report gives you the numbers you want BUT if you want the drill down to be coherent with that number you must take care to include in addition all "sliced" fields at least as a pages to the report you're "drilling".
Works great ! Thanks again

 


Comment by: AK (5/26/2016 10:44:50 AM)

Hi,

I need to enter manual value in slicer.

Need help to add this value manually.

Thanks
AJ

 


Comment by: Jan Karel Pieterse (5/30/2016 6:08:49 PM)

Hi AK,

I am not sure what you mean?

 


Comment by: Joe (6/12/2016 3:58:54 PM)

i am using excel 2010- Microsoft professional plus, and when i click on the slicer , after selecting any cell from a table, it opens a dialog for existing connection . how can i solve this ?

 


Comment by: Jan Karel Pieterse (6/13/2016 5:40:08 PM)

Hi Joe,

Sounds like the table has an external connection which needs credentials. What happens if you refresh the table first?

 


Comment by: Stephen (7/12/2016 3:48:10 PM)

Hi,

When printing a pivot table and slicers, I apply 'fit columns to one page' in the print option for obvious reasons but the slicer font doesn't shrink in proportion with the rest of the scorecard.

Is there anyway of fixing this so that the slicer font is in proportion with the rest of the document?

Thanks for any help.

Stephen

 


Comment by: Jan Karel Pieterse (7/15/2016 4:54:48 PM)

Hi Stephen,

The way to adjust it is to create a new slicer style and adjust the font of the new style (one of the pages of this article shows how).

 


Comment by: Siam (10/26/2016 8:27:59 AM)

How to manual sort slicer icon? i would like to mange it by my self.

 


Comment by: Jan Karel Pieterse (10/26/2016 8:55:43 PM)

Hi Siam,

One way to achieve that is by adding a custom list in the sort order that you need and set the slicer to use custom lists (default).

 


Comment by: Ty (12/7/2016 1:30:16 AM)

I want to resemble in my slicer as a dollar amount so it is consistent with the pivot table can this be done and how

 


Comment by: Jan Karel Pieterse (12/8/2016 6:59:29 AM)

Hi Ty,

If you make sure the data is formatted as $ it should show up on the slicer as $.

 


Comment by: Giedrius (12/12/2016 12:03:10 PM)

Hello,

I have a slicer for years (2016, 2017) but in the list of available selections I also get "<2016-01-01" and ">2017-12-31".
How do I get rid of them? I just need the years. Thank you.

 


Comment by: Jan Karel Pieterse (12/13/2016 6:59:54 AM)

HI Giedrius,

One way is by adding a calculated column to your sourcedata in which you calculate the Year.

 


Comment by: Whitney (12/15/2016 4:00:06 PM)

I have inserted a slicer, the resource the slicer is using has multi colored fonts (for categorization purposes). When you double click on the value to induce the slicer information it does not transfer the font color from the original resource. Is there a setting or a way that this can be done?

 


Comment by: Jan Karel Pieterse (12/15/2016 4:02:49 PM)

Hi Whitney,

I'm afraid the font color of the source data cannot be reflected on a slicer. Only the number formatting (like currency symbols) will come through.

 


Comment by: Peter (1/31/2017 2:16:12 PM)

Hi.
I can change the font in the slicers. But the "bottums" do not change size. Is there a way to change them as well. The reason why I change the fonts is to have the whole slicer to take less space when printed.

Regards

Peter

 


Comment by: Jan Karel Pieterse (1/31/2017 2:28:57 PM)

Hi Peter,

You can change the slicer directly from the ribbon and set the button height from there.

 


Comment by: Josh (2/23/2017 5:18:35 PM)

Hi,
I have a slicer that shows different account owners and their department spend. Is it possible to set a passcode similar to one in access, so that when each individual account owner receives this file they can only access their respectful department information?

 


Comment by: Jan Karel Pieterse (2/24/2017 3:05:51 PM)

Hi Josh,

In Excel the only way to do that is to send out the file with ONLY the information the user is allowed to see.

This does not apply if the data is tied to e.g. a SQL Server database with proper user rights and permissions set up.

 


Comment by: Shane (6/12/2017 6:07:54 PM)

I have a table as follows:

Job Start Time Complete Time
Script A 00:00:00 00:10:00
Script B 00:05:00 00:06:00
Script C 00:03:00 00:14:00
Script D 02:20:00 03:50:00

I need to be able to slice and display records by time ranges.
For example: Show me all rows where Start Time >= 00:00:00 and Complete Time <= 00:12:00
Note: these time ranges are at the discretion of the user.
Any advice would be much appreciated.
Thanks!!

 


Comment by: Jan Karel Pieterse (6/13/2017 10:12:21 AM)

Hi Shane,

I'm afraid this can only be done using filtering, slicers are not up to a task like this (yet).

 


Comment by: Nelly (7/20/2017 9:35:49 AM)

Hi all,
Please advise if there is any option to customize the list of slicer buttons/item? For example, on the slicer I have 3 buttons (Public, Commercial and blank) and I want to see only Public and Commercial. Is there any option to delete/hide a button without changing the original source?

many thanks

 


Comment by: Jon (8/11/2017 7:59:41 PM)

Hi,

I create a pivot table on excel 2013 and I'm using the slicer option.
First I have 3000 records, I'm sorting by region and I have the following results:

Mexico - 5 results
India - 48
US - 129

When I click on the 5 under Mexico, instead of showing me the 5 records from Mexico it opens the 3000 records. What did I do wrong?
Please help.

Thank you

 


Comment by: Jan Karel Pieterse (8/13/2017 5:08:31 PM)

Hi Nelly,

You can't do that without modifying the data I'm afraid.

 


Comment by: Jan Karel Pieterse (8/13/2017 5:18:56 PM)

Hi Jon,

I'm afraid this is hard to trouble-shoot without the data or the file. You can contact me through the email address at the bottom of this page if you like?

 


Comment by: Hayden (9/11/2017 7:57:10 PM)

(Working in Excel 2013)

I have 2 connected Pivot Tables, 2 traditional slicers, and 1 timeline slicer. As it currently stands, the selection in the first slicer will effectively determine what options appear in the second slicer (ie. "Hide data with no value" works correctly). However, when I use the timeline slicer to choose a certain range of time, it does not remove options in the subsequent slicers that would now have no data matching the selected time frame. IS there a way to make the slicers recognize and respond to the timeline slicer's selection?

 


Comment by: Jan Karel Pieterse (9/12/2017 9:56:16 AM)

Hi Hayden,

Hmm, I wouldn't have expected that to happen indeed. IMO this is a bug.

 


Comment by: Luke (10/19/2017 3:20:19 AM)

Hi - I have 4 slicers connected to one pivot table. I would like to show no data in the pivot table (except headers) until a selection is made from one of the slicers. Is there any way to do this without using VBA?

 


Comment by: Jan Karel Pieterse (10/19/2017 9:42:59 AM)

Hi Luke,

That would be hard to do as a pivot table does not allow you to filter itself to display no data at all to begin with. You can only filter it to show at least one existing category. That is, unless you change the source data of the PT after setting up that filter. If after a refresh of the data no applicable categories remain then your PT ends up without displaying data. So you try to could trick Excel into a state like than somehow.

 


Comment by: M.Matawie (11/14/2017 5:15:08 AM)

So used to Excel 2003, I got frustrated when using Excel 2013.
Real consolation will be the slicer. Fabulous! Made my filtering so much easier!!!

 


Comment by: Rita Lima (12/5/2017 2:54:07 AM)

Hello Luke,

Is it possible configure a background color of a Slicer item (Button) based on its value?

For example, I would like show my slicer like:
0 - 30 (button green)
31 - 60 (button yellow)
60+ (button red)

Thank you,

Rita

 


Comment by: Jan Karel Pieterse (12/5/2017 3:57:08 PM)

Hi Rita,

I'm afraid that cannot be done.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].