Formatting Spreadsheets
4 Formatting
EXTRA – Renaming Worksheet Tabs
To rename a worksheet tab · Right click on the worksheet tab that you wish to rename. From the popup menu displayed select the Rename command. · Your can then type over the default worksheet name, which will become highlighted. · In this case the worksheet name Dave has been used.
Format Cells - Numbers
Format cells to display different number styles: number of decimal places, number of zeros after the decimal point, with or without commas to indicate thousands.
To change number formatting You can quickly change
the formatting of a cell or selected range by using the following icons
on the Formatting toolbar:
Selecting this icon: Will do this:
Currency 2345 will become £23,45.00 (or your local currency equivalent)NOTE:
In the United States this icon may contain a $ symbol.
Percent .25 will become 25%
Comma 12345 will become 12,345.00
Increase Decimal 23,456.00 will become 23,456.000
Decrease Decimal 23,456.00 will become 23,456.0
To establish a fixed number of decimal places for cell formats · Select the cell, or range, you wish to format with a fixed number of decimal places.· To add a decimal point to the selection, click on the Increase Decimal icon on the Formatting toolbar. You can continue to click to add as many decimals as required. · To remove a decimal point from the selection, click on the Decrease Decimal icon on the Formatting toolbar. Again, you can continue to click to remove as many decimals as required.
To format a cell using commas · Select the cell(s) which contains the value you wish to format.· Click on the Comma Style icon.
Format cells to display different date styles.
To format date styles · Enter a date in a cell.· Right click to display a pop-up menu, and select Format Cells, to display the Format Cells dialog box. Select Date from the Category list and use the Type section of the dialog box to select the required date format.
Format cells to display different currency symbols.
To format cells using currency symbols · Enter a number in a cell.· Right click to display a pop-up menu, and select Format Cells, to display the Format Cells dialog box. Select Currency from the Category list and use the Symbol section of the dialog box to select the required currency format.· Select OK.
Format cells to display numbers as percentages.
To format numbers as percentages · Click on a cell to select it and then click on the Percent Style icon on the Excel formatting toolbar.· Enter the percentage value into the cell.NOTE: Formatting a cell using the percentage style, multiplies the value by 100 and displays the results using a percentage symbol. Thus if you wished to use a value that would display as 20%, you would enter 0.2 into a cell formatted this way.
Format Cells - Text
Change text size. Format text: bold, italic, font type.
To change text size · Select the cell or range that you wish to change the text size of.· Click on the down arrow to the right of the Font Size icon located on the Excel formatting toolbar.· Select the required font size.
To format text as bold · Select the cell or range that you wish to apply text formatting to.· Use the Bold icon located on the Excel formatting toolbar.
To format text as italic · Select the cell or range that you wish to apply text formatting to.· Use the Italic icon located on the Excel formatting toolbar.
To modify the font type used by text · Select the cell or range that you wish to apply text formatting to.· Click on the down arrow in the Font section of the Excel formatting toolbar. Select the required font.
Change text font color.
To modify the color used by the text · Select the cell or range that you wish to apply text formatting to.· Use the Font Color icon located on the Excel formatting toolbar. Click in the down arrow to display a range of color options.
Adjust text orientation.
To modify text orientation · Select the
cell or range that you wish to apply text formatting to.· Right
click, and from the pop-up menu displayed select the Format Cells command,
which will display the Format Cells dialog box.· Select the Alignment
tab and modify the options as required.
Horizontal Alignment options include:
Vertical Alignment options include:
The orientation can also be changed to display text at an angle, which
can be very useful for header formatting.
Format Cells - Cell Ranges
4.4.3.1 Centre and align cell contents in a selected cell range: left
and right; top and bottom.
To align data between the left and right sides of a cell · Select the cell(s) you wish to align. · On the Formatting toolbar, click the Align Left icon to align data with the left edge of the cell.· Click the Align Right icon to align data with the right edge of the cell. · Click the Center icon to center the data in the cell.
To align data between the top and bottom of a cell · Select the cell(s) you wish to align. From the Format menu, select Cells to display the Format Cells dialog box.· To view the Alignment options, click on the Alignment tab at the top of the dialog box.· Choose the Top, Center, or Bottom option in the Vertical area to align the data in the cell. · To make the lines of data fit evenly within the height of a cell, choose the Justify option.· Click on OK.
To change the “read” orientation of data in cells · Select the cell(s) you wish to change and from the Format menu, choose Cells or press Ctrl+1, to display the Format Cells dialog box.· To view the Alignment options, click on the Alignment tab at the top of the Format Cells dialog box.· Click the option you require in the Orientation area.· Click on OK.
To wrap multiple lines of data in a cell · Type the data you require into the cell and press Enter. The entry will appear as one long line and select the cell(s) you wish to format.· From the Format menu, choose Cells or press Ctrl+1 to display the Format Cells dialog box.· Click on the Alignment tab to display the Alignment options.· Select the Wrap text check box and click on OK.
To indent data within a cell · First select the cell(s) that you
wish to apply the indenting formatting to.· Right click to display
the pop-up box, and from the list displayed, select Format Cells. This
will display the Format Cells dialog box. Select the Alignment tab.·
Select Left (Indent) from the Text alignment / Horizontal list, and then
use the spinner controls to add the required indent.· Select OK.
To rotate text to any angle · First select the cell(s) that you wish to apply the rotation formatting to.· Right click to display the pop-up box, and from the list displayed, select Format Cells. This will display the Format Cells dialog box. Select the Alignment tab.· From the Orientation section either enter the exact amount of rotation required into the Degrees box, or drag the Text dial to give the desired level of rotation.· Select OK.
To center a heading over multiple columns · Type a heading at the top of the left-hand column.· Place the cell pointer on the heading cell and highlight it along with all the cells across the columns.· On the Formatting toolbar, click the Merge and Center icon.
To center data within a cell · Highlight the cell(s) which contain the data you wish to center.· On the Formatting toolbar, click the Center icon to center the data within the cell(s).
Add border effects to a selected cell range.
To apply a border to cells or ranges · Select the cell(s) to which you want to add a border.· To view border options, click on the down arrow next to the Borders icon on the Formatting toolbar.· Select the option you require. Once you have added a border using the Borders icon, the border you selected will appear on the Borders icon. You can add the same border simply by clicking on the icon after you have select the cell(s) to which you wish to apply the border.
To use the Format Cells (Border) dialog box · You could select
a cell, or range of cells, and then click on the Format drop down menu,
from which you can select the Cells command. This will display the Format
Cells dialog box.· Select the Border tab, as illustrated. ·
You can use this dialog box to apply borders and also to vary the type
of border applied. · First choose a preset format to apply a border
style.· Then click on the Border icons within the dialog box.·
Finally use the Style and Color options to customize your borders. An
example is illustrated below. · Click on the OK button to apply
the border formatting and close the dialog box.
To remove a border from cells or ranges · Select the cell(s) that contain the border you wish to remove. If you have a border that appears to be on the left side of a cell, but may actually be on the right side of the adjacent cell, select both of the cells.· To reveal the border options, click the arrow next to the Borders icon on the Formatting toolbar.· Click on the first border option. This contains no border selection and all highlighted borders will be removed.
To change the style and color of borders · Select the cell(s) that
contain a border and then from the Format menu, choose Cells, or press
Ctrl+1 to display the Format Cells dialog box.· To display the
border options, click the Border tab on the top of the Format Cells dialog
box.· Select the border style you require by clicking on it.·
From the Color drop-down palette, select the color you require.·
Click on the Border options to determine where you want the colored borders
to appear on the selected cells. Click on OK.
To AutoFormat a table · Place the insertion cell within a table
of information.· From the Format drop down menu, select the AutoFormat
command.· From the list displayed in the Table format section of
the dialog, select the required format, and click on the OK button to
apply the formatting information.NOTE: Clicking on the Options button
within the dialog box allows you to specify the following customization
options.
· Checking any of the above boxes applies the appropriate elements
when AutoFormat is applied.· If you do not wish to apply a format
to a particular element uncheck the appropriate box.