excel, copy formatting

ash_scotland88 · 17 Sep 2019 at 22:21

Fairly new to excel and looking for help.
Creating a basics accounts type spreadsheet.
I have a column that is "invoice date" and the next is "due date" (=G.2+30)
I can copy the formula down the column, but annoyingly this fills in the due date column as 01/01/1901 if the invoice date is empty, for easy reading I would like it so no date is displayed in the due date till the invoice date is filled in. How do I acheive this?

Out of curiosity is the only way to copy a formula down an entire (like the above) is to drag and paste technique?

I will have follow up questions too...

bazzabear · 17 Sep 2019 at 22:33

If(G2<>0,G2+30,"")

That should work. Only does the calculation if there's something in G2, otherwise makes the cell blank.

bremen1874 · 17 Sep 2019 at 22:40

or use ISBLANK

=IF(ISBLANK(G2),"",G2+30)

There must be some cheap/free online resources that'll teach you these sorts of basics. If all else fails read a book.

wesimmo · 17 Sep 2019 at 22:43

ash_scotland88 said:
Fairly new to excel and looking for help.
Creating a basics accounts type spreadsheet.
I have a column that is "invoice date" and the next is "due date" (=G.2+30)
I can copy the formula down the column, but annoyingly this fills in the due date column as 01/01/1901 if the invoice date is empty, for easy reading I would like it so no date is displayed in the due date till the invoice date is filled in. How do I acheive this?

Out of curiosity is the only way to copy a formula down an entire (like the above) is to drag and paste technique?

I will have follow up questions too...

The formula above is what you need.

In answer to your question re: copying formulas. There's several ways to speed up your model building.

E.g. If you double click the little square in the bottom right of a cell it will auto populate down to the end of an adjacent column if the cells are populated.

Learn the keyboard shortcuts for copy (CTRL + C) and paste (CTRL + V) and how to zip around and select ranges using keys. So look into how selecting using CTRL + SHIFT works and how CTRL + Arrow keys help you move to the end or start of a row or column of populated cells.

ash_scotland88 · 17 Sep 2019 at 23:29

ISBLANK has worked and i've been able to re-use it elsewhere in my spreadsheet.
Can't seem to modify it to work with:
=IF(H5<TODAY(),"Overdue","Not due")

where H5 shows the due date and is blank if G5 would be empty...

bremen1874 · 17 Sep 2019 at 23:38

You can nest the functions

=IF(ISBLANK(G5),"",IF(H5<TODAY(),"Overdue","Not due"))

Not tested, but you should get the idea.

bazzabear · 18 Sep 2019 at 09:47

Oh, for the filling formulae down (or across) I use CTRL+D (fill down) a LOT.

Basically you highlight the cell you have the formula in, and all the cells below you want to copy it to, press CTRL+D and it fills all the empty cells, adjusting the formula accordingly.

CTRL+R is fill right.