Solved

Graph based on Date Range

Posted on 2011-03-03
9
598 Views
Last Modified: 2012-05-11
I would like to be able to:
* Redraw graph based on date range
* Show PIE chart based on date range to show were time is being allocated
Additonally, is there way to format this data so as the weeks are appended on (up to a 52 weeks), the spreadsheet doesn't get cumbersome?
Workload-WIP.xlsx
0
Comment
Question by:singleton2787
  • 5
  • 4
9 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35032207
Hello,

the chart in the attached file links to an external source. No way of doing anything with the chart unless you post the source data.

cheers, teylyn
0
 

Author Comment

by:singleton2787
ID: 35033380
I was going to point the source to the data in the same workbook I posted, apologies
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35033509
Hello,

I see no way the chart and the data on the Data Entry sheet have anything in common. Where is the "Actual" data in the table? There are no dates in the source data.  The way your data is laid out in the Data Entry sheet does not lend itself to charting easily.

Can you explain in words what you want to chart?
0
 

Author Comment

by:singleton2787
ID: 35037570
Here is a newer version of the spreadsheet. The data and the graph are linked now.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:singleton2787
ID: 35037574
file...oops
Workload-WIP.xlsx
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 35041490
Hello,

thanks for the updated file. What do you mean by "redraw graph based on date range"? You don't have any dates in the source data, just text.

Any chart-related task would be much easier if your data were arranged in one contiguous table instead of interspersing it with blank rows. Your data has the form of a report. Create a data entry table with three fields

Date | Task | Value

You can then easily use Pivot tables to create summary reports like the one on your Data Entry sheet, and with just a few clicks can create dynamic charts.

And a little piece of advice: Don't use pie charts for series with more than two data points. 3D pie charts are even less readable, and the explosion in the 3D graph hides the data more than it exposes it. For example, the wedge at 9 o'clock looks a lot smaller than the wedge at 4-5 o'clock, although the data value is the same. The eye needs the number label to realise that the numbers are actually the same value. Also, one needs to constantly flick back and forth between the pie and the legend to understand which colour is which task. A much better way of presenting this data is a horizontal bar chart, where it is immediately apparent which bars are longer than others and text labels sit right with the bars.

In the attached file, please find some of your sample data re-arranged in a simple table. I created a pivot table report that looks like your Data Entry sheet. On the next tab, I created a Pivot chart. Use the drop-downs on the chart to select the data you want to show in the chart.

Below that, please find a horizontal bar chart as an alternative to the pie chart. Again, you can select with drop downs and change the scope of the chart.

Pivot tables and pivot charts are very powerful tools and can produce reports on large data sets with just a few clicks.

Hope that helps.

cheers, teylyn
Copy-of-Workload-WIP.xlsx
0
 

Author Closing Comment

by:singleton2787
ID: 35058522
Spectacular! Thanks!
0
 

Author Comment

by:singleton2787
ID: 35058725
Sorry, one last question... How do I make a single source of data entry? The columnar (Columns A,B,C on 'Data and pivot table report' tab appear to be where the Pivot grpahs are pulled from?
I would like the columns F-N be the data source for all, they would be easier to enter data on.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35060813
Hello,

to change the source of a Pivot table, select the pivot table and on the Options Tab of the PivotTable Tools click Change Data Source. You will then get a dialog where you can select the range for the pivot table.

cheers, teylyn
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

910 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

16 Experts available now in Live!

Get 1:1 Help Now