Solved

Graph based on Date Range

Posted on 2011-03-03
9
600 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

Author Comment

by:singleton2787
ID: 35037570
Here is a newer version of the spreadsheet. The data and the graph are linked now.
0
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

829 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