Please enable JavaScript.

Coggle requires JavaScript to display documents.

Excel intermediate II - Coggle Diagram

- - - - Select the column > Data > Data tools > Data validation
        
        Allow > List
        
        Source
        
        If the list is long or changes frequently
        
        Create/use a look up list : =List (F3 shortcut)
        
        Copy paste the column in a new sheet
        
        Remove duplicates
        
        1 more item...
        
        Change the heading
        
        Convert the list into a table (for automation)
        
        2 more items...
        
        Sort items of the list
        
        Else
        
        Type in the items manually separated by a comma
  - - - Make sure you convert the column into a named range first (automation)
  - - - Data > Data tools > Data validation > Clear validation
    - - Data > Data tools > Data validation drop down menu > Circle invalid data
    - - Home > Editing > Find & select > Go to special > Data validation
    - - Right click > Paste special > Paste special > Validation
  - - - Format the whole row
        
        Use mixed cell references in the formula
      - Format only the cell
        
        Use relative cell references in the formula
- - - - Home > Editing > Find & Select > Go to special > Formulas > Error
    - - Formulas > Formulas auditing > Error checking
    - - Formulas > Formulas auditing > Show formulas
    - - Formulas > Formulas auditing > Error checking drop down menu > Trace error
  - - - Formulas > Formula auditing > Error checking drop down menu > Circular references
    - - Right click exclamations > Copy fomula from above
    - - Formulas > Calculations > Calculations option drop down menu
  - - - Computes the formula step by step to identify where the error occurs
        
        Formulas > Formula auditing > Evaluate formula
    - - Allows you to see changes in another sheet's cell
        
        Formulas > Formula auditing > Watch window
- - - - IF
    - - AND
      - OR
  - - - Example : if NA return 0
    - - Example If error return ""
- - - - Vlookup (exact match)
      - Choose
        
        For finite lists that do not change frequently
    - - Index
        
        Dynamic look up