I generally consider myself an advanced Excel user (I can do pivot charts), but I have a spreadsheet that’s acting really weird and I can’t figure it out. Hoping someone here can help me. I have Excel 2011 for Macintosh.
I have a simple spreadsheet where I’m charting my weight on a weekly basis. So column A is the date on every Saturday. It starts with 9/20/2014 and ran for about six months. I have a line chart based on this and it works great… until now.
I didn’t want my chart to get wider than a printable page, so I moved the last few months up to the second date position and erased everything after that so that will continue from today. In other words, it used to be:
9/20/14, 9/27/14, 10/4/14, 10/11/14, etc. to 6/6/15 and charted accordingly
Today I changed it to this:
9/20/14, 4/25/15, 5/2/15, 5/16/15, etc. to 1/2/16
and now the chart still shows the X axis as 9/20/14, 9/27/14, 10/4/14, 1011/14, etc all the way to 1/2/16 and of course it’s all squashed in really tightly to fit that. I don’t want that. I wanted the chart to actually plot 9/20/14, and have a dip in the line to 4/25/15 but no wasted space between them. Ideas? Do I need to attach a picture to illustrate?
If I understand you correctly the problem is this:
If you try to plot a chart in excel with dates - let’s say for a while year 1/1 - 12-31 and you only have the data for all of January and all of December - excel will take these 62 data points and plot them out as 365 data points with nothing in March - November and make the graph look stupid as it looks like you weigh zero pounds in those missing data points (plus only 1/6th of the chart is used)
Do I understand the problem correctly.
I’m not familiar with the Mac version, but In my version of excel you can:
right click on x axis with date
select option for “format axis”
change “axis type” from “Automatically select based on data” to “Text Axis”
The T in “Text Axis” is underlined meaning it is a shortcut, but I don’t know how to use shortcuts on Mac
Also - in cases like this - if you want to make the data more understandable by others - I will sometimes use two columns for the data - so in the example I used - I would put the weight for January in one column and the data for December in another
This works well if you have a few well designed breaks - as excel will plot those as different colors - making the graph more understandable by others - but this is of course optional.
So looking at that “after” picture, you can see that the dates in column A do NOT match the dates in the X axis of the chart. That’s the weird thing I don’t like. It’s like it’s second guessing me.
Start by checking the actual data series range used for the X-axis, as well as both data series. You may find one or more is not the set of rows you think it is. It’s pretty clear from those pix that you now have a lot more chart columns = data series rows selected for display.
After you get that squared away, if the problem persists then …
The next step is to check the formatting of the x-axis. major and minor labels, tickmarks, etc. Something may have slipped into automatic mode and is no longer doing what you expected.
There are lots of ways to break charts, and moving data or removing the rows that it thinks are the anchors are the usual suspects.
I think the problem you’re running into in the time span between your first row and the rest is rather large and the charting tool, as far as I’m aware, will default to regular, equal intervals for the axis values. So, if your dates are really counted in days (which I think Excel does), then your span between the first and second row is 217 days and of course the rest is 7 days. The smallest value between two rows is seven days, so that’s the x-axis interval Excel is chosen.
As to how to fix it, I’m not sure. You could treat the dates as text and it would cause it to plot them equidistant from each other, maybe. I don’t know if it would try to sort it first though. It’s worth a shot.
Actaully, it’s being smart. It sees that the data you do have is in week intervals and it’s preserving that so that your chart is not skewed at the beginning.
Thanks, Nate had the solution. I did have to re-type the dates after changing the cell format to Text because it redisplayed them as the numeric Word dates, but I can deal with that. It’s more important for the chart to look the way I want it than to have the drag-to-continue-the-sequence functionality.
Also, yeah, it’s doing it “right”, but sometimes we humans want to do things not-right for a reason. In this case, I actually preferred a skew at the beginning rather than trying to chart years worth of data on an ongoing basis. Past history isn’t as important to my “use case” as much as just showing where I started and then recent history. Or I should say I want to see recent history “against the backdrop” of where I started.
Sorry if I wasn’t clear in the post above, but you can actually leave the dates in their default format in the spreadsheet and just have your chart treat them as text by double-clicking on your x-axis and changing the Horizontal Axis Type to “Text”.
This is just a little bit offtopic, but have you considered a smart scale? They’ll automatically record your weight and body fat and take care of all the charting over time for you. They can also integrate with other fitness devices (fitbit) and apps to track your overall nutrition vs exercise, and sleep too if you want. Some scales:
A friend of mine who sort of works in the industry calls it the emerging “quantifiable self” movement. Even if you don’t have the fancy suite of personal tracking going on, just having the scale do it all automatically is nice… at least until the guilt and laziness combine to just make you never go on again.
In this case, I actually preferred a skew at the beginning rather than trying to chart years worth of data on an ongoing basis. Past history isn’t as important to my “use case” as much as just showing where I started and then recent history. Or I should say I want to see recent history “against the backdrop” of where I started.