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.

Explore the if function in Excel 2007, using logical tests to award bonuses when sales meet thresholds (4000, 5000, 2500). Build nested ifs with and/or and yes/no results.

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.

Learn to use Excel subtotals to sum sales, commission, and bonus by region, switch to quarter totals, and explore functions like max, min, average, and variance.

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.

Explore Excel 2007 advanced scenario manager to compare multiple car loan scenarios using named ranges, what-if analysis, and scenario summaries that feed a readable report.

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.

Apply conditional formatting with data bars, color scales, and icon sets to visualize values and identify top and bottom items. Mix formats and clear rules, noting color blindness considerations.

Manage conditional formatting rules to highlight values and reorder formats for clarity. Create new rules, such as blank cells or top positives, and see live updates as data changes.

Create a single Excel database list with unique field headings and records, avoid blank rows, and use the sort dialog for multi level sorting by product and salesperson.

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.

Start a pivot table from your data table, then sort and filter to analyze data, and optionally create a pivot chart on a new worksheet.

Use the pivot table field list pane to configure values, row labels, and filters, revealing sums by product and region. Filter by year to compare 2003 and 2004 results.

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 how to import data from Microsoft Access into Excel, choose table or pivot options, set destination, manage and refresh external data connections, and adjust formatting and update settings.

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 how to manage updates in Excel 2007 advanced by refreshing external data sources, choosing refresh all versus refresh, and controlling update frequency to optimize performance.

Export data from Excel by saving the active sheet as a delimited text file, choosing tab or comma delimited formats for import into a database.

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.

Learn to run a macro from a custom button on the Quick Access Toolbar, add macros with icons or colors, and rely on tooltips to identify them.

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.