Assume numbers from 1 to 8 in range B3:B10 and numbers from 9 to 16 in range C3:C10. The objective is to create all possible combinations in column E from these two ranges without using VBA (macros). This combined range of all possible combinations is called a Cartesian product.
You may refer to the following steps to create all possible combinations in column E
1. Type a heading in cell B2, say Data Set1. Format cell B2 to Bold (Ctrl+B).
2. Type a heading in cell C2, say Data Set2. Format cell C2 to Bold (Ctrl+B).
3. Select range B2:B10, press Ctrl+F3 > New and in the name box, type Data_set1
4. Select range C2:C10, press Ctrl+F3 > New and in the name box, type Data_set2
5. Select range B2:B10 and press Ctrl+T > OK
6. Select range C2:C10 and press Ctrl+T > OK
7. Save the file at your desired location, say on your desktop
8. Select cell E2 and go to Data > From Other Sources > From Microsoft Query > Excel Files > OK
9. In the Select Workbook dialog box, navigate to the folder and select the file which you just saved on the desktop (in step 7 above) and click on OK
10. With Data_set1 selected, click on the > symbol
11. Select Data_set2 and click on the > symbol
12. Click on Next > OK
13. Go to File > Return Data to Microsoft Excel
14. In the Import Data box, select cell E2 in the Existing Worksheet box and click on OK. Just in case, you do not see the Import Data box, after step 12, press Alt+Tab.
15. You should now see your desired result in range E2:F66.
Furthermore, since you had converted the ranges in column B and column C to Tables individually, if you add additional rows of data to column B and/or column C, just refresh while you are clicked on any cell of the result range. This will update the table in the result area.