Selection of Proper Chart - Trend Line - Customize Charts

Hi Friends,

In the last article, we learnt how to create Charts and the basic uses of the ‘Chart tools’ in the right-side panel, in this article we will discuss how to select the proper chart for your data and how to use trend line. We will discuss how you can change the chart data source without effecting the existing Chart. We will also discuss how to format a chart to make it presentable, more styles and colors to make it attractive in any presentation.

Before going to the discussion, I would like to inform you, please note that from the technical point of view every chart works in same manner except few charts like PIE chart. I will discuss about PIE chart in another article because improper use of PIE chart will impact your data analysis.

So far we know that to make a chart, we need to go to the INSERT menu and we select a Chart from Charts group after selecting the Data and the chart is displayed.

What chart to select or choose for your data?

The most confusing thing in preparing Charts for the MIS people or the data analyst is which chart type to be selected or which chart type is the perfect Chart for your data. Friends, it solely depends upon your data, how the data is arranged, the type of data you are using etc. Just think before selecting the chart type and I am sure your chart selection will be the best.

Remember, you should always choose that chart which describes your data more logical and analytical. For example, if you need to display the percentage share of Sales made in the cities like New York, Los Angeles, Chicago, Houston, Philadelphia, Phoenix and San Diego, you must use a PIE Chart, because PIE chart will not only display the percentage but also it will show the area it covers in a circle and a sudden drop of sale or growth of sale will be prominent because the area will either be relatively low or big area.

PIE Chart showing Market Potentiality with percentage share

Now let suppose you want to display how Sales are performing with respect to potentiality; you should use a BAR Chart or a COLUMN Chart because BARS or COLUMNS will be positioned side by side. If you want to compare between the Sales made in the last month last year versus current year last month or the target taken you may use bar charts with a TREND line. (I will show you how to add a trend line.)





As soon as you select a chart after selecting your data range, the chart is displayed, with colored lines (for Line Chart) or bars (for BAR or COLUMN Chart) i.e., the pictorial representation of your data, here you will find a Legend box, which signifies the used colors in the chart and the data it represents e.g., blue line for potentiality and saffron colored line for sales quantity, Right Click on chart a drop-down menu will appear, at the bottom you will find an option Format Legend…, you can align the position of this legend in the right tool box under Format Legend option.

Right Click Drop Down Menu Format Legend

This is an important drop-down menu because you can use it for many purposes, like delete or position the Legend, Change the chart type etc., you can also change the font of the Chart and you can change the data.

How to Change Chart Type of an Existing Chart?

If you click on the Change Chart Type option, a new window will appear and you can change your chart from BAR chart to Pie Chart or scatter chart etc.

How to Change Chart Type of an Existing Chart

You can format the AXIS (X or Y axis) of the chart, click on the AXIS box and in the right tool box you can change it or you can right click on it and the option Format Axis will appear where you can align or give text directions etc.

Format Chart Axis

How to Change Data Source of an Existing Chart without effecting Chart Design?

Right Click on the Chart, from the drop-down menu click on Select Data Source, a new window will appear as Select Data Source.

How to Change Data Source of an Existing Chart without effecting Chart Design

In this window, you can very quickly and easily change the data range, first remove the data range in Chart data range textbox then select the data in the Excel sheet, click on OK and you are done.
Excel provides options to display the selective data range, in the Legend Entries (Series) just below the Chart data range textbox you can select what to display by unchecking tick mark in Select Data Source window.

Double click on the chart, two new menus will appear in the ribbon (top of the Excel) as Design and Format where you will get lots of options to make your chart visually beautiful.

Chart Menu - Design & Format

For example, in the design window you can choose the desired chart style which is predefined. You can Switch between rows and columns if you click the Switch Row/Column button. Here you get the Select data button which we already get from the drop-down menu.

Note : Except PIE chart everything is very similar to different types of charts, if you know how to format a BAR chart, you can also format the LINE Chart or Column chart.

How to add a Trend Line in Charts?

Except PIE Chart, you can add TREND line to every chart, be it a Line chart or a BAR chart or any other chart type.

To insert a Trend Line, you just click on the line or the bar in the graph or chart to select the line or column and then right click over it, from the drop-down menu select Add Trendline… and you will notice instantly a line is created and this line is the TREND Line which shows the trends of the given data.

How to add a Trend Line in Charts

Friends, that’s all for this article, thank you for reading this article.
Take care…