Insert Line maintaining formatting and formulas from preceding line

Hi,

I use an Excel 2003 spreadsheet for my company accounts and in order to
maintain data order of entries I need to insert lines between existing
lines in the spreadsheet, maintaining the formulas and formats of the
preceding lines but leaving the data fields of the new line blank in
readiness for the new data.

Can anyone tell me how to do this ?

Thanks,

David

insert row it will automatically take the format of the row above it

> insert row it will automatically take the format of the row above it

Nope.

David

<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I use an Excel 2003 spreadsheet for my company accounts and in order to
> maintain data order of entries I need to insert lines between existing
> lines in the spreadsheet, maintaining the formulas and formats of the
> preceding lines but leaving the data fields of the new line blank in
> readiness for the new data.
>
> Can anyone tell me how to do this ?
>
> Thanks,
>

David

If inserting a line doesn't carry formats from the previous line, then
insert lines as needed, select the line to be copied, copy, paste special to
the inserted rows. Select "fomulas and number formats". Does this work for
you, or too long a process? (I'm using Excel 2002)

beege

> David
>

>> I use an Excel 2003 spreadsheet for my company accounts and
>> in order to maintain data order of entries I need to insert lines
>> between existing lines in the spreadsheet, maintaining the
>> formulas and formats of the preceding lines but leaving the data
>> fields of the new line blank in readiness for the new data.

> If inserting a line doesn't carry formats from the previous line,
> then insert lines as needed, select the line to be copied, copy,
> paste special to the inserted rows. Select "fomulas and
> number formats". Does this work for you, or too long a
> process? (I'm using Excel 2002)

Well, that;s what I've been doing, but it's an almighty nuisance and
then you have to clear the pasted data from the new cells afterwards...

I'm sorry to moan, but this seems like such obviously necessary
functionality that I really cannot understand why it's not more simply
available in the interface?

Thanks,

David

David

Tools>Options>Edit "extend formats and formulas" will look after the
formatting.

David McRitchie's INSERTROW macro will look after both formulas and
formatting.

http://www.mvps.org/dmcritchie/excel/insrtrow.htm


Gord Dibben Excel MVP

On 28 Nov 2005 08:32:20 -0800, [email protected] wrote:

>>> I use an Excel 2003 spreadsheet for my company accounts and
>>> in order to maintain data order of entries I need to insert lines
>>> between existing lines in the spreadsheet, maintaining the
>>> formulas and formats of the preceding lines but leaving the data
>>> fields of the new line blank in readiness for the new data.
>
>> If inserting a line doesn't carry formats from the previous line,
>> then insert lines as needed, select the line to be copied, copy,
>> paste special to the inserted rows. Select "fomulas and
>> number formats". Does this work for you, or too long a
>> process? (I'm using Excel 2002)
>
>Well, that;s what I've been doing, but it's an almighty nuisance and
>then you have to clear the pasted data from the new cells afterwards...
>
>I'm sorry to moan, but this seems like such obviously necessary
>functionality that I really cannot understand why it's not more simply
>available in the interface?
>
>Thanks,
>
>David

> ...I need to insert lines
> between existing lines in the spreadsheet, maintaining the
> formulas and formats of the preceding lines

Don't know if these keyboard shortcuts will work for you, but these I use
often enough...
Shift + Spacebar ...select entire row.
Ctrl+Shift "+" to Insert Row
Ctrl + "D" to copy the row above, down to present row

Hope these help.
--
Dana DeLouis
Win XP & Office 2003


<[email protected]> wrote in message
news:[email protected]...
>>> I use an Excel 2003 spreadsheet for my company accounts and
>>> in order to maintain data order of entries I need to insert lines
>>> between existing lines in the spreadsheet, maintaining the
>>> formulas and formats of the preceding lines but leaving the data
>>> fields of the new line blank in readiness for the new data.
>
>> If inserting a line doesn't carry formats from the previous line,
>> then insert lines as needed, select the line to be copied, copy,
>> paste special to the inserted rows. Select "fomulas and
>> number formats". Does this work for you, or too long a
>> process? (I'm using Excel 2002)
>
> Well, that;s what I've been doing, but it's an almighty nuisance and
> then you have to clear the pasted data from the new cells afterwards...
>
> I'm sorry to moan, but this seems like such obviously necessary
> functionality that I really cannot understand why it's not more simply
> available in the interface?
>
> Thanks,
>
> David
>

Hi David,
See
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

The use of Extend Formulas does not work well unless all of the cells
are filled in consistently above -- in other words a debit column and
a credit column spells failure.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Thanks to all for the answers, the macro worked for me.

If anyone from the Microsoft Excel team is listening then I hope you
don't mind me giving my opinion that I really think this issue needs to
be addressed in the "Insert Line" menu somehow.

Thanks again,

David