Allow Cell Drag and Drop Option in Excel 2003, 2007 and 2010



Excel Macro | Excel Tips

Hi Friends,

Before I go and explain you how to enable or disable drag and drop option in Excel, let me explain you what is Drag and Drop Option in Excel.

What is Drag and Drop Option in Excel ?

When you select a Cell in Excel and roll your mouse to the Right-Bottom Corner, one Plus Sign ( + ) will be appearing. On Clicking on that you can drag towards Column or Row in any Direction like Left, Right, Above or down. After dragging you can copy the Cells data in all the Cells till you dragged.

While working on Excel, you suddenly find that you are unable to Drag and Drop a Cell. You DO NOT see the little dot at the Right-Bottom corner of your selected cell or range, clicking on which you see a plus sign to hold and drag it in all directions (Up, Down, Left and Right). Refer the above picture (gif)

Huuuhh… without this working with Excel seems difficult because you are not able to drag a Series or Formula and many more things. Then you start looking for option to make this function enabled. Here is the step-by-step solution for that. It differs in Excel 2003 and 2007 or 2010. I will show you, how to Enable/Disable this property in Excel 2003 and 2007. In Excel 2007 and 2010, it is same.

[one_full spacing=”yes” last=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#FEEFB3″ background_image=”” background_repeat=”no-repeat” background_position=”left top” link=”” hover_type=”none” border_position=”all” border_size=”1px” border_color=”#f4b327″ border_style=”dashed” padding=”10px” margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””]
Note: Cell drag and drop option is applicable for Excel as an application. This means, if you disable it in any of your Excel workbook, it will get disabled for Excel application in your computer. It means any workbook you open, you will find it disabled. Similarly, enabling it in any workbook will enable it for all the workbooks you open thereafter[/one_full]

How to Enable or Disable Cell Drag and Drop Option in Excel 2003

Follow the below Steps:
Step 1. Go to Tools –> Options

Drag and Drop in Excel 2003

Step 2. Select the Edit Tab. Here you have that highlighted Checkbox to enable and disable drag and drop in Excel 2003.

Drag and Drop in Excel 2003 – 2

How to Enable or Disable Cell Drag and Drop Option in Excel 2007

Follow the below Steps:
Step 1. Click on the Excel Button at the Top Left Corner of Excel 2007 or 2010.

Drag and Drop in Excel 2007 -1

Step 2. Go to Advanced Tab
Step 3. Here in right hand side, you find the Checkbox to enable and disable this property as shown below in the picture:

Drag and Drop in Excel 2007 -2

Excel VBA code to Enable or Disable Cell Drag and Drop Option

How can I not tell you how to do this in Excel VBA !! Ofcourse, I will tell you the VBA code which can be used to enable or disable the Cell Drag and Drop” option in Excel.
As I have highlighted above, this option is applicable for Excel as an application and not specific to a workbook or worksheet. Hence be careful while enabling and disabling it.


Sub DragAndDropControl()

    'To disable cell drag and drop option in Excel
    Application.CellDragAndDrop = False
    
    'To enable cell drag and drop option in Excel
    Application.CellDragAndDrop = True

End Sub

[one_full spacing=”yes” last=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#FEEFB3″ background_image=”” background_repeat=”no-repeat” background_position=”left top” link=”” hover_type=”none” border_position=”all” border_size=”1px” border_color=”#f4b327″ border_style=”dashed” padding=”10px” margin_top=”” margin_bottom=”” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=””]From above code also you can see that this setting belongs to Application and not to specific to a workbook or worksheet[/one_full]

Buy a coffee for the author

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Hurray..Lets Go

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

Feb 9, 2024

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA

Feb 3, 2024

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

Feb 3, 2024

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

Know more

Praveen on July 23, 2012 at 2:48 pm

The solution was perfect and 100% assisted me in fixing the issue in excel 2007.

Thank you …So Much appreciated !!!

Praveen John
Reply
- Vishwamitra Mishra on July 23, 2012 at 4:41 pm
  
  Thanks Praveen !!
  Reply
Mohammad Ahad on December 23, 2012 at 7:05 am

thanks………

it was really help full to me
Reply
ZeXTree on February 6, 2013 at 5:37 am

Big Thanks !
Reply
Nilesh on December 6, 2013 at 8:16 am

Thanks, this was helpfule
Reply
Ganesh on February 3, 2014 at 10:59 am

This information really helps me to find the solution for 2007 software
Reply
Faiz Asim on December 31, 2014 at 7:55 am

The solution was perfect and 100% assisted me in fixing the issue in excel 2007.

Thank you …So Much appreciated !!!
Reply
khaleel on January 9, 2015 at 7:38 am

thanks, it is very helpful……………
Reply
peter pater on February 11, 2015 at 11:26 am

This solution helped me. Thank u
Reply
jijo on March 17, 2015 at 1:20 pm

thaanx a lot brother ..
much appreciated…
Reply
yogesh on March 27, 2015 at 6:11 am

Thank You Sir
Reply
Tamilarasn on June 13, 2015 at 9:11 am

Its very useful
Reply
RR MAHANTHI on July 22, 2015 at 12:00 pm

Thank you. it is very useful to us.
Reply
deepak on July 27, 2015 at 5:47 am

Thank you. it is very useful to us.
Reply
Hari on August 18, 2015 at 9:11 am

Solution works perfect. Thanks a lot 🙂
Reply
ria on August 31, 2015 at 9:30 am

yess very helpfull
Reply
basuki on September 3, 2015 at 12:21 pm

Thanks for your great help.

Regards,
basuki
Reply
Shanky Agrawal on September 12, 2015 at 10:09 am

Thanks bro….thanks a lot
Reply
Ayang Tayeng on October 10, 2015 at 2:52 am

Thanks for the help. Now its working properly.
Reply
Kishore on February 16, 2016 at 5:50 am

Thank you.
Reply
Vipra on March 7, 2016 at 7:29 am

Great help
Reply
Balram Rathor on June 14, 2016 at 10:34 am

Thanks a lot
Reply
venkatesh on July 6, 2016 at 9:47 am

Thanks
Reply
Nuzhath on August 1, 2016 at 8:03 am

Thank u simple and easy for amateurs like me. so quickly understood.
Reply
Durai Murugan on August 11, 2016 at 7:50 pm

Thanks a ton. It was really helpful to complete my Bau.
Reply
Gopal Menon on September 8, 2016 at 3:30 pm

Thank you, Vishwamitra.

You have solved my problem.
Reply
Sukumaran on February 17, 2017 at 5:16 pm

Thanks Brother…
Reply
- Vishwamitra Mishra on July 20, 2017 at 10:27 am
  
  You are welcome Sukumaran !! Thanks for stopping by here..
  Reply
S David on July 5, 2017 at 4:54 am

Thank you. Ready reckoner for any excel doubt….
Reply
- Vishwamitra Mishra on July 20, 2017 at 10:27 am
  
  Thanks David !! I am glad it helped you 🙂
  Reply
A. Crasta on July 20, 2017 at 8:40 am

Thanks Bro.Excellent coaching even for the dumbest
Reply
- Vishwamitra Mishra on July 20, 2017 at 10:24 am
  
  Thank you so much Crasta for your awesome words !! Keep providing your feedback to improve.
  Reply
bharti on June 7, 2018 at 5:50 am

thank you
Reply
Bhushan HAdke on August 7, 2018 at 5:04 am

Hii

I am having one excel sheet to track the due date of document , i want to design the excel in such manner that if there is an less than 90 days from the due date of review of document excel send the auto mail without any manual intervention and for calculation of days already apply the logic in excel sheet.
Reply
Dnyaneshwar Matre on October 10, 2018 at 5:31 pm

Thank you so much !
Reply
Amir on October 12, 2018 at 10:06 am

but how to prevent drag and drop option for a specific cell not all the sheet
Reply
Hari Prasad on November 1, 2018 at 12:11 pm

Thank you! The explanation was perfect and working very good.
Reply
Al Young on May 2, 2019 at 10:26 pm

How to enable drag and drop a row, but disable drag and drop formulas? (disable the “+” option, but allow users to shift rows around)
Reply
Jaaved on August 16, 2019 at 12:33 pm

thanks a lot dear by ur help i resolved my issue..
Reply
Bill Johnson on January 16, 2020 at 10:11 pm

This is fine and all except in 2019 the tools tab is nowhere to be found.
Reply
Jayesh on September 4, 2020 at 1:15 am

thank you so….. much…..!
Reply
laxman Rawat on January 1, 2021 at 6:44 am

Tnks for the help
Reply
Mark on June 3, 2021 at 11:27 am

Thank you very much!!!
Reply