Monday, 11 April 2016

Quick Tip: Excel Formula Filldown Shortcut

The extensive range of formula functions available in Microsoft Excel, makes it a popular choice for data analysis and reporting among Planners & Schedulers. But when working with large amount of data in Excel, you might find it irritating that you have to drag or copy/paste formulas across hundreds or thousands of rows, so here is a shortcut.

Assuming we have the P6 export shown below and we want to calculate duration of each activity 
based on a 24-hour calendar, we would subtract the Start date from the Finish date, i.e. in cell H2, we would enter the formula =G2-F2.
Instead of dragging  (or copying & pasting) the formula from cell G2  down to the last row, select the cell with the formula (in this example, cell G2) and you should notice that there is a thick black box round cell G2. Look at the bottom right hand corner of this thick black box and you should see a black cross (+) indicated by the arrow in the image below.
Now double-click on this cross, and formula in cell G2 will automatically be filled down to the last row
That is all for today and I hope you have find this tip useful.

No comments:

Post a Comment