Pro Mastering Advanced Excel - Formulas, Functions & Charts

Explore the basics of formula writing in Excel, including what a formula is, the equals sign, syntax, arguments, operators, cell references, and function library categories.

Learn how relative addressing shifts references when copying formulas, and how absolute addressing locks rows or columns with the dollar sign (A$1, $A1, $A$1).

Recognize common Excel formula errors like #N/A, #DIV/0!, #NAME?, #NUM!, #REF!, #VALUE!, and column width issues, and learn how warnings help prevent incorrect calculations.

Master the if function and other logical functions in Excel for conditional scenarios. Apply three-argument structure, value if true or false, nested conditions, and basic error handling.

Learn to calculate overtime pay in excel using an if formula to cap hours at each individual's ceiling, apply a 1.4 coefficient, and 90% after tax, with a min-based alternative.

Learn how the and function in Excel evaluates multiple conditions, returning true only when all are met, and how to combine it with other functions to compute discounts by tonnage.

Master the or function in Excel to evaluate multiple conditions and return true when any condition is met. Use or with other functions, illustrated by performance score examples.

Master the iferror function in Excel to control errors in formulas, returning a specified value when errors occur and otherwise showing the formula result, alongside the if, and, or functions.

Explore conditional formatting with highlight cells rules to color cells above 10% in green and below 8 in red, using the home tab in Excel.

Learn to identify the highest and lowest weekly costs over a year using conditional formatting, max and min functions, and display options like data bars, color scales, and icon sets.

Learn to remove conditional formats in Excel by using clear rules on selected cells or the entire sheet, ensuring you delete formats without altering cell data.

Create a new user formula rule in conditional formatting to color and strike through cells in the status column when a check mark is entered.

Implement automatic formatting for new rows in an Excel table with a user formula rule; use absolute reference $A$2 and a condition greater than zero to apply color and borders.

Explore how to create a gantt chart in excel by using a formula to mark days from start to end, set absolute references, and apply conditional formatting to color cells.

Master text functions in Excel, starting with left, right, and mid, to extract characters from text strings by length or position, with practical examples from a sample cell.

Use the Len function to count characters in a cell or text, returning length and including spaces and symbols; literals in quotes, numbers counted without quotes and without number format.

Learn how to use concatenate to join text and values, note numbers convert to text when joined, and why Excel 2019 prefers concat with alternatives like and operator.

Master the replace and substitute functions in Excel. Apply their four arguments to modify text and note case sensitivity in practical examples.

Explore practical text function examples in Excel, removing leading zeros and dashes from numbers using pattern methods, find and replace, and a combined substitute approach.

Using the rept function, the lecture demonstrates how to repeat a character with text and times as two parameters to pad numbers to 12 digits with asterisks dynamically.

Show how the REPT function pads an account number to ten digits and an amount to fifteen digits with zeros, using LEN to compute padding and generate a 15-digit code.

Master the find function to locate a character or substring in text, using find_text, within_text, and an optional start_num, then extract results with left, right, and mid.

Explore how the text function converts numbers, dates, or times into formatted text, with two parameters: value and format, to control display while preserving numeric properties.

Convert text values to numbers with the value function in Excel. Learn to handle strings, dates, and times, and convert outputs of text functions into numeric properties.

Master the trim function to remove leading, trailing, and internal spaces, and review related Excel text functions such as left, right, mid, len, concatenate, replace, and substitute.

Master Excel hyperlinks with the insert hyperlink dialog, linking to web pages, files, or bookmarks. Adjust text to display and screen tips for clear link hints.

Master the hyperlink function in Excel to create and manage many links with a dynamic link location tied to a cell and a meaningful friendly name displayed in the cell.

Master editing and deleting hyperlinks in Excel by using the Insert Hyperlink dialog, right-clicking to edit text or location, adjusting hyperlink formulas, and removing or clearing link content.

Master sumif and countif functions as conditional tools, using a range, criteria, and sum range to total salaries by contract type; countif then counts records meeting the condition.

Learn how to use sumifs and countifs for conditional sums and counts with multiple criteria, including up to 127 criterion pairs; apply to salaries by gender and contract type.

Learn how the count function tallies numeric values, dates, and times as serial numbers with cell references. Use counta for non-empty cells, and count blank for empty cells.

Learn practical Excel formulas using countif and if to mark repeated names with an asterisk, then compare methods with sales data to display stars.

Learn to apply limit conditions with sumifs to sum based on two criteria, creating dynamic ranges using greater than, less than, and inclusive bounds.

