Copying and Pasting Validation list only in Microsoft Excel

When copying a cell that contains validation criteria to a different cell, the criteria are copied along with the text formula and format. If you want to copy only the validation criteria, use Paste Special.

Paste Special: You can use the Paste Special to paste the specific cell content or attributes such as formula, formatting, comments etc.

Let’s take an example and understand how we can copying and pasting the validation list only in Microsoft Excel.

I have a table of month in Column and we want to create drop down list in cell C3.

img1

To create the Month drop down list follow below given steps:-

  • Select the cell C3, go to in “Data” tab select Data Validation in the Data Tools group.

img2

  • Data Validation dialog box will appear.

img3

  •  In “Settings” tab from the Allow drop down list select List then source tab will get active click on source tab and select the range A2:A13.

img4

  • Go to “Input Message” tab, in “Input Message Box” type “Enter only the month name”, the message box will appear near to the cell.

img5

img6

  • Go to “Error Alert” tab and write the message in the box of “Error Message” Input Error and click on ok

img7

  • If you will enter the number instead of month name the pop up will get to appear and give the message what you had entered in “Error Message” box.

img8

  • Copy the cell C3 by pressing the “CTRL+C” and paste in cell D1, select the cell D1 and right click of the mouse, select “Paste Special” from the popup.

img9

  • Paste Special dialog box will appear, click on Validation Click on ok.

img10

img11

This is the how we can update the drop down list from one cell to another cell by just clicking the copy the cell and use the Paste special function in Microsoft Excel.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Users are saying about us...

  1. Pingback: User

  2. Greg Christopher

    Hi-
    Exactly what I'm looking for but not quite! I have a data validation that is "relative" to the field next to it. In my case it looks like "Allow List" with "=INDIRECT($D$4)". There is a list to the left of my cell in the same row. For each cell in my row I want my pulldown list to be "different" based on the cell to my left.
    Unfortunately "Paste Special" with "Validation" only copies that exact same formula to each cell (just as in your example). I would like it to vary the "$N" part. I suppose this is not possible without Visual basic programming (which I have never done) but thought you wouldn't mind the question.
    Dependent drop down lists are very powerful but so far the data validation scheme seems to only be able to reply to a single cell's validation scheme when using paste special. We are so used to formulas being able to change the row number automatically it's a shame we can't seem to do this with validation formulas.

    • You can absolutely achieve the above by using $N. But I didn't use paste special/validation, but instead just dragged the bottom right corner of the desired cell.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube