This week in the Chandoo.org Forums, Greg asked the question, “I would like to conditionally format the data labels position to be above the plot line in a scatter plot if a certain cell contains ‘True’ and below the plot line if that cell contains ‘False’.”
Greg also wanted a Non-VBA Solution.
This post will describe how this is achieved as well as extend the idea into the fourth dimension.
All the charts in this post are available in the sample file: Download Sample File.
The Concept
The concept applied here to achieve the final result that Greg wants is that charts can use multiple data series.
These data series do not have to be visible but they can, at the same time, have Data Labels or other formatting applied.
The Application
First setup a set of data,
I have used values A to P as X Axis Labels and used a formula =Randbetween(10,20) in column C for the Y Values for the chart
Now add a Data Validation to a cell G3
Goto the Data, Data Validation Tab and select Data Validation
next add 2 columns
D3: =IF($G$3,C3,NA())
E3: =IF($G$3,NA(),C3)
Copy these down to Row 18
Select the Range B3:E18, note it includes the X Axis Labels and Headers
Now goto the Insert, Chart tab and select the chart type you want to use. I have chosen a Line Chart
Excel will draw a Chart with 3 series of lines
Now is a simple job of applying labels and formatting as applicable
The first thing to notice is that the chart has 3 series, Random Value, True and False
We can only see the True series, as it is in front of the Random Value series, The False series is hidden for now.
Select the True Series by Clicking on it
Then Right Click on it and Add Data Label
Excel adds the Data Labels to the True Series
Right click on any of the Data Labels and select Format Data Label
For the True values we will plot them above the Data Point
Change the values as shown above
Right click on the Data Series Line (the orange line) and select Format Data Series
Change the Line Type to No Line
The Orange line is gone and there is now a Blue Line, this is the Random Values series
Note we can still see the Data Labels for the True Series, even though the True Series Line is not visible
You can set or disable markers whilst you are here as well
Next select the False Series, by changing the Data Validation cell to FALSE
We can now see the False Data Series and the Random Values Series which is behind the Grey Line as before.
Right click the False Data Series, Add Data Labels
Then Right Click the New Data Labels and Change there settings to be below
Finally set the False Data Series Line Line Type to No Line
Now we can see the Rand Value series (Blue line) with the Data Labels showing for the False Series below the line
Change the Data validation from True to False and vice-versa and observe that Excel is only showing the series Labels for the Data Series which has values and doesn’t have #N/A errors in Columns D & E
So we are seeing 3 Series and 2 sets of Data Labels, it is just that we have set Two of the Line Types to No Line and Excel doesn’t display Series Values where the Value is the error value #N/A.
Now set the data Validation to True and select the Data Labels Font Color to Blue
Repeat the Process for the False Data Labels and set them to Red
Finally clean up the legend
Select the Chart, then click on the legend
Then click on TRUE and press the Delete Key
Repeat for the FALSE Legend
Our Final Chart
Change the Data Validation cell to True/False to verify that the system is working.
The techniques described above can be applied to most chart types.
Care must be taken with Column and Bar and other cumulative chart types.
Extensions
Having seen how Excel treats the #N/A error we can use that to create a number of variations for our Data Labels
Conditionally Format Data Labels above and below a set value
This is achieved by using a formula that applies to individual data points in each series
so that when a Data Point in a series (>15) is less than 15 it will return a #N/A error and not be displayed and also when a Data Point in a series (<=15) is greater than 15 it will return a #N/A error and not be displayed
Add a Third or more Set of Conditional Data Labels
This is achieved by simply adding a Fourth Data Series to the chart and adjusting the formulas as appropriate
Add Conditional Formatted Text Data Labels to Highlight Points
These are achieved by using the above techniques but instead of Displaying Values for the Data Label Series, we use the Value From Cells option
Add Conditionally Formatted Markers to Highlight Points
This is achieved by using the above techniques but alter the markers for the two helper Columns as well as the Data Labels
Explore
You can explore how these are constructed using the sample file.
All the above charts are shown in the sample file: Download Sample File.
Selecting Chart Series
One of the annoying aspects of dealing with charts and formatting individual series is the ability to select hidden or covered series
Fortunately there are a number of ways to get around this.
Use the arrows Keys
In older versions of Excel, you can select a Chart, then use the Up/Down arrow keys to cycle through all the chart objects.
Once you had the object you wanted Press Ctrl+F1 to bring up it’s format Properties
Unfortunately Microsoft in its wisdom has removed this functionality in recent versions of Excel, so try it, If it works, Enjoy, If it doesn’t keep reading
Use the Tab Menu
If you select a Chart you will see two extra menu items on the Tab Menu
These are the Chart Design and Chart format Tabs
Select the Chart Format Tab
Then Goto the Drop down on the Far Left of the Tab
It contains a list of all the available Chart Objects,
Select the Chart Object you want, then press Ctrl+1 to bring up the format options
Use the Chart Format Menu
If you select a Chart and select any part of the chart press Ctrl+1 and the Format Menu for that object is shown
Now use the small drop down just under the Format Title and select the Object you wish to change
Warning
Despite being able to use the Excel =NA() function to force an #N/A error, which is ignored by Excel, future versions of Excel maybe about to change this behavior.
Some people using the Excel 365 Insider Fast Edition are noticing a new Dialog option.
So keep in mind if all of a sudden this behavior changes, you may have upgraded Excel and introduced this new menu
You can read more about how to use this new functionality here:
http://www.exceluser.com/excel_dashboards/two-business-uses-for-excels-new-chart-feature.html
Comments
If you have any other ideas about how to use this functionality let us all know in the comments below
23 Responses to “Displaying Text Values in Pivot Tables without VBA”
Its possible to display up to 4 text values.
Have a look at the screen shot of an example that I had posted way back at the EHA and figure out how its done !
http://tinypic.com/r/muzywk/6
With Excel 2010 you can use Conditional Formatting to apply custom number formats which can display text. (In older versions you can only modify text color and cell background color, but not number formats.) Using CF allows for an even larger number of different display values.
[...] Display text values in Pivot Tables without VBA [...]
Hey,
Thanks, this helps. But how do you do it for multiple values where there is a huge amount of non repeating text?
@Soumya
The only way to do more than 4 values is to make the Pivot Table manually with formulas, of course then it isn't a Pivot table
You can of course do it with VBA
You may want to have a look at this description of how to do it here: http://www.clearlyandsimply.com/clearly_and_simply/2011/06/emulate-excel-pivot-tables-with-texts-in-the-value-area-using-vba.html
@Soumya
The only way to do more than 4 values is to make the Pivot Table manually with formulas, of course then it isn’t a Pivot table
You can of course do it with VBA
You may want to have a look at this description of how to do it here: http://www.clearlyandsimply.com/clearly_and_simply/2011/06/emulate-excel-pivot-tables-with-texts-in-the-value-area-using-vba.html
[...] Pivot Tables take tables of data and allow the user to summarise and consolidate the data at the same time. This is a great and very fast method of analysis but is restricted to handling mathematical functions on the value field resulting in numerical summaries. – read more [...]
[…] Read more here: Displaying Text Values in Pivot Tables without VBA […]
There is a very good way actually for handling text inside values area.
First you create a special column on the very left side and call it ID, and put unique ID (numbers only), and then create a pivot table with:
Row Labels and Column labels as you like, and in the Values labels use the unique ID number.
Move the unique ID number (copy paste) somewhere to the right and use vlookup to load the data you need using the ID as reference.
It is a bit longer way but for me it works perfectly to combine values as you like in any moment.
hope helps.
Regards,
Jon
Thank you! I finally understand pivot tables thanks to your clear, concise explanations and examples.
Good Day. This is exactly what i have been looking for. However when i try it on my pivot table or even when i try to recreate this exercise using the sample worksheet, i get this error:
"Microsoft Excel cannot use the number format you typed. Try using one of the built-in number formats."
Same thing here, Excel quite did not like the format in my PowerPivot. Any clues as to what may be going on? Thanks.
I have the same thing happening on my end. I'm running a normal pivot table on a .xlsm file.
@Danzi
What format did you use?
can you post the file ?
pls. help in table there is name, pan. amount. i have to make pivot table for example
NAME PAN AMOUNT
MR.X AAAAC1254T 500.00
MR.Y AAABR1258C
MR.A CFVDE2458T
MR.Z AAVCR12548C
MR.X AAAAC1254T
MR.Z AADCD245T
pls. help in table there is name, pan. amount. i have to make pivot table for example
NAME PAN AMOUNT
MR.X AAAAC1254T 500.00
MR.Y AAABR1258C 1000
MR.A CFVDE2458T 2000
MR.Z AAVCR12548C 5451
MR.X AAAAC1254T 45564
MR.Z AADCD245T 4500
how to get pivot tabe so i get PAN no. against Name.
I found an easy way to get text values in pivot table.
I create an other worksheet in wich each cell has a formula that copy the pivot table. The trick is that the formula does a lookup for the numbers in the pivot table.
The formula looks like that:
=IF(ISNUMBER(table!A1);VLOOKUP(table!A1;Code!$A$1:$B$65;2);IF(ISBLANK(table!A1);" ";table!A1))
Code is a worksheet where there is a liste of text /numbers correspondance.
As a bonus The new sheet is easier to format
Additional trick:
In my case, i encoded differents codeid with a power(2, codeId-1) so that summing then is equivalent to concatenate them.
1-A
2-B
4-C
8-D
yields :
5 - AC
14 - BCD
Hi
I want to ask if pivot can display dates in pivot field. As in a column i have customers and in row different items i want to know there last purchase date. anyone help in this??
Hello Guys, Need your help
I am doing some analysis of the cycle time of the product i.e how much time a product takes from manufacturing to the central warehouse.
I have batch numbers for the product and against them i have to pull out the diff. dates
Like the base date is from where the manufacturing start. So i have the batch number,against it's manuf. date. Now i have to pull out the date when it was quality released.
I have the quality released data but the data have duplicates, like i will have two dates or may be three for the same batch. So my main objective is to pull out the date which is latest among them.
BATCH NO. DATE of Mfg. DATE of Quality release
A1 12/4/2014 (HERE I HAVE TO PULL value)
Next Sheet
BATCH NO. DATE of Quality Release
A1 14/5/2014
a2 23/5/2016
A1 12/5/2014
A1 13/6/2014
From this sheet i have to pull up the latest date format of date here is dd/mm/yyy
TIA
[…] needed to present text instead of counts in a pivot table value column. Here is an excellent resource for Excel manipulation, in addition to an overview of pivot […]
This is great thank you.
Wow!!! Excellent!! It helped me a lot.
I am developing training tracking sheet for 200 employees with training completed date. Each employee will be attending 25 courses. How to indicate actual dates in pivot table value field.