Office 365: Advanced Ms Excel Formulas, Functions & Tricks.
Published 4/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English
| Size: 1.77 GB
| Duration: 3h 13m
111 most useful functions like XLOOKUP, TEXT, Count, DATE, nestle formulas and many more.(MS excel 2013 to Office 365)
What you'll learn
Enable students to achieve more with less effort, less time, and less investment, maximizing productivity and efficiency in data management, and reporting task.
Advanced Formulas and Functions: Mastery of complex functions such as XLOOKUP, TEXT, INDEX-MATCH, IFERROR, VSTACK, COUNTIFS, and nested functions.
Macros (VBA) and Automation: Introduction to Visual Basic for Applications (VBA) to automate repetitive tasks. Learn how to inspect VBA codes.
Advanced Data Visualization: Creation of interactive dashboards using Excel's features like slicers, timelines, combo charts, trendlines, and data validation.
Requirements
Access to Excel Software: Access to Microsoft Excel software (preferably the latest version like from 2013,2016,2019 or Office 365) is essential.
Basic Excel Skills: Understanding of fundamental Excel concepts such as entering data, capy data, save file etc.
Consistency & willingness to Learn: Commitment to completing course all contents, assignments and actively participating in course activities, is crucial for maximizing the learning experience.
Description
Discover Excel's 111 most indispensable functions covering a wide spectrum, from logical and mathematical operations to text manipulation, statistical analysis, financial calculations, and nested formulas. Whether you're performing complex data analysis, financial modeling, or simply formatting text, this comprehensive collection equips you with the essential tools to tackle diverse tasks efficiently, making Excel an indispensable tool for professionals across industries.Excel's text manipulation functions including LEFT, RIGHT, UPPER, LOWER, PROPER, SEARCH, and FIND empower users to efficiently extract, modify, and search for text within cells, facilitating precise data manipulation and analysis in spreadsheets. These functions streamline tasks such as text formatting, case conversion, and locating specific text strings, enhancing productivity and accuracy in Excel workflows.Logical functions such as IF, AND, OR, and NOT facilitate decision-making and conditional logic operations, while Financial functions like PV, FV, NPV, and IRR aid in financial analysis, planning, and investment evaluation.Statistical functions in Excel, including AVERAGE, MAX, MIN, and COUNT, provide essential tools for analyzing and summarizing data, enabling users to derive insights and make informed decisions based on numerical information.Information functions such as ISNUMBER, ISBLANK, and ISTEXT help validate and analyze data types and properties, ensuring data accuracy and integrity. Compatibility functions like CONVERT and COMPLEX facilitate smooth data exchange across different systems and units, enhancing interoperability within spreadsheets.
Overview
Section 1: Introduction
Lecture 1 Mindset & Scope of the course
Section 2: Mathematics and trigonometric functions
Lecture 2 Cells relative reference and absolute reference
Lecture 3 1 Functions_Product, ABS & RAND
Lecture 4 2 Rand between, Roman, Round
Lecture 5 3 Subtotal, Sum, Sumif, Sumifs
Lecture 6 4 Sumproduct, Aggregate functions
Section 3: Lookup & Reference functions
Lecture 7 5 Address, Column, Columns, Row, Rows functions
Lecture 8 6 Offset, Transpose, Hyperlink functions
Lecture 9 7 Vlookup, Hlookup
Lecture 10 8 Xlookup, Match, Xmatch
Section 4: Date and time
Lecture 11 10 Date, day, days, month, year and weekdays
Lecture 12 11 Weeknum, time, hour, minute & second
Lecture 13 12 Edate, Eomonth, Networkdays & workday
Section 5: Logical and Financial
Lecture 14 13 And, or, not & if functions
Lecture 15 14 If and, if or, true, false and iferror
Lecture 16 15 Ifs function
Section 6: Text functions
Lecture 17 16 Left, right, upper, proper, lower search & find
Lecture 18 17 Mid, trim, fixed, & concatenate
Lecture 19 18 Text, t, clean, value & textsplit functions
Section 7: Statistical functions
Lecture 20 19 Average, averagea, averageif & averageifs
Lecture 21 20 Count, counta, countif & countifs
Lecture 22 21 Large, max, maxa, maxif, small & Min
Section 8: Information and compatibility functions
Lecture 23 22 Address, col, row, type & width
Lecture 24 23 Directory, numfile, origin & system
Lecture 25 24 Isblank, iserror, isodd, istext & type
Lecture 26 25 Forecast, sequence, torow, tocol, hstack & vstack
Section 9: Excel Short-cut keys
Lecture 27 26 Short cut keys - 1
Lecture 28 27 Short cut keys - 2
Lecture 29 28 Short cut keys - 3
The advanced Excel course is designed for professionals, analysts, and students who already possess a foundational understanding of Excel and seek to enhance their skills to tackle more complex data analysis, manipulation, and reporting tasks. It is ideal for individuals looking to streamline workflows, increase efficiency, and unlock the full potential of Excel for advanced data-driven decision-making in their respective fields.,This course will further enhance your skills by introducing from basic excel knowledge to more advanced concepts like working with functions, data visualization, and basic data manipulation techniques. It's a great way to solidify your existing knowledge and expand your capabilities in using Excel effectively.
Free search engine download: Office 365 Advanced MS Excel Formulas functions tricks
