Excel 2013 Automatically insert a row when the last formatted row is used

You will need to do some slight modifications, but it starts with converting your template to Tables. This will add rows and keep the equations within the rows. It will also 'push' down your chart and summary calculations. Note, that you may have to move your charts down just a bit, so that there is it least one empty row between your data and the chart for it to push down properly. For some reason, if it is less than a blank row (or if the top of the chart overlaps the table) then the chart does not move.
Using the Sep sheet as an example.
select A2:I26 and then hit Ctrl-T. Make sure the 'My table has headers' comment is checked. Hit Ok, and you should now have a table. You'll see the rows become colored in a striped pattern (but you can change this in the Table Tools -> Design ribbon). If you are on the last cell and you hit Tab, then a new row is added.

In your summary calculations, you have all of your ranges hard coded to absolute references (e.g. =SUMIF($B$3:$B$26,B29,$G$3:$G$26)). Since you are using absolute, those will not change when the table is modified. You will likely need to update those to reference the table (use your mouse to select the ranges to make it easier) or remove the absolute. So, the example above becomes something like:
=SUMIF(Table1[Department],B29,Table1[Hours])
or
=SUMIF(B3:B26,B29,G3:G26)

More info here:
http://office.microsoft.com/en-us/ex...010155686.aspx