Microsoft Excel is a powerful tool essential for managing and presenting data
in today’s working environment. In this one day excel course,
you will gain the knowledge and skills required to create and edit worksheets, use formulas
and functions, sort and filter detail data visually, and present summary information
in a consumable and professional format.
A macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer Errors. Macros are written in VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft.
The Power Query and Power Pivot features in Microsoft Excel can make a powerful combination. Power Query enables you to discover, connect to, and import data, and Power Pivot lets you quickly model that data. You will learn how to use the DAX formula language to provide lookup abilities.
I will walk you through step-by-step how to use Power Query to select data, prepare a query, cleanse data, and prepare data for Power Pivot. Also i will walks you through the Power Pivot workflow, showing how to create a data model, import additional data if needed, build relationships between data, and create calculations and measures.
You will learn hands on real-world scenarios for working together with Power Query and Power Pivot.
What You Will Learn Include:
What is Office 365
Setting up Office 365
Excel 2016 user interface
Exploring the Excel Ribbon
Customizing the Quick Access Toolbar
Keeping Tabs on the Ribbon
Entering Data
Opening a new workbook
Entering Data in Excel
Basic Data entry
Entering Data with Autofill
Entering Date
Entering Time
Undo and Redo Changes
Adding Comments
Giving your worksheet a title
Saving your work
Creating Formulas and Functions
Using formulas for arithmetic tasks
Re-using Formulas
Calculating YTD Profits
Calculating Percentage Change
Using Relative and Absolute Reference
Using RANK Function
STD Function
Small and Large Functions
Median Function
Count and counta functions
Formatting
Exploring Fonts
Adjusting Column Width and Row Height
Using Alignment
Designing Borders
Formatting Numbers
Conditional Formatting
Creating Tables
Inserting Shapes
What VBA is and why it is so incredibly useful
What are Macros
Creating Macros using macro recorder
Creating macro manually from scratch
Editing Macros
Debugging Macros
How to write VBA code and execute it in Excel
How to make your macros work with workbooks, worksheets and the data on them
How to find and eliminate errors in your programs and make your macros run
Automation using macros.
Analyse the macro from the Developer tab or the View tab.
Examine the VBA window and VBA components.
Distinguish between absolute and relative references.
How to save workbook with macros
Create a module
Create procedure
Create a sub
Understand the difference between a module and sub
Run the code in a sub
Preparing queries
Cleansing data with Power Query
Enhancing queries
Creating a data model in Power Pivot
Building relationships
Creating Pivot Tables and Pivot Charts
Creating Lookups
Using DAX to link data
Creating data model
Creating relationship between data sources
Analyzing data with PivotTables and PivotCharts
Using Power Query with PowerPivot
Connecting to a variety of data sources with Power BI
Create and publish reports to Power BI Service
Refreshing Data Source
Updating Queries
Using Conditional statements
Using quick and dynamic measures
Transforming data on connected datasets
