Auto formatting new pivot rows to data bars

Question

I have a pivot table that takes data from database and displays it as a data bars. Problem is, that the data is updated daily - every day new row is added so the pivot table is growing. Unfortunately each new row doesn't follow the formatting of previous rows I've tried to apply the formatting to currently empty cells (with format painter) hoping that they will get formatted when new data will arrive, but no such luck. I've tried as well to manually edit rule and change the boundaries by using "conditional formatting"/manage rules and either changing values in "applies to" or by "edit rule/apply to" but I've got an error saying that "Cannot apply a conditional format to a range that has cells outside of a pivotTable data region". But the whole problem is that my pivot data region keeps growing every day!

Answer 1

Big +1 for using Power Pivot - I'm a huge fan of that Add-In.

I would first clear all the existing Conditional Formatting for Data Bars.

Then I would select the Pivot Table column which you want to format. This needs to be done by hovering your cursor over the top border of the heading cell, so the mouse pointer changes to a down-arrow. After selecting the Pivot Table column, it's header cell will appear with a grey background and a dashed-line border, as shown in the picture below.

Then from the Home ribbon choose Conditional Formatting / Data Bars, and pick your style.

Then notice that a small box appears immediately below the column heading cell. Click it and navigate through the menu that appears, to choose the option for "All cells showing ...", as shown in the picture below.

Now your Data Bars will expand (and contract) to follow the Pivot Table.