How can I change the spacing of tick marks on the x axis as one per day is too many?

m.loeffler

The daily measurements are too many showing to read the dates. How can I fix this to have the chart adjust to the number of data points?

Samuel Mueller

@m.loeffler So what you would do here is make a helper column that has a date in every 4 rows, and leave the others blank. Then make that your farther left column. When you add the chart and you click the option that says use the first column as data labels, it should be spaced out more.

If that doesn't make sense, I can send an example.

Paul Newcome

When doing something like this, I like to create a table that has every day listed (even if there were no results). The absolute cleanest way I have found is to create a Sheet Summary field to pull in the start date using a MIN function. Then I create a text/number column called "Number" and start with 0 (zero) in the top row and go to however many numbers I think I am going to need. In my date column I use something like this as a column formula:

=IF([Start Date]# + Number@row<= TODAY(), [Start Date]# + Number@row)

Now my dates will automatically adjust based on today's date. You can use an INDEX/MATCH or some other function as needed to bring in my totals being sure to leave any dates that don't have data blank (not zero) with an IFERROR or some other similar approach. From there we have our "Labels" column as previously described.

Finally we create a report that only pulls in rows where my date column is not blank. We only include the Labels column and the calc column and then use that to generate your graph.

Now you have something where the labels are evenly spaced and will also automatically grow as time goes on. Of course once you get enough weeks populating the chart, you may need to adjust the logic for which dates are pulled into your "Labels" column, but that is relatively minor maintenance.

The chart below actually covers 365 days (there are some days where there was no data recorded).

Ken Armstrong

This may or may not help but as far as I know you can adjust the widget size and get some more space between.

m.loeffler

Ken, Thanks for the idea, but it only buys a bit of time as the data will continue to crowd the axis. I know in Excel you can adjust the ticks and they automatically adjust as well. I am hopeful that someone has a trick for this....

Samuel Mueller

@m.loeffler So what you would do here is make a helper column that has a date in every 4 rows, and leave the others blank. Then make that your farther left column. When you add the chart and you click the option that says use the first column as data labels, it should be spaced out more.

If that doesn't make sense, I can send an example.

Samuel Mueller

@m.loeffler like this post: Smartsheet Chart Date Axis Formatting — Smartsheet Community

Paul Newcome

Depending on how many data points you have, I usually use a formula that will pull the 1st and 15th into the helper column mentioned by @Samuel Mueller (I usually call it "Labels").

=IF(OR(DAY([Date Column]@row) = 1, DAY([Date Column]@row) = 15)), [Date Column]@row + "")

m.loeffler

Paul,

Interesting solution, I used your concept to pull the Mondays only for the data labels. Seems a bit better, but clean. It seems the testing is not every monday so it looks a bit off. Thanks for the idea.

Paul Newcome

When doing something like this, I like to create a table that has every day listed (even if there were no results). The absolute cleanest way I have found is to create a Sheet Summary field to pull in the start date using a MIN function. Then I create a text/number column called "Number" and start with 0 (zero) in the top row and go to however many numbers I think I am going to need. In my date column I use something like this as a column formula:

=IF([Start Date]# + Number@row<= TODAY(), [Start Date]# + Number@row)

Now my dates will automatically adjust based on today's date. You can use an INDEX/MATCH or some other function as needed to bring in my totals being sure to leave any dates that don't have data blank (not zero) with an IFERROR or some other similar approach. From there we have our "Labels" column as previously described.

Finally we create a report that only pulls in rows where my date column is not blank. We only include the Labels column and the calc column and then use that to generate your graph.

Now you have something where the labels are evenly spaced and will also automatically grow as time goes on. Of course once you get enough weeks populating the chart, you may need to adjust the logic for which dates are pulled into your "Labels" column, but that is relatively minor maintenance.

The chart below actually covers 365 days (there are some days where there was no data recorded).