Quick Guide to Master Advance Excel

Learn to build basic Excel formulas using cell references, start with an equal sign, apply operators, and copy formulas with the fill handle; grasp the order of operations.

Explore Excel 2016 functions through the insert function tool, using average, max, min, and count to analyze data ranges. Understand relative references and preview the upcoming absolute values topic.

Master absolute values in Excel by anchoring the commission rate with dollar signs and using F4 to toggle, then copy formulas with the fill handle to maintain the correct reference.

Learn to compute the average, mode, and median in Excel 2016 using the corresponding functions, with practical employee data and range calculations using max and min.

Learn to compute circumference, area, and volume in Excel using pi, diameter, radius, width, length, and depth. Apply proper formulas for circle and rectangle shapes and format results for clarity.

Practice exercise in Excel 2016 section 2 covers calculating quarterly totals, averages, and highs, applying absolute values for a 15 percent commission with dollar signs and currency formatting, then save.

Learn the if syntax in Excel 2016, mastering how a test yields true or false and triggers different results, including commission calculations from quotas.

Apply sumif and sumifs to perform selective counting in Excel, using named ranges for names, categories, and expenses, and verify results with sample data from Carson’s expenses.

Learn how to use countif and countifs in Excel 2016 to count items by criteria, such as tickets per sales person and amounts greater than 50.

Master advanced Excel with practical average if techniques to calculate mean values by criteria, using named ranges, quotes in criteria, and nested if scenarios.

Explore nesting the if statement in Excel 2016, using nested functions to award bonuses based on job grade four or five, or three, with blank results for others.

Shows using the and and or operators in an if statement to test multiple conditions, such as total sales and job grade, and return review asap or no review needed.

Master the not operator in Excel 2016 to refine if statements with and/or logic, testing values between 120 and 130 to mark acceptable versus not acceptable.

Apply if statements to award a $2000 bonus to those who meet the quota, use count to tally bonuses, and average bonuses for recipients; flag as acceptable or needs help.

Master vertical lookups in Excel 2016 by using VLOOKUP against a bonus table to pull bonuses based on sales, learn exact vs closest matches and range lookups, and explore HLOOKUP.

Learn to perform horizontal data lookups with HLOOKUP in Excel 2016, using a sales and bonus table to retrieve bonus amounts and apply exact-match with false.

Master data validation in Excel by creating dropdown lists from a separate list, enforcing restricted inputs, and using input messages and error alerts to keep data consistent and valid.

Learn to handle missing data in Excel lookups by wrapping VLOOKUP with an error check that returns zero, ensuring accurate sums.

Learn to nest vlookup and hlookup to pull data from a second table, using date and agent lookups in excel 2016 to assign daily duties.

Master vlookup and hlookup in Excel through a hands-on practice exercise, using data validation and nested lookup formulas to calculate bonuses from a bonus table.

Explore sparklines, miniature charts inside worksheet cells that visualize quarterly data. Insert sparklines, customize style and high/low points, and manage groups with the fill handle.

Create sparkline charts in Excel 2016 by inserting line or column sparklines from a data range and specifying the location range in the target cells.

Alter the design of sparklines by adjusting color, weight, and which points to display (high, low, markers), and switch between line, column, or win/loss types; learn to edit data ranges.

Learn to fix empty cells in sparklines by using the edit data options to connect data points with a line in Excel 2016, and understand ungrouping and removing sparklines.

Learn to remove sparklines from worksheets in Excel 2016 by deleting individual sparklines, removing groups, or clearing selected sparklines using the sparkline tools.

Practice sparklines in Excel 2016: insert and convert to a line sparkline, add high and low markers, fix gaps, and remove sparklines before moving to section 6.

Learn to work with time in Excel 2016 using date and time functions such as day, month, and now. See how now returns current date and time for your workbook.

Learn to perform time calculations in Excel using date-time shortcuts and the now function; format for 24-hour time and use if to compute hours worked across midnight.

Learn how to use Excel 2016's round function to round numbers with a specified number of digits, including negative numbers, and understand how Excel treats numbers as serial values.

Explore the mod and int functions in Excel 2016, which return remainders and round down to the lowest integer, with examples like 162 divided by 16 giving remainder 2.

Generate random numbers in Excel 2016 using rand function, including between 0 and 1 and between 10 and 1000, and drag down with fill handle. Refresh with calculate or F9.

Explore loan and investment calculations in excel 2016 using goal seek and what-if analysis to determine principal, rate, and payments; build amortization charts with PMT and CUMIPMT.

Use the forecast function in Excel 2016 to predict 2016 sales from yearly data, and visualize with a line chart and optional trend line, including linear, exponential, or polynomial trends.

Learn to manually add an outline in Excel 2016 by using the data tab, group, auto outline, and level buttons to view totals and levels across columns and rows.

Master outline editing in Excel by adjusting grouping levels, ungrouping, using plus/minus signs to show details, and clearing outlines to reveal data.

Explore Excel 2016 outlining and functions to automate department totals with subtotals and the sum function. Apply outlining, sorting, and subtotal techniques to compute gross pay by department.

Open the Sand Thomas clothing sales report 2014 file, locate the sales tab and outline feature, then sort the region column, apply subtotals, collapse totals and averages, save and close.

Explore scenarios in Excel 2016 to analyze budget data using what-if analysis and the scenario manager. See how revenue increases of 10% or 15% affect expenses and profit.

Learn to set up scenarios in Excel with the scenario manager, create an original budget, and compare 10% and 15% revenue increases to project profit.

Discover how to use Excel's scenario manager to create a scenario summary showing total revenue and profit across 10% and 15% revenue scenarios, with named cells.

Create and analyze pivot table reports from the scenario manager in Excel, showing revenue and profit per branch with a named range and field settings.

Apply practice scenarios to sales by genre by adjusting prices. Create named price ranges for original, increase number one, and increase number two, and generate a report and pivot table.

Learn to use Excel custom views to save and switch between presets like fruits, vegetables, and totals, using the workbook views to show or hide data.

Create and manage custom views in Excel by saving an original view with print and hidden settings, then build fruit, veggies, and totals views and switch between them.

Use outlining from section 7 to create custom views displaying quarters and the yearly total. Save, switch between views, and edit or delete them as needed.

Learn how to edit and delete custom views in Excel, manage outlines on the data tab, and save changes with practical examples like adding a blank row and renaming views.

Save each quarter as a separate view in the practice views file, include quarterly totals and averages, then save as my practice views and close.

Practice makes Excel mastery clear: apply formulas with care, explore multiple ways to reach the right answer, and use a calculator when needed to solidify your skills.