Excel 2007 - Advanced

Explore a sampling of Excel's advanced financial functions, including the loan payment function for auto and mortgage loans, with syntax, inputs, and insert function help.

Learn to clean and normalize text in Excel using proper, trim, right, concatenate, and exact functions to fix data imported from external sources.

Learn to use Excel 2007's sum and countif functions to sum amounts by state, using ranges and quoted criteria like Massachusetts and New Hampshire.

Master the Excel lookup function by retrieving multiple columns from a table array using a fixed lookup value, absolute references, and named ranges with practical employee data.

Explore the show formulas tool in Excel 2007 advanced to view underlying formulas and audit complex spreadsheets, helping you understand and manage workbooks with many functions.

Trace cell relationships in Excel with precedence and dependence arrows to see which cells a formula relies on and which depend on it, then remove arrows to simplify the view.

Learn to use the formula evaluator to step through nested if statements in Excel, revealing the underlying expressions and the resulting value.

Use error checking to audit formulas across a spreadsheet, trace errors to their source, and understand daisy-chained precedents to fix mistakes before finalizing your workbook.

Use goal seek, a what-if analysis tool, to set the monthly payment to 500 by adjusting the loan amount in a car loan, yielding about 21,711 dollars.

learn to create customized numeric formats in Excel, apply conditional formatting, and use the four-section format for positive, negative, zero, and text values with color codes.

Apply a four-part custom format to show positives with thousands and two decimals, negatives in red with parentheses, and text as N.A.; enforce a two-digit dash three-digit product code.

Master conditional formatting in Excel 2007 by applying presets to highlight cells below minimum values with colors, and copy formats using paste special to apply formats across a range.

Master multi-level sorting in large data tables by adding, deleting, and copying sort levels, adjusting order by data type, and applying case sensitivity.

Discover advanced filtering in Excel 2007: apply multi-criteria filters, top/bottom n analysis, and custom and wildcard conditions, and clear or compound filters to isolate specific sales, region, or blank units.

Use advanced filter to set a criteria range and filter in place or copy results, using multi-line criteria like dairy in east and west with sales over 5000 pivot tables.

Use pivot table options to customize values (average, max) and currency formatting, create new pivot tables from source data, filter by year, and expand or collapse details.

Learn to sort pivot table data by field, use expand and collapse, and create calculated fields to add bonuses, format currency, and adjust value settings.

Format a pivot table in Excel 2007 advanced by selecting styles, choosing a layout, and toggling blank rows, grand totals, subtotals, and cumulative totals for rows and columns.

Create pivot charts from pivot tables in Excel 2007, customize chart styles, and filter by sales reps or product IDs. Move charts to separate sheets while preserving the pivot table.

Format pivot charts using design, layout, and format options, and use the pivot chart filter and field list to analyze totals like total sales.

Learn to import text data into Excel 2007 using the delimited or fixed-width options, configure delimiters (tab, comma, space, semicolon), and set column formats and refresh connections.

Learn to automate simple tasks in Excel 2007 with the macro recorder, name the macro, and record steps to insert labels across worksheets, format cells, and replay the macro.

Run and test macros to automate date creation and monthly fills across sheets using record macro and keyboard shortcuts in Excel 2007 - advanced.

Master Excel 2007 advanced functions and data analysis tools, including scenario manager, goal seek, data tables, sorting and filtering, pivot tables and charts, and macros and VBA.