Similar presentations

2Objectives Understand the use of spreadsheets and ExcelLearn the parts of the Excel windowScroll through a worksheet and navigate between worksheetsCreate and save a workbook fileEnter, analyze, and present quantitative dataEnter text, numbers, and dates into a worksheetNew Perspectives on Microsoft Office 201372

6Introducing Microsoft Excel 2013Computer program used to enter, store, analyze, and present quantitative data in an organized and easily updatable mannerCreates electronic versions of spreadsheetsCollection of text and numbers laid out in a gridDisplays values calculated from dataRepresents data in both text and graphical from through the use of chartsNew Perspectives on Microsoft Office 2013

7Microsoft Excel 2013 (Continued)Allows what-if analysisAbility to change values in a spreadsheet and assess the effect they have on calculated values automaticallyAutomates many tasks through the use of formulas including both automatic formulas and custom formulasA Quick Analysis tool allowing conversion from data to charts in two easy stepsNew Perspectives on Microsoft Office 2013

8Microsoft Excel 2013 (Continued)Integrates data from several spreadsheets within a single workbook, and also interfaces with other workbooks when data from multiple sources is requiredTemplates already available for budgets, calendars, forms, and reportsA new Flash Fill feature allows you to have data automatically entered based on initial data you enterNew Perspectives on Microsoft Office 2013

12Worksheet Navigation A workbook can have two kinds of sheets:Worksheet contains a grid of rows and columns into which user enters dataChart sheet provides visual representation of dataCell reference identifies column/row locationNew Perspectives on Microsoft Office 2013

13Worksheet Navigation To navigate between worksheets Use sheet tabsTo navigate within a worksheetUse mouse, keyboard, GoTo dialog box, or type cell reference in Name boxNew Perspectives on Microsoft Office 2013

15Planning a Workbook Use a planning analysis sheet to define:Goal or purpose of workbookType of data to collectFormulas needed to apply to data you collected and enteredAppearance of workbook contentNew Perspectives on Microsoft Office 2013

16Creating a New Work BookYou create new workbooks from the New screen in Backstage view from the Blank workbook templateOn the ribbon, click the File tab to display Backstage view.Click New in the navigation bar to display the New screenClick the Blank workbook tile.New Perspectives on Microsoft Office 2013

17Working with WorksheetsInserting a worksheetName of new worksheet is based on number and names of other sheets in the workbookDeleting a worksheetRenaming a worksheet31 characters maximum, including blank spacesWidth of sheet tab adjusts to length of nameNew Perspectives on Microsoft Office 2013

18Working with WorksheetsMoving and copying a worksheetTo move:Click and dragTo copy:Ctrl + drag and dropPlace most important worksheets at beginning of workbook (leftmost sheet tabs), less important worksheets toward end (rightmost tabs)New Perspectives on Microsoft Office 2013

19Entering Text, Numbers, and DatesText dataCombination of letters, numbers, and symbolsOften referred to as a text stringNumber dataNumerical value to be used in a mathematical calculationDate and time dataCommonly recognized formats for date and time valuesNew Perspectives on Microsoft Office 2013

20Entering TextNew data appears in both the active cell and the formula barTruncationAutoComplete featureTo enter multiple lines of text within a cellCreate a line break with Alt + EnterNew Perspectives on Microsoft Office 2013

23Editing Cell ContentAs you work, you might find entries you need to change or correct.If you want to replace all of the content in a cell, you simply select the cell and then type the new entry to overwrite the previous entry.If you need to replace only part of a cell’s content, you can work in Edit mode.New Perspectives on Microsoft Office 2013

24Editing Cell Content in Edit ModeDouble-click the cell to select the cell and switch to Edit modeA blinking insertion point appears within the text of cellPress the arrow keys to move the insertion point to the right of the word being editedPress the Backspace key three times to delete the word being editedType & to enter the new text, and then press the Enter keyNew Perspectives on Microsoft Office 2013

25Editing Worksheet ContentUse Edit mode to edit cell contentsKeyboard shortcuts apply only to text within selected cellUndoing and redoing an actionExcel maintains a list of actions performed in a workbook during current sessionNew Perspectives on Microsoft Office 2013

28Working with Columns and RowsTo make data easier to read:Modify size of columns and rows in a worksheetTo modify size of columns or rows:Drag border to resizeDouble-click border to autofitFormat the Cells group to specifyNew Perspectives on Microsoft Office 2013

29Working with Columns and RowsColumn widthExpressed in terms of number of characters or pixels (8.43 characters equals 64 pixels)Note: Pixel size is based on screen resolutionRow heightMeasured in points (1/72 of an inch) or pixelsDefault row height: 15 points or 20 pixelsNew Perspectives on Microsoft Office 2013

30Working with Columns and RowsNew Perspectives on Microsoft Office 2013

31Working with Columns and RowsResizing columns and rows with AutoFitNew Perspectives on Microsoft Office 2013

32Working with Columns and RowsDeleting and clearing a row or columnDeleting removes both the data and the cellsClearing removes the data, leaving blank cells where data had beenNew Perspectives on Microsoft Office 2013

33Wrapping Text Within a CellWrapping text enables text to display on two or more rows within one cell allowing more text within a narrower column.Resize the width of column as appropriate.Select the cells you wish to apply wrapping toOn the Home tab, in the Alignment group, click the Wrap Text button.The Wrap Text button is toggled on, and text in the selected cells that exceeds the column width wraps to a new line.New Perspectives on Microsoft Office 2013

34Working with Cells and RangesRange reference indicates location and size of a cell rangeAdjacent (A1:G5)Nonadjacent (A1:A5;F1:G5)Selecting a rangeWork with all cells in the range as a groupMoving and copying a rangeDrag and dropCut and pasteNew Perspectives on Microsoft Office 2013

35Working with Cells and RangesInserting and deleting a rangeExisting cells shift to accommodate the changeNew Perspectives on Microsoft Office 2013

36Moving a cell or a Range of CellsSelect the cell or range you want to move or copyMove the pointer over the border of the selection until the pointer changes shapeTo move the range, click the border and drag the selection to a new locationOr to copy the range, hold down the Ctrl key and drag the selection to a new locationNew Perspectives on Microsoft Office 2013

37Copying a Cell or Range of CellsSelect the cell or range you want to move or copyOn the Home tab, click the Cut or Copy buttonOr right-click the selection, and then click Cut or Copy on the shortcut menuSelect the cell or the upper-left cell of the range where you want to paste the contentClick the Paste buttonNew Perspectives on Microsoft Office 2013

40Working with Formulas Formula An expression that returns a valueWritten using operators that combine different values, resulting in a single displayed valueNew Perspectives on Microsoft Office 2013

41Working with Formulas Entering a formulaClick cell where you want formula results to appearType = and an expression that calculates a value using cell references and arithmetic operatorsCell references allow you to change values used in the calculation without having to modify the formula itselfPress Enter or Tab to complete the formulaNew Perspectives on Microsoft Office 2013

42Working with Formulas Order of precedenceSet of predefined rules used to determine sequence in which operators are applied in a calculationNew Perspectives on Microsoft Office 2013

43Working with Formulas Viewing a formulaSelect cell and review expression displayed in the formula barEach cell reference is color coded in the formula and corresponding cell in the worksheetNew Perspectives on Microsoft Office 2013

44Working with Formulas Copying and pasting formulasCell references adjust to reflect new location of the formula in the worksheetNew Perspectives on Microsoft Office 2013

45Working with Formulas Guidelines for writing effective formulas:Keep them simpleDo not hide data values within formulasBreak up formulas to show intermediate resultsNew Perspectives on Microsoft Office 2013

46Introducing FunctionsNamed operation that returns a valueSimplifies a formula, reducing a long formula into a compact statement; for example, to add values in the range A1:A10:Enter the long formula: =A1+A2+A3+A4+A5+A6+A7+A8+A9+A or -Use the SUM function to accomplish the same thing: =SUM(A1:A10)New Perspectives on Microsoft Office 2013

47Entering Functions with AutoSumFast, convenient way to enter commonly used functionsIncludes buttons to quickly insert/generate:Sum of values in column or row (SUM)Average value in column or row (AVERAGE)Total count of numeric values in column or row (COUNT)Minimum value in column or row (MIN)Maximum value in column or row (MAX)New Perspectives on Microsoft Office 2013

52Previewing a Workbook Working with page orientationPortrait orientation (default)Page is taller than wideLandscape orientationPage is wider than tallNew Perspectives on Microsoft Office 2013

53Printing a WorkbookPrint tab provides options for choosing what to print and how to printPrintout includes only the data in the worksheetOther elements (e.g., row/column headings, gridlines) will not print by defaultGood practice: Review print preview before printing to ensure that printout looks exactly as you intended and avoid unnecessary reprintingNew Perspectives on Microsoft Office 2013

55Viewing and Printing Worksheet FormulasSwitch to formula viewUseful when you encounter unexpected results and want to examine underlying formulas or to discuss your formulas with a colleagueNew Perspectives on Microsoft Office 2013

56Viewing and Printing Worksheet FormulasScaling the printout of a worksheet forces contents to fit on a single pageNew Perspectives on Microsoft Office 2013