Swap cell contents in Excel?
up vote
33
down vote
favorite
Is there an easy way to swap the contents of two cells in Microsoft Excel?
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. In other words, I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents. Surely, there has got to be a way to do this?
microsoft-excel
asked Mar 14 '11 at 16:18
Dan Moulding
3141311
add a comment |
up vote
33
down vote
favorite
Is there an easy way to swap the contents of two cells in Microsoft Excel?
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. In other words, I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents. Surely, there has got to be a way to do this?
microsoft-excel
asked Mar 14 '11 at 16:18
Dan Moulding
3141311
This seems like a very specific implementation of a sort, and if its not already present in the advanced sorting options, I'd be surprised if you can find it elsewhere, as a non-VBA solution
– drapkin11
Mar 14 '11 at 16:29
add a comment |
up vote
33
down vote
favorite
up vote
33
down vote
favorite
Is there an easy way to swap the contents of two cells in Microsoft Excel?
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. In other words, I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents. Surely, there has got to be a way to do this?
microsoft-excel
asked Mar 14 '11 at 16:18
Dan Moulding
3141311
Is there an easy way to swap the contents of two cells in Microsoft Excel?
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. In other words, I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents. Surely, there has got to be a way to do this?
microsoft-excel
microsoft-excel
asked Mar 14 '11 at 16:18
Dan Moulding
3141311
asked Mar 14 '11 at 16:18
Dan Moulding
3141311
asked Mar 14 '11 at 16:18
Dan Moulding
3141311
asked Mar 14 '11 at 16:18
Dan Moulding
3141311
asked Mar 14 '11 at 16:18
Dan Moulding
3141311
3141311
This seems like a very specific implementation of a sort, and if its not already present in the advanced sorting options, I'd be surprised if you can find it elsewhere, as a non-VBA solution
– drapkin11
Mar 14 '11 at 16:29
add a comment |
This seems like a very specific implementation of a sort, and if its not already present in the advanced sorting options, I'd be surprised if you can find it elsewhere, as a non-VBA solution
– drapkin11
Mar 14 '11 at 16:29
This seems like a very specific implementation of a sort, and if its not already present in the advanced sorting options, I'd be surprised if you can find it elsewhere, as a non-VBA solution
– drapkin11
Mar 14 '11 at 16:29
This seems like a very specific implementation of a sort, and if its not already present in the advanced sorting options, I'd be surprised if you can find it elsewhere, as a non-VBA solution
– drapkin11
Mar 14 '11 at 16:29
add a comment |
8 Answers
8
active
oldest
votes
up vote
43
down vote
accepted
From: http://www.extendoffice.com/documents/excel/860-excel-swap-contents-of-two-cells.html
Sometimes, there are two adjoining cells need to be swapped. We can manually do it easily. Look at the following screenshot, I want to swap cell A4 and B4, please do as follows:
Select the cell you want to swap. In this example, select cell A4.
Press Shift key, and put the cursor at the right border.
Then drag the cursor to the right border of cell B4.
When there displays “工”, release the mouse.
And the two cell contents have been swapped.
With this method, we can also swap two adjoining rows or columns.
edited Sep 10 '15 at 19:24
nixda
20.6k777131
answered Jan 15 '13 at 19:12
user190216
45452
6
This should be the selected anser to me. Thank you!
– Nam G VU
Feb 27 '14 at 6:45
It doesn't work for me. It just insert empty cells between those 2 cells
– phuclv
Feb 18 '16 at 10:09
This only works for adjacent cells which is not what the original question asked for.
– jpierson
Jul 14 '17 at 1:22
This doesn't work at all for me on Excel 2010 - it quickly flashes, but the contents of the cells remain unchanged.
– Hashim
Oct 12 '17 at 22:05
add a comment |
up vote
14
down vote
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents.
Why impose this restriction? Creating a macro makes this trivial. As far as I know, it can't be done any other way. You can assign the macro to a button or hotkey.
Sub Swap()
If Selection.Count <> 2 Then
MsgBox "Select 2 cells (only) to swap."
Exit Sub
End If
Set trange = Selection
If trange.Areas.Count = 2 Then
temp = trange.Areas(2)
trange.Areas(2) = trange.Areas(1)
trange.Areas(1) = temp
Else
temp = trange(1)
trange(1) = trange(2)
trange(2) = temp
End If
End Sub
answered Mar 14 '11 at 16:44
ghoppe
6,0441621
4
I'm not a VBA expert and have neither the time nor the inclination to become one. I also frequently use many different computers. It would be nice to have swapping available on every computer I use for Excel, without having to look-up the code for the VBA macro that does it, so that I can bind it to a hotkey.
– Dan Moulding
Mar 14 '11 at 19:16
3
@Dan It would be nice if everyone could have their pet functionality built into Excel, but then it would become even more a bloated mess than it already is. There is a reason why Excel has a macro language, to automate repetitive tasks like this. I posted code found through a google search. You don't have to be an expert.
– ghoppe
Mar 14 '11 at 19:29
+1 vba is the way excel allows you to add your pet functions. there are also ways of making that vba avaliable on many computers, to just yourself or to all (eg personal workbook, or addins)
– chris neilsen
Mar 18 '11 at 23:34
8
I would hardly call swapping the content of two cells / rows /columns to be a pet function.
– Nicolai
Feb 17 '12 at 11:35
5
@ghoppe Maybe not essential, but if you do a quick search on "swap the content of two cells in excel" (which you already have) it is quite obvious that Dan and I are hardly the only ones that care about this feature.
– Nicolai
Feb 21 '12 at 13:57
|
show 1 more comment
up vote
12
down vote
For the specific case of adjacent same-size rectangular ranges, you can use the method described in this answer to a similar question.
- Select the right or bottom range
- Press Ctrl+X
- Select the adjacent range (i.e., directly above or to the left)
- Press Ctrl++ (the
+is usually above the=key so this translates to Ctrl+Shift+=)
Note you can use the same procedure to swap whole adjacent rows or columns.
edited Nov 22 at 6:55
Scott
15.4k113789
answered Nov 22 '12 at 9:20
Jonas Heidelberg
1,25711534
1
Excellent. This is exactly what I was looking for. Being able to use keyboard shortcuts and being able to operate on multiple cells is so much faster.
– Anthony Geoghegan
Nov 2 '15 at 9:36
add a comment |
up vote
4
down vote
No. There is no way to swap the contents of two cells in Excel, without writing your own macro to do it.
EDIT: It sounds like there may now be an easy way to swap cell contents in more recent versions of Excel, so this answer is probably now out of date.
answered Mar 24 '11 at 20:24
Dan Moulding
3141311
this statement is incorrect. See the answer from user190216.
– Karl Rookey
Sep 10 '15 at 18:08
@KarlRookey Originally that answer stated (and it kind of still does) that the cells must be adjacent. It sounds like this may no longer be true for more recent versions of Excel. If that's the case, then, yes, there is now a better answer. Thanks!
– Dan Moulding
Sep 10 '15 at 18:20
add a comment |
up vote
2
down vote
Select first set of cells to be swapped and hit ctrl+x:
Select the cells BESIDE the ones you want to swap with and hit ctrl++.
edited Jan 5 '14 at 20:02
Ƭᴇcʜιᴇ007
98.1k14153212
answered Jan 5 '14 at 19:25
user287020
211
1
This works only if cells are adjacent. Otherwise the whole stuff is shifted near target cells.
– tumchaaditya
Jan 5 '14 at 19:45
add a comment |
up vote
0
down vote
You can paste up to 25 items to the clipboard, so they are easy to swap using ctr+tab or cmd+tab mac
answered Dec 5 '14 at 17:19
Aaron
91
2
Is this a standard Mac feature? The author of the question didn't specify what platform they are on. I'm not sure whether the standard clipboard in all versions of Windows holds more than one item, but free utilities are readily available to provide that functionality (and the keyboard shortcuts may vary). Could you expand your answer to make it more comprehensive for this approach?
– fixer1234
Dec 5 '14 at 18:14
I tried this on Windows but couldn't figure it out. I even tried the ctrl+shift+p option that normally works in Visual Studio to paste out of the clipboard ring.
– jpierson
Jul 14 '17 at 1:21
add a comment |
up vote
0
down vote
I read this post but actually needed a macro to swap full ranges. In addition, I needed to swap the colors. Modded the originally posted macro slightly, this might be useful for someone.
Sub Swap()
If Selection.Areas.Count <> 2 Then
MsgBox "Select 2 cell ranges (only) to swap."
Exit Sub
End If
If Selection.Areas(1).Count <> Selection.Areas(2).Count Then
MsgBox "The two areas must be of equal size"
Exit Sub
End If
'With this for loop we run through each cell 1 by 1
For i = 1 To Selection.Areas(1).Count
'Swapping values
temp = Selection.Areas(1)(i)
Selection.Areas(1)(i) = Selection.Areas(2)(i)
Selection.Areas(2)(i) = temp
'Swapping color
tempColor = Selection.Areas(1)(i).DisplayFormat.Interior.Color
Selection.Areas(1)(i).Interior.Color = Selection.Areas(2)(i).DisplayFormat.Interior.Color
Selection.Areas(2)(i).Interior.Color = tempColor
Next i
End Sub
answered Jan 8 at 3:00
Ivo van der Marel
1
add a comment |
up vote
0
down vote
Select the bottom cell you want to swap
Press Ctrl + x
and go to cell you want to swap with
Press Ctrl + Shift + =
the swap will be executed
answered Nov 22 at 4:53
mehndiratta co
1
This is exactly the same as Jonas Heidelberg's answer, from (exactly!) six years ago, and effectively the same as user287020's answer.
– Scott
Nov 22 at 6:25
add a comment |
8 Answers
8
active
oldest
votes
8 Answers
8
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
43
down vote
accepted
From: http://www.extendoffice.com/documents/excel/860-excel-swap-contents-of-two-cells.html
Sometimes, there are two adjoining cells need to be swapped. We can manually do it easily. Look at the following screenshot, I want to swap cell A4 and B4, please do as follows:
Select the cell you want to swap. In this example, select cell A4.
Press Shift key, and put the cursor at the right border.
Then drag the cursor to the right border of cell B4.
When there displays “工”, release the mouse.
And the two cell contents have been swapped.
With this method, we can also swap two adjoining rows or columns.
edited Sep 10 '15 at 19:24
nixda
20.6k777131
answered Jan 15 '13 at 19:12
user190216
45452
6
This should be the selected anser to me. Thank you!
– Nam G VU
Feb 27 '14 at 6:45
It doesn't work for me. It just insert empty cells between those 2 cells
– phuclv
Feb 18 '16 at 10:09
This only works for adjacent cells which is not what the original question asked for.
– jpierson
Jul 14 '17 at 1:22
This doesn't work at all for me on Excel 2010 - it quickly flashes, but the contents of the cells remain unchanged.
– Hashim
Oct 12 '17 at 22:05
add a comment |
up vote
43
down vote
accepted
From: http://www.extendoffice.com/documents/excel/860-excel-swap-contents-of-two-cells.html
Sometimes, there are two adjoining cells need to be swapped. We can manually do it easily. Look at the following screenshot, I want to swap cell A4 and B4, please do as follows:
Select the cell you want to swap. In this example, select cell A4.
Press Shift key, and put the cursor at the right border.
Then drag the cursor to the right border of cell B4.
When there displays “工”, release the mouse.
And the two cell contents have been swapped.
With this method, we can also swap two adjoining rows or columns.
edited Sep 10 '15 at 19:24
nixda
20.6k777131
answered Jan 15 '13 at 19:12
user190216
45452
6
This should be the selected anser to me. Thank you!
– Nam G VU
Feb 27 '14 at 6:45
It doesn't work for me. It just insert empty cells between those 2 cells
– phuclv
Feb 18 '16 at 10:09
This only works for adjacent cells which is not what the original question asked for.
– jpierson
Jul 14 '17 at 1:22
This doesn't work at all for me on Excel 2010 - it quickly flashes, but the contents of the cells remain unchanged.
– Hashim
Oct 12 '17 at 22:05
add a comment |
up vote
43
down vote
accepted
up vote
43
down vote
accepted
From: http://www.extendoffice.com/documents/excel/860-excel-swap-contents-of-two-cells.html
Sometimes, there are two adjoining cells need to be swapped. We can manually do it easily. Look at the following screenshot, I want to swap cell A4 and B4, please do as follows:
Select the cell you want to swap. In this example, select cell A4.
Press Shift key, and put the cursor at the right border.
Then drag the cursor to the right border of cell B4.
When there displays “工”, release the mouse.
And the two cell contents have been swapped.
With this method, we can also swap two adjoining rows or columns.
edited Sep 10 '15 at 19:24
nixda
20.6k777131
answered Jan 15 '13 at 19:12
user190216
45452
From: http://www.extendoffice.com/documents/excel/860-excel-swap-contents-of-two-cells.html
Sometimes, there are two adjoining cells need to be swapped. We can manually do it easily. Look at the following screenshot, I want to swap cell A4 and B4, please do as follows:
Select the cell you want to swap. In this example, select cell A4.
Press Shift key, and put the cursor at the right border.
Then drag the cursor to the right border of cell B4.
When there displays “工”, release the mouse.
And the two cell contents have been swapped.
With this method, we can also swap two adjoining rows or columns.
edited Sep 10 '15 at 19:24
nixda
20.6k777131
answered Jan 15 '13 at 19:12
user190216
45452
edited Sep 10 '15 at 19:24
nixda
20.6k777131
edited Sep 10 '15 at 19:24
nixda
20.6k777131
edited Sep 10 '15 at 19:24
nixda
20.6k777131
20.6k777131
answered Jan 15 '13 at 19:12
user190216
45452
answered Jan 15 '13 at 19:12
user190216
45452
answered Jan 15 '13 at 19:12
user190216
45452
45452
6
This should be the selected anser to me. Thank you!
– Nam G VU
Feb 27 '14 at 6:45
It doesn't work for me. It just insert empty cells between those 2 cells
– phuclv
Feb 18 '16 at 10:09
This only works for adjacent cells which is not what the original question asked for.
– jpierson
Jul 14 '17 at 1:22
This doesn't work at all for me on Excel 2010 - it quickly flashes, but the contents of the cells remain unchanged.
– Hashim
Oct 12 '17 at 22:05
add a comment |
6
This should be the selected anser to me. Thank you!
– Nam G VU
Feb 27 '14 at 6:45
It doesn't work for me. It just insert empty cells between those 2 cells
– phuclv
Feb 18 '16 at 10:09
This only works for adjacent cells which is not what the original question asked for.
– jpierson
Jul 14 '17 at 1:22
This doesn't work at all for me on Excel 2010 - it quickly flashes, but the contents of the cells remain unchanged.
– Hashim
Oct 12 '17 at 22:05
6
6
This should be the selected anser to me. Thank you!
– Nam G VU
Feb 27 '14 at 6:45
This should be the selected anser to me. Thank you!
– Nam G VU
Feb 27 '14 at 6:45
It doesn't work for me. It just insert empty cells between those 2 cells
– phuclv
Feb 18 '16 at 10:09
It doesn't work for me. It just insert empty cells between those 2 cells
– phuclv
Feb 18 '16 at 10:09
This only works for adjacent cells which is not what the original question asked for.
– jpierson
Jul 14 '17 at 1:22
This only works for adjacent cells which is not what the original question asked for.
– jpierson
Jul 14 '17 at 1:22
This doesn't work at all for me on Excel 2010 - it quickly flashes, but the contents of the cells remain unchanged.
– Hashim
Oct 12 '17 at 22:05
This doesn't work at all for me on Excel 2010 - it quickly flashes, but the contents of the cells remain unchanged.
– Hashim
Oct 12 '17 at 22:05
add a comment |
up vote
14
down vote
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents.
Why impose this restriction? Creating a macro makes this trivial. As far as I know, it can't be done any other way. You can assign the macro to a button or hotkey.
Sub Swap()
If Selection.Count <> 2 Then
MsgBox "Select 2 cells (only) to swap."
Exit Sub
End If
Set trange = Selection
If trange.Areas.Count = 2 Then
temp = trange.Areas(2)
trange.Areas(2) = trange.Areas(1)
trange.Areas(1) = temp
Else
temp = trange(1)
trange(1) = trange(2)
trange(2) = temp
End If
End Sub
answered Mar 14 '11 at 16:44
ghoppe
6,0441621
4
I'm not a VBA expert and have neither the time nor the inclination to become one. I also frequently use many different computers. It would be nice to have swapping available on every computer I use for Excel, without having to look-up the code for the VBA macro that does it, so that I can bind it to a hotkey.
– Dan Moulding
Mar 14 '11 at 19:16
3
@Dan It would be nice if everyone could have their pet functionality built into Excel, but then it would become even more a bloated mess than it already is. There is a reason why Excel has a macro language, to automate repetitive tasks like this. I posted code found through a google search. You don't have to be an expert.
– ghoppe
Mar 14 '11 at 19:29
+1 vba is the way excel allows you to add your pet functions. there are also ways of making that vba avaliable on many computers, to just yourself or to all (eg personal workbook, or addins)
– chris neilsen
Mar 18 '11 at 23:34
8
I would hardly call swapping the content of two cells / rows /columns to be a pet function.
– Nicolai
Feb 17 '12 at 11:35
5
@ghoppe Maybe not essential, but if you do a quick search on "swap the content of two cells in excel" (which you already have) it is quite obvious that Dan and I are hardly the only ones that care about this feature.
– Nicolai
Feb 21 '12 at 13:57
|
show 1 more comment
up vote
14
down vote
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents.
Why impose this restriction? Creating a macro makes this trivial. As far as I know, it can't be done any other way. You can assign the macro to a button or hotkey.
Sub Swap()
If Selection.Count <> 2 Then
MsgBox "Select 2 cells (only) to swap."
Exit Sub
End If
Set trange = Selection
If trange.Areas.Count = 2 Then
temp = trange.Areas(2)
trange.Areas(2) = trange.Areas(1)
trange.Areas(1) = temp
Else
temp = trange(1)
trange(1) = trange(2)
trange(2) = temp
End If
End Sub
answered Mar 14 '11 at 16:44
ghoppe
6,0441621
4
I'm not a VBA expert and have neither the time nor the inclination to become one. I also frequently use many different computers. It would be nice to have swapping available on every computer I use for Excel, without having to look-up the code for the VBA macro that does it, so that I can bind it to a hotkey.
– Dan Moulding
Mar 14 '11 at 19:16
3
@Dan It would be nice if everyone could have their pet functionality built into Excel, but then it would become even more a bloated mess than it already is. There is a reason why Excel has a macro language, to automate repetitive tasks like this. I posted code found through a google search. You don't have to be an expert.
– ghoppe
Mar 14 '11 at 19:29
+1 vba is the way excel allows you to add your pet functions. there are also ways of making that vba avaliable on many computers, to just yourself or to all (eg personal workbook, or addins)
– chris neilsen
Mar 18 '11 at 23:34
8
I would hardly call swapping the content of two cells / rows /columns to be a pet function.
– Nicolai
Feb 17 '12 at 11:35
5
@ghoppe Maybe not essential, but if you do a quick search on "swap the content of two cells in excel" (which you already have) it is quite obvious that Dan and I are hardly the only ones that care about this feature.
– Nicolai
Feb 21 '12 at 13:57
|
show 1 more comment
up vote
14
down vote
up vote
14
down vote
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents.
Why impose this restriction? Creating a macro makes this trivial. As far as I know, it can't be done any other way. You can assign the macro to a button or hotkey.
Sub Swap()
If Selection.Count <> 2 Then
MsgBox "Select 2 cells (only) to swap."
Exit Sub
End If
Set trange = Selection
If trange.Areas.Count = 2 Then
temp = trange.Areas(2)
trange.Areas(2) = trange.Areas(1)
trange.Areas(1) = temp
Else
temp = trange(1)
trange(1) = trange(2)
trange(2) = temp
End If
End Sub
answered Mar 14 '11 at 16:44
ghoppe
6,0441621
By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts or anything like that. I'm looking for a way to just select two cells and click some menu item or press some key combination that will swap their contents.
Why impose this restriction? Creating a macro makes this trivial. As far as I know, it can't be done any other way. You can assign the macro to a button or hotkey.
Sub Swap()
If Selection.Count <> 2 Then
MsgBox "Select 2 cells (only) to swap."
Exit Sub
End If
Set trange = Selection
If trange.Areas.Count = 2 Then
temp = trange.Areas(2)
trange.Areas(2) = trange.Areas(1)
trange.Areas(1) = temp
Else
temp = trange(1)
trange(1) = trange(2)
trange(2) = temp
End If
End Sub
answered Mar 14 '11 at 16:44
ghoppe
6,0441621
answered Mar 14 '11 at 16:44
ghoppe
6,0441621
answered Mar 14 '11 at 16:44
ghoppe
6,0441621
answered Mar 14 '11 at 16:44
ghoppe
6,0441621
6,0441621
4
I'm not a VBA expert and have neither the time nor the inclination to become one. I also frequently use many different computers. It would be nice to have swapping available on every computer I use for Excel, without having to look-up the code for the VBA macro that does it, so that I can bind it to a hotkey.
– Dan Moulding
Mar 14 '11 at 19:16
3
@Dan It would be nice if everyone could have their pet functionality built into Excel, but then it would become even more a bloated mess than it already is. There is a reason why Excel has a macro language, to automate repetitive tasks like this. I posted code found through a google search. You don't have to be an expert.
– ghoppe
Mar 14 '11 at 19:29
+1 vba is the way excel allows you to add your pet functions. there are also ways of making that vba avaliable on many computers, to just yourself or to all (eg personal workbook, or addins)
– chris neilsen
Mar 18 '11 at 23:34
8
I would hardly call swapping the content of two cells / rows /columns to be a pet function.
– Nicolai
Feb 17 '12 at 11:35
5
@ghoppe Maybe not essential, but if you do a quick search on "swap the content of two cells in excel" (which you already have) it is quite obvious that Dan and I are hardly the only ones that care about this feature.
– Nicolai
Feb 21 '12 at 13:57
|
show 1 more comment
4
I'm not a VBA expert and have neither the time nor the inclination to become one. I also frequently use many different computers. It would be nice to have swapping available on every computer I use for Excel, without having to look-up the code for the VBA macro that does it, so that I can bind it to a hotkey.
– Dan Moulding
Mar 14 '11 at 19:16
3
@Dan It would be nice if everyone could have their pet functionality built into Excel, but then it would become even more a bloated mess than it already is. There is a reason why Excel has a macro language, to automate repetitive tasks like this. I posted code found through a google search. You don't have to be an expert.
– ghoppe
Mar 14 '11 at 19:29
+1 vba is the way excel allows you to add your pet functions. there are also ways of making that vba avaliable on many computers, to just yourself or to all (eg personal workbook, or addins)
– chris neilsen
Mar 18 '11 at 23:34
8
I would hardly call swapping the content of two cells / rows /columns to be a pet function.
– Nicolai
Feb 17 '12 at 11:35
5
@ghoppe Maybe not essential, but if you do a quick search on "swap the content of two cells in excel" (which you already have) it is quite obvious that Dan and I are hardly the only ones that care about this feature.
– Nicolai
Feb 21 '12 at 13:57
4
4
I'm not a VBA expert and have neither the time nor the inclination to become one. I also frequently use many different computers. It would be nice to have swapping available on every computer I use for Excel, without having to look-up the code for the VBA macro that does it, so that I can bind it to a hotkey.
– Dan Moulding
Mar 14 '11 at 19:16
I'm not a VBA expert and have neither the time nor the inclination to become one. I also frequently use many different computers. It would be nice to have swapping available on every computer I use for Excel, without having to look-up the code for the VBA macro that does it, so that I can bind it to a hotkey.
– Dan Moulding
Mar 14 '11 at 19:16
3
3
@Dan It would be nice if everyone could have their pet functionality built into Excel, but then it would become even more a bloated mess than it already is. There is a reason why Excel has a macro language, to automate repetitive tasks like this. I posted code found through a google search. You don't have to be an expert.
– ghoppe
Mar 14 '11 at 19:29
@Dan It would be nice if everyone could have their pet functionality built into Excel, but then it would become even more a bloated mess than it already is. There is a reason why Excel has a macro language, to automate repetitive tasks like this. I posted code found through a google search. You don't have to be an expert.
– ghoppe
Mar 14 '11 at 19:29
+1 vba is the way excel allows you to add your pet functions. there are also ways of making that vba avaliable on many computers, to just yourself or to all (eg personal workbook, or addins)
– chris neilsen
Mar 18 '11 at 23:34
+1 vba is the way excel allows you to add your pet functions. there are also ways of making that vba avaliable on many computers, to just yourself or to all (eg personal workbook, or addins)
– chris neilsen
Mar 18 '11 at 23:34
8
8
I would hardly call swapping the content of two cells / rows /columns to be a pet function.
– Nicolai
Feb 17 '12 at 11:35
I would hardly call swapping the content of two cells / rows /columns to be a pet function.
– Nicolai
Feb 17 '12 at 11:35
5
5
@ghoppe Maybe not essential, but if you do a quick search on "swap the content of two cells in excel" (which you already have) it is quite obvious that Dan and I are hardly the only ones that care about this feature.
– Nicolai
Feb 21 '12 at 13:57
@ghoppe Maybe not essential, but if you do a quick search on "swap the content of two cells in excel" (which you already have) it is quite obvious that Dan and I are hardly the only ones that care about this feature.
– Nicolai
Feb 21 '12 at 13:57
|
show 1 more comment
up vote
12
down vote
For the specific case of adjacent same-size rectangular ranges, you can use the method described in this answer to a similar question.
- Select the right or bottom range
- Press Ctrl+X
- Select the adjacent range (i.e., directly above or to the left)
- Press Ctrl++ (the
+is usually above the=key so this translates to Ctrl+Shift+=)
Note you can use the same procedure to swap whole adjacent rows or columns.
edited Nov 22 at 6:55
Scott
15.4k113789
answered Nov 22 '12 at 9:20
Jonas Heidelberg
1,25711534
1
Excellent. This is exactly what I was looking for. Being able to use keyboard shortcuts and being able to operate on multiple cells is so much faster.
– Anthony Geoghegan
Nov 2 '15 at 9:36
add a comment |
up vote
12
down vote
For the specific case of adjacent same-size rectangular ranges, you can use the method described in this answer to a similar question.
- Select the right or bottom range
- Press Ctrl+X
- Select the adjacent range (i.e., directly above or to the left)
- Press Ctrl++ (the
+is usually above the=key so this translates to Ctrl+Shift+=)
Note you can use the same procedure to swap whole adjacent rows or columns.
edited Nov 22 at 6:55
Scott
15.4k113789
answered Nov 22 '12 at 9:20
Jonas Heidelberg
1,25711534
1
Excellent. This is exactly what I was looking for. Being able to use keyboard shortcuts and being able to operate on multiple cells is so much faster.
– Anthony Geoghegan
Nov 2 '15 at 9:36
add a comment |
up vote
12
down vote
up vote
12
down vote
For the specific case of adjacent same-size rectangular ranges, you can use the method described in this answer to a similar question.
- Select the right or bottom range
- Press Ctrl+X
- Select the adjacent range (i.e., directly above or to the left)
- Press Ctrl++ (the
+is usually above the=key so this translates to Ctrl+Shift+=)
Note you can use the same procedure to swap whole adjacent rows or columns.
edited Nov 22 at 6:55
Scott
15.4k113789
answered Nov 22 '12 at 9:20
Jonas Heidelberg
1,25711534
For the specific case of adjacent same-size rectangular ranges, you can use the method described in this answer to a similar question.
- Select the right or bottom range
- Press Ctrl+X
- Select the adjacent range (i.e., directly above or to the left)
- Press Ctrl++ (the
+is usually above the=key so this translates to Ctrl+Shift+=)
Note you can use the same procedure to swap whole adjacent rows or columns.
edited Nov 22 at 6:55
Scott
15.4k113789
answered Nov 22 '12 at 9:20
Jonas Heidelberg
1,25711534
edited Nov 22 at 6:55
Scott
15.4k113789
edited Nov 22 at 6:55
Scott
15.4k113789
edited Nov 22 at 6:55
Scott
15.4k113789
15.4k113789
answered Nov 22 '12 at 9:20
Jonas Heidelberg
1,25711534
answered Nov 22 '12 at 9:20
Jonas Heidelberg
1,25711534
answered Nov 22 '12 at 9:20
Jonas Heidelberg
1,25711534
1,25711534
1
Excellent. This is exactly what I was looking for. Being able to use keyboard shortcuts and being able to operate on multiple cells is so much faster.
– Anthony Geoghegan
Nov 2 '15 at 9:36
add a comment |
1
Excellent. This is exactly what I was looking for. Being able to use keyboard shortcuts and being able to operate on multiple cells is so much faster.
– Anthony Geoghegan
Nov 2 '15 at 9:36
1
1
Excellent. This is exactly what I was looking for. Being able to use keyboard shortcuts and being able to operate on multiple cells is so much faster.
– Anthony Geoghegan
Nov 2 '15 at 9:36
Excellent. This is exactly what I was looking for. Being able to use keyboard shortcuts and being able to operate on multiple cells is so much faster.
– Anthony Geoghegan
Nov 2 '15 at 9:36
add a comment |
up vote
4
down vote
No. There is no way to swap the contents of two cells in Excel, without writing your own macro to do it.
EDIT: It sounds like there may now be an easy way to swap cell contents in more recent versions of Excel, so this answer is probably now out of date.
answered Mar 24 '11 at 20:24
Dan Moulding
3141311
this statement is incorrect. See the answer from user190216.
– Karl Rookey
Sep 10 '15 at 18:08
@KarlRookey Originally that answer stated (and it kind of still does) that the cells must be adjacent. It sounds like this may no longer be true for more recent versions of Excel. If that's the case, then, yes, there is now a better answer. Thanks!
– Dan Moulding
Sep 10 '15 at 18:20
add a comment |
up vote
4
down vote
No. There is no way to swap the contents of two cells in Excel, without writing your own macro to do it.
EDIT: It sounds like there may now be an easy way to swap cell contents in more recent versions of Excel, so this answer is probably now out of date.
answered Mar 24 '11 at 20:24
Dan Moulding
3141311
this statement is incorrect. See the answer from user190216.
– Karl Rookey
Sep 10 '15 at 18:08
@KarlRookey Originally that answer stated (and it kind of still does) that the cells must be adjacent. It sounds like this may no longer be true for more recent versions of Excel. If that's the case, then, yes, there is now a better answer. Thanks!
– Dan Moulding
Sep 10 '15 at 18:20
add a comment |
up vote
4
down vote
up vote
4
down vote
No. There is no way to swap the contents of two cells in Excel, without writing your own macro to do it.
EDIT: It sounds like there may now be an easy way to swap cell contents in more recent versions of Excel, so this answer is probably now out of date.
answered Mar 24 '11 at 20:24
Dan Moulding
3141311
No. There is no way to swap the contents of two cells in Excel, without writing your own macro to do it.
EDIT: It sounds like there may now be an easy way to swap cell contents in more recent versions of Excel, so this answer is probably now out of date.
answered Mar 24 '11 at 20:24
Dan Moulding
3141311
edited Sep 10 '15 at 18:22
answered Mar 24 '11 at 20:24
Dan Moulding
3141311
answered Mar 24 '11 at 20:24
Dan Moulding
3141311
answered Mar 24 '11 at 20:24
Dan Moulding
3141311
3141311
this statement is incorrect. See the answer from user190216.
– Karl Rookey
Sep 10 '15 at 18:08
@KarlRookey Originally that answer stated (and it kind of still does) that the cells must be adjacent. It sounds like this may no longer be true for more recent versions of Excel. If that's the case, then, yes, there is now a better answer. Thanks!
– Dan Moulding
Sep 10 '15 at 18:20
add a comment |
this statement is incorrect. See the answer from user190216.
– Karl Rookey
Sep 10 '15 at 18:08
@KarlRookey Originally that answer stated (and it kind of still does) that the cells must be adjacent. It sounds like this may no longer be true for more recent versions of Excel. If that's the case, then, yes, there is now a better answer. Thanks!
– Dan Moulding
Sep 10 '15 at 18:20
this statement is incorrect. See the answer from user190216.
– Karl Rookey
Sep 10 '15 at 18:08
this statement is incorrect. See the answer from user190216.
– Karl Rookey
Sep 10 '15 at 18:08
@KarlRookey Originally that answer stated (and it kind of still does) that the cells must be adjacent. It sounds like this may no longer be true for more recent versions of Excel. If that's the case, then, yes, there is now a better answer. Thanks!
– Dan Moulding
Sep 10 '15 at 18:20
@KarlRookey Originally that answer stated (and it kind of still does) that the cells must be adjacent. It sounds like this may no longer be true for more recent versions of Excel. If that's the case, then, yes, there is now a better answer. Thanks!
– Dan Moulding
Sep 10 '15 at 18:20
add a comment |
up vote
2
down vote
Select first set of cells to be swapped and hit ctrl+x:
Select the cells BESIDE the ones you want to swap with and hit ctrl++.
edited Jan 5 '14 at 20:02
Ƭᴇcʜιᴇ007
98.1k14153212
answered Jan 5 '14 at 19:25
user287020
211
1
This works only if cells are adjacent. Otherwise the whole stuff is shifted near target cells.
– tumchaaditya
Jan 5 '14 at 19:45
add a comment |
up vote
2
down vote
Select first set of cells to be swapped and hit ctrl+x:
Select the cells BESIDE the ones you want to swap with and hit ctrl++.
edited Jan 5 '14 at 20:02
Ƭᴇcʜιᴇ007
98.1k14153212
answered Jan 5 '14 at 19:25
user287020
211
1
This works only if cells are adjacent. Otherwise the whole stuff is shifted near target cells.
– tumchaaditya
Jan 5 '14 at 19:45
add a comment |
up vote
2
down vote
up vote
2
down vote
Select first set of cells to be swapped and hit ctrl+x:
Select the cells BESIDE the ones you want to swap with and hit ctrl++.
edited Jan 5 '14 at 20:02
Ƭᴇcʜιᴇ007
98.1k14153212
answered Jan 5 '14 at 19:25
user287020
211
Select first set of cells to be swapped and hit ctrl+x:
Select the cells BESIDE the ones you want to swap with and hit ctrl++.
edited Jan 5 '14 at 20:02
Ƭᴇcʜιᴇ007
98.1k14153212
answered Jan 5 '14 at 19:25
user287020
211
edited Jan 5 '14 at 20:02
Ƭᴇcʜιᴇ007
98.1k14153212
edited Jan 5 '14 at 20:02
Ƭᴇcʜιᴇ007
98.1k14153212
edited Jan 5 '14 at 20:02
Ƭᴇcʜιᴇ007
98.1k14153212
98.1k14153212
answered Jan 5 '14 at 19:25
user287020
211
answered Jan 5 '14 at 19:25
user287020
211
answered Jan 5 '14 at 19:25
user287020
211
211
1
This works only if cells are adjacent. Otherwise the whole stuff is shifted near target cells.
– tumchaaditya
Jan 5 '14 at 19:45
add a comment |
1
This works only if cells are adjacent. Otherwise the whole stuff is shifted near target cells.
– tumchaaditya
Jan 5 '14 at 19:45
1
1
This works only if cells are adjacent. Otherwise the whole stuff is shifted near target cells.
– tumchaaditya
Jan 5 '14 at 19:45
This works only if cells are adjacent. Otherwise the whole stuff is shifted near target cells.
– tumchaaditya
Jan 5 '14 at 19:45
add a comment |
up vote
0
down vote
You can paste up to 25 items to the clipboard, so they are easy to swap using ctr+tab or cmd+tab mac
answered Dec 5 '14 at 17:19
Aaron
91
2
Is this a standard Mac feature? The author of the question didn't specify what platform they are on. I'm not sure whether the standard clipboard in all versions of Windows holds more than one item, but free utilities are readily available to provide that functionality (and the keyboard shortcuts may vary). Could you expand your answer to make it more comprehensive for this approach?
– fixer1234
Dec 5 '14 at 18:14
I tried this on Windows but couldn't figure it out. I even tried the ctrl+shift+p option that normally works in Visual Studio to paste out of the clipboard ring.
– jpierson
Jul 14 '17 at 1:21
add a comment |
up vote
0
down vote
You can paste up to 25 items to the clipboard, so they are easy to swap using ctr+tab or cmd+tab mac
answered Dec 5 '14 at 17:19
Aaron
91
2
Is this a standard Mac feature? The author of the question didn't specify what platform they are on. I'm not sure whether the standard clipboard in all versions of Windows holds more than one item, but free utilities are readily available to provide that functionality (and the keyboard shortcuts may vary). Could you expand your answer to make it more comprehensive for this approach?
– fixer1234
Dec 5 '14 at 18:14
I tried this on Windows but couldn't figure it out. I even tried the ctrl+shift+p option that normally works in Visual Studio to paste out of the clipboard ring.
– jpierson
Jul 14 '17 at 1:21
add a comment |
up vote
0
down vote
up vote
0
down vote
You can paste up to 25 items to the clipboard, so they are easy to swap using ctr+tab or cmd+tab mac
answered Dec 5 '14 at 17:19
Aaron
91
You can paste up to 25 items to the clipboard, so they are easy to swap using ctr+tab or cmd+tab mac
answered Dec 5 '14 at 17:19
Aaron
91
answered Dec 5 '14 at 17:19
Aaron
91
answered Dec 5 '14 at 17:19
Aaron
91
answered Dec 5 '14 at 17:19
Aaron
91
91
2
Is this a standard Mac feature? The author of the question didn't specify what platform they are on. I'm not sure whether the standard clipboard in all versions of Windows holds more than one item, but free utilities are readily available to provide that functionality (and the keyboard shortcuts may vary). Could you expand your answer to make it more comprehensive for this approach?
– fixer1234
Dec 5 '14 at 18:14
I tried this on Windows but couldn't figure it out. I even tried the ctrl+shift+p option that normally works in Visual Studio to paste out of the clipboard ring.
– jpierson
Jul 14 '17 at 1:21
add a comment |
2
Is this a standard Mac feature? The author of the question didn't specify what platform they are on. I'm not sure whether the standard clipboard in all versions of Windows holds more than one item, but free utilities are readily available to provide that functionality (and the keyboard shortcuts may vary). Could you expand your answer to make it more comprehensive for this approach?
– fixer1234
Dec 5 '14 at 18:14
I tried this on Windows but couldn't figure it out. I even tried the ctrl+shift+p option that normally works in Visual Studio to paste out of the clipboard ring.
– jpierson
Jul 14 '17 at 1:21
2
2
Is this a standard Mac feature? The author of the question didn't specify what platform they are on. I'm not sure whether the standard clipboard in all versions of Windows holds more than one item, but free utilities are readily available to provide that functionality (and the keyboard shortcuts may vary). Could you expand your answer to make it more comprehensive for this approach?
– fixer1234
Dec 5 '14 at 18:14
Is this a standard Mac feature? The author of the question didn't specify what platform they are on. I'm not sure whether the standard clipboard in all versions of Windows holds more than one item, but free utilities are readily available to provide that functionality (and the keyboard shortcuts may vary). Could you expand your answer to make it more comprehensive for this approach?
– fixer1234
Dec 5 '14 at 18:14
I tried this on Windows but couldn't figure it out. I even tried the ctrl+shift+p option that normally works in Visual Studio to paste out of the clipboard ring.
– jpierson
Jul 14 '17 at 1:21
I tried this on Windows but couldn't figure it out. I even tried the ctrl+shift+p option that normally works in Visual Studio to paste out of the clipboard ring.
– jpierson
Jul 14 '17 at 1:21
add a comment |
up vote
0
down vote
I read this post but actually needed a macro to swap full ranges. In addition, I needed to swap the colors. Modded the originally posted macro slightly, this might be useful for someone.
Sub Swap()
If Selection.Areas.Count <> 2 Then
MsgBox "Select 2 cell ranges (only) to swap."
Exit Sub
End If
If Selection.Areas(1).Count <> Selection.Areas(2).Count Then
MsgBox "The two areas must be of equal size"
Exit Sub
End If
'With this for loop we run through each cell 1 by 1
For i = 1 To Selection.Areas(1).Count
'Swapping values
temp = Selection.Areas(1)(i)
Selection.Areas(1)(i) = Selection.Areas(2)(i)
Selection.Areas(2)(i) = temp
'Swapping color
tempColor = Selection.Areas(1)(i).DisplayFormat.Interior.Color
Selection.Areas(1)(i).Interior.Color = Selection.Areas(2)(i).DisplayFormat.Interior.Color
Selection.Areas(2)(i).Interior.Color = tempColor
Next i
End Sub
answered Jan 8 at 3:00
Ivo van der Marel
1
add a comment |
up vote
0
down vote
I read this post but actually needed a macro to swap full ranges. In addition, I needed to swap the colors. Modded the originally posted macro slightly, this might be useful for someone.
Sub Swap()
If Selection.Areas.Count <> 2 Then
MsgBox "Select 2 cell ranges (only) to swap."
Exit Sub
End If
If Selection.Areas(1).Count <> Selection.Areas(2).Count Then
MsgBox "The two areas must be of equal size"
Exit Sub
End If
'With this for loop we run through each cell 1 by 1
For i = 1 To Selection.Areas(1).Count
'Swapping values
temp = Selection.Areas(1)(i)
Selection.Areas(1)(i) = Selection.Areas(2)(i)
Selection.Areas(2)(i) = temp
'Swapping color
tempColor = Selection.Areas(1)(i).DisplayFormat.Interior.Color
Selection.Areas(1)(i).Interior.Color = Selection.Areas(2)(i).DisplayFormat.Interior.Color
Selection.Areas(2)(i).Interior.Color = tempColor
Next i
End Sub
answered Jan 8 at 3:00
Ivo van der Marel
1
add a comment |
up vote
0
down vote
up vote
0
down vote
I read this post but actually needed a macro to swap full ranges. In addition, I needed to swap the colors. Modded the originally posted macro slightly, this might be useful for someone.
Sub Swap()
If Selection.Areas.Count <> 2 Then
MsgBox "Select 2 cell ranges (only) to swap."
Exit Sub
End If
If Selection.Areas(1).Count <> Selection.Areas(2).Count Then
MsgBox "The two areas must be of equal size"
Exit Sub
End If
'With this for loop we run through each cell 1 by 1
For i = 1 To Selection.Areas(1).Count
'Swapping values
temp = Selection.Areas(1)(i)
Selection.Areas(1)(i) = Selection.Areas(2)(i)
Selection.Areas(2)(i) = temp
'Swapping color
tempColor = Selection.Areas(1)(i).DisplayFormat.Interior.Color
Selection.Areas(1)(i).Interior.Color = Selection.Areas(2)(i).DisplayFormat.Interior.Color
Selection.Areas(2)(i).Interior.Color = tempColor
Next i
End Sub
answered Jan 8 at 3:00
Ivo van der Marel
1
I read this post but actually needed a macro to swap full ranges. In addition, I needed to swap the colors. Modded the originally posted macro slightly, this might be useful for someone.
Sub Swap()
If Selection.Areas.Count <> 2 Then
MsgBox "Select 2 cell ranges (only) to swap."
Exit Sub
End If
If Selection.Areas(1).Count <> Selection.Areas(2).Count Then
MsgBox "The two areas must be of equal size"
Exit Sub
End If
'With this for loop we run through each cell 1 by 1
For i = 1 To Selection.Areas(1).Count
'Swapping values
temp = Selection.Areas(1)(i)
Selection.Areas(1)(i) = Selection.Areas(2)(i)
Selection.Areas(2)(i) = temp
'Swapping color
tempColor = Selection.Areas(1)(i).DisplayFormat.Interior.Color
Selection.Areas(1)(i).Interior.Color = Selection.Areas(2)(i).DisplayFormat.Interior.Color
Selection.Areas(2)(i).Interior.Color = tempColor
Next i
End Sub
answered Jan 8 at 3:00
Ivo van der Marel
1
answered Jan 8 at 3:00
Ivo van der Marel
1
answered Jan 8 at 3:00
Ivo van der Marel
1
answered Jan 8 at 3:00
Ivo van der Marel
1
1
add a comment |
add a comment |
up vote
0
down vote
Select the bottom cell you want to swap
Press Ctrl + x
and go to cell you want to swap with
Press Ctrl + Shift + =
the swap will be executed
answered Nov 22 at 4:53
mehndiratta co
1
This is exactly the same as Jonas Heidelberg's answer, from (exactly!) six years ago, and effectively the same as user287020's answer.
– Scott
Nov 22 at 6:25
add a comment |
up vote
0
down vote
Select the bottom cell you want to swap
Press Ctrl + x
and go to cell you want to swap with
Press Ctrl + Shift + =
the swap will be executed
answered Nov 22 at 4:53
mehndiratta co
1
This is exactly the same as Jonas Heidelberg's answer, from (exactly!) six years ago, and effectively the same as user287020's answer.
– Scott
Nov 22 at 6:25
add a comment |
up vote
0
down vote
up vote
0
down vote
Select the bottom cell you want to swap
Press Ctrl + x
and go to cell you want to swap with
Press Ctrl + Shift + =
the swap will be executed
answered Nov 22 at 4:53
mehndiratta co
1
Select the bottom cell you want to swap
Press Ctrl + x
and go to cell you want to swap with
Press Ctrl + Shift + =
the swap will be executed
answered Nov 22 at 4:53
mehndiratta co
1
answered Nov 22 at 4:53
mehndiratta co
1
answered Nov 22 at 4:53
mehndiratta co
1
answered Nov 22 at 4:53
mehndiratta co
1
1
This is exactly the same as Jonas Heidelberg's answer, from (exactly!) six years ago, and effectively the same as user287020's answer.
– Scott
Nov 22 at 6:25
add a comment |
This is exactly the same as Jonas Heidelberg's answer, from (exactly!) six years ago, and effectively the same as user287020's answer.
– Scott
Nov 22 at 6:25
This is exactly the same as Jonas Heidelberg's answer, from (exactly!) six years ago, and effectively the same as user287020's answer.
– Scott
Nov 22 at 6:25
This is exactly the same as Jonas Heidelberg's answer, from (exactly!) six years ago, and effectively the same as user287020's answer.
– Scott
Nov 22 at 6:25
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f257516%2fswap-cell-contents-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
This seems like a very specific implementation of a sort, and if its not already present in the advanced sorting options, I'd be surprised if you can find it elsewhere, as a non-VBA solution
– drapkin11
Mar 14 '11 at 16:29