Advanced Reporting - Next Generation Excel هذه الدورة تدرس باللغة الإنجليزية

تفاصيل الدورة

Day 1: Data massaging: tools and techniques
· Consolidating data from separate files and sheets
· Advanced data validation using lists, dates and custom validation
· Cell management tools: left, right, mid, concatenate, value
· Naming, editing, and managing cells and ranges
· Subtotal, Sumif, Sumifs, Sumproduct, Count, Countif, Countifs
· Looking-up data, texts, and values using Vlookup
· The incredible table-tools technique
· Slicing dates into day names, weeks, week numbers, month names, years and quarters
· Text to columns and dynamic trimming using Trim, Len
· Managing texts and numbers using replace, find, and substitute
· Text change functions
 
Day2: Reporting, analysis and reconciliations using pivot tables
· The 20 must learn rules
· Creating pivot tables
· Number formatting techniques
· Designing report layout
· Sorting in ascending, descending and more sort options
· Filtering labels and values
· Expanding and collapsing reports
· Summarize data by sum, average, minimum, maximum, count
· Show values as % of total and % of
· Pivot table options
· Drill down option
· Showing report filter pages
· Inserting formulas
· Date analysis
· Copying pivot tables
· Creating pivot charts
· Dynamic chart labeling
· Mastering the slicer
· Linking pivot tables and pivot graphs with PowerPoint
· Conditional formatting with pivot tables
· Designing reports using GetPivotData 
 
Day 3: Modeling and integration techniques
· Perform 'what-if' analysis using spinner
· Check box data modeling with 'if' function
· Option button data modeling with 'if' function
· List box data modeling with 'Choose' function
· Linking Excel with text files
· Linking Excel with databases (Access)
· Linking Excel with multiple Excel files and SQL
· Linking Excel with internet
· Linking Excel with Excel
· Scenario manager
 
Day 4: Introduction to learning the ultimate tool in Excel: ''Macros''
· Macro basics
· Planning a macro
· Designing your control board
· Recording macro
· Testing macro
· Editing macro
· Macro workshops
· Advanced filter with macro
 
Day 5: Tips and tricks in Excel
· Data entry form
· Custom list
· Camera tool
· Text to speech
· Protecting worksheets and workbooks
 
  تحديث بتاريخ 17 November, 2022