Create a flexible histogram using FREQUENCY() and INDEX()

We can make a chart that will represent different columns of our data with one chart using the INDEX() function

By highlighting two sets of data, we can create a scatterplot the represents the relationship between the two data sets.

The FREQUENCY() function will recalculate when data changes and this can be used to create a histogram that is always representing the current values (unlike the histogram tool).

The Data Analysis Toolpak has a dialog box that will create a histogram. The data and chart are static — if you change the data, the graph will not change.

This shows the size of each data point with a bar that emphasizes the size (rather than just a dot).

This graph shows each point in a data set. The y-axis shows the value of each point, and the x-axis is just the sequence of the points (first to last).

When working with a data set, naming the data set and then using INDEX() and MATCH() to find particular portions of the data is a very common task in Excel.

The MATCH() function can find a particular value in a range. It can be used with the INDEX() function to match particular columns or rows in a data range.

If we have a range of cells in a worksheet, we can access parts of the range by using the INDEX() function – the parameters are the range, the row(s), and the column(s)