Quick Excel Tip to roll down formulas in Dynamics GP excel reports

I don’t know about you folks but it used to seriously frustrate me when you got a data set out of Dynamics GP into an excel report, created lots of funky formulas in your columns, then refreshed your data and got extra rows returned and your formulas did not copy down.


With the advent of the excel based reporting and other dynamically updating excel reports in Dynamics GP you come across this more and more.



Well there is a simple tick box in excel which will solve all your problems.

Goto the DATA tab on the tool ribbon in excel.

On the connections section next to the refresh All option you should have connections and under that properties.

Now properties may be greyed out., if it is click your cursor into one of the cells on the report which contains returned data. Properties should now be enabled.

Select properties and this should open the external Data Range Properties Window.

At the bottom of this is a tick box for “Fill down formulas in columns adjacent to data”

Tick this box.



Now when you refresh your data if extra rows are returned your formulas will automatically copy down.