Advanced Excel Techniques | Conquer Excel On A High Level

Name: Advanced Excel Techniques | Conquer Excel On A High Level
Rating: 4.5 (104 reviews)

Advanced Microsoft excel High Level Techniques : data analyst : Automation : Advanced Formulas and Functions & more!

Created byThe Table Of Bosses, Isabelle Arrieta Support, Isabelle Arrieta

Last updated 8/2023

English

English [Auto],

What you'll learn

Utilize advanced functions and formulas for complex data analysis.
Create dynamic and interactive dashboards for data visualization.
Master data validation techniques to ensure data accuracy and consistency.
Automate tasks using macros and Visual Basic for Applications (VBA).
Implement powerful data analysis tools like PivotTables and Power Query.
Craft sophisticated financial models for budgeting and forecasting.
Merge and analyze large datasets from diverse sources seamlessly.
Optimize Excel performance through efficient formula usage and file management.
Develop advanced charting and graphing techniques for compelling presentations.
Collaborate effectively by using data sharing and protection features.

Course content

7 sections • 23 lectures • 4h 42m total length

1 - Adv Excel Functions13:20
Master advanced Excel functions like Vlookup, Hlookup, index, match, and if to analyze data and automate tasks across worksheets and workbooks.
2 - Data Validation _ Conditional Formatting14:05
Explore data validation and conditional formatting in excel to prevent invalid entries, highlight failing grades and above average scores, and apply advanced custom rules (date, dependent values, length, duplicates).
4 - Filter and Sort10:00
Learn to filter and sort in Excel using quick sort, custom sort, and multi-level rules with headers, to organize data by worldwide and domestic values and criteria.
4 - Filter and Sort part210:21
Sort data with multiple criteria, including representative and location, using ascending order, custom lists, and color; apply advanced filtering to extract, copy to another location using a criteria range.

2.1 - Dynamic and Interactive15:51
Create dynamic, interactive Excel charts using a slider and scroll bar to control year ranges, with index formulas driving the data and a dynamic chart title.
2.2 - Advanced Charting Features16:58
Explore advanced Excel charting techniques to create dynamic data visuals from monthly sales and earnings. Learn to add trend lines, forecasts, growth forecasts, and sparklines for quick insights.
2.3 - Adv Data Visualization Tech15:00
Master advanced data visualization in Excel with conditional formatting, data bars, color scales, and icon sets, and build dynamic dashboards using pivot tables and charts.
2.3 - Adv Data Visualization Tech part215:21
Build interactive Excel dashboards with stacked column and line charts, pivot charts, and timeline slicers to filter by country, product, and month.

3.1 - Macros and VBA Fundamentals9:46
Automate repetitive Excel tasks with macros and VBA, record and edit macros, enable the developer tab, and apply text-to-columns, conditional formatting, and button-triggered automation.
3.2 - Automated Workflows and Processes9:53
Master automated workflows in Excel by recording macros, writing VBA, and handling errors through debugging, to automate repetitive tasks and boost productivity.
3.3 - Conditional Logic and Nested Formulas9:51
Master conditional logic in Excel by building nested if statements with and functions to assign grades from score ranges. Learn to create efficient formulas and drag them across the sheet.

4.1 - Analysis ToolPak for Statistical Analysis13:45
Learn to use Excel's Analysis ToolPak to perform descriptive statistics and regression analysis, enabling you to extract insights with data analysis tools and outputs.
4.2 - What-If Analysis and Scenario Planning11:02
Explore what-if analysis and scenario planning in Excel using scenario manager and goal seek to compare scenarios, optimize December sales, and forecast grand totals.
4.3 - Introduction to Power Query16:55
Master Power Query and Power Pivot in Excel by importing, transforming, and cleaning data from multiple sources, building robust data models, and enabling advanced analysis.
4.4 - Advanced Data Analysis Techniques with Power Pivot12:00
Install Power Pivot in Excel and use Dax to build data models, import data, create relationships, and add measures like total tuition from enrolled courses.
4.4 - Advanced Data Analysis Techniques with Power Pivot part212:22
Develop proficiency in Power Pivot by creating measures such as distinct count and amount owed using related tables and DAX formulas, and visualize results with pivot tables, charts, and slicers.

5.1 - Sharing Workbooks and Collaborating with Others10:23
Learn to share and co-author Excel workbooks in real time with sheet view on OneDrive or SharePoint, and track changes across desktop and web.
5.2 - Protecting and Securing Excel Files14:06
Protect Excel workbooks and worksheets with password encryption and read-only options to safeguard confidentiality and integrity. Set permissions, unlock specific cells, and save or print as PDF to manage access.
5.3 - Customizing Excel with Add-ins and Advanced Settings14:30
Explore how to customize Excel with add-ins and advanced settings, installing tools like QR generator, Wikipedia, Bing Maps, web video player, mini calendar, and Excel colorizer to boost productivity.

6.1 - Applying Advanced Excel Techniques to Real-World Problems9:03
Discover real-world applications of advanced excel techniques by building a bike sales dashboard, cleaning data, creating age brackets, and using dynamic filters and charts to analyze purchasing behavior.
6.2 - Presenting Findings and Insights with Advanced Excel Features15:33
Master presenting findings and insights with advanced Excel features by building pivot tables and charts to create professional reports and dynamic dashboards.

7.1 - Project Planning and Execution4:03
Plan and execute the final Excel project by cleaning, analyzing, and visualizing data. Set milestones, track tasks, and present a city-filtered dashboard for peer feedback.
7.2 - Project Presentation and Feedback8:15
Present your excel analysis with a clear structure, engaging visuals, and storytelling, using slides and consistent design. Communicate findings effectively, simplify data, and incorporate feedback to refine your work.

Requirements

Need a computer to be able to access excel

Description

Welcome to the transformative journey of "Advanced Excel Techniques | Conquer Excel On A High Level." This comprehensive course is designed to propel your Excel proficiency to new heights by delving into intricate functionalities and expert strategies.

Unlock the full potential of Excel as you explore advanced functions, formulas, and techniques tailored for high-level data analysis. Navigate the world of data visualization by creating dynamic dashboards that captivate and communicate insights effectively.

With a focus on productivity, you'll master the art of automation through macros and Visual Basic for Applications (VBA), streamlining repetitive tasks and boosting efficiency. Dive into sophisticated data analysis tools, such as PivotTables and Power Query, to unravel patterns and extract valuable insights from complex datasets.

Financial modeling takes center stage as you learn to construct intricate models for budgeting, forecasting, and decision-making. Seamlessly merge and analyze large datasets from diverse sources, sharpening your skills in handling real-world scenarios.

Optimize Excel performance by understanding formula efficiency, file management, and resource utilization. Elevate your presentations with advanced charting techniques, transforming data into compelling visual stories.

Collaborative prowess is honed through data sharing and protection features, ensuring secure and seamless teamwork. Strengthen problem-solving abilities by tackling intricate challenges rooted in real-world contexts.

Whether you're an analyst, manager, or aspiring Excel expert, this course equips you for success. By journey's end, you'll be prepared to tackle certification exams with confidence, armed with expertise that extends far beyond the basics. Join us in conquering Excel on a high level and elevate your data manipulation and analysis skills to unparalleled heights.

Who this course is for:

This course is for Intermediate to Expert level.

Advanced Excel Techniques | Conquer Excel On A High Level

What you'll learn

Explore related topics

Course content

Section 1_ Data Analysis and Manipulation4 lectures • 48min

Section 2_ Advanced Charting and Visualization4 lectures • 1hr 3min

Section 3_ Automation and Efficiency Techniques3 lectures • 30min

Section 4_ Data Analysis Tools5 lectures • 1hr 6min

Section 5_ Collaborative and Advanced Excel Features3 lectures • 39min

Section 6_ Case Studies and Real-World Applications2 lectures • 25min

Section 7_ Final Project2 lectures • 12min

Requirements

Description

Who this course is for: