Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old April 25th 20, 05:55 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2020
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, May 15, 2019 at 1:24:31 PM UTC-4, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


This is VERY helpful. Thank you for this creative solution. Hopefully, in the future Google makes this a directly supported feature.

I will note if your original column header has a line break (carriage return) in it as such:

Sales
Person

then you will get the dreaded #ERROR! "Formula parse error".

There is a workaround I have found. Temporarily, change the column header you want to reference in the Calculated Field into a single word. In the case above, let's say we changed it to 'Person' (removing the word Sales and the following carriage return from the example above). Now go create the Calculated Field. In this case, the formula would be

=TO_TEXT('Person')

The field will be created in the Pivot Table without error. Now for the interesting part, you can go change the column name back to the original, and Google Sheets is then smart enough to handle the change.

Thanks so much to for the original idea to use the T0_TEXT function. This is a really creative solution.


  #22   Report Post  
Old July 16th 20, 04:40 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2020
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, 15 May 2019 18:24:31 UTC+1, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


Brilliant! Thank you!
  #23   Report Post  
Old August 3rd 20, 03:34 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2020
Posts: 1
Default Using text values in Pivot Tables

On Wednesday, May 15, 2019 at 10:54:31 PM UTC+5:30, wrote:
go to values add value calculated field (at the bottom of the field list) set 'Summarize by' to 'Custom', and use the formula:

=TO_TEXT('field_title')


Great... your solution saved a lot of time for me.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Tables-Text Values? Eden397 Excel Discussion (Misc queries) 0 March 5th 09 02:27 PM
Calculating from values in pivot tables Owen Peck Excel Worksheet Functions 3 September 13th 07 03:31 PM
Pivot Tables - Ranking Values Jeff Reese Excel Discussion (Misc queries) 1 May 1st 07 03:44 PM
Pivot tables with zero/null values Todd1 Excel Discussion (Misc queries) 4 March 28th 06 12:45 PM
Supress Zero Values on Pivot Tables AROR Excel Discussion (Misc queries) 0 April 21st 05 01:23 AM


All times are GMT +1. The time now is 03:10 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017