WISDOMBYDATA
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me

Creating a Gantt chart using Conditional Formatting in MS Excel

7/31/2016

0 Comments

 


The conditional formatting feature of MS excel enables us to create a Gantt charts very similar to a MS project Gantt chart. Consider the table below demonstrating a subset of the testing activities that need to be performed during a software testing cycle of a project. 
Picture
Using the five steps explained below we could create a Gantt chart based on the start and end sates for each of the testing periods.

STEP 1] First select the range of cells that you would like conditional formatting to be applied to and then go to the home tab of the ribbon, select “Conditional Formatting” and click on “New Rule”.
Picture
 STEP 2] Enter a formula similar to the one below. After inputting the formula, click on “Format” and proceed to step 3.
Picture
 How does this formula work?

=AND(F$4>=$D5, F$4<=$E5)

This formula basically provides a value of true whenever the dates located on row four, fall between the respective Start Date and End Date for each phase. Note that in the formula above it is require to reference lock the values using the $ sign so that the correct range is applied. For example, the $ in F$4 creates a horizontal reference lock and the $ before D5 (I.e. $D5) creates a vertical reference lock.
 
STEP 3] in the “Fill” section chose a color of your choice and click on “OK”
Picture
 STEP 4] Click on “Ok” a second time.
Picture
​STEP 5] Here is the desired Gantt chart
Picture

0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    Categories

    All
    BI
    EXCEL
    MISC
    SQL

    Archives

    June 2020
    May 2020
    April 2020
    March 2020
    February 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013

Powered by Create your own unique website with customizable templates.
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me