Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Graph based on Date Range

Posted on 2011-03-03
9
Medium Priority
?
606 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
[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
  • 5
  • 4
9 Comments
 
LVL 50
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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 (Microsoft MVP / EE MVE) earned 2000 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
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

610 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