Department Directory | Highline College



Video Topic List For Projects 20 to 40Office 2010 Class #20 What Is Excel?, Editing Ribbons and Quick Access Toolbar File ExtensionsWhat is Excel? Calculating formulas and Data AnalysisRows, Columns, Cells, Worksheets, Sheet Tab Names, WorkbookFile extensions (file types): .xlsx, .xlsm, .xls, .xlsbExcel 2010 RibbonsExcel 2010 Quick Access Tool Bar (QAT)How to find features in Excel 2007How to add buttons (Customize) to the QAT Quick Access Toolbar and RibbonsNew Keyboard Shortcut:noneOffice 2010 Class #21: Excel Formulas & Functions: Formula Inputs Numbers or Cell References?Equal SignUse Cell References for formula Inputs any time a number can change (like payroll data or an interest rate)Numbers that can be typed into a formula (numbers that do not change)Three ways to enter a formula (Mouse, Arrows, Typing)Ampersand = Join Symbol (Shift + 7)Calculating FormulaText FormulaSearch For Function using Insert Function dialog boxEFFECT and PMT finance functionsFormat Cells Dialog BoxCurrency Number formattingGeneral Number formattingDOLLAR function for Text Formulas (applies Currency Formatting to number in text formulas)Number Formatting is a fa?ade that sits on the surface of the spreadsheet: the actual item (like a number) in the cell can be different than what you see on the surface of the spreadsheet.New Keyboard Shortcut:Turn off Dancing Ants = EscOffice 2010 Class #22: Formulas: Math Operators and Order Of OperationsExcel operator symbolsParenthesis is ()Exponent is ^Multiplication is *Division is /Adding is +Subtracting is –Order of OperationsComplete list of how Excel Evaluates formulasExcel 2003: Formula Evaluator: Tools, Formula Auditing, Formula AuditingExcel 2007: Formula Evaluator: Formula Ribbon, Formula Auditing group, Formula Auditing iconNew Keyboard Shortcut:Run Formula Evaluator = Alt, T, U, FOffice 2010 Class #23: Excel Functions and Defined NamesTopics Covered In Video:Functions:SUM (add)COUNT (Count Numbers)COUNTA (Count non-blank cells)MIN (minimum value)MAX (maximum value)COUNTIF (count with a condition or criteria)SUMIF (Sum with a condition or criteria)STDEVP (standard deviation for the population)Compatibility functionsHow to name a range of cells using Defined Names FeatureInsert Function keyboard = Shift + F3Paste Name keyboard = F3Name Manager keyboard = Ctrl + F3Create Names From Selection keyboard = Ctrl + Shift + F3New Keyboard Shortcut:Insert Function keyboard = Shift + F3Paste Name keyboard = F3Name Manager keyboard = Ctrl + F3Create Names From Selection keyboard = Ctrl + Shift + F3Currency Number Format = Ctrl + Shift + 4Office 2010 Class #24: Excel Functions COUNTIF & SUMIF Count and Add with one Condition (Criteria)Topics Covered In Video:Learn how to use the Excel Functions COUNTIF & SUMIF Count and Add with one Condition (Criteria).Create Names From Selection keyboard = Ctrl + Shift + F3New Keyboard Shortcut:Create Names From Selection keyboard = Ctrl + Shift + F3Office 2010 Class #25: Excel Cell References: Relative, Absolute, MixedTopics Covered In Video:Learn how to create and when to use : Relative, Absolute, Mixed Cell References in formulasNew Keyboard Shortcut:F4 Toggle between the 4 cell references.Office 2010 Class #26: Orientating Assumption Tables (Formula Inputs) & Cell Ranges in FunctionsTopics Covered In Video:Orientating Assumption Tables for Formula Inputs for Maximum Efficiency so that you can use Mixed Cell ReferencesHow to use Cell Ranges instead of individual cells for Maximum Efficiency (=SUM(F3:H3) instead of F3+G3+H3)New Keyboard Shortcut:noneOffice 2010 Class #27: Excel Stylistic Formatting & Page Setup For ReportsTopics Covered In Video:Center Across Selection, Not Merge and CenterFill Color & Font ColorNumber FormattingBordersPage Setup: Scaling, Margins, Header and Footer, Set Print Area, Rows To Repeat At TopNew Keyboard Shortcut:noneOffice 2010 Class #28: Excel Number Formatting: Formatting As Fa?ade Topics Covered In Video:Number Formatting as Fa?ade – what you see on the surface of the cell is not always what is actually in the cellAccounting Number FormattingCurrency Number FormattingPercentage Number FormattingDate Number FormattingTime Number FormattingDate MathTime MathNew Keyboard Shortcut:Apply General Number Format = Ctrl + Shift + ~Insert Todays Date = Ctrl + ;Office 2010 Class #29: Excel Basics 10: ROUND Function How And When To Use ics Covered In Video:When and how to use the ROUND function.You are required to round like with money involved with invoices, taxes or payroll (there are no partial pennies).The formula calculation involves multiplying or dividing numbers that contain decimals (numbers that are being added or subtracted should already be rounded properly).The formula calculation result will be used in a subsequent formula (like SUM function for adding a column of tax calculations).The second argument of the ROUND function = 2, then it rounds to pennyThe second argument of the ROUND function = 0, then it rounds to dollarNew Keyboard Shortcut:noneOffice 2010 Class #30: Excel Date Formulas: Days Invoice Past Due, Loan Maturity Date, Project LengthTopics Covered In Video:Date Number Format (Serial Numbers)Date MathFormula for Days Invoice Past DueFormula for Loan Maturity DateFormula for Length in Days for ProjectNew Keyboard Shortcut:noneOffice 2010 Class #31: Excel Time Number Format & Payroll Time Sheet CalculationsTopics Covered In Video:Learn about Time Number Format (Serial Number)Build a Time Sheet in Excel to add time worked in a day and for payrollNew Keyboard Shortcut:noneOffice 2010 Class #32: Percentage Number FormatTopics Covered In Video:Situation 1: Type .03 in cell, then add % formatSituation 2: Type 3 in cell, then add % formatSituation 3: Add % format, then type 3, EnterSituation 4: Add % format, then type .03, EnterSituation 5: Add % format as you typeSituation 6: Type .025, then add % from ribbonNew Keyboard Shortcut:noneOffice 2010 Class #33: Excel Charts: Column, Bar, Pie, Line, X-Y Scatter, Sparklines Formatting, Link LabelsTopics Covered In Video:Column ChartsFormat Chart ElementsAdd or delete Chart ElementsLink Chart labels to cells using the F2 key and the equal sign Adjust ChartsBar ChartPie ChartLine ChartX-Y Scatter Chart GraphSparklines – Cell ChartsChange Chart LocationNew Keyboard Shortcut:Open Format Chart Element keyboard = Ctrl + 1After Chart Label selected, to jump cursor to formula bar keyboard = F2Create Default Chart on Sheet keyboard = Alt + F1Create Default Chart on New Sheet keyboard = F11Office 2010 Class #34: How Data Must Be Setup To Use Excel Data Analysis FeaturesHow Data Must Be Setup To Use Excel Data Analysis Features: Table Format Structure:Field Names (Column Headers) in First RowRecords (Individual Transactional Records) must be in rowsNo Blank Columns, Rows, Fields names. Try not to have blanks in data set.Data set must be surrounded by blank columns and rows and/or Excel Worksheet column or row headersNew Keyboard Shortcut:Select whole table keyboard = Ctrl + *Jump to bottom of column keyboard = Ctrl + Down ArrowJump to end of Current Region keyboard = Ctrl + ArrowJump To Cell A1 keyboard = Ctrl + HomeOffice 2010 Class #35: Excel Sort and Filter (Data Analysis)Topics Covered In Video:Sort with ButtonsSort with Dialog boxSort three columnsSort by ColorFilterFilter By ColorFilter and Extract DataNew Keyboard Shortcut:Insert New Sheet keyboard = Shift + F11Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examplesTopics Covered In Video:PivotTables are easy: Just envision the table in advanceField list has fields and areas that you can drag fields toColumn Labels – Column HeadersRow Labels – Row HeadersValue Area – this is where the calculation goesCross tabulation (row label and column label)Double Click to create new sheet with dataFilter area – filters whole reportValue Field Settings Dialog boxNumber Format For Value Field in Value Field Settings Dialog boxSUM functionChange Function in Value Field Settings Dialog boxAverage FunctionFormat PivotTableLayout For PivotTableMultiple calculations: just drop field into value area multiple times and change functionChange Field Names in Value Field Settings Dialog boxFrequency Table to CountGroup By DatesNew Keyboard Shortcut:Open PivotTable dialog box keyboard = Alt, N, V, TOffice 2010 Class #37: Excel Table Feature For Dynamic Ranges in Functions, Charts, PivotTablesTopics Covered In Video:Learn how to use the Excel Table Feature to create dynamic ranges for functions, charts and PivotTables.Dynamic means that if you add new records to the bottom of the table (Tab in lower right corner) the ranges in formulas, charts and PT will update.Ctrl + T to create TableNew Keyboard Shortcut:Create Table keyboard = Ctrl + TOffice 2010 Class #38: IF Function Formula Made EasyTopics Covered In Video:Use IF function to put one of two things into a cell or formulasLogical Formula comes out to be TRUE or FALSELogical Test is IF functions 1st argument and it comes out to be TRUE or FALSEThen you tell the IF function what you want in the cell if the test is TRUEAnd you tell the IF function what you want in the cell if the test is FALSEIF function to put one of two numbers in a cellIF function to put one of two words in a cellIF function for bonusIF function for checking if 2 numbers are in balanceNew Keyboard Shortcut:NoneOffice 2010 Class #39: VLOOKUP Function Formula Made Easy (3 Examples)Topics Covered In Video:See how to use the VLOOKUP function to look up an item in a table and return something to a cellVLOOKUP with Exact Match (Lookup a word)Data Validation List (Data Ribbon tab, Data Tools Group, Data Validation) Keyboard = Alt, D, L.VLOOKUP with Approximate Match (Lookup a number)VLOOKUP for looking up product priceVLOOKUP for looking up tax rateVLOOKUP to assign a sales category to a sales numberNew Keyboard Shortcut:NoneOffice 2010 Class #40: SUM Function Keyboard Shortcut for 5000 Rows of DataTopics Covered In Video:Learn Excel’s best Keyboard ShortcutAuto SUM keyboard = Alt + =Highlight a Column of Numbers keyboard = Ctrl + Shift + Down ArrowNew Keyboard Shortcut:Highlight a Column of Numbers keyboard = Ctrl + Shift + Down Arrow ................
................

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

Google Online Preview   Download