Build a Better, Cleaner, More Professional Line Chart


Better Line Chart LeadExcel has some great basic charts and graphs build in, which makes it easy to build visualizations of your data. They are functional, but they don’t give a very professional look to your data. To make your presentation, website, or sales pitch really make a good impression, you’ll want to find a way to improve on the default charts. Even a basic line chart can be given a cool makeover with better colors, axes, and  shaded background to make it stand out. These touches can give the boring old graphs a fancy new look. Here’s how to make a better line chart in Excel…

The Default Line Chart

It may not have the polish we want, but Excel’s basic line chart is a great starting point for our cool new version. Start by selecting your basic data table with the series name on the left and the dates (or labels) in the top row.

Chart Data

From the Chart menu on the Insert tab, choose the Line drop-down and choose the first option.

Make Chart

This is what we start with. It isn’t very pretty, but it would get the job done. Let’s see what we can do to make it look more professional.

Default Chart

Small Tweaks and Improvements

Removing the Legend

When you are working with a single data series, there is no confusion about what data is being presented. If the chart is titled “Sales” and there is only one line, the user doesn’t need to be told again that the line represents sales figures. Since it’s not needed, let’s go ahead and delete the legend.

Select the legend by clicking on the text or line graphic. When it is selected as seen below, press the Del key to remove the legend from the graph.

Delete Legend

Formatting the Date Axis

When working with dates on an axis, it is often difficult to read the timeline. This chart only shows monthly data, so it doesn’t help to provide the day. The year will probably be shown in the chart title, so we only really need to see the month. It is possible to just show the number of the month, but it can sometimes be clearer to use the first letter of the month. To format the date axis, select the date cells from the original data table.

In the Format menu of the Home tab, click the arrow in the lower right to bring up the Format Cells dialog.

Format Date

In the Format Cells dialog, choose the Custom category, and in the Type field, type “mmmmm”. To learn about other date formats, you can check out the Definitive Guide Custom Number Formats in Excel.

Format Date Initial

The new x-axis will be much cleaner to look at:

Chart Date Axis

Fixing Line Margins

The default line chart in Excel adds margins before the first data point and after the last data point. Line charts (especially line charts with shaded areas – see the last section of the tutorial) often look better without these margins:

Line Margins

Double-click the date axis to bring up the Format Axis dialog. Under Axis Options, change Position Axis to On Tick Marks.

Format Date Axis

Formatting the Dollars Axis

The y-axis can also use some cleaning up. In the default view, the full thousands of dollars are shown, and there are too many lines crowding up the graph. We can ask Excel to show fewer lines and abbreviate the dollar values to make it easier to read.

Double-click one of the dollar values on the y-axis to bring up the Format Axis dialog.

Default Axis

In the Format Axis dialog under the Axis Options, change the Major Unit from Auto to Fixed. Since our chart data ranges from 0 to $1m, it is probably safe to use $250k increments. In the Fixed field type “250000”.

Under Display Units, choose to group the data by “Thousands”.

Format Axis

These changes make the y-axis much easier to read:

New Chart Axis

Cleaning Up the Chart Line

The default line chart in Excel has no data point markers. There is a chart type that uses markers, but most of the options are ugly and difficult to read. Let’s clean up the line chart itself…

Double-click the chart line to open up the Format Data Series dialog.

Default Chart Line

Under Marker Options, choose Built-In and select the Circle mark type. This is generally the most attractive default option, and you should only use something different if you have no other choice (e.g. too many data series).

FormatMarkerOptions

It also may improve visibility to add a drop shadow to the line chart. Under Shadow, choose the first Presets option under Outer.

Drop Shadow

A Better Line Chart

The result of these small changes is a much more readable, cleaner, more professional looking line chart:

Improved Chart

You can repeat this process to add multiple data series with different colors to build a clean, cool chart for all your presentations and reports:

Multiple Lines Chart

If you have only one data series (one line on the chart), there is one more step you can take to make the line chart truly stand out…

Adding a Shaded Area to the Line Chart

To make a single series line chart truly stand out, you can add a shaded area below the line. These shaded areas don’t tend to look as good with a drop-shadow on the line, so if you have added a shadow above, you may want to remove it again.

To do achieve the shaded area under the line chart, we have to trick Excel by adding another data series. Select the Sales row and copy it to the row below so there are two identical data series.

Then select the chart you made so the data table is highlighted. The date axis is highlighted in purple. The series name is highlighted in green. The data points themselves are highlighted in blue. Drag the corner of the blue box down to include the second data series.

Duplicate Series

A red line will appear on top of our blue line on the chart. Click to select the red line. Right-click and then choose Change Series Chart Type from the drop-down menu.

Change Chart Series Type

In the dialog box that appears, choose the first type under the Area category.

Change Chart Series Type Dialog

Double-click the red area on the chart to bring up the Format Data Series dialog.

Chart Area

Under Fill, choose Solid Fill. Under Fill Color choose the same color as the line in the chart. Change Transparency to 66%.

Format Transparent Fill

Under Border Color, choose Solid Fill. Under Fill Color choose the same color as the line in the chart. Change Transparency to 66%.

Transparent Border Color

The final result will be an eye-catching chart that will make a great impression on presentations, publications, and other professional materials.

Completed Chart

Feel free to experiment with other color schemes. Try not to use default Excel colors, as they are easy to recognize…

Chart Color Options