It’s easy to miss critical details when analyzing and reconciling financial transactions, especially during month-end or year-end close. The pressure to meet deadlines sometimes results in miscalculations, typos and other errors.
To prevent these errors, you need to format your Excel spreadsheet properly.
This means using bold headings, color coding, and other formatting techniques to make it easy to distinguish between different sections of your spreadsheet and fix potential errors, so you can turn in accurate financial reports.
Below, we cover 10 ways to format your spreadsheet, including guidelines, Excel best practices and additional resources to help you make your financial reports look polished and professional.
Number formatting lets you customize the appearance of numerical values on your spreadsheet. For example, you can:
For example, say you're preparing for month-end close. Financial statements should follow certain guidelines when it comes to how the numbers are presented.
Currencies are often punctuated with the comma style, without any decimals. The currency symbol only needs to appear once on the first row and the Net Income row.
Click and drag your mouse over the range of cells you want to format.
Or, click on the first cell and then hold down the Shift key (for Windows) or Command key (for Mac) to choose the cells you want.
Next, click on the Home tab on the Excel ribbon and navigate to the dropdown that says "General." This dropdown lets you select your desired number format.
Click on the dropdown to view the options and choose the accounting number format. It ensures all the symbols and decimal points neatly line up in a column, so it’s easier to read and compare the numbers.
Alternatively, right-click on the selected cells and click on "Number Format."
A dialog box will appear with a list of different number formatting options and more flexibility to customize the values. Click on the accounting format.
The default number of decimal places is two (since it is in currency format) and the symbol is the general dollar sign ($). To change the currency symbol, click on the dropdown arrow and choose the appropriate one.
To remove the decimal points, click on the dropdown and select “0.”
Once satisfied, click "OK" to apply the formatting to the selected cells.
There are also other relevant formatting options you can select from the list, with different use cases:
| Number Format | Use Case |
| Number | Used for general numerical values. It also allows you to specify the number of decimal places, include a thousands separator, and choose how to display negative numbers |
| Currency | Used for monetary values and includes a currency symbol, a specified number of decimal places, and a thousands separator |
| Short date | Used for displaying the day, month, and year (e.g., "4/13/2024") |
| Long date | Includes the day of the week and the full name of the month (e.g., "Tuesday, April 13, 2024") |
| Time | Formats time values, allowing you to specify the hours, minutes, and seconds in various formats (e.g., "13:45:00" for 1:45 PM) |
| Comma | Adds a thousands separator to large numerical values (e.g., "1,000" instead of "1000") |
| Percentage | Converts numerical values into percentages and displays them with a percentage symbol (e.g., "50%" for 0.5) |
| Fraction | Formats numerical values as fractions (e.g., "1/2" for 0.5) |
| Special | Offers additional formatting options, such as displaying numbers as fractions, ZIP codes, phone numbers, or postal codes |
| Custom | Lets you create your own custom formats to display numbers exactly the way you want |
Color coding text cells in Excel makes it easier to identify and analyze different types of data. For example, say you want to analyze sales data for different products. You can use different colors to differentiate the sales performance of each product category.
First, review your data and identify categories that you want to color code.
Click and drag your mouse over the range of cells you want to format. Or, click on the first cell and then hold down the Shift key (for Windows) or Command key (for Mac) to choose the cells you want.
Next, go to the "Home" tab on the Excel ribbon. In the Home tab, click on the dropdown button next to the Fill Color icon. Then select the color you want.
When choosing colors, follow these Excel color coding best practices:
Clear and well-formatted text makes it easier for stakeholders to quickly understand and interpret the information in your financial reports.
For example, using formatting such as bold, italics, and underlining, lets you emphasize headers, titles, or key information, making it easier to navigate through large datasets and identify important sections.
Some common ways to format text in Excel include:
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 use bold for final values like total income and italic for preliminary values like total revenue, like this:
To do this, select the cell(s) containing the text you want to format. Then go to the "Home" tab on the Excel ribbon. Click the "B" icon to make the text bold.
To italicize the text, click on the “...” sign on the Home ribbon, then click on "I."
From the "Home" tab on the Excel ribbon, click the "Font Name" dropdown to select the desired font.
Click on the “Font Size” dropdown beside it to change the size.
In the Home tab, click on the dropdown button next to the Font Color icon. Then, select the color you want.
Conditional formatting formats cells automatically when their values meet certain conditions. For example, in the variance column of a budget vs. actual report, you can highlight variances greater than $1,000 with light red fill and dark red text colors.
First, select the entire column that you want to apply this rule to. In the Home tab, click on the Conditional Formatting icon
For the above example, select Highlight Cell Rules and Greater Than.
A box will appear on your screen for you to set rules. Following our example of budget variances greater than $1,000, in the empty box, enter 1000 and use the drop-down menu on the right to select a specific formatting option (in this case light red fill with dark red text). Click “OK”.
You can always create additional formatting rules using the other available options. As you get more proficient with Excel, you'll even be able to create custom formatting conditions with your own unique formulas.
Filters let stakeholders consuming your financial reports easily sort or filter specific columns based on criteria such as category, date, or amount, to identify trends, outliers, and patterns in the data. This way, you can compare performance across different categories, periods, or segments.
Highlight the range of cells containing the data you want to filter, then navigate to the "Data" tab in the Excel ribbon and click on "Filter."
This adds drop-down arrows to the header row of each column in the selected range.
Click on the drop-down arrow in the header of the column you want to filter. This will display a list of unique values or a search box, depending on the data type.
Choose the filter options that meet your criteria. You can select specific values, sort data in ascending or descending order, or use text filters such as “contains”, “begins with”, or “ends with”. Then click "OK" or "Apply" to apply the filter.
Excel will filter the data based on your selected criteria, hiding rows that do not meet the specified conditions. To remove filters, click on the filter drop-down arrow in the header of the filtered column and select "Clear Filter."
The table feature in Excel is useful for making large volumes of data more readable, sortable and user-friendly. For example, say you want to categorize some individual assets by their acquisition date, original value, current market value, etc.
Plus, if you want to use Microsoft Copilot to draw AI-powered insights from your data, you’ll need to format your 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.
Formatting your table this way makes it easier to read, as well as sort or filter the data by color for any of the columns.
Format Painter saves time by allowing you to copy the formatting of one cell or range and apply it to another with just a few clicks. This eliminates the need to manually format each cell individually, especially when dealing with large datasets.
Plus if you have complex formatting, such as custom number formats, conditional formatting, or merged cells, Format Painter will replicate these settings automatically.
Highlight the cell(s) you want to copy the format from, then click on the Format Painter icon located in the Home tab.
Next, hover your cursor over any cell and you'll see an active paintbrush. Click to 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:
When building any spreadsheet, Balance Check Formatting is a useful feature that can help you avoid data entry errors. 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 its numbers, you track them on a spreadsheet, carefully itemizing departmental details and subtotals that make up the company's grand totals.
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 following these steps:
Charts allow stakeholders to quickly identify trends, patterns, and outliers in financial data over time. For example, a line chart showing revenue trends or fluctuations in expenses lets stakeholders assess the business’s financial performance.
Select the key metrics or trends that you want to visualize in the financial statements, such as revenue, expenses, profit margins or cash flow.
Then choose the appropriate chart type that best represents the data. Here’s a visual to help you determine which type of chart may best meet your data representation needs.
Common types of charts used in financial statements include line charts, bar charts, pie charts, and scatter plots.
To insert a chart into your spreadsheet, click on the Insert tab on the Excel ribbon and navigate to the section that says "Charts."
Click on your choice chart and it will be inserted in the same sheet. From the chart editor at the right side of the screen, click on the “Data” tab to select the data range for the chart and the “Format” tab to customize the chart.
Ensure that the chart is properly labeled with clear titles, axis labels, and data labels as needed. Format the chart elements, such as colors, fonts, and gridlines, to improve its readability and visual appeal.
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 1). Cascade this format throughout the entire income statement.
First, copy the format across the row with these steps:
Next, copy the format from the first row to the columns, like this:
To make your financial reports clear and visually appealing, be sure to follow these best practices:
Follow the Excel sheet formatting tips in this guide to make your financial reports easy to read and navigate.
However, even if you follow them to the letter, this doesn’t guarantee the accuracy of your data, which is a critical requirement for financial statements.
If you want to go beyond formatting to get the most out of Excel for FP&A processes and ensure accurate year-, quarter-, or month-end close financial reports, use Vena.
The platform offers the familiar Excel interface and adds enterprise-class features including workflow automation, version control and audit trails.
Vena integrates seamlessly with all your existing source systems and pulls 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.
Looking to learn more about creating charts in Excel? Explore the free course series in Vena Academy.