Interactive Storyteller – Instruction on Excel

OVERVIEW

Interactive Storyteller is the idea of using visualization to transform Excel report into presentable slides. This visualization utilizes the Option Buttons in Form Control to create different visualization slides in the same graph. The Interactive Storyteller is applied for explaining reports about a time frame data or the growth of a products / segments with additional comments and presentation headlines for each important data point. The below samples are showing the growth of a product called Moonville from 2013 to 2015 with important time period of growing process. This sample is illustrated with data and visualization from the author of the book Storytelling with Data.

6

  • Stage 1 :

1

  • Stage 2 :

2

  • Stage 3 :

3

  • Stage 4 :

4

  • Stage 5 :

5

  • Stage 6 (Full) :

capture

Concept: This is one line graph showing the number of active users through time periods. The top buttons are option buttons which provide different data reflected by the visualization when clicking. The top presentation note is also changeable by the option buttons, which create multiple stages of presentation in one graph.

PREPARING DATA

  • Require: Line chart; If statement; Option Buttons in Form Control
  • Prepare data: Data tab

7.PNG

  • Data elements:
    • Data table: This include the real data table of the whole period (Year, Month, Active Users) and preparation data for two series on the graph (Series 1 presents the dimmed line of the past period and Series 2 presents the darker line showing the period in each stage).
    • Control Button: This cell use to connect all the Option Buttons to this cell value (The number represents the option number: 1 – 6).
    • Comments: This is the table of all the presentation headline on top of each stage. And the cells J2 presents the actual cell link to the headline. (Beginning is the first headline when not clicking on any option yet).
    • Final Comments: This is the list of final comments in the final stage showing all the comments in each period. (only appear for option 6)

FORMULA

  • Data table: use IF statement in Series 1 and Series 2 to return the value based on the option. (Ex: Stage 3 presents 2 series, series 1 from Sept 2013 – Dec 2013 and series 2 from Dec 2013 – Mar 2014 -> We need Series 1 and 2 in each option show the number based on the period they cover otherwise return NA()). The If statement will changed for each period (because each period will show the number differently based on the option – reference from Excel file.
_ Year, Month, Active User (A - C): actual data
_ Series 1 (D): D2 = IF(OR($G$2=3,$G$2=4,$G$2=5),C3,NA())
_ Series 2 (E): E2 = IF(OR($G$2=1,$G$2=2,$G$2=6),C3,NA())
  • Control Button: This button will be set up later in Form Controls
  • Comments: Each of the comments from row 3 are preset up explanation about the data. Only cell J2 has If statement to pick up the comments based on each stage (Control button – cell G2)
_ Comments (J): J2 =IF(G2=0,J3,IF(G2=1,J4,IF(G2=2,J5,IF(G2=3,J6,IF(G2=4,J7,IF(G2=5,J8,J9))))))
  • Final Comments: show the comments and the date only for stage 6 -> IF statement when G2 = 6.
_ Final Comments (L): L2 = IF(G2=6,"Moonville..."," ") 
_ Date (M) : M2 = IF(G2=6,"Sep - Dec 2013"," ")

CREATE GRAPH

  • Insert Line Graph: Use two data Series 1 and Series 2

8.PNG

  • Colors and Edit: 
    • I use a dimmed blue color (Transparency 50%) for Series 2 And a blue color (Transparency 0%) for Series 1. 
    • Data point shows only the first and the last point of the Series 1 (deleting all other Data Labels of the months between) and show no Data labels or Series 2. This process will be setup for each options.
    • Data labels format as a circle in white color with blue border to flag out easily.
  • Set up Option Buttons in Form Controls
    • Open Develop / Insert / Form Controls – Option Button
    • Create 6 Option Buttons by copy and paste10.png
    • Right click on one button and select Format Control
    • 10.png
    • In Control / Cell Link -> Set up the Cell link to the Option Button Cell in data sheet (Data!$G$2)

11.PNG

  • Arrange the Option Button in the correct arrangement from 1 – 6.
  • In the last option (Option Button 6), we add more text box in the graph to show notes for each period (like below).

9.PNG

  • Link each Text Box and the Headline cell (B2) with correct Comments (Data!J2) and Final Comments and Date.

TIPS AND OPINIONS

  • Two series with a dimmed line and a solid line to compare the historical trend but still focus on the current period.
  • Notes and summary about each period helps viewers to summarize easier and to understand the whole pictures.
  • Interactive Option Button in a smooth flow can easily deliver the message to the viewers without an actual presentation of the report builder
  • This visualization can be easily transferred to PowerPoint with the sequence of images from different stages in each slide.

REFERENCES:

  • Knaflic, C. N. (2015). Storytelling with data: A data visualization guide for business professionals. Hoboken, NJ: Wiley.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s