How to Format Your Excel Spreadsheet: 10 Tips

The most critical aspect of any Excel spreadsheet is having the right numbers in the right cells, especially if you're working in finance. But formatting--which means customizing spreadsheets to look and feel a certain way--is pretty important too. An attractive spreadsheet will draw users in, and good formatting will focus their attention exactly where you want it to go.

A skilled finance professional with a keen eye for formatting can produce spreadsheets that are both beautiful and functionally effective. That's because consistency, efficiency and clarity can be the difference between an adequate Excel model and a great one. 

All it takes are some basic formatting guidelines and best practices. Let's get started with our top 10 spreadsheet formatting tips:

Color coding text in Excel gives context to the data. Merely glancing at the spreadsheet can help you understand what kind of data you're working with. For financial models, follow these guidelines to determine which colors to use:

• Blue for constants and hard-coded numbers like historical data and assumptions.
• Black for formulas linked to other cells within the same worksheet.
• Green for formulas with links to other worksheets within the same file.
• Red for formulas with external links to another file.

To color your text:

• Select the cell(s) you wish to color.
• In the Home tab, click on the dropdown button next to the Font Color icon.
• In the color palette provided, select the color you want.

Bold text is used for emphasis, and italic text indicates that there's something distinctive about the data. For instance, on a monthly cash flow spreadsheet, you might want to bold the subtotals and use italics to display percentages, like this:

Here's how to bold and italicize text:

• Select the cell(s) you want to bold or italicize.
• Under the Home tab in the Font section, find the icons for Bold and Italic and click the one you want. You could also click both icons and format the text both ways, if you wish.

Conditional formatting allows you to format cells automatically when their values meet certain conditions. For example, in the variance column of a budget vs. actuals report, you might want to highlight variances greater than $1,000 with light red fill and dark red text colors. Follow these steps to apply this rule and format values greater than $1,000:

• First, select the entire column that you want to apply this rule to.
• In the Home tab, click on the Conditional Formatting icon.

• Select Highlight Cell Rules and Greater Than.

• A pop-up box will appear reflecting your previous selections. In the empty box, enter 1000 and use the drop-down menu on the right to select a specific formatting option. Click OK.

• You can always create additional formatting rules using the other available options. As you get more proficient, you'll even be able to create custom formatting conditions with your own unique formulas.

Up to your eyeballs in spreadsheets? Learn how White Castle cut 400 budget spreadsheets down to just one!

The Fill Handle is located on the bottom right corner of a selected cell. When you hover your cursor over the dot, it will turn into a solid black cross.

You can use the Fill Handle to copy the formatting from one cell and apply it to other adjacent cells. The method for copying formats across rows is different than copying down columns.

