Excel – How to draw an excel chart from two columns

I assume this is an XY scatter chart, not a line chart. You can create dynamic range names and plug these into the chart. If each data series has its own distinct set of time stamps, i.e. there are different time stamp values in each series, then you need to create range names for each X and each Y pair. If the data shares the same time stamp, you only need one range name for the X labels and can use offsets for the Y data.

In the screenshot below, the X labels are in the chtLabels range name that is defined with the formula

=INDEX(Sheet1!$A:$A,MATCH(StartTime,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(EndTime,Sheet1!$A:$A,0))

The Y value range name is defined with

=OFFSET(chtLabels,0,1)

The marching ants show the current content of the range name chtLabels

Then plug the range names into the chart source:

NOTE: This question is vague in the sense of exactly what you are dealing with, but there is enough information here to answer the question. You will have to alter things in this answer to fit your exact scenario.

The easiest way may be to create some helper cells to count the occurrences which fall into the groups you have outlined. Then from those results you can create a chart which has the bars as you described.

In the example below, I used the data you described with the COUNTIFS function (click the link to read more about the function).

To do this, choose cells outside your working data table. Then enter the formula so it checks the range of your data. I added the data to A1 thru G1. Then I used A3 and B3 for labels while the formula goes in the cells directly below. In A4 the formula counts numbers greater or equal to 1 and also numbers less than or equal to 3. You will notice the range must be given for each criteria.

=COUNTIFS(A1:G1,">=1",A1:G1,"<=3")

Using the same format, the formula was placed in B4 to count numbers between 4 and 5 is as follows;

=COUNTIFS(A1:G1,">=4",A1:G1,"<=5")

With the numbers counted, select A3 thru B4 and choose to insert a chart of your choice from the Insert tab.