Contingency tables (also called crosstabs or two-way tables) are used in statistics to summarize the relationship between two categorical variables. They provide a basic picture of the interrelation between two variables.Suppose there are two variables, sex (male or female) and applicant status (Admitted, rejected) to a graduate school program. We can create a 2x2 table to show the numbers of individuals who are male accepted or rejected, female accepted or rejected. Such a table is shown below:

One of the ways to represent 2x2 tables graphically is shown below. In this graph there are four quadrants representing each cell of the 2x2 table. The areas of the squares are proportional to the cell count.

2. On the worksheet, type 0 in cell B18 and 1 in cell B19. Select the range B18:B19 and drag down the fill handle to cell B1118 to fill a series of numbers from 0 to 1,100.
3. I’m going to represent each quadrant using an Area chart with 1,101 data points each, split in half on each side of the vertical axis—that is, 550 data points on each side of the vertical axis. Therefore, type this formula = (COUNT(B18:B1118) - 1) / 2 in cell D12 to calculate the midpoint.
4. Next, I’ll calculate the size of each square by taking the square root of the relative frequency. Hence, type the following formulas in the respective cells:
       C15 --> = SQRT(J5)
       D15 --> = SQRT(K5)
       E15 --> = SQRT(J6)
       F15 --> = SQRT(K6)
5. Next, I need to rescale the size of the quadrants from 0-1 to 0-550 scale. Hence, type this formula = ROUND(C15 * $D$12,0) in cell C16. Then, Copy/Paste cell C16 into cells D16:F16.
6. Next, I need to calculate the limits of each quadrant on the horizontal axis. For example, the upper left quadrant should start at data point 268—that is, the difference between 550 (the midpoint) and 282 (the size of the square). Hence, type the following formulas in the respective cells:
        C17 --> = D12 - C16
        D17 --> = D12 + D16
        E17 --> = D12 - E16
        F17 --> = D12 + F16
The worksheet should look like this (by adding some labels):

HOW TO CREATE A FOURFOLD CHART IN EXCEL

7. Type the following formulas in the respective cells:
       C18 --> = IF(AND($B18 >= C$17,$B18 <= $D$12),C$15,0)
       D18 --> = IF(AND($B18 >= $D$12,$B18 <= D$17),D$15,0)
       E18 --> = IF(AND($B18 >= E$17,$B18 <= $D$12),-E$15,0)
       F18 --> = IF(AND($B18 >= $D$12,$B18 <= F$17),-F$15,0)
8. Select the range C18:F18, press Ctrl + C to copy the cells, then select cells C19:F118 and click Home > Paste and pick the Formulas paste option. The worksheet should look like this:

9. Highlight the range C18:F1118 and then click Insert > Insert Area Chart > Area. The chart should look like this:

10. Right-click the chart and then, on the shortcut menu, click Select Data.
11. In the Select Data Source dialog box, edit the Series1 data series by clicking the Edit button under Legend Entries (Series).
12. In the Edit Series dialog box, select cell C14 in the Series name edit box. Click Ok.
13. Repeat the same procedure for Series2, Series3 and Series4 by referring to cells D14, E14 and E14 respectively.
14. Click on the Edit button under Horizontal (Category) Axis Labels. In the Axis Labels dialog box, select the range B18:B1118 under Axis label range. Click Ok twice to close the Axis Labels and the Select Data Source dialog boxes. The chart should look like this:

15. Right-click the Vertical (Value) Axis in the chart and then, on the shortcut menu, click Format Axis.
16. In the Format Axis pane, with Axis Options selected, set the Bounds Maximum to 1 and the Bounds Minimum to -1.
17. Turn off the Vertical (Value) Axis Major Vertical Gridlines, the Chart Title and the Legend by selecting these elements form the chart and pressing Delete on the keyboard.
18. Right-click the Horizontal (Category) Axis in the chart and then, on the shortcut menu, click Format Axis.
19. In the Format Axis pane, with Axis Options selected, turn the labels off by selecting None from the dropdown list Label Position under Labels.
20. Next, click on the Fill & Line icon and click on No Line under Line.
21. Turn off the border of the Chart Area by setting the Border to No line. The chart should look like this:

22. Next, I need to add the vertical and horizontal axis separating the four quadrants. To that end, I’ll use a scatter plot to define the two axis. Hence, add the following data in cells H9:L12 to the worksheet as below:

23. Right-click the chart and then, on the shortcut menu, click Select Data.
24. In the Select Data Source dialog box, add a new data series by clicking the Add button under Legend Entries (Series).
25. In the Edit Series dialog box, enter Horizontal axis in the Series name edit box. Click Ok twice to close the Edit Series and Select Data Source dialog boxes.
26. Select the chart. Click on the Format tab under Chart Tools and then select the Series “Horizontal axis” from the Chart Elements dropdown list as shown in the screenshot below:

27. Under Chart Tools, click on the Change Chart Type icon under the Design tab.
28. In the Change Chart Type dialog box, check the Secondary axis checkbox for Horizontal axis series. Next, choose the Scatter icon from the dropdown list under Chart Type. The chart should look like this:

29. Right-click the chart and then, on the shortcut menu, click Select Data.
30. In the Select Data Source dialog box, select the Horizontal axis series and then click the Edit button under Legend Entries (Series).
31. In the Edit Series dialog box, edit the Series X values to include cells H11:H12 and the Series Y values to include cells I11:I12 as per below screenshot:

32. Click Ok to close the Edit Series dialog box. Next, In the Select Data Source dialog box, add a new data series by clicking the Add button under Legend Entries (Series).
33. In the Edit Series dialog box, enter Vertical axis in the Series name edit box. Next, edit the Series X values to include cells K11:K12 and the Series Y values to include cells L11:L12 as per below screenshot:

34. Click on Ok twice to close the Edit Series and Select Data Source dialog boxes.
35. Select the chart and then click the Charts Elements button located on the upper-right corner of the chart. Next, in the expanded menu, check the Secondary Horizontal option under Axes, as per the screenshot below:

36. Right-click the Secondary Horizontal (Value) Axis in the chart and then, on the shortcut menu, click Format Axis.
37. In the Format Axis pane, with Axis Options selected, set the Bounds Maximum to 1.
38. Next, click the Secondary Vertical (Value) Axis in the chart and then set the Bounds Minimum to -1 and the Bounds Maximum to 1. The chart should look like this:

39. Select the chart. Click on the Format tab under Chart Tools and then select the Series “Horizontal axis” from the Chart Elements dropdown list. Next click on the Format Selection button in the Current Selection group.
40. In the Format Data Series pane, under Series Options, click the Fill & Line icon. Next, turn on the line by clicking on the Solid Line radio button under Line. Next, set the line color to black and its width to 1.75 pt.
41. Next, set the turn off the markers by setting the Marker Options to None under Marker.
42. Repeat the same process for the Vertical axis series. The chart should look like this:

1. First, calculate the totals of each row and column and then calculate the relative frequencies as follow:

43. Turn off the data labels for all axes, by setting the Label Positon to None in the Format Axis pane under Axis Options and Labels.
44. Change the colors of each quadrant to a pallet of your choice.
45. Right-click on the Vertical axis series and then, on the short-cut menu, click Add Data Labels.
46. Right-click on the Series “Vertical axis” Data Labels and then, on the short-cut menu, click Format Data Labels.
47. In the Format Data Labels pane, under Label Options, set the Label Position to Center. Next, click on Value From Cells under Label Contains.
48. In the Data Label Range dialog box, set the Select Data Label Range to include cells C5:C6. Press Ok to close the Data Label Range dialog box.
49. Turn the X Value and the Show Leader Lines options off in the Format Data Labels pane.
50. In the Format Data Labels pane, click on the Fill & Line icon. Under Fill, select Solid fill and set the color to white.
51. Repeat the same process for the Horizontal axis series. The chart should look like this:

52. Adjust the size of the chart’s Plot Area to be square so that the quadrants become squares. Add text boxes for the chart title, sources and annotations. And your chart should look like this:

Views > How to create a Fourfold Chart in Excel