What is formatting?
Excel 2013’s Format as Table feature enables you to both define an entire range of data as a table and format all its data all in one operation. After you define a cell range as a table, you can completely modify its formatting simply by clicking a new style thumbnail in the Table Styles gallery.
Excel also automatically extends this table definition — and consequently its table formatting — to all the new rows you insert within the table and add at the bottom as well as any new columns you insert within the table or add to either the table’s left or right end.
The Format as Table feature is so automatic that, to use it, you only need to position the cell pointer somewhere within the table of data prior to clicking the Format as Table command button on the Ribbon’s Home tab.
Clicking the Format as Table command button opens its rather extensive Table Styles gallery with the formatting thumbnails divided into three sections — Light, Medium, and Dark — each of which describes the intensity of the colors used by its various formats.
As soon as you click one of the table formatting thumbnails in this Table Styles gallery, Excel makes its best guess as to the cell range of the data table to apply it to (indicated by the marquee around its perimeter), and the Format As Table dialog box similar to the one shown appears.
This dialog box contains a Where Is the Data for Your Table? text box that shows the address of the cell range currently selected by the marquee and a My Table Has Headers check box (selected by default).
If Excel does not correctly guess the range of the data table you want to format, drag through the cell range to adjust the marquee and the range address in the Where Is the Data for Your Table? text box.
If your data table doesn’t use column headers, click the My Table Has Headers check box to deselect it before you click the OK button — Excel will then add its own column headings (Column1, Column2, Column3, and so forth) as the top row of the new table.
Keep in mind that the table formats in the Table Styles gallery are not available if you select multiple nonadjacent cells before you click the Format as Table command button on the Home tab. You can convert only one range of cell data into a table at a time.
After you click the OK button in the Format As Table dialog box, Excel applies the format of the thumbnail you clicked in the gallery to the data table, and the command buttons on the Design tab of the Table Tools contextual tab appear on the Ribbon.
As you can see, when Excel defines a range as a table, it automatically adds AutoFilter drop-down buttons to each of the column headings (the little buttons with triangle pointing downward in the lower-right corner of the cells with the column labels).
To hide these AutoFilter buttons, click the Filter button on the Data tab or press Alt+AT. (You can always redisplay them by clicking the Filter button on the Data tab or by pressing Alt+AT a second time.)
The Design contextual tab enables you to use the Live Preview feature to see how your table data would appear in other table styles.
Simply select the Quick Styles button and then highlight any of the format thumbnails in the Table Style group with the mouse or Touch pointer to see the data in your table appear in that table format, using the vertical scroll bar to scroll the styles in the Dark section into view in the gallery.
In addition to enabling you to select a new format from the Table gallery in the Table Styles group, the Design tab contains a Table Style Options group you can use to further customize the look of the selected format. The Table Style Options group contains the following check boxes:
Header Row: Add Filter buttons to each of the column headings in the first row of the table.
Total Row: Add a Total row to the bottom of the table that displays the sum of the last column of the table (assuming that it contains values).
To apply a Statistical function other than Sum to the values in a particular column of the new Total row, click the cell in that column’s Total row. Doing this displays a drop-down list — None, Average, Count, Count Numbers, Max, Min, Sum, StdDev (Standard Deviation), or Var (Variation) — on which you click the new function to use.
Banded Rows: Apply shading to every other row in the table.
First Column: Display the row headings in the first row of the table in bold.
Last Column: Display the row headings in the last row of the table in bold.
Banded Columns: Apply shading to every other column in the table.
Keep in mind that whenever you assign a format in the Table Styles gallery to one of the data tables in your workbook, Excel automatically assigns that table a generic range name (Table1, Table2, and so on). You can use the Table Name text box in the Properties group on the Design tab to rename the data table by giving it a more descriptive range name.
When you finish selecting and/or customizing the formatting of your data table, click a cell outside of the table to remove the Design contextual tab from the Ribbon. If you later decide that you want to further experiment with the table’s formatting, click any of the table’s cells to redisplay the Design contextual tab at the end of the Ribbon.
How to Access the Custom Formatting Menu
The Excel custom formatting menu is found in the Number tab of the Format Cells control box, which can be accessed by either :
- right-clicking on the selected cell or range and selecting the Format Cells … option from the drop-down menu
- Pressing CTRL-1 (ie. Selecting the CONTROL key and while this is depressed, pressing the “1” (one) key)
- Using the menu at the top of the spreadsheet (i.e. in Excel 2003, select Format→Cells, or in Excel 2007 or 2010, select the Home tab, and from this, select Format→Format Cells…)
From within the Number tab of the Format Cells control box, select the Custom option from the Category list. A list of pre-defined formatting styles will then appear to the right of the Format Cells control box (see right). You can either select and use these pre-defined formats as they are, or you can edit them to define your own formatting style.
The following sections discuss each of the Excel formatting options that can be used for displaying numbers:
Custom Integer, Decimal & Currency Formatting
When defining an excel custom number format for an integer, a decimal or a currency, the following characters are used:
|0||–||Forces the display of a digit in its place|
|#||–||Display digit if it adds to the accuracy of the number (but don’t display if a leading zero or a zero at the end of a decimal)|
|.||–||Defines the position that the decimal place takes|
|[colour]||–||The name of a colour can be inserted between square brackets to define the font colour|
Other characters, such as the “,”, “$”, “£”, “+”, “-“, “(” and “)” can be displayed at the start, middle and/or end of numbers, to make the number more readable, denote currency, or denote positive or negative values, etc. You can even replace the number completely with your chosen characters (eg. replacing a zero value with the text “NIL”)
You can define one, two or three basic Excel formats for any one cell. If the cell contains a number, these formats are applied depending on whether the number is positive, negative or zero, as follows:
- If a single text format is supplied, this format is applied to all numbers;
- If two text formats are supplied, and separated by a semi-colon, the first format is applied to positive numbers (and zeros) and the second format is applied to negative numbers
- If three text formats are supplied, separated by semi-colons, the first format is applied to positive numbers, the second format is applied to negative numbers and the third format is applied to zero values.
Decimal & Currency Formatting Examples
The following examples show the effect of different Excel formatting definitions on the numbers 5198.34, -98.66667 and 0
|Formatting Definition:||Resulting Formatted Number|
|+#,##0.0#; (#,##0.0#); “NIL”||+5,198.34||(98.67)||NIL|
|[Blue]+0.0; [Red](0.0#); [Green];”NIL”||+5198.3||(98.67)||NIL|
Excel Custom Percentage Formatting
The formatting of percentages in Excel uses the “0”, “#” and “.” characters in the same way as the formatting of integers and decimals. However, when using the percentage format, Excel displays the number multiplied by 100 and followed by the % sign.
Percentage Formatting Examples
Some examples of the numbers 0.55555, -0.5 and 0, formatted as percentages are shown below:
|Formatting Definition:||Resulting Formatted Number|
|[Blue]+0.0%; [Red]-0.0%; [Green]0.0%||+55.6%||-50.0%||0.0%|
Excel Custom Date & Time Formatting
When formatting a cell as a date, time or date and time, use the following characters:
|AM/PM||–||Indicates that a time should be represented using a 12-hour clock, followed by “AM” or “PM”|
Date & Time Formatting Examples
The example below shows different Excel formatting definitions for the date and time ’06:00AM on 25th August 2008′:
|Formatting Definition:||Resulting Format of Date / Time|
|ddd dd mmmm yy||Mon 25 August 08|
|dd/mm/yyyy hh:mm:ss||25/08/2008 06:00:00|
|dddd dd mmm yyyy hh:mm:ss AM/PM||Monday 25 Aug 2008 06:00:00 AM|
We will discussed details in the very next articles ….