NEWVena-Logo NEWVena-Logo
Request Demo
Blog

How to Format Your Excel Spreadsheet: 10 Tips

February 5, 2020 |

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:

 

1. How to Use Text Colors

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:

To color your text:

1

 

2. How to Use Bold and Italic

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:

2-1

Here’s how to bold and italicize text:

3-1

 

3. How to Use Conditional Formatting

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:

4

5

6

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

 

4. How to Copy Formatting With the Fill Handle

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.

7

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.

8-1

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

9-1

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

10-600x355

 

5. How to Copy Formatting With the Format Painter

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:

11

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

 

6. How to Format Data as a Table

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:

12-600x327

13-504x600

14-600x218

15-600x287

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.

 

7. How to Format Numbers

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.

16

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

17

18

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

19-600x281

20

 

8. How to Format Percentages

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:

21

Here’s how to format percentages without any decimals:

22

 

9. How to Format Dates

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:

23

24-235x600

25

10. How to Use Balance Check Formatting

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:

26

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.

Recommended Posts

40% of Finance Teams Don’t Perform Scenario Analysis When Forecasting, Survey Finds

July 28, 2020 |

Read More

3 Eye-Opening Takeaways From the Inaugural Vena Industry Benchmark Report

July 22, 2020 |

Read More

Ease of Use the #1 Driver of BPM Software Success, Survey Finds

June 17, 2020 |

Read More