Thread: Why can't I insert a row in my worksheet?

I am trying to insert a row of simple data into my worksheet. When I do, I
get the following message:

"This operation is not allowed. The operation is attempting to shift cells
in a list on your worksheet."

I don't understand this. I have created lists from the worksheet but don't
have any now. Why does it think I have a list on my worksheet? And, if I have
one, how do I remove it?

I couldn't find anything on this in the knowledge base and am very
frustrated. Thanks for any help.

Hi Susan
It sounds like the location your are trying to insert a row has merged cells
in it.
You will need to either unmerge the cells or go 1 row above or below the
merged area.

HTH
Michael

"susanholland3" wrote:

> I am trying to insert a row of simple data into my worksheet. When I do, I
> get the following message:
>
> "This operation is not allowed. The operation is attempting to shift cells
> in a list on your worksheet."
>
> I don't understand this. I have created lists from the worksheet but don't
> have any now. Why does it think I have a list on my worksheet? And, if I have
> one, how do I remove it?
>
> I couldn't find anything on this in the knowledge base and am very
> frustrated. Thanks for any help.

I don't have any merged cells, at least not that I know of or that I
intended. I have created lists from this worksheet and wonder if it thinks I
still have a list open, but don't know how to correct for that.

I tried locating the row above and below where I need it----it didn't work.
Thanks for trying.

"Michael M" wrote:

> Hi Susan
> It sounds like the location your are trying to insert a row has merged cells
> in it.
> You will need to either unmerge the cells or go 1 row above or below the
> merged area.
>
> HTH
> Michael
>
> "susanholland3" wrote:
>
> > I am trying to insert a row of simple data into my worksheet. When I do, I
> > get the following message:
> >
> > "This operation is not allowed. The operation is attempting to shift cells
> > in a list on your worksheet."
> >
> > I don't understand this. I have created lists from the worksheet but don't
> > have any now. Why does it think I have a list on my worksheet? And, if I have
> > one, how do I remove it?
> >
> > I couldn't find anything on this in the knowledge base and am very
> > frustrated. Thanks for any help.

OK
Are the some of the toolbars greyed out ??
If so, I would suggest that you're sheet is protected and needs to be
unprotected.
"The shift cells error " definately appears when there are merged cells

HTH
Michael M

"susanholland3" wrote:

> I don't have any merged cells, at least not that I know of or that I
> intended. I have created lists from this worksheet and wonder if it thinks I
> still have a list open, but don't know how to correct for that.
>
> I tried locating the row above and below where I need it----it didn't work.
> Thanks for trying.
>
> "Michael M" wrote:
>
> > Hi Susan
> > It sounds like the location your are trying to insert a row has merged cells
> > in it.
> > You will need to either unmerge the cells or go 1 row above or below the
> > merged area.
> >
> > HTH
> > Michael
> >
> > "susanholland3" wrote:
> >
> > > I am trying to insert a row of simple data into my worksheet. When I do, I
> > > get the following message:
> > >
> > > "This operation is not allowed. The operation is attempting to shift cells
> > > in a list on your worksheet."
> > >
> > > I don't understand this. I have created lists from the worksheet but don't
> > > have any now. Why does it think I have a list on my worksheet? And, if I have
> > > one, how do I remove it?
> > >
> > > I couldn't find anything on this in the knowledge base and am very
> > > frustrated. Thanks for any help.

Susan
This can also apply to cells in columns as well

MM

"susanholland3" wrote:

> I don't have any merged cells, at least not that I know of or that I
> intended. I have created lists from this worksheet and wonder if it thinks I
> still have a list open, but don't know how to correct for that.
>
> I tried locating the row above and below where I need it----it didn't work.
> Thanks for trying.
>
> "Michael M" wrote:
>
> > Hi Susan
> > It sounds like the location your are trying to insert a row has merged cells
> > in it.
> > You will need to either unmerge the cells or go 1 row above or below the
> > merged area.
> >
> > HTH
> > Michael
> >
> > "susanholland3" wrote:
> >
> > > I am trying to insert a row of simple data into my worksheet. When I do, I
> > > get the following message:
> > >
> > > "This operation is not allowed. The operation is attempting to shift cells
> > > in a list on your worksheet."
> > >
> > > I don't understand this. I have created lists from the worksheet but don't
> > > have any now. Why does it think I have a list on my worksheet? And, if I have
> > > one, how do I remove it?
> > >
> > > I couldn't find anything on this in the knowledge base and am very
> > > frustrated. Thanks for any help.

Well, I hate to be difficult but it's still not working. None of the toolbars
are grayed out. The sheet is not protected (I double-checked) and the cells
are not merged (I double-checked on that, too).

But here's some more information I'm gleaning as I work with this: I have
created lists from the columns of this worksheet. On the column headings
where I have previously created lists, I click on the heading and get the
drop down list--as though the list is still there and part of the worksheet.
I think that is why I can't add a row----a row cuts across those columns that
think they still have lists.

