3 Month

Introducing Microsoft Excel

• Working With Excel
• Worksheets and Workbook    
• Understanding the User Interface     
• Quick Access Toolbar
• Ribbon
• Formula Bar        
• Worksheet Area
• Worksheet Tab
• Status Bar
• The Backstage View
• Saving Excel Workbook
• Creating New Excel Workbook        
• Opening Existing Excel Workbook

Customizing Common Options in Excel

• File location
• Number of sheets, Alignment Of row & Column
• Cursor movement
• Font face and size
• Startup folder
• File format, Enable text wrap
• Advance Filter, Advance Sorting
• Maintain Track Changes
• Hide ,Unhide Row & Column
• Merge & Unmerge
• Insert Header & Footer In Excel
• Insert Shape ,Picture ,Smart Art Etc..

Entering Data

• Concept of Tabular Data
• Cell Nomenclature
• Editing Workbook
• Editing Worksheet
• Entering Data      
• Renaiming Sheet

Working With Numbers

• Excel Data Types: Number ,Date,Time  Etc..
• Moving Data
• Organizing Data in Excel Tables
• Creating Simple Formulas .
• Arithmatic & Comparison Operator
• Relative vs. Absolute References
• Using Functions
• Using Math Functions:
• Round,Power,SQRT,RoundUp,
• Using Statistical Functions
• Average If,Count,Countif
• Averageif,Min,Max ,Sumif,Countif Etc..
• Using Financial Functions: Etc..
• FV,INTRATE,IPMT,Rate,NPER,PPMT

Formatting Data

• Common Font Formatting,
• Font Type ,Size,Color,Style, Text Orientation
• Cell Border, Fill Colour
• Cell Alignment, Text Alignment
• Text Indentation, Text Wrapping,
• Formatting Numbers, Merging Cells
• Number – Format, Currency Format
• Accounting  Format, Format As Percent
• Decimal Formatting, Applying Styles
• Additional Cell Formatting Features,
• Conditional Formatting
• Using Format Painter

Managing Your Data in Excel  

• Setting Named Range    
• Finding and Replacing Data
• Inserting Cut or Copied Cells,Paste Special
• Sorting Worksheet Data
• Filtering Worksheet Data,Color  Filter Etc.
• Formula Auditing, Adding, Displaying.
• Editing, and Removing Comments
• Trace Dependents,Formula Auditing

Working with Text and Date

• Text Functions: concatenate, Left Function
• Trim,Find,Replace,Exact,Search
• Using Date & Time : Today ,Days360,Networkdays,Now
• Formatting Date: Hour,Yearfrac,Minut,Month,Time,Weekday
• Formatting Time in Different Format

Manipulating Data

• Relative & Absolute Referencing
• Transposing Rows Into Columns
• LOOKUP Functions : Vlookup,Hlookup
• Lookup_value,Table_Array,Range_Lookup
• Manipulating Data

Working with Charts

• Charts in Excel :Column,Line,Pie,Bar,Area,Scatter
• Chart Tools Tabs: The Anatomy Of Chart
• Chart Area,Label,Grid Lines   
• Creating Charts
• Changing Chart Type
• Editing Charts
• Changing Data Source
• Changing Chart Style / Appearance 
• Resizing Charts
• Moving Charts
• Changing Chart Layout 
• Managing Chart Labels
• Managing Axes Layout
• Adding a Trendline to Chart
• Naming Your Chart
• Formatting Chart Area and Labels

Working with PivotTable

• Creating PivotTable
• PivotTable Tools Tabs
• PivotTable Field List Task Pane
• Report Filter,Axis Field
• Legend Field & Values
• Applying Style to PivotTable
• Applying Filters and Slicers
• Applying Filter , Applying Slicer
• Filtering Data and Creating PivotCharts

What If Analysis

• Using Goal Seek
• Using Scenario Manager
• Recording Macro
• Managing Macro Security Level in Excel
• Recording Macros
• Running Macros

Printing Excel Files     

• Page Layout
• Setting Up Your Page
• Printing Your Worksheet
• Applying Background
• Printing Headers
• Printing Data

