14 Quick and Easy Ways to Format a Spreadsheet: Beginners Guide

AUTHOR:  JASON MORRELL 

Jason Morrell

Reading Time:
  minutes remaining

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)

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

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

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

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)

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)

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

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

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

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:

  • Click on one of the border pre-sets.
  • Click on each individual border icon.
  • Click in the preview itself.

6.  Click OK

Format a spreadsheet: Drawing cell borders

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

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

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:

  • Click the Format Painter icon ONCE.
  • Click the destination cell or cell range.

         The formatting is copied, and the Painter is switched off.

4.  To copy the formatting as many times as you want:

  • Double-click the Format Painter icon.
  • Click the destination cell or cell range. 

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:

  • Click the Clear icon on the Home ribbon (right-hand side).
  • Choose Clear Formats.
Use the Clear Formats option to reset any formatting without deleting the cell contents

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.

15. Format a spreadsheet: Key takeaways

  • Press Ctrl 1, right-click and choose Format cells or click the launcher in the Font group (Home ribbon) to display the Format Cells dialog.
  • The Number tab of the Format Cells dialog allows you to format the content of the cell. This could be text, numbers, currency, dates, times, percentages etc.
  • The Alignment tab of the Format Cell dialog box controls positioning.
  • The Font tab of the Format Cells dialog allows you to format text.
  • The Borders and Fill tabs of the Format Cells dialog let you set the cell colours and border options.
  • The Format Painter tool can be used to copy the formatting from one cell (or range of cells) to another.
  • The Format As Table option on the Home ribbon is the easiest way to format a table of data.
  • The Clear button on the Home ribbon lets you clear content and/or formatting from a selection.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
The Best Way to Create Drop-Down Lists in Excel That Are Easy to Maintain, Re-use and Put YOU in Full Control
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic
Read More
Excel Formulas for Beginners: Bring Your Spreadsheet to Life
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>