Plotting anomalies on Excel charts|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi Tom,

I've got some ideas for this. Can you please upload a sample Excel file so I can give you an example in response.

Mynda

Hi Tom,

Thanks for sharing the data.

In the attached file I've used Power Query to merge the two tables and added a value for the surgery date that is the maximum value of all 'Results'. This enables me to plot a marker for the Surgery day that sits at the top of the columns. This marker also has a label assigned for the type of surgery. Note: if you have Excel 2010 or earlier then you won't see this label. I've included a screenshot just in case.

I've used a date axis so that the dates missing are still included, this allows the data points to be in line with actual time.

I don't recommend a line chart for this data because there are irregular gaps in the dates for which you have data. I think the column chart is better.

To update the chart, simply update your two tables (RBC Count and Surgeries) as you normally would, and then click the 'Refresh All' button on the data tab.

Let me know if you have any questions.

Merry Christmas!

Mynda

Hi Tom,

If you want to manually merge the data that's ok. However, I have some points of improvement for your chart that will make it quicker and clearer for the reader to interpret.

1. The RBC counts weren't taken at equal intervals in time. Your chart has no context of time because it equally plots the dates that the RBC count were taken. You lose sense of time between each count and can't easily see when there were big/small gaps between counts. At best it makes it difficult for the chart reader to interpret those gaps, and at worst the chart reader won't notice and will make the incorrect assumption that the gaps are equal.

Recommendation: Use a proper date axis, as I did in my example, so the missing dates are included and the reader instantly knows the RBC count intervals are not equal. 

2. Putting the surgeries in the same series and using a fictitious RBC count value of 4.6 implies to the chart reader that the RBC count was taken on that date and the count was 4.6. We humans subconsciously glance at the height of the columns and decode a pattern and the highs and lows. By including these fictitious values as columns in their own right is misleading. That is why I put the fictitious value in it's own series and only used the marker as a place holder to indicate the date of the surgery.

Recommendation: Put the fictitious surgery values in their own series and only include the marker so it cannot be misinterpreted as a RBC count. In fact, you could have a separate series for each type of surgery (Lesion, Brain and Radiation) and colour code those series so the dots were automatically coded.

3. If you're going to have labels you don't need the vertical axis. This is just noise.

Recommendation: remove the vertical axis

4. Colour coding high/low points. I don't think this is necessary. The columns instantly show us the highs and lows, we don't need them colour coded.

Recommendation: remove high/low colour coding. You could have it as a headline, as you've done with the 'Range' information. I've inserted one that automatically calculates and updates based on the data.

5. Gradient fill in chart background. This just adds noise and doesn't aid interpretation.

Recommendation: remove the gradient fill from the background. Better to leave out unnecessary formatting. It's more professional, too.

6. Trendline. I'm not sure this is necessary. You can see the overall trend is upward using the height of the columns. There are also some peaks that influence this trend and that may distract from the more recent data.

Recommendation: remove the trendline. Let the reader make their own interpretation based on the column height.

I've set up the chart so it automatically updates. You don't have to manually apply labels or colour code. I hope that helps.

Mynda