All Excel Formatting Options Explained in a Complete Guide
.png)
This is a list of all Excel formatting options and how to use each one. A spreadsheet that is hard to read is a spreadsheet that does not get used. Whether you are presenting financial data to a board, sharing a project tracker with your team, or building a report for a client, the way your Excel data looks is almost as important as the data itself. Poor formatting makes accurate data look untrustworthy. Strong formatting makes complex data instantly understandable.
This guide covers every Excel formatting option available, explained clearly with practical examples you can apply immediately. Whether you are brand new to Excel or have been using it for years and never properly learned the formatting tools, this is the complete reference you need.
Why Excel Formatting Matters More Than Most Users Realize
Most people who use Excel spend 90% of their time on data and formulas and 10% on formatting. The people who build the spreadsheets that actually influence decisions flip that ratio during the final stage of their work. They know that a well-formatted spreadsheet communicates credibility, makes patterns visible at a glance, and reduces the cognitive load required to extract meaning from a table of numbers.
Formatting in Excel is not decoration. It is communication. Every formatting decision you make either helps the reader understand your data faster or makes it harder. The goal of this guide is to make sure every decision you make is the former.
Excel Formatting Options: The Complete Guide to Making Your Spreadsheets Look Professional
A spreadsheet that is hard to read is a spreadsheet that does not get used. Whether you are presenting financial data to a board, sharing a project tracker with your team, or building a report for a client, the way your Excel data looks is almost as important as the data itself. Poor formatting makes accurate data look untrustworthy. Strong formatting makes complex data instantly understandable.
This guide covers every Excel formatting option available, explained clearly with practical examples you can apply immediately. Whether you are brand new to Excel or have been using it for years and never properly learned the formatting tools, this is the complete reference you need.
Why Excel Formatting Matters More Than Most Users Realize
Most people who use Excel spend 90% of their time on data and formulas and 10% on formatting. The people who build the spreadsheets that actually influence decisions flip that ratio during the final stage of their work. They know that a well-formatted spreadsheet communicates credibility, makes patterns visible at a glance, and reduces the cognitive load required to extract meaning from a table of numbers.
Formatting in Excel is not decoration. It is communication. Every formatting decision you make either helps the reader understand your data faster or makes it harder. The goal of this guide is to make sure every decision you make is the former.
1. Cell Formatting: The Foundation of Every Spreadsheet
Cell formatting controls how individual cells look. It covers everything from the font inside the cell to the background color behind it to the border around it. Getting cell formatting right is the first step in building a spreadsheet that looks intentional rather than assembled in a hurry.
Font Formatting
Font formatting controls the typeface, size, color, and style of the text inside your cells. These are the options most users discover first and use most frequently.
Font style is the typeface applied to cell text. Excel defaults to Calibri at 11 points, which is clean and readable for most purposes. Change the font by selecting the cell or range you want to format, then choosing a different typeface from the Font dropdown in the Home tab. For professional spreadsheets, stick to one or two fonts maximum. Mixing multiple fonts in a single spreadsheet almost always looks unprofessional.
Font size controls how large the text appears in the cell. Larger sizes work well for headers and titles. Standard body text in a spreadsheet typically reads best at 10 to 12 points. Anything smaller than 9 points becomes difficult to read on screen and prints poorly.
Font color changes the color of the text itself, as opposed to the cell background. Use font color deliberately. A header in a dark blue or charcoal reads more professionally than one in bright red. Reserve high-contrast colors like red for data that requires immediate attention, such as negative values or overdue dates, where the color carries functional meaning rather than just visual interest.
Bold, italic, and underline are the three basic text style options. Bold is most useful for headers, totals, and any value that needs to stand out from surrounding data. Italic works well for notes, caveats, and secondary information that should be visible but not dominant. Underline is less commonly used in spreadsheets than in documents because borders serve a similar visual function more cleanly.
Practical example: To format a header row with bold white text on a dark background, select the header row, apply bold formatting, change the font color to white, and then apply a dark fill color to the cell background. This creates a clean professional table header that clearly distinguishes the label row from the data rows below it.
Cell Alignment
Alignment controls where the content of a cell sits within the cell boundaries, both horizontally and vertically. Consistent alignment across a spreadsheet dramatically improves readability.
Horizontal alignment positions content left, center, or right within the cell. As a general rule, text labels align left, numbers align right, and headers align to match whatever alignment you are using in the column below them. Centering everything looks tidy at first glance but actually makes data harder to scan because the eye has to search for the start of each value rather than finding it in a predictable position.
Vertical alignment positions content at the top, middle, or bottom of the cell. Middle alignment looks best in most cases, particularly in rows with variable height. Top alignment works well in cells containing wrapped text where you want the content to read from the top of the cell down.
Wrap Text is one of the most useful and most underused alignment options in Excel. When a cell contains more text than fits on one line at the current column width, Wrap Text automatically expands the row height and displays the content on multiple lines within the cell. This is far better than letting text overflow into adjacent cells, which is the default behavior and one of the most common causes of messy-looking spreadsheets.
Merge and Center combines multiple adjacent cells into a single cell and centers the content within it. This is most useful for main titles that span the width of a table. Use it sparingly. Overusing merged cells creates problems with sorting, filtering, and copying data that can turn a formatting convenience into a functional headache.
Indent increases or decreases the space between the cell border and the cell content. Indenting is particularly useful in financial models and hierarchical data where you want to show that certain rows are sub-items of a parent category without changing the actual structure of the data.
Cell Borders
Borders are the lines drawn around the edges of cells. Used well, borders guide the eye through your data and create clear visual separation between different sections of a spreadsheet. Used poorly, they create visual noise that makes everything harder to read.
To apply borders, select your cells, click the Borders dropdown arrow in the Font group on the Home tab, and choose from the available border styles. The most useful options for most spreadsheets are:
Outside borders draw a line around the perimeter of a selected range, visually boxing it in as a distinct section. This works well for tables, totals rows, and any block of data you want to visually separate from surrounding content.
All borders draws lines around every individual cell in a selected range, creating a full grid. Use this for data tables where you want the grid structure to be explicit. Avoid it for summary sections where the visual weight of a full grid competes with the importance of the data.
Bottom border only is a clean way to separate a header row from data rows or to draw attention to a total row at the bottom of a table without boxing the entire section.
Thick box border creates a heavier border around the perimeter of a selection, which helps high-priority sections stand out from surrounding content.
For the most professional results, use borders selectively rather than applying them uniformly across the entire spreadsheet. The contrast between bordered and unbounded sections creates visual hierarchy that guides the reader's attention.
Fill Color
Fill color applies a background color to cells. It is one of the most impactful formatting tools in Excel when used with restraint and one of the most damaging to readability when overused.
The most effective uses of fill color are alternating row shading to make wide tables easier to read across rows, header row backgrounds to distinguish labels from data, and highlighting specific cells or ranges to draw attention to important values or input fields.
To apply fill color, select your cells, click the Fill Color dropdown arrow in the Font group on the Home tab, and choose a color. For professional spreadsheets, choose colors from a consistent palette that complements each other rather than selecting colors at random. Soft blues, grays, and greens work well for structural formatting. Reserve brighter or more saturated colors for data that genuinely requires immediate attention.
2. Number Formatting: How Your Data Displays
Number formatting controls how Excel displays the values inside your cells. It does not change the underlying value, only how it appears. A cell displaying $1,234.56 still contains the number 1234.56. The formatting is cosmetic but critical for readability and professional presentation.
Currency and Accounting Formats
Currency format displays numbers with a currency symbol, thousands separator, and two decimal places by default. Select your cells, open the Number Format dropdown in the Home tab, and choose Currency. You can customize the currency symbol, decimal places, and how negative numbers display through the Format Cells dialog.
Accounting format is similar to Currency but aligns the currency symbols and decimal points vertically across all rows in a column, which is standard practice in financial statements. When you have a column of dollar amounts, Accounting format makes the numbers significantly easier to compare down the column than Currency format does.
The practical difference: use Currency format for individual values and mixed contexts. Use Accounting format for financial statements, budgets, income statements, and anywhere you are presenting a column of monetary values that a reader will scan vertically.
Percentage Format
Percentage format multiplies the cell value by 100 and displays the result with a percent sign. A cell containing 0.15 formatted as Percentage displays as 15%.
This is important to understand when entering data. If you type 15 into a cell and then apply Percentage format, it will display as 1500% because Excel multiplies your entry by 100. If you want to display 15%, either type 0.15 and apply Percentage format, or type 15% directly and Excel will handle the conversion automatically.
To control decimal places in a percentage, use the Increase Decimal and Decrease Decimal buttons in the Number group on the Home tab, or set the decimal places precisely through the Format Cells dialog.
Date and Time Formats
Excel stores dates and times as numbers internally, which is what allows you to perform date arithmetic like calculating the number of days between two dates. The display format determines how that number appears to the reader.
Common date format examples and their display codes:
dd/mm/yyyy displays as 20/05/2026
dd-mmm-yyyy displays as 20-May-2026
mmmm dd, yyyy displays as May 20, 2026
ddd, dd mmm yyyy displays as Wed, 20 May 2026
To apply a date format, select your cells, open the Format Cells dialog with Ctrl+1, go to the Number tab, select Date from the Category list, and choose from the available formats or enter a custom format code.
Time formats work similarly. Common codes include h:mm AM/PM for 12-hour format and hh:mm:ss for 24-hour format with seconds.
Custom Number Formatting
Custom number formatting is one of the most powerful and least understood formatting tools in Excel. It lets you create display formats that are not available in the standard options, which allows you to present data in exactly the way your audience needs to see it.
Custom format codes use a specific syntax with up to four sections separated by semicolons, covering positive numbers, negative numbers, zero values, and text in that order.
Useful custom format examples:
#,##0 displays numbers with thousands separators and no decimal places. 1234567 displays as 1,234,567.
#,##0.00 adds two decimal places. 1234567.8 displays as 1,234,567.80.
0.0"x" adds a suffix. 3.5 displays as 3.5x, useful for multiples and ratios.
"$"#,##0"K" displays values in thousands with a dollar sign. 5000 displays as $5K.
[Green]#,##0;[Red]-#,##0 displays positive numbers in green and negative numbers in red, which is a clean way to apply automatic color coding to financial data.
+#,##0;-#,##0;0 displays explicit plus signs on positive numbers, which is useful for variance analysis where positive and negative values both need to be clearly identified.
To create a custom format, select your cells, open Format Cells with Ctrl+1, go to the Number tab, select Custom from the Category list, and type your format code in the Type field.
3. Conditional Formatting: Formatting That Responds to Your Data
Conditional formatting automatically applies formatting to cells based on the values they contain or the results of a formula you define. It is one of the most powerful tools in Excel for making patterns and exceptions visible without requiring the reader to scan every number manually.
Highlight Cell Rules
Highlight cell rules apply formatting to cells that meet a specific condition. Access them through Home, Conditional Formatting, Highlight Cells Rules.
Greater Than and Less Than highlight cells above or below a threshold you specify. Use this to flag sales figures that exceed a target, budget items that have gone over their allocated amount, or any metric where crossing a threshold requires attention.
Between highlights cells whose values fall within a range you define. Useful for identifying values in an acceptable range or for spotting outliers that fall outside expected bounds.
Equal To highlights cells that match a specific value exactly. Useful for finding specific entries in large datasets.
Text Contains highlights cells that include specific text. Useful for scanning categorical data for particular values or for flagging entries that contain error indicators.
A Date Occurring highlights cells containing dates that fall in a specified time period such as yesterday, last week, last month, or next month. Particularly useful for project trackers and deadline management.
Duplicate Values highlights cells that appear more than once in a range, which is invaluable for data cleaning and identifying data entry errors in lists that should contain unique values.
Data Bars
Data bars add a horizontal bar inside each cell whose length is proportional to the cell's value relative to the other values in the selected range. The cell with the highest value gets the longest bar. The cell with the lowest value gets the shortest.
Data bars are most effective for quick visual comparison of values across a range where the exact numbers are less important than the relative magnitude. A sales leaderboard, a performance ranking, or a budget utilization summary all benefit from data bars because they let readers immediately see which values are high and which are low without reading every number.
Access data bars through Home, Conditional Formatting, Data Bars. Choose from solid fill or gradient fill styles in various colors.
Color Scales
Color scales apply a color gradient across a range of cells based on their values. The most common configuration is a three-color scale where the lowest values display in one color, the highest values in another, and values in the middle in a transitional color.
The classic red to yellow to green color scale is immediately intuitive to most readers: red means low or bad, green means high or good, yellow is in the middle. This makes color scales particularly effective for performance dashboards, heat maps, and any situation where you want to communicate relative performance at a glance.
Access color scales through Home, Conditional Formatting, Color Scales. You can customize the colors, the midpoint, and whether the scale represents the actual minimum and maximum in the range or specific values you define.
Icon Sets
Icon sets add a small icon to each cell in a range based on the cell's value relative to the other values or to thresholds you define. Common icon set options include traffic lights in red, yellow, and green, directional arrows pointing up or down, and rating stars.
Icon sets work particularly well in dashboards and summary tables where you want to communicate status at a glance without relying on color alone, which is important for accessibility and for data that will be printed in black and white.
Access icon sets through Home, Conditional Formatting, Icon Sets. You can reverse the icon order and define custom thresholds for each icon through the Manage Rules option.
Formula-Based Conditional Formatting
The most powerful form of conditional formatting uses a custom formula rather than a built-in rule. This allows you to apply formatting based on virtually any condition you can express as an Excel formula, including conditions that reference other cells in the spreadsheet.
Common uses for formula-based conditional formatting include highlighting entire rows based on the value in a specific column, applying formatting to cells based on whether they meet multiple conditions simultaneously, and creating dynamic formatting that updates automatically when underlying data changes.
To create a formula-based rule, go to Home, Conditional Formatting, New Rule, select Use a Formula to Determine Which Cells to Format, and enter your formula. The formula must return TRUE for formatting to be applied. Reference the top-left cell of your selected range in the formula and Excel will apply the logic to each cell in the range automatically.
4. Cell Styles: Consistent Formatting at Scale
Cell styles are saved formatting configurations that you can apply to cells with a single click. They are the most efficient way to maintain consistent formatting across a large spreadsheet or across multiple spreadsheets that need to look the same.
Built-In Cell Styles
Excel includes a library of built-in cell styles accessible through Home, Cell Styles. These include styles for headings at different hierarchy levels, styles for data entry cells, styles for calculated cells, and styles for totals and summary rows.
The heading styles are particularly useful for creating a clear visual hierarchy in complex spreadsheets. Heading 1 is the largest and most prominent. Heading 2, 3, and 4 decrease in visual prominence in a consistent way that communicates the hierarchical relationship between different sections of your spreadsheet.
Custom Cell Styles
You can create your own cell styles that match your organization's branding or your personal formatting preferences. Format a cell exactly the way you want, then go to Home, Cell Styles, New Cell Style, give it a name, and save it. Your custom style is now available in the Cell Styles gallery and can be applied to any cell with a single click.
The real power of custom styles becomes apparent when you need to change a formatting element across many cells. Instead of manually updating each cell, you modify the style definition and every cell using that style updates automatically.
5. Format Painter: Copy Formatting Instantly
The Format Painter is the fastest way to apply the formatting from one cell to another without manually reapplying every individual formatting option.
Select the cell whose formatting you want to copy. Click the Format Painter button in the Clipboard group on the Home tab, the icon that looks like a small paintbrush. Your cursor changes to a crosshair with a paintbrush icon. Click the cell or drag across the range where you want to apply the formatting. The formatting is applied instantly.
For applying formatting to multiple non-adjacent ranges, double-click the Format Painter button instead of single-clicking. This locks the Format Painter on so you can apply the same formatting to as many ranges as you want. Press Escape when you are done.
The keyboard shortcut for Format Painter is to select the source cell, press Ctrl+C to copy, select the destination cell or range, and press Ctrl+Alt+V to open the Paste Special dialog, then select Formats and click OK. This achieves the same result as Format Painter and is faster for users who prefer keyboard shortcuts.
6. Table Formatting: The Fastest Way to Format a Data Range
Converting a data range to an Excel Table using Ctrl+T or Insert, Table automatically applies a complete set of formatting including alternating row colors, styled headers, and filter dropdowns. Table formatting also updates automatically when you add new rows or columns, which eliminates the common problem of manually extending formatting every time your data grows.
Excel provides dozens of built-in Table styles ranging from light to dark, accessible through the Table Design tab that appears when your cursor is inside a table. You can also create custom Table styles that match your preferred color scheme.
Beyond formatting, Tables provide functional benefits including automatic formula extension, structured references that make formulas easier to read, and built-in filter and sort controls. For most data ranges in Excel, converting to a Table is the single formatting decision with the highest combined impact on both appearance and functionality.
Frequently Asked Questions
How do I format cells in Excel?
Select the cells you want to format, then use the formatting options on the Home tab including font style, size, color, alignment, borders, and fill color. For more options including number formats and advanced alignment settings, open the Format Cells dialog by pressing Ctrl+1 or right-clicking and selecting Format Cells.
What is conditional formatting in Excel?
Conditional formatting automatically applies formatting to cells based on their values or the results of a formula. It is used to highlight cells that meet specific conditions, add data bars or color scales for visual comparison, and apply icon sets for status indicators. Access it through Home, Conditional Formatting on the ribbon.
How do I copy formatting in Excel without copying the content?
Use the Format Painter tool on the Home tab to copy formatting from one cell and apply it to another. For more control, copy the source cell with Ctrl+C, select the destination, open Paste Special with Ctrl+Alt+V, choose Formats, and click OK. This pastes only the formatting without affecting the cell content.
What is the difference between Currency and Accounting format in Excel?
Both display numbers with a currency symbol and decimal places but they differ in alignment. Currency format places the currency symbol immediately before the number. Accounting format aligns all currency symbols at the left edge of the cell and all decimal points vertically, which is standard practice for financial statements and makes columns of monetary values easier to compare.
How do I create a custom number format in Excel?
Select your cells, press Ctrl+1 to open Format Cells, go to the Number tab, select Custom from the Category list, and type your format code in the Type field. Format codes use symbols like 0 and # for digit placeholders, commas for thousands separators, and quoted text for literal characters. You can preview how your format will look before applying it.
How do I apply alternating row colors in Excel?
The easiest method is to convert your data range to a Table using Ctrl+T, which automatically applies alternating row colors and updates them as data grows. For manual alternating colors without converting to a Table, use conditional formatting with the formula =MOD(ROW(),2)=0 applied to your data range and set your desired fill color for the formatting.
Why is my conditional formatting not working?
The most common causes are formula syntax errors in formula-based rules, incorrect cell references that do not account for absolute versus relative referencing, rules that conflict with each other where Excel applies them in priority order from top to bottom, and formatting applied after conditional formatting that overrides it. Open Manage Rules through Home, Conditional Formatting to review the rules applied to a range and adjust their priority or scope.
What are cell styles in Excel and how do I use them?
Cell styles are saved formatting configurations that apply multiple formatting options at once with a single click. Excel includes built-in styles for headings, data entry cells, and calculations accessible through Home, Cell Styles. You can create custom styles by formatting a cell exactly as desired and selecting New Cell Style from the Cell Styles gallery. Custom styles update all cells using them simultaneously when the style definition is modified.
Learn more in the Excel course here.
Learn more about Excel here
How to make a project progress dashboard in Excel
10 Tips for Creating Dashboards in Excel and Google Sheets
10 Excel Functions Every User Should Know