Excel

Excel Tips

Creating Macros [Microsoft]

  • Enable Developer tab
  • Record Macro
    • Name it
    • Assign a shortcut key
    • Save macro in the personal workbook [C:Users<username>AppDataRoamingMicrosoftExcelXLSTART]
    • Give it a description
  • Add it to the toolbar
    • Give it a custom name
    • Give it a custom icon.

Opening Files in Separate Windows [Microsoft]

  • Open Up Regedit
    • Create a backup
    • Browse “HKEY_CLASSES_ROOTExcel.Sheet.8shellOpen”
      • Rename folder “ddeexec” to “ddeexec_”
      • Select the “command” folder
      • Rename the “command” string from “xb’BV6!!!!4!!!!MKKSkEXCELFiles>u2BeT,8X[=]9==,*)Nn] /dde” to “xb’BV6!!!!4!!!!MKKSkEXCELFiles>u2BeT,8X[=]9==,*)Nn] “%1″”
      • When updates occur, both strings will be present, delete the “/dde” string.
    • Do the same thing for
      • Browse “HKEY_CLASSES_ROOTExcel.Sheet.12shellOpen”
      • Browse “HKEY_CLASSES_ROOTExcel.Sheet.CSVshellOpen”

VLookup [Link]

  • VLookup can find a value in the left most column of the range and return information to the right of it.
  • Enter your “Code” and “Value”.
  • Select all of the “Codes” and “Values” > right click > select “Define Name…”
  • Enter the Name of this array in the “Name” field.
    • Can’t have a space in it.
  • Select the first cell you want to have the “Value” inserted.
  • Select the “Fx” function key and search for vlookup.
  • Fill out the following in the Function Arguments box.
    • Lookup_value=The value you will be typing.
    • Table_array=The name of the array you created
    • Col_index_num=The column number that you want to return.
      • If the table has 1 column, enter “1”
    • Range_lookup
      • Enter “True” to be the closet match
      • Enter “False” to be an exact m

Converting numbers to a negative

  • Add -1 to a cell and copy to the clipboard.
  • Select the negative numbers you want to convert.
  • Use Paste Special > Values + Multiply.

Combining Two Columns into One

  • =(D2&” “&D3)
  • =(A2&” “&B2)
  • =CONCATENATE(A2,B2,C2)

Aligning Values in two Different Columns [Link]

  • insert a blank column into column B
  • in B1 put =IF(ISNA(MATCH(A1,C:C,0)),””,INDEX(C:C,MATCH(A1,C:C,0))) and copy down
  • copy and paste back column B over itself as values to remove the formula

Conditionally format a Row based on one cell containing text [Link]

  • Select all of the rows and columns that will be formatted.
  • Select Conditional Formatting > New Rule
    • Rule Type=Use a formula to determine which cells to format
    • Format values where this formula is true=”=$G1130=”X”” [shouldn’t be formatted as “$G$1130”
    • Format as needed

Consolidating Multiple Rows into One Row [Link]

  • Unconsolidated Data
    • Data must have headers
    • All columns in the “Name” field will be included.
    • The left-hand column will be the consolidated column.
    • Select all cells [including headers] and define a “Name”
    • Sort the columns in the same way you want them displayed on the consolidated report
  • Consolidated Data
    • Select the cell to place the consolidated list
    • Menu: Data > Consolidate
      • Function: Choose [Sum is a good default]
      • Reference: Enter your “Name”, then select the “Add” button.
      • Select “Left column”

Formulas

Changing the Case of Text

  • Insert a temporary column net to the column you are changing.
  • Insert this formula based
    • Proper [=PROPER(A2)]
    • Lower [=UPPER(A2)]
    • Upper [=LOWER(A2)]

Checking a Cell for Lower Case Text

  • Insert a column
  • Insert this formula
    • Formula [=EXACT(B2,UPPER(B2))]
  • Copy and Paste for every other cell.

Trim the last digit off a number [Link]

  • Insert a column
  • Insert this formula
    • =LEFT(A1, LEN(A1)-1)
  • Copy and Paste for every other cell.

Verifying if two cells are the same

  • “=A2=B2”

Verifying if three cells are the same

  • “=COUNTIF(A2:C2,A2)=3
  • “=COUNTIF(A197:B197:C197:D197,A197)=4”

XLook Up

  • “=XLOOKUP(lookup_value,lookup_arrow,return_arrow,[if_not_found],[match_mode],[search_mode]]