parm530
5/21/2018 - 1:37 PM

EXCEL Basics

Intro to using excel

excel.md
content_copyfile_download
  • Rendered
  • Source

What is Excel?

  • Spreadsheet package
  • Workbook and file mean the same thing
    • A worksheet is composed of columns and row
    • Worksheet = Individual excel file in the workbook (or file)

Tips (MAC OS)

  • Text will be aligned to the left
  • Numbers will be aligned to the right
  • Move to the top: cmd + up arrow
  • Move to the bottom: cmd + down arrow
  • Font dialogue box: cmd + 1
  • Formatting phone numbers, or SS#: highlight the column, use cmd + 1 -> Number -> Special
  • Inserting a column, go to the column you want to insert it to the left of
  • Control drag: highlight the fields you want to copy, hover over the edge for the hand icon and then press option. The icon should have a little plus, drag it over to a new destination and release.
  • Removing borders: cmd + shift + _
  • Moving columns and rows
    • Select the data and then cut and paste
    • Select the data, go to any edge and drag the area down
  • Copy data:
    • Select the data and then press cmd as you drag the data
  • Move data:
    • Select the data, then hold shift and drag the data to the new area
  • Select across columns and then double click the bottom edge of the selection to select all the data to the bottom of the sheet

Formulas

  • To begin a formula in excel type = in the cell where you need the value computed!
    • When you click on the cell, you will see a formula, so that that cell nevers needs to be be updated, it will automatically be updated when you change the affected cells: =b2-b4
  • Sum up across many rows: =sum( then drag mouse across the rows + ) then enter!
  • To copy a formula into adjacent columns (instead of rewriting them over and over), just place the cursor on the bottom right until it changes icon and then drag the icon over the rows.
  • Excel follows the order of operations PEMDAS!
  • To use a certain cell in all operations: insert a $ before each char: $B$4 to use cell B4 , this means reference!
  • Auto Sum: located in formulas in the ribbon
    • Adds values from the above (default) or the left

Tables

  • Slicers: can be used only with tables!
    • Create small windows that contain the filter you applied

IF

  • Applies a condition based on the value of the data
  • click on a cell to apply the formula to:
    • =if(F7>=1500, 0, round(2%*F7,2))
    • first arg = condition
    • sec arg = value if true
    • third arg = value if false

VLOOKUP

  • Vertical Lookup
  • Alternative HLOOKUP (horizontal)
  • Looks up values in a different worksheet or workbook
  • click on a cell
    • =VLOOKUP(arg1, arg2, arg3, arg4)
    • arg1 = the value you want to find in another table
    • arg2 = the table that arg1 might be in, so select the entire table
    • arg3 = the index of the column in the table of arg2 (column index starts at 1)
    • arg4 = is usually FALSE (find an exact match), or TRUE to find an approximate match
    • NEED TO MAKE THE REFERENCE IN ARG2 ABSOLUTE BY ADDING $ IN FRONT OF THE CELL NAME = F4 key
      • This is cmd + T for mac!!!
    • The table used in arg2 MUST be sorted ascending order for the lookup to work properly!

COUNTIF