Excel tips & tricks: Best Practices in Financial Models (with pictures)

Financial modeling can feel like a daunting skill to master, but here's the good news: you don't need to know everything to excel at it. The truth is, by following a few essential tips, you can achieve 80% of what you need with just 20% of the effort.

These actionable, time-saving steps will help you build clean, professional, and efficient financial models without unnecessary stress. Think of this as your shortcut to becoming a pro.

Here’s the list of tips that will transform the way you work in Excel. Follow them, and you’ll be well on your way to modeling mastery.

Always start your model from cell B2

Starting model in cell B2 keeps things clean and visually more organized. It leaves space for titles, headers, and notes while making the model look professional.

Here is how the above picture should look like instead

How to Do It:

• Begin your input, assumptions, or formulas in cell B2 instead of A1.
• Leave the first row (1) and column (A) empty for additional headers or notes if needed.

Keep the Start Column of Your Dates Consistent Across Sheets

Consistency is key for readability. If your date series always starts in the same column (e.g., column B for October 2024) across all sheets, it becomes easier to navigate and review your formulas. You will no longer fear linking a formula to the wrong date

How to Do It:

Set up a template sheet with dates in a time series across columns. Copy this template to a new sheet everytime you want to start modeling something new.

Pro Tip: Link all of the date headers to one source, in case you change the start date and forget to update this across all your sheets.

Never Merge Cells - I BEG YOU!

• Why It Matters: Merged cells might look neat, but they create problems when copying, pasting, or running formulas. Instead, use other formatting options to keep things clean.

What to Do Instead:

• Select the header and unmerge cells (ALT + H + M + U)
• Use "Center Across Selection" instead of merging:
  1. Select the cells you want to "center across".
  2. Press Ctrl + 1 to open the Format Cells dialog box.
  3. Go to the Alignment tab.
  4. Under "Horizontal," select Center Across Selection.

Color-Code Your Models

Proper color coding helps you (and others) quickly understand what’s going on in your model. It distinguishes between inputs, formulas, and links, saving you time during reviews.

Here is the universal color-coding standard that needs to be followed all the time:

• Blue Font Color: All hard-coded (i.e. manually entered) numbers; used for historical financial data and also for many input cells in the “Assumptions” part of models
• Black Font Color: All formulas; Links to the current sheet
• Green Font Color: All direct links to cells in other worksheets
• (optional) Purple Font Color: Formulas containing hidden assumptions in the form of hardcoded numbers (e.g. =A1*1.53)

Can You Automatically Format All Your Cells?
A common question I see a lot is how to automatically color code Excel spreadsheets based on the universal color coding standards mentioned above. While Excel doesn’t have a built-in feature for this, you can use an add-in to achieve the same result. With just one click, you can automatically apply consistent color coding across your entire spreadsheet.

Beyond Manual Formatting Limits
At first glance, the universal guidelines may seem straightforward—just select and color—but applying advanced coloring rules manually becomes increasingly complex.

For instance, consider a spreadsheet with actuals and forecasts for multiple projects, each starting at different times. In this case, you can’t simply highlight a block of cells and apply the blue formatting for hardcoded numbers. Instead, you’d need to carefully select each project line and manually color actuals and forecasts.

The challenge grows further if you want to differentiate single references (e.g., =B52) from other formulas or highlight formulas containing suspicious hardcoded numbers (e.g., =B52*1.53). Inspecting and formatting every single cell manually would be incredibly time-consuming - but it needs to be done properly every time!

Instantly format your Excel model

ProForma is an Excel add-in that smartly color-codes cells to give you an edge in building complex financial models.

Get it from the Add-ins Store

Learn Shortcuts: Top Excel Users Rarely Use the Mouse

You don’t need to memorize hundreds of shortcuts. Mastering just 15-20 essential ones can increase your speed by at least 50%. Here are the must-know shortcuts:

Navigation and Selection:

Ctrl + Arrow Keys: Jump to the edge of data in any direction.

Ctrl + Shift + Arrow Keys: Select blocks of cells quickly.

Editing:

F2: Edit the active cell or go into a formula.

F4: Repeat the last action (e.g., formatting, inserting rows).

Ctrl + `: Show all formulas on the sheet.

Adding and Deleting Rows/Columns:

Ctrl + Shift + (plus): Insert a new row or column.

Ctrl + (minus): Delete a row or column.

Copy and Paste Special:

Alt + E + S: Opens Paste Special.

• Use V for values, F for formulas, or T for formats.

Auditing Formulas:

Ctrl + [: Go to the first precedent (linked cell).

Freeze Panes for Easier Navigation

When working with large models, freezing panes keeps headers and important rows/columns visible as you scroll through them.

How to Do It:

1. Select the top most and left most cell that you would like to freeze. In the above case it will be cell E4
2. Go to the View tab.
3. Click Freeze Panes > Freeze Panes.

Use Consistent Names between Sheets and Titles

Your sheet names below and at the top of the sheet need to be consistent. Sometimes you will update one and forget to update the other.

Lukily there is a cool trick that can automate this task for you. In cell B2 (where the title of your Excel sheet should be), write the below formula

Now every time you will update the sheet name below, the title name will update automatically!

Set Up Formulas for Faster Review

Clean, transparent formulas are easier to review and debug!

Ctrl + [ is a powerful shortcut to trace precedents in a formula. Sadly, it will only take you to the first precedent. Therefore, if you have a formula that has 2 references, of which one is from another sheet, it is best to put if first. That way you can jump to the other sheet with Ctrl + [, without having to touch your mouse.

INCORRECT: = F2 + Sheet 2! B52

CORRECT: = Sheet 2! B52 + F2

Tracing precedents can be more challenging in longer formulas across multiple sheets. An Excel add-in can help you speed up the process of tracing precedents without loosing sight of your logic.

Trace precedents faster

Tracy is a free Excel add-in for Mac and Windows that simplifies formula auditing with shortcuts to quickly trace precedents.

Get it from the Add-ins Store

Clean Up Your Model

A clean model is easier to review and understand. Here’s what you should do:

Unhide All Sheets: Right-click any sheet tab > Select Unhide > Unhide all relevant sheets.

Unhide All Rows and Columns: Select the entire sheet (Ctrl + A), right-click on the row/column numbers, and choose Unhide.

Delete Unused Data: Remove calculations, rows, or columns that are not part of the final output.

Set the Same Zoom for All Sheets and Go to Cell A1

Final Thoughts

By following these simple yet powerful tips, you can create clean, organized, and professional financial models without spending hours on unnecessary details. These steps will transform the way you work, allowing you to focus on the analysis rather than battling Excel. Remember, mastering just a few shortcuts and habits can give you huge gains in speed and efficiency.

Start applying these tips today, and you’ll be amazed at how quickly you become a pro in financial modeling!