Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Swapping Two Numbers.
Written by Allen Wyatt (last updated May 2, 2026)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
If you do any serious macro programming, there will eventually come a time when you want to swap the values in two numeric variables. In some versions of BASIC, there are commands that handle this. VBA leaves you to our own devices, however. The following technique should do the trick for most people:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
TempNum = MyNum1
MyNum1 = MyNum2
MyNum2 = TempNum
When completed, the values in MyNum1 and MyNum2 have been swapped, and TempNum doesn't matter since it was intended (by this technique) as a temporary variable anyway.
If you want to get fancy and do the swapping without using the intermediate TempNum variable, you could use this series of commands:
MyNum1 = MyNum1 Xor MyNum2
MyNum2 = MyNum2 Xor MyNum1
MyNum1 = MyNum1 Xor MyNum2
This approach only works if MyNum1 and MyNum2 are whole numbers, meaning they are Integer or Long data types. (If you need to swap any other type of data, you need to use the previous approach in this tip.) It works because the Xor function basically "encodes" the difference between the values. So, successively applying Xor causes the variables to switch their values. (It is mind exploding to try to understand, but it does work.)
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9098) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Swapping Two Numbers.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Perhaps the most common way of communicating with programs is through the use of dialog boxes. We expect dialog boxes to ...
Discover MoreMacros allow you to perform all sorts of file-related operations. One such operation allows you to delete a directory. ...
Discover MoreOne of the powerful programming structures available in VBA is the Select Case structure. This tip explains how you can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-05-03 10:35:30
J. Woolley
This Tip pertains to swapping values between two VBA variables. You might also be interested in swapping two cells on a worksheet. See https://excelribbon.tips.net/T001664_Swapping_Two_Cells.html
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Comments