Managing Excel Files

• Saving Files
• Excel Workbook
• Excel Macro-Enabled Workbook
• Excel 97-2003 Workbook
• Web Page,csv,pdf
• Emailing Workbooks

Grouping and Outlining Data

• Grouping Your Data ,Outline Your Data
• Adding Subtotal
• Adding Outline ,Automatic Style,
• Linking and Protecting Worksheets

Working With Reports

• Convert data to tables
• Finding the related data
• Creating Subtotal
• Multiple-level subtotals
• Formatting and Customizing Pivot Tables
• Using data Consolidation feature to consolidate data
• Freeze Pane
• Setting of row & column (height & width)

• Overview
• Power BI

Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Your data may be an Excel spreadsheet, or a collection of cloud-based and on-premises hybrid data warehouses.

Learn to create stunning Dashboards and Reports using Microsoft's free Business Intelligence / Analytics tool, Power BI.

Do you want to build professional-quality Business Intelligence Reports from the ground up ? Do you want to blend and transform raw data into beautiful Interactive Dashboards ? Do you want to learn design and implement the same B.I. tools used by professional analysts and data scientists ? Do you want to understand the Business Intelligence workflow from end-to-end.

 Then Microsoft Power BI is the right tool for you and this comprehensive course will teach you everything you need to know to use Power BI. 

• Course Curriculum

Session 1: Quick Start Power BI Service

• Get Power BI Tools
• Introduction to Tools and Terminology
• Dashboard in Minutes
• Refreshing Power BI Service Data
• Interacting with your Dashboards
• Sharing Dashboards and Reports

Session 2: Getting and Transforming Data with Power BI Desktop

• Introduction to Power BI Desktop
• Getting Data: Excel vs Power BI Desktop & Service
• Naming for Q&A
• DirectQuery vs Import Data
• Recap and What's Next

Session 3: Modeling with Power BI

• Introduction to Modeling
• Setup and Manage Relationships
• Cardinality and Cross Filtering
• Default Summarization & Sort by
• Creating Calculated Columns
• Creating Measures & Quick Measures
• Recap and What's Next

Session 4: Power BI Desktop Visualisations

• Creating Visualisations
• Color & Conditional Formatting
• Setting Sort Order
• Scatter & Bubble Charts & Play Axis
• Tooltips
• Slicers, Timeline Slicers & Sync Slicers
• Cross Filtering and Highlighting
• Visual, Page and Report Level Filters
• Drill Down/Up
• Hierarchies
• Constant Lines
• Tables, Matrices & Table Conditional Formatting
• KPI's, Cards & Gauges
• Map Visualisations
• Custom Visuals
• Managing and Arranging
• Drillthrough
• Custom Report Themes
• Grouping and Binning
• Bookmarks & Buttons
• Recap and What's Next

Session 5: Power BI Service Visualisation Tools

• Introduction to the Power BI Service
• Standalone Tiles
• Data Driven Alerts (Power BI Pro/Premium)
• Quick and Related Insights
• Custom Q&A

Session 6: Publishing and Sharing

• Sharing Options Overview
• Publish from Power BI Desktop
• Publish Reports to Web Sharing Reports & Dashboards (Power BI Pro/Premium)
• Workspaces (Power BI Pro/Premium)
• Apps (Power BI Pro/Premium)
• Printing, PDFs and Exporting to PowerPoint
• Row Level Security (Power BI Pro)
• Export Data from a Visualisation
• Publishing for Mobile Apps
• Sharing Options Summary

Session 7: Refreshing Datasets

• Understanding Data Refresh
• Personal Gateway (Power BI Pro and 64-bit Windows)
• Replacing a Dataset
• Troubleshooting Refreshing

Session 8: Power BI and Excel Together

• Options for Publishing from Excel
• Pin Excel Elements to Power BI (Excel 2010-2013)
• Analyze in Excel (Power BI Pro or Premium)
• Excel Publish: Upload and Export to Power BI
• Sharing Published Excel Dashboards (Power BI Pro or Premium)