MS Excel Mastery: Analyze, Automate & Visualize Data

This lecture covers the BEDMAS acronym, which stands for Brackets, Exponents, Division and Multiplication, Addition and Subtraction. You’ll learn how to apply this order of operations in Excel formulas to ensure accurate calculations.

Learn how to perform basic mathematical operations in Excel without using built-in functions. This lecture focuses on using arithmetic operators (such as +, -, *, /) directly in formulas to carry out calculations.

Continuing from the previous lecture, this session delves into more advanced calculations using arithmetic operators. You’ll explore complex formulas and combinations of operations to solve various problems.

This lecture introduces the IF function, a powerful tool for making logical comparisons in Excel. You’ll learn how to use the IF function to test conditions and return specific values based on the outcome of these tests.

Building on the previous lecture, you’ll explore advanced uses of the IF function, including nested IF statements and combining IF with other functions to handle more complex scenarios.

This lecture covers how to import data from external sources into Excel and perform calculations on the imported data. You’ll learn about various import methods and how to manipulate the data once it’s in your workbook.

Learn about the COUNT function, which is used to count the number of cells that contain numerical values. This lecture explains how to use this function effectively for summarizing data.

Explore the COUNTIF function, which counts the number of cells that meet a specific condition. This lecture provides examples of how to use COUNTIF to perform conditional counting based on criteria.

This lecture focuses on the SUM function, which adds up a range of numbers. You’ll learn how to use SUM to quickly calculate totals and understand its role in summarizing data.

Learn how to use the AVERAGE function to calculate the mean of a set of numbers. This lecture explains how to apply the function to find averages and interpret the results in the context of your data.

This session covers the AVERAGEIFS function, which calculates the average of cells that meet multiple criteria. You’ll learn how to apply this function to perform conditional averaging based on specified conditions.

Discover how to use the MAX function to find the highest value in a range of cells. This lecture explains the application of the function to identify maximum values and analyze data.

Learn how to use the MIN function to find the lowest value in a set of data. This lecture covers the function’s usage and its importance in data analysis.

This lecture explains how to calculate proportions and update calculations dynamically in Excel. You’ll explore methods to determine ratios and how to adjust calculations as data changes.

Learn about conditional formatting, a feature that allows you to apply formatting based on cell values or conditions. This lecture covers how to set up rules to change the appearance of cells to highlight important information.

Building on the previous lecture, this session delves into advanced conditional formatting techniques. You’ll learn about using multiple rules, custom formulas, and more complex formatting options to enhance your data visualization.

This lecture introduces the concept of tables in Excel. You’ll learn how to create, manage, and utilize tables for better data organization and analysis, including benefits such as structured referencing and automatic formatting.

Explore various ways to format tables to improve their appearance and readability. This lecture covers table styles, header formatting, and how to apply consistent formatting across table elements.

Learn how to sort and filter data in Excel to organize and analyze your information effectively. This lecture explains how to use Excel’s built-in sorting and filtering features to manage large datasets and extract meaningful insights.

This session covers how to use the Freeze Panes and Split features to manage large worksheets. You’ll learn how to keep specific rows or columns visible while scrolling through your data and how to divide the worksheet into multiple panes.

Discover how to set up your Excel worksheets for printing, including adjusting page layout, margins, and print area. This lecture also covers options for printing multiple sheets, adding headers and footers, and previewing your print layout.

This lecture introduces Pivot Tables, a powerful feature in Excel for summarizing and analyzing large datasets. You’ll learn the basics of what Pivot Tables are, how they work, and how to create a basic Pivot Table to quickly organize and aggregate your data.

In this session, you’ll explore how to create custom Pivot Tables tailored to your specific data analysis needs. This lecture covers how to adjust fields, apply filters, and modify layouts to generate insights from your data in a way that suits your requirements.

Learn advanced techniques for organizing Pivot Tables to enhance data analysis. This lecture focuses on how to group, sort, and format data within Pivot Tables, and how to use additional features like slicers and timelines to improve data presentation and interactivity.

This lecture covers the basics of creating and customizing Column Charts in Excel. You’ll learn how to use Column Charts to display data in vertical bars, making it easy to compare values across categories. Topics include selecting data, choosing chart styles, and formatting columns to enhance visual appeal.

Explore Bar Charts in this lecture, which are similar to Column Charts but display data in horizontal bars. You’ll learn how to create Bar Charts to compare different categories more effectively, especially when dealing with long category names. The session covers customization options and how to adjust bar widths and colors.

This lecture focuses on Line Charts, which are ideal for showing trends over time or continuous data. You’ll learn how to create Line Charts, add data series, and customize the lines to highlight trends and patterns in your data. The lecture also covers how to format markers and lines for better clarity.

Learn how to create and customize Pie Charts, which are used to show the proportions of a whole. This lecture explains how to represent data as slices of a pie, making it easy to visualize percentage contributions. You’ll also explore how to format the pie, add labels, and enhance the chart’s readability.

In this lecture, you’ll discover how to create Map Charts to visualize geographic data. You’ll learn how to use Map Charts to display data by regions or countries, including how to format maps and add data layers to show geographical distributions and patterns.

This lecture introduces you to the most frequently encountered errors in Excel, such as #DIV/0!, #VALUE!, #REF!, #NAME?, and #NUM!. You’ll learn what each error means, how it typically occurs, and basic strategies to troubleshoot and resolve these issues.

Building on the previous lecture, this session delves deeper into handling and fixing more complex Excel errors. You’ll explore advanced error-checking techniques, such as using the Error Checking tool, auditing formulas, and applying best practices to prevent errors from occurring in your spreadsheets.

This lecture covers the IF function, which allows you to perform logical tests and return different values based on whether the test evaluates to TRUE or FALSE. You'll learn how to set up basic IF statements to make decisions within your spreadsheets.

Explore the AND, OR, and XOR logical functions in this session. These functions help you create more complex logical tests by combining multiple conditions. You’ll learn how to use these functions to build advanced logical formulas.

This lecture introduces the IFS function, which allows for multiple conditions and outcomes without the need for nested IF statements. The session provides a detailed example to show how to apply the IFS function in practical scenarios.

Continuing from the previous lecture, this session offers another example of using the IFS function. You’ll explore a different application to deepen your understanding of handling multiple criteria and outputs in your formulas.

Learn about the SWITCH function, which provides an alternative to nested IF statements for handling multiple conditions. This lecture explains how to use SWITCH to compare a single value against a list of values and return corresponding results.

This session covers additional logical functions in Excel, such as NOT, TRUE, and FALSE. You’ll learn how these functions can be used in conjunction with other logical functions to build more sophisticated formulas.

Explore various information functions that help you check the type or status of data in your cells. This lecture covers ISBLANK, ISERR, ISERROR, ISEVEN, ISODD, and ISTEXT functions, showing how to use them to validate and manage data.

This lecture introduces basic date functions, including TODAY and DAY. You’ll learn how to use these functions to insert the current date and extract day information from a date value.

Learn how to work with dates more comprehensively using the MONTH, YEAR, WEEKDAY, and WEEKNUM functions. This session covers how to extract specific parts of a date and determine the week number, which can be useful for scheduling and reporting.

xplore the EDATE and EOMONTH functions, which are used to calculate dates that are a specific number of months before or after a given date. This lecture explains how to use these functions for financial and scheduling calculations.

This lecture covers the YEARFRAC function, which calculates the fraction of the year represented by the number of complete days between two dates. You’ll learn how to use this function for age calculations, investment analysis, and more.

Learn how to combine text from multiple cells using CONCAT and TEXTJOIN functions. This lecture demonstrates how to join text strings with specified delimiters and handle data concatenation efficiently.

This session introduces text manipulation functions such as LEN, FIND, LEFT, RIGHT, and TRIM. You’ll learn how to measure text length, locate substrings, extract characters, and clean up text data.

