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 KnowledgeAdvanced | 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
|
