A crosstab is a table showing the relation between two or more variables. The table below is a crosstab that shows the number of medals won in the summer Olympics by the top 5 countries during the period 1896 - 2016.

Classic. Simple. Straightforward.

A couple of drawbacks, however. First, the chart needs a legend. Second, the order of the countries within each medal type is the same, hence creating—in many instances—the peak-trough pattern.

This is basically a one-dimensional scatterplot with icons—with some advantages. First, although the country labels are still there, we let each country represent itself—at its location in the graph—by its flag. More intuitive. Second, because we are using position—and not length—to encode the data, it becomes easier to check ranking within each category and across categories.

Lets build the graph in Excel.

First, I need to get the county flags into the worksheet.

1. Select the range C3:C7 containing the country names.
2. With the cells still selected, go to the Data tab, and then click Geography under Data Types.
3. Select the range C3:C7, and the Insert Data button        appears. Click that button, and then click the Image field name to extract the flag of each country.

The worksheet should look like this:

Next, I need to create a scatterplot to locate the countries within the graph.

4. Enter "x position" in cell C10.
5. Enter the number 0.5 in cell D10, the number 1.5 in cell E10 and the number 2.5 in cell F10.
6. Highlight the range C2:F7 and then click Insert > Scatter > Scatter to insert a scatterplot on the worksheet.
7. While the chart is still selected, click Switch Row/Column under Data on the Chart Design tab. The chart should look like this:

8. Next, select cell G3. Click Copy > Copy as Picture... under the Clipboard group on the Home tab.
9. Click the Ok button to close the Copy Picture dialog box.
10. Next, select the "United States" data series in the graph and press Ctrl + V to paste the content of the clipbaord into the graph.
The graph should look like this:

11. Repeat steps 8 - 10 for the remaining countries and the chart should look like this:

12. On the worksheet, right-click the chart and then, on the shortcut menu, click Select Data.
13. In the Select Data Source dialog box, select the United States data series and then click the Edit button under Legend Entries (Series).
14. In the Edit Series dialog box, edit the Series X values to include cells D10:F10.
15. Press the Ok button to close the Edit Series dialog box.
16. Repeat steps 13 - 15 above for the remaining countries and the chart should look like this:

17. Lets declutter. Delete the legend, the chart title and the horizontal gridlines. Set the Shape Outline of the Chart Area to No Outline.
18. Set the horizontal axis Maximum Bound to 3 and its Major Units to 1. Set the vertical axis Maximum Bound to 1,050, its Minimum Bound to 125 and its Major Units to 75. Delete the horizontal axis.
19. Set the Plot Area fill color to grey and the Vertical Major Gridlines color to white and their Line Width to 1.5pt.
20. Set the chart's font to Trebuchet MS, its size to 9 and its color to black.
21. Set the vertical axis Tic Marks Major type to Outside and their color to black. Adjust the size of the chart and the chart should look like this:

22. Add chart title, axis label, data source, country names, Medal names and a warning that the vertical axis is trimmed and does not start at zero. The chart should look like this:

23. It is a beautiful chart, but there is one problem. Germany is overlapping Russia in the Bronze medals category. To fix this problem we need to adjust the x position for these two locations to avoid overlap. For that we need to create two more sets of Xs, one for Russia and one for Germany as per below set up:

A common way to represent crosstabs graphically is the use of the clustered bar chart as shown in the graph below:

Views > Excel charts > How to create a flag chart in excel