Excel 365 – Advanced

TEACHING UNIT 1. BASIC CONCEPTS Interface Elements Entering and Editing Data Formatting Working with Multiple Sheets Creating Charts Customization Help: An Important Resource TEACHING UNIT 2. DATA ENTRY AND FORMULAS Data Types – Assigning a data type – Determining a data type Data Entry – Entering text and numbers – Entering dates and times – Entering Formulas – Repeatedly Entering the Same Data – Generating Sequences – The Enhanced Office Clipboard Cell References – Multiple References and Range References – Two-Dimensional, Three-Dimensional, and Other References – References and names – Data validation Formatting – Custom formats – Conditional formatting – AutoFormat LEARNING UNIT 3. TABLES AND DATA LISTS Initial data Totaling and summarizing – Totaling – Sorting Filtering and grouping data – Creating subtotals and groups – Creating layouts – Filtering data – Advanced filters Pivot Tables – Designing a pivot table – Customizing elements – Including additional fields – PivotTable Reports – PivotCharts – Using PivotTable Data in Formulas Data Tables LEARNING UNIT 4. DATA ANALYSIS Configuring Analysis Tools Tables with Variables – Table with One Variable – Table with two variables Functions for making forecasts Scenario simulation – Creating scenarios – Using scenarios Goal seeking The Solver tool – Applying constraints – Reports and scenarios – Solution options – Applications of Solver Other Data Analysis Tools – Descriptive statistics – Creating a histogram TEACHING UNIT 5. DATABASES Obtaining Data – Data sources – Text files – Data tables – Database queries – Connection settings – Web queries – Microsoft Query – Data properties – Updating data Editing databases Database functions XML mapping LEARNING UNIT 6. CHARTS AND DIAGRAMS Creating Charts – Creating a Chart – Chart Areas – Customizing Elements Inserting Mini-Charts Customizing Highs and Lows Inserting Shapes – Using Controls – Text Boxes – Outlines and Fills – Shadows, borders, reflections, and 3D rotation – Layouts Images – Searching for images online – Editing images Graphic elements and interactivity SmartArt – The Text Pane – Formatting SmartArt LEARNING UNIT 7. PUBLISHING DATA Printing Worksheets – Selecting data to print – Page breaks – Headers and footers – Preview Publishing Excel Workbooks – Sending directly to one or more recipients – Web publishing LEARNING UNIT 8. LOGICAL FUNCTIONS Relationships and logical values – Comparing values – Complex expressions Decision-making – Using decisions to avoid errors Nesting expressions and decisions Conditional operations Selecting values from a list LEARNING UNIT 9. DATA SEARCH Manipulating References – Number of Rows and Columns – Direct and Indirect References – Shifting References Searching for and Selecting Data – Finding Matches – Direct selection of a data point – Searching in rows and columns – VLOOKUP function Transposing tables TEACHING UNIT 10. OTHER USEFUL FUNCTIONS Text manipulation – Codes and characters – Concatenating strings – Extracting characters – Search and replace – Conversions and other operations Working with dates – Informational functions – Operational functions Miscellaneous information TEACHING UNIT 11. ACCESSING EXTERNAL FUNCTIONS Registering External Functions – Macro Sheets – The Register Operation Invoking Functions – Obtaining a Function’s Identifier – Calls with Automatic Registration Excel 4.0-Style Macros Workbooks with Macros TEACHING UNIT 12. MACROS AND FUNCTIONS Recording and Playing Back Macros – A Macro to Insert Subtotals – Playing Back the Macro Managing Macros – Modifying and Step-by-Step Tracking – Macros and Security Defining Functions LEARNING UNIT 13. INTRODUCTION TO VBA The Visual Basic Editor – Project management – Editing properties The Code Editor – Examining objects The Immediate Window A Practical Example – Workbooks, worksheets, cells, and ranges – Drawing boxes – Renaming worksheets LEARNING UNIT 14. VARIABLES AND EXPRESSIONS Variables – Defining variables – The default type – Arrays – Scope issues Expressions TEACHING UNIT 15. CONTROL STRUCTURES. THE EXCEL OBJECT MODEL Conditional values Conditional Statements – If/Then/Else – Select Case Looping Structures – Counter-based loops – Condition-based loops – Iterating through collections Core Excel Objects – The application – The workbook – The worksheet Other Excel Objects LESSON 16. DATA MANIPULATION Selecting a Data Table – Cells, Ranges, and Selections – Selection and Activation – Navigation – Selecting the Table Data Manipulation Inserting New Data The Complete Solution LESSON UNIT 17. DIALOG BOXES Pre-designed dialog boxes – How to display a dialog box – Confirmations and data requests Custom dialog boxes – Adding a form to the project – Working with components – Component access order A more user-friendly and convenient macro Launching the dialog box – Adapting the process to the options TEACHING UNIT 18. GROUP WORK Sharing a Workbook Commenting on Data Track Changes – Enabling Track Changes – Change Summary – Reviewing Changes Review Tools LESSON UNIT 19. DOCUMENTS AND SECURITY Restricting Access to a Document – Workbook Protection – Sheet Protection – Password-Protected Ranges – Protecting Other Elements Digital Security – Obtaining a Digital Certificate – Digital Signing TEACHING UNIT 20. CUSTOMIZING EXCEL Settings for Workbooks and Sheets – Default Attributes for New Workbooks – Options for Individual Workbooks and Sheets Environment Options – The Quick Access Toolbar The Ribbon Creating Custom Tabs and Groups