Formatting Text in Excel Tutorial
Free Online Microsoft Excel Tutorial
Excel 2010 – Entering Data
* Experimenting with Font Options
* Fitting Text to a Cell
* Setting Text Alignment
Making your spreadsheet easy to read and understand is essential. You can do this by applying formatting to text, including changing the font type and size, font color, text alignment, merging cells, wrapping text and using the fit to cell feature. We also examine, once you have created a format that works for you, how to replicate your custom formatting over unformatted text.
* Experimenting with Font OptionsFont options in Excel allow you to change the font type, size, make text bold, italic and/or underlined. You can even change the color of text. So if you thought Excel was only for number crunchers and not for creative people, hopefully this tutorial will prove you just a little bit wrong.
Using font options, you can make your spreadsheet look attractive, make it easier to read, emphasize certain pieces of text or headings and differentiate different types of data.
1. Open a spreadsheet that contains some data or add data to a new spreadsheet. For the purposes of this exercise, we will be using a sample spreadsheet that contains no formatting to demonstrate the difference formatting text will make to it. Below is an image of the unformatted sheet (scroll to the end of this tutorial to see the finished product):
2. To change the Font type, select the cell or cells you wish to modify, click the Home tab and then in the Font group, click the dropdown arrow next to the font name (circled in blue in the example below). This will open a list of all the fonts you have available.
3. If you hover your mouse cursor over the different font names in the list, you will have a live preview of what that font will look like if you apply it to your text.
4. When you are happy with the look of a specific font, click on the select that font to apply it.
You can also change the size of your text by making heading text larger or specific sections of text smaller. To do this:
1. Select the cell or cells containing the text you wish to make bigger (or smaller).
2. In the Font group, either click the ‘Grow Font’ or ‘Shrink Font’ button (the large A and small A in the Font group) until your text is the required size, or select a specific pre-set size from the ‘Font Size’ dropdown list.
These options are circled in blue in the image below:
3. By clicking on the number 16 in the list, I have changed the headings in my spreadsheet to a font size of 16.
Size isn’t everything, and sometimes your text needs that little bit extra to really give it emphasis. To do this we use bold, italics and underline. We can also change the color of text, for example, you may want to make a total on a spreadsheet that is negative, a red font, and a positive total, a green font, to make the spreadsheet easier to understand.
1. Select the text you wish to apply bold, italic, underline or a different font color to.
2. Select the relevant option(s) in the Font group by clicking the relevant button(s). Bold is indicated by a capital letter B, Italic by a capital cursive letter I and Underline, by the letter U with a line underneath. To change the font color, you need to click on the selection arrow next to the underlined capital A and select the color you wish to use from the selection.
All these options have been circled in blue in the image below:
I have modified the sample spreadsheet to apply bold to the headings and italics to the contents of the Salary column. I have also changed the font color of the Profile column. Study the image below and see if you can identify these changes:
Go ahead and experiment with different combinations of font types and sizes and apply bold, italic, underline, color change, to selected text in Excel.
* Fitting Text to a CellYou may have noticed looking at our sample spreadsheet, that there are some cells that contain data that appears ‘cut off’ and another section where the text appears to run over several columns. One of the most important skills to have for using Excel successfully may just be the knowledge and ability to fit text in a cell and thus creating a functional readable spreadsheet. There are a number of functions that allow you to do this. In this section of the tutorial, we will examine resizing cells, using the automatic fit text to cell function, wrapping text within a cell and using the merge function to fit text across a number of cells.
The most basic way to make text fit in a cell is to resize the cell(s):
1. Select the column or row you wish to resize.
2. Hover your mouse over the dividing line at the side of the column (bottom or top line of a row), from where you wish to resize. You will note that your mouse cursor icon changes to a two-pointed arrow line. Study the example below as to where to hover your cursor if you wish to resize a column (circled in blue):
3. Once your mouse icon has changed into a two-pointed arrow, hold the left mouse button down and drag the column (or row) line up to where you wish to resize. You can also double-click (using the left mouse button) to automatically resize the column to fit its contents.
I resized all the columns on our sample spreadsheet to fit the contents. Note how much neater it is looking now:
Automatic Fit to Cell Feature
Another option for fitting text to a cell is using the automatic fit to cell feature. This will automatically shrink or grow the size of the font of the selected rows/columns/cells to fit. This function does not always deliver useful results, but is worth knowing about should you quickly need to reduce the size of cells to fit a spreadsheet.
1. To use the automatic fit to cell feature, select the cell or cells you wish to adjust.
2. On the Home tab, go to the Alignment group and launch the Format Cells dialogue box by clicking the dialogue box launcher in the right-hand corner of this group (circled in blue in the image below):
3. Select the Alignment tab in the Format Cells dialogue box and then select the ‘Shrink to fit’ option under the Text control heading (circled in blue in the image below) and press OK.
Wrap Text to Fit Text to a Cell
Another option is Wrap Text which allows you to fit text into the width of a cell, by automatically wrapping it and increasing the height of a cell. Examine what the Profile column on our sample spreadsheet looks like after applying the Wrap text option to it and resizing the column:
It is a really useful tool to ensure that your data is more readable, not only on the screen, but also in printed form.
1. To use the Wrap Text function, select the cell or cells you wish to wrap.
2. Click the Wrap Text icon in the Alignment group on the Home tab (circled in blue below):
3. Finally, resize your column to the desired width.
TIP: If you wish to stop wrapping text in a cell and have it spread out in the length of a column, select the cell or cells and toggle the Wrap Text button off.
SHORT CUT: ALT+ENTER – If you wish to add line or paragraph breaks within a cell to separate lines of text, hold down the ALT button on your keyboard whilst pressing the ENTER key.
The Merge Function – Fitting Text to CellThe final Fitting Text to Cell option we examine is the Merge function. Say for example we had a title for our sample spreadsheet that we require to be above the column titles and centered across all five columns. If we add a line above the heading row and we type our title in, you will note it is restricted by the size of the first column and is cut off because we have formatted and centered it:
To fix this we use the Merge & Center function:
1. Select all the cells across which you wish to merge the content. In our example, we would select cells A1 through to E1.
2. On the Home tab, in the Alignment group, click the Merge & Center button.
3. Your content will automatically be fitted and centered across the selected number of cells:
TIP: If you click the dropdown arrow next to the Merge & Center button, you have further merging options to experiment with!
* Setting Text Alignment
Text alignment settings dictate how data is displayed in a cell, both vertically and horizontally. It allows you to add the final finishing touches to take your spreadsheet from being OK to being perfectly lined up. You can set alignment to position text both vertically and horizontally (and rotated somewhere in-between!).
1. Firstly, select the cell or cells you wish to apply alignment to. You will find the alignment options in the Alignment group on the Home tab. If you study the icons, you will note that they visually represent how the text will be displayed in each cell (icons circled in blue in the image below):
2. To set how text is positioned horizontally in a cell, you have choice of left, centered or right.
3. To set how text is positioned vertically in a cell, you have a choice between alignment to the: top, middle or bottom of a cell.
4. If we center all the contents of our sample spreadsheet horizontally and align all content in the middle, vertically, of each cell, this is the result (rows and columns have been resized to fit):
Compare the above to the sample spreadsheet we started out with at the beginning of this tutorial!
TIP: You can also change the direction of text in a cell. Select the cell or cells and click on the Orientation button in the Alignment group (the slanted lowercase ab with a line underneath) and select the desired rotation or angle for your text.
* Copy Formatting
When you get the formatting of a cell or cells in a spreadsheet ‘just right’ it can be tedious to reproduce. For example if you wanted other cells to look the same, you would have to change all the font and alignment settings again every time you paste new content into your spreadsheet.
There are two quick ways of copying formatting:The first is to use the Fill handle to copy formatting:
1. If the cell or cells that contain the desired formatting are adjacent to the unformatted cells, you can use the fill handle to duplicate formatting.
2. Select the cell with the desired formatting. Drag the Fill handle (located at the bottom right-hand corner of the selected cell) down through the unformatted cell.
3. Click on the Autofill Options icon that appears at the bottom right-hand corner of the last cell you dragged the Fill through and select ‘Fill Formatting Only’.
The second way of copying formatting is using the Format Painter:
1. Select the cell that contains the desired formatting.
2. On the Home tab, in the Clipboard group click on the Format Painter icon (circled in blue in the image below). You will note a little paintbrush appearing next to the usual Excel cursor.
3. Click with the paintbrush on the cell you wish to replicate the format on.
TIP: Should you wish to ‘format paint’ multiple cells without repeatedly selecting the cell and clicking the Format Painter for every instance, simply double click the Format Painter after selecting the formatted cell. You can now go ahead and just paint as many cells with the Format Painter as you need to. When you are done, press the Escape key on your keyboard.