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.
Thank you for these observations. As an Early Childhood thaceer, this observation is not surprising. Children use Lego in its various forms to create and communicate from 2 years of age. So why wouldn’t they want to keep communicating this way expressing themselves through creative thinking? At school, we shut them down. We tell them that these ways of thinking are not valued and then we try to teach them other ways of thinking creatively as if it was our idea in the first place.Children naturally want to combine materials. Teachers and parents are the ones who tell them not to be so untidy.