As I have mentioned in Why Is Best Practice Modeling Important, using consistent formatting throughout an Excel workbook is very important.
Imagine opening this when you need to make a million-dollar decision.
You can’t! You would need to spend days trying to understand everything and you’ll have doubts if the spreadsheet is even working properly.
In this post, I’ll discuss some tips to make you an Excel formatting wizard. Sounds good? Then let’s get going.
How To Format A Messy Excel Spreadsheet
Make Your Inputs Easy To Identify
Consistent coloring is an important aspect of Excel formatting. If you’re trying to break into Wall Street or some financial modeling firm, there’s a “general” standard of formatting for inputs, calculations, and off-sheet links. These are:
- Blue font for input cells or historical data – some places also like having a yellow background in each input cell
- Black font for any formulas and calculations
- Green font for links to other tabs in the same Excel workbook
- Red text labels for any rows or columns of data that use a named range for VBA macros
For everyone else, you’re free to use whatever color coding system you’d like. However, I would suggest including a legend in the Excel workbook to clarify the color coding you end up working with!
Unless you’re super familiar with text color formatting or have built your own shortcuts, the easiest way to set this up would be to use the cell styles under the Home ribbon. You can quickly reference and update the cell style instead of trying to replicate the format every time.
Set Up A Workbook Like A Book
Most people will read your workbook like a book. That is, left to right and top down. Create a clear structure and flow for users.
It’s also a good idea to split themes into separate tabs as well. For example, inputs, calculations, and outputs should be on different tabs. Also, within calculations, you might have one for sales and one for production costs. Think about how a reader might approach your workbook when you put it together.
Add Labels (And Units)
Nothing worse than a random cell that’s being used but no one understands what it relates to or how it was sourced. When in doubt – add a label.
Also, if your units are in million dollars, add a unit label somewhere!
Interesting read – How To Format Yes Or No In Excel
Create Easy To Read Charts
End users of a spreadsheet will rely on key outputs. Along with financial metrics in a table, results are usually depicted in charts. Ensure that charts are formatted in a way that users can easily draw conclusions. For example:
Charts don’t need fancy formatting (like the left one). If anything, it detracts readers from the key messages. Always go for the simple, clean approach like the right chart.
One other suggestion would be to create a “Charts” output tab. Use this tab to aggregate all the underlying chart references. It will make your life easier when you’re trying to trace back and understand certain trends.
Related article – How To Quickly Copy A Chart Format In Excel
Printing Format Options
On a similar note, make sure your outputs are easily printable (or exportable to slides). Take a few minutes to set up the correct printing options.
Don’t run into the situation where a last-minute meeting has been set up and you’re printing out reams and reams of paper for an illegible Excel workbook!
Set the appropriate borders, margins, and orientation under the Page Layout tab and have a look via the Print Preview window.
Learn The Formatting Keyboard Shortcuts
If you’re spending a good portion of your day in Excel, you can improve your Excel productivity by learning some of the formatting shortcuts. Here are some of the ones I think will be most useful:
- CTRL + B (or I or U) – bold, italicize, or underline cells
- F4 – this repeats the last action. For example, if you changed a cell text to blue, select the next cell, hit F4 and it’ll also color it blue.
- CTRL + 1 – This opens the format dialog box for anything you’ve selected in Excel
- ALT + E, S, T – Copy a cell, use the shortcut and it will paste the format to another cell. More info in Copy Paste Like A Pro In Excel
- CTRL + T – This applies the table formatting to the current area of cells
Think of formatting an Excel workbook as making a first impression. It doesn’t take up much time and it really adds to the output of your work.
Thanks for reading and I hope this helps your Excel productivity!
Other Excel tutorials you may also like: