Create Worksheet with Common Values from Two Other Sheets

Creating a worksheet with common values from two other sheets can be a useful task when you have multiple sheets with related data and you want to find the common values between them. In this article, we will guide you through the steps to create a worksheet with common values from two other sheets in a simple and easy-to-understand way.

Step 1: Open your Excel workbook

Start by opening the Excel workbook that contains the two sheets you want to compare and find the common values from.

Step 2: Create a new worksheet

To create a new worksheet that will contain the common values, right-click on any existing worksheet tab at the bottom of the Excel window. From the context menu, select "Insert" and then choose "Worksheet".

Step 3: Rename the new worksheet

Once the new worksheet is created, it will be named "SheetX" where X is a number. Right-click on the new worksheet tab and select "Rename" from the context menu. Give it a meaningful name, such as "Common Values", to easily identify its purpose.

Step 4: Select the range for the common values

Now, go to the first sheet that you want to compare and find the common values from. Select the range of cells that contains the data you want to compare. You can do this by clicking and dragging the mouse over the desired range of cells.

Step 5: Copy the selected range

With the range of cells selected, right-click on the selection and choose "Copy" from the context menu. Alternatively, you can use the keyboard shortcut "Ctrl+C" to copy the selected range.

Step 6: Paste the copied range into the new worksheet

Switch to the newly created "Common Values" worksheet. Click on the first cell where you want to paste the copied range. Right-click on the cell and select "Paste" from the context menu. Alternatively, you can use the keyboard shortcut "Ctrl+V" to paste the copied range.

Step 7: Repeat steps 4-6 for the second sheet

Go to the second sheet that you want to compare and find the common values from. Repeat steps 4-6 to select the range of cells, copy the selected range, and paste it into the "Common Values" worksheet. Make sure to paste it below the previously pasted data to keep everything organized.

Step 8: Remove duplicate values

Now that you have the data from both sheets in the "Common Values" worksheet, you may have duplicate values. To remove these duplicates and keep only the common values, select the entire range of cells in the "Common Values" worksheet. You can do this by clicking on the top-left cell and dragging the mouse to the bottom-right cell.

With the range selected, go to the "Data" tab in the Excel ribbon. Click on the "Remove Duplicates" button in the "Data Tools" group. In the "Remove Duplicates" dialog box, make sure the checkbox next to the column(s) you want to check for duplicates is selected. Click "OK" to remove the duplicates and keep only the common values.

Step 9: Format the worksheet (optional)

Once you have the common values in the "Common Values" worksheet, you can format it to make it easier to read and understand. You can apply different formatting options, such as changing the font style, size, or color, adding borders, or applying conditional formatting to highlight specific values.

Step 10: Save your workbook

Finally, don't forget to save your workbook to keep the changes you made. You can do this by clicking on the "File" tab in the Excel ribbon and selecting "Save" or using the keyboard shortcut "Ctrl+S". Choose a location on your computer to save the file, give it a name, and click "Save".

That's it! You have successfully created a worksheet with common values from two other sheets. Now you can easily compare and find the common values between different sheets in your Excel workbook.

References