Explore the power of Excel spreadsheet formulas for data analysis in this 30-minute video tutorial. Compare traditional formulas with new Office 365 Dynamic Array Formulas, focusing on SUMIFS, COUNTIFS, and AVERAGEIFS for creating data analysis summary reports. Learn to change default settings for table formula nomenclature, use cell references effectively, and apply Excel functions for calculations based on single and multiple conditions. Practice with provided start files, finished files, and PDF notes to master both old school and dynamic array approaches. Gain valuable skills in data analysis and business intelligence using Microsoft Excel Power Tools, including PivotTables, Power Query, Power Pivot, and Power BI Desktop.
Overview
Syllabus
) Introduction.
) Excel Spreadsheet Formulas Uses in Data Analysis.
) Change Default Settings for Table Formula Nomenclature (Structured References)..
) Cell References in Formulas..
) Excel Functions to Make Calculations Based on one Condition, four examples:.
) SUMIFS Function with One Condition shown on next page in cell H13.
) SUMIFS Function with One Condition & Dynamic Array shown on next page in cell K13.
) COUNTIFS Function with One Condition shown on next page in cell H24.
) COUNTIFS Function with One Condition & Dynamic Array shown on next page in cell K24.
) Excel Functions to Make Calculations Based on an AND Logical Text..
) SUMIFS for Summary Report Based on Adding, Old School Cell References and Dynamic Arrays..
)AVERAGEIFS for Summary Report Based on Averaging, Old School Cell References and Dynamic Arrays..
) COUNTIFS for Summary Report Based on Counting, Old School Cell References and Dynamic Arrays..
) Homework sheets..
) Summary.
Taught by
ExcelIsFun
