ResagratiaResagratiaResagratia
+234 806 299 1199
contact@resagratia.com

Custom Excel Charts – Part 2

What are custom Excel charts? You can think of them as infographic charts: a visual representation of information, data or knowledge intended to present information quickly and clearly. They can improve cognition by utilizing simplified graphics to enhance the human visual system’s ability to see patterns and trends.

Sequel to the article on Customs Charts and Infographics in Excel, this tutorial is all about learning other ways to transform a mundane Excel chart into an info-chart.

Let’s take a glance at the objective we are want to achieve in this article. The chart below is a Column chart :

Microsoft Excel Column Chart
Microsoft Excel Column Chart

The above chart will be transformed into the info-chart below:

Info Chart
Custom Excel Chart

How The Info Chart Works

Custom Excel charts, like any other info chart, are automated i.e. whenever there is a change in the values, the chart updates automatically. An example can be seen below:

The corresponding custom Excel chart for the data provided is shown below. Here, you can see that the month with the highest expense is automatically highlighted in the teal colour.

Info-Chart
Custom Excel Chart

If, for example, we alter the expense figure for the month of August to #7000 to match the figure for March, the result is equally effected in the custom Excel chart as shown below. Notice in the image below that though two separate months have similar values, the custom Excel chart still responded accordingly.

Infographics
Custom Excel Chart

Already excited? great! Let’s get started, shall we?

Designing the Custom Excel Chart

We will be working with this fictitious data-set to create our custom Excel chart. This process will be broken down into steps for you to have a vivid understanding of how to go about it.

Step 1: A well-formed Dataset

Ascertain that your dataset has both the category and values fields – Month and Amount respectively – as seen in the image below:

Dataset

Step 2: Insert Column Chart 

In order to insert a column chart, take the following steps:

  1. Click on any cell within the dataset
  2. Select Insert tab
  3. Select the column chart option in the chart tools group 

Your output should have a semblance of the chart below:

Custom Excel Chart
Column Chart

Step 3: Format the Column Chart

In order to format the chart effectively, undertake the following steps:

  1. Double-click on the gridlines and delete it
  2. Double-click on the y-axis data labels and delete it
  3. Double-click on any of the bars
  4. Right-click and click on “Add Data Labels” 
  5. Keep the bars selected
  6. In the Format Data Series pane
  7. Adjust the gap width by decreasing it to 75%
Format Pane
Format Data Series pane

Your custom Excel chart should be looking like the image below.

Custom Excel Chart

Add Shape Fill to the Column Chart Bars Colour

  1. Rename the chart title to “Total Expenses Per Month
  2. Add shape fill by double-clicking on any of the bars
  3. Select the format option in the chart tools contextual tab
  4. Select shape fill and select the grey colour with the theme colour name “Black, Text 1, Lighter 50%
Chart tools tab

At this juncture, your custom Excel chart should have a semblance of the chart below:

Custom Excel Chart
Column chart

Step 4: Insert Conditional Formatting to Custom Excel Chart

We will be inserting conditional formatting to the chart, in order to have the chart reflect the changes to the maximum value in the dataset. Essentially, we want the maximum value to always have its own colour and be differentiated.

Custom Excel Chart
Custom Excel Chart

Let’s make the maximum value in the chart to be dynamic.

Note: In order to apply conditional formatting to the chart, we need to add a new data column. i.e. we will add a data series that consists of a single bar and the conditional formatted bar will emerge whenever there’s a maximum value in the chart.

Add a New Data Column

To add a new column, apply the following steps:

  1. Insert a new column and name it Max
  2. Use the “IF” function to make only the maximum number constant and every other number to remain as 0
  3. After inserting the formula below, double-click the black-cross (+) sign to auto-fill the entire Max column
Custom Excel Chart

The logic here is simple. We’re extracting the maximum value from the Amount column into a new column. Any month with the highest value will be populated into the Max column.

All other values remain as 0.

=IF(B3=MAX($B$3:$B$14),B3,0)

Apply the following steps in order to add the “Max” series to the custom Excel chart for dynamic conditional formatting :

  1. Apply a similar fill colour of the other column bars to the maximum number
  2. Right-click the chart and Select Data
  3. In the Select Data Dialogue window, Click the Add Button
  4. Select the column name to add the “Series name”
  5. Highlight the value cells to add the “Series values”
Custom Excel Chart
Custom Excel Chart
Custom Excel Chart

Your output should have a semblance of the chart below:

Custom Excel Chart

Change the fill colour

To change the fill color, apply the following steps:

  1. Double-click on the “Max” series bar
  2. Format Data point pane dialogue window will pop up. If it does not, right-click on the bar and select from the options “Format Data Point”.
  3. Select the “Fill” option and change the colour to a turquoise colour or any colour you wish
Format Data Pane
Format Data Point Pane

Overlap the series

To overlap the series, follow these steps :

While the max series bar is selected

  1. Select the “Series Options” 
  2. Go to Series Overlap and change it to 100%
Custom Excel Chart
Format Data Series Pane

Your output should have a semblance of the chart below:

Column chart
Column chart

Step 5: Add Shapes

Note: Before selecting or adding shapes to the chart, duplicate the chart. 

To Insert a shape, follow these steps : 

  1. Select the Insert tab
  2. Click-on Illustrations and then Shapes
  3. In the Flow chart shapes, Select the Delay Icon
  4. Drag out the (+) sign that pops up after selecting the shape on any cell to see the shape
Excel Insert tab
Inserting Shapes

After dragging out the shape, turn the shape around and change the colour to “grey” by using the “Shape Fill” option and remove the shape outline by using the “Shape Outline” option to remove the outline.

Shape Outline

Your output should have a semblance of the Image below:

To add the shape to our chart, follow these steps : 

  1. Copy the shape using Ctrl + C (Excel shortcut for copy)
  2. Select all the bars and paste the shape on it by using Ctrl + V (Excel shortcut for paste)

Your output should have a semblance of the chart below:

Custom Excel Chart
Excel Infographic

Change the maximum number bar shape by following these steps:

  1. Duplicate the Flow chart Delay shape
  2. Select the duplicate shape and change the fill colour to turquoise or any colour you choose
Excel Shape Fill
Shape Fill

Your output should have a semblance of the Image below:

After applying the fill color :

  • Copy the colour-filled Flow Chart delay shape
  • Double-click on the maximum bar in the chart and paste

Your output should have a semblance of the chart below:

Custom Excel Chart
Excel Infographic

Note : The bar tips are not equally curved. The problem here is that the bars with bigger values have pointy shapes while the bars with smaller values do not. The reason is because for larger values the bars are stretched out, while the bars for smaller values are not stretched out.

Fret not, the solution is coming right up…

Adjust the Stretch of the Custom Excel Chart

To adjust the stretch, we will apply a format. The format will be to split each data bars into halves. I.e. the top shape will be identical for all bars while the bottom shape will be the alternating part. To achieve this, we need to add two new data series.

Step 1: Add two Data Series

Note : We are adding two new series to the duplicated data-set.

To add two new data series to the bars, we will use the stacked column chart and this means we will make a slight change to our dataset. I.e. we will duplicate the Data set.

Your output should have a semblance of the Image below:

Custom Excel Chart

Step 2: Add New Stacks

To add new stacks, follow these steps :

  1. Insert two new columns to the new data table and name the columns “1st and 2nd Stack” respectively.
  2. Apply the “MIN” function on the 2nd Stack column.

Note: The function finds the minimum number in the “Amount” column.

Your output should have a semblance of the Table below:

Custom Excel Chart

Note : The 2nd stack represents the shape format for the top stack. To get the shape format for the 1st stack which represents the bottom stack, we will subtract the values in the Amount column from the values in the top stack.

Your output should have a semblance of the Table below:

Custom Excel Chart

Step 3: Insert Stacked Column Chart

To insert stacked column chart to the dataset, follow these steps :

  1. Select “Insert tab” and click the stacked column chart
  2. Right-click on the chart and click Select Data
  3. A dialog box will pop up. Go to “Add” on the dialog box and select the data range.
  4. Add the categories range to the chart and untick the Amount column
Custom Excel Chart
Change Axis Labels

To change the Axis labels, follow these steps:

  • Select “Edit” and select the “Month” data and click “OK”
Custom Excel Chart
Select Data Dialogue Box
Format Stacked Column Chart

To format your chart, follow these steps :

  1. Select the gridlines and click delete
  2. Select the Y-axis label and click delete
  3. Select the Legend field and click delete
  4. Change the chart title to Total Expenses per Month
  5. Reduce the gap width of the Bars to 50%
  6. Copy the grey filled color Flow chart delay shape and paste it on the Top bar stack
  7. Use the “Shape Fill” option to apply grey color to the Bottom stack color(orange color)
Custom Excel Chart
Format Data Series

Your custom Excel chart should have a semblance of the chart below:

Custom Excel Chart
Add Data Labels to the Top stacks

To add data labels to the Top stacks, follow these steps :

  1. Double-click on any of the top stacks to highlight all the Top stacks
  2. Right-click on any of the selected stack and click “Add Data Labels”
  3. Select any of the labels to highlight all the labels
  4. Select the Font option
  5. In the Font color box, change the font color to white for visibility
  6. In the Font style box, change the font style to bold and adjust the font size to size 10.
  7. In the Format Data labels pane, adjust the Label position to “Inside Base”

Note: The labels only show the values for “2nd Stack” which isn’t appropriate. To change the data Labels to that of the “Amount” values, follow these steps:

  1. Right-click on the stack
  2. Select Format Data Labels option
  3. Tick the Box for Values from cells
  4. In the pop-up prompt, highlight the range for the Amount Column in the Data-set and press OK
  5. Untick the box for value and show leader lines

Your Formatting options should be like this after unticking the boxes:

Custom Excel Chart
Format Data Labels
Add Axis Labels to the Bottom Stack 

To add Axis label to the bottom stack, follow these steps : 

  1. Double-click on any of the Bottom stacks to highlight all Bottom stacks
  2. Right-click on any of the selected stacks and click “Add Data Labels”
  3. Select any of the labels to highlight all the labels
  4. Select the Font option
  5. In the Font colour box, change the font colour to white for visibility
  6. In the Font style box, change the font style to bold and adjust the font size to size 10.
  7. In the Format Data labels pane, adjust the Label position to “Inside Base”
  8. Right-click on any labels in the Bottom stack
  9. Select Format Data Labels option
  10. Tick the Box for Values from cells
  11. In the pop-up prompt, highlight the range for the Month Column in the Data-set and press OK
  12. Untick the box for value and show leader lines
Custom Excel Chart
Untick the value and show leader lines boxes respectively

Your custom Excel chart should have a semblance of the chart below:

Info Chart

Congratulations on making it this far, we’re almost at the climax…

Adding Dynamic Conditional Formatting

In order to insert conditional formatting to the chart, we have to add the “Max” series column as we did earlier. To add Dynamic conditional formatting, follow these steps:

  1. Add new columns and name them “1st and “2nd Stack Max” respectively
  2. Apply the “IF” formula as it was applied earlier but ensure the “IF” condition covers the values of both Stack columns

Your output should have a semblance of the Table below:

Custom Excel Chart

Adding the Stacks Max Series to the Custom Excel Chart

To add the Stacked columns, follow these steps:

  1. Right-click on the chart and click select Data Add
  2. Add the series name and title for both “1st Stack Max” and “2nd Stack Max”

Your new Select data source dialogue box should look like this:

Custom Excel Chart

Your custom Excel chart should have a semblance of the chart below:

Custom Excel Chart
Custom Excel Chart

It looks weird right?

This is as a result of the new “Max” stacks being automatically added on top of the previous stack. To adjust this outcome, we need to do the following:

  1. Right-click on the Bottom stack and select change series chart type
  2. Check the box for the “1st Stack Max” and “2nd Stack Max” to be on the Secondary Axis and press “OK”
Change Chart type
Change chart type

Change Shape Fill of the Custom Excel Chart

To change the shape color, follow these steps:

  1. Double-click on the bar with the highest value
  2. Change the “Shape Fill” of the selected bar to “turquoise” colour

Your final output should have a semblance of the chart below:

Custom Excel Chart

You can experiment with your dataset by changing the values in order to come up with another alternative output like the custom Excel chart below:

Custom Excel Chart
Custom Excel Chart with two maximum values

We appreciate our readers and learners and would love to get your feedback.

If you enjoyed this article, don’t forget to like, share the article and subscribe for more exciting content.


Do you want to learn how to automate tasks with Excel?

YouTube video

Receive Fresh, Weekly Analytics Content

Join 1K+ subscribers to receive original, freshly curated content every week.