How to Create a Beeswarm Chart in Tableau using Excel - My Way

on

Bee swarm plots are similar to strip charts and are designed to show the underlying distribution of the data but unlike strip charts, the data is arranged in a way that avoids overlapping. I have used it for my entry to the Data Vizard segment of the Data Arcade Tournament (DAT 2022). To create the chart as seen in the entry above, I have developed my own method by tapping on Excel. In this post, I will share my way of creating a Beeswarm Chart in Tableau via Excel. Admittedly, the formula used for the entry is different as I have "improved" the formula for this tutorial whereby the circles can be centred without the gap in the middle even if the number of circles for value is even.

For other methods to create a Beeswarm Chart in Tableau, please click on the links to see the blog posts by the following authors:

You may also check out the Beeswarm generator by Tristan Guillevin on LaDataViz.

1. Set Up the Data in Excel
  • Leave the first row of the Excel sheet blank
  • Have at least 4 columns: OddEven, Item, Value, First Point and Pos
  • Item refers to the unique reference for each dot in the Beeswarm
  • Value refers to the value of each Item

2. Input Formula for OddEven
  • In reference to the Value, input the following formula in the OddEven column to calculate if the total of the number of times the Value is odd or even number of circles at the values
  • Formula is =COUNTIF(C:C,C3)
  • Then fill the cells downwards

3. Input Formula for First Point
  • In reference to the Value, input the following formula in the First Point column to determine if the item is the first appearance for the value
  • Formula is =IF(C3<>C2,TRUE,FALSE)
  • Then fill the cells downwards



4. Input Formula for Pos
  • Input formula with reference to OddEven, First Point, and the cells above (hence, the blank first row)
  • Formula is =IF(AND(ISEVEN(A3),D3=TRUE),0.5,IF(AND(ISODD(A3),D3=TRUE),0,IF(AND(ISEVEN(A3),D3=FALSE,E2>0),E2*-1,IF(AND(ISEVEN(A3),D3=FALSE,E2<0),E1+1,IF(AND(ISODD(A3),D3=FALSE,E2=0),E2+1,IF(AND(ISODD(A3),D3=FALSE,E2>0),E2*-1,IF(AND(ISODD(A3),D3=FALSE,E2<0),E1+1)))))))
  • Then fill the cells downwards

4. Import Data into Tableau and Place Pills in Place
  • Value as Continous Dimension in Columns
  • Pos as Continous Dimension in Rows
  • Item as Discrete Dimension in Detail under Marks
  • Set Marks to Circle or Shape
  • Adjust the size of the chart to scale
  • Hide the Columns by unchecking "Show Header" after right-clicking the Value pill in Columns



5. Publish your Beeswarm Chart!