Excel Graphs

Hi Sir/Madam,

Can someone please help me with dynamic charting and here are the requirements.

I am required to develop a similar graph as per the screen shot in the attached word document.
X-axis will be dates

Y-axis will be Rank in the reverse order. (High ranks will show lower in the graph and low ranks will show on the top on the Y-axis.)

The keywords to be in a drop down menu where by I can select the keyword and the respective graph for the particular keyword is then displayed. I want to have the graph on the separate tab with sample data on another tab.

The sample data to develop the graph is available in the attached excel.

Thanks!
Amita

 sample-data-new.xlsx dynamic-charting-sample-graph-ou.docx
deoyagyaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
With a different data layout of the source data, you can create a better pivot chart. See attached. Select the keyword in the dropdown at the top and select the Search engine in the legend dropdown.


Copy-of-sample-data-new.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

see attached. You need to create range name with the series names and use that in the data validation. The chart range also uses a range name, which is dynamically created with an Offset formula based on the value selected in the data validation list.

See attached

cheers, teylyn

chart.xlsx
0
 
wellousCommented:
Hi Amita,

Pls send a photo sample (jpg,gif,bmp ...etc) instead of the docx which i can't open it.
Because i couldn't understand how it should looks.

Brgds
Wellous
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@Wellous, the Word docx opens fine. Maybe you have an Office version earlier than 2007 and need to install the free compatibility pack?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You need to create a pivot table to sum keywords by date. Then you can create a pivot chart.

cheers, teylyn


Copy-of-sample-data-new.xlsx
0
 
deoyagyaAuthor Commented:
Wellous,

I am attaching the screen grab of the sample graph. I am looking for the similar thing done where user can anytime change the parameters using the drop down. EVen the legends can be changed using the drop down (Green line, Blue line etc).

Thanks,
Amita
 sample excel graph
0
 
deoyagyaAuthor Commented:
teylyn - Thanks for taking time to complete this for me. However, the format of the excel is different than what I have specified in the sample graph. I am not very good at excel, therefore I am looking for the solution that is easy customizable for the varying requirements.


As stated in recent comments, the graph parameters can be changed using the drop down. Please let me know if this is something you can help me achieve.

Thanks,
Amita
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
In the last file I attached, you can select a keyword from a drop-down and a search engine from another drop-down. This is only possible with the source data arranged as in that file.

If the source data is arranged like in your original file, then you will not achieve these charting results.

Charts rely very much on the layout of the source data. Some things just can't be done if the source data is not arranged in a certain way.

I've given you a working solution for your question. Maybe you need to follow up with another question on how to convert your original source data to the layout required for the Pivot Chart.

cheers, teylyn
0
 
wellousConnect With a Mentor Commented:
Hi Amita,

I am attaching my work now to check with you if i am working in the same way you want or you need different stuff?
Cheers ,
Wellous
sample-4-Amita.xls
0
 
wellousCommented:
@teylyn , thank you
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Amita,

I don't understand this:

>> teylyn - Thanks for taking time to complete this for me. However, the format of the excel is different than what I have specified in the sample graph. I am not very good at excel, therefore I am looking for the solution that is easy customizable for the varying requirements.

The sample graph you have attached has a completely different data set, so it's not easy to guess what you need from your current data set. Assuming that you want to be able to chart one or more keywords for one or more search engine and let the user pick any combination of keyword(s) and Search engine(s), that is what I have provided.

Please let me know what is not working for you.

cheers, teylyn
0
 
deoyagyaAuthor Commented:
Hi Teylyn,

I have sent the sample graph to my boss. As soon as I get an OK... I will be able to award the points to you. Sorry for the delay :(


Thanks,
Amita
0
 
deoyagyaAuthor Commented:
Hi Wellous,

Can you give some instructions to reproduce the graph as you have created. I am unable to reproduce with the exact results.

Thanks,
Amita
0
 
wellousCommented:
Hi Amita,
Simplly i modified your table a little bit to meet what i could guess from your needs,
To work in the same way ,maybe this example will make you understand my idea..

* whenever you add a new row in the table ( new date, data(Ranks) or even new key ,,,etc) it will appear in yr chart as it is..

Pls check the attachement,
Good luck & apna dhayan rakhay ;)
Brgds,
Wellous


sample-4-Amita.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.