Are you tired of the limitations in Google Sheets when it comes to displaying multiple categories on the x-axis of your charts? Well, you’re not alone! In this article, I’ll share a simple workaround that will allow you to show month and year in the x-axis of your Google Sheets charts. Say goodbye to cluttered charts and hello to a more organized and visually appealing data visualization!
How a Normal Column Chart Displays Monthly Data
Let’s start by understanding how a normal column chart displays monthly data. If you have monthly consumption data, such as the gasoline consumption of a fleet of trucks over the past two years, you can plot a column chart in Google Sheets. However, the x-axis ends up being crowded with values, making it difficult to read and interpret the chart.
As you can see, the month and year names clutter the x-axis. But don’t worry, I have a solution for you!
The Workaround to Display Month and Year in X-axis in Sheets
I’m here to share a workaround that will make your charts much more organized and easier to understand. Instead of displaying each individual month on the x-axis, we’ll use a combination of the year as the main category and the month names as sub-categories.
This chart looks much cleaner, right? Now, let me explain how you can achieve this.
The key to this workaround lies in re-structuring your data. You can do this either manually or by using formulas. Here’s how your data should look in order to plot the chart in a clutter-free manner:
To split the month and year into separate columns, you can use the following formulas:
- Cell D2:
=to_text(year(A2)) - Cell D3:
=if(year(A3)<>year(A2),to_text(year(A3)),) - Cell E2:
=ArrayFormula(text(A2:A25,"mmm")) - Cell F2:
={B2:B25}
Now that your data is properly formatted, you can plot the chart by following these settings in the chart editor:
- Chart type: Column
- Stacking: None
- Data range: D1:F25
- X-axis: D1:D25 (by default, you will see the field label)
- Labels: E1:E25
- Series: F1:F25 (by default, you will see the field label)
And voila! You now have a beautiful chart that shows month and year in the x-axis without cluttering it with unnecessary labels.
So, next time you want to create a chart in Google Sheets and display month and year in a more organized manner, remember this simple workaround.
I hope you found this article helpful. If you want to learn more about Google Sheets and other useful tips, visit Crawlan.com for more exciting tutorials. Happy charting!
