Microsoft Excel Bootcamp - Learn to Master Excel in 3hrs!

color inputs blue and keep other cells black to show what to change, avoid hard coding numbers in formulas, and place assumptions at the top to improve readability.

Learn to format numbers easily, apply borders and fill colors wisely with soft colors, and keep spreadsheets clean, readable, and intuitive.

Master keyboard shortcuts to boost Excel efficiency; memorize universal and personalized shortcuts, then practice to save about 40 hours a year.

Use keyboard shortcuts to manage workbooks quickly: save with control s, copy with control c, paste with control v, undo with control z, and redo with control y.

Master essential excel keyboard shortcuts for inserting and deleting columns and rows, using ctrl+space to select a column and shift+space to select a row, with ctrl+shift++ and ctrl+-.

Learn essential keyboard shortcuts in Excel by using the alt key to access the ribbon, toggle grid lines, and adjust row height for faster formatting.

Customize the quick access toolbar by removing default items and adding your most-used commands, then master keyboard shortcuts to use borders and pivot tables efficiently.

Master keyboard shortcuts in Excel by using ctrl and arrows to navigate data, plus shift to select blocks, and combine control and shift for efficient data selection across large datasets.

Master absolute cell references by locking cells with dollar signs using F4, and copy formulas down or across with the fill handle, keeping the row or column fixed as needed.

Learn how to use sumifs in Excel to sum sales by multiple criteria, building on the sum and countifs basics with clear criteria and ranges.

Master sumifs by selecting entire columns for sum and criteria ranges, enabling dynamic totals without hard-coding values and ensuring accuracy across data updates.

Learn to use the countifs formula to count cells meeting multiple criteria, including product and quarter, with examples using Tartus and Q1.

Master the vlookup function by treating it like a phone book: find the item in the first column of a master lookup table and retrieve its corresponding value.

Learn how to use the VLOOKUP formula in Excel, including selecting the table array, locking it with F4, choosing the column index, and using exact match (false) for reliable lookups.

Learn to avoid vlookup pitfalls by locking down the table and using false for exact matches, since the lookup value must be in the first column of data.

Apply learned ifs and vlookup to analyze the State Street Corporation balance sheet of securities, classify groups, and verify totals by summing direct obligations and mortgage backed securities.

In this case study, learn to use sumifs with multiple criteria and fixed references, divide results by 1000, and troubleshoot variances caused by the other category.

Master if statements in Excel by building logical tests that yield true or false, then extend to nested ifs with equals, greater than, less than, and not equal to.

Learn to build nested if statements in Excel by combining two or more tests into one formula, evaluating equal, less than, and greater than criteria to determine pass or fail.

Master edate and eomonth date functions to increment dates by months or years, and return the last day of each month, including leap year considerations, for streamlined financial modeling.

Extract year, month, and day from dates using Excel's year, month, and day formulas to consolidate data by year or month for pivot tables and charts.

Learn to extract pieces of text in Excel using the left, right, and mid functions, applying them to employee numbers and area codes from data dumps.

Use the trim function to strip leading spaces from text in Excel, preventing formula errors in IF and VLOOKUP; convert text numbers to actual numbers by multiplying by one.

Discover how to split data with Excel's text to columns, using delimited vs. fixed width options, and apply a comma as the delimiter while choosing a safe destination.

Master the remove duplicates tool in Excel to clean data with headers, set duplicate criteria to account number and balance, and undo changes with Ctrl+Z for faster analysis.

Apply conditional formatting in Excel to highlight cells meeting criteria, such as sales above 80,000. Use highlight cell and top bottom rules, data bars, color scales, and pivot table integration.

Apply multiple conditional formatting rules in Excel to highlight high and low sales, customize formats, and manage or remove rules, with best practices like adding a visual key.

Explore conditional formatting in excel with data bars and color scales, create a heat map, and manage rules across a sheet while understanding legends and overwriting behavior.

Pivot tables are easy and powerful; insert them, place region in rows and sales in values, and experiment to see changes, using the analyze tab and field list.

Explore how to drill down data with pivot tables to show total sales by region, then by product and quarter by dragging fields into rows and columns.

Learn to format pivot tables efficiently by using value field settings and currency formatting, set subtotals at the bottom, customize layout, and clean up headers and spacing for clearer insights.

Discover how to use slicers with pivot tables to filter data, create pivot charts, and manage field headers and data sources, while refreshing updates for accurate dashboards.

Apply case study practice by using text to columns, nested if statements, and pivot tables to analyze a portfolio, building a credit rating matrix with good, fair, and poor categories.

Master nested if logic to assign credit ratings good, fair, or poor, and build pivot tables to summarize loans by fixed or variable type and totals, bucketed by month.

Learn to build pivot tables in Excel with credit rating as rows, loan amount as values, and months as columns; format currency, set subtitles at bottom, and add a slicer.

Celebrate finishing the course by reviewing material, practicing keyboard shortcuts, and considering the excel vba macros course to automate spreadsheets and access free resources.