Microsoft Excel - Sweet and Short

Uncover the power of Excel tables that automate features, enable banded rows, total rows, slicers, and dynamic pivot data ranges.

Identify the three components of a table: header row, data area, and total row, and learn how a named table becomes an object for formulas and reliable references, including vlookup.

Convert a data range to a table, apply design options, and use header, total, and banded rows for readability. Emphasize data with first and last column formatting.

Use auto filter on Excel tables to filter data and sort by header drop-downs, selecting A to Z or Z to A, with multiple headers and resulting totals.

Filter data visually with slicers for tables, enabling dashboard-style drill-down by clicking values and combining slicers; add and format slicers via table tools.

Explore advanced sorting in Excel using the data ribbon, sorting by multiple headers, such as product and quarter, and sorting numerical headers like sales to reveal best sellers per quarter.

Learn how to use the total row in an Excel table to calculate sums, averages, counts, mins, and maxes, including filtering to show totals for visible data.

Reference within a table using header names and the at sign to indicate the current row. Calculate stock value by multiplying unit price by quantity in stock, then sum total.

Learn that Excel stores data as text or numbers, while formats are cosmetic; use the formula bar to see true contents and avoid misinterpreting values like 0.75 or currency.

Apply text and numeric formatting to cells using the home ribbon, including bold, color, and font size, as well as percentage, date, and decimal formats.

Learn how Excel distinguishes text from numbers, how leading zeros are preserved by setting cells to text or using apostrophes, and how to convert text to numbers for correct calculations.

Learn how Excel stores dates as numbers to enable calculations, and apply a consistent date format: number, month abbreviation, and four-digit year to avoid misinterpretation.

Display values in currency format to make data easier to read. Apply currency via the Home ribbon or Format Cells, adjust decimal places and symbols, and consider the accounting format.

Understand that percentages are decimals in Excel (1% = 0.01, 120% = 1.2). Enter as % or decimal, format with the percentage option, and adjust decimals.

Master the format painter in Excel to copy a cell's formatting to one or many cells without changing content, using single or double clicks.

Apply conditional formatting with a formula to highlight expenses above £500 per month by using a rule that computes monthly values, adjusts relative references, and formats matching items in red.

Insert a screenshot into an Excel sheet with the built-in tool from the insert ribbon. Capture full or clipped areas, paste with alt+print screen or Ctrl+V, and crop.

Insert most shapes and graphics from PowerPoint into your Excel sheet by drawing them from the insert ribbon, resize and format fills to direct attention.

Explore advanced manipulation of smart art to illustrate information on your sheet with varied layouts, shapes, and colors. Convert to shapes to edit items individually, losing smart art functionality.

Master inserting symbols and equations in excel with the insert symbol and equation tools. Use the recently used list or pre-populated options and format the floating equation box.

Learn to format a graphic object in Excel by using shapes, the drawing tools ribbon, and the format shape pane to customize fill, gradient, outline, shadow, 3D rotation, and text.

Change the shape of a graphic object by editing points with the edit shape tool, add or delete points, and adjust handles for sharp or rounded corners.

Use the selection pane, activated from the page layout ribbon, to manage many shapes and graphics, highlight objects, toggle visibility, and reorder items to control overlap on your sheet.

Learn to perform multi-criteria and customized sorts in Excel, grouping raw data by region, month, and product, and using a custom list to order months correctly.

Learn to input in multiple cells in Excel using Ctrl+Enter, autofill, and the go to special blanks method to populate values from the cell above.

Master custom lists to fix sort order by your own sequence, not alphabetical. Apply a saved custom list via the data ribbon to rank Porsche first, then the remaining items.

Master advanced hypertext links in Excel by turning text into hyperlinks that point to web pages, files, or shapes, and build navigable menus between sheets.

Create and edit a data form in Excel using dropdowns, checkboxes, radio buttons, and spinners, with two-way data links to output cells, and learn how to enable the developer ribbon.

Create and edit a form in Excel using the developer ribbon, inserting form controls and configuring input ranges, cell links, and options.

Learn to use the equals sign to create calculation cells in Excel, reference and target cells, and automatically update results across sheets with simple, powerful calculations.

Explore the difference between formulas and functions in Excel, learn to use operators and the sum function, and see how to calculate averages by combining methods.

Learn to build basic Excel formulas with addition, subtraction, multiplication, and division using cell references and the equals sign, and apply operator precedence with parentheses.

Identify the components of a function in Excel, including the function name and input parameters. See how the equal sign, brackets, ranges, and commas structure inputs to perform calculations.

Master the sum and average functions in Excel by calculating total sales and averages across quarters, using range selection, auto-highlighting, and formula bar verification.

Learn to combine multiple Excel functions in a single cell, performing sum, count, and average within one formula for streamlined calculations.

Lock rows and columns with the dollar sign to create absolute references when you copy formulas in Excel, keeping the target range fixed as you drag and use Countif.

Discover how to find and use Excel functions via the insert function tool, formulas ribbon, and category searches, and learn to enter required inputs with the wizard.

Learn how to use the count function to count numeric values in a range, distinguish it from sum, and switch to counta for text values.

Explore how to use Excel's round, roundup, and rounddown functions to change values, while formatting only changes display, not the actual data.

Explore the left and right functions in Excel to extract text from the left or right by digits, with dynamic updates and absolute references as source data changes.

Learn the mid function in excel, similar to left and right, to extract a substring from text by specifying source text, starting position, and length, with spaces counted as characters.

Identify common errors in functions, such as name error, value error, div zero error, and reference error, then fix by editing the function cell, addressing the green triangle, and updating the result.

Master the concatenate function to combine text from multiple cells, such as names and surnames, and insert a space between values in the formula.

Learn how to use the upper, lower, and proper text functions in Excel to convert text to uppercase, lowercase, or proper case.

Explore how vlookup links two data sets, retrieves descriptions, categories, and prices, and keeps data dynamic through single value updates. Learn exact and approximate matching to improve accuracy.

Learn how to structure a data set for vlookup by using the first left column as the search field, selecting a source range, and returning values for a product code.

Demonstrate using vlookup to search a lookup value in a table array, specify the first column for the search, and return data from specific columns with exact match false.

Expose common vlookup errors and resolve them by locking source ranges with absolute references (F4), dragging formulas correctly, and aligning the return column with the source data.

Compare exact and approximate vlookup matches and how the final argument controls matching; approximate lookups require ascending sort and return the closest match, while exact lookups return errors when missing.

Learn how to compare two sheets in Excel using Vlookup to identify missing entries, handle errors, and quickly reveal discrepancies between data sets.

Learn how to perform vlookup with multiple criteria by creating a unique search string through concatenation of quarter and product code, then applying vlookup with an absolute data range.

Explore the hlookup function, a horizontal counterpart to vlookup, and learn to set the first row for searches, choose exact or false matches, and use absolute references.

Learn to handle errors in Excel by wrapping Vlookup in the iferror function to return zero when no match, and propagate the fix across cells by dragging.

Prepare data in a standard format with column headers and a names column, then insert a graph in Excel. Keep data concise and preset number formatting to ensure accurate visuals.

Explore key elements of a chart in Excel, including the chart area, plot area, axes, legend, and gridlines, with options to add data labels.

Choose the right graph to visualize and analyze data; compare column, bar, line, area, stacked, pie, and donut charts, and learn to switch chart types to tell different stories.

Format chart elements to create a visually appealing Excel chart by using the home ribbon, formatting pane, and options for font, color, fill, borders, effects, and series overlap and gap.

Learn to adjust the chart axes in Excel by formatting the axis, setting bounds, and zooming to reveal quarterly changes; reset to auto and reverse order as needed.

Learn to create bar and column graphs, switch between clustered and stacked column types, and customize colors and formatting to compare product sales across quarters.

Use donut and pie graphs to show the size and share of data values; avoid time-based data. Insert from the ribbon, add percentage data labels, and limit entries to eight.

Create combo graphs in Excel to plot sales in currency with the number of orders as a line, using a secondary axis for clear interaction.

Learn to create sparklines in cells, add and delete sparklines, customize line and win-loss sparklines, and adjust axis values in Excel to highlight trends and high or low data points.