SOLVED

Excel Pivot - Move Row to Column (wants to sum the text)

%3CLINGO-SUB%20id%3D%22lingo-sub-2399362%22%20slang%3D%22en-US%22%3EExcel%20Pivot%20-%20Move%20Row%20to%20Column%20(wants%20to%20sum%20the%20text)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399362%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20pivot%20and%20I%20do%20not%20like%20the%20format%20of%20it.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20move%20the%20the%20row%20for%20%22description%22%20(the%20red%20starred%20in%20the%20pic%20below)%20from%20the%20row%20to%20the%20column%20but%20when%20I%20do%20this%20it%20wants%20to%20sum%20this%20text%20field.%26nbsp%3B%20How%20can%20I%20move%20the%20row%20to%20the%20column%20but%20make%20it%20display%20the%20text%20as%20it%20is%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2399362%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2399412%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Pivot%20-%20Move%20Row%20to%20Column%20(wants%20to%20sum%20the%20text)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Pivot%20Table%2C%20as%20you%20know%2C%20is%20a%20powerful%20and%20useful%20feature%20of%20Excel%3B%20many%20consider%20it%20one%20of%20the%20most%20useful%20capabilities.%20Its%20power%20comes%20in%20being%20to%20summarize%20data%20in%20a%20cross-tabulated%20way%2C%20summarizing%20where%20fields%20are%20in%20common%20by%20row%20and%2For%20by%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20difficulties%20with%20your%20data%2C%20I%20%3CEM%3E%3CU%3Ethink%3C%2FU%3E%3C%2FEM%3E--not%20sure%20since%20I'm%20not%20fully%20familiar%20with%20it--is%20that%20each%20row%20in%20your%20database%20is%20actually%20distinctive%20in%20at%20least%20one%20of%20the%20fields.%20So%20it's%20hard%20to%20even%20know%20what%20kind%20of%20summary%20you're%20expecting.%20Here%2C%20to%20illustrate%2C%20were%20a%20couple%20of%20possibilities%20I%20came%20up%20with.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1622394978463.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284902iBBF43347FC935306%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1622394978463.png%22%20alt%3D%22mathetes_0-1622394978463.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1622395011048.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284903iA26BE7A7D1563CDC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_1-1622395011048.png%22%20alt%3D%22mathetes_1-1622395011048.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20own%20experience%20with%20Pivot%20Tables%2C%20I've%20often%20found%20it%20necessary%20to%20play%20around%20with%20the%20various%20combinations%20and%20possibilities%20before%20finding%20the%20format%2Flayout%20that%20provides%20the%20summary%20I%20have%20in%20mind.%20You%20may%20have%20done%20a%20lot%20of%20that%20experimentation%20already......it%20might%20be%20that%20what%20you%20need%20to%20do%20is%20change%20the%20raw%20data%20itself%2C%20not%20to%20make%20it%20inaccurate....%20but%20to%20make%20it%20more%20summarizable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20you%20have%20the%20%22Date%20of%20Amendment%22%20field%20specified%2C%20AND%20you%20say%20under%20description%2C%20such%20things%20as%20%221st%20milestone%20extension%2C%202nd%20amendment%20of%20the%20LC%2C%22%20which%20strikes%20me%20not%20only%20as%20wordy%20but%20perhaps%20only%20helpful%20as%20a%20follow-up%20investigation%20into%20significance%20of%20any%20given%20summary%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20questions%20are%3A%3C%2FP%3E%3CUL%3E%3CLI%3Eare%20those%20two%20fields%20redundant%20in%20many%20or%20all%20of%20the%20cases%3F%3CUL%3E%3CLI%3EAnd%20if%20so%2C%20can%20one%20of%20them%20be%20eliminated%20altogether%20from%20your%20pivot%20table%2C%20so%20that%20the%20other%20can%20be%20the%20basis%20for%20a%20cross-tabulation%3F%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3EOr%2C%20for%20the%20purposes%20of%20this%20summary%2C%20assuming%20you%20keep%20the%20dates%2C%20does%20it%20matter%20which%20amendment%20is%20applicable%20to%20a%20given%20row%3F%3CUL%3E%3CLI%3EWe%20know%20that%20it%20was%20amended%2C%20so%20who%20cares%20%7Bfor%20the%20purposes%20of%20this%20summary%5D%20which%20specific%20amendment.....this%20is%3F%3CUL%3E%3CLI%3EYou%20can%20always%20click%20on%20a%20cell%20in%20a%20Pivot%20Table%20to%20have%20Excel%20highlight%20the%20detail%20rows%20that%20are%20summarized%20in%20it%20(Did%20you%20know%20that%3F)%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2399469%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Pivot%20-%20Move%20Row%20to%20Column%20(wants%20to%20sum%20the%20text)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399469%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehi%20Mathes%2C%20thank%20you%20for%20the%20response.%26nbsp%3B%20To%20make%20it%20simple%2C%20can%20you%20drag%20the%20decription%20field%20to%20the%20column%3F%26nbsp%3B%20You%20see%20that%20it%20sums%20and%20it%20is%20text.%26nbsp%3B%20%26nbsp%3BDo%20you%20have%20a%20crafty%20way%20of%20being%20able%20to%20display%20a%20text%20field%20in%20the%20column%20but%20simply%20display%20(no%20summing).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2399475%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Pivot%20-%20Move%20Row%20to%20Column%20(wants%20to%20sum%20the%20text)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399475%22%20slang%3D%22en-US%22%3EHi%2C%20it%20looks%20like%20I%20have%20found%20the%20answer%20here%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fexcel-tips%2Fpivot-table-with-text-in-values-area%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mrexcel.com%2Fexcel-tips%2Fpivot-table-with-text-in-values-area%2F%3C%2FA%3E%3CBR%20%2F%3Eyou%20have%20to%20add%20a%20calculated%20field%20but%20when%20creating%20the%20pivot%20you%20need%20to%20click%20a%20special%20button%20%22add%20to%20data%20table%22%20and%20the%20formula%20to%20use%20is%20%3DCONCATENATEX(Values(Table1%5Byour%20field%5D)%2C%20Table1%5Byour%20field%5D%2C%20%22%2C%20%22)%3CBR%20%2F%3Eseems%20very%20odd%20because%20I%20am%20not%20concatenating%20anything%20but%20it%20does%20work.%20I%20have%20tested%20it.%3CBR%20%2F%3E%3CBR%20%2F%3Ethank%20you.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Contributor

