Advanced Excel Training

Explore macros, advanced formulas, and functions to manipulate text and numbers across multiple sheets. Create dynamic dropdown lists, pivot tables, and lookup functions, applying Goal Seek to inventory.

Calculate retail value from cost with a 150% markup in an inventory sheet, and learn to round numbers using the round function and nested formulas.

Learn how to nest the round function within other formulas in Excel, using the inner function result (like sum) to round to two decimals and display the outcome.

Explore how the Excel text function converts numbers to text strings, enabling currency formatting and character-level edits to digits, and how to integrate it into formulas.

Use the text function in Excel to convert a rounded result, derived by multiplying by 1.5, into a currency-formatted string embedded in a formula to display a dollar amount.

Explore the len function in excel to measure text length and enable precise string manipulation with the replace function for modifying the last three digits in a text-formatted number.

Explore Excel's replace function, detailing its old text, start position, number of characters to replace, and new text, with dynamic updates and using the length function to determine start.

Build an Excel formula with replace and length to replace the last three digits with 9s, using length minus 3 to locate the start, and handle decimals and thousands later.

Master the if function in Excel by building a formula that tests value ranges, returns true or false, and uses nested logic for numbers under 10 and over 1000.

Use the if function to test values, such as less than 10 or a thousand dollars or more, and adjust digits to display the correct dollar amount.

Apply nested if functions in Excel to handle multiple outcomes, copy and update formulas across cells with relative references, and format numbers with thousands separators and currency formatting.

Learn to extract text from strings in Excel using the left, mid, and right functions, including how many characters to read and where to start, and how spaces affect results.

Master the concatenate function in advanced Excel training by stitching multiple string arguments into one. Explore left, mid, and right text functions and optional parameters.

Apply the Excel functions upper, lower, and proper to format text case consistently, converting text to uppercase, lowercase, or proper capitalization for clean, standardized sheets.

Clean PayPal data in Excel by removing USD, trimming leading and trailing spaces, and converting text strings to numbers with the trim function.

Learn how to convert messy text to numbers in Excel by using trim, left, length, and value to clean values, perform arithmetic, and format currency.

Learn to work with multiple Excel sheets, reference cells across sheets with the sheet name and exclamation mark, and pull data into other sheets via lists.

Build drop-down lists in Excel via data validation, referencing category and brand lists on another sheet to populate an inventory sheet with models.

Explore a real world Excel scenario that builds a complex formula to compute per-employee project completion percentages by counting completed and scheduled tasks while excluding not applicable tasks.

Learn to use counta to count non blank cells and countif to count cells by criteria, including not equal to test, then combine them to filter non blanks.

Learn to calculate task completion percentages in Excel using count and countif to exclude not applicable and blank cells, applying the x divided by y times 100 formula.

Master relative and absolute references by anchoring cells with dollar signs, copy formulas across, adjust rows, and use counta and countif to match or not match criteria.

Master Excel 2007 to calculate total cost and total value, determine loan amount for opening a music store and purchasing equipment, budget with interest, and format numbers as currency.

Explore how to use Excel's PMT function to model loans, calculate monthly payments, total repay, and projected profit while adjusting interest, terms, and inventory costs.

Learn how Excel macros automate repetitive tasks by recording and playing back steps, enable the developer tab, and use relative referencing for flexible automation.

Explore real-world Excel macros by planning steps, recording with relative references, cleaning data, converting text to numbers, and automating currency formatting, sums, and PayPal data handling.

Learn how to view and edit recorded macros in the Visual Basic Editor, remove unnecessary steps, adjust font size and column width, and apply simple replacements to streamline macro execution.

Learn to safely work with macros in Excel, recognizing risks from external documents and how to enable and access macros via the quick access toolbar.

Explore using VLOOKUP to pull product details from an inventory tab and populate an invoice sheet, including cross-worksheet lookups and exact-match returns.

Learn to use vlookup to populate the model and price from a product ID on the inventory sheet, calculate totals, handle errors, and improve user friendliness for an invoice workflow.

Use vlookup and if to populate invoice fields only when a product id exists, leaving blanks otherwise, with a default quantity and dropdowns to build a mini database.

Learn to build a dynamic invoice in Excel using vlookup across sheets, data validation dropdowns, and nested if formulas to auto-compute subtotal, tax, and total.

use hlookup to retrieve data arranged across rows and compare with vlookup for vertical layouts, and employ data validation dropdowns for names, addresses, and phones.

Learn to create pivot tables quickly to summarize inventory data, choose fields like category, brand, and value, and turn data into actionable totals and charts.

Master pivot table options to toggle fields, set row and column labels, drag fields to rearrange, and choose sum or count via value field settings.

Create a dynamic pivot chart from your data by selecting a range, inserting a chart, and configuring category, brand, and values to visualize inventory trends.

Learn to use Excel's Goal Seek in what-if analysis to set a cell to a profit goal and adjust cell, like interest rate or payments, to reach 125,000 or 150,000.

Develop skills in intermediate and advanced Excel tactics, connect with a support forum for quick answers, and access supplemental information at get excel training dot com forward slash forum.