🏡

Advanced Data Management and Analysis using Excel

Outline: Outline
Introduction: intro.pptx
Reading Material:
Template:

DayModules
1
  • Course Background and Motivaion
  • Course Objectives
  • Good practices in Data Modeling<
  • Information Retrieval Techniques
  • Road-Map
  • What is Name? Let’s give your data a meaning
  • The advantages of using Names
  • Name for single cell
  • Name for literal value
  • Name for expression
  • Name for multi-cells range
  • Name Scoping, ambiguity and conflict resolution
  • Modify Name
  • Delete Name
  • Creating a Table object
  • Delete Table
  • Change Table Name
  • Sorting a Table
  • Filtering a Table
  • Working with the Total Row
  • Creating a Calculated Column
  • Formatting a Table
  • Referring to different parts of table
  • Applying Conditional Formatting
  • Formatting based on Cell values
  • Formatting based on Formulas
  • Other Conditional Formatting (bars, scales, icons)
  • Using Data Validation
  • Entry Guide
  • Custom Error Message
  • Creating and Using Custom Formats
  • Using the Quick Analysis Tool
  • Using Table for Validation
  • Exception cases where data validation will fail
2
  • What is Controls?
  • Problems with text entry
  • Handle Yes/No value with Check-Box
  • Select one out of many with Radio-Button
  • Use Combo-Box and List-Box
  • Increase and decrease value with Spin Button
  • Techniques to hide cell values
  • Techniques to prevent user direct data entry
  • Different between Form and ActiveX Controls
  • Information Retrieval with functions
  • TEXT Functions
  • IF* and IS* functions
  • LOGICAL Functions
  • VLOOKUP and HLOOKUP functions
  • COUNT related Functions
  • SUM related functions
  • DATABASE functions
  • DATE & TIME functions
  • Other Useful functions
  • Auto Filter – Filter By Color, Filter by Icon Sets
  • Advanced Filter
  • Remove Duplicates
  • Built-In Subtotal
  • Data Grouping
  • Pulling data together
  • Consolidate based on data Position
  • Consolidate based on data labels and headers
  • Pivot-Report consolidation
  • Creating Charts
  • Changing the Chart Location and Size
  • Changing the Chart Type
  • Modifying Chart Elements
  • Formatting Chart Elements
  • Adding and Removing Data Series
  • Printing Charts
  • Creating and Using a Chart Template
  • Sparkline
3
  • Create a PivotTable
  • View fields and data
  • Change field settings
  • Styles
  • Filter a field
  • Creating PivotChart Reports
  • Calculated Fields
  • Calculated Items
  • Grouping
  • Formatting – Number/Conditional
  • Slicer
  • Slicing simultaneously multiple Pivot Tables
  • PivotChart
  • Presenting reports using dashboard techniques
  • Goal Seek
  • Scenario Manager
  • Data Tables
  • Solver
  • Automation with Macros
  • Macros Recording and Playback
  • Macros Editing
  • Introduction to VBA
  • VBA: Macro
  • VBA: Custom Functions
  • VBA: Worksheet Events handling
  • VBA: Workbook Event handling
  • VBA: ActiveX Controls
  • VBA: User Forms
  • VBA: Class Modules

Recommanded Books
TitleTeach Yourself VISUALLY Excel 365 (Teach Yourself VISUALLY (Tech))
ISBN978-1-119-93362-5
AuthorPaul McFedries
Year2022
Publisher Visual
TitleExcel 2024: Mastering Charts, Functions, Formula and Pivot Table in Excel 2024 as a Beginner with Step by Step Guide
ISBN979-8-879-13393-6
AuthorThomas Reynolds
Year2024
Publisher Independently published
TitleExcel Cookbook: Recipes for Mastering Microsoft Excel
ISBN978-1-098-14332-9
AuthorDawn Griffiths
Year2024
Publisher O'Reilly Media
TitlePivot Table And Pivot Chart: Become An Excel Pivot Table & Pivot Charts Ninja: What Is A Category Label In A Pivot Chart
ISBN
AuthorRifenbery, Melynda
Year2021
Publisher
TitleMastering Microsoft Excel: From Novice to Pro in One Book
ISBN979-8-871-91659-9
AuthorChandan Kumar
Year2024
Publisher Independently Published
TitleThe Excel Book Of The Dead: Bring Your Sheets Back To Life
ISBN979-8-875-63296-9
AuthorBisette, Vincent; Van Der Post, Hayden
Year2024
Publisher Reactive Publishing
TitleMastering Microsoft Excel From Beginner to Expert
ISBNB0CW1BLTPR
AuthorSkipton Tech
Year2024
Publisher
TitleData Analysis in Microsoft Excel: Deliver Awesome Analytics in 3 Easy Steps Using VLOOKUPS, Pivot Tables, Charts And More
ISBN979-8-852-38845-2
AuthorAlex Holloway
Year2023
Publisher Independently published
TitleEXCEL 2023: The Practical Step-by-Step Manual of Microsoft Excel for Learning Basic and Advanced Features, Formulas, and Charts with Easy and Clear Examples | From Beginner to Advanced in 7 days
ISBN979-8-372-17595-2
AuthorAlan Roberts
Year2023
Publisher Bonus Liber
TitleEssential Guide to Microsoft Excel for All Levels (2024 Collection: Forging Ahead in Tech and Programming)
ISBNB0CM1XBVQV
AuthorO., Adeolu
Year2023
Publisher
TitlePivot Table And Pivot Chart 101: Create Awesome Pivot Tables And Pivot Charts: Microsoft Excel
ISBN
AuthorStarrs, Salley
Year2021
Publisher
TitleMicrosoft Excel Pivot Table Data Crunching (Office 2021 and Microsoft 365)
ISBN978-0-137-52183-8
AuthorBill Jelen
Year2021
Publisher Microsoft Press
Title50 Things You Can Do With Excel Pivot Table (2020)
ISBN
AuthorALISHA, Mr.
Year2020
Publisher
TitleExcel Pivot Table Champion
ISBN978-1-799-14756-5
AuthorHenry E. Mejia
Year2019
Publisher Independently published
TitleExploring Microsoft Office Excel 2019 Comprehensive
ISBN978-0-135-45275-2
AuthorMary Anne Poatsy, Keith Mulbery & Jason Davidson
Year2021
Publisher Pearson Education
TitleAdvanced Excel Success: A Practical Guide to Mastering Excel
ISBN978-1-484-26466-9
AuthorAlan Murray
Year2021
Publisher Apress
TitleData Visualization with Excel Dashboards and Reports
ISBN978-1-119-69872-2
AuthorDick Kusleika
Year2021
Publisher Wiley
TitleMicrosoft Excel Advanced: Functions and Formulas
ISBN978-1-651-12943-2
AuthorSheikh, Ahmed
Year2019
Publisher