Excel tips I wish I knew sooner

1. Use the "$" to freeze rows and/or columns. Adding the dollar sign before the letter (column) freezes that column.

• $A1 changes the number but does not change the column. If you copy this formula anywhere in the sheet, it will change the number (row) to match the row you're in, but it will always stay at column A.
  

• A$1 changes the letter but does not change the row. If you copy this formula anywhere in the sheet, it will change the letter (column) to match the column you're in, but it will always stay at row 1.

2. Find the information associated with a variable with "vlookup()". Let's use the example in the green box. On the left column (column A) there are some adjetives and in the right column (column B) there are nouns associated with those. Say that in another location of the sheet or in another tab you want to know what noun is associated with the color black.

To find what is associated with "Black", use

=vlookup(what you're looking for,

range where the information is found,

column where your variable is contained,

if you're looking for a partial or exact match)

In our example, we want to find that "Black" is associated with "cat".

= vlookup(D3, A1:B5, 2, false)

• D3 is where the variable you want information about is found (in this case the word "Black").

• A1:B5 is the range of rows and columns where you're looking for what corresponds with "Black".

• The number "2" denotes the second column of the range noted above, where the information you're looking for is found. The word "cat" is in the second column.

• "False" denotes that we want to find exact matches to "Black".

3. Double click to populate downward. If you have a formula that you want to copy all the way down but you don't want to do it manually, you can double click on the bottom right corner of the cell where your formula is contained and it will automatically copy that formula all the way to the bottom.

• The distance that formula is copied depends on the number of rows in the adjacent column. In the example shown below, there are five rows, double-clicking the formula found in row 1 will copy down this formula until row 5 and not beyond this point.

4. Merge the contents of several cells with "&". When you have text and/or numbers separated into different columns and you want to merge them all into one column, click the cells that you want to merge separated by the "&".

• Between two cells there needs to be the "&".

• You can merge as many cells as you want.

• You can add text or spaces by using " " between the "&" symbol. See example below. If we used "&" only between A3 and B3 the resulting word would read "Threeblind". But by adding &" "& between A3 and B3 it adds a space between those two words.

5. Use "shift" + "end" + arrow

• to select the entire row (using the right/left arrow)

• to select the entire column (using the top/bottom arrow).