Excel For Analysts

Master Excel Skills to Elevate Your Data Analysis and Reporting

What you'll learn

Go from beginner to advanced by using Excel to conduct insightful and powerful analysis. You will boost your confidence in using Excel!

Create pivot tables, charts, and dashboards in Excel using dynamic functions like VLOOKUP, HLOOKUP, XLOOKUP.

Use the Maps feature in Excel to analyze customer data by address.

Unearth the true story behind data to effectively explain to your stakeholders what is happening in the business.

Use Excel shortcuts to make data analysis easier and faster.

Use Logic functions like AND, IF, embedded IFs, SUMIFS, AVERAGEIF, COUNTIF + Conditional Formatting, Data Sorting and Filtering.

Use the features of the Data Analytics toolbar including creating a Histogram and conducting Regression Analysis.

Use Data validation to force specific data types to be entered in Excel Sheets.

Use Goal Seek and Scenario Manager to help you run different Scenarios in your Excel Models.

All the information is broken down into simple steps.

You will explore different data sets across industries.

Requirements

Previous experience with Excel would be beneficial but if not you will still learn alot!

Description

Course Description: Excel for AnalystsSection 1: Introduction to Large WorksheetsLecture 1: Introduction (Preview enabled) - Get acquainted with the basics of handling large worksheets in Excel, setting the foundation for advanced data analysis.Lecture 2: Navigating Excel - Learn efficient ways to navigate through large datasets in Excel.Lecture 3: Viewing Data - Techniques for effectively viewing and interpreting data.Lecture 4: Viewing Large Workbooks - Strategies for managing and navigating large Excel workbooks.Lecture 5: Printing Large Workbooks - Master the nuances of printing large and complex Excel workbooks.Lecture 6: Multiple Worksheets - Understand the dynamics of working with multiple worksheets and how to link them effectively.Lecture 7: Formatting and Filtering Data - Learn advanced techniques in formatting and filtering data for clearer analysis.Section 2: FunctionsLecture 8: Functions Overview - An introduction to the vast array of functions available in Excel.Lecture 9: Logic Functions 1 - Dive into IF functions and embedded IF functions.Lecture 10: Logic Functions 2 - Explore SUMIFs, AVERAGEIFs, COUNTIFs, and logical operators like OR, AND, NOT.Lecture 11: Working With Dates - Master the complexities of handling dates in Excel.Lecture 12-14: TEXT Functions Parts 1-3 - A three-part series delving deep into the TEXT functions of Excel.Lecture 15: Absolute Referencing - Understand the importance and application of absolute referencing in Excel.Lecture 16: HLOOKUP, VLOOKUP, and XLOOKUP - Learn the key lookup functions for data analysis.Lecture 17: INDEX + MATCH - Advanced techniques combining INDEX and MATCH functions for sophisticated data retrieval.Section 3: Data ToolsLecture 18: Intro to Data Tools - Introduction to various data tools available in Excel for advanced analysis.Lecture 19: Scenario Manager - Learn to use the Scenario Manager for forecasting and analysis.Lecture 20: Goal Seek - Master the Goal Seek function for solving equations and achieving target values.Lecture 21: Data Validation - Techniques for ensuring data integrity through validation.Lecture 22: Cell References, Trace Precedents and Dependents, and Watch Window - Explore advanced features for tracking and analyzing data relationships.Lecture 23: Formatting Tables - Learn to format tables for better readability and analysis.Lecture 24: Pivot Tables - Comprehensive guide to creating and manipulating pivot tables.Lecture 25: Importing Data from the Web - Techniques for importing web data to create tables and pivot tables.Lecture 26-27: Charts Parts 1 and 2 - A two-part series on creating and customizing charts for data visualization.Lecture 28: Analysis Toolpack - Histograms - Utilize the Analysis Toolpack for creating histograms.Lecture 29: Analysis Toolpack - Regression - Learn regression analysis using Excel's Analysis Toolpack.This course is designed to equip analysts with a comprehensive understanding of Excel's capabilities, ensuring proficiency in data handling, analysis, and reporting.

Overview

Section 1: Introduction to Large Worksheets

Lecture 1 Introduction

Lecture 2 Navigating Excel

Lecture 3 Viewing Data

Lecture 4 Viewing Large Workbooks

Lecture 5 Printing Large Workbooks

Lecture 6 Multiple Worksheets

Lecture 7 Formatting and Filtering Data

Section 2: Functions

Lecture 8 Functions Overview

Lecture 9 Logic Functions 1: IF Functions and Embedded IF Functions

Lecture 10 Logic Functions 2: SUMIFs, AVERAGEIFs, COUNTIFs, OR, AND, NOT

Lecture 11 Working With Dates

Lecture 12 TEXT Functions

Lecture 13 TEXT Functions Part 2

Lecture 14 TEXT Functions Part 3

Lecture 15 Absolute Referencing

Lecture 16 HLOOKUP , VLOOKUP and XLOOKUP

Lecture 17 INDEX + MATCH

Section 3: Data Tools

Lecture 18 Intro to Data Tools

Lecture 19 Scenario Manager

Lecture 20 Goal Seek

Lecture 21 Data Validation

Lecture 22 Cell References, Trace Precedents and Dependents and Watch Window

Lecture 23 Formatting Tables

Lecture 24 Pivot Tables

Lecture 25 Importing Data from the Web to Create Tables and Pivot Tables

Lecture 26 Charts Part 1

Lecture 27 Charts Part 2

Lecture 28 Analysis Toolpack - Histograms

Lecture 29 Analysis Toolpack - Regression

Section 4: Building Dashboards

Lecture 30 Introduction to Dashboards

Lecture 31 Dashboard Setup

Lecture 32 Creating a Dashboard

Lecture 33 Creating Slicers and Conditional Formatting in a Dashboard

Intermediate Data Analysts or Business Owners who are interested in expanding their skills,Managers who wish to provide more insightful reports