Microsoft Excel: Learning The Basics And Putting Them To Use

Microsoft Excel: Learning theBasics and Putting them to UsePresented by: Karen Osborne, CPA, MBALacher McDonald & Co., CPA’sCourtesy of: Live Oak Bankkosborne@lachercpa.com1.888.884.1506

Spreadsheet Software DefinedAn electronic spreadsheet is an application youcan use to perform numeric calculations and toanalyze and present numeric data

SpreadsheetColumnsCell B4Rows

Definitions Cell – a block formed bythe intersection ofcolumn and rowgridlines displayed in aworksheet. Cell Reference –identifies the location ofa cell in the worksheet. Cell Pointer – A heavyoutline that indicateswhich cell in theworksheet is selected.Cell ReferenceCellCell Pointer

Definitions ‐ continuedCell Range – a grouping ofcells that are right next toeach other.Formula – a set ofinstructions that thespreadsheet uses toproduce a result.Example: “ 2 2”

Spreadsheet Uses CalculationsOrganize dataAnalyze dataGenerate reportsRepresent datagraphically

To open Microsoft Excel Double‐click with the From the ‘StartMenu’ as follows:left mouse button onthe Excel icon– ‘Start’ or WindowsExcel 2007Excel 2010Excel 2013button– ‘All Programs’– ‘Microsoft Office’– ‘Microsoft Excel’

Excel Window

Excel Window ‐ continuedFile tabQuick Access ToolbarTitle barThe Ribbon

Excel Window ‐ continued

Excel Window ‐ continued

FormulasA formula can consist of any of the elements: Mathematical Operators: for addition‐ for subtraction* for multiplication/ for division Cell references (such as Cell B3 or H2) Values or text Functions (such as SUM or AVERAGE)

Cut, Copy & Paste – Keyboard ShortcutsKeyboard shortcuts: Cut Press and hold ‘Ctrl’ and press the letter X Copy Press and hold ‘Ctrl’ and press the letter C Paste Press and hold ‘Ctrl’ and press the letter V

Relative vs. Absolute Cell ReferencesRelative Cell Reference Relative cell references canchange when you copy theformula to another cellbecause the references arelocations in relation to thecurrent row and column. Example B6‐(B7 B8)Absolute Cell Reference An absolute cell referencedoes not allow the rowand/or column referencesto change when you copythe formula because thereference is to an ACTUALcell address. Example B6/ B 6Using the ‘ ’ is what changes the cell reference fromrelative to absolute. No matter where you move thisformula the denominator will always be cell B6.

Absolute Cell References An absolute cell reference consists ofthe column letter and row numbersurrounded by dollar signs ( )Examples: B 32 A 326A 36 B15– The ( ) is just a random symbol used in Excel tochange a relative cell reference to an absolute cellreference. Shortcut ‐ An easy way to add the dollar signsto a cell reference is to click on a cell referenceand then press the F4 key on the keyboard.

Useful FunctionsSUM – totals a range of cellsMAX – provides the maximum number in a range of cellsAVERAGE – calculates the average of a range of cellsIF – tells Excel to perform an action if a certain conditionis met SQRT – calculates the square root of a number ROUND – will automatically round a value to whateverdigit you wish TODAY – will return the current date into the cell

Microsoft Excel: Learning theBasics and Putting them to UsePresented by: Karen Osborne, CPA, MBALacher McDonald & Co., CPA’sCourtesy of: Live Oak Bankkosborne@lachercpa.com1.888.884.1506

Cut, Copy & Paste –Keyboard Shortcuts Keyboard shortcuts: Cut Press and hold ‘Ctrl’ and press the letter X Copy Press and hold ‘Ctrl’ and press the letter C Paste Press and hold ‘Ctrl’ and press the letter V