Best Excel Chart to show the lifecycle of a customer in the company database systems

applesandpears

New Member
Joined
Oct 16, 2012
Messages
15
Hi all!

I am a bit stuck. I have been working on a reconciliation project to find out where all our customers are for a certain product in 6 different systems from:

1. Sales team will put customers on as signed
2. Operation team will put in their system if its live or in production
3. 2nd Operation team will put in thier system if its production and the next two levels
4. Another team will enter when they will get their sims for the product
5. Another team will then put in their system when the product is delivered to the customer
6. Finally the last system will tell us if its been billed.

what i have done is I have I have done a v look up against the customer names and then brought back all the different phases and dates for the above steps - so now have one excel sheet with Customers on the left and different columns with the above status.

What I am stuggling is how to show in a chart and what chart? None seem to me to be able to show the process easily?

The whole lifecycle of the chart from 1-6 as above
To show the variances in the different systems eg if its showing in production in one system why is it not production in the system before? etc

I hope this is clear ;)

I would love to hear some expert opinions and inupt ;)
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Maybe you need some kind of Gantt chart ?
This isn't one of the built in Excel chart types, but it can be replicated using a horizontal stacked bar chart.
 

applesandpears

New Member
Joined
Oct 16, 2012
Messages
15
Hi Gerald,

How can this be replicated with Bar charts? I know excel very well but struggle with charts outside the box!
I could send you the excel file also?
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Please don't send me the file :)

At a very basic level, let's say you have two tasks that you want to plot on a Gantt chart.

Let's say one starts on 1st January and lasts for a week, and the other starts on 20th January and lasts for 2 weeks.

You need source data something like this

TASK...............START..........DURATION
A..................1/1/2013..........7
B.................20/1/2013.........14

Create a horizontal stacked bar chart, with 2 data series, one for START and one for DURATION.
Make the series for START have no colour, no border line, so that it's invisible.

This can then be adapted to be much more complex if you like, such as

TASK............START 1..........DURATION 1........START 2.........DURATION 2.......START 3....
A..................1/1/2013..........7.........................3....................14......................7........
B.................20/1/2013.........14........................0...................7........................7........

and so on.
 
Last edited:

applesandpears

New Member
Joined
Oct 16, 2012
Messages
15
hi Gerald,

Thank you.

The issue is that not all the processes will have dates and some overlap each other? How would I display that in the chart? Can this be done by putting the data in a pivot table?

Thabnks
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
I'm assuming you know what a Gantt chart is, and I'm guessing a Gantt chart is appropriate for you, but maybe it's not, you'll have to decide that yourself.
If you DO want a Gantt chart, then pretty much by definition you have to apply dates.
You might not know all the dates for certain yet, in which case you can estimate dates, or leave items off the chart altogether, or put them only on the axis, without being plotted with bars.

In terms of overlapping processes, it would probably be best to show them on separate rows. It is possible to overlap them directly in Excel, but this can be complicated.

Another option - don't use Excel at all, use a tool designed for creating Gantt charts such as MS Project.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,140
Messages
5,527,059
Members
409,742
Latest member
setam

This Week's Hot Topics

Top