By using t2do.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

Excel 1 Pg Guide

edit | random
Functions
  • What is a Function?
  • Parameters
  • Types of functions
  • =If(cond,True,False)
  • =indirect()
  • =sum( )
  • =count( )
  • =counta( )
  • =mid(cell,start,len)
  • =vlookup(cell,range,column,0)
  • =match( )
  • =subtotal( )
  • =left(cell,# chars)
  • =right(cell,# chars)
  • =mid(cell,start,length)
  • =len(cell)
  • =text(now(),"ddd") - Gets day of week for passed date
  • =countBlank(B:B)
  • =And(Cond1, Cond2, ...Condn) - All must be True to be True otherwise False
  • =Or(Cond1, Cond2, ... Condn) - One must be True to be True otherwise False
  • =SumIfs(   )
  • =Substitute(      )
  • =Find(    )
  • =Search(    )
  • =ifna( ) - use with VLookup
  • User defined functions
  • =hyperlink(location,text to display)
Shortcuts
  • Alt - Show shortcuts
  • Hover
  • Quick Access Toolbar - Right click on a ribbon item and select Add to toolbar
  • Alt+I+R - Insert Row
  • Alt+I+C = Insert Column
  • Windows Shortcuts Ctrl+(cut X, copy C, paste V, undo Z, select all A)
  • F2 - Edit Cell
  • F4 - Toggle Addressing A1 -> $A$1 -> A$1 -> $A1 -> A1
  • Ctrl+F - Find
  • Ctrl+; = date
  • Ctrl+: - Time
  • Alt+D+F+F - Toggle Filter
  • Alt+W+F+F = Toggle Freeze panes
  • Ctrl+K - Hyperlink
  • Ctrl+5 - Toggle Strikethrough
  • Ctrl+PgUp - next sheet
  • Alt+Enter - New line while editing a cell
  • Ctrl+F3 - Name Range Dialog
  • Ctrl+F1 - Toggle Ribbon off/on
  • Shift+F11 - Add Sheet
  • Alt+F11 - Go to VBA window
  • Ctrl+Alt+L reapply filter
  • Alt+T,A - Autocorrect -  set up typing shortcuts
Cell References
  • A1 - Row/Column relative - copy and cell changes
  • A:A - Column A
  • A:D - Column A thru D
  • 2:2 - Row 2
  • $A$1 - Absolute reference - copy doesn't change
  • $A1 - Absolute Column - copy doesn't change column but row adjusts
  • A$1 - Absolute row - copy doesn't change row but column adjusts
  • A1:C20 - Range of cells
  • Named Range - Assign a name to a cell, range of cells, row or column = Ctrl+F3
Intermediate Excel Knowledge
    Advanced
    Special Characters
    • = - Formula
    • $ - Absolute Address
    • * - Multiply
    • / - Divide
    • ^ - Exponent
    • "....." - String
    • & - Concatenation
    • ( )  - precedence
    • ! - Sheet!A1
    • ' - Text field
    • 'sheet with space'!A1 - use single quote
    • '[c:\file.sfx]sheet name'!A1
    Formatting
    • Problems formatting solves
    • Conditional formatting
    • Font
    • Size
    • Text Color
    • Cell Background color
    • Borders
    • Bold/Italic/Underline - Ctrl+B, Ctrl+I, Ctrl+U
    • Aligning text (Wrap Text, Merge, Center
    • Formatting Numbers/Time
    • Format as Table - alternating row colors
    • Find duplicates
    • Decrease/Increase decimal
    Options
    • Formulas > Calculation Options - Also on Formula Ribbon - Calculation Group
    • Proofing > AutoCorrect Options... - Use to create shortcuts (ex. cpt changes to counterparty)
    • Advanced > Display Options for this wbk > Show sheet Tabs - Hides/shows them
    Dates and Times
    • =Now() - Get the current Date and time (changes when recalculated)
    • Ctrl+; (semicolon) - Get current date
    • Ctrl+: (colon) - Get the current time
    • Get the current date and time (doesn't change) Ctrl+; space Ctrl+:
    • =text(date,"mm/dd/yyyy") - different ways to format dates in a string
    • Number of days - subtract 2 cells with dates =A1-A2 (A1 should be after A2) format as a number
    • Day of week =text(date,"ddd")
    • Convert String to date = datevalue("1/2/2019")
    • Weekend - =weekday(date) = 1 to 7 (Sun. to Sat)
    • Holidays - create a holiday table and use vlookup
    • Get just the date =int(now())
    • Get the time only - =now() - int(Now())
    Fixing Problems
    • #N/A - Vlookup not found
    • #NAME - cell reference doesn't exist
    • #DIV/0 - formula is dividing by 0 use =if(a1=0,)
    • #REF! - referring to a cell that's been moved or row that was deleted
    • Step through formula calculation - Formulas > Formula Auditing > Evaluate Formula
    • Circular Reference - in cell A1 and the formula =A1
    • External Link
    • #VALUE! - the parameter to the function exceeded 255 characters or adding a string with a number
    • Data not recalculating - Check options to see that Calculations set to Automatic


    $0.99 for a limited time at Amazon.com
    Going Fast.