MS Excel Session 5 Topics

  • Docx File 26.41KByte



Microsoft Excel: Session 5: Pivot Tables & CollaborationRich MalloySubtotalsReview: The Subtotal ToolThis is an old technique used to find totals or average for groups of data.Sort the data by the desired column(s) (e.g., Class and/or Category)Click Data > Subtotal.In the At each change in list box, choose the desired group column.Choose the desired function (e.g., Average)Select the field(s) to be calculated.Repeat steps 2-5 for an additional grouping, but be sure to uncheck Replace current subtotals.To remove the Subtotals, click Data > Subtotals > Remove AllExcel TablesReview: The Amazing “Excel Table”Also known as: Formatted Table. Not only do Excel Table have nice formats, they also have special capabilities for managing data.Click someplace inside the range of cells(or, select whole range).Click Home > Format as TableChoose the desired Table StyleVerify table range and click OKTo remove filter buttons, click Data > FilterAdd a Total Row to an Excel TableBy adding a Total Row at the bottom of an Excel Table, you can sum, average, or perform many other functions on your data.Click a cell in the Excel Table.Click: Table Tools > DesignCheck to box for: Total RowClick any cell in the Total RowChoose the desired function (e.g., Sum)To convert a Table back to a Range:This converts an Excel Table back to an ordinary range of cells, but preserves the formatting.Select a cell in the tableClick Table Tools Design > Convert to rangeClear formatting manually, if desired.Pivot TablesThe Amazing Pivot TableThis is a great way to compare groups and categories of data to see general trends. Note that in a Pivot Table, columns are called Fields.Click any cell within a table of data.Click Insert > PivotTableIn Create PivotTable dialog box, click OK.A new worksheet will appear with a blank PivotTable on left and list of fields (columns) on right. Drag the desired fields to either the Columns or Rows box.Drag a numeric field such as Salary to the Values box.Double-click the column header of the Values cells (e.g., “Sum of Salary”) to change the function from Sum to Average or Count or to change the Number Format.To delete a field from the table, click and drag the field-name from the Columns or Rows box and drop it on the spreadsheet grid.Click outside the PivotTable to hide the Field List on the right.Fix the Column and Row HeadersThe default column and row headers in a PivotTable are misleading. Here is a way to improve them:Click any place in the PivotTable.Click: PivotTable Tools > DesignClick: Report Layout > Show in Outline FormUpdate a Pivot TableNote: Unlike charts, Pivot Tables do not automatically update when their underlying data changes. You must remember to update a Pivot Table after you update the Pivot Table’s data. If you do not, the Table will show obsolete data.Rt-click any cell in the PivotTable.Choose RefreshThe Equally Amazing Pivot ChartA great way to display data in a PivotChart.Click any place in the PivotTable.Click: PivotTable Tools > AnalyzeClick: PivotChartChoose the desired chart format.Fix the PivotChart LabelsUse this technique to remove the Field Buttons on the PivotChart.Click the chart to select it.Click: PivotChart Tools > FormatClick the top half of the button: Field ButtonsNested FunctionsOften one function is placed inside or nested in another function.The ROUND FunctionQuite often a function or formula is nested in a ROUND function. This function is useful for removing fractions of a penny that could cause accounting errors later.Format: ROUND(number, digits).Example: ROUND(45.78315, 3) 45.783Nested IF FunctionsAn IF function can choose between two values. To choose between 3 values, place an IF function inside another IF function.Format: IF(logical test 1, value 1, IF(logical test 2, value 2, value 3)).Example: IF(C3=”CT”, 6.35%, IF(C3=”RI”, 7%,0))CollaborationInsert a NoteSelect a cellClick: Review > New NoteOr, Rt-Click > Insert NoteType note textClick outside note to close itEdit a NoteRt-click cell > Edit NoteEdit the text of the noteClick outside the noteDelete a NoteRt-click cell > Delete NoteInsert or Edit a CommentComments are like notes except that people can respond to a comment and create a Threaded Comment.Insert a Text BoxClick: Insert > Text > Text BoxDraw the Text Box by clicking and draggingType the text of the Text BoxClick outside the Text BoxSave a file as a TemplateClick: File > Save as > BrowseChange Save as Type to TemplateEnter filename and click SaveOpen a TemplateClick: File > New Click: PERSONAL (not: Personal)Click the desired templateHide or Unhide WorksheetsRight-click sheet tabChoose: HideTo Unhide a Worksheet:Right-click any sheet tabChoose: UnhideDouble-click the hidden worksheetProtect WorksheetsSet cells as unprotected:Select cellsRight-click a selected cellChoose: Format Cells …Click the tab ProtectionClear the check box for LockedClick: OKProtect a WorksheetClick: Review > Protect SheetIf desired, type a passwordClick: OKTo Unprotect a WorksheetClick: Review > Unprotect WorksheetIf required, enter the passwordExcel OnlineAccess Excel OnlineClick: ExcelDownload an Excel Online fileClick: File > Save As > Download a Copy ................
................

Online Preview   Download