Solved

Excel Graphs

Posted on 2011-03-03
14
959 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
  • 6
  • 4
  • 4
14 Comments
 
LVL 50

Expert Comment

by:teylyn
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

Expert Comment

by:teylyn
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
 
LVL 50

Expert Comment

by:teylyn
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:
teylyn 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Expert Comment

by:teylyn
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

Expert Comment

by:teylyn
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now