Excel Tips And Tricks

Optimizing Batch / Bulk Tasks:


How to Trim or Remove Text in Excel (in Bulk)

  • RIGHT(text, [num_chars]) –  text = cell you want to modify, num_chars = numbers from the right you want to display
  • LEFT(text, [num_chars]) –  text = cell you want to modify, num_chars = numbers from the left you want to display
  • LEN(text) –  counts number of letters in cell

Combine these 3 functions to excel to bulk remove prefixes/suffixes, or any string of text, in excel

More Examples:

  • =LEFT(S7,FIND(“El Cajon”,S7)-1)  –  To find “El Cajon” in cell S7, and give me only the text to the left of “El Cajon”. Note “-1” at the end to account for getting rid of the space)

More references:
Extract Text from cells in Excel – How to get any word from a cell in Excel

To pull out just the address out of “6618 Bridgegate Dr Spring, Tx.” for example:

=LEFT(A2,FIND(” “,A2,1+FIND(” “,A2,1+FIND(” “,A2)))-1)

Rename Multiple Files Using Excel or Google Docs

  • Ctrl+A + F2 (Will rename all files to first file name + (number))

How to Copy Formula Without Changing Its Cell References In Excel

  • Find and replace the “=” sign with “#”
  • So “=A1*A2” becomes “#A1*A2”, for example
  • Copy and paste your formula as necessary, and at end, copy and replace in reverse to restore “=” sign

Filter / Sort / Modify / Find Cells That Contain CAPS

  • =EXACT(A1,UPPER(A1)) (Modify “A1” accordingly)
  • Note: EXACT function compares two strings to determine whether they are equal. If both values are the same, it returns TRUE. Otherwise it returns FALSE.
  • Note: UPPER function converts all letters in a cell to UPPERCASE.
  • Note: LOWER function converts all letters in a cell to lowercase.

How To Merge Rows in a Column Into One Cell in Excel

Split Multiple Words in One Cell Into Separate Columns: Reference 1, Reference 2

  • Solution: Use “Text to Columns” feature

FORMULAS

ERRORS

EXCEL FORMULA ERRORS EXPLAINED

#N/A Error for MATCH LOOKUP Explained

  • Lookup values are Text, and the table contains Numbers

    If the lookup array contains numbers, and the value to look up is text, use a formula similar to the following:

    =MATCH(A5,ItemList,0)

    The double unary (–) converts text to a number, and will work correctly even if the lookup values are numbers.

Turn a String Formula Into a “Real” Formula

CLEANING UP DATA

Deleting Empty Rows

  • Select column in which blank values you’d like to delete the rows (If you do whole selection, you run the risk of deleting rows with pertinent data, just because one of the columns was blank
  • Press F5 and select “Special”
    • Select “Blanks” and click OK
  • Go to Home tab and underneath “Cells” region, click “Delete” > “Delete Sheet Rows”

 

DEFINING VARIABLES

Name A Dynamic Range in Excel

 

DUPLICATES

Excel – Finding Duplicates Across Sheets

Delete Duplicate Cells in Excel Across Tabs

How To Extract A Unique Distinct List From a Column in Excel

Categorize Each String under Group (1 Time), Based on String Content? – Thread 1, Thread 2, Thread 3

 

VBA

VBA Tips and Tricks (Resources)