Solved

Excel Graphs

Posted on 2011-03-03
14
972 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:deoyagya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 50
ID: 35031014
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
 
LVL 5

Expert Comment

by:wellous
ID: 35031112
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
 
LVL 50
ID: 35031952
@Wellous, the Word docx opens fine. Maybe you have an Office version earlier than 2007 and need to install the free compatibility pack?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50
ID: 35032083
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 350 total points
ID: 35032142
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
 

Author Comment

by:deoyagya
ID: 35033810
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
 

Author Comment

by:deoyagya
ID: 35033818
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
 
LVL 50
ID: 35033841
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
 
LVL 5

Assisted Solution

by:wellous
wellous earned 150 total points
ID: 35039536
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
 
LVL 5

Expert Comment

by:wellous
ID: 35039545
@teylyn , thank you
0
 
LVL 50
ID: 35041307
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
 

Author Comment

by:deoyagya
ID: 35043570
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
 

Author Comment

by:deoyagya
ID: 35043915
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
 
LVL 5

Expert Comment

by:wellous
ID: 35044683
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

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question