How do you produce a timeline of recurring activity types easily using EXCEL? I had some trouble getting EXCEL to do this so I am sharing my technique here. I use this in my research to characterize the engineering design process of different students as they do a LEGO robotics open ended engineering task.
Let’s say you have this data (see below).
Select the Start and Code columns including the header. Produce a marked scatter chart. That produces this chart. It is what we basically want but the duration of each activity is not shown. Delete the legend marked “Code”. I also delete the y axis values 1 to 6 and manually write in the EDP phases using Insert -> Shape. See final example.
The trick is to use custom X error bars (positive only), which is actually what we want to see.
Select the code series and format it. Select error bars, plus, and custom. Then select the duration (elapsed) cells for the custom values of the error bars.
You then format the error bars to be a thick line.
Then erase the points by selecting and formatting those as Marker -> No Marker. Then delete the actual data points.
Here is the result. I also changed the color of the error bars. I have not yet figured out how to change each code’s timeline (error bars) to a different color.
Note that EXCEL correctly handles the overlapping, simultaneous activities 1 and 2 that occur between 2 and 3 minutes.