MOS Excel Expert 2016 Microsoft Certification Exam 77-728

Learn to copy macros between workbooks by opening both files, creating a models folder, and transferring the model one file to the destination to verify the macros were copied.

Protect a worksheet by using the review tab, choose the default permission options, and set a password so only someone who knows it can unprotect the sheet.

Open the number format options and navigate to the custom number format category. Remove the desired custom format from the list.

Apply advanced conditional formatting in Excel by using a formula to highlight the max value in a range with a yellow fill.

Learn to use form controls in Excel by adding a spin button linked to cell 3 with a 0–100 range, and configure minimum and maximum values via the developer tab.

Use the accessibility checker from the File menu to identify and fix navigation issues for screen readers, and merge cells to improve accessibility in your workbook.

Learn to use the SUMIFS function to calculate gross sales totals across multiple criteria, including district, product type, color, and units sold, by defining ranges and criteria in one formula.

Learn to use the averageifs function in Excel to calculate conditional averages across sales data by district and color, using multiple criteria and wildcards.

Learn how to use the countifs function in Excel to count sales data across multiple criteria, including district, product type, color and unit thresholds, with wildcards for pattern matching.

Learn the Excel index function, its two formats, and how row and column numbers (zero to return a full row or column) fetch data and help compute trimester averages.

Explore how the match function returns the position of a value in an array, with exact or closest match, including wildcards for text and sorted data for approximations.

Explore how to use the offset function in Excel to dynamically sum the 10 largest sales from a table, counting rows and resizing the data range.

Master VLOOKUP in Excel by retrieving values from a table using distance in miles, referencing the leftmost column, supporting exact and approximate matches, and returning data from a chosen column.

Use the hlookup function to search the top row of a table and return a value from a chosen row. Explore exact versus approximate matches with mileage examples.

Master advanced lookup techniques in Excel by combining VLOOKUP and HLOOKUP for vertical and horizontal matrix searches, using nested lookups and exact-match retrieval of units and dates.

Learn to build robust excel lookups by combining index and match, overcoming the right-to-left limitation and offset-related challenges, enabling exact lookups across a data table.

Master advanced lookup formulas with index and match, including index-match and index-match-match, to locate exact sales data across multi-column arrays and overcome left-to-right lookup limitations.

Learn how to use goal seek and what-if analysis in Excel to adjust loan rate and term, and see how monthly payments change for a $20,000 car loan.

Master the transpose function in Excel to convert data from rows to columns using an array formula, and learn how transposed tables affect formatting and layout.

Master pivot tables in Excel to summarize sales data by salesman, date, and product; choose fields for rows, columns, and values, apply filters, and use the recommended pivot tables feature.

Explore creating calculated fields and calculated items in a table to compute income (quantity × price), including combining products like shoes and boots and managing totals.

Explore creating and configuring pivot charts in Excel, linking the people table to sales data, using product, salesman, and the sum of quantity fields with filters.

Explore creating and using slicers and timeline slicers to filter a people and product table, connect slicers to multiple tables, and customize their options in Excel 2016.

Explore excel's financial functions, approximately 40 in number, illustrated with practical everyday examples to help you budget and manage finance for the MOS Excel Expert 2016 Microsoft certification exam 77-728.

Explore how PMT, IPMT, and PPMT calculate loan payments, present value, and future value, showing how beginning versus end of period payments affect monthly installments.

Explore how the PV, FV, NPER, and RATE functions model loans and investments, calculate present value, future value, periods, and interest rates through practical cash-flow scenarios.

Learn how to use NPV and XNPV in Excel to evaluate investments by calculating the net present value of periodic and non-periodic cash flows, using discount rates.

Learn to compute IRR, MIRR, and XIRR for investments with periodic and non-periodic payments; understand reinvestment rate assumptions and how MIRR and XIRR differ from IRR.

Learn how the DOLLARFR and DOLLARDE functions convert between fractional and decimal dollar values in Excel, with step-by-step examples converting decimals to fractions and back using cells.

Explore how to calculate security yields in Excel using the functions YIELD, YIELDDISC, and YIELDMAT, including settlement and maturity concepts.

Explore the DISC, RECEIVED, and INTRATE functions to compute discount rate, amount received at maturity, and interest rate for fully invested securities.

Learn to price bonds with excel using price, pricemat, and pricedisc, computing prices per 100 face value from settlement, maturity, coupon, and yield across three scenarios to compare yields.

Explore Excel's accrint and accrintm functions for calculating accrued interest on bonds, determining dirty price, and comparing prices using the price function across settlement dates and coupon schedules.

Learn to use COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, and COUPPCD to calculate coupon dates, days in coupon periods, and coupons between settlement and maturity, with 30/360 and actual bases.

Compute duration and modified duration of bonds using Excel's duration and mduration functions, illustrating sensitivity to interest rates and price volatility.

Learn to calculate asset depreciation in Excel using sln, syd, db, and ddb, including straight-line, sum-of-years-digits, and declining balance methods.

Learn how to use Excel's tbilleq, tbillprice, and tbillyield functions to compute treasury bill price, discount rate, and yield, using settlement and maturity dates and a 100 dollar face value.

Enable power pivot, add tables to the data model, and create relationships across sources to analyze product, price, and quantity within a unified data view.

Build and relate a date table in Power Pivot within the data model, define product and price fields, and organize hierarchies for clear year by month analysis.

Master the MOS 2016 exam format, a 50-minute test with multiple projects and tasks. Understand scoring, the passing threshold, and score reports, and practice with dozens of skill-category assignments.

Explore how MOS 2016 multi-project questions simulate real-life tasks, guiding you through multiple projects and tasks with review, completion, and grading controls to track progress.

Rate and comment on these lessons to help us improve our lectures and identify any problems. Apply what you learned to turn knowledge into practical skills in your life.