Bee Swarm Chart

I want to create a bee swarm chart with World Cup 2022 quarter finalist teams’ data. Let’s make it step by step in Excel together.

Data I found on internet was as below. I copied the top 10 rows of the whole data here.

NoPosPlayerDate of BirthAgeCapsGoalsClubCountry
1GKDominik Livaković09-Jan-9527340Croatia Dinamo ZagrebCroatia
2DFJosip Stanišić02-Apr-002270Germany Bayern MunichCroatia
3DFBorna Barišić10-Nov-9230281Scotland RangersCroatia
4FWIvan Perišić02-Feb-893311632England Tottenham HotspurCroatia
5DFMartin Erlić24-Jan-982440Italy SassuoloCroatia
6DFDejan Lovren05-Jul-8933725Russia Zenit Saint PetersburgCroatia
7MFLovro Majer17-Jan-9824113France RennesCroatia
8MFMateo Kovačić06-May-9428843England ChelseaCroatia
9FWAndrej Kramarić19-Jun-91317420Germany 1899 HoffenheimCroatia
10MFLuka Modrić (captain)09-Sep-853715523Spain Real MadridCroatia

First, I need to count how many players there are of all ages, on each team. I used COUNTIFS formula for ages from 16 to 40.


I will use a stacked bar to create a bee swarm chart. However, I want to add buffers to both the left and right of the bars in order not to make the bars (bees) of the players of different teams come together and to place spaces between them. Considering the maximum number of players (7) in an age, I set myself an interval: 10.

I calculate each buffer as half of (interval minus the relevant count). For example, for age 20, Croatia’s buffers (Buffer1.1 and Buffer1.2) will be (10-2)/2=4.


As next step, I will create a stacked bar chart with all dataset. Excel set the maximum of the x axis as 90 because we had a total of 80 for all ages.

Then I will change the maximum of the x axis as 80 because I don’t need to see between 80 and 90 in the chart.

After that I will change the fill of the buffer bars to No Fill in order to hide them.

Afterwards, I changed the gap width to 50%.

I removed the border outside the chart area and moved the chart title to the upper left corner for general format editing.

It’s time to create our bees 🙂 I copy and paste a circle inside the first team’s bars so that the size matches the size of the bar representing a single player.

Then I right click on the bars with circles and choose Format Data Series. In the Picture Fill settings, I tick “Stack and Scale with” option, instead of Stretch.

I repeat all the circle fill steps for the other teams. If you want you can change the color of the circles in order to create seperation or categorisation between countries. I used black circles (bees) for all teams.

Then I added the name of each country as a text box.

Somewhere outside the chart, I stacked 5 equally sized rectangles and formatted them with diverging colors. Then I selected and copied all of them together and right clicked on the plot area, selected format, and placed the group with Picture Fill/Clipboard option.

After the final makeups, my bee swarm chart is ready in Excel.

Date: Dec 7, 2022
AUTHOR: Deniz Aksoy

Leave a Reply

Your email address will not be published. Required fields are marked *