Does that make any sense? I think I need to somehow get rid of the lists or
convince the worksheet that I don't care about that.

Thanks for the efforts so far.....

Susan
"Michael M" wrote:

> OK
> Are the some of the toolbars greyed out ??
> If so, I would suggest that you're sheet is protected and needs to be
> unprotected.
> "The shift cells error " definately appears when there are merged cells
>
> HTH
> Michael M
>
> "susanholland3" wrote:
>
> > I don't have any merged cells, at least not that I know of or that I
> > intended. I have created lists from this worksheet and wonder if it thinks I
> > still have a list open, but don't know how to correct for that.
> >
> > I tried locating the row above and below where I need it----it didn't work.
> > Thanks for trying.
> >
> > "Michael M" wrote:
> >
> > > Hi Susan
> > > It sounds like the location your are trying to insert a row has merged cells
> > > in it.
> > > You will need to either unmerge the cells or go 1 row above or below the
> > > merged area.
> > >
> > > HTH
> > > Michael
> > >
> > > "susanholland3" wrote:
> > >
> > > > I am trying to insert a row of simple data into my worksheet. When I do, I
> > > > get the following message:
> > > >
> > > > "This operation is not allowed. The operation is attempting to shift cells
> > > > in a list on your worksheet."
> > > >
> > > > I don't understand this. I have created lists from the worksheet but don't
> > > > have any now. Why does it think I have a list on my worksheet? And, if I have
> > > > one, how do I remove it?
> > > >
> > > > I couldn't find anything on this in the knowledge base and am very
> > > > frustrated. Thanks for any help.

