Step 1: Select Chart Data
Step 2: Name the Range as “DataRange”
Step 3: Create Chart using Chart Data
Insert Tab -> Column Chart (under Charts Group)
Step 4: Resize the Chart into Range (as you wish); Select the Range behind the Chart & Name it as “Chart”
Step 5: Move to the Sheet2 & Click Any Cell & Change BG Color; Click Data Tab -> Data Validation.
In Data Validation Dialog Box Select List Criteria & type Source as “Chart, DataRange” (Same as given in naming the Ranges)
Step 6: Copy the Chart from Sheet1 & Paste in Sheet2, Paste as Picture
Step 7: In Formulas Tab, Click Name Manager; In Name Manager Dialog Box Click New.
In New Name Dialog Box Type Name as “ShowChart” &
Refers to as “=If(Sheet2!$C$4="Chart",Chart,DataRange)” , then Close.
Step 8: In Sheet 2 Select the Chart Picture & type in the Formula Bar as “=ShowChart”. Now in Drop Down Menu(In Cell C4), Select Data Range to display chart data & select Chart to display.