Excel stacked columns chart target line

I have searched but unfortunatelly did not find any answer to my issue.

I have the following table:

_ |   A   |   B    |   C    | 
-----------------------------
1 |  feb  | yes/no | target |
2 |  feb  |  yes   |  90%   |
3 |  feb  |   no   |  90%   |
4 |  feb  |  yes   |  90%   |
5 |  feb  |  yes   |  90%   |
6 |  feb  |  yes   |  90%   |
7 |  feb  |  yes   |  90%   |
8 |  feb  |  yes   |  90%   |
9 |  feb  |  yes   |  90%   |
. |  mar  |  yes   |  90%   |
. |  mar  |   .    |   .    |
. |  mar  |   .    |   .    |

With the table above I created a pivot table:

| Row Labels | Yes  | target of Yes |  No  | target of no |
 ---------------------------------------------------------
| feb        | 87%  | 90%           | 13%  | 90%          |
| mar        | 95%  | 90%           |  5%  | 90%          |
 ---------------------------------------------------------

My problem is that I do not need "target of Yes" and "target of No".. This is the general target of it. If I create a chart I get two target lines. One for "Yes" and one for "No".

I just need Target (90%) in the pivot table, like so:

| Row Labels | Yes  |  No  | target |
 -----------------------------------
| feb        | 87%  | 13%  | 90%    |
| mar        | 95%  |  5%  | 90%    |
 -----------------------------------

Like that I would get a stacked chart something like:

100%    -13%-       --5%-
95%    |     |     |-95%-|
90%  --|-----|--------------  >> target line
85%    |-87%-|     |     |
80%    |     |     |     |
75%    |     |     |     |
70%    |     |     |     |
60%    |     |     |     |
50%    |     |     |     |
.%     |     |     |     |
0%     |     |     |     |
       -------------------
    |    feb    |    mar    |

I hope it is understandable.

How can I achieve that? I need it in order to add the 99% mark in the stacked chart of excel.

Thank you all in advance for any suggestion.

Serge Inácio

Posted 2019-05-28T14:13:57.640

Reputation: 111

Can you please edit your question to provide more info? The expected result, what you've tried and how it fails to achieve the desired outcome would help.

– cybernetic.nomad – 2019-05-28T18:53:00.197

Hello @cybernetic.nomad, Thank you for your reply, I added my expected result. – Serge Inácio – 2019-05-29T07:17:02.670

I don't think that's possible with pivot table. Here is a tutorial on how to add horizontal line to a chart, but unfortunately that doesn't work for charts based on pivot table. Your options are: 1. add a line manually to the chart (insert - shapes - line) 2. copy data from pivot table to another location, so source of chart won't be pivot table. – Máté Juhász – 2019-05-30T06:42:58.287

Excel stacked columns chart target line

Answers