MS Excel Session 5 Topics - Tech Help Today



Microsoft Excel 2016 Session 4: Managing Multiple WorksheetsRich MalloyDatabase Tricks (Cont.)The COUNTIF FunctionCounts only certain cells in a table.Format: COUNTIF(range, criteria)Example: COUNTIF(C2:C10, “Yes”)SUMIF and AVERAGEIF FunctionsConsiders only certain data in a table.Format: SUMIF(range, criteria, average_range).Example: SUMIF(C2:C10, “Yes”, D2:10): This sums the values in column D that have a value in column C equal to “Yes”.Naming Cells for Easier FormulasIf you define names for cells, your formulas will be easier to create and understand.Select a range of cells.Click in the Name Box.Enter a name (no spaces).To use the name in a formula, press F3.Create Names from a SelectionAn easier way to name ranges of cellsSelect a colum (or row) including the header label.Click: Formula > Create from SelectionCheck: Top row (or Left column for a row)Use a Named Cell in a FormulaAn easy way to insert a named cell in a formula:Press the F3 keyChoose the desired named cellManaging Multiple WorksheetsCreate New WorksheetsClick “Plus” (New Sheet) button in Status BarRename a WorksheetDouble-click the Sheet tab, orRt-click the Sheet tab and choose Rename.Moving SheetsClick and hold the Sheet tabDrag the tab to the desired location.Apply Tab ColorsRt-click the Sheet tab.Choose: Tab ColorChoose the desired colorSelect Multiple SheetsClick the tab of the first SheetPress and hold the Ctrl keyClick the tabs of the other SheetsTo select adjoining sheets:Click the tab of the first SheetPress and hold the Shift keyClick the tab of the last SheetUngroup SheetsRt-click the tab of one grouped SheetChoose: UngroupEdit Multiple WorksheetsSelect multiple SheetsEdit one Sheet to edit all of themUngroup the SheetsNote that some editing options are not allowed on grouped Sheets.Format Multiple SheetsSelect multiple SheetsEdit one Sheet to edit all of themUngroup the SheetsHide a WorksheetRt-click the tab of the SheetChoose: HideUnhide a WorksheetRt-click the tab of any SheetChoose: UnhideChoose the Sheet to unhidePaste Data with Special Paste OptionsThe Ribbon Method:Click: Home > Paste ↓Choose the desired option, or click Paste SpecialThe Paste Options Method:Paste using Ctrl + V or Rt-click PasteClick the Paste Options buttonChoose the desired Paste optionPaste LinkClick: Home > Paste ↓Choose: Paste Link, or press the N keyRefer to Other Worksheets in FormulasStart creating a formulaNavigate to a SheetClick the desired cell on the SheetContinue creating the formulaSparklinesTiny Charts within a cell.Select a cellClick: Insert > Sparklines > ColumnFor Data Range, select the data cellsHyperlink to a WebpageClick a cellEnter a Web addressHyperlink to Other WorksheetsCreate a Table of ContentsClick a cellClick: Insert > LinkAt the left, choose: Existing File or Web PageIn the Address box, enter the Web addressRemove a HyperlinkRt-click a cell with a HyperlinkChoose: Remove HyperlinkPrint Multiple WorksheetsSelect multiple SheetsClick: File > PrintAfter printing, Ungroup the SheetsInsert Sheet Names in a HeaderSelect multiple SheetsTraditional Method:Click: File > PrintClick: Page SetupClick the tab: Header/FooterClick: Custom Header [or Footer]Ribbon Method:Click: Insert > Header & Footer, or Text > Header & FooterThe Page Layout view will openClick the desired Header/Footer areaEdit the area as desiredClick any cell in the Sheet gridClick: View > NormalCharts (Cont.)Some additional Chart skill that will probably be on the MOS Core Excel Exam.Add additional data seriesSelect a ChartClick: Chart Tools Design > Select DataEdit the text box labeled Chart Data Range, or select a new data range with your mouseClick: OKAnalyze data by using Quick AnalysisSelect a Range of cellsClick the button: Quick AnalysisChoose the desired optionApply Chart LayoutsSelect a ChartClick: Chart Tools Design > Quick LayoutChoose the desired layoutApply Chart StylesSelect a ChartClick: Chart Tools DesignChoose the desired Chart StyleInsert ImagesClick: Insert > Pictures, or Illustrations > PicturesBrowse to the desired picture fileModify Object PropertiesApplies to Charts, Pictures, Shapes, ObjectsRt-click Object; Click: Format Object; Click: Size & Properties; Click: PropertiesAdd alternative text to objects for accessibilityFor screen readers used by blind people. Applies to Charts, Pictures, Shapes, Objects, Pivot TablesMethod A: Rt-click Object; Click Edit Alt TextMethod B: Click: Picture Tools Format > Alt TextMethod C: Rt-click ObjectClick: Format ObjectClick: Size & PropertiesClick: Alt TextLeave the Title blank unless there is a long DescriptionText FunctionsParse text with LEFT, RIGHT, and MIDLEFT("Test",2) "Te"RIGHT("Test",2) "st"MID("Test",2,1) "e"Format with UPPER, LOWER, and PROPERUPPER("test case") "TEST CASE"PROPER("test case") "Test Case"Combine text with the CONCATENATECONCATENATE("Test", " ", "case") "Test case" ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download