How to Make a Stiff Diagram in Excel

By Chad Davis

Plot your cation and anion concentrations using Microsoft Excel to create a Stiff diagram. While Excel doesn't natively provide support for charting Stiff diagrams, you can create a Stiff diagram from scratch using the Illustration commands located on the Insert ribbon in Excel. After you’ve inserted text boxes to represent your cation and anion labels, use the Freeform shapes tool to plot each value of your Stiff diagram. Your completed Stiff diagram can represent the ion concentrations in a substrate -- commonly used in water quality testing.

Step 1

Enter labels for your data in the first row of a new Excel worksheet. For example, type "Cations" into cell A1, "Cation Concentrations" into cell B1, "Anions" into cell C1 and "Anion Concentrations" into cell D1.

Step 2

Enter your data values for each data set into the appropriate cells below your column labels.

Step 3

Click the "Insert" tab, click the "Shapes" drop-down button in the Illustrations group and then select "Rectangle." Click and drag in an empty area of your worksheet and then release to draw a rectangle.

Step 4

Click the "Shape Fill" drop-down in the Shape Styles group of the Format ribbon and then select the color "White."

Step 5

Click the "Insert" tab. Click the "Text Box" button in the Text group and then draw a vertical text box on the left side of your diagram to represent your cation categories.

Step 6

Repeat the process to draw a text box to the right of your diagram representing your anion categories

Step 7

Enter your labels for your cation and anions into the two vertical text boxes. For example, enter "Na," "Ca," and "Mg" on the left to represent cations and then "Cl," "HCO3," and "SO4" on the right side to represent anions.

Step 8

Click on the "Insert" tab, click the "Shapes" button and then select "Line" from the line section. Click and drag to draw a line across the center of your rectangle. If necessary, adjust the size of your rectangle so that the x-axis aligns with the Ca and HCO3 labels.

Step 9

Repeat the process to draw a vertical line down the middle of your rectangle to represent the y-axis divider between the cations and anions, extending the line above and below your rectangle.

Step 10

Insert a horizontal text box directly above the top of your diagram.

Step 11

Enter your x-axis labels into the horizontal text box. For example, space the following values evenly across your diagram: "10," "5," "0," "5," and "10." Align the zero value with the center y-axis line on your diagram.

Step 12

Insert another text box above your x-axis labels.

Step 13

Enter "Cations" into the left side of the text box, "Anion" into the right side. You can also label your calcium concentration measurement aligned with the center y-axis line. For example, "meq/L" or "meq/kg."

Step 14

Click the "Insert" tab, click the "Shapes" drop-down in the Illustrations group and then click the "Freeform" tool in the Lines section; Freeform resembles an abstract filled shape.

Step 15

Draw the data points for your Stiff diagram using the Freeform tool while referencing the cation and anion concentrations you initially entered into your worksheet. For example, click the location in your rectangle to represent your first cation data point, move your cursor to your first anion data point and then click to place your next point., Continue working clockwise until you have plotted all of your data points. Your Stiff diagram will automatically fill when you complete the shape by clicking on your starting cation data point.

Tips & Warnings

  • Information in this article applies to Microsoft Excel 2013. Procedures may vary with other versions or products.