+ Reply to Thread
Results 1 to 2 of 2

How to create pivot table with number range options (e.g. <5, 6-10, 11-15, >16)

  1. #1
    Registered User
    Join Date
    02-16-2017
    Location
    new york
    MS-Off Ver
    2010
    Posts
    1

    How to create pivot table with number range options (e.g. <5, 6-10, 11-15, >16)

    I am working to make a dashboard in excel. I have one field (let's call is 'Salary1') in my pivot table that contains employee salaries. I would like to create a slicer that will allow me to sort Salary1 in the pivot table. Of course when I create the slicer for Salary1 it gives me every numerical piece of data as a selection option. I can then select an individual number to only view employees who make that salary.

    Instead I would like to have the slicer have options that look similar to this with ranges:

    <=10000
    10001-50000
    50001-100000
    100001-300000
    300001-400000
    >=400001

    Is this possible to do? Since I am making a dashboard I would like it to be user friendly, but it is not mandatory that a slicer is the tool to do this.

    Thanks all!

  2. #2
    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: How to create pivot table with number range options (e.g. <5, 6-10, 11-15, >16)

    Hi,

    Since your groups are not regular, you cannot simply group the field in the pivot table. You will need to use a lookup table and an additional column in the source data. You can then connect your slicer to the new field to show the options you want.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ 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. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  2. Create Range from highlighted cells for Pivot Table Source Data
    By athyeh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-08-2015, 03:34 PM
  3. Numeric Range in Pivot Table can't be used to create a Histogram
    By Viral107 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-12-2015, 07:04 AM
  4. Replies: 1
    Last Post: 02-04-2008, 04:35 PM
  5. Using dynamic range to create pivot table
    By cursednomore in forum Excel General
    Replies: 3
    Last Post: 06-13-2006, 04:40 PM
  6. Pivot table to launch Macro- preserve format under table options
    By Darin Kramer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2005, 02:05 PM
  7. Pivot Table - Default Settings for Table Options
    By Nacho in forum Excel General
    Replies: 0
    Last Post: 02-25-2005, 02:06 PM

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