By using t2do.com you agree to our
cookie policy
, We and our partners operate globally and use cookies, for multiple purposes
Close
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
VBA Dashboard
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.
