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
24 Responses
I’d suggest simply using the subtotal function and filtering the data using the Win/Loss column. You get the same results and the formula is more comprehensible.
@John
That is one option.
There are times however when you want to see the whole data table or a filtered subset and still want to produce summary reports against an unfiltered field.
Is there a particular reason why you are using a comma and the unary (–) operator for the second array in the SUMPRODUCT formula? It seems to work the same if you were to string the arrays together using the asterisk (*). The advantage is that SUMPRODUCT treats the entire string of arrays as a single array.
@Mathew
Your correct, There is no difference.
I thought it may have been easier to explain this method.
Is there a way to do this on a large set of data? As in ~100,000 rows? When I try I get an error because the formula becomes too long. It says the max length of a formula is 8,192 characters. Excel 2010.
How do I incorporate a specific text within a cell for the second array. For instance, – -(C7:C13=”Apple”)
when I chose a specific text the formula does not work.
@RB
I am not sure what is the issue as if I use the sample data in the post the following work fine
Count:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), –(C7:C13=”L”))
Sum:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),(C7:C13=”L”)*(D7:D13))
You may want to check that there are no leading or trailing spaces in your list of Apples
I should have given a better explanation. Heres my situation. I have a column with cells filled with names like Column 1, Column 2, Pier 1, Pier 2, etc. If the cell just contained Pier and searched for that it works. But because it has other characters in the cell its not recognizing the pier. So how can I extract specific characters of a string of text in this formula?
Hopefully this was a better explanation
Hello-
This formula works pretty well for me except that it slow down excel and prevents some of my macros from working. I was wondering if there was a way to program this in VBA so that excel isn’t always trying to recalculate it. I would like to use a push of a button to get it to run then paste in a cell.
Thanks!
I am trying to sum filtered data in a column, but would want to ignore the negative values in the column. How to go about doing this?
@Akshay
Why not just add a filter to that column to only show the values greater than zero?
The negative values are required for reporting purposes, but their effect on the total is distorting the required output. Please advise.
@Akshay
I’d suggest making a post in the Chandoo.org Forums
http://forum.chandoo.org/
Attach a sample file to simplify the task
I have this working for counting and summing, however, I have a list and for the second array, I need a criteria. That is, I’m looking for b13:b200=”01.??.??” or =left((a1,2) or something like that. These types of criteria matches do not appear to work as I get a blank as a result.
Thanks!
@Bob
As your formula b13:b200=”01.??.??” looks like you are trying to check the first day of the month of the range
What about trying Day(B13:B200)=1
Hai Experts,
i understood this formula well and working fine in MS Excel 2013
but when the same am trying to place in google Spreadsheet it shows error as
“SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 2014, column count: 1.” and as a result #VALUE! Appears in cell.
Can anyone please help me how would i get it done in Google Spread sheet
or is there any other formula as a substitute for this.
Thank you very much.
thanks for providing this.. but why does excel keeps on prompting Circular referencing in cell D3?
@Vivek
I don’t know
I just downloaded the file and it is working fine and not showing that error
Goto the Formulas, Calculation Options Tab and check that Calculation is set to Automatic
What version of Excel and Windows are you using ?
I know that this forum is for MS Excel, but I am trying to help someone who is working in Google Sheets. The below formula works in Excel but Google Sheets returns:
“SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 39000, column count: 1.” and as a result #VALUE! Appears in cell.
This is the same problem asked by Srichirin above. Does anyone know if there is a formula for Google Sheets that will replicate what MS Excel does?
=SUMPRODUCT(SUBTOTAL(3,OFFSET($C$6:$C$39500,ROW($C$6:$C$39500)-MIN(ROW($C$6:$C$39500)),,1)),- -($C$6:$C$39500=H1),($D$6:$D$39500))
Trying to find a SUMPRODUCT formula that counts the word Closed by date for the last 7 days in a filtered list.
=COUNTIF(M:M,”>”&TODAY()-7) works ok for unfiltered count Column M contains Closure dates (blank if open) and Column L is Status Open or Closed
@ Terry
Please ask the question at the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file to ensure a quicker more accurate answer
I used this formula and worked like a charm! But, now I’ve been requested to use it but adding not one but two criteria in the same formula. For instance the sum I was doing added negative and positive numbers. I’ve been asked to use the exact same formula but adding that only positive numbers were considered… any idea on how to do this?
How exactly do you do sum filtered cells when two criteria are need not just one?
Thank you so much brother literally I have been struggling since morning to get the sum of the filtered category, however, after reading your blog attentively i got my solution, so thanks a lot once again.