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!

  • When you say "takes data from database", do you mean you went to the Data ribbon and clicked From Other Sources / From SQL Server (or similar)? – Mike Honey May 30 '14 at 6:50
  • 1
    Data is taken freom SQL server database by powerpivot, which in turn generates pivot chart. This chart is set up to update data on start – Yasskier May 31 '14 at 8:18

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.

enter image description here

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.

enter image description here

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.