Let's say you're creating a monthly income statement with the months as your column headers and the various revenue/expense categories as your rows. In the first fillable cell, enter a value and format it with the comma style, no decimals (see tip #7 below). You'll then want to cascade this format throughout the entire income statement.

First, copy the format across the row with these steps:

• Select the formatted cell.
• Hover the cursor over the Fill Handle.
• When you see the solid black cross, drag it across the row to the last cell that needs formatting.
• The value and format from the first cell will be copied across, but you only want to copy the format.
• Click on the Auto Fill Options icon next to the last copied cell to display a list of options (pictured below).
• From this list, select Fill Formatting Only. The numbers will disappear from the copied cells, leaving only the original number. You have now finished formatting the first row.

Next, copy the format from the first row to the columns, like this:

• Highlight the first row with your cursor.
• Hold the mouse over the Fill Handle and double click.
• All the cells down to Net Income will fill sequentially with the same format as the first row (pictured below).
• Click on the Auto Fill Options icon and select Fill Formatting Only. Once again, all the numbers below the first row will disappear, leaving only blank, pre-formatted cells.

The Format Painter makes it easy to copy the formatting from an individual cell and apply it to another part of the spreadsheet. If you're trying to format cells or groups of cells that aren't right next to each other, this is the tool you need. Here's how to use it:

• Highlight the cell(s) you want to copy the format from.
• Click on the Format Painter icon located in the Home tab.
• Hover your cursor over any cell and you'll see an active paintbrush. Just click or highlight the cells you want to apply the new formatting to. 

To lock the Format Painter and apply new formatting to multiple groups of cells, follow these steps:

• Highlight the area you want to copy the format from.
• Instead of clicking the Format Painter icon once, double click on it.
• Then single click or highlight the cells you're copying to. The paintbrush will remain active and you can continue to copy formatting to other cells or groups of cells.
• When finished, press the Escape key to get rid of the active paintbrush.

The table feature in Excel is useful for making large volumes of data more readable, sortable and user-friendly. Let's say, for example, that you were trying to categorize some individual assets by their acquisition date, original value, current market value, etc.  

Here's how you would format that data as a table:

• Select a cell that's within the data range you want to turn into a table.
• In the Home tab, click on the Format as Table icon.

• Pick a format from the table styles provided in the next pop-up box.

• If there are no blank cells in your data, Excel will automatically detect the required range for the table. Otherwise, you'll need to input your data range manually.

• Make sure you select My Table Has Headers in the pop-up box (pictured above).
• Click OK, and a table will be created.

As you can see, the table is now much easier to read. If you want to sort or filter the data in any of the columns, simply click on the drop-down menu in the column header and make your selection. There are lots of other table features and options you can explore as well.

If your finance team loves spreadsheets, check out this free guide from the Association for Financial Professionals, Making Excel Work for FP&A.

Numbers on a spreadsheet should be formatted based on what they're meant to convey. Financial statements, for example, follow certain guidelines when it comes to how the numbers are presented. In these reports, currencies are often punctuated with the comma style, without any decimals. The currency symbol might only need to be shown once on the first row, and again on the Net Income row.

To format your numbers like the example above, follow these steps:

• Select the first cell at the top of the column.
• Under the Home tab in the Number section, click on the Accounting Number icon, which is represented by the dollar sign (pictured below).
• Two decimal points will be automatically added to the number, as well as the currency symbol on the left. To remove the decimal points, click on the Decrease Decimals icon twice, once for each decimal (pictured below).
• Copy this formatting to the Net Income cell with the Format Painter, then bold the cell and add borders if you wish.

• Now, format the rest of the numbers in the column by selecting all cells between the first and last rows.
• Under the Home tab in the Number section, click on the Comma Style icon (pictured below).
• Two decimal places will be automatically added to the numbers. To remove them, click on the Decrease Decimals icon twice, once for each decimal.

You can also format large groups of numbers using the Format Cells menu, like this:

• Highlight a group of cells to be formatted.
• Under the Home tab in the Cells section, click on Format and then Format Cells.

• Pick the relevant format from the list in the next pop-up box. To follow the same example as above, select Accounting, and in the Decimal Places field, enter 0 (pictured below).
• In the Symbol field, use the drop-down menu to select None, or the appropriate currency symbol if required.

Percentages are often required in spreadsheets that include metrics such as variances, growth assumptions, proportions and more. If you're analyzing various sources of revenue, for example, you'll want to use percentages to display the proportion of each revenue source to the total revenue, as shown below:

Here's how to format percentages without any decimals:

• Highlight the cell(s) you want to format.
• Under the Home tab in the Number section, click on the Percent Style icon(pictured below).
• Two decimal places will be automatically added to the numbers. To remove them, click on the Decrease Decimals icon twice.

Including dates in your spreadsheet is crucial for financial statements, cash flows, budgets, and any other financial model. Preferences and best practices determine how dates are formatted. Balance sheets, for example, often have dates formatted the long way, like this: December 31, 2019.

Excel provides quick access in the Home tab if you want to format dates using the most common styles. Here's how to access it:

• Click the Number Format box in the Home tab, and a drop-down menu will appear.

• Two date formats are available here, Short Date and Long Date.

• For more unique date formatting options, click on More Number Formats (pictured above).
• In the Format Cells box (pictured below), select Date on the left and then find the style that suits your needs.
• Select the style and click OK.

When building any spreadsheet, Balance Check Formatting is a useful feature that can help you avoid data entry errors. Even if you're an Excel whiz and a finance expert, you're still human, and humans make mistakes sometimes! However, it's easy to actively check for common errors with Balance Check Formatting if you understand how these mistakes happen. 

Let's assume you're putting together a budget for capital spending. As each department submits their numbers, you're tracking them on a spreadsheet, carefully itemizing departmental details and subtotals that make up the grand totals for the company. To ensure that the grand totals aren't missing an item or a department, you can create a Balance Check formula and make it visible by placing the formula right below the grand totals. Here's how to do it:

• Below the grand totals, create a formula that sums up the all the individual company spending lines. Make sure it's a different formula than the one you used to derive the grand totals. So, if you just added the two departmental subtotals to get the grand totals the first time, make sure this new formula includes all the individual spending lines instead.
• Extend the formula that you've just created by referencing and subtracting the grand totals right above. The result should be zero, and imbalances clearly visible. Use conditional formatting to highlight any value that's not equal to zero.
• In the example below, you can see that a spending line in year two was missed in your original formula, causing the error.

Keep in mind that when you integrate Excel with an external software application, it's likely that your general ledger entries or other data will be exported into spreadsheets. To avoid errors, build a Balance Check formula to catch incomplete or incorrect data extracts. You'll be able to formulate your expected totals and compare them to the actuals that are downloaded into Excel, just like the example above.

Get the Most Out of Excel

Whether you use colors, fonts, professional number styles, conditional formatting or all of those combined, it's important to follow this guide if you want your financial spreadsheets to be elegant and efficient. 

And if you really want to get the most out of Excel for FP&A processes, Vena Solutions adds enterprise-class features including workflow, version control and auditability. Vena can integrate seamlessly with all your existing source systems and pull your financial data right into Excel, making it easy to automate traditionally manual processes like template formatting, budget to actuals variance analysis, and report generation. 

So, if you're looking to streamline FP&A processes at your organization, don't just "rip and replace" Excel with an unfamiliar tool that your team doesn't know how to use. Instead, get the best of both worlds with Vena: the flexibility, familiarity and power of Excel, with all the modern features of a cloud solution.