Advanced Excel Chart Techniques

1. How to graph a Normal Distribution

The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart.

This curve is often used in probability theory and mathematical statistics.

Instructions

First I'll show you how to construct the data needed, then insert a chart. Lastly, customize the chart to make it look better.

Normal distribution data

The chart requires two sets of data, x and y. Enter values from -4, -3.5, -3 ... to 4, they will be x-axis values shown in column B in the picture below.

The NORM.DIST function allows you to calculate the normal distribution for each x value.

Formula in cell C21:

Copy and paste this formula to cells below, as far as needed.

Insert a chart

Select the cell range, in my example B21:C37.

Go to tab "Insert" on the ribbon then press with left mouse button on the scatter chart button.

Press with mouse on "Scatter with smooth lines" button.

The chart shows up, clearly, we have to do a few changes to this chart.

Customize chart

The y-axis values are in the middle of the graph if you want to move it to the left or right follow these steps.

1. Press with right mouse button on on x-axis values. (Yes, x-axis.)
2. Press with mouse on "Format Axis..."
3. Find "Vertical axis crosses" setting.
   
4. Select Axis value and type a value. In my example, -6.

Press with mouse on major gridlines and press delete button to delete them, if you prefer.

Change the chart title.

Get Excel *.xlsx file

How to graph a normal distribution.xlsx

2. How to build an arrow chart

This chart is an arrow chart that has horizontal and vertical lines, positive arrows are green and negative arrows are red.

How to build an arrow chart

I am going to use a scatter chart to plot these lines, I will have two different series, one for the green arrows and one chart series for the red arrows.

The following data set shows how I arranged values in order to get an ending arrow for each group of chart values.

Select the first series (green arrows).

Go to tab "Insert" on the ribbon.

Press with left mouse button on the "Insert Scatter (X,Y) or Bubble chart" button.

Press with left mouse button on the "Scatter with Straight Lines and Markers" button.

Press with right mouse button on on chart and then press with left mouse button on "Select Data...".

Press with mouse on the "Add" button.

Select the x and y values.

Press with left mouse button on the "OK" buttons.

Doublepress with left mouse button one of the series to open the task pane.

Go to tab "Fill & Line", press with left mouse button on the "Marker" button and then press with left mouse button on radio button "None" to remove markers. Select the other chart series and repeat the steps once again to remove the markers for the second chart series.

Now go to "Line" and change the color for both chart series. If the smoothed line check box is checked then deselect that one as well.

Change the "End Arrow type" and the End Arrow size" to display arrows for each line.

3. How to graph an equation

Why graph an equation?

Graphs make it easier to analyze features of the equation like intercepts, increasing/decreasing, maxima/minima, concavity, etc. Examining a graph can reveal insights.

What are intercepts?

Intercepts refer to the points where a function or curve intersects the x-axis and y-axis. There are two main types of intercepts: x-intercepts and y-intercepts. Identifying these intercepts provides insight into the function and can help derive key characteristics.

What is an increasing/decreasing equation?

• Increasing - The graph is rising as it moves left to right. The y-values increase as x increases.
• Decreasing - The graph is falling as it moves left to right. The y-values decrease as x increases.

Identifying increasing/decreasing intervals helps understand the function's behavior.

What are maxima/minima?

Maxima and minima refer to the points where a function reaches its highest or lowest value.

• Local maxima is the highest value in a region and global maxima when absolute highest over all x values.
• Local minima is the lowest value in a region and global minima when absolute lowest over all x values.

What is concavity?

Concavity refers to the shape of the curve and whether it curves up or down.

The two types of concavity are:

• Concave up - The curve resembles a cup or smile shape opening upwards. Indicates the rate of change is increasing.
• Concave down - The curve resembles a frown shape curving downwards. Indicates the rate of change is decreasing.

Concavity

What are inflection points?

An inflection point is a point on the curve where the concavity changes from concave up to concave down or vice versa.

3.1 Chart an equation

The picture above shows the following equation

plotted on an x y scatter chart.

3.2 Calculate values

Here are the instructions on how I built this chart:

1. Type -10 and -9 in cell A2:A3
2. Select cell A2:A3 and press and hold on the black dot.
3. Drag down until you see the number 10.
   
4. Release the mouse button
5. Select cell B2 and type: =A2^3+3*A2^2-3
6. Press Enter
7. Double press with left mouse button on dot in the lower right corner. This copies the formula to cells below as far as there are cells containing values in column A.
   

3.3 Insert x y scatter chart

1. Go to tab "Insert" on the ribbon
2. Press with mouse on scatter chart icon
3. Press with mouse on the scatter chart you prefer, I chose Scatter with straight lines.

3.4 Change the x and y axis minimum and maximum value

1. Press with right mouse button on on x axis
2. Press with mouse on "Format axis..."
3. Change minimum and maximum value
4. Change major units

Repeat with y axis.

3.5 Format x and y axis

1. Select x axis again
2. Press with mouse on Fill and Line icon
   
3. Select "Solid Line" and pick a color.
   
4. Repeat with y axis

Useful resources

How to Plot an Equation in Excel

