Data Management And Analysis With Advance Excel

Learn and Master Excel functions and tools for data analytics. Course compatible with Excel 2007, 2010, 2013 & 2016

What you'll learn

After taking the course the students would be able to do Autofill, Data Validation

Analytical Functions like - Goal Seek, Scenario Manager and Data table

Functions like PMT, Vlookup, Hlookup, Multiple vlookup, Identifying and deleting duplicates

Importing and exporting data, creating subtotal based reports

Pivot tables, Pivot Charts

Functions like count, countif, countifs, sum, sumif,sumifs,

Introduction to basic Macros and Mail Merge

Fun, interactive, and highly effective lessons from a best-selling Excel instructor.

Unique tips, tools and case studies that you won't find in ANY other course.

Lifetime access to reference materials and practice files

Requirements

Microsoft Excel should be installed on your system, this course is compatible with Excel 2007, Excel 2010, Excel 2013, or Excel 2016

Some experience with basic formulas is recommended, but not required.

Description

This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. While most Excel courses focus on simply what each formula does, I would teach you on how to apply the formula and understand its business and analytical application as well.By the end of the course you'll be writing robust, elegant formulas and functions which would help you to analyze data by allowing you to:Understanding basics:AutoFill a SeriesCreating a Custom ListData ToolsData ValidationDrop-Down ListsText to ColumnspmtGoal SeekScenario ManagerData TableVlookuphlookupImporting and Exporting DataIdentifying and deleting duplicates Outline, Sort, Filter and SubtotalIntroductionGroup and Ungroup and subtotalsSubtotalsConsolidate Pivot TablesIntroductionCreating Pivot TablesChoosing FieldsPivot Table LayoutFiltering Pivot TablesModifying Pivot Tables DataPivot ChartsSpecial FunctionsCount / Counta / Countif / CountifsSum /sumif / sumifs Macros Mail merge

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Autofill Data and Creating Custom Lists

Section 2: Data Tools

Lecture 3 Data Validation

Lecture 4 PMT function

Lecture 5 Data Table

Lecture 6 Scenario Manager

Lecture 7 Goal Seek

Lecture 8 Vlookup, Hlookup

Lecture 9 Approximate Vlookup

Lecture 10 Multiple Vlookup

Lecture 11 Identifying and Deleting Duplicates

Section 3: Subtotaling Data

Lecture 12 Data Subtotal

Lecture 13 Data Consolidate

Section 4: Summarizing Data through Pivot Tables

Lecture 14 Branchwise Pivot

Lecture 15 Datewise Pivot

Lecture 16 Count of Account Types Pivot

Lecture 17 Bucketwise Pivot

Lecture 18 Pivot with Conditional Formatting

Lecture 19 Pivot Graphs

Section 5: Special Functions

Lecture 20 Count, Countif and Countifs

Lecture 21 Sum, Sumif and Sumifs

Section 6: Macros and Mail Merge

Lecture 22 Creating and Editing a Simple Data Entry Macro

Lecture 23 How to Mail Merge using Word and Excel

Lecture 24 How to Mail Merge to Email

Section 7: Bonus Lectures - Getting Started with Excel 2016

Lecture 25 Understanding the Excel Interface

Lecture 26 Understanding the Menu System

Lecture 27 Understanding the Quick Access Toolbar

Lecture 28 Understanding Workbooks and Worksheets

Lecture 29 Understanding the Formula Bar

Lecture 30 Understanding the Status Bar

Lecture 31 Understanding the Navigation System of Excel

Lecture 32 Using shortcut menus and the Mini toolbar

Lecture 33 Creating new workbooks

Lecture 34 Using Excel Help

Section 8: Entering Data

Lecture 35 Exploring Data Entry and Editing Techniques

Lecture 36 Entering Data with Autofill

Lecture 37 Understanding Undo and Redo

Lecture 38 Adding Comments

Lecture 39 Using Save and Save As

Section 9: Working with Formulas and Functions

Lecture 40 Creating Simple Formulas

Lecture 41 Copying Formulas to Adjacent Cells

Lecture 42 Calculating Year to Date Totals

Lecture 43 Calculating Percentage

Lecture 44 Working with Relative, Absolute and Mixed Referencing

Lecture 45 Using Sum and Average

Lecture 46 Using other common functions

Section 10: Formatting

Lecture 47 Exploring Font Styles and Effects

Lecture 48 Adjusting Row Heights and Column Widths

Lecture 49 Working with alignment and Wrap Text

Lecture 50 Working with Borders

Lecture 51 Formatting numbers and dates

Lecture 52 Using conditional formatting

Lecture 53 Creating and using tables

Section 11: Data Tools

Lecture 54 Understanding Data Validation

Lecture 55 Understanding Text to Columns

Lecture 56 Financial Function PMT

Lecture 57 Data Table

Lecture 58 Scenario Manager

Lecture 59 Goal Seek

Section 12: Searching and Filling Data

Lecture 60 Autofill Series

Lecture 61 Flash Fill Data

Lecture 62 Searching Data using VLOOKUP (Exact)

Lecture 63 Searching Data using HLOOKUP (Exact)

Lecture 64 Searching Data using VLOOKUP (Approximate)

Section 13: Organizing Data and Subtotaling

Lecture 65 Organizing Data using Data Sort

Lecture 66 Data Subtotal

Lecture 67 Filtering Data using Data Filter

Section 14: Pivot Tables

Lecture 68 Understanding the data and creating Branch wise Pivot

Lecture 69 Creating a Branch wise and opened by Pivot

Lecture 70 Creating a Date wise Pivot

Lecture 71 Creating a Count of Account Types Pivot

Lecture 72 Creating a Bucketing Pivot

Lecture 73 Creating a Conditional Formatting Pivot

Lecture 74 Creating a Graph out of Pivot Data

Section 15: Mail merge

Lecture 75 Merging Excel Data with Word Document

This course is suitable for any individual who wants to go beyond the basics of Excel and learn to master excel.,Anyone hoping to expand their analytics skill set, work more efficiently with data, and take their career to a new level,Students looking for an engaging, hands-on, and highly interactive approach to Excel analytics training