Axis defaults by chart type

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,376
Office Version
  1. 365
Platform
  1. Windows
I uploaded a workbook to this Dropbox folder:


It has a table used to generate a chart. Cols D & D contain dates. Cols E & F contain data for scatter plots. Cols G-I contain band width data for stacked area plots. The plan is to shows the two scatter plots on top of the three horizontal bands.

I select all 7 columns and insert a either a scatter chart and change the other three to stacked area plots or insert a stacked area chart and change the other two to scatter plots.

The labels and tick marks on the horizontal axis change depending on which chart type I select first. If I start with stacked area charts, I get days and dates. If I start with scatter charts, I get counting numbers. After I change the other two types, both charts have the days and dates.

Can someone explain what is going on here?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,376
Office Version
  1. 365
Platform
  1. Windows
PS: I also noticed that the order of the entries in the legend changes when the graph has two types of plots. When it is all scatter plots or all stacked area plots, the entries are in the same order as the columns. But when I change the types of some of the plots, the stacked area plots are first and the scatter plots last. Is this because it is showing the order of the layers? That is, if the scatter plots were listed first, would they be behind the stacked area plots and not visible?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,376
Office Version
  1. 365
Platform
  1. Windows
I just uploaded an improved workbook to the Dropbox folder. I hope this makes things clearer.


It also includes some questions about the difference between line and scatter plots, which I clearly don't understand.

I added this explanatory text to the sheet:

All of these graphs are based on the same data range (C4:I11) which is for blood glucose reading charts.
C & D contain the dates of the readings for X axis labels.
E & F contain the PM & PM readings in mg/dL for X,Y plots.
G-I contain band widths for stacked area plots showing the ranges of readings that are low, normal, and high.

The objective is to get the combo graphs (A2, B2, & C2) that show where the readings (AM & PM) fall in the three ranges (Low, Normal, & High). This is accomplished by using line or scatter plots for the readings and stacked area plots for the range bands.

Before I knew about combo plots, I would insert a chart of all one type and then change the ones that needed to be changed. This brought to my attention some oddities in the horizontal axes of the initial charts (A1, B1, & C1).

If I start by inserting either a line plot or a stacked area plot, the horizontal axis shows both the dates and the days. But if I start with a scatter plot, it starts with counting numbers. It was my understanding that scatter and line plots would behave just the opposite. Scatter plots, being X,Y plots, would use the first column as the independent variable (X) and the rest as the dependent variables (Yi), whereas line plots always treat all columns as Y values. If you select any of the first 6 graphs (A1, B1, C1, A2, B2, & C2), the highlighting in the table seems to indicate that columns C & D are the independent variable and the rest (E-I) are the dependent variables.

Curiously, if I select only the Date column (D) and not the Day column (C), then both the line and stacked area plots use the date as the independent variable (A3, A4, B3, & B4).

I would love it if someone could explain what is going on to me.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,110
Messages
5,576,162
Members
412,702
Latest member
maggielrux
Top