Essentials Of Microsoft Excel - Beginner To Advanced

Fundamental step-by-step, format worksheets, tables, functions, charts, pivot table, conditional format, import, macros

What you'll learn

Learn the essentials of Microsoft Excel

Hands-on practice files while watching lectures

Learn at your own pace with concise lectures

Create charts, reports, Pivot Table, Power Pivot, Power Query import, Insert slicers and time lines,

Learn common functions to simplify tasks, dive into database functions, HLOOKUP, VLOOKUP, MATCH, XMATCH, XLOOKUP and more.

Use AI - ChatGPT to create sample datasets, charts

Create What-If Analysis: goal seek, data table, scenario manager, automate tasks with macros, shortcuts

Customize ribbon, quick access toolbar, status bar, link worksheets, protect worksheets, understand relative and absolute referencing

Conditional formatting, create data validation, named ranges, link worksheets, consolidate, grouping, format painter, printing techniques

Format table and borders, create template, insert shapes, images, smart arts, format dates, freeze pane, filtering, sorting, flash fill, texts to columns

Build a solid foundation of Excel to advance your career

Requirements

No experience or previous knowledge needed

Learn quickly and at your own pace

Work with Microsoft Excel 365, 2021, 2019, 2016, older versions will also work with the basics

Short lectures to keep you focused and accompanied exercises for you to practice while watching

Description

This course takes you from beginner to advanced. The course consists of short and concise lectures to keep you focused and engaged. Each lecture has accompanied lecture files for you to practice along. You learn your best with practice and complex subjects will sink in.My objective is for you to grab the fundamental. You learn practical uses of Excel to apply immediately.You'll learn the following: Navigate around Excel workbook, worksheets, the ribbon, quick access tool bar, status barEnter and edit data, move, copy, cut, indent, align, zoomFormat painterAbsolute and relative referencingPrinting set up and sharingData tools for sorting, filtering, totaling, highlight duplicates, uniqueCreate chartsCreate tablesCreate templatesFormat as tableFormat datesShortcutsNamed constant, named rangeData validationText to columnsFlash fillGrouping, consolidateNamed rangeDynamic named rangeSortingWhat-If Analysis with goal seek, data tables, scenario managerUse Power Query to import from text/csv, get-dataFormat as table, auto fill, merge cells, adjust rows and columns, insert bordersFreeze pane, conditional format, Insert shapes and images, templatesLink worksheetProtect worksheetText functions: OFFSET, CONCATENATE, IF, SEARCH, FIND, REPLACE, SUBSTITUTE, REPLACE, LEN, TRIM, FILTERLOWER, UPPER, PROPER, REPT, RIGHT, LEFT,Conditional functions: IF, SEARCH, CONCATENATEIF, FIND DUPLICATES with IF COUNTIF, SUMIF, SUMIFS, COUNTIF,COUNTIFS, COUNT, COUNTA, AVERAGEIF, AVERAGEIFS, SUBTOTALAdvanced functions: MATCH, INDEX, HLOOKUP, VLOOKUP, XLOOKUP, XMATCHDatabase functions: DSUM, DCOUNT, DCOUNTA, DAVERAGECreate from selectionFormula auditingPivot table, Pivot chart, Power Pivot, KPI, slicersCreate sample practice files with ChatGPTMacrosAnd moreWhat you get for enrolling:Risk-free 30-day money-back guaranteeLifetime access that you can review any timeAccess Q&A to get helpCertificate of completion to add to your career pathPractice files are included with the lectures for you to practice while watching. You'll gain confidence and apply what you learn immediately. Excel can be useful for works and promotion or your own business. Start learning today!

Overview

Section 1: Introduction

Lecture 1 Introduction

Section 2: Getting started

Lecture 2 Open Excel

Lecture 3 Overview of Home, New, Open, Accounts

Lecture 4 Ribbon and Quick Access Toolbar

Lecture 5 Customize the Ribbon

Lecture 6 Save Options

Lecture 7 Worksheets

Lecture 8 Cells - Rows - Columns

Lecture 9 Workbook Layout

Lecture 10 Shortcuts

Section 3: Basic Formatting

Lecture 11 Insert, Delete, Backspace

Lecture 12 Entering texts in cells

Lecture 13 Formatting texts and cells

Lecture 14 Indenting and aligning

Lecture 15 Format Painter

Lecture 16 AutoFill

Lecture 17 AutoFill Table

Lecture 18 Merge Cells

Lecture 19 Row Column Width

Lecture 20 Rows and Columns, Insert, Copy, Move, Delete, Hide

Lecture 21 Insert Borders

Lecture 22 Absolute and Relative Referencing

Section 4: Formatting Data

Lecture 23 Custom Sort

Lecture 24 Filter

Lecture 25 Named Constant

Lecture 26 Find Duplicates and Uniques

Lecture 27 Data Validation

Lecture 28 Text to Columns

Lecture 29 Flash Fill

Lecture 30 Consolidate using SUM

Lecture 31 Exercise - Consolidate using Average

Lecture 32 Grouping

Lecture 33 Named Range

Lecture 34 Create from Selection

Section 5: Basic Math

Lecture 35 Add, Subtract, Multiply, Divide

Lecture 36 SUM

Lecture 37 Average

Section 6: Format Table

Lecture 38 Freeze Pane

Lecture 39 Table Formulas

Lecture 40 Currency

Lecture 41 Percentage

Lecture 42 Table and Borders

Lecture 43 Format Date

Lecture 44 Conditional Formatting

Lecture 45 Insert Shapes, Images, SmartArt

Lecture 46 Template

Lecture 47 Hide-DivZero

Section 7: Format as Table

Lecture 48 Format as Table

Lecture 49 Dynamic Named Range

Section 8: Creating Charts

Lecture 50 Charts Overview

Lecture 51 Chart Title using the Formula Method

Lecture 52 3D Column Chart - Switch Row and Column - add Arrow

Lecture 53 3D Pie chart - Move Chart

Section 9: Linking Worksheet

Lecture 54 Link Work Sheet

Section 10: Protecting Workbook and worksheets

Lecture 55 Protecting Worksheet and ReadOnly

Lecture 56 Protecting cells

Lecture 57 Protect Selected Sheet

Lecture 58 Protect Workbook Structure

Section 11: Introduction to Import Data with Power Query

Lecture 59 Import From Text

Lecture 60 Get Data

Section 12: Printing

Lecture 61 Printing - Entire Workbook, Active Sheet, Orientation

Lecture 62 Printing - Page Layout, Header and Footer

Lecture 63 Printing - Page Layout, Print Titles, Page Order, Column Headings

Lecture 64 Printing - PDF

Section 13: IF FUNCTIONS

Lecture 65 IF FUNCTION

Lecture 66 IF IS BLANK

Lecture 67 IF AND

Lecture 68 IF OR

Lecture 69 IF NESTED IF

Lecture 70 Exercise - NESTED IF

Section 14: Text Functions

Lecture 71 OFFSET

Lecture 72 AMPERSAND

Lecture 73 CONCATENATE

Lecture 74 REPLACE

Lecture 75 SUBSTITUTE

Lecture 76 LEN, TRIM

Lecture 77 FILTER

Lecture 78 LOWER, UPPER, PROPER

Lecture 79 REPT

Lecture 80 RIGHT, LEFT

Section 15: Creating Data Validation with UNIQUE

Lecture 81 Data Validation with UNIQUE function

Section 16: Auditing Formulas and Worksheets

Lecture 82 Formula Auditing

Section 17: What-If Analysis

Lecture 83 Goal Seek

Lecture 84 Data Table

Lecture 85 Scenario Manager

Section 18: Common Conditional Functions

Lecture 86 SEARCH FIND IFERROR

Lecture 87 FIND DUPLICATES, IF COUNTIF

Lecture 88 CONCATENATE IF

Lecture 89 SUMIF

Lecture 90 SUMIFS

Lecture 91 COUNTIF

Lecture 92 COUNTIFS

Lecture 93 COUNT, COUNTA

Lecture 94 AVERAGEIF

Lecture 95 AVERAGEIFS

Lecture 96 SUBTOTAL

Section 19: Databases Functions

Lecture 97 DSUM

Lecture 98 DSUM - Two Categories

Lecture 99 DSUM - Two Selections

Lecture 100 DCOUNT, DCOUNTA

Lecture 101 DCOUNTA Multiple Criteria

Lecture 102 DAVERAGE

Section 20: Lookup Functions

Lecture 103 MATCH, INDEX

Lecture 104 VLOOKUP

Lecture 105 HLOOKUP

Lecture 106 Data Validation with VLOOKUP

Section 21: X Functions

Lecture 107 XLOOKUP as VLOOKUP

Lecture 108 XLOOKUP as VLOOKUP with wild card

Lecture 109 XLOOKUP as HLOOKUP

Lecture 110 XMATCH

Section 22: Pivot Tables

Lecture 111 Introduction to Pivot Table

Lecture 112 Overview of the Pivot Table

Lecture 113 SUM and Refresh

Lecture 114 Design Pivot Table, Rows, Values

Lecture 115 Format as Table

Lecture 116 Pivot Table Values

Lecture 117 Pivot Chart

Section 23: Power Pivot

Lecture 118 Introduction to Power Pivot

Lecture 119 Import from Other Sources

Lecture 120 Create Relationship

Lecture 121 Data Set - Pivot Table

Lecture 122 KPI

Section 24: Macro

Lecture 123 Macro Recording

Lecture 124 Create a Macro Button

Lecture 125 Create Macro using Icon

Lecture 126 Run Macro from Quick Access Toolbar

Section 25: Creating Sample Practice Files with ChatGPT

Lecture 127 Creating Sample Files with ChatGPT

Section 26: Wrapping Up

Lecture 128 Congratulation!

Anyone who wants to have a good fundamental of Excel,To be productive at works, schools or self-improvement,Promotional opportunities,Anyone who wants to take the skill to a high level