4. Build a comparison table/chart

I found a chart that I wanted to show you how to build. It contains values both horizontally and vertically, the intersecting cells are colored based on conditions.

The printable planting guide is found here. This is what the color means:
Light green : Plants grow well together.
Red : Don't plant together.
Dark green : The combination helps bug control.
Yellow : Carrots will have good flavor but stunted roots.
Grey : Beneficial to the garden in general.

Back to top

4.1 How to transpose values vertically to horizontally?

The animated image above shows a smaller set of values being transposed.

1. Select cells in cell range A2:A31.
2. Copy cells.
3. Select the destination cell.
4. Press with left mouse button on the "Paste" button. A pop-up menu appears.
5. Press with left mouse button on the "Transpose" button.

Back to top

4.2 How to orient cell values vertically?

The image above shows cell values in B1:D1 aligned vertically.

1. Select cell range B1:AE1.
2. Press with right mouse button on on cell range B1:AE1. A pop-up menu appears.
3. Press with left mouse button on "Format Cells..." on the pop-up menu. A dialog box shows up, see image below.
   
4. Go to tab "Alignment".
5. Change "Orientation" to 90 degrees, see image above.
6. Press with left mouse button on OK button to apply settings..

Back to top

4.3 How to change column width for multiple columns simultaneously?

The animated image above shows how to change column width by dragging the column border with the mouse.

1. Select columns B:AE.
2. Press and hold with left mouse button on any column border.
3. Drag with mouse until column width is around 20 pixels,
4. Release the left mouse button.

Back to top

4.4 How to apply cell background color using conditional formatting?

The image above shows the cell grid populated with numbers, we are going to color the cell background using Conditional Formatting based on these numbers.

1. Type 1 for green, 2 for red, 3 for grey, 4 for dark green and 5 for yellow in cell range B2:AE31.
2. Select cell range B2:AE31 and go to tab "Home" on the ribbon.
3. Press with left mouse button on "Conditional formatting" button.
4. Press with left mouse button on "New Rule..".
5. Select "Use a formula to determine which cells to format", see image below.
6. Type =B2=1
   
7. Press with left mouse button on "Format..." button.
8. Go to tab "Fill".
9. Pick a color.
10. Press with left mouse button on OK button twice.

Repeat above steps for cell values 2, 3, 4, and 5 but the formulas become =B2=2, =B2=3 , B2=4 and B2=5 and pick different fill colors.

Back to top

4.4.1 Explaining conditional formatting formula

Conditional formatting formula:

Step 1 - Relative cell reference

B2 is a relative cell reference, it changes when the CF formula moves to the next cell. For example, the CF formula moves to cell B3 and the relative cell reference then changes to B3.

This evaluates the contents of each cell and colors the corresponding cell background based on the its cell value.

Step 2 - Cell value is equal to 1

The equal sign compares the contents of cell B2 to 1.

B2=1

becomes

""=1

and returns FALSE.

Step 3 - Evaluate TRUE and FALSE

If the logical expression returns TRUE the cell background changes to green. If FALSE then nothing happens.

Back to top

4.5 How to hide cell values using cell formatting?

The image above shows the cell grid without the numbers only cell background colors. The numbers are still there, however, you can't see them. Select a cell and the number shows up in the Formula bar.

Here is how to hide the numbers in cell range B2:AE31.

1. Select B2:AE31.
2. Press CTRL + 1. A dialog box appears.
3. Press with left mouse button on "Custom" category, see image below.
4. Type ;;;
5. Press with left mouse button on OK button to dismiss the dialog box and apply settings..
   

4.6 How to create cell borders?

The image above shows cell range A1:AE31 selected. Here is how to insert borders to cell range A1:AE31:

1. Select A1:AE31.
2. Go to the "Home" tab on the ribbon.
3. Press with left mouse button on "Borders". A pop-up menu shows up.
   
4. Select "All Borders".

You could color each cell manually, but in my opinion, building this chart is a lot easier using cell numbers.

5. Heat map yearly calendar

The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional formatting formulas and an Excel defined table that allows you to add new events or edit/delete old ones.

5.1 Conditional formatting formulas

What you perhaps want to customize is how events are highlighted. The following table shows how conditional formatting is used.

Event frequency	CF formula
1	=SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=1
2	=SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=2
3	=SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=3
4	=SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=4

This image shows the rules manager for conditional formatting formulas, it shows you the fill color I used and the calendar cell range it is applied to.

5.2 Explaining CF formula

The following CF formula is the one that identifies dates that have only one event scheduled.

Step 1 - Check date in calendar with start column in Excel table

The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference.

Function syntax: INDIRECT(ref_text, [a1])

Check if date in cell B6 is larger than or equal to start dates. The INDIRECT function is needed to be able to use an Excel table name in a Conditional Formatting formula.

The INT function removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

Function syntax: INT(number)

INT(INDIRECT("Table1[Start]"))<=B6

becomes

