none
Showing text in pivot table data area

    Question

  • Dear All,

    I am generating an excel sheet with a pivot table in excel 2007 xlsx format, backed by an sql server query. The query results contain a data value column, multiple dimension as expected, and a footnote column (usually 1-2 character long codes) that applies to some of the rows in the result set.

    My users do not really want any aggregation but reorientation of columns/rows and filtering capabilities of the pivot table. So, we do not have the conceptual problem of what to do with aggregated text. Everything works fine except for the fact that the footnotes are not displayed in the pivot table along with the data value when I put the footnotes column into the data fields area. Instead, it shows the count of (or sum, or ..) of them.

    I found on the other groups that  Shane Devenshire had a solution to displaying text in the pivot table data area but I could not find the actual code anywhere on the internet.

    Any solutions to showing text in pivot table data area that works with excel 2007? or any other suggestion that might work for my problem, please?

    Thanks a lot

    Kemal

    Thursday, November 25, 2010 12:26 PM

Answers

  • Hi Kemal,

    To the issue about How to show text in a pivot table’s values area instead of numbers, I suggest you try following method.

    For example, if you add the Region field to the Values area, you’d like to see the region’s name, instead of a Count Of Region number.

    PivotTextVal01

    If you use Crosstab queries in Microsoft Access, you might have achieved this by using the First or Last summary function, instead of Sum or Count.

    Unfortunately, the First and Last functions aren’t available in Excel pivot tables, so there’s no easy way to show text in the Values area.

    Workaround #1 – Use the Row Fields

    You could add the Region field to the Row Labels area, with the City field. Then add another field in the Values area to show a count of the regions. The layout won’t be exactly what you wanted, but it will show the region name.

    PivotTextVal02

    Workaround #2 – Create a Custom Number Format

    In this example, there are only two region names, so you could use a custom number format to show the region names. You’ll assign a number to each region, then use that number in the pivot table. Note: This technique is limited to 2 items.

    Create the Region Number field:

    1. In the source data, add a new column with the heading RegNum. In this column, type a 1 for East region orders and 2 for North region orders.
    2. Refresh the pivot table, so the RegNum appears in the Field List.
    3. Add the RegNum field to the Values area, and right-click on one of the numbers.
    4. In the popup menu, click summarize by Max.

    PivotTextVal03

    Apply a Custom Number Format:

    1. Right-click a Region value in the Values area in the pivot table.
    2. In the popup menu, click Number Format.
    3. In the Category list, click Custom.
    4. In the Type box, enter [=1]“East”;[=2]“North”;General
    5. Click OK, to close the dialog box.

    PivotTextVal04

    The pivot table will show the Region names, instead of the Region numbers.

    PivotTextVal05

    Hope that helps.

    Wednesday, December 01, 2010 6:00 AM
    Moderator

All replies

  • Hi Kemal,

    To the issue about How to show text in a pivot table’s values area instead of numbers, I suggest you try following method.

    For example, if you add the Region field to the Values area, you’d like to see the region’s name, instead of a Count Of Region number.

    PivotTextVal01

    If you use Crosstab queries in Microsoft Access, you might have achieved this by using the First or Last summary function, instead of Sum or Count.

    Unfortunately, the First and Last functions aren’t available in Excel pivot tables, so there’s no easy way to show text in the Values area.

    Workaround #1 – Use the Row Fields

    You could add the Region field to the Row Labels area, with the City field. Then add another field in the Values area to show a count of the regions. The layout won’t be exactly what you wanted, but it will show the region name.

    PivotTextVal02

    Workaround #2 – Create a Custom Number Format

    In this example, there are only two region names, so you could use a custom number format to show the region names. You’ll assign a number to each region, then use that number in the pivot table. Note: This technique is limited to 2 items.

    Create the Region Number field:

    1. In the source data, add a new column with the heading RegNum. In this column, type a 1 for East region orders and 2 for North region orders.
    2. Refresh the pivot table, so the RegNum appears in the Field List.
    3. Add the RegNum field to the Values area, and right-click on one of the numbers.
    4. In the popup menu, click summarize by Max.

    PivotTextVal03

    Apply a Custom Number Format:

    1. Right-click a Region value in the Values area in the pivot table.
    2. In the popup menu, click Number Format.
    3. In the Category list, click Custom.
    4. In the Type box, enter [=1]“East”;[=2]“North”;General
    5. Click OK, to close the dialog box.

    PivotTextVal04

    The pivot table will show the Region names, instead of the Region numbers.

    PivotTextVal05

    Hope that helps.

    Wednesday, December 01, 2010 6:00 AM
    Moderator
  • I tried to do this and at the point of creating a custom number format keep getting the same error that it cannot use my formula.  Can I not do more than two formulas?

    

    Wednesday, November 06, 2013 9:07 PM