Explore limit conditions in Excel by using Countif, Sumif, Averageif, Averageifs, Sumifs, and Countifs to analyze birth year and salary data, with single and multiple criteria.

Master the vlookup function, Excel’s vertical lookup, to retrieve data from the first column of a table. Learn about duplicates, case insensitivity, and forward search with exact or approximate results.

Explore a practical vlookup example to fetch a person’s name from a code in a data table using vertical search, the table array, column index number, and exact search.

Master vlookup errors in Excel, as part of the pro mastering advanced excel course, by diagnosing #N/A, #REF!, and #VALUE! issues with practical examples like missing codes and invalid references.

Learn the hlookup function for horizontal lookups across the first row, returning a value from a specified row using the lookup value, table array, and row index.

Master the lookup function in Excel, exploring vector and array modes, configuring lookup value, lookup vector (ascending), and result vector, and noting array mode limits and the Vlookup/Hlookup preference.

Explore the lookup function as an alternative to vlookup and hlookup, using vector and array modes to search from first column to last column and return matching results.

Use the row function and the column (colon) function as counters to update Vlookup references when copying formulas, including absolute references, to get correct results.

Explore row and counta techniques to power vlookup across tables, using absolute references and copy down, while recognizing how match will locate positions in unsorted data.

Learn the match function to locate a value in a range in Excel, return position with exact (zero) or approximate (one or minus one) matches, and handle errors with IFERROR.

Master excel lookups by solving a two-condition match with Vlookup, handle duplicates by merging cells to a unique key, and copy formulas across and down with absolute references.

Explore wildcard characters in Excel, using asterisk for many characters and question mark for a single character, and apply the sumif function to total 2022 codes and three-digit product codes.

Master chart principles, types, and composite charts in Excel, and learn to insert and customize charts. Configure axes, titles, data labels, and legend for clear visuals.

Select data and headings to draw an Excel chart, use the insert tab or alt+f1/f11 for creation, and ensure the first column is text and next contains numbers for axes.

Explore Excel chart types and categories, from column charts to pie charts, using the charts dialog launcher; learn when simple charts suffice or when composite charts enhance dashboards.

Learn to create bar, line, and area charts in Excel, including 3d options, markers, color and transparency adjustments, and how to visualize monthly expenses over time.

Create and customize pie charts in Excel to show category names and percentages with data labels, and explore pie of pie and bar of pie for small slices.

Master radar or spider charts in Excel to compare multiple indicators, using simple radar, radar with marker, and field marker styles from the insert tab.

Master surface charts in Excel to visualize data with three variables on x, y, and z axes, adjust 3D rotation and axis titles for clearer interpretation.

Scatter charts show the relationship between two numerical variables and data dispersion; learn to create, adjust axes, add data labels, and distinguish scatter charts from line charts.

Explore the bubble chart in Excel, a three-axis scatter visualization where bubble size represents a third dimension. Learn to create, format axes, adjust minimums, and label bubbles with product names.

Master how to create and customize a histogram chart to analyze score distributions, including bin width, number of bins, format axis options, and overflow/underflow handling.

Master treemap charts that display data with large and small squares to show importance and classification, with red for negative and green for positive, in Excel for stock market analysis.

Learn to create and customize a funnel chart in Excel to show data declining through steps, using two-column data, selecting the area, inserting the chart, and adjusting colors.

Explore sunburst charts, a circular tree visualization that uses rings to show hierarchical levels, display equal-sized sections for organization charts, and compare with treemap and pie of pie charts.

Master advanced Excel charts by building combo charts that pair line and column plots on a single axis, customize titles and labels, and explore box and whisker charts for statistics.

Learn to edit and format charts in Excel using the chart design and formats tabs, chart elements, styles, and filters, and to change chart types and copy charts.

Learn to format chart axes with the format axis dialog: set min and max, adjust scale and intervals, apply a logarithmic scale, reverse order, and rotate axis labels.

Learn to protect sheets and workbooks with passwords from the review tab, customize locked/unlocked cell behavior, hide formulas, and use unprotect to remove protection.

Learn to protect a workbook by encrypting with a password and using protect workbook options to lock structure and window settings, preventing sheet changes and requiring a password to open.

Learn to encrypt part of an excel sheet by using the allow users to edit ranges feature to designate A1 to A5, then protect the sheet with a password.

Master data grouping in Excel by grouping rows or columns, collapsing groups to save space, and ungrouping when needed using the data tab and outline settings.

Learn to use the subtotal function to group sales by month, sum the number of sales, and manage subtotals in Excel.

Learn to remove duplicate data in Excel, including duplicate product codes in column A, using the data tab's remove duplicate option with headers checked.