0

all. I do not see an answer to my problem in this site, so hopefully I can find help from you all. I have a table of data, reflecting pseudo-Project Management data. The table is 7,000+ rows long but only 4 columns wide. Here is a very simplified example:

**PROJECT_NAME  MILESTONE_TYPE    MILESTONE_DATE  REPORT_DATE**
Project A     Project Start     10/16/2017      6/1/2017
Project A     Beta Finished     2/1/2017        6/1/2017
Project A     Project Complete  11/1/2017       6/1/2017
Project A     Project Start     10/16/2018      1/1/2018
Project A     Beta Finished     3/1/2017        1/1/2018
Project A     Project Complete  2/1/2018        1/1/2018
Project B     Project Start     5/1/2018        6/1/2017
Project B     Beta Finished     8/1/2018        6/1/2017
Project B     Project Complete  2/1/2019        6/1/2017
Project B     Project Start     5/1/2018        1/1/2018
Project B     Beta Finished     8/1/2018        1/1/2018
Project B     Project Complete  3/1/2019        1/1/2018

I want to display data for one project at a time (I expect to use a filter), although there are actually over 70 Projects. The 36 Report Dates are snapshots (progress reports, really) of anticipated Milestone Dates.

I want to show whether any Milestone Dates have slipped for a particular project, using a line graph that will indicate any changes to Milestone Dates over time. Each Milestone Type should be its own curve/line, and I want the line to track all report dates for that particular milestone along the x-axis. So, the Y-axis will reflect target dates for each milestone, while the X-axis will reflect dates each data point was gathered, as a "trend line."

I am very familiar with Office/VBA/etc. but very new to Pivot Tables and Pivot Charts. I found another similar question in this forum, but the solution is given for Excel 2010; I am working with Office 365, which means the settings referred to don't match (as far as I can see.)

I feel I'm this close, but I can't get it. Please help!

(Thanks!)

0

I don't think this can be done with a pivot table, because pivot charts don't support all chart types. Since you want dates on both the X and the Y axis, you will need a scatter chart.

You can use pivot tables to ringfence the data for the chart. If you have three milestone types, you need three pivot tables with Report date and milestone date in the rows and the milestone type in the filters.

Add a slicer to filter the project name.

Now you can insert a scatter chart with a line and add the dates from the three pivots as series.

enter image description here

If the number of rows (report dates) is the same for all pivots and projects, you can simply select the rows for the series. If the number of rows can vary, you need to create named formulas that dynamically adjust the populated range (for example using Offset and Count)

The mini pivot table below the slicer shows just the selected slicer value in a cell, so the chart title can be linked to that cell and changes when a different project is selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.