Explore the UNIQUE and TRANSPOSE functions to manage and transform data. This lecture explains how to use UNIQUE to remove duplicates and TRANSPOSE to switch data between rows and columns.

This lecture covers the TEXT function, which allows you to format numbers and dates as text in specific formats. You’ll learn how to use TEXT to display data in a readable and customized format.

Learn about the CHOOSE function, which lets you select a value from a list based on an index number. This lecture explains how to use CHOOSE to make dynamic selections and simplify complex formulas.

Continuing from the previous lecture, this session delves deeper into advanced uses of the CHOOSE function. You’ll explore additional examples and applications to maximize the function’s potential in your formulas.

This lecture introduces the VLOOKUP function, a powerful tool for searching a table or range by row. You’ll learn how to use VLOOKUP to find and retrieve data from a specified column in a table based on a lookup value.

Explore the HLOOKUP function, which searches for data in a table by looking up values horizontally. This lecture covers how to use HLOOKUP to find data in rows and retrieve corresponding information from a specified row in the table.

This session focuses on the INDEX function, which returns the value of a cell in a specified row and column within a given range. You’ll learn how to use INDEX to perform advanced lookups and retrieve data based on row and column numbers.

Learn about the MATCH function, which finds the position of a specified value within a range. This lecture demonstrates how to use MATCH to locate data within a list and how it can be combined with other functions like INDEX for more powerful lookups.

This lecture covers the XLOOKUP and XMATCH functions, which are more advanced and versatile alternatives to VLOOKUP and HLOOKUP. You’ll learn how to use XLOOKUP to perform lookups both vertically and horizontally and how XMATCH can be used to find positions of values with greater flexibility.

This lecture introduces the OFFSET function, which returns a reference to a range that is a specified number of rows and columns from a starting cell. You’ll learn how to use OFFSET to dynamically reference and manipulate data in your worksheets.

Explore practical applications of the OFFSET function by calculating total and average scores. This lecture demonstrates how to use OFFSET to create dynamic ranges for aggregating and averaging data, making your calculations flexible and adaptable to changing datasets.

This session shows how to use the OFFSET function to identify the highest and lowest scores in a dataset. You’ll learn how to combine OFFSET with other functions to dynamically determine and analyze extreme values in your data.

Learn how to apply the OFFSET function to analyze sales data from a yearly perspective down to monthly details. This lecture covers how to create dynamic ranges that adjust to different time periods and provide insights into monthly sales performance.

This lecture focuses on using the OFFSET function for analyzing sales data on a quarterly and half-month basis. You’ll explore how to set up dynamic ranges that accommodate different time intervals, enabling detailed sales analysis across various periods.

This lecture covers how to assign names to individual cells in Excel. You’ll learn the benefits of naming cells for easier reference in formulas and functions, and how to manage and use these names effectively.

Explore the process of naming ranges of cells in Excel. This lecture explains how to create named ranges to simplify formula creation, enhance readability, and make data management more intuitive.

Learn how to use the Text to Columns feature to split data from one column into multiple columns based on a delimiter or fixed width. This lecture demonstrates how to handle data that needs to be separated for better analysis or reporting.

This session covers how to group worksheets into tabs and use the Subtotal function to organize and summarize data within a group. You’ll learn how to apply grouping and subtotals to streamline data management and reporting.

Discover how to create and manage hyperlinks in Excel. This lecture explains how to link to other cells, worksheets, external documents, or websites, and how to use hyperlinks to navigate and connect different parts of your workbook.

This lecture introduces Data Validation, a feature that restricts the type of data that can be entered into a cell. You’ll learn how to set up validation rules to ensure data accuracy and consistency in your worksheets.

Continuing from the previous lecture, this session explores advanced data validation techniques. You’ll learn how to create complex validation rules, use custom formulas, and apply validation across multiple cells or ranges.

Learn how to create custom data validation rules tailored to your specific needs. This lecture covers how to use formulas and criteria to enforce unique data entry requirements and ensure high data quality.

This session covers methods for generating random numbers in Excel using functions like RAND and RANDBETWEEN. You’ll learn how to create random values for simulations, sampling, or other applications where randomness is required.

