ICAEW.com works better with JavaScript enabled.
Exclusive

Design and format

Less-known formatting options

Author: David Lyford-Tilly

Published: 03 Feb 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

In this article, we're taking a whistle-stop tour of a few small, less-known, but nonetheless useful formatting options for your spreadsheets.

This article was originally published in 2019 as Excel Tip of the week #311.

It has been edited to reference latest Excel developments.

Angled text

Did you know that you can have text appear at any angle you like? Particularly useful for labelling rows and columns when space is tight, there are a couple of ways of accessing this option.

You have some basic options on the Home Ribbon:

Screenshot of angled text options in Excel Home Ribbon

Or you can be more specific from the Format Cells pop-out menu (Ctrl 1):

Screenshot of angled text options in Format Cells menu in Excel

Non-standard text effects

Bold, underline, and italic are of course on the Home Ribbon and they've all got common keyboard shortcuts (Ctrl and b, u, and i respectively). But did you know you can also apply superscript, subscript, and strikethrough formats from the Font tab of the Format Cells menu? You can even apply these formats to just part of a sentence.

Screenshot of font tab in Format Cells menu in Excel

Patterned borders and fills

They're not my cup of tea, but there are options for both various patterned cell borders, and for patterned background fills.

NB: Both demonstrations are shown with the gridlines hidden from the View Ribbon for clarity.

Screenshot of border tab in Format Cells menu in Excel
Screenshot of patterned cell options in Format Cells menu in Excel

Justified text

There are a couple of options for justifying text (that is, laying it out such that the right edge of the text is smooth).

First, with Wrap Text enabled, you can choose Horizontal => Justify from the Alignment tab of the Format Cells menu:

Screenshot of Alignment tab in Format Cells menu in Excel

Alternatively, you can have Excel cut and paste text into a series of cells in order to try and justify it, using Fill => Justify from the Home menu. The number of columns you select at the beginning will be the width that Excel aims for; note that no cell in the selection can exceed 255 characters.

Screenshot of Justify options in Home menu in Excel

Indents

You can set an indent for text that will move it away from the cell edge:

Screenshot showing an example of indented text in Excel

This can be done with the highlighted buttons on the Home Ribbon, or from Format Cells => Alignment.

Copying format

Formatting can easily be copied across from one cell to another throughout a workbook. You can use the format painter to do this.

First, select the cell with the formatting you want to copy and click on the format painter. Then select the cell(s) where you want to apply the format. To apply the same format to multiple cells, you can double click on the format painter.

Gif showing how to use Format Painter in Excel

The inverse of this – copying cell data without formatting, which is sometimes useful too – has always been possible with the ability to paste values through the advanced paste options (in Excel on the web, the shortcut Ctrl+Shift+V is now available to do this quickly and easily).

There are always more options to explore - so don't be afraid to wander off into the Excel Ribbons and menus and find things for yourself! You can see all the examples in this post in this file.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250