Let’s say you have some actual measurements of some parameter over time or a series, and you’re measuring it to see if it falls within the “safe” or “ideal” range. How can you create a visualization to show both the measurements and the safe/idea range values effectively?
That’s what I’m going to answer in this blog and I’ll show you several different types of visualizations that are all very effective in conveying the meaning.
Suppose we are measuring a freshware aquarium water’s pH. We start of by two columns: Date and pH. On each date (say, you record it weekly) you enter the date in Date column, and that day’s pH measure. You want to chart the pH history over time and show a chart to show how close or off the pH readings are from the ideal pH numbers which is a range. You know that the minimum ideal value of pH is 6.5, for the type of creatures you have in the aquarium, and the upper limit is 8.0. In order to show the ranges, you need to enter those values in two additional columns (one for minimum, one for maxium values). Then you can create: 1) a Scatter chart with lines and markers or 2) Stock chart using high-low-close paradigm or 3) Combo chart with stacked columns and a line or 4) Combo chart with stacked columns and markers with no line.
Each chart example is shown below.
Scatter: 
Stock: 
Combo: 
And finally, here’s a more complex combo chart that shows multiple classifications within a parameter. In the following example, suppose we have measured TDS in ppm units of different water bodies. The data points are shown as markers within each water body, and each source has multiple categories (Poor, Fair, Good, Excellent) and it clearly shows where our TDS measurement falls for each water source as red diamonds.
Combo II: 
Which one you should is totally up to you as I think any of the above charts will do. You can further fine-tune it such as adding or removing data values to each data point along the marker, marker and line styles and colors, etc. However, one thing to keep in mind that the Combo chart as shown above will NOT work if the Date column is in true Date data type and will only work if it’s a generic text instead…this is due to a bug in Excel (verified that bug exists even in the latest version of O365, Jan 2024) where it won’t reduce the gap between clustered columns to zero, which is necessary to create the filled range area, if the x-axis is Date type. The workaround is to change the data type or enter the dates in another format so Excel doesn’t automatically consider it as Date values.
Hope you enjoyed this tip.
▛Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino! 
▟