A Beginners approach to Advanced Excel

Learn to create basic formulas in Excel using the BODMAS order of operations, including brackets, power, division, multiplication, addition, and subtraction, with practical cell-based examples.

Explore the SUM function as a group operation to total costs, revenue, and discounts in Excel, using quantity, unit price, and drag down techniques for automatic calculations.

Apply basic group functions in Excel to compute max, min, average, count, and product, extracting extremes, averages, counts, and volumes from data with live summary results.

Learn how to add, edit, and delete comments in Excel, using right-click or keyboard shortcuts like Shift+F2, and customize comment color, size, and font for sales data.

Master the intersect operator in Excel by using a space between a column and a row, then use the sum function to sum intersecting salesman data for April.

Learn how to use autosum in Excel to quickly sum, average, max, min, and count values; select cells, use the autosum dropdown, and understand how blanks or characters affect calculations.

Learn how to use auto fill and fill series in Excel, apply formatting with format painter or fill formatting only, and create serial numbers, dates, weekdays, and custom series.

Discover how flash fill (Ctrl+E) automates data transformations, from extracting first or last characters, middle characters, and years to joining names, creating emails, and applying capitalization.

Learn to create and use custom lists in Excel, including days, months, student names, and departments, by importing lists via file options and using autofill with drag.

Master clear options to delete content, comments, or formatting, and use goto and goto special to navigate to specific cells, locate formulas, constants, errors, and precedents or dependents.

Learn the most common ctrl shortcuts for excel, including copy, paste, cut, bold, italic, underline; undo/redo; find and replace; navigate and select data with ctrl arrows and shift keys.

Explore essential alt key shortcuts and classic ctrl shortcuts for Excel, including hiding rows and columns, showing formulas, copying and filling data, inserting new sheets, and navigating ribbons.

Link data across six excel sheets—travel, food, house, mobile, electricity, and annual expense—by creating a consolidated total that updates automatically when individual sheets change.

Learn how to hide and unhide worksheets, and create, move, and copy sheets within and between workbooks using right-click options, drag-and-drop, and keyboard shortcuts.

Master freeze panes to keep headers and top rows visible while scrolling, and left columns like order id and customer id. Unfreeze panes to adjust view.

Compare data side by side on the same sheet or across two files by opening a new window, arranging vertical views, and seeing changes synchronize between panes.

Learn to use the splitting sheets pane to view four different sections of the same data on one Excel sheet, compare positions, and remove the split when finished.

Learn to link data across multiple Excel workbooks, pull totals from two files, adjust links for visibility, and preserve links by keeping file locations unchanged.

Explore Excel options from file and data settings, including default font and sheet counts, to customize new workbooks. Learn auto save intervals, quick analysis, and data options.

Master paste special in Excel to paste only values, without copying formulas or formatting, and learn how to paste formulas when needed.

Demonstrates using paste special multiply to increase a sales forecast by 10 percent with 1.1, and by 20 percent each quarter with 1.2, while maintaining formulas and formatting.

Learn paste special with maths operators to divide, add, and subtract in Excel, compute averages and percentages for student marks across six subjects, and apply salary changes to target cells.

Master paste special options to normalize column width, merge lists with skip blanks, and copy formatting across cells, enabling efficient data shaping in Excel.

Learn to transpose data in Excel using paste special and the transpose function, turning rows into columns and understanding the differences between independent copies and linked results with matching counts.

Explore creating column charts from insert, choosing between recommended and all charts to visualize data, and customize with 3d or stacked options while keeping charts linked to data.

Learn to create and customize Excel charts using Alt+F1 and F11, explore plot and chart areas, axes, titles, data labels, legend, and chart styles.

Edit charts across types using the design tools to add axis, chart title, data labels, grid lines, legend, and data tables, then refine with trendlines, error bars, and layout options.

Learn to edit charts in Excel by adjusting chart and plot areas, axes, titles, and data series, insert shapes and logos, apply colors and transparency, and manage gaps and overlap.

Learn to create and customize line, bar, and area charts in Excel, including selecting data, adding drop lines, smoothing lines, and correcting axis assignments for accurate data visualization.

Learn how to visualize and compare three products' sales with a stacked area chart, identify which product leads total sales, and adjust the chart to display total and individual contributions.

Learn to create and customize pie and donut charts in Excel, explode slices, adjust hole size, and use pie of a pie for multiple series.

Explore how to create a combo chart in Excel by combining revenue as a line with quantity as cluster columns, using markers for easy identification.

Learn to add a trendline to a column chart in Excel. Forecast the next three months using exponential and linear forecasting and moving averages, with customizable line options.

Learn to handle missing data in graphs by choosing gaps, zero, or connecting datapoints with lines and markers, and adjust data visibility for clear Excel charts.

Learn how to set a pie chart as the default in Excel by selecting a pie chart, using insert options, and choosing set as default for frequent pie-based work.

Apply Excel filters to analyze sales data by quarters and products, using keyboard shortcuts and the Data or Home tab controls, while respecting headers and nonblank rows.

Master number filters in Excel by applying greater than and between criteria, analyzing top 10 values, comparing to the average, spotting blanks, and managing missing unit prices in sales data.

Learn how to apply date filters to your data in excel, including last week, next week, year-to-date, and quarter filters, with custom filters and between dates.

Learn to use text filters in Excel to search data by text patterns, using begins with, contains, and case-insensitive matching on product names and sales records.

Learn to apply color coding to cells to indicate low and high sales, and use filter by color, including font color, with conditional formatting in Excel.

Use wild characters in Excel filters to search data, using the question mark to represent a single character and refine results in categories like beverages and sausages.

Learn to copy and paste in Excel using filters, select only the visible data, and paste it to a chosen destination, such as coffee items.

Explore subtotal in excel by applying filters to sum revenue across first and second year quarters, focusing on product filters and handling hidden values with subtotal functions.

Discover how to sort data in Excel using the Home tab options, including sorting by numbers and text, ascending or descending, and distinguishing header rows from data.

Learn how to perform multi-column sort in Excel to arrange rows by total marks in descending order, choosing whether to sort the whole data or just the total column.

Practice sorting a four-column data set in Excel—share name, price, dividend, and character—using headers and a custom sort to order by price and by name alphabetically.

Learn multi-level sorting in Excel by region, sales, and managers, using color coding to highlight top and bottom data, and sorting by product and alphabetical order.

Learn to sort Excel data by color using color coding, apply multiple color levels, and order results in descending sequences by manager and color.

Discover how to sort data in reverse order in Excel, compare reverse sorting with alphabetical orders, and arrange entries from largest to smallest by merit.

Learn how to insert a blank row after every bank transaction using sorting in Excel, including creating serial numbers and sorting by the first column.

Learn how to sort data by a custom list in Excel, using the data tab and advanced options to organize finance, marketing, payroll, and other departments.

Sort horizontally in excel by region values, compare numbers to arrange rows, and preserve the data integrity of each row as you apply left-to-right sorting.

Explore the sort and sortn functions in excel, performing multi-level sorts by column, choosing ascending or descending order, and extracting top n rows while keeping data linked to the source.

Learn to create data tables in Excel, apply filters and sort options, including smallest to largest and no greater than filters, cancel filters, and return to original order.

Learn to convert data into a table, rename the table and headers, add and name new columns (fiction, mystery), and resize rows or columns through the design tab.

Explore the design tab to manage tables, insert columns, and remove duplicates using the dialog to choose rules, while tuning filters, banded rows, and totals such as sum and count.

Learn how to apply table styles and perform automatic calculations in Excel, including creating dynamic cost updates by 10 percent, auto-populating new rows, and calculating total revenue.

Master inserting and configuring slicers in Excel to filter by columns, resize and align slicers, adjust settings to sort by type of books in descending order, and clear filters.

Convert a table back to a range in Excel, learn to copy, move, and add columns within a table, and revert to a normal data range using the design tab.

Link files using data tables in Excel to consolidate marks across sheets, convert ranges to tables, and use Ctrl+Enter and dollar signs to propagate calculations.

Explore how relative referencing adjusts cell references when formulas are copied across rows or columns, using drag-and-fill and show formula features to understand reference behavior.

Master absolute referencing in excel by fixing cell references with dollar signs when dragging formulas, using f4, and applying this to tax calculations.

Master absolute and mixed referencing in Excel by fixing rows or columns with dollar signs, ensuring revenue formulas stay correct when dragging across cells.

Explore calculating areas in a grid using Excel formulas, and fix cell references with dollar signs when dragging. Learn to check results with show formula and avoid common reference mistakes.

Explore how to build and copy formulas in Excel, use absolute references with dollar signs, and calculate cost, quantity, and discounts across rows with dragging.

Explore logical comparison operators such as equal to, not equal to, less than, less than or equal to, and greater than or equal to, applying them to data and formulas.

Explore the Excel if function by building conditional statements with true and false outcomes, using comparisons (<, >, =), and logical operators to test and apply results.

Learn to apply nested if functions in Excel to compare values and determine better, equal, or other outcomes, using practical examples from project cash flow and grading thresholds.

Explore and/or/not logic in Excel conditions, with examples of true and false outcomes for various condition tests.

Apply if and and functions to test whether a value lies between two bounds, returning in range or out of range, with a commission example and absolute references.

Master if and or in Excel to evaluate multiple conditions, calculate commissions and bonuses, and use absolute referencing to copy formulas across days and datasets.

Master not, if, and, or in excel to apply range-based conditions with true or false outcomes, including pricing adjustments like red or green rules and 10 percent increases.

Explore xor in Excel, defining exclusive or as true when any one argument is true, and false when all arguments are true or all are false, with practical xor examples.

Learn to apply Excel's count functions (count, counta, and countblank) to tally numbers, text, and blanks across regions and dates with practical examples.

Master countif techniques to tally attendance and values across ranges, applying criteria such as equals, not equals, and greater than, with examples using apples, oranges, and days.

Learn to use the sum if function to total sales for year 2020 by specifying the criteria range, sum range, and dynamic references across quarters and products.

Explore how the averageif function in Excel uses a range, criteria, and average_range to compute averages such as year 2020 sales or per product averages.

Master the IFS function and nested if logic in Excel to classify scores as first, second, or fail based on thresholds such as 60, 50, and 33.

learn to use countifs to count students who passed all subjects across multiple ranges, and to count salespeople who exceeded targets for toothpaste and perfume.

Learn how to use sumifs to calculate total sales for Q2 by applying multiple criteria across quarter, product, and country.

Learn to apply averageifs to calculate the average monthly mobile expenses across countries using multiple criteria like country and month.

Master maxifs and minifs to extract maximum and minimum costs per unit across multiple criteria, such as shop and product, for sales data analysis.

Learn how to use the iferror function in Excel to handle calculation errors, such as division by zero, by wrapping formulas and returning a blank or zero, with three variations.