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

 


Create a List Add to top of List Add all to top Add to bottom Edit List Comment Add a Checklist Add list Counter Import/Add many Export List Formatting Modify a List Delete a List
Ascending Descending No Sort
Add ---------- Show list only Show everything Show tags Hide tags
Nav Overview Top Up Show BreadCrumbs Shortcuts Subheader Random Search Last 100 items you added Last 100 modified Public Lists Popular Lists
Create/View to-do List Upcoming Deadlines Add a deadline Time balance List Daily Top 50
How to View Shortcuts Add a Shortcut System shortcuts Saving time with Tags Date/time Time Tracking Shortener AutoHotKeys
Time is your most valuable resource k Link Track Task from List Name Track via shortcut To-do list and time tracking Timer-Creator.com Time Tracking Spreadsheet based Time Tracking Set up @Time Tracking list Reports on how you spent your time Today Popup Current task Window
Random select Shuffle list Random Book Title How to use the Random Tag Random Domain Name Random Book Title Random List Item
To-Do List Add to to-do Set Goals Balance your time Track your time Create User defined header Create Top 10 List Ideas Habits Questions Creativity Accomplished Problems Learn Health Misc Lists
Work to-do Work Terms Work Accomplishments Work 1 Pagers Weekly Status Reports Work Checklists Work Rules Work test
Weight Loss Terms Accomplishments 1 Pagers Health Weekly Status Reports Checklists Rules
-->

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.