Explore how to create and manage custom views in Excel. This lecture explains how to set up different views of your data, such as hiding or displaying specific columns and rows, and saving these views for quick access.

Learn how to protect your Excel worksheets and workbooks to prevent unauthorized changes. This lecture covers options for setting passwords, restricting editing, and safeguarding your data from accidental or intentional modifications.

This lecture delves into advanced features of PivotTables, including complex calculations, multiple value fields, and advanced filtering. You’ll learn how to enhance your PivotTable reports with more sophisticated data analysis techniques.

Building on the previous lecture, this session explores additional advanced features of PivotTables. You’ll discover how to use custom calculations, advanced sorting, and grouping to create more detailed and insightful reports.

Learn about Slicers and Timelines, which are interactive tools for filtering data in PivotTables and charts. This lecture demonstrates how to use these features to enhance data visualization and make it easier to analyze data across different time periods.

This lecture introduces Solver, an optimization tool in Excel used for finding the best solution to a problem with multiple constraints. You’ll learn how to set up and use Solver for complex calculations and decision-making scenarios.

Explore Data Tables, a feature used for performing sensitivity analysis by creating a table of results based on different input values. This lecture shows how to set up Data Tables to analyze how changes in variables affect your calculations.

Continuing from the previous lecture, this session covers advanced techniques for using Data Tables. You’ll learn how to create and interpret complex tables and use them to gain deeper insights into your data.

Learn how to use the Goal Seek tool to find the input value needed to achieve a desired result. This lecture demonstrates how to set up and use Goal Seek for solving problems where you need to determine the required inputs to meet specific goals.

This lecture introduces Combo Charts, which combine two or more chart types in a single chart to display different types of data together. You’ll learn how to create and customize Combo Charts to enhance data visualization.

Building on the previous lecture, this session explores advanced techniques for working with Combo Charts. You’ll learn how to fine-tune chart elements, adjust axes, and use advanced formatting options to improve your chart’s effectiveness.

Discover Sparklines, which are tiny charts embedded within cells to provide a visual summary of data trends. This lecture explains how to create and customize Sparklines to quickly visualize data patterns and trends within your worksheet.

Learn how to create Waterfall Charts to visualize incremental changes in data. This lecture covers how to use Waterfall Charts to show the cumulative effect of sequential data points and analyze financial and performance data.

This session introduces Box Charts (Box-and-Whisker Plots), which are used to display the distribution of data based on quartiles. You’ll learn how to create Box Charts to analyze data spread, detect outliers, and understand data variability.

Explore Whisker Charts, which are similar to Box Charts but focus on visualizing data spread with additional detail on whiskers representing data ranges. This lecture shows how to use Whisker Charts to provide a clearer picture of data dispersion.

Learn about TreeMap and Sunburst Charts, which are used for visualizing hierarchical data. This lecture explains how to create and use these charts to represent data structures and relationships in a visually intuitive way.

This lecture covers Radar Charts, which are used to compare multiple variables across different categories. You’ll learn how to create Radar Charts to visualize multi-dimensional data and analyze performance across various metrics.

Discover how to use Scroll Bars in Excel to create interactive controls for adjusting values dynamically. This lecture shows how to insert and configure Scroll Bars to enhance user interactivity and data visualization.

Learn about Spin Buttons and Radio Buttons, which are form controls that allow users to select or adjust values easily. This lecture demonstrates how to use these controls to create interactive and user-friendly spreadsheets.

Explore how to apply form controls in Excel to enhance your worksheets. This lecture covers various types of form controls, including checkboxes, combo boxes, and list boxes, and how to use them to create interactive data forms.

Continuing from the previous lecture, this session delves deeper into advanced applications of form controls. You’ll learn how to customize controls, link them to data, and use them to build sophisticated user interfaces.

Learn how to create and use Data Entry Forms to simplify data input tasks. This lecture covers how to set up a form to streamline data entry processes, making it easier for users to input and manage data in your Excel worksheets.