Hello, 

 

I have a pivot and I do not like the format of it. 

I want to move the the row for "description" (the red starred in the pic below) from the row to the column but when I do this it wants to sum this text field.  How can I move the row to the column but make it display the text as it is?  

 

thank you

 

thank you

7 Replies

@Tony2021 

 

The Pivot Table, as you know, is a powerful and useful feature of Excel; many consider it one of the most useful capabilities. Its power comes in being to summarize data in a cross-tabulated way, summarizing where fields are in common by row and/or by column.

 

One of the difficulties with your data, I think--not sure since I'm not fully familiar with it--is that each row in your database is actually distinctive in at least one of the fields. So it's hard to even know what kind of summary you're expecting. Here, to illustrate, were a couple of possibilities I came up with.

mathetes_0-1622394978463.png

 

mathetes_1-1622395011048.png

 

In my own experience with Pivot Tables, I've often found it necessary to play around with the various combinations and possibilities before finding the format/layout that provides the summary I have in mind. You may have done a lot of that experimentation already......it might be that what you need to do is change the raw data itself, not to make it inaccurate.... but to make it more summarizable.

 

For example, you have the "Date of Amendment" field specified, AND you say under description, such things as "1st milestone extension, 2nd amendment of the LC," which strikes me not only as wordy but perhaps only helpful as a follow-up investigation into significance of any given summary number.

 

My questions are:

  • are those two fields redundant in many or all of the cases?
    • And if so, can one of them be eliminated altogether from your pivot table, so that the other can be the basis for a cross-tabulation?
  • Or, for the purposes of this summary, assuming you keep the dates, does it matter which amendment is applicable to a given row?
    • We know that it was amended, so who cares {for the purposes of this summary] which specific amendment.....this is?
      • You can always click on a cell in a Pivot Table to have Excel highlight the detail rows that are summarized in it (Did you know that?)

@mathetes 

 

hi Mathes, thank you for the response.  To make it simple, can you drag the decription field to the column?  You see that it sums and it is text.   Do you have a crafty way of being able to display a text field in the column but simply display (no summing). 

 

thank you. 

Hi, it looks like I have found the answer here:
https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/
you have to add a calculated field but when creating the pivot you need to click a special button "add to data table" and the formula to use is =CONCATENATEX(Values(Table1[your field]), Table1[your field], ", ")
seems very odd because I am not concatenating anything but it does work. I have tested it.

thank you.


I did drag it there, and just saw that it made the set of columns ridiculously long....and useless. I didn't see what you seem to be describing. Can you post a picture of what you're describing and don't want? But also come up with some better description or manual creation of what you do want. I gather that the two images I sent back don't come any closer to the cross-tabulated result than what you already had. It would help if you gave feedback rather than just reiterating what your first post said.
best response confirmed by Tony2021 (Contributor)
Solution

Not something you'll find on a Mac @mathetes , I'm afraid.

You'll need the DAX formula to create a measure in the Data Model, that will allow you to drop a text in the value field of the pivot table. The end result then looks like this:

Screenshot 2021-05-31 at 10.16.38.png

 

@Tony2021 

It very depends on what we would like to show for grouped rows (e.g. for entire month, quarter or year).

If we don't group or only first, when something like

Description_:=FIRSTNONBLANK(Table1[Description], "")

could work. If all descriptions in period, when iteration with CONCATENATEX().

Yes, in any case some measure.

 

Thanks for that explanation. Yet another (fortunately minor) Mac limitation.