Hi guys,
I am trying to create a column chart of call elements, showing what happened in the call at what point. I have attached an example file of what I have attempted so far (call1), and what a finished call would look like (call2).
Because the order and frequency of call parts change on each call, I think that a standard excel graph would be unworkable, as such I have been attempting to use conditional formatting, on a row of cells, where each cell represents a second. The calls are recorded on the Calls tab, then pulled into the table on the left side of the Summary page (this would be hidden) to make all the data in one line, and create a cumulative sum of the seconds. Then the value of the call element is matched against the Legend and using conditional formatting, all numbers in the graph are coloured up to that second.
The idea being that this would be a template that could be adapted depending on the type of calls or what was attempting to be tracked - so for example the legend could be changed to include 'Promotional Offer 1' and 'Promotional Offer 2' if that was something looking to be recorded.
This solution would work, but for each call section it requires 20 rules, and there needs to be between 10 and 15 sections. Lots of rules which are tedious and hard to manage!
I've included Call 2 which I have coloured manually to show what an actual call could look like, with multiple sections of the same colour (if someone is put on hold twice for example).
Does anyone have any suggestions or examples of a more elegant and simple solution? Could an image be used for each section which is stretched and coloured for the duration/type? I had also started to try to setup a macro to automate creating the rules, but again was unsure if this was the best approach.
Any help would be much appreciated.
Thanks!
I am trying to create a column chart of call elements, showing what happened in the call at what point. I have attached an example file of what I have attempted so far (call1), and what a finished call would look like (call2).
Because the order and frequency of call parts change on each call, I think that a standard excel graph would be unworkable, as such I have been attempting to use conditional formatting, on a row of cells, where each cell represents a second. The calls are recorded on the Calls tab, then pulled into the table on the left side of the Summary page (this would be hidden) to make all the data in one line, and create a cumulative sum of the seconds. Then the value of the call element is matched against the Legend and using conditional formatting, all numbers in the graph are coloured up to that second.
The idea being that this would be a template that could be adapted depending on the type of calls or what was attempting to be tracked - so for example the legend could be changed to include 'Promotional Offer 1' and 'Promotional Offer 2' if that was something looking to be recorded.
This solution would work, but for each call section it requires 20 rules, and there needs to be between 10 and 15 sections. Lots of rules which are tedious and hard to manage!
I've included Call 2 which I have coloured manually to show what an actual call could look like, with multiple sections of the same colour (if someone is put on hold twice for example).
Does anyone have any suggestions or examples of a more elegant and simple solution? Could an image be used for each section which is stretched and coloured for the duration/type? I had also started to try to setup a macro to automate creating the rules, but again was unsure if this was the best approach.
Any help would be much appreciated.
Thanks!