+ Reply to Thread
Results 1 to 16 of 16

Slicer buttons i don't want

  1. #1
    Registered User
    Join Date
    11-04-2016
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    17

    Slicer buttons i don't want

    I use Excel 2007 and when I insert a slicer into one of my pivot charts, it has buttons that I don't want. The buttons have no data that would applie to them and when you click on them, no data shows up.

    Any help removing them would be appreciated.

    Here is a screen show of the slicer to show what I mean. Untitled.png

    Thanks for your help!

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Slicer buttons i don't want

    Right click,

    Slicer Setting and check to hide items with no data

    But I can't see your image so not sure if that's what you want but I suspect it is

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Slicer buttons i don't want

    Hi,

    I am intrigued that you have slicers at all in 2007- they were not added to Excel until 2010.

    In any event, you would need to create a year column in your source data unless you actually have a later version that allows hiding items with no data rather than merely indicating that they have no data by fading them.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Registered User
    Join Date
    11-04-2016
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    17

    Re: Slicer buttons i don't want

    Sorry, You are right. I have 2010 on this computer... Thought I was sitting at another desk.

    I have tried to use the options available to hide them in the slicer options menu but have had no luck. The buttons I would like to get rid of say >"a date" and <"a date" and I am not sure why the system creates them, there is no data that would apply.

    xlnitwit you are right, it is just fading the buttons and I can't remove them. Can you elaborate on your idea about adding a year column? I am using ODBC driver to pull information from our accounting system, and I do not have a field specifically for year.

    Thanks for your help guys.

    Scott

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Slicer buttons i don't want

    You need to edit the query that is retrieving the data from the other system to add a new column using the expression Year(date field)

  6. #6
    Registered User
    Join Date
    11-04-2016
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    17

    Re: Slicer buttons i don't want

    Will this change my date field from 01/15/16 to 2016? If so, how will this change the slicer?

    Also, not exactly sure where to enter the expression.

    In here:ms query #1.jpg


    Or in the command text:ms query #2.jpg
    Attached Images Attached Images

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Slicer buttons i don't want

    You would edit the query in MS Query to add a new column that calculates just the Year part. Your existing column will be unaffected.

  8. #8
    Registered User
    Join Date
    11-04-2016
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    17

    Re: Slicer buttons i don't want

    I appreciate your help and I understand what you are suggesting, I just don't have the knowledge to make this happen in MS query. I will work with a guy here locally to try and figure this out. Once again, thanks!!!

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Slicer buttons i don't want

    If you click the SQL button in MS Query, can you then please copy and paste the full query's SQL here? I will try to adapt it, though it would help to know what the package you are connecting to is because the syntax is not always the same for different databases.

  10. #10
    Registered User
    Join Date
    11-04-2016
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    17

    Re: Slicer buttons i don't want

    I know what you mean. every attempt I made gave ma a syntax error!

    Here is the SQL Statement:

    SELECT "Quote Header File".DOCUMENT_NUMBER, "Quote Move History".ORDER_NUMBER, "Quote Move History".DATE_MOVED, "Quote Move History".MOVED_BY, "Quote Move History".ORDER_VALUE
    FROM "Quote Header File" "Quote Header File", "Quote Move History" "Quote Move History"
    WHERE "Quote Move History".QUOTE_NUMBER = "Quote Header File".DOCUMENT_NUMBER

    The field .DATE_MOVED is the only field that contains a date. It is located in the "Quote Move History" table and this table is in a database that I really know nothing about. I might be able to call someone and get some information, but the 3rd party people that created our accounting software are usually very quiet when it comes to messing with their database. They did set up the connection for us to pull information into excel but that is about as far as they like to go!

    Thanks!

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Slicer buttons i don't want

    Perhaps this
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    11-04-2016
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    17

    Re: Slicer buttons i don't want

    Here is the result of this change:

    result.jpg

  13. #13
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Slicer buttons i don't want

    Perhaps remove the word 'As' from before 'Move_Year'?

  14. #14
    Registered User
    Join Date
    11-04-2016
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    17

    Re: Slicer buttons i don't want

    Same result!

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Slicer buttons i don't want

    After a little research, it seems as though MSQuery was changed and does not allow aliases for columns so perhaps this
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    11-04-2016
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    17

    Re: Slicer buttons i don't want

    I will look into this and post a result later today or tomorrow. thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can I Sync a Slicer to Another Slicer If....
    By Polak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2015, 12:35 AM
  2. Make one slicer mirror filters of another slicer
    By IbenezPrez in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-28-2014, 02:00 PM
  3. Macros with buttons, spin buttons, scroll buttons, etc.
    By qqbbppdd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2013, 09:34 AM
  4. How to insert and user of Buttons, Radio buttons, check boxes in excel sheet.
    By krishco in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2013, 02:52 AM
  5. Slicer Macro - unfilter another slicer on selection
    By chadheins in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2012, 10:41 AM
  6. [SOLVED] Change Chart Title Depending on a Slicer - slicer advice
    By JungleJme in forum Excel General
    Replies: 8
    Last Post: 08-17-2012, 07:59 AM
  7. Lock everything but slicer buttons
    By svebbi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-04-2012, 12:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1