In this post you'll discover an array of wonderful tools to format a spreadsheet and make it look fantastic.
If you're reading this, then I'm going to assume that you have already entered your data and written all the formulas you need.
If you haven't, then return to your spreadsheet and finish the data stage. Only then should you start formatting otherwise you'll be faffing around, chopping and changing and fiddling. And it will take 10 times as long. Trust me.
1. Quick number formatting
On the Home ribbon, front and centre, you’ll find the Number group with 5 useful tools for quick number formatting and a drop-down menu with options for other types of content like dates, accounting etc.
![The quick number formatting tools are the easiest way to format a spreadsheet (content) The quick number formatting tools are the easiest way to format a spreadsheet (content)](http://officemastery.com/wp-content/uploads/2020/03/E1-05-01-formatting.png)
2. The Format Cells dialog
A cell may be formatted in a number of ways. From simple number formatting (like decimal places) to content styling, alignment, positioning, text control, borders and shading.
The control centre for anything related to cell formatting is the Format Cells dialog.
1. Click on the cell or cell range to be formatted.
2. Select the Home tab.
3. Click the launcher (pictured right) In the Number group
… or press Ctrl 1
… or right-click and choose Format Cells from the context menu.
The Format Cells dialog is now displayed.
3. How to format a number in Excel
To control how a number is displayed (decimal places, 1,000 separator etc.):
1. Display the Format Cells dialog
2. Select the Number tab.
3. Select Number from the Category list.
4. Choose how many decimal places the number should have.
5. Decide whether to use a 1000 separator for numbers like 1,000 or 1,000,000.
6. Click OK
![Format a spreadsheet: Number formatting Format a spreadsheet: Number formatting](http://officemastery.com/wp-content/uploads/2020/03/E1-05-03-formatting-a-number.png)
4. How to format a date in Excel
To change the way a date looks:
1. Display the Format Cells dialog
2. Select the Number tab.
3. Select Date from the Category list.
4. Ensure your locale (country or region) is set correctly.
5. Choose a date format from the Type list.
6. Click OK
![Format a spreadsheet: Date formatting Format a spreadsheet: Date formatting](http://officemastery.com/wp-content/uploads/2020/03/E1-05-04-fomratting-date.png)
5. How to rotate cell content
To angle text at 30, 45 or 90 degrees (or any angle for that matter):
1. Display the Format Cells dialog
2. Select the Alignment tab.
3. Drag the red diamond in the Orientation box to any position on the semi-circle (or click directly on the circle or type the degree figure in the box)
4. Click OK
![Format a spreadsheet: Rotate the content inside a cell Format a spreadsheet: Rotate the content inside a cell](http://officemastery.com/wp-content/uploads/2020/03/E1-05-05-rotating-cell-content.png)
6. How to position cell content in Excel
METHOD 1: Format Cells dialog
To set the horizontal and vertical positioning of cell content:
1. Display the Format Cells dialog
2. Select the Alignment tab.
3. Click the drop-down arrow on the Horizontal box and choose Left, Center,or Right.
4. Click the drop-down arrow on the Vertical box and choose Top, Middle or Bottom.
5. Click OK
![Format a spreadsheet: Positioning cell content (method 1) Format a spreadsheet: Positioning cell content (method 1)](http://officemastery.com/wp-content/uploads/2020/03/E1-05-06-positioning-cell-content.png)
METHOD 1: the Home ribbon
1. Select the HOME tab, if necessary.
2. Click any of the alignment icons in the Alignment group to set the horizontal and vertical alignment.
![Format a spreadsheet: Positioning cell content (method 2) Format a spreadsheet: Positioning cell content (method 2)](http://officemastery.com/wp-content/uploads/2020/03/E1-05-07-positioning-cell-content-method-2.png)
7. How to make cell content fit better
Sometimes text just doesn’t fit. There are two fantastic tools to handle this problem automatically.
1. Display the Format Cells dialog
2. Select the Alignment tab.
3. Select Wrap Text to wrap text automatically to a new line if it is too wide (like the end of a line in a Word doc).
4. Or select Shrink to Fit to reduce the size of text to make it fit on one line using the current column width.
5. Click OK
![Format a spreadsheet: How to make cell content fit better using Wrap Text and Shrink to Fit Format a spreadsheet: How to make cell content fit better using Wrap Text and Shrink to Fit](http://officemastery.com/wp-content/uploads/2020/03/E1-05-08-cell-content-fit.png)
In the Alignment group on the Font ribbon there is a button you can use. However, there is no Shrink to Fit button.
8. How to merge cells
For simple spreadsheets, it’s okay to merge cells. When spreadsheets become more complex, merging cells can create lots of problems. This is discussed (along with a remedy and alternative approach) in a higher-level course.
To merge two or more cells into one big cell:
1. Select all the cells to be merged.
2. Display the Format Cells dialog
3. Select the Alignment tab.
4. Select Merge Cells.
5. Click OK
![Format a spreadsheet: How to merge cells Format a spreadsheet: How to merge cells](http://officemastery.com/wp-content/uploads/2020/03/E1-05-09-merging-cell.png)
Microsoft realised that many people were merging cells then centring the content, so, they gave us a Merge & Center button on the Home ribbon to do just that.
9. Special formatting like strikethrough & superscript
To set the font attributes that are not on the Home ribbon:
1. Display the Format Cells dialog
2. Select the Font tab.
3. Select a font, style, size and colour.
4. Choose an underline style by selecting from the drop-down list.
5. The Strikethrough will draw a line through the cell contents, like this.
6. Superscripted text is used for things like 32 and 13th.
7. Subscripted text is used for things like H2O.
8. Click OK
![Format a spreadsheet: How to apply special font formatting like strikethrough, superscript and subscript Format a spreadsheet: How to apply special font formatting like strikethrough, superscript and subscript](http://officemastery.com/wp-content/uploads/2020/03/E1-05-10-special-font-formatting.png)
10. Drawing cell borders
To draw printable borders:
1. Select the cells you wish to apply borders to.
2. Display the Format Cells dialog and select the Borders tab.
3. Select a line style.
4. Select a line colour.
5. Use one of the following techniques:
6. Click OK
![Format a spreadsheet: Drawing cell borders Format a spreadsheet: Drawing cell borders](http://officemastery.com/wp-content/uploads/2020/03/E1-05-11-drawing-borders.png)
11. Adding colour to cells in Excel
To add some colour and interest to your selected cells:
1. Display the Format Cells dialog
2. Select the Fill tab.
3. Select a colour from the colour palette.
4. To break up a colour, pick a pattern and pattern colour from the Pattern drop-down palette.
5. Click OK
![Format a spreadsheet: Adding colour to cells Format a spreadsheet: Adding colour to cells](http://officemastery.com/wp-content/uploads/2020/03/E1-05-12-adding-colour-to-cells.png)
12. two ways to use Format Painter
There is a beautiful little tool on the Home ribbon called Format Painter which allows you to copy the formatting from one cell or cell range to another.
![Format a spreadsheet: The Format Painter tool. Many people don’t realise there are 2 ways to use this tool Format a spreadsheet: The Format Painter tool. Many people don’t realise there are 2 ways to use this tool](http://officemastery.com/wp-content/uploads/2020/03/E1-05-13-format-painter.png)
1. Select the cell or range of cells with the source formatting.
2. Click the Home tab, if necessary.
3. To copy the formatting once:
The formatting is copied, and the Painter is switched off.
4. To copy the formatting as many times as you want:
The formatting is copied, but the Painter remains on, so you can continue to apply the same formatting to additional cells or cell ranges.
5. To switch off the Format Painter, click the icon again or hit Esc (Escape).
13. How to reset all cell formatting
To clear the formatting from a cell or cell range:
![Use the Clear Formats option to reset any formatting without deleting the cell contents Use the Clear Formats option to reset any formatting without deleting the cell contents](http://officemastery.com/wp-content/uploads/2020/03/E1-05-15-resetting-format.png)
14. Using the Format-as-Table tool
Excel provides many pre-set designs that you can use for your tables of data.
1. Click any cell in your data range.
2. Select the Home ribbon, if necessary.
3. Click the Format as Table button in the Styles group. A gallery is presented.
4. Choose a format design from the gallery (see next page).
5. Confirm whether your table already has headers, or you want Excel to create some for you.
![Using the format-as-table tool gives you perfectly formatted tables in just two clicks. Don't waster your time formatting your own tables. It's not worth it. Using the format-as-table tool gives you perfectly formatted tables in just two clicks. Don't waster your time formatting your own tables. It's not worth it.](http://officemastery.com/wp-content/uploads/2020/03/E1-05-14-using-format-as-table-tool.png)
15. Format a spreadsheet: Key takeaways
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
![Jason Morrell blog signature Jason Morrell blog signature](https://d1pzfp4ixszkr4.cloudfront.net/img/2015/11/01100151/jason-graffiti-sig-01-200w.jpg)
![500x500 Jason Morrell - Broadwater-19-Water About Jason Morrell](https://d1pzfp4ixszkr4.cloudfront.net/img/2021/01/01094415/500x500-Jason-Morrell-Broadwater-19-Water.png)
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE