Excel Dashboards - Excel Charts



If you choose charts for visual display of data, Excel charts help you to pick up and change the different views. Excel provides several chart types that enable you to express the message you want to convey with the data at hand in your dashboard with a graphical representation of any set of data.

In addition, there are certain sophisticated charts that are useful for some specific purposes. Some of these are available in Excel 2016. But, they can also be built from the built in chart types in Excel 2013.

In this chapter, you will learn about the chart types in Excel and when to use each chart type. Remember that in one chart in the dashboard, you should covey only one message. Otherwise, it may cause confusion in the interpretation. You can size the charts in such a way that you can accommodate more number of charts in the dashboard, each one conveying a particular message.

Apart from the chart types that are discussed in this chapter, there are certain advanced charts that are widely used to depict the information with visual cues. You will learn about the advanced chart types and their usage in the chapter – Advanced Excel Charts for Dashboards.

Types of Charts

You can find the following major chart types if you have Excel 2013 −

Column Charts

Stacked Column

# D Column Chart

Line Charts

Line Chart

3D Line Chart

Pie Charts

Pie D Chart

2-3 D Chart

Pie-Pie Chart

Doughnut Chart

Doughnut Chart

Bar Charts

Clustered Bar Chart

Stacked Bar

3-D Bar Chart

Area Charts

Area Stacked Chart

Three D Area Chart

XY (Scatter) Charts

Scatter XY Chart

Scatter with Lines

Bubble charts

Bubble chart

Stock Charts

Stock Volume

Surface Charts

Surface Chart

Radar Charts

Radar with Markers

To learn about these charts, refer to the tutorial − Excel Charts.

Combo Charts

When you have mixed type of data, you can display it with Combo (Combination) charts. The charts can either have only the Primary Vertical Axis or a combination of Primary Vertical Axis and Secondary Axis. You will learn about Combo charts in a later section.

Selecting the Appropriate Chart Type

To display the data by a chart in your dashboard, first identify the purpose of the chart. Once you have clarity on what you want to represent by a chart, you can select the best chart type that depicts your message.

Following are some suggestions on selecting a chart type −

  • If you want to compare data values, you can choose a bar chart, pie chart, line chart, or scatter chart.

  • If you want to show distribution, you can do so with a column chart, scatter chart or line chart.

  • If you want to show trends over time, you can use a line chart.

  • If you want to represent parts of a whole, a pie chart can be an option. But, while you use a pie chart, remember that only two to three different data points with very different data values can be effectively depicted with the varying sizes of the Pie slices. If you try to depict more number of data points in a Pie chart, it can be difficult to derive the comparison.

  • You can use Scatter chart if any of the following is the purpose−

    • You want to show similarities between large sets of data instead of differences between data points.

    • You want to compare many data points without regard to time. The more data that you include in a Scatter chart, the better the comparisons you can make.

  • Recommended Charts in Excel helps you to find a chart type that is suitable to your data.

In Excel, you can create a chart with a chart type and modify it later any time easily.

Showing Trends with Sparklines in Tables

Sparklines are tiny charts placed in single cells, each representing a row of data in your selection. They provide a quick way to see trends. In Excel, you can have Line Sparklines, Column Sparklines or Win/Loss Sparklines.

You can add Sparklines to your table quickly with the Quick Analysis tool.

  • Identify the data for which you want to add Sparklines.

  • Keep an empty column to the right side of the data and name the column. Sparklines will be placed in this column.

  • Select the data.

Quick Analysis tool button Analysis Tool appears at the bottom right corner of your selected data.

Quick Analysis
  • Click on the Quick Analysis Analysis Tool button. Quick Analysis tool appears.

  • Click on SPARKLINES. Chart options appear.

Chart Option
  • Click on Line. Line Charts will be displayed for each row in the selected data.

Sparkline Chart
  • Click on Column. Column Charts will be displayed for each row in the selected data.

Sparkline Column

Win/Loss charts are not suitable for this data. Consider the following data to understand how Win/Loss charts look.

Win Loss Chart

Using Combo Charts for Comparisons

You can use Combo charts to combine two or more chart types to compare data values of different categories, if the data ranges are varying significantly. With a Secondary Axis to depict the other data range, the chart will be easier to read and grasp the information quickly.

Combo Chart

Fine Tuning Charts Quickly

You can fine tune charts quickly using the three buttons Add, Customize and Filter that appear next to the upper-right corner of the chart.

  • With Add Chart Elements, you can add or remove axis, axis titles, legend, data labels, gridlines, error bars, etc. to the chart.

  • With Customize Chart Styles, you can customize the look of the chart by formatting the chart style and colors.

  • With Filter Chart Filters, you can dynamically edit the data points (values) and names that are visible on the chart being displayed.

Fine Tuning
  • You can select / deselect Chart Elements.

Display Selected Chart
  • You can format the Gridlines to show the depth axis.

Gridlines
  • You can set a Chart Style.

Chart Style
  • You can choose a color scheme for your chart.

Color Scheme
  • You can dynamically select values and names for display.

    • Values are the data series and the categories.

    • Names are the names of the data series (columns) and the categories (rows).

Values Names

Using Aesthetic Data Labels

You can have aesthetic and meaningful Data Labels.

You can place Data Labels at any position with respect to the data points.

Data Labels

You can format Data Labels with various options, including effects.

Format Data Label

You can change Data Labels to any shape.

Data Label Shapes

Changed Labels

Data Labels can be of different sizes. You can resize each Data label so that the text in it would be visible.

Resized Labels

You can include text from data points or any other text for any of the Data Labels so as to make them refreshable and thus dynamic.

Data Label Field

You can connect Data Labels to their data points with Leader Lines.

Leader Line

You can place Data Labels with Leader Lines at any distance from the data points by moving them.

Adjust Leader Line

You can format Leader Line to make them conspicuous.

Leader Line Option

You can choose any of these options to display the Data Labels on the chart based on your data and what you want to highlight.

Data Labels stay in place, even when you switch to a different type of chart. But, finalize the chart type before formatting any chart elements, including Data Labels.

Using Trendlines in Charts

You can depict forecast of the results in a chart using Trendlines.

Trendline

Using Shapes in Charts

You can insert different types of Shapes in your chart. After you insert a Shape, you can add Text to it, with Edit Text. You can Edit Shape with Change Shape and/or Edit Points.

Shapes in Charts

You can change the Style of the Shape, choose a Shape Fill Color, Format Shape Outline and add Visual Effects to the Shape.

Formatted Shape

Using Cylinders, Cones, and Pyramids

In 3-D Column charts, by default, you will have boxes.

Default Chart

To make your charts more conspicuous in dashboards, you can choose other 3-D column shapes like cylinders, cones, pyramids, etc. You can select these shapes in the Format Data Series pane.

Column Shapes

Columns with Pyramid shape

Pyramid Shape

Columns with Cylinder shape

Cylinder Shape

Columns with Cone shape

Cone Shape

Using Pictures in Charts

You can create more emphasis on your data presentation by using a Picture in place of Columns.

Pictures In Charts
Advertisements