Adding dynamic bands to your Google Sheets is very useful to highlight specific parts of the chart. Using simple formulas, we can easily create the dynamic bands for the charts on Google Sheets. On this page, let us discuss how to create the dynamic chart banding in the spreadsheet using Google Sheets Tips and Tricks. Read further to find out more.

Table of Contents

Creating Dynamic Chart Bands in Google Sheets

To create a dynamic band for the charts in Google Sheets, we will have to create the dataset. In this post, I have used the Website Traffic analysis dataset and have modified the same as follows.

chart dynamic bands in google sheets10

As shown in the image above, I have dates and pageviews in Columns A and B.

Now in Column C, we will have to determine whether the specified date in Column A falls on which weekday. To analyse the same, we are using the formula “= choose (weekday (A2, 2), “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”, “Sunday”)“. Once you enter this formula, you will have the results as follows:

chart dynamic bands in google sheets10

The next step is to know if the days in column C fall on a weekend or a weekday. To find out, we use the following formula:

=if(or(C2="Saturday",C2="Sunday"),"Weekend","Weekday")

This formula categorizes the day as a weekday or weekend as per the calendar. So your results will be as follows:

chart dynamic bands in google sheets10

Finally, we need to create a data validation drop-down list for weekdays and weekends. In this post, I have chosen Column K to create the data validation as shown in the image below.

chart dynamic bands in google sheets10

Now in Column E, we are creating the banding. So our formula in Column E is “if (D2=$K $1,900,””)“. Press the “Enter” key and you will see the results as shown below.

chart dynamic bands in google sheets10

So our dataset will finally look like the above image.

How to Make a Dynamic Chart Banding in Google Sheets?

The steps for creating dynamic chart bands in a Google Spreadsheet are as follows:

  • 1st Step: Select Columns A, B, and E in the above dataset.
  • 2nd Step: Click on the “Insert” tab and choose “Chart” from the drop-down menu.
  • 3rd Step: By default, Google Sheets will create a chart.

chart dynamic bands in google sheets10

  • 4th Step: Now select “Combo Chart” under the Chart Type.

chart dynamic bands in google sheets10

  • 4th Step: Now we have to customise the combo chart. To do the same, click on the “Customise” section.
  • 5th Step: Under “Series,” select “Page Views.”
  • 6th Step: Select “Type” as lines.

chart dynamic bands in google sheets10

  • 7th Step: Now again, under “Series“, choose “Banding” and select the “Type” as “Rows“.

chart dynamic bands in google sheets10

  • 8th Step: That’s it. The Dynamic Band Chart has been created. Now in column K, choose the “Weekday” from the drop-down list and you will see the following results.

chart dynamic bands in google sheets10

Changing the drop-down list to “Weekend” will help you see the following results.

chart dynamic bands in google sheets10

Leave a Reply

Your email address will not be published.