Windmill Data Acquisition Software Logo
20 April 2017

Windmill Software Ltd
Data Acquisition Intelligence
Call now: +44 (0)161 833 2782


Charting with Excel

This article gives tips on charting with Excel for scientific, engineering or manufacturing applications. It deals with how best to present data once you've collected it from measurement instruments, devices and sensors.

Choosing the Type of Excel Chart | Charting Questions Answered


Choosing the Type of Excel Chart to Use

Excel provides a multitude of chart styles and it can sometimes be confusing which one is the best to use. Here we highlight three well suited to data acquisition applications - stock chart, bar & column chart and xy scatter chart.

Stock Chart: Useful for Displaying High, Low and Average Readings

A stock chart shows high, low and close data. This is designed for plotting stock market movements, but can be useful when plotting, say, daily temperatures. To plot the temperature range for each day you would have the date in the first column, the highest temperature of the day in the next column, the lowest temperature in the third column, and, for example, the average temperature of the day in the fourth column - replacing the stock market close data.

Stock Chart Showing Temperature Data

Column and Bar: Good for Displaying Counts

Column and bar charts are useful for comparison of discrete measurements made at regular intervals. For example, if you were counting people entering a building, you might use a column chart to show count totals for a series of days or weeks. To display data from several buildings you could use a stacked column chart, where figures for each building are shown as a part of the total column count.

A column chart has time on the horizontal x-axis and a bar chart time on the vertical y-axis. If you have too many columns, Excel won't display all the column labels. Displaying data in bars removes this problem.

Line Charts Versus xy Scatter

You would usually use an xy scatter chart in preference to a line chart. With xy scatter an independent variable is plotted on the x-axis and variables dependent upon this plotted on the y-axis. When you add a trend line, you can see the relationship between the variables. For example you might see a linear relationship between the concentration of a compound in solution and its absorbance of light.

With Line charts the x values are more like labels than values. They are spaced equally, no matter what their value. Using our above example, suppose you plotted absorbance against the concentration of four solutions of 0, 1, 2 and 6 mM. With xy scatter the line will be straight as the 6mM point is plotted in its correct position 4 units away from the 2 mM point. With a line chart the 6mM point is plotted just 1 unit away from the 2 mM point, giving a steep rise in absorbance and thus non-linear graph.


Frequently Asked Questions About Charting Windmill Data with Excel

Can I Quickly Add None-Adjacent Columns of Data to an Excel Chart?

- Highlight a column
- Move the mouse pointer over the edge of the highlighted data, so it changes to an arrow
- Click and drag the column onto the chart.

How can I overlay scatter and stock charts?

The answer to this is in the Excel corner of Issue 39 of our Monitor newsletter.

How do I label individual points on scatter charts?

See the Excel corner of Issues 41 and 67 of Monitor.

How do I set a chart to automatically update?

See the Excel corner of Issue 215 of Monitor.

Can I set the chart ignore empty cells and zeroes?

See the Excel corner of Issue 63 of Monitor.

How to Plot Random Samples against Time?

See the Excel corner of Issue 171 of Monitor.

How do I get a log x axis and log y axis on the same chart?

Set your chart to be XY scatter type. Double-click an axis and set its scale to logarithmic. Do the same for the other axis. For more details see the Excel corner of Issue 140 of Monitor.

How do I add several y axes to a chart?

See the Excel corner of Issue 110 of Monitor.

How to find a y value from a known x value on an xy scatter chart?

See the Excel corner of Issue 111 of Monitor.

Can I scroll an Excel Chart?

How do I zoom into an Excel Chart?

To zoom into the y axis: right-click the axis, select Format Axis and change the scale. To zoom into the x axis,see the Excel corner of Issue 69 of Monitor.

How do I show only recent data in a Chart?

See the Excel corner of Issue 75 of Monitor.

How do I create a histogram?

See the Excel corner of Issue 93 of Monitor.

How do I annotate a chart?

See the Excel corner of Issue 94 of Monitor.

How do I mark an external event on a chart?

The Excel corner of Issue 136 of Monitor tells you how to add markers to your chart, for example to show when a sale started in a chart of people counts.

How can I paste an Excel chart as a Picture in a Report?

See the Excel corner of Issue 100 of Monitor.

How do I plot one variable against another on a Column Chart?

See the Excel corner of Issue 103 of Monitor.

How do I Insert a Chart with a macro?

See the Excel corner of Issue 104 of Monitor.

Can I add a grid line that moves with the last chart value?

The answer to this is in the Excel corner of Issue 135 of our Monitor newsletter.

How do I add alarm markers to a chart?

See the Excel corner of Issue 137 of our Monitor newsletter.

How do I add maximum, minimum and average grid lines to a chart

The answer to this is in the Excel corner of Issue 223 of our Monitor newsletter.

How do I make a logarithmic chart?

See the Excel corner of Issue 140 of our Monitor newsletter.

Plotting Data Sets with Different Timestamps

See the Excel corner of Issue 156 of our Monitor newsletter.

How do I create a carpet or 3D surface plot

See the Excel corner of Issue 207 of our Monitor newsletter.

Another Question?


    Question:

    

            

    Organisation:

    Email:       

    

More Information