Sign up

How to Draw 9 Professional Chart Types With Zero Effort

Get Business-ready visuals with 1-click

In this article, I share all tricks of the trade how I draw professional charts every day with minimal effort.

Detailed instructions and core source code, so you can replicate the results and amend them as you please.

You can also test the ready-made example add-in to get started without writing or compiling any code, in 3 simple steps:

1- Download the Excel add-in installer
2- Activate your free account
3- Test the Magic Button

1-click charts

Showing your analysis: challenging but crucial.

Tired after all the hard analytical work, results are now clear to you: so why bother? Unfriendly charting tools make you want to cut this step short.

Charts with 1-click, using the Magic Button of which I share the source code as well as provide a ready-to-use working example, an approach which can be replicated in any tool/software beyond Excel (used for this demo).

After you will have chosen your chart type and prepared the dataset, this post shows how-to formatting for the 9 main chart types: Line, Pie, Scatter, Bubble, Ladder, Column, Boxplot, Clustered, Bar.

1. Line chart: demo video

Magic Button to cut charting short in 5 easy steps

Step 1. Select the dataset

Step 2. Insert regular Excel 2-D line chart then resize it to an approximately 2:1 ratio, which has the best readability

Step 3. Push the Magic button

Step 4. Set the chart name by looking at the left side of the formula bar, while the chart is selected

Step 5. Fix the 5 finishing touches using appropriate User Defined Functions (UDF)

a. Legend
b. Labels
c. Unit of measure
d. Title
e. Subtitle

Charting User Defined Functions (UDF)

To achieve full automation even if the data changes, while avoiding some specific bugs of Excel, I use 3 UDF — alternatively, you can implement these finishing touches directly within your Magic Button, but losing some freedom and consistency.

  • Legend: added automatically with the right sequencing (unlike Excel that inverts it!); remove it if you so prefer
  • Labels: add on the left hand side using =EvoLabelsApply()
  • Unit of measure: trim away the ,000,000 too many zeros using =EvoChartApplyFormatCode(“Chart Name”,EvoFormatCode())

  • Title/subtitle: in this case I show only a title, with the unit of measure (e.g. Millions) to match the right multiplier. You can use the same technique to set both a title (e.g. Revenues) and a subtitle (e.g. EUR, millions)

To make the title automatically editable, link it to a cell content by selecting its shape, then entering =A1 in the formula box for example, to reference cell A1, or you can use any other cell for example in this case =Line!$K$53.

2. Pie chart

After pushing the Magic Button, to get the double labels, inside and outside, use the =evoLabelsApplyPie() function.

3. Scatter

After pushing the Magic Button, format both the X and Y axes with the apply format function e.g.

=EvoChartApplyFormatCode(J16,EvoFormatCode(O15),”X”)

to prevent all those unnecessary ,000,000,000 from crowding up the chart.

Use =EvoLabelsHide() to remove overlapping labels.

4. Bubble

Same as the scatter chart, plus use positive/negative bubble sizes to have two different fills while using a single dataset.

5. Ladder

Handy chart type to show price ranges; a bubble chart but with custom axis labels and vertical lines.

6. Column

Truly handy chart for business!

Pre-sort the data for greater readability using =evoSortValue() where relevant; hide labels of data sets smaller than 6% of the chart height by formatting a supporting dataset with =EvoChartApplyFormatCode().

7. Boxplot

Regular column chart, where the base bar is hidden and the whiskers are error lines. Use to show distributions, e.g. of prices.

Note: custom error bars do not accept ‘empty’; if needed use ={0}


8. Clustered

Not very readable. Use this chart type rarely, please 🙂

9. Bar

Use the Column chart as reference: the bar chart is a close relative!

Source code: Magic Button (skip directly to the next section if not into programming!)

This is a Visual Basic C# / .NET project.

First, this first block of code behind the ‘Magic Button’ sets core properties, then calls the appropriate sub-function for the selected chart type; see below for further details.

public static void FormatMagicButton(Chart chart)
        {
            if (chart == null)
            {
                return;
            }// General
            chart.Parent.Placement = XlPlacement.xlMove;// ChartArea Font
            chart.ChartArea.Format.TextFrame2.TextRange.Font.Name = "Lato";
            chart.ChartArea.Format.TextFrame2.TextRange.Font.Size = 10;
            chart.ChartArea.Font.Color = FormatColors.EvoChartFont;// Shapes Font
            foreach (Shape shape in chart.Shapes)
            {
                shape.TextFrame2.TextRange.Font.Name = "Lato";
                shape.TextFrame2.TextRange.Font.Size = 10;
                shape.TextFrame2.TextRange.Font.Fill.ForeColor.RGB =
                    ColorTranslator.ToOle(FormatColors.EvoChartFont);
                shape.TextFrame2.TextRange.Font.Italic = MsoTriState.msoTrue;
            }// Title Font
            if (!chart.HasTitle)
            {
                chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
            }
            chart.ChartTitle.Position = XlChartElementPosition.xlChartElementPositionCustom;
            chart.ChartTitle.Left = chart.ChartTitle.Top = -3;
            chart.ChartTitle.Format.TextFrame2.TextRange.Font.Name = "Lato Black";
            chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 12;
            chart.ChartTitle.Format.TextFrame2.TextRange.Font.Bold = MsoTriState.msoTrue;
            chart.ChartTitle.Font.Color = FormatColors.EvoChartFont;switch (chart.ChartType)
            {
                case XlChartType.xl3DBarClustered:
                case XlChartType.xl3DBarStacked:
                case XlChartType.xl3DBarStacked100:
                case XlChartType.xlBarClustered:
                case XlChartType.xlBarStacked:
                case XlChartType.xlBarStacked100:
                    FormatChartAreaBase(chart);
                    FormatBarChart(chart);
                    break;case XlChartType.xlBubble:
                case XlChartType.xlBubble3DEffect:
                    FormatPlotAreaBase(chart);
                    FormatBubbleChart(chart);
                    break;case XlChartType.xlColumnClustered:
                case XlChartType.xlColumnStacked:
                case XlChartType.xlColumnStacked100:
                case XlChartType.xl3DColumn:
                case XlChartType.xl3DColumnClustered:
                case XlChartType.xl3DColumnStacked:
                case XlChartType.xl3DColumnStacked100:
                    FormatChartAreaBase(chart);
                    FormatColumnChart(chart);
                    break;case XlChartType.xlLine:
                case XlChartType.xlLineStacked:
                case XlChartType.xlLineStacked100:
                case XlChartType.xlLineMarkers:
                case XlChartType.xlLineMarkersStacked:
                case XlChartType.xlLineMarkersStacked100:
                case XlChartType.xl3DLine:
                    FormatChartAreaBase(chart);
                    FormatLineChart(chart);
                    break;case XlChartType.xl3DPie:
                case XlChartType.xl3DPieExploded:
                case XlChartType.xlPie:
                case XlChartType.xlPieExploded:
                    FormatChartAreaBase(chart);
                    FormatPieChart(chart);
                    break;case XlChartType.xlXYScatter:
                case XlChartType.xlXYScatterLines:
                case XlChartType.xlXYScatterLinesNoMarkers:
                case XlChartType.xlXYScatterSmooth:
                case XlChartType.xlXYScatterSmoothNoMarkers:
                    FormatPlotAreaBase(chart);
                    FormatScatterChart(chart);
                    break;
            }

Source code: Line chart formatting

After the global function above, the specific code for the line chart (and any other chart type) is then relatively straightforward: scripting each chart property one line at a time, rather than applying them manually.

private static void FormatLineChart(Chart chart)
	{// Axis X
		if (!chart.HasAxis[XlAxisType.xlCategory])
		{
			chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryCategoryAxisShow);
		}
		var axisX = (Axis)chart.Axes(XlAxisType.xlCategory);
		axisX.TickLabels.Offset = 0;
		axisX.TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionLow;
		axisX.TickLabels.Orientation = XlTickLabelOrientation.xlTickLabelOrientationHorizontal;
		axisX.MajorTickMark = XlTickMark.xlTickMarkInside;
		axisX.MinorTickMark = XlTickMark.xlTickMarkNone;
		axisX.Border.Color = FormatColors.EvoChartAxisBorder;
		axisX.Format.Line.Weight = .25f;
		axisX.Crosses = XlAxisCrosses.xlAxisCrossesMaximum;
		axisX.AxisBetweenCategories = false;
		axisX.MajorGridlines.Format.Line.Visible = MsoTriState.msoFalse;// Axis Y
		if (!chart.HasAxis[XlAxisType.xlValue])
		{
			chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisShow);
		}
		var axisY = (Axis)chart.Axes(XlAxisType.xlValue);
		axisY.TickLabels.Offset = 0;
		axisY.TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionHigh;
		axisY.MajorTickMark = XlTickMark.xlTickMarkInside;
		axisY.MinorTickMark = XlTickMark.xlTickMarkNone;
		axisY.Border.Color = FormatColors.EvoChartAxisBorder;
		axisY.Format.Line.Weight = .25f;
		axisY.Crosses = XlAxisCrosses.xlAxisCrossesAutomatic;
		axisY.MajorGridlines.Format.Line.Visible = MsoTriState.msoFalse;// Series
		int seriesIndex = 0;
		var allSeries = (SeriesCollection)chart.SeriesCollection();
		var legendSeries = new List<String>();
		foreach (Series series in allSeries)
		{
			if (series.Formula.Contains($@"""{series.Name}"""))
			{
				series.Delete();
				continue;
			}
			var seriesDefinition = _seriesDefinitions[seriesIndex++ % _seriesDefinitions.Count];
			series.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(seriesDefinition.Color);
			series.Format.Line.DashStyle = seriesDefinition.DashStyle;
			series.Format.Line.Weight = seriesDefinition.Weight;
			series.PlotOrder = allSeries.Count - seriesIndex + 1;
			if (legendSeries != null)
			{
				legendSeries.Add(series.Name);
			}
		}// Legend
		if (chart.HasLegend)
		{
			chart.Legend.Delete();
		}
		chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementLegendTop);
		chart.Legend.Left = chart.ChartArea.Width - chart.Legend.Width;
		chart.Legend.Top = 0;
		var legendHeight = chart.Legend.Height;var legends = (LegendEntries)chart.Legend.LegendEntries();
		try
		{
			int count = legends.Count;
			int i = 1;//remove legend entry from the chart
			while (count >= i)
			{
				legends.Item(i)?.Delete();
				i++;
			}
		}
		catch { }seriesIndex = 0;if (legendSeries.Any())
		{
			foreach (var name in legendSeries)
			{
				var series = allSeries.NewSeries();
				var seriesDefinition = _seriesDefinitions[seriesIndex++ % _seriesDefinitions.Count];
				series.Format.Line.ForeColor.RGB = ColorTranslator.ToOle(seriesDefinition.Color);
				series.Format.Line.DashStyle = seriesDefinition.DashStyle;
				series.Format.Line.Weight = seriesDefinition.Weight;
				series.Name = name;
				try
				{
					if (legends.Count > seriesIndex)
					{
						chart.Legend.LegendEntries(1)?.Delete();
					}
				}
				catch { }
			}
		}// PlotArea
		chart.PlotArea.Top = Math.Max(legendHeight, chart.ChartTitle.Height) + 10;
		chart.PlotArea.Height = chart.ChartArea.Height - chart.PlotArea.Top - 5;
		chart.PlotArea.Left = 30;
		chart.PlotArea.Width = chart.ChartArea.Width - chart.PlotArea.Left - 5;
}

Bonus: ready-to-test add-in

If, like me, you just want the ready solution without having to bother with code, here are the 3 simple steps to charting with zero effort:

1- Download the Excel add-in installer
2- Activate your free account
3- Test the Magic Button

This comes with the Magic Button as well as 46 User Defined Functions, just start typing =evo inside any cell to get the full list.

Happy charting!

PS For more details about the rationale of each formatting choice, please review my related piece:

https://resources.evopricing.com/intelligence/business-charts-in-excel/

Example results from using this approach:

https://resources.evopricing.com/intelligence/how-business-science-revealed-hidden-pricing-opportunities-during-covid-19/

https://resources.evopricing.com/intelligence/data-science-is-dead-long-live-business-science/

To get Business Science software, University-level learning (launching October 2020), and a monthly summary of insights:
Any questions? Please connect on Linkedin

About the author

Fabrizio Fantini is the brain behind Evo. His 2009 PhD in Applied Mathematics, proving how simple algorithms can outperform even the most expensive commercial airline pricing software, is the basis for the core scientific research behind our solutions. He holds an MBA from Harvard Business School and has previously worked for 10 years at McKinsey & Company.

He is thrilled to help clients create value and loves creating powerful but simple to use solutions. His ideal software has no user manual but enables users to stand on the shoulders of giants.

Hey! Was this page helpful?

We’re always looking to make our docs better, please let us know if you have any
suggestions or advice about what’s working and what’s not!

Send Feedback