Business Analytics: Data Analysis & Visualizations with Microsoft Excel
Duration
5 Days
Level
Basic Level
Design and Tailor this course
As per your team needs
This instructor-led hands-on course is designed to get you started with Business Analytics using one of the most popular productivity tools Microsoft Excel. This course assumes no familiarity with MS Excel and takes the learners from the basics of the tool to using advanced analytics features.
The course begins with a review of data analysis fundamentals and exploring Excel features. Then it starts the data analysis journey by working with data, formatting, and making charts to visualize data. It then moves on to using formulae and functions for advanced analysis and goes on to summarizing and analyzing data with functions, including statistical and geospatial analysis. Then the course heads to explore a powerful feature of Excel – pivot tables and pivot charts and creating dashboards.
Before moving onto sharing insights via reports, the course discusses PowerPivot and details about data cleaning and transformation. In the module on PowerPivot, we will explore statistical analysis, data transformation, data modeling, and analysis.
At the end of this course, you will be able to:
- Work confidently with Excel
- Make use of Formulae and Functions
- Perform basic data analysis
- Visualize data with Excel using charts, trendlines, and sparklines
- Analyze data with formulas and functions
- Perform standard statistical analysis
- Analyze data with PivotTables and Pivot charts
- Create dashboards to present visual insights
- Make use of geospatial data with visualizations
- Capture, clean, and transform data
- Leverage PowerPivot to model and analyze data
- Present insights with reports and PowerPoint presentation
Any one who wishes to excel at Excel. This course is targeted toward business users, novice data analysts, and data analysts who wish to leverage Microsoft Excel in addition to the tools they already use.
- What is Data Analytics?
- Types of Analytics
- Data analysis scenarios
- Importance of Spreadsheets for analyzing structured content
- Pros & Cons of MS Excel vs some RDBMS
- Getting started with MS Excel
- Excel core concepts – Layout, UI and navigation,Worksheets & Workbooks
- Maneuvering Excel spreadsheets – Keyboard shortcuts
- Adding data to Excel – Using CSV files to import data
- Formatting the data and changing data types
- Performing Calculations – Common formulae and usage
- Working with data – Filtering, Sorting, Conditional formatting
- Cell referencing – Relative, Absolute, and Mixed reference
- Working with Tables
- Referencing data from other worksheets & workbooks
- Freezing panes for easy navigation
- Visualize data with charts
- Modify and format charts
- Best practices for selecting charts
- Summarizing and Analyzing data with formulas
- Analyze data with functions
- Mathematical functions
- Text functions
- Date and Time functions
- Logical functions
- Statistical Functions
- Financial Functions
- Information Functions
- Introduction to Lookup Functions
- Data Validation – using formulas for validation
- Conditional Logic
- Logical Tests, Conditional Operations, nested-if
- Basic and advance conditional formatting
- Automatic Lookup functions – INDEX, MATCHCHOOSE, VLOOKUP, HLOOKUP
- Create conditional visualization with Lookup functions
- Formula Auditing and Protection
- Creating data trendlines and sparklines
- Installing Data Analysis Toolpak
- Analyzing data with Data Analysis Toolpak
- Creating map charts in Excel
- Formatting and customizing map charts in Excel
- Creating a PivotTable
- Analyzing data through a PivotTable
- Visualizing data with PivotCharts
- Filter data using Slicers
- Using macros for automating tasks
- Creating dashboards
- Install PowerPivot in Excel
- Create data models with PowerPivot
- Create PowerPivots
- Perform advanced data analysis and visualization
- Connecting to data sources with queries
- Data cleaning, preparation and combine
- Shaping and transforming data
- Planning a report
- Summarizing the analysis and presenting a report
- Incorporating Excel results in Powerpoint
- Presenting in PowerPoint
- Dos and Don’ts of a good presentation
- Keys of effective presentation
There is no prior knowledge of Excel, data analytics, or any other topic is assumed. However, one must know how to use a computer and have a little exposure to using Word, Excel, or PowerPoint.
