How to Quickly Add a Chart to Visually Compare Data in Excel - Part 1

In previous posts we have covered some of the many ways Excel can be used to help track data. These have included how to identify patterns like date-based patterns, separating text to columns, creating controlled drop-down menus in cells, filtering data, and much more! Excel is a powerful tool that almost anyone can find a way to use if they understand its features and how to use and modify them.

This post discusses another way Excel can format data to find trends more easily: by creating charts that visually represent the data.

A video showing how to quickly add a chart in Excel.

How to Quickly Add a Chart to Visually Compare Data in Excel - Part 1

In this example, we will show how to create a chart that compares the income from customers over a year. Keep in mind this data could be just about anything, this is a simple visual so you can get an idea how charts work.

To quickly add a chart to visually compare data in Excel:

  • First, unless you already have some data you want to work with, create a spreadsheet with some dummy data to practice with. Once you are more comfortable with how charts work, you will be more prepared to enter data into Excel in a way that a chart can easily handle.
  • Highlight the data you want to use to create a chart. This can be done by left-clicking on the first cell and holding the mouse key while dragging the mouse to the last cell you want to include.

Select all the data to include in your chart.

  • Click on the "Insert" tab and select from the types of charts available. If you are unsure of the type of chart to use, click on "Recommended charts". In this example, because the data lends itself to a column or bar chart, we will click on the image of a column chart and select a 2-D column chart from the drop down list.

Click on the Insert tab and select from the many types of charts, or select "Recommended Charts" to the left of the specific types of charts.
Choose a type of chart from the types of charts available in the drop down list you clicked on.

  • The chart will immediately be created within the same sheet as your data.

Once you create the chart it will appear in the same sheet as your data.

Modifying the chart to better represent your data

There are tons of ways to adjust a chart. This post discusses some of the items that affect the actual data and its values, while part two will discuss the aesthetics of how the data appears.

As you can see in the example above, there are some things about the newly created chart that are less than ideal. For example:

  1. The horizontal axis has listed the entries as numbers rather than using the names in the customer column.
  2. The chart was created directly within the sheet rather than on a new sheet all by itself.
  3. The data is skewed because the last entry is not a customer, but rather a sum of all the customers which does not make sense for our purposes.

To fix the labels of an axis:

  • Right-click on the axis labels you want to change and choose "Select Data...". NOTE: Be sure only the axis labels are selected, otherwise you may get different menu options.

Right-click on the axis labels to change then select "Select Data...". If you do not get this menu, you have accidentally selected the wrong element.

  • In the "Select Data Source" box, click on the "Edit" button on the right side above the axis labels.

In this window you can see the current labels and edit them as desired.

  • The Axis Labels box will appear.
  • Select the range of cells in the spreadsheet that have the correct labels.

Selecting the cells with the correct labels will enter them into the Axis label range box in the correct format.

  • Click OK to save the labels.
  • With the correct labels selected, the label names will appear in the list of axis labels below the edit button in addition to the chart.

With the axis labels selected, the right labels appear.

To update where the chart is located:

  • Right-click on the middle of the chart - but not on a data point or label - to select the entire chart. This is probably easiest at the top of the chart. Select "Move Chart..." from the pop-up menu.
  • The menu looks like this:

In the pop-up chart menu, select "Move Chart...".

  • In the Move Chart box, click the radio button to either move the chart to a new sheet or move it into a sheet that already exists. To move it to an existing sheet, click the drop down and select an existing sheet.
  • If you are moving the chart to a new sheet, you can name the new sheet at the same time by clicking in the box next to "New sheet:" and typing a name.

A chart can be moved at any time from one sheet to another existing sheet or from one sheet to a new sheet.

  • Click "OK" to save the sheet in its new location.

The chart has been moved to the new sheet named for it.

To update the data range selected and add or remove cells:

  • Right-click on the chart and choose "Select Data".
  • Click in the box at the top next to "Chart data range".
  • Re-select the range of data in the spreadsheet to include in the chart. This will enter the data in the proper format in the box.

With the data range updated, the data in the chart automatically updates.

Charts can provide great visual representations of data which make it easier to find trends, notice issues, and track any data that is important to you. Inserting a chart is quick and easy, but there can be some work required to get the chart to best represent your needs. Luckily, data labels, the location of the chart, and the data range selected, can all be modified at any time to better represent the data. In our next post we show how to take charts another step by adding and formatting labels and more in the chart.

As always, knowing the best way to configure and represent data is well worth the time!