Excel table not formatting new rows appropriately – where does formatting come from

These numbers look like typical "sequential serial number" format of the Date function, meaning the number of the day.

The cell that displays "MONTH yyyy" is formatted as a date, probably a custom date format since this is not a standard display format. Or maybe you have used the Group By option of the Pivot Table and have chosen "Group by Month".

If you wish to change the display format of a cell, see Microsoft's Format a date the way you want.

We need to know more about your datasheets to give better advice.

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.