Business Intelligence with Excel 2013
Excel 2013 is a game changer for any Excel user. Excel 2013 provides new technologies and tools for the business user to easily analyze data to create powerful reports and dashboards in a fraction of the time of traditional spreadsheets.
This course is for all business users who want to learn how to create management reports, analyze data and create interactive dashboards using Excel 2013.
In this course we learn the following:
This course will provide you with an easy to follow step by step approach to using the new Business Intelligence technology in Excel 2013.
Welcome to the Business Intelligence Technologies for Excel 2013. This course will provide a comprehensive overview of Power Query, Power Pivot, Power View and Power Map.
In this lesson we will cover how to activate the add-ins for Power Query, Power Pivot, Power View and Power Map.
In this lesson we review the menus and options for the Power Query interface.
Please click on the View Resources button to download the Power Query training data file.
In this lesson we review how to easily transform data using the Power Query interface. Learn to remove columns, rename columns and replace values in data sources.
Click on the View Resource button to download the append training data sources.
Learn to easily append data sources with Power Query. Data sources need to have the same data structure to append data.
Click on the View Resources button to view the Power Query merge function. Please download the training data files to follow the lesson.
In this lesson we will learn to easily merge data from different data sources. This particularly useful for looking master data names and other attributes.
Learn to use the Group By function in Excel Power Query to create great summary tables. For example create a summary table that shows total sales, total profit and number of transactions by Customer.
In this lesson we will cover how to load data from Power Query into Power Pivot. This is particularity useful for loading data sources that are greater than 1 million rows that can not be loaded into Excel.
Please click on the View Resources button to download the Employee Master Training file to use in this section.
In this lesson we review how to easily load and import data into Excel Power Pivot.
In this lesson we review how to create relationships between tables of data in Excel Power Pivot.
In this lesson we will review how to analyze data that is in Excel Power Pivot using Excel Pivot Tables.
How to create calculated columns in Power Pivot
In this lesson we review how to easily create powerful calculated fields in Power Pivot. Calculated fields allow the user to create new custom calculations, KPIs and Key Metrics.
In this lesson we review how to use Pivot Tables and Pivot Charts to analyze and review Excel Power Pivot data.
In this lesson we create an interactive dashboard using Excel Pivot Tables and Pivot Charts using Power Pivot data.
In this lesson we review the Excel Power View designer.
This lesson teaches how to create tables of data in Excel Power View using simple drag and drop technologies. With tiles we show how to easily filter the data displayed in Excel Power View tables.
Learn to create powerful column and bar visualizations using Power View graph types. Learn to use clustered, stacked and 100% graph types.
Learn to use the line graph to easily visualize time and trend data. Easily review how data changes and the movements over time.
In this lesson we will create a powerful fully interactive dashboard using tables and graphs using Excel Power View designer.
In this lesson we will learn to use the powerful Excel Power View filtering capabilities to ask questions of our data
Please click on the View Resources button to download the Sales training data file.
In this lesson we will learn how to use Power View scatterplots to understand correlations between data. Scatterplots are a very powerful method of data analysis.
Learn to use the Time feature in Scatterplots to display how your data changes over time.
Learn to use the powerful mapping and geographical analysis tools in Excel Power View.
Introduction to the Excel Power Map geographic and mapping tool.
In this lesson we will learn to map geographic data in Excel Power Map.
Learn to change the method of visualization from clustered columns, stacked columns and hot spots in Excel Power Map.
Learn to view the changes and movements in data over time. Visualize the changes in your data on the Power Map.
Change the method of visualization from Sum, Average, Count, Distinct Count, Max and Min.
Create video based tours of your data that has been created in Excel Power Map.
The conclusion to the course.
Ian is a trainer that specializes in Microsoft Excel Business Intelligence tools and Google Data Studio. Ian is an experienced trainer that teaches techniques and tools making it easy for learners to harness the power of Excel PivotTables, PowerPivot, Power Query, Power BI and Google Data Studio.
Ian has been training learners on these powerful technologies for over 10 years making it easy for business users to easily create management reports, develop interactive dashboards and generate new insight and intelligence from business data.
Ian has over 10 years of Management Consulting experience and he brings this knowledge and skills to his training course showing Excel and Google Data Studio users how to easily create sophisticated management reports, perform data analysis and create amazing interactive dashboards without using any complex programming or specialized tools.
Ian has consulted and worked with major organizations in the Banking, Insurance, Manufacturing, Telecommunications and Logistics industries across a number of countries and continents.