Master Excel From Basic To Advance

Excel in 2023 - learn the power of every aspect of Excel from data entry to functions to pivot tables and M-code.

What you'll learn

Students will learn a basic to avdanced comprehenshion and usage of the Excel interface. They will know who to use the commands and manipulate the basic ribbon.

Students will also learn a basic to advanced comprehension and usage of a large array of formulas and functions.

Students will learn basic to advanced programming using VB Excel.

Students will learn basic to advanced Power Query and M-code.

Students will learn about ActiveX, Form objects and the usage of the Excel Forms

Students will learn how to use the basic default commands on the default Excel ribbon.

Requirements

Students should have a very basic exposure to Excel. Some minor exposure to programming would be helpful but the programming aspect of the course is geared towards a novice.

Description

Hi, and welcome on behalf of all of us here at Xcell’N Excel. Our team has put together an ongoing and current course on the major aspects of Excel. In this course, you will learn:· Basic data entry - learn simple and advanced data input and keystrokes· Formatting - learn how to affect the font, styles, border colors and sizes.· Functions - learn the power of Excel through basic to advanced inbuilt functions.· Dynamic Arrays – learn about the power of this new technology for performing calculations.· Pivot Tables - learn how to summarize and analyze data.· Power Query - learn how to transform and connect to different data forms.· Printing – learn the seeming mysteries of printing documents.· VBA - learn the basics of VBA coding· Charts - insert charts of different formats for professional visual presentation of your data.· M-Code – learn the basics of this robust language that powers Power Query· and a lot more.Our course will be updated on a weekly basis to help keep you up to date.Although we have tried to cater this course to users of 2010 upwards, be aware that some of the material such as Dynamic Arrays will not work with older, legacy versions.Plus, we will be always here to hear what you have to say and help as you grow in your knowledge of the most popular spreadsheet software in the world.

Overview

Section 1: Overview

Lecture 1 Excel Basic Overview

Section 2: Basic Commands

Lecture 2 Section Overview

Lecture 3 Create a New Document

Lecture 4 Keyboard Movements

Lecture 5 Basic Input

Lecture 6 Basic Input 2

Lecture 7 Custom Ribbon

Lecture 8 Quick Access Tool Bar

Section 3: Basic Commands 2

Lecture 9 Section Overview

Lecture 10 Copy, Move, Rename Sheets

Lecture 11 Adjust Column and Row Size

Lecture 12 Insert and Delete Rows and Columns

Lecture 13 Format Painter

Lecture 14 Clipboard

Lecture 15 Cut and Paste

Lecture 16 Copy and Paste 1

Lecture 17 Copy and Paste 2

Lecture 18 Save Options

Lecture 19 Save As/ Save

Section 4: Formatting

Lecture 20 Section Overview

Lecture 21 Font

Lecture 22 Wrap Text

Lecture 23 Border 1

Lecture 24 Borders 2

Lecture 25 Merge Cells

Section 5: Shapes and Texts

Lecture 26 Section Overview

Lecture 27 Shapes

Lecture 28 Shapes 2

Lecture 29 Shapes 3

Lecture 30 Text Box

Section 6: Referencing

Lecture 31 Section Overview

Lecture 32 Relative Referencing

Lecture 33 Absolute Referencing

Lecture 34 Mixed Referencing

Section 7: Tables

Lecture 35 Section Overview

Lecture 36 Tables 1

Lecture 37 Tables 2

Lecture 38 Tables 3

Lecture 39 Slicer

Section 8: Data Tools

Lecture 40 Section Overview

Lecture 41 Consolidate

Lecture 42 Data Validation 1

Lecture 43 Data Validation 2

Lecture 44 Data Form Entry

Lecture 45 Remove Duplicates

Section 9: Conditional Formatting

Lecture 46 Section Overview

Lecture 47 Conditional Formatting 1

Lecture 48 Conditional Formatting 2

Lecture 49 Conditional Formatting 3

Section 10: Find and Replace

Lecture 50 Section Overview

Lecture 51 Find

Lecture 52 Replace

Section 11: Fill

Lecture 53 Section Overview

Lecture 54 Flash Fill 1

Lecture 55 Flash Fill Command

Lecture 56 Create Custom Edit Lists

Section 12: Error Checking

Lecture 57 Section Overview

Lecture 58 Auditing Tools 1

Lecture 59 Auditing Tools 2

Lecture 60 Watch Window

Lecture 61 Synchronous View

Section 13: Printing

Lecture 62 Section Overview

Lecture 63 Printing

Lecture 64 Printing 2

Lecture 65 Printing 3

Lecture 66 Printing 4

Lecture 67 Printing 5 (Header/Footer)

Lecture 68 Printing 6 (Header/Footer) 2

Section 14: Name Ranges

Lecture 69 Section Overview

Lecture 70 Name Ranges

Section 15: Password Protection

Lecture 71 Section Overview

Lecture 72 Password Protect Cells/Sheet

Lecture 73 Password Protect Range

Section 16: Functions

Lecture 74 Section Overview

Lecture 75 BEDMAS -Order of Operation

Lecture 76 Average Function

Lecture 77 Address Function

Lecture 78 Aggregate Function

Lecture 79 Char

Lecture 80 Choose

Lecture 81 Column and Row

Lecture 82 Concat Function

Lecture 83 Count_IF Function

Lecture 84 Count_IFS Function

Lecture 85 Count, CountA & CountBlank

Lecture 86 Double Negative Function

Lecture 87 Exact Function

Lecture 88 Find Function

Lecture 89 Hyperlink Function

Lecture 90 If Function

Lecture 91 Indirect

Lecture 92 Index Function

Lecture 93 IF AND Function

Lecture 94 IF OR Function

Lecture 95 IF_S Function

Lecture 96 Large and Small

Lecture 97 Max_Ifs Function

Lecture 98 Max_Min Functions

Lecture 99 Match Function

Lecture 100 Median Function

Lecture 101 Min_Ifs Function

Lecture 102 Mode Function

Lecture 103 Offset Function

Lecture 104 Power Function

Lecture 105 Rand

Lecture 106 RandBetween

Lecture 107 Rank Function

Lecture 108 RankEQ Tie Breaker

Lecture 109 Replace Function

Lecture 110 Search Function

Lecture 111 Substitute Function

Lecture 112 Sum Function

Lecture 113 Subtotal Function

Lecture 114 Sum_IF Function

Lecture 115 Sum_IFS Function

Lecture 116 SumProduct Function

Lecture 117 Switch Function

Lecture 118 Tranpose Function

Lecture 119 Trim Function

Section 17: Casing And Positioning

Lecture 120 Section Overview

Lecture 121 Upper, Lower and Proper Casing Functions

Lecture 122 Left, Mid, Right Functions

Section 18: Date/Time Functions

Lecture 123 Section Overview

Lecture 124 Today Function

Lecture 125 Datedif Function

Lecture 126 EDate Function

Lecture 127 EOMonth Function

Lecture 128 Date Function

Lecture 129 Day, Month, Year Function

Lecture 130 Networkdays Function

Lecture 131 Basic Time/Date Calculations

Lecture 132 Workday

Section 19: Look Ups

Lecture 133 Section Overview

Lecture 134 Lookup Function

Lecture 135 VLookup Function

Lecture 136 HLookup Function

Lecture 137 Xlookup Function

Lecture 138 Index Match Function

Lecture 139 Index Match 2

Lecture 140 Index Match 3

Lecture 141 Index XMatch Functions

Section 20: Dynamic Array

Lecture 142 Section Overview

Lecture 143 Basic Simple Arrays

Lecture 144 Basic Dynamic Array

Lecture 145 ByCol Function

Lecture 146 ByRow Function

Lecture 147 Filter

Lecture 148 Lambda Function

Lecture 149 Let

Lecture 150 MakeArray

Lecture 151 TextSplit Function

Lecture 152 Sequence Function

Lecture 153 Sort Function

Lecture 154 SortBy Function

Lecture 155 TextJoin Function

Lecture 156 ToCol

Lecture 157 ToRow

Lecture 158 Unique

Lecture 159 WrapCols

Lecture 160 WrapRows

Section 21: Errors

Lecture 161 Section Overview

Lecture 162 Calculation Errors

Lecture 163 Division by Zero

Lecture 164 NA Error

Lecture 165 Null Error

Lecture 166 Name Error

Lecture 167 Spill Error

Lecture 168 Value Error

Lecture 169 Pound Sign Error

Lecture 170 Reference Error

Lecture 171 Circular Reference Error

Section 22: Pivot Tables

Lecture 172 Section Overview

Lecture 173 Pivot Tables Intro

Lecture 174 Pivot Tables 2

Lecture 175 Pivot Table 3

Lecture 176 Pivot Table Option Box

Lecture 177 Pivot Table Option Box 2

Lecture 178 Pivot Table Option Box 3

Lecture 179 Pivot Table Print

Lecture 180 Pivot Table Field Value

Lecture 181 Pivot Table Update

Lecture 182 Pivot Table Calculated Field

Lecture 183 Pivot Table Item

Lecture 184 Pivot Table Data Model

Lecture 185 Pivot Table Dashboard

Lecture 186 Pivot Table Dashboard 2

Section 23: Charts

Lecture 187 Section Overview

Lecture 188 Charts Introduction

Lecture 189 Charts 2

Lecture 190 Charts 3

Lecture 191 Charts 4

Lecture 192 Charts 5

Lecture 193 Charts Update

Lecture 194 Pie Charts

Lecture 195 Sparklines

Lecture 196 Scatter Plot

Lecture 197 Select

Lecture 198 Formatting

Section 24: Power Query

Lecture 199 Section Overview

Lecture 200 Power Query Intro

Lecture 201 Power Query 2

Lecture 202 Power Query 3

Lecture 203 Power Query Split Column

Lecture 204 Power Query 5 GroupBy

Lecture 205 Power Query 6 GroupBy 2

Lecture 206 Power Query Data Types

Lecture 207 Power Query Replace

Lecture 208 Power Query Merge

Lecture 209 Power Query Merge 2

Lecture 210 Power Query Merge 3

Lecture 211 Power Query Merge 4

Lecture 212 Power Query Append

Lecture 213 Power Query Parameters

Lecture 214 Power Query Parameters 2

Lecture 215 Power Query Filters

Lecture 216 Power Query Add Data

Lecture 217 Power Query Transform Rename/Detect

Lecture 218 Power Query Transform Format

Lecture 219 Power Query Transform Delimiter Extract

Lecture 220 Power Query Transform Statistical Functions

Lecture 221 Power Query Transform Date

Lecture 222 Power Query Transform Time

Lecture 223 Power Query Column From Example

Lecture 224 Power Query Invoke Custom Function

Lecture 225 Power Query Conditional Column

Section 25: VBA

Lecture 226 Section Overview

Lecture 227 Install VB

Lecture 228 VB Excel Editor

Lecture 229 VB Macros Settings

Lecture 230 Variable Declarations

Lecture 231 Scope (Public, Private, Dim)

Lecture 232 Sub and Function Procedures

Lecture 233 Sub Procedures & Variable Declarations 2

Lecture 234 Sub Procedures and Variable Declarations 3

Lecture 235 Function Procedures 1

Lecture 236 Function Procedures 2

Lecture 237 VBA Comments

Lecture 238 VB MsgBox

Lecture 239 Calling a Sub Procedure/Function

Lecture 240 Passing Arguments

Lecture 241 VB Excel Input Box

Lecture 242 VB Excel Input Box 2

Lecture 243 Const

Lecture 244 VB Excel Mathematics 1

Lecture 245 VB Excel Comparison Operators

Lecture 246 VBA Logical AND

Lecture 247 VBA Logical OR

Lecture 248 VBA For-Loop

Lecture 249 VBA For-Loop 2

Lecture 250 VBA Select Case

Lecture 251 VBA If Then Else

Lecture 252 VBA If Then ForLoop

Lecture 253 VBA Static

Lecture 254 If Else If

Lecture 255 VBA If_And_Or

Lecture 256 VBA Do While Loop

Lecture 257 VBA Array

Lecture 258 VBA Array with For Loop

Lecture 259 VBA Array/Loop/User Input

Lecture 260 VBA Array Variant

Lecture 261 VBA Redim Dynamic Array

Lecture 262 VBA 2D Array

Lecture 263 VBA Date/Time

Lecture 264 VBA DateValue

Lecture 265 VBA DateValue 2

Lecture 266 VBA DateDiff

Lecture 267 VBA String

Section 26: VBA Class

Lecture 268 Section Overview

Lecture 269 VBA Class

Lecture 270 VBA SubProcedures

Lecture 271 VBA Members

Section 27: VBA Form Controls

Lecture 272 Section Overview

Lecture 273 VBA Form Button

Lecture 274 VBA Form DropDown

Lecture 275 VBA Form Checkbox

Lecture 276 VBA Form Spinner

Lecture 277 VBA Form List Box

Lecture 278 VBA Form Scroll

Section 28: VBA Active X

Lecture 279 Section Overview

Lecture 280 VBA Active X Button

Lecture 281 VBA Active X Combo

Lecture 282 VBA Active X Checkbox

Lecture 283 VBA Active X Listbox

Lecture 284 VBA Active X Textbox

Lecture 285 VBA Active X ScrollBar

Lecture 286 VBA Active X Spinner

Lecture 287 VBA Active X Toggle

Section 29: VBA Range

Lecture 288 Section Overview

Lecture 289 VBA Range 1

Lecture 290 VBA Range 2

Lecture 291 VBA Range Select

Lecture 292 VBA Range Copy and Paste

Lecture 293 VBA Named Range

Lecture 294 VBA Range Copy Paste 2

Lecture 295 VBA Color Range

Lecture 296 VBA Range Loop

Lecture 297 VBA Range Offset

Lecture 298 VBA Range Resize

Lecture 299 VBA Range End

Lecture 300 VBA CurrentRegion

Section 30: Guided Project

Lecture 301 Guided Project Overview

Lecture 302 Guided Project 1

Lecture 303 Guided Project 1 - Part 2

Lecture 304 Guided Project 1 - Part 3

Section 31: M-Code

Lecture 305 Section Overview

Lecture 306 M-Code Intro

Lecture 307 M-Code Primitives

Lecture 308 M-Code Records

Lecture 309 M-Code Tables

Lecture 310 M-Code Tables 2

Lecture 311 M-Code Let

Lecture 312 M-Code Functions

Lecture 313 M-Code Identifiers

Lecture 314 M_Code If Statement

Lecture 315 M-Code List Functions

Lecture 316 M-Code Table Function

Lecture 317 M-Code Table.ColumnCount

Lecture 318 M-Code Table.SelectRows

Lecture 319 M-Code Table.AddColumn

Lecture 320 M-Code Table.AddColumnIndex

Lecture 321 M-Code Table.Combine

Lecture 322 M-Code Table.CombineColumns

Lecture 323 M-Code Table.Contains

Lecture 324 M-Code Table.Distinct

Lecture 325 More to come…

Beginner Excel learner and user.