how to Creat a chart like in "MSN Money"

[Danyspl]

Dears

hope all well

Need some thoughts and help.
Was looking on "MSN Money"
http://www.msn.com/en-sg/money/indexdetails/fi-151.10.FTSTI

Really liked the idea, and would like to do it in excel.

Point #1:
how you can click on the dot and to get the dot changing the color.

If there is solution, I believe it could be applied for Bar Charts: for example - you click on bar chart and the selected bar changes the color...on top label shows relevant metrics..

Point #2:
If you select the particular dot, label appears on top.
Any Idea how it is done?

Thank you in advance!
Dany

 

[Luke M]

Hi Danyspl,

Could get pretty close. First, we can use a chart_select event to detect when user picks a marker. Next, we can have a second series which serves as the highlighted point. See attached.

 

[Danyspl]

Hey Luke

Thank you for the prompt reply.
Will check it out.

Cheers!
Dany

 

[Danyspl]

Hey Luke
Thank you. it works!

have short one: how you are able to show only chart area in Chart 1 sheet and to hide all other cells? any way to see all the sheet? it gave me good Idea on dashboard design..basically to lock dashboard area the same way you did with the chart.

Thank you again!
Dany

 

[Luke M]

Hi Dany,

Not sure you'll like the answer. The chart is in it's own sheet, not a worksheet. You can do this with any chart by right-clicking, move chart, and then choose a new sheet. Upside is that this is the only way to get access to the chart event type macros. Downside is that it doesn't work so well for a big dashboard type design.
We could add some basic text boxes on the chart sheet to bring over some information, like an overall total, or highlight something. But for a big dashboard, that may not work.

 

[Danyspl]

Thank you. Really appreciate prompt reply.
...it is good solution.
i am sure it will come with Excel 2020 or something for dashboards..

 

[NARAYANK991]

Hi ,

You might find this interesting :

http://www.databison.com/stock-chart-with-scroll-and-zoom/

Narayan

 

[Danyspl]

Dear Narayan

Thank you. looks interesting and no need for VBA.
Will try to implement.

Thank you for prompt support!

Dany

 

[Danyspl]

Dears

Was trying to use all the comments.
The solution is quite Close to MSN..but have to select the time period to show on chart via clicking on the relevant period.
Please see the attached file for more details and steps.

• Using Excel '13.
• It is macro, So don't forget to enable Macros & save as "xlsm".

Macro helps to highlight the selected period:

• Macro to identify the address of the cell.
• once month selected, address of the cell calculated
• address then being use to show what is in the selected cell

Use several data ranges:

• one to show all the points
• one to show only the selected point (circle will appear), so once the month is chosen the data of this month will be shown on the chart.

Linked Labels to value from the cells and aligning all the labels in one place:

• Adding additional line above the data range...+50 above the Max point of original data (in my case it is 50 as the data is up to 150).
• Add label for this line data.
• Then Label to contain value from the cells.

More in the attached..

It is 80% MSN...the 20% is to find solution to see the same by clicking on the chart itself (and not by selecting the month)..so for 20% still working to find solution (have seen some Mouse Tracking Macro..)

Will be happy to hear your view and recommendations...

Regards!
Dany