How to Join Microsoft Office Insider

By joining Microsoft Office Insider, you have the opportunity to get exclusive access to new features (like the new XLOOKUP function) in addition to the ability to send feedback directly to Microsoft.

Navigate to File -> Account and click on the “Office Insider” button located on this panel. Follow the prompts from there and you’re in!

Microsoft Office insider

Once you’ve joined you’ll be prompted to download new updates and features when available.

How to Compare Two Lists in Excel

When analyzing data you often have to compare two lists to see if

  • They both contain the same data
  • You’re missing data from one of the lists
  • There are any duplicates or misspellings
  • etc..

If you’re looking to compare two lists for equality, you can use simple comparison operators.

compare lists excel

By turning column C into a helper column, we can compare the lists in A and B by simply typing

  1. =A2=B2
    

and auto-filling that formula down the column. As you can see this works for text, numbers, and dates the same way.

If you need to check to see if certain values exist or not in the other list, I’d suggest using ISNUMBER and MATCH together to find your answers.

Let’s separate the two columns for a second and add some additional values to List #2:

compare lists excel_2

We’re going to do a check to see which values in List 2 are in List 1.

We’ll create a helper column in Column D and first use the MATCH function to return the ROW that a match exists on, if any.

compare lists excel_3

  1. =MATCH(C2,A:A,0)

Excel searches for each of the values in column C inside of column A. The numbers returned are the ROW in column A where a match was found, while #N/A simply means there was no match found.

By wrapping this function in the ISNUMBER function, we can simplify this to only return TRUE (if a row number is found) or FALSE (error, no match).

compare lists excel_4

  1. =ISNUMBER(MATCH(C2,A:A,0))

We can now see exactly which values exist in both List 1 and List 2.

How to use Paste Special in Excel

Paste special is a very good tool to have for a variety of reasons. Let’s cover the most common use cases:

Paste Values

download

Here we have a table with days worked at Company X, sales per employee, and a calculated field with Sales per day. Column D is calculated with an easy formula of dividing Column C by Column B. Easy, right? But what if our table has thousands of rows with formulas far more complicated?

By leaving in formulas that have already yielded their desired result, we risk slowing down Excel. Also, if you want to paste these values into another sheet or workbook, Excel will still try to calculate “=C2/B2” on the new workbook, when you only want to paste the result. Paste Values to the rescue.

Steps:

  • Select Column D.
  • Press “control + c” to copy

paste-special2

  • Select the top cell you’re going to replace (D2 in our case)

paste-special3

  • Control + Alt + V to bring up Paste Special

paste-special4

  • Press “V” on your keyboard to select the Values radio button.
  • Click OK

paste-special5

As you can see, the cells no longer have a formula in them, but only the resulting Value of that formula.

Paste Special – Transpose

Transpose is used when you want to flip data on its axis.

paste-special6

Let’s say you wanted to take the headers from row 1 above and list them in a new column.

paste-special7

In the picture above, I did the following:

  • Copy all of row 1
  • Click cell I1
  • Paste special
  • Transpose

Everything is now flipped vertically.

But that’s not all. Transpose can also flip multiple rows into columns and vice versa.

paste-special8

In the image above I’ve selected 5 rows, copied them, and transposed it on H1. The use cases for Transpose are often very specific and it may be a feature that you do not use as often as some of the others, but when you really need it, it’s a fantastic tool.