Compare two columns with Go To Special

In the figure below, say that you want to find any changes between column A and column D.

There are lists of products in A and D. Select A2:A9. Ctrl+Select D2:D9. Open Home, Find & Select, Go To Special. Choose Row Differences. OK.

Select the data in A2:A9 and then hold down the Ctrl key while you select the data in D2:D9.

Select, Home, Find & Select, Go To Special. Then, in the Go To Special dialog, choose Row Differences. Click OK.

Only the items in column A that do not match the items in column D are selected. Use a red font to mark these items, as shown below.

After choosing Row Differences, any items in column A that do not match column D are highlighted. Apply a red font so you can identify which have changed.

Caution

This technique works only for lists that are mostly identical. If you insert one new row near the top of the second list, causing all future rows to be offset by one row, each of those rows is marked as a row difference

Comments

Protect Formula Cells in Excel

The use of worksheet protection in Excel is a little strange. Using the steps below, you can quickly protect just the formula cells in your worksheet. It seems unusual, but all 16 billion cells on a worksheet start out with their Locked property set to True. You need to unlock all of the cells first: Select all cells by using the icon above and to the left of cell A1. Press Ctrl+1 (that is the number 1) to open the Format Cells dialog. In the Format Cells dialog, go to the Protection tab. Uncheck Locked. Click OK. While all cells are still selected, select Home, Find & Select, Formulas. At this point, only the formula cells are selected. Press Ctrl+1 again to display the Format Cells dialog. On the Protection tab, choose Locked to lock all of the formula cells. Locking cells does nothing until you protect the worksheet. On the Review tab, choose Protect Sheet. In the Protect Sheet dialog, choose if you want people to be able to select your formula cells or no...

Data Analysis Tool Pack

The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis. To load the Analysis ToolPak add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Analysis ToolPak and click on the Go button. 3. Check Analysis ToolPak and click on OK. 4. On the Data tab, in the Analysis group, you can now click on Data Analysis . The following dialog box below appears. 5. For example, select Histogram and click OK to create a Histogram in Excel. Example Rank and Percentile The Rank and Percentile contained within the Analysis-ToolPak can be quickly used to find the rank of all the values in a list. The advantage of using the Rank and Percentile feature is that the percentile is also added to the output table. The percentile is a percentage that indicates the proportion of the list which is below a given number. Highlight the list (or the cells) which...

Charts - Make your data presentable

One-click charts are easy: Select the data and press Alt+F1 . What if you would rather create bar charts instead of the default clustered column chart? To make your life easier, you can change the default chart type. Store your favorite chart settings in a template and then teach Excel to produce your favorite chart in response to Alt+F1 . Say that you want to clean up the chart above. All of those zeros on the left axis take up a lot of space without adding value. Double-click those numbers and change Display Units from None to Millions. To move the legend to the top, click the + sign next to the chart, choose the arrow to the right of Legend, and choose Top. Change the color scheme to something that works with your company colors. Right-click the chart and choose Save As Template. Then, give the template a name. (I called mine ClusteredColumn.) Select a chart. In the Design tab of the Ribbon, choose Change Chart Type. Click on the Templates folder to see the template that ...

Solutions for Data Science

Search This Blog