Microsoft Excel for the Corporate Financial Analyst

Choose a keyboard with a dedicated number pad and a top row of F1–F12 keys to maximize Excel productivity and minimize keystrokes.

Master keyboard shortcuts in Excel to dramatically boost productivity, learning Alt key ribbon navigation to apply actions like yellow fill and outside borders without the mouse.

Master efficient navigation in Excel using the arrow keys, start from B3, use shift to select ranges, and control to jump to the next row with a value.

Learn how to use cell references to compute rent as a percentage of total revenue, and lock denominators with F4 to copy formulas across units.

Apply filters to a data set in Excel, using the Data Filter command or Alt shortcuts, to sort, filter by color, or select records, then paste filtered rows efficiently.

Group rows and columns in Excel to hide or reveal details with collapse and expand. Use alt g g for rows and alt a g for columns to manage visibility.

Use paste special to replace formulas with hard coded values while preserving formatting, then paste formatting from the original table; keyboard shortcuts offer a faster method.

Explore pivot tables for quick, high-level sales analysis by state and product in Excel, compare their flexibility to manual IFS and lookups, and learn to set filters and date breakdowns.

Learn where to begin a new Excel worksheet by starting in column B, reducing column A, removing gridlines, and adding a bordered title in B1 before entering data in B4.

Format numbers in Excel for corporate finance by applying currency and percentage formats, adjust decimals, and explore accounting and other number formats.

Align headers with data by applying left or right alignment for text and numbers, avoid merge and center, use center across selection, autofit, and column resizing for a clean table.

Use the format painter in Microsoft Excel to copy cell formatting, including fill color, font color, and bold, and apply it to multiple cells with a single or double-click.

Apply header and totals row formatting in Excel, including fill color, font color, borders, and bolding. Implement conditional formatting to shade every other row and explore color scales for visualization.

Master footnote formatting in Excel by adding parenthetical numbers, converting them to superscripts, and sizing footnotes smaller to clarify monthly rent, parking, and total revenue.

Improve Excel model readability with color coding. Use blue for hard coded inputs, light yellow for inputs, black for formulas, green for links to sheets, and red for external files.

Master the essential Excel formulas for corporate finance, enabling you to tackle most tasks with these core functions and a little googling when needed.

Explore the lookup concept in Excel, learn how to find the corresponding record in a list, and return values such as rent or parking for a given unit, using functions.

Learn how to use vlookup to retrieve values from a table by specifying the lookup value, table array, and exact-match column index; compare its leftmost-column limitation to xlookup.

Explore index match, a two-function lookup approach in Excel to return a value such as the bedroom count for unit two, highlighting its advantages over Vlookup and comparing to Xlookup.

Learn how index match, with exact matches and dynamic column lookup, enables flexible lookups in Excel across a table, as shown with unit two and rent.

Explore the IFS concept in Excel, evaluating whether criteria are true to return different values, and apply this logic across formulas from the if formula to IFS variants.

Demonstrate how the if statement in Excel evaluates a logical test and returns yes or no based on true or false, using unit one bathroom count as an example.

Use the and operator with an if statement to evaluate bedroom and bathroom counts, classifying units as standard or premium floor plans.

Explore the or operator in Excel and how it pairs with an if statement to create flexible logic; determine standard vs premium units based on bedroom or bathroom counts.

Learn how to implement nested if statements in Excel to classify units as standard or premium based on bedroom type, bathroom count, and a rent threshold of at least $2,500.

Use the IFERROR formula for error handling in Excel, wrapping a faulty calculation to return a chosen value (such as zero or a text label) instead of an error.

Learn to use the sumifs formula to sum rent values by one-bedroom units, define the sum range and criteria ranges, and add multiple criteria like bathrooms for flexible Excel modeling.

Apply text functions in Excel to combine data from first and last name fields into a full name, and extract state abbreviations from a city and state cell.

Master concatenation in Excel by combining first and last names into a full name using the concatenate function, or the ampersand operator, and copy the formula down.

Master the left and right functions in Excel to extract characters from text, demonstrated by splitting a full name like Rick Smith into separate columns.

Learn how the mid function extracts a substring from a string by specifying the start position and length to obtain Smith from Rick Smith Junior.

Learn how the trim function cleans dirty data by removing leading and trailing spaces, fixing countifs results for accurate one-bedroom unit counts in Excel.

Combine left, right, find, len, and trim to extract first and last names from a full name. Copy formulas down to update dynamically and remove trailing spaces.

Calculate portfolio weighted averages in Excel by using sumproduct to multiply rents or occupancies by unit counts, then divide by total units to obtain metrics like average rent and occupancy.

Explore CAGR, the compound annual growth rate, to smooth volatile year-over-year rent growth from 2021 to 2023 using the final value over starting value formula and a quick check.

Convert the csv to an excel workbook to preserve formatting, then add month-year columns and create a unique product identifier by combining product type fields with a hyphen.

Create an organized summary tab in excel by adding inputs tab, data validation drop-downs, and a market filter; build monthly and quarterly, plus scenario sales with sumifs and date functions.

Learn to build and format the presentation tab in a financial model, create inputs and drop-downs, link tabs, and build a quarterly actual vs budget sales chart with dynamic title.