Excel for Accountants: Volume 2

Learn five key Excel shortcuts for volume 2, including F9 to evaluate selected formula text and mastering edit and enter modes with F2, plus fill down and fill right.

Master keyboard-only updates to the commission worksheet using shortcuts for subtotal, round, and F9 evaluation, while navigating with control, shift, and arrow keys to edit formulas.

Master conditional summing in Excel by using the ifs/sumifs logic to add amounts only when criteria match. Build subtotals by account and name, including table data and multi-criteria expressions.

Apply conditional summing to summarize data by account using sumifs with absolute references. Build counts with countifs, use table references, and refresh a report sheet from a data sheet.

Remove duplicates in lists and tables using the remove duplicates command with headers, then build a formula-based vendor report with the named table tbo_data and sumifs.

Master lookups with vlookup to pull exact amounts from a table, using range lookup and match to stay accurate as data updates. Build efficient, consistent formulas that fill down reliably.

Master vlookup range lookups by learning how the first column is matched, how the column index references the lookup range, and when sorting matters for dates and bonuses.

Use the match function to create a dynamic third argument for VLOOKUP, ensuring it adapts to inserted columns and avoids errors. This approach improves reliability and efficiency in workbook lookups.

Learn to use vlookup on the TBO_zip's and TBO_vendors tables, converting zip codes with the value function and vendor IDs with the text function for exact match lookups.

Move beyond vlookup by using the index function with match to return the correct value from any column, enabling leftward lookups and two-dimensional lookups.

Learn to use index and match to retrieve account names from a table by exact account number matches, using structured table references and filling formulas across.

Trap errors with the iferror function to substitute a chosen value, such as 0, for lookup errors in vlookup-based reports. Learn wrapping vlookup in iferror to maintain accurate totals.

Apply the iferror function to clean errors in Excel reports, building variance-to-budget calculations and robust lookups, then compute month-over-month SGA changes for budget reporting.

Learn how the if function tests whether assets equal liabilities and equity and returns yes or no, with hands-on balance sheet practice.

Apply the if function in three exercises: verify balance sheet balance, label income statements as net income or net loss, and compute commissions from excess and rate.