Excel: Charts, Customizing Which Columns You Want to Chart

Here is the data set if you want to play along: ExcelCharts2

While Excel does a pretty good job of auto-formatting charts based on your data, sometimes a little customization work is in order. Take this data below for example:

What if I want to create a bar chart of the average readings?

chart1.jpg

Well first we need to make an “Average” column

chart2.jpg

chart3.jpg

I really don’t need all the decimal places. Hit the decimal reduce button in the picture below until you have a column full of integers.

chart4.jpg

Chart Method 1

Just like you would normally, select all the data and go to Insert > Column Chart

chart5.jpg

Right Click on your new chart and Select Data…

chart6.jpg

I don’t want my readings on the X axis, I want my Sensor Names, so click Switch Row/Column in the new window.

chart7.jpg

The X Axis is now A,B,C,D

chart8.jpg

Now, we only want to see the Average  column, so uncheck Reading 1,2,3 

chart9.jpg

Here is our chart

chart10

Chart Method 2

Don’t select any data yet. Just go to the Insert and Select Column Chart

chart11

Click Select Data now from the Ribbon (if you don’t see this, click anywhere on your new blank chart and this should appear in the Ribbon)

chart12

Click Add in the low left box.

chart13.jpg

In Series Name, either type a name or select the column header

chart14.jpg

In Series values: put put your data rows from the Average  column

chart15.jpg

Now we have data loaded into our chart, let’s change the data labels for our X Axis. In the Horizontal(Category) Axis Labels –  click Edit

chart16.jpg

Highlight A,B,C,D from the Sensor column

chart17

Now you see the numbers have been replaced by A,B,C,D

chart18

Here is our chart

chart19.jpg

 

 

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s