Excel does not have a Stiff Diagram chart option in the charts. But, we can create a Stiff Diagram in Excel by using a scatter chart and a freeform diagram in the shapes tool. Tweaking a bit of the coloring of the chart will give us the perfect Stiff Diagram. Here's how.
Creating a stiff diagram
These are a few basic steps on how to create Stiff Diagram in Excel.
Step 1: Enter the values of cations and anions. Use the anion values in the negative as shown below.
Step 2: Select 1, 3, and 5 (press Ctrl key, so select cells individually) and copy (ctrl+c).
Step 3: Right click on empty cell -> Paste special -> Transpose
Step 4: Select the data in this table (2, 3, 4)
How to plot a stiff graph?
Step 5: Now go to Insert -> Charts -> Insert Scatter
Step 6: Now assign the data labels according to the values in the first table. Select the blue dots by clicking -> right clicking -> format data labels.
Step 7: Select a value from the cells and then provide the range of cation names.
Step 8: Format Data Labels -> Size and Properties -> Text Direction -> Rotate 90 degree
Step 9: Select free form diagram from Insert -> Shapes -> Freeform. Click on the dots to connect them into the shape below.
Step 10: Remove the gridlines. Copy the chart and paste it as an image. Then Format -> Rotate 90 degree.
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-clicking -> axes -> Primary horizontal axis.
Your Stiff Diagram is ready to use.