Stiff Diagram

Excel does not have a stuff diagram chart option in the charts. But, we can create a stiff diagram in Excel by using a scatterplot chart and freeform diagram in the shapes tool. Tweaking a bit of coloring of the chart will give us the perfect stiff diagram. Here's how -

Step 1: Enter the values of cations and anions. Use the anion values in negative as shown below.

anions and cations

Step 2: Select 1, 3, and 5 (press Ctrl key, so select cells individually) and copy (ctrl+c).

select data

Step 3: Right click on empty cell -> Paste special -> Transpose

transpose

Step 4: Select the data in this table (2, 3, 4)

select transposed data

Step 5: Now go to Insert -> Charts -> Insert Scatter

insert scatter

Step 6: Now assign the data labels by the values in the first table. Select the blue dots by click -> right click -> format data labels.

format data labels

Step 7: Select value from cells and then provide the range of cation names

provide cations range

Step 8: Format Data Labels -> Size and Properties -> Text Direction -> Rotate 90 degree

rotate 90 percent degree

Step 9: Select free form diagram from Insert -> Shapes -> Freeform. Click on the dots to connect them into a shape as below.

insert shapes freeform

Step 10: Remove the gridlines. Copy the chart and paste as an image. Then Format -> Rotate 90 degree.

stiff diagram ready

To delete the point colors, right click on them and select color as white.

You can also add a horizontal axis to the chart by right click -> axes -> Primary horizontal axis.