These duplicate X values will provide the vertical edges of the shaded region.
The formulas for the area chart’s Y values in column E provide the same Y values for both chart series.
We will also use another SQL statement to populate the top right hand side table for calls data when all the 3 drop downs have some options selected.
We need to add a reference to the Microsoft Active X Data Objects Library to be able to use the worksheet as a database table. I usually select the most recent version, however if you are developing a product it will be best suited if you are familiar with the operating system and office version used by the end-user’s system and accordingly select the best version available.
Shading between plotted lines with a light color can enhance some charts.
The shading may help to indicate a target range for the data.
This technique plotted the XY chart data on the primary axes and the Area chart data on the secondary axes.
It also took advantage of a trick using the category axis of an area (or line or column) chart: when used as a date axis, points that have the same date are plotted on the same vertical line, which allows adjacent colored areas to be separated by vertical as well as horizontal lines.
Often I have thought, if I could have write “Select Employee Name From Sheet Where Employee ID=123” and use this on my excel sheet, my life would be simpler. People spend a lot of time thinking whether to use Excel as their database or not.
Eventually they start using Access or SQL Server etc.
The Y values are extended by two cells above and below the calculations, which contain zeros.