INT({41282.375; 41330.3333333333; 41312.3333333333; 41351.4166666667; 41365.5; 41421.75; 41438.3333333333; 41448.625; 41283.3333333333; 41283.3333333333; 41312.3333333333; 41313.3333333333; 41314.3333333333; 41357.4166666667; 41366.5; 41408.75; 41408.75; 41449.75; 41462.75; 41472.75; 41613; 41477; 41478; 41479})<=B6

becomes

{41282; 41330; 41312; 41351; 41365; 41421; 41438; 41448; 41283; 41283; 41312; 41313; 41314; 41357; 41366; 41408; 41408; 41449; 41462; 41472; 41613; 41477; 41478; 41479}<=41273

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Step 2 - Check date in calendar with end column in Excel table

INT(INDIRECT("Table1[End]"))>=B6))

returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 3 - Multiply conditions (AND logic)

In order to identify a date inside an event range both conditions must be met.

(INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6)

becomes

{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} * {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

Step 4 - Sum events

The SUMPRODUCT function

Function syntax:

SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))

becomes

SUMPRODUCT({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 0 (zero).

Step 5 - Check if the number of events equal 1

If the sum is equal to 1 then the Conditional formatting formula returns TRUE and the cell is highlighted, if not then the formula returns FALSE and the cell is not highlighted.

SUMPRODUCT((INT(INDIRECT("Table1[Start]"))<=B6)* (INT(INDIRECT("Table1[End]"))>=B6))=1

becomes

0=1

and returns FALSE. Cell B6 is not highlighted.

5.3 Populating the Excel table

The following picture shows the Excel defined table containing the data about each event, the event name, when it starts and when it ends.

5.4 CF formula examples

If you want to highlight cells that have less then 5 events scheduled with a given color then use this CF formula:

If you want to highlight cells that have greater then 5 events and less than 10 events scheduled with a given color then use this CF formula:

There are also CF formulas that hide dates and formatting on each month because some cells show the previous or the next months dates. The calendar looks cleaner without them.

5.5 Get Excel *.xlsx

Heat map calendar.xlsx

Note that no VBA is used in this workbook, however, there is a VBA solution demonstrated below if you prefer that.

5.6 Heat map - VBA Solution

Answer:

This article demonstrates how to highlight events on a yearly calendar based on frequency per day. You will find a link to this workbook at the end of this article.

The color on the calendar gives a rough estimate on the number of events per date.

• No color no events.
• Light color one or a few events.
• Darker color means many events.

You add, edit or delete events to worksheet "Table" and every time you go back to worksheet "Calendar" the colors are refreshed by the macro below.

There is a specific cell next to the calendar that allows you to change the highlight color if you prefer. Press with mouse on that cell and change the cell color to a color you want.

5.6.1 VBA code

1. Press with right mouse button on on sheet Calendar
2. Press with left mouse button on "View Code"
   
3. Copy vba code below
4. Paste code to sheet module
5. Exit VB Editor

Private Sub Worksheet_Activate()
Dim CRng As Variant
Dim Dt As Variant
Dim CDt As Variant
Dim Cnt As Integer
Dim r As Long
Dim c As Long
Dim St As Integer
Application.ScreenUpdating = False
CRng = Worksheets("Calendar").Range("B6:X38").Value
With Worksheets("Table")
    For r = 1 To UBound(CRng, 1)
        For c = 1 To UBound(CRng, 2)
            If CRng(r, c) <> "" Then
                For CDt = 1 To .Range("Table1[Start]").Cells.Count
                    If CRng(r, c) >= Int(.Range("Table1[Start]").Cells(CDt).Value) And CRng(r, c) <= Int(.Range("Table1[End]").Cells(CDt).Value) Then Cnt = Cnt + 1 End If Next CDt End If If Cnt > St Then St = Cnt
        Cnt = 0
        Next c
    Next r
End With
Set Rng = Worksheets("Calendar").Range("B6:X38")
    'Remove previous formatting
    With Rng.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Worksheets("Table")
        For Each Dt In Worksheets("Calendar").Range("B6:X38")
            For CDt = 1 To .Range("Table1[Start]").Cells.Count
                If Dt >= Int(.Range("Table1[Start]").Cells(CDt).Value) And Dt <= Int(.Range("Table1[End]").Cells(CDt).Value) Then Cnt = Cnt + 1 End If Next CDt If Cnt > 0 Then
            With Dt.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = Worksheets("Calendar").Range("AB5").Interior.Color ' xlThemeColorAccent4 '
                .TintAndShade = 1 - (Cnt / St)
                .PatternTintAndShade = 0
            End With
            'Reset counter a
            Cnt = 0
            End If
        Next Dt
    End With
Application.ScreenUpdating = True
End Sub

5.6.2 WorkSheet "Table"

The picture below shows the events in an Excel defined table named [Table1].

You don't need to adjust cell references or formulas everything is automatic, Excel defined Tables are great in that aspect.

You can find a heat map monthly calendar here.

5.6.3 Get excel *.xlsm file

Heat map calendar.xlsm

6. Advanced Gantt Chart Template

This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are green and remaining days are red, see image above.

The chart utilizes an Excel defined Table in a very efficient way, you don't need to adjust chart data source ranges when you add or delete records. The Excel defined Table takes care of that for you.

6.1 How to build worksheet

First you need to convert your data into an Excel defined Table.

6.1.1 Excel defined Table

1. Select a cell in your data set.
2. Press CTRL + T.
   
3. Enable the checkbox if the data set contains column headers.
4. Press with left mouse button on OK.

Now you need to create a stacked bar chart.

6.1.2 Insert a stacked bar chart

1. Select one of the cells in the Excel defined Table.
2. Go to tab "Insert" on the ribbon.
3. Press with mouse on "Insert Column or Bar Chart" button.
4. Press with mouse on "Stacked Bar chart" button.
   
5. A chart appears, however, we need to customize it quite a lot before we can use it.
   

6.2 Customize stacked bar chart

The data source ranges are not what we want but we can easily change those.

1. Press with right mouse button on on chart
2. Select "Data source..."
3. Change the first Legend entry "Days" so i points to column "Start Date" in the Excel defined Table".
   
4. Press with left mouse button on "Edit" button (Horizontal (Category) Axis Labels and change the cell reference so it points to the "Activity" column on the Excel defined Table.
5. Press with left mouse button on OK button.

The chart now looks like this:

We are now going to remove the blue bar.

1. Double-press with left mouse button on one of the blue bars to open the format pane.
2. Press with mouse on the Fill & Line button.
3. Select "No fill".

If you want the tasks to be arranged in reverse order then follow these steps.

1. Double press with left mouse button on the y-axis categories to open the Format Pane, see image above.
2. Press with mouse on "Axis Options" button.
3. Press with left mouse button on the checkbox "Categories in reverse order.

The image now looks like this, the dates are however in a mess.

1. Double-press with left mouse button on x-axis values to open the Format Pane.
2. Press with mouse on "Axis Options" button.
3. The "minimum" value is the number equivalent to an Excel date, in this case, 10/24/2011.  The first date range starts at 20/11/2011 which is number 40868. I will use 40867.
4. Feel free to change the maximum value as well.

I recommend that you apply minor gridline to make the chart easier to read.

1. Press with right mouse button on on x-axis values.
2. Press with left mouse button on "Add minor gridlines".
3. Change the "Minor" value to 1 in the Format Pane.
4. Select and delete the major gridlines.

Repeat above steps but this time with y-axis values.

If you prefer the x-axis values below the chart then double-press with left mouse button on the y-axis values to open the Format Pane.

Press with left mouse button on "Horizontal axis crosses" At maximum category.

Recommended blog posts

Gantt Chart with Repeated Tasks via Excel XY Chart | Peltier Tech Blog 

7. Dynamic Gantt chart

This section demonstrates how to create a dynamic Gantt chart. A Gantt chart helps you plan and track various elements of a project. A dynamic chart automatically adds new values to the chart. Let's start!

Create a table

1. Select cell range (A1:D7)
   
2. Press with left mouse button on "Insert" tab on the ribbon
3. Press with left mouse button on "table" button
4. Press with left mouse button on ok!

Create a stacked chart

1. Select table
2. Go to "Insert" tab
3. Press with left mouse button on "Bar chart" button and then press with left mouse button on "Stacked bar" button.

Setting up the stacked chart

1. Press with right mouse button on a blue bar
   
2. Press with left mouse button on "Format Data Series..."
3. Press with left mouse button on "Fill"
4. Press with left mouse button on "No Fill"
5. Press with left mouse button on OK!
   
6. Press with right mouse button on on chart
7. Press with left mouse button on "Select Data"
   
8. Select "Finish Date"
9. Press with left mouse button on "Remove" button
10. Press with left mouse button on OK!

Format x-axis

1. Press with right mouse button on on x axis dates
   
2. Press with left mouse button on "Format axis.."
   
3. Change "Minimum:" and "Major unit:" to fixed
4. Change "Minimum" value to 40547.
5. Change "Major unit:" value to 7.
   
6. Press with left mouse button on "Alignment"
7. Change "Text direction:" to Rotate all text 270
8. Press with left mouse button on Close

Setting up the legend

1. Press with left mouse button on text "Start date" in legend
2. Delete
3. Press with right mouse button on on chart
4. Press with left mouse button on "Select Data"
5. Press with left mouse button on "Duration"
6. Press with left mouse button on Edit
   
7. Change "Series name:" to Sheet4!$A$1 (Activity)
8. Press with left mouse button on OK!

8. How to replace columns with pictures in a column chart

I found an interesting chart on CNN's website: Rise of the supersize rugby player It shows the average height of athletes for the past 40 years. Check it out.

It made me think how can I do this in Excel? First I drew this nice man, see the image below. I am going to use this picture in my chart. It is not as nice as the other chart but it will do for this demonstration.

I searched for average heights around the world and found some statistics: Wikipedia. I chose a few countries and built a small data set, I will use this data in my example chart.

8.1. Create a column chart

1. Select the data, see image above.
2. Go to tab "Insert" on the ribbon.
3. Select a new clustered column chart.

Back to top

8.2. Delete the chart legend

1. Press with left mouse button on the chart legend to select it.
2. Press Delete on your keyboard to remove it from the chart.

Back to top

8.3. Change chart gap width

1. Press with right mouse button on on the data series.
2. Press with left mouse button on "Format Data Series...". You will now see a dialog box or a settings pane depending on what Excel version you are using.
3. Press with left mouse button on Series Options.
4. Change gap width to 20%.
   
5. Close the dialog box.

Back to top

8.4. Insert a picture to chart column

1. Press with left mouse button on "Fill and Line"
2. Press with left mouse button on "Picture or texture fill".
3. Press with left mouse button on the "Insert.." button.
   
4. A dialog box appears. Select a picture file you want to use.
5. Press with left mouse button on Insert.

Back to top

8.5. Change min y-axis value

1. Press with right mouse button on on chart y-axis, see image above. A popup menu shows up.
2. Press with left mouse button on "Format axis..."
3. Press with left mouse button on "Axis Options".
   
4. Change the minimum value to 0 (zero).
5. Press with left mouse button on OK.

Back to top

Back to top

9. Schedule project dates based on a finish date

Answer:

I calculated the duration for each step (column E) and the days between each step (column F). I then used the calculations in column E and F to calculate new dates in cell range E15:D23 based on a new finish date in cell D24.

The following formula calculates the number of days between From: and To: dates.

Formula in cell E3:

The IF function checks if cell C3 is blank and then returns 0 (zero) if TRUE and D3-C3 if FALSE. Copy cell E3 and paste to cell range E4:E11.

The formula below calculates the number of days between tasks.

Formula in cell F3:

Copy cell F3 and paste to cell range F4:F11.

9.1 Calculate new dates based on finish date

The following formula in cell C15 checks if C3 is blank and returns a blank if TRUE or returns D15-E3 if FALSE. D15-E3 calculates the From: date by subtracting the duration from the To: date.

Formula in cell C15:

Copy cell C15 and paste to cell range C16:C23.

The formula below calculates the To: date by subtracting the number of days between the tasks from the To: date of the next task.

Formula in cell D15:

Copy cell D15 and paste to cell range D16:D23.

The formulas above make it possible to change the date in cell D24 and the other project dates will follow based on the calculations made in the first table.

This Gantt chart shows the processes and the days between, green days are the task duration and red days are the number of days between the current task and the next task.

Read more about Gantt charts here: Dynamic Gantt charts

10. Color chart columns based on cell color

This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is based on a regular column chart and the second example shows a stacked bar chart.

The image above shows a data table in cell range A1:B6 containing continents in the world and a random number.

What's on this page

1. Format fill color on a column chart based on cell color
2. Get Excel *.xlsm file
3. Change stacked bar colors
4. Get Excel *.xlsm file

The chart graphs the regions and numbers using columns, categories are distributed horizontally (x-axis) and numbers vertically (y-axis) from 0 to 45 with increments of 5.

To the right of the data table is a button linked to a VBA macro. When you press the button named "Color chart columns" macro ColorChartColumnsbyCellColor is rund.

The VBA macro goes through each cell in the chart data source, copies the cell color and applies the same color to the corresponding chart column.

VBA code

'Name macro
Sub ColorChartColumnsbyCellColor()

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Sheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection(1)

'Save chart data source range from first chart on worksheet Sheet1
Set vAddress = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))

'Iterate through each cell in data source range
For i = 1 To vAddress.Cells.Count

'Copy color from cell to bar
.Points(i).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(vAddress.Cells(i).Interior.ColorIndex)

'Continue with next cell
Next i
End With
End Sub

Where to put the code?

The VB Editor allows you to build macros and UDFs in Excel. To the left is the "Project Explorer" window and to the right is a window where you put your code.

The Project Explorer window lets you choose which open workbook to use and if you want to save your macro in a worksheet module or a regular module.

1. Copy VBA code.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Press with mouse on "Insert" on the top menu.
4. Press with mouse on "Module", the module name appears below your workbook in the "Project Explorer" window.
5. Paste VBA code to window, see image above.
6. Exit VB Editor and return to Excel.

How to create and link a button?

1. Go to tab "Developer" on the ribbon.
2. Press with mouse on the "Insert" button on the ribbon.
3. Press with mouse on "Button".
4. Press and hold with left mouse button on your worksheet.
5. Drag with mouse until you got the size you want.
6. Release left mouse button.
7. A dialog box appears allowing you to assign a macro.
8. Press with left mouse button on macro "ColorChartColumnsbyCellColor" to select it.
9. Press with left mouse button on "OK" button.

You can now press with left mouse button on the button to trigger the macro.

Back to top

Change stacked bar colors programmatically

The picture above shows a stacked bar chart and a data table with colored columns, each category has it's own color based on the corresponding data table column.

The macro below lets you color the bars with the same color as the source range.

How to use macro

You select the stacked bar chart you want to color differently. Make sure you have colored the source cell range. Go to "Developer" tab on the ribbon, press with left mouse button on "Macros" button. Select "ColorChartBarsbyCellColor" and press with left mouse button on OK.

Series 1 (Asia) has it's source values in cell range B2:B5. The color in that cell range matches the color in the stacked bar chart.

VBA

'Name macro
Sub ColorChartBarsbyCellColor()

'Dimension variables and declare data types
Dim txt As String, i As Integer

'Save the number of chart series to variable c
c = ActiveChart.SeriesCollection.Count

'Iterate through chart series
For i = 1 To c

'Save seriescollection formula to variable txt
txt = ActiveChart.SeriesCollection(i).Formula

'Split string save d to txt using a comma ","
arr = Split(txt, ",")

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveChart.Legend.LegendEntries(i)

'The SET statement allows you to save an object reference to a variable, the image above demonstrates a macro that assigns a range reference to a range object.
'Save a range object based on variable arr to variable vAdress
Set vAddress = ActiveSheet.Range(arr(2))

'Copy cell color from cell and use it to color bar chart
.LegendKey.Interior.Color = ThisWorkbook.Colors(vAddress.Cells(1).Interior.ColorIndex)
End With

'Continue with next series
Next i
End Sub

Where to put the code?

Back to top

11. How to color chart bars based on their values

All columns in this chart are blue.

1. Press with right mouse button on on a column on the chart.
2. Press with mouse on "Format Data Series...".
3. Press with left mouse button on "Fill" button.
   
4. Press with mouse on "Solid fill".
5. Press with left mouse button on "Invert if negative", see the image above.
6. Pick a color for positive values.
7. Pick a color for negative values.

12. How to color chart columns based on a condition?

What if you want to color bars by comparing?

This chart example shows bars colored differently depending on the preceding value. If a value is larger than the previous one, it will be colored blue. Smaller than the previous value and the bar will be red.

12.1 How to build

The trick here is to split data into two different chart series, you can do that by placing them in two columns using formulas.

Formula in cell B2: =IF(A3>A2,A3,"")

Formula in cell C2: =IF(A3<A2,A3,"")

Copy these cells and paste them on cells below, as far as needed.

12.2 Insert chart

Now it is time to build the column chart, shown above.

1. Select values in in column A
2. Go to tab "Insert" on the ribbon
3. Press with mouse on "Insert column chart" button

12.3 Add data series

1. Press with right mouse button on on columns and press with left mouse button on "Select Data..."
   
2. Press with left mouse button on "Edit" button below "Legend Entries (Series)"
   
3. Press with left mouse button on "Series values" button and select cell range B2:B33
4. Press with left mouse button on OK
5. Press with left mouse button on "Add" button
6. Select cell range C2:C33
7. Press with left mouse button on OK

The chart changes to this:

12.4 Change chart gap width and series overlap

You can see that there are gaps between series.

1. Press with right mouse button on on a column
2. Press with left mouse button on "Format Data Series..."
   
3. Change "Series Overlap" to 100%
   

This is what the chart looks like:

13. How to color chart bars/columns based on multiple conditions?

The image above demonstrates a chart that has bars/columns colored based on multiple conditions. It shows colored columns based on quarter, the color corresponds to the quarter number.

13.1 Prepare data

The image above shows the data, it is divided into four different columns. Each column corresponds to a quarter and is its own chart series.

You can create a formula that populates columns F to I accordingly based on the Month name in column D if you don't want to copy the values manually and paste them to their destinations cells.

Columns B and C are only there two create categories based on year and quarter for the months on the chart. The chart shows this on the x-axis (horizontal axis), the year and quarter are displayed below the months.

13.2 Explaining formula in cell F3

Step 1 - Calculate the relative position of the given item in the array

The MATCH function returns a number representing the relative position of an item in a cell range or array.

MATCH($D3, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)

becomes

MATCH("Jan", {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0)

and returns 1.

Step 2 - Calculate the quotient

The QUOTIENT function returns the integer portion of a division.

QUOTIENT(MATCH($D3, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0), 4)+1

becomes

QUOTIENT(1, 4)+1

becomes

0+1

and returns 1.

Step 3 - Compare with column

The equal sign compares the values and returns TRUE if they match and FALSE if not. The COLUMNS function returns a number representing the number of columns in a cell range.

(QUOTIENT(MATCH($D3, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0), 4)+1)=COLUMNS($F$2:F2)

becomes

1=COLUMNS($F$2:F2)

becomes

1=1

and returns TRUE.

Step 4 - Show value in cell if condition is met

The IF function returns one argument if the logical expression evaluates to TRUE and another if FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF((QUOTIENT(MATCH($D3, {"Jan"; "Feb"; "Mar"; "Apr"; "May"; "Jun"; "Jul"; "Aug"; "Sep"; "Oct"; "Nov"; "Dec"}, 0), 4)+1)=COLUMNS($F$2:F2), $E3, "")

becomes

IF(TRUE, $E3, "")

The IF function returns the value in cell E3 if TRUE and nothing if FALSE.

IF(TRUE, $E3, "")

becomes

IF(TRUE, 0.1, "")

and returns 0.1.

13.3 Insert column chart

1. Select cell range F2:I26.
2. Go to tab "Insert" on the ribbon.
3. Press with mouse on "Column Chart" and a popup menu appears.
4. Press with mouse on "Clustered Column", a chart appears on the screen see image above.

13.4 Add values to the x-axis

1. Press with right mouse button on on the chart.
2. Press with mouse on "Select Data...", a dialog box appears.
   
3. Press with left mouse button on the "Edit" button. Another dialog box shows up on the screen.
   
4. Select cell range B3:D26.
5. Press Enter, press with left mouse button on "OK" button. You are now back to the first dialog box.
6. Press with left mouse button on "OK" button to dismiss the dialog box.

13.5 Change gap width and series overlap

1. Press with right mouse button on on any bar or column on the chart. A menu appears on the screen.
2. Press with mouse on "Format Data Series...". A pane shows up on the right side of the screen, see image above.
3. Press with mouse on "Series Options" button to access Series Overlap settings.
4. Change "Series Overlap" to 65%.
5. Change "Gap width" to 0%.
6. Close pane.

13.6 Change column colors

1. Press with right mouse button on on any bar or column on the chart. A menu appears on the screen.
2. Press with mouse on "Format Data Series...". A pane shows up on the right side of the screen, see the image above.
3. Press with left mouse button on the "Fill & Line" button.
4. Press with mouse on the black triangle next to "Fill" to expand settings.
5. Press with left mouse button on radio button "Solid fill".
6. Press with mouse on Color, see image above.
7. Pick a color.
8. Press with mouse on another series on chart
9. Repeat step steps 5 to 8 until all series have been changed.
10. Close settings pane.

14. Add pictures to a chart axis

This section demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country names and their corresponding flags below each column.

14.1. How to insert pictures above column chart item names

Watch this video to learn how to build the above chart or follow the instructions below the video.

Back to top

14.1.1 How to create a column chart

1. Select a data range
   
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on the "Column" button, a pop-up menu appears. See the image below.
   
4. Press with mouse on the "Clustered column" button.

Back to top

14.1.2 How to insert pictures above column chart names

1. Press with right mouse button on on the second series and press with left mouse button on "Change Series Chart Type...".
   
2. Change it to x y (scatter) and press with left mouse button on "Scatter with only markers".
3. Remove axis and legend
   
4. Press with right mouse button on on x axis and press with left mouse button on "Format Axis..."
   
5. Change label distance to 500
   
6. Press with left mouse button on a x y scatter data point
   
7. Paste a picture. I resized the picture to 25 px width first.
   
8. Repeat step 7 and 8 with the remaining pictures.

Back to top

14.2. How to add pictures above chart columns

The image above shows pictures above each column, I will below describe how I built this chart.

14.2.1 Insert column chart

1. Select cell range B3:C7.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on the "Column" chart button. A popup menu appears.
4. Press with left mouse button on the "Clustered column" button.

Back to top

14.2.2 Add a second series

1. Press with right mouse button on on the chart. A popup menu shows up.
2. Press with mouse on "Select data...". A dialog box appears.
3. Press with left mouse button on "Add" button. Another smaller dialog box is now visible on the screen, see image above.
4. Press with left mouse button on "up pointing arrow" next to "Series values:".
5. Select cell range D3:D7.
6. Press with left mouse button on the OK button.
7. Press with left mouse button on the OK button again.

Back to top

14.2.3 Change chart type to scatter chart

1. Press with right mouse button on on the second series shown on the chart. A popup menu appears.
2. Press with mouse on "Change Chart Type...". A dialog box shows up.
3. Change the second series to an x y scatter chart, see image above.
4. Press with left mouse button on the OK button.

Back to top

14.2.4 Add pictures to markers

1. Doublepress with left mouse button on the first dot to open the settings pane.
2. Press with left mouse button on again on it to select it. You don't want the entire series selected.
3. Press with left mouse button on "Fill & Line" button on the settings pane.
   
4. Press with mouse on "Marker Options" to expand settings.
5. Press with left mouse button on radio button "Built-in".
6. Press with left mouse button on drop-down list next to Type.
7. Press with mouse on image button located at the very end of the drop down-list, see image above.
8. Select "From a File".
9. A dialog box appears, select a file name.
10. Press with left mouse button on "Open".

Repeat above steps with remaining images.

Change the values in cell range D3:D7 so the images appear above the columns. You can also use the same value in all cells to align images above all columns if you prefer that.

Back to top

14.3. How to add pictures next to y-axis values

The image above shows pictures next to the y-axis in a column chart. I will below describe how I built this chart, basically, it is two different chart types combined into a combo chart.

14.3.1 Create a column chart

1. Select cell range B3:C7.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on the "Insert column or bar chart" button. A pop-up menu appears.
4. Press with left mouse button on the "Clustered column" button.
5. A chart appears, see image above.

Back to top

14.3.2 Add a second series

1. Press with right mouse button on on the chart. A context menu appears.
2. Press with mouse on "Select Data...". A dialog box shows up.
3. Press with left mouse button on the "Add" button. Another smaller dialog box appears, see image above.
4. Press with left mouse button on the "Arrow pointing up" button next to "Series values:".
5. Select cell rage F3:F7.
6. Press with left mouse button on "OK" button.
7. Press with left mouse button on "OK" button again to dismiss the larger dialog box.
   The chart now looks like the one above.

Back to top

14.3.3 How to change the chart type of the secondary series?

1. Press with right mouse button on on the second series. A popup menu shows up.
2. Press with left mouse button on "Change Series Chart Type...". A dialog box appears, see image above.
3. Press with left mouse button on the "drop-down" button that corresponds to "Series2".
4. Press with left mouse button on the "Scatter" chart button to select it.
5. Press with left mouse button on the "OK" button.

Back to top

14.3.4 Add x-axis values to series2

1. Press with right mouse button on on the red dots.
2. Press with left mouse button on "Select data...". A dialog box appears.
3. Press with mouse on "Series2" to select it.
4. Press with mouse on the "Edit" button.
5. Press with left mouse button on the "Arrow pointing up" next to Series X values:".
6. Select cell range G3:G7.
7. Press with left mouse button on "OK" button.
   The chart now looks like the one shown in the above image.

Back to top

14.3.5 Insert chart marker pictures

1. Press with mouse on a red dot twice to select it.
2. Press with right mouse button on on the selected dot. A popup menu appears.
3. Press with mouse on "Format Data Point...". A settings pane appears probably on the right side of your screen.
4. Press with mouse on the "Fill & Line" button, see the image below.
   
5. Press with mouse on "Marker Options" to expand settings.
6. Press with mouse on the radio button "Built-in".
7. Press with mouse on the drop-down list to expand it.
8. Press with mouse on the image button located at the very bottom.
9. A dialog box appears.
10. Press with mouse on "From a File". A dialog box shows up.
11. Select the image you want to use.
12. Press with left mouse button on "Open" to use it.
    The image appears on the chart where the dot was located.

Repeat the above steps with the remaining dots and images you want to use.

Back to top

14.3.6 Final adjustments

14.3.6.1 Remove major gridlines

1. Press with mouse on a horizontal gridline to select all grid lines.
2. Press with left mouse button on the "Delete" key to remove them.

Back to top

14.3.6.2 Move y-axis to category 1

1. Doublepress with left mouse button on the x-axis to open the settings pane.
2. Press with mouse on the "Axis Options" button.
   
3. Press with mouse on "Axis Options" to expand settings.
4. Press with mouse on the radio button "At category number", type 1.
5. Press with mouse on the worksheet to apply settings.

Back to top

14.3.6.3 Add y-axis line

1. Double press with left mouse button on y-axis values to open the settings pane.
2. Press with mouse on "Fill & Line" button.
3. Press with left mouse button on "Line" to expand settings.
   
4. Press with left mouse button on "Solid Line"
5. Pick a line color.

Back to top

3.6.4 Remove 0 (zero) from chart axis

1. Double-press with left mouse button on y-axis values to open the settings pane.
   
2. Press with mouse on the "Axis Options" button.
3. Press with mouse on "Number" to expand settings.
4. Press with mouse on the drop-down list below "Category".
5. Select "Custom".
6. Change type to 0;;
7. Press with mouse on the worksheet to apply settings.

Back to top

14.4. How to add pictures to a bar chart axis

You can use the same technique with a bar chart.

Back to top

Back to top

Built-in Charts

Combo Charts

Combined stacked area and a clustered column chart
Combined chart – Column and Line on secondary axis
Combined Column and Line chart

Chart elements

Chart basics

How to create a dynamic chart
Rearrange data source in order to create a dynamic chart
Use slicers to quickly filter chart data
Four ways to resize a chart
How to align chart with cell grid
Group chart categories
Excel charts tips and tricks

Custom charts

How to build an arrow chart
Advanced Excel Chart Techniques
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template

Sparklines

Win/Loss Column Line

Highlight chart elements

Highlight a column in a stacked column chart no vba
Highlight a group of chart bars
Highlight a data series in a line chart
Highlight a data series in a chart
Highlight a bar in a chart

Interactive charts

How to filter chart data
Hover with mouse cursor to change stock in a candlestick chart
How to build an interactive map in Excel
Highlight group of values in an x y scatter chart programmatically
Use drop down lists and named ranges to filter chart values
How to use mouse hover on a worksheet [VBA]
How to create an interactive Excel chart
Change chart series by clicking on data [VBA]
Change chart data range using a Drop Down List [VBA]
How to create a dynamic chart

Animate

Line chart Excel Bar Chart Excel chart

Advanced charts

Custom data labels in a chart
How to improve your Excel Chart
Label line chart series
How to position month and year between chart tick marks
How to add horizontal line to chart
Add pictures to a chart axis
How to color chart bars based on their values
Excel chart problem: Hard to read series values
Build a stock chart with two series
Change chart axis range programmatically
Change column/bar color in charts
Hide specific columns programmatically
Dynamic stock chart
How to replace columns with pictures in a column chart
Color chart columns based on cell color
Heat map using pictures
Dynamic Gantt charts

Stock charts

Build a stock chart with two series
Dynamic stock chart
Change chart axis range programmatically
How to create a stock chart

Excel categories

Comments (57)