In Excel, formatting spreadsheet (or sheet) data is easier than ever. You can use several quick and easy ways to create professional-looking spreadsheets that display data efficiently. For example, you can use document themes for a consistent look across all Excel spreadsheets, styles to apply predefined formats, and other manual formatting features to highlight important data.
Work with Document Topics:
A document theme is a predefined set of colors, fonts, and effects (such as line styles and fill effects) that will be available when you format your spreadsheet data or other elements, such as tables, pivot tables, or charts. For a smooth, professional look, a document theme can be applied to all Excel workbooks and other Office documents.
Your company can provide a corporate document theme that you can use, or you can choose from a variety of predefined document themes that are available in Excel. If necessary, you can also create your own document theme by changing any or all of the theme colors, fonts, or effects on which a document theme is based.
Before formatting spreadsheet data, you may want to apply the document theme you want to use, so that the format you apply to spreadsheet data can use colors, fonts, and effects determined by that subject of the document.
For information on working with document themes, see Apply or customize a document theme.
Use styles to quickly format data:
A style is a format based on a predefined theme, and can often be applied to change the appearance of data, tables, charts, pivot tables, shapes, or diagrams. If the predefined styles don’t suit your needs, you can customize a style. For charts, you can customize a chart style and save it as a chart template that you can reuse.
Depending on the data you want to format, you can use the following Excel styles:
- Cell Styles To apply multiple formats in one step and make sure cells are consistent, you can use a cell style. A cell style is a defined set of formatting characteristics, such as fonts and font sizes, number formats, cell borders, and cell shading. To prevent any user from making changes to specific cells, you can also use a cell style that locks the cells.
Excel has several predefined cell styles that you can apply. If necessary, you can modify a predefined cell style to create a custom cell style.
Some cell styles are based on the document theme that applies to the entire book. When switching to another document theme, these cell styles are updated to match the new document theme.
For more information about working with cell styles, see apply, create, or remove a cell style .
- Table Styles To quickly add professional, designer-quality formatting to an Excel table, you can apply a predefined or custom table style. By choosing one of the predefined alternative row styles, Excel maintains the alternate row pattern by filtering, hiding, or rearranging the rows.
For information on working with table styles, see Format an Excel table .
- PivotTable Styles To format a PivotTable, you can quickly apply a predefined or custom PivotTable style. As with Excel tables, you can choose a predefined alternative row style that maintains the alternate row pattern by filtering, hiding, or rearranging the rows.
For information about working with PivotTable styles, see Design the layout and format of a PivotTable report.
- Chart Styles Apply a predefined style to the chart. Excel provides a variety of useful predefined chart styles to choose from, and you can customize a style if necessary by manually modifying the style of individual chart elements. You cannot save a custom chart style, but you can save the entire chart as a chart template that you can use to create a similar chart.
Format Data Manually
To highlight specific data (such as text or numbers), you can format the data manually. Manual formatting is not based on the theme of the book document unless you choose a theme font or use theme colors; the manual format remains the same when changing the subject of the document. You can manually format all the data in a cell or range at the same time, but you can also use this method to format individual characters.
Use Border and color to highlight the Data:
To distinguish between different types of information in a spreadsheet and to make searching for a spreadsheet easier, you can add borders around cells or ranges. To improve visibility and draw attention to specific data, you can also shade cells with a solid background color or a specific color pattern.
If you want to add a colorful background to all the data in the spreadsheet, you can also use an image as the background of the sheet. However, a sheet background cannot be printed, a background only improves the on-screen display of the spreadsheet.
Change alignment or orientation of data:
To optimize the presentation of data in the spreadsheet, you may want to change the position of the text within a cell. You can change the alignment of the cell content, use indentation to provide better spacing, or display data at different angles by rotating.
Data rotation is especially useful when column headings are wider than the column data. Instead of creating unnecessarily wide columns or shortened labels, you can rotate the text in the column heading.
For information on changing the alignment or orientation of the data, see change the position of the data in a cell .
Copy and Existing format to other data
If you have already formatted some cells in a spreadsheet the way you want, you can simply copy the format to other cells or ranges. With the Paste Special command ( Home tab, Clipboard group, paste button ), you can only paste the formats of the copied data, but you can also use the copy format ( Home tab, Clipboard group ) to copy and paste formats to other cells or ranges.
Also, data range formats automatically expand to additional rows by entering rows at the end of a data range that you have already formatted, and the formats appear in at least three of the previous five rows. The option to extend formats data ranges and formulas is enabled by default, but you can turn on or off as needed (click Options File > Options > outposts > extend the date range and formulas (in Edit Options ) or, if it comes to Excel 2007, click the Microsoft Office button > options in Excel