Define Gridlines Based on Bin Values from Sort

Copper Contributor

 

I have a spreadsheet sorted into bins by a custom list - day of the week. The number of elements per day is not uniform and the sort type is not critical to this suggestion.

 

I created a chart from this data and would like to have a gridline to separate the days to make it easy to see the Sunday vs Monday vs... information. In the short term, I added a blank row between the days so that a blank space replaces the gridline; not difficult for days of the week, but prohibitive if there are many different bins.

 

Has anyone done something like this? Does anyone see value it in?

6 Replies

@Alan_Berow 

Let's say the days of the week are in D2:D100.

Select this range. D2 should be the active cell in the selection.

You can also select multiple columns, e.g. A2:K100.

The active cell should still be in row 2.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'not equal to' from the second drop down.

In the box next to it, enter the formula =$D1

(D1 is the cell in the day column above the row with the active cell)

Click Format...

Activate the Border tab of the Format Cells dialog.

Click on the top border of the example box.

S2333.png

You can select a color for the border if you wish, before clicking the top border in the example box.

Click OK, then click OK again.

@Hans Vogelaar 

Hi Hans. Thank you for the quick response and the advice. If I followed your directions correctly, I now have a spreadsheet with the cells in the data tab's Day of Week column having a border line above each day value. It did not translate into the chart where I hoped to add gridlines between the days of the week.

 

I anonymized the data and attached the spreadsheet in case you or someone else has any suggestions.

@Alan_Berow 

I don't see an easy way to add such lines to the chart, sorry.

@Hans Vogelaar 

 

Hi Hans. Thank you for trying. I suspect this is an enhancement request.

added a blank row between the days so that a blank space replaces the gridline;

what is the expected result?

the chart from Date has grid.
the other from Day of Week no grid.

which one is your expected result?
how about online charts like echarts?