Most of my posts are dedicated to XY scatter charts because those are by far the most common chart types in Excel for engineers. However, there are a few more chart types that we’ll discuss in the next two sections. This section will cover three other types of charts: bar, column, and line charts. These are essentially three different forms of the same chart. Whereas scatter charts are useful for easily visualizing numerical data, these three charts are used when the x-values are text.
In the worksheet, you’ll find torsional fatigue data for four different shaft designs – one with an undercut and others with a few different radii. To create a quick column chart, select the data, go to the Insert tab, and click the Insert Column Chart button. Choose the first 2-D column chart.
To change the color of the columns, click on one of the columns, then go to Format > Shape Fill.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
You can also adjust the width of the columns by reducing the gap between them. Right-click on a column and choose Format Data Series. In the task pane that opens, decrease the gap width. This makes the columns wider and moves them closer together.
To clean up the chart and make it easier to understand the magnitude of the values, you can delete the y-axis that Excel adds by default and use data labels instead. Click on the y-axis and press Delete on your keyboard. Right-click on a column and choose Add Data Labels. To make the text easier to read, click within the chart area, then go to the Home tab and increase the text size with this button:
These modifications make the chart easier to read:
We can change this quickly to a bar chart which has horizontal bars instead of vertical columns. Select the chart, go to the Design tab, and click Change Chart Type:
In the window that opens, select Bar and click OK. When you do this to an existing chart, it will retain the formatting – in this case, the color, gap-width, and text size:
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
One adjustment that you may wish to make is to show the data from smallest to largest. Right-click on the y-axis, select Format Axis. Within the task pane that appears, choose Categories in Reverse Order under Axis Position.
Three-dimensional column and bar charts are an option in Excel, but they may not be the best way to display your data. As an exercise, we’ll change this chart to a 3-D chart. Select the chart, then Change Chart Type in the Design tab. Choose the 3-D Clustered Bar chart button along the top:
The data becomes skewed when displayed in 3-D. To really see this effect, you can rotate the chart. Right-click the chart, select 3-D Rotation and increase the X Rotation to 40°. Now it’s difficult to discern the relative difference between the columns. These kinds of charts are not recommended.
Excel has one more type of chart for displaying data when the X values are text: a line chart. Change the chart type by going to Design > Change Chart Type > Line.
What’s the difference between a line chart and an XY scatter chart? They look similar, but a scatter chart doesn’t handle text inputs well. For comparison, let’s look at these data as a scatter chart:
In the scatter chart, Excel has replaced the text values with numbers. The numbers don’t correspond to anything in the data table, so the scatter chart doesn’t make sense.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]