Well, after all that.....I figured it out! I clicked on the columns that were
lists and used the list drop down menu----clicked on "change to range" (I
still don't know what that means). At any rate, the worksheet no longer has
lists attached and I was able to add the row.

Thanks for the help.

"susanholland3" wrote:

> I am trying to insert a row of simple data into my worksheet. When I do, I
> get the following message:
>
> "This operation is not allowed. The operation is attempting to shift cells
> in a list on your worksheet."
>
> I don't understand this. I have created lists from the worksheet but don't
> have any now. Why does it think I have a list on my worksheet? And, if I have
> one, how do I remove it?
>
> I couldn't find anything on this in the knowledge base and am very
> frustrated. Thanks for any help.

Are you able to delete a row ?

Then it must be you lsits.

I've recreated your problem by giving a cell data validation with a list=

And using a whole column for that list

Rows can be inserted, deleted - until you put a value in the last row !

Check the list definitions - guess some of them refer to columns

Now in normal case the empty cells of the column will be *empty*
but maybe you filled the column with blanks or or or

Steve


On Thu, 03 Aug 2006 13:43:02 +0100, susanholland3 =

<susanholland3@discussions.microsoft.com> wrote:

> Well, I hate to be difficult but it's still not working. None of the =

> toolbars
> are grayed out. The sheet is not protected (I double-checked) and the =
=

> cells
> are not merged (I double-checked on that, too).
>
> But here's some more information I'm gleaning as I work with this: I h=
ave
> created lists from the columns of this worksheet. On the column headin=
gs
> where I have previously created lists, I click on the heading and get =
the
> drop down list--as though the list is still there and part of the =

> worksheet.
> I think that is why I can't add a row----a row cuts across those colum=
ns =

> that
> think they still have lists.
>
> Does that make any sense? I think I need to somehow get rid of the lis=
ts =

> or
> convince the worksheet that I don't care about that.
>
> Thanks for the efforts so far.....
>
> Susan
> "Michael M" wrote:
>
>> OK
>> Are the some of the toolbars greyed out ??
>> If so, I would suggest that you're sheet is protected and needs to be=

>> unprotected.
>> "The shift cells error " definately appears when there are merged cel=
ls
>>
>> HTH
>> Michael M
>>
>> "susanholland3" wrote:
>>
>> > I don't have any merged cells, at least not that I know of or that =
I
>> > intended. I have created lists from this worksheet and wonder if it=
=

>> thinks I
>> > still have a list open, but don't know how to correct for that.
>> >
>> > I tried locating the row above and below where I need it----it didn=
't =

>> work.
>> > Thanks for trying.
>> >
>> > "Michael M" wrote:
>> >
>> > > Hi Susan
>> > > It sounds like the location your are trying to insert a row has =

>> merged cells
>> > > in it.
>> > > You will need to either unmerge the cells or go 1 row above or =

>> below the
>> > > merged area.
>> > >
>> > > HTH
>> > > Michael
>> > >
>> > > "susanholland3" wrote:
>> > >
>> > > > I am trying to insert a row of simple data into my worksheet. =

>> When I do, I
>> > > > get the following message:
>> > > >
>> > > > "This operation is not allowed. The operation is attempting to =
=

>> shift cells
>> > > > in a list on your worksheet."
>> > > >
>> > > > I don't understand this. I have created lists from the workshee=
t =

>> but don't
>> > > > have any now. Why does it think I have a list on my worksheet? =
=

>> And, if I have
>> > > > one, how do I remove it?
>> > > >
>> > > > I couldn't find anything on this in the knowledge base and am v=
ery
>> > > > frustrated. Thanks for any help.



-- =

Steve (3)

Good,
I posted a reply before I read yours
But I think you must have a later version of Excel than me (2000)
as I don't get any of your List menus, etc etc.

Anyway glad you got it sorted.
Steve

On Thu, 03 Aug 2006 14:00:02 +0100, susanholland3 =

<susanholland3@discussions.microsoft.com> wrote:

> Well, after all that.....I figured it out! I clicked on the columns th=
at =

> were
> lists and used the list drop down menu----clicked on "change to range"=
(I
> still don't know what that means). At any rate, the worksheet no longe=
r =

> has
> lists attached and I was able to add the row.
>
> Thanks for the help.
>
> "susanholland3" wrote:
>
>> I am trying to insert a row of simple data into my worksheet. When I =
=

>> do, I
>> get the following message:
>>
>> "This operation is not allowed. The operation is attempting to shift =
=

>> cells
>> in a list on your worksheet."
>>
>> I don't understand this. I have created lists from the worksheet but =
=

>> don't
>> have any now. Why does it think I have a list on my worksheet? And, i=
f =

>> I have
>> one, how do I remove it?
>>
>> I couldn't find anything on this in the knowledge base and am very
>> frustrated. Thanks for any help.

You may have found one of the quirks about an array formula somewhere in the
range that you are trying to add to.


"susanholland3" <susanholland3@discussions.microsoft.com> wrote in message
news:0B651741-585F-4B64-B734-D7E924025606@microsoft.com...
> Well, I hate to be difficult but it's still not working. None of the
> toolbars
> are grayed out. The sheet is not protected (I double-checked) and the
> cells
> are not merged (I double-checked on that, too).
>
> But here's some more information I'm gleaning as I work with this: I have
> created lists from the columns of this worksheet. On the column headings
> where I have previously created lists, I click on the heading and get the
> drop down list--as though the list is still there and part of the
> worksheet.
> I think that is why I can't add a row----a row cuts across those columns
> that
> think they still have lists.
>
> Does that make any sense? I think I need to somehow get rid of the lists
> or
> convince the worksheet that I don't care about that.
>
> Thanks for the efforts so far.....
>
> Susan
> "Michael M" wrote:
>
>> OK
>> Are the some of the toolbars greyed out ??
>> If so, I would suggest that you're sheet is protected and needs to be
>> unprotected.
>> "The shift cells error " definately appears when there are merged cells
>>
>> HTH
>> Michael M
>>
>> "susanholland3" wrote:
>>
>> > I don't have any merged cells, at least not that I know of or that I
>> > intended. I have created lists from this worksheet and wonder if it
>> > thinks I
>> > still have a list open, but don't know how to correct for that.
>> >
>> > I tried locating the row above and below where I need it----it didn't
>> > work.
>> > Thanks for trying.
>> >
>> > "Michael M" wrote:
>> >
>> > > Hi Susan
>> > > It sounds like the location your are trying to insert a row has
>> > > merged cells
>> > > in it.
>> > > You will need to either unmerge the cells or go 1 row above or below
>> > > the
>> > > merged area.
>> > >
>> > > HTH
>> > > Michael
>> > >
>> > > "susanholland3" wrote:
>> > >
>> > > > I am trying to insert a row of simple data into my worksheet. When
>> > > > I do, I
>> > > > get the following message:
>> > > >
>> > > > "This operation is not allowed. The operation is attempting to
>> > > > shift cells
>> > > > in a list on your worksheet."
>> > > >
>> > > > I don't understand this. I have created lists from the worksheet
>> > > > but don't
>> > > > have any now. Why does it think I have a list on my worksheet? And,
>> > > > if I have
>> > > > one, how do I remove it?
>> > > >
>> > > > I couldn't find anything on this in the knowledge base and am very
>> > > > frustrated. Thanks for any help.

The problem for me was that I had used the 'format as table' option in my spreadsheet, to alternate row colors. Apparently doing so creates a different kind of table where you cannot insert rows. To fix it, I had to copy my data into a new table and use the conditional formatting option, with a formula that worked in prior versions of Excel.

Took me a while to figure out, surfed a lot of sites trying to find answers, nothing worked. It seems impossible to undo whatever is done when you choose the 'format as table' option. Everything was visible, nothing was locked, nothing was protected, nothing was merged.

I had the same problem and found what I think is the official fix: Convert your Excel table into a range of cells.

Short answer: Right click within your data, point to Table, and then click Convert to Range.

Details: http://office.microsoft.com/en-us/ex...675551033.aspx