Excel Training

• to paste values copied from a cell with a calculation: Paste Special -> Values

• to add copied values to other cell values: Paste Special -> Values -> Add -> paste over top of other cell values

Calculations

• alt = : autofills with AutoSum (no need to select cells)

• ctrl enter : hits the enter key but doesn't move the cursor down a row

• double clicking AutoSum: autofills with AutoSum (no need to select cells)

• select all cells w/ an extra column and row, then select AutoSum: performs AutoSum on all rows and columns selected

• absolute reference

  • when copying formulas, Excel makes relative adjustments (adjusts row and column numbers

  • not helpful if you're trying to use a set value for multiple formulas (ex. a set tax rate)

  • you can create an absolute value by hitting F4 after inputting the value that you lock into place

    • A1 becomes $A$1

    • hitting F4 multiple times will cycle through reference types

      • $A1, A$1

      • $ indicates if the row or column is locked

• F5 -> Special -> Formulas highlights all cells with formulas

• ctrl ~ will show all formulas

Formatting

• select cells -> alt O A to autoformat selected cells

  • only visual formatting

  • Cell Styles allow for canned formatting with colors, borders, etc.

• select cells -> Insert -> Table to format as a table

  • formatting as a table allows for sorting

  • can then sum as a PivotTable

• Insert -> PivotTable is another way to create a PivotTable

• freeze panes allow for locking rows/columns in place

  • the default is left and above the clicked cell

  • View -> Freeze Panes

• split screen splits the sheet into 4 independent windows to scroll through

  • default is left and above again

  • can split only horizontally or vertically by selecting the leftmost or topmost cell

Sheet Management

• ctrl pgup/pgdn to move between sheets in the same file

• right-click on the arrows to move between sheets to see all sheets in the file

• select multiple sheets in a row by holding shift, select non-continuous tabs by holding ctrl

• select all sheets and type on one to get that text on all sheets

• to move a column between other columns, select the one you want to move, get the "move" icon, hold shift, and move to the vertical line between the columns where you want the selected column to be, and click (there should be a green line on the vertical line before you click)

• 3D formulas are formulas that go across several sheets

  • =sum(sheet1!A1)

    • allows you to grab a cell value from a sheet other than the one you're on

  • =sum(sheet1:sheet2!A1)

    • allows you to grab the A1 value from all sheets between and including sheet1 and sheet2

  • if the sum popup is annoying, you can move it by dragging it

Financial Functions

• payment

  • fx allows you to insert functions

    • drop down menu for categories, as well as a search bar

  • PMT

    • click cells that contain needed info to grab that info automatically

    • if your nper value is term/year, you need to have [cell]*12 for an accurate calculation

      • nper assumes that the value is the # of payments

  • Excel assumes a constant interest rate

• future value

  • FV

    • $ value must be - because you're putting down money

• select cells -> Home -> Clear Formats to remove formatting

Conditionals

• if statements

  • IF

    • click cell with the value to test, add conditional statement (ex. =100)

    • add in values for if the test returns true or false

      • string values must be encased in quotes (the function window will do it automatically)

    • =IF(statement,passvalue,failvalue)

Dates

• Excel stores dates as serial numbers to be able to do calculations with them

• ctrl ; inserts today's date

• clearing formatting on a cell that has a date allows you to see the serial number

• the serial number is the amount of days since 1/1/1900

• the today() function will insert the current day and update itself

• the now() function does the same but includes time

  • the serial number has decimals

• edate function

  • adds months to a date

  • =edate(start time, months to add)

  • negative values work for subtraction

  • can do multiplication *12 for years

• date function

  • allows for calculations too

  • date(years, months, day)

  • can do years + x, etc.

Sorting and Filtering

• sorting

  • click inside the column you want to sort by

  • Data -> Sort and Filter

  • can also right-click on a cell in the column

  • multi-level sort

    • Advanced Sorting

    • creates a hierarchy of sorting- sorts the second hierarchy within the first, etc.

  • non-alphabetical sorting

  • Sort -> Custom Sort -> Order -> Custom List

• filter

  • select a subset or criteria

    • make sure column headings are bold- it makes them fields and allows them to be filtered

  • can filter with and/or conditions

Extras

• can customize the bottom right status bar by right-clicking on it

• alt to see what keyboard shortcuts exist

• Windows Key + V shows Windows clipboard history

  • can access all copied material

• fill series - drag, copy, click bottom right icon, Fill Series, can select sequential numbers, dates, etc.

  • months, days, dates, and consecutive numbers automatically populate with the corner drag

• File -> Show All Properties allows you to add comments, etc. so the document becomes searchable in SharePoint

• Track Changes allows you to see other edits live on a shared doc

• File -> Info -> Inspect Workbook -> Inspect Document allows you to see document properties and personal info attached to the document and remove it

Word

• =rand(#1, #2) generates random text with #1 paragraphs and #2 sentences per paragraph

• ctrl enter for a new page

• References -> Table of Contents to insert an automatic table of contents