Making Dynamic and Interactive Pictograms With MS Excel

I've had a spot of Blogger's Block lately, but thought I'd try to get back into writing by asking what you think of my Pictogram? Cool huh! Inspired by a Twitter thread this week I thought I'd share the process I used to make it and a few thoughts about why this simple technique excited me so much when I discovered it accidentally and in true "IKEA man" style a while back.

I have used Excel with Key Stage 2 children to support data handling activity for a long time now. It was originally part of our BEON Project toolkit back in the 90's, but the more I use it as a resource development, teaching and learning tool the more I realise how I have only ever really scratched the surface. Even amid the excitement around available online applications such as google docs with its frequently identified opportunities for collaborative data handling activity, and open source alternatives such as open office, it still, after all this time manages to throw up surprises, things you wouldn't expect in an application with it's intended commercial audience.

Why is using Excel to create Pictograms so exciting?

The most powerful affordance of Excel, which it shares with other spreadsheets is its "dynamic" aspect, any chart we make is not fixed, but automatically updated if we change data in the cell range that created it. The speed of rerepresentation is one of the key reasons I would choose to use a spreadsheet for data handling activity in the classroom, (that and of course in real life contexts spreadsheets are usually used for this purpose and not generally "data bases.") With the entry of new data, it is possible to observe immediately the effects that this has on the chart we have made. We can change the scale of a chart and observe the effect that this has on the apearance of the data. We can filter and sort data, and discuss the effect this has on the reader's ability to interpret particular types of question, perhaps we might even structure our filters to match criteria we have identified from a question. We can propose models, make predictions and inferences, by asking what would happen if questions, then change the data, (perhaps using formulae to help) and observe the effect in the charts we have made.

A really nice task I did with year five and six students last year involved collecting and logging data about how we used our time over the course of a week. Using formulae in our spreadsheet to help us calculate the amount of time we spent doing particular activities, how many hours a day we spent in total engaged in tasks the idea being to ensure we didn't overspend our 24 hours. This did yield some interesting thinking points for me later about students and multitasking, but that should probably be saved for another time. In addition to standard formulae we also calculated the average (mean) time we spent doing particular tasks, finding modal and median values for tasks to compare directly time spent across the week, before charting these.

If you have used Excel to create charts with students it is more than likely that before you have had time to turn around they have gone on to discover without your help how to recolour the sections of the charts they have made, passed this on to their neighbours and gone on to play with the fill effects tool. It is this tool which is key to making your chart into a pictogram, since here you can import images to chart columns to change their appearence. Eg

• creating a chart to show the populations of countries,
• comparing the heights/weights of dionosaurs
• surveying traffic or modes of transport we used to get to school

we could insert a downloaded image of each nation's flag, the dinosaur or vehicle type as the fill for it's column.

What may be missed in this process however is the key to turning these charts into pictograms, the tick box that enables you to scale the images, and the text box beside it that allows you to decide on the unit each image in the column will represent.

Making a Pictogram with Excel

Creating the chart

• Input your data to the spreadsheet
• Select the Cells containing the data you want to use by clicking and dragging to highlight it
• Click on the chart wizard and work through the process to develop a column or bar chart, adding axis labels, a title, deciding on the numerical range for the y axis and so on.
• Select where you would like to insert your chart, in the curent page or as a separate sheet.
• click ok

Editing the bars to add a background image

• Click once on the bars to select all and then again on the individual bar you want to change
• Double click to open the colour swatch, and select fill effects
• Click the picture tab
• Click select picture and browse to find the image you want to use in your column
• Click OK

Now if you click OK again the whole image will be stretched to fill the column

Turning Your Bar Chart into a Pictogram

Having followed the process above to insert a stretched image, double clicking and returning to the fill effects dialogue box you will see to the lower right, the "stack and scale to" tick box.

• click in the "stack and scale to" box
• Enter a value for the image to represent in the text box below this eg 1 scales the image to represent 1 unit, 2 scales the image to represent 2 units in the chart and so on.
• Click OK and return to the chart, to see the effect.

Do you want to use your chart as as a Pictogram or a Block Chart?

This affordance of Excel as a tool I think is really useful for helping develop resources to bridge the relationships between chart types. I can make several versions of the same chart, and edit each one to support discussion. Eg

• A bar chart (with all chart features included, but no scaled images in my columns)
• A Block chart (with all chart features included, with scaled images in my columns)
• A Pictogram (with axis scales removed)

With the chart completed I can decide whether I want to keep axes and grids, and if not simply click on and delete chart elements and features I don't want to include.

Pictorial representations must be read, inorder to make meaning from them. Creating charts that include or exclude features allows us to treat them like Mathematical "cloze procedures," that allow inference and engagement with them as meaning making structures.

The nice thing about using a tool like Excel to do this is how we can repeat the chart making process, representing the same data in different ways, and even present the pictogram as a bar chart with complete scales and axes to consolidate the similarities between the chart types. Changing the data in the spreadsheet itself will update all of your representations simultaneously, and save time, in presenting the data for further discussion and review. Adding these as copies, or saving the file as will allow you to keep your original template.

Using these artefacts as tools beyond Excel

When preparing notebooks for the whiteboard, these charts can be selected, copied and pasted directly to the notebook page, and then using hide and reveal techniques focussed discussion can be developed around the charts.

Charts Like these tell "multimodal" mathematical stories. Stories that can be set in a range of cross curricular contexts, and as such these must be explicit through titling and axis labelling primarily. As well as engaging students with the subject matter though it is important to read the scale and legend, to help us to characterise and populate our story. When creating charts to share with students I like to begin discussion with a chart or charts where all written elements have been hidden including scale values.

• I prepare notebooks using the pen tool and white ink, to cover these.
• During the lesson or shared task these are gradually removed using the eraser as discussion evolves around the "shared text."
  

Beginning sessions with charts where no information is visible other than the image representing the data, and asking children to discuss and make suggestions about what story the chart might tell opens up opportunities to evaluate student knowledge and experience around the chart type, as well as to ellicit understanding of how the chart might be used. Asking what other information we might expect to find, and then rubbing to reveal suggestions using the eraser, allows us as a class to gradually build up a picture of the story the chart is telling, before engaging with any interpretation of the representation.

Making Links Between Representations

Starting with the spreadsheet enables multiple representations of the same data to made and edited adding or removing elements to change the form that the representation takes. Inputting new or updated data to the spreadsheet, will dynamically alter the representations made from the range selected allowing comparisons to made as the session progresses. This process is possible with the other tools I explored too when thinking about writing this post, however what made Excel different was the pictogram, none of the other tools I used or had available to me this week allowed this.

As a time saving device this tool has been an exciting find for me, and I hope you find it useful too. Happy playing.