CHAPTER 10 Flashcards
Formatting
We can format workbooks, worksheet, or cells in Excel
Formatting a cell
We can format the data in a cell by applying a variety of format options in Excel. We can make the font bold, italicized, underlined, and in different colors. We could also wrap text inside a cell, align it to the top, bottom, or middle of the cell, and put borders of various styles around the cell.
Formatting the labels and data makes our worksheet more aesthetically pleasing, easier
to read and to spot errors.
Number Formats
A number format is simply the way a number is presented. The underlying value is not changed. Excel offers a number of built-in number formats. A commonly used format is to present a number with (or without) a certain number of decimal values and with (or without) a
1000 separator.
options for viewing custom formats
To view custom formats:
a) Keyboard: Ctrl - 1 -> Number -> Custom
b) Mouse: HOME ribbon -> Cells Group -> Format Cells -> Number -> Custom
A number format has four parts. Just as a function in a formula has a comma separating two arguments, the parts in a number format are separated by a semi-colon.
Part 1: format for a positive number [Blue]#,##0.00_)
Part 2: format for negative number Red
Part 3: format for zero 0.00
Part 4: format for text @”Product discontinued”
Put together, the four parts will read as follows:
[Blue]#,##0.00_);Red;0.00;@”Product discontinued”
When using a number format in Excel, the format will be entered without space.
To understand the effect of these different formats, select custom format in the ‘Format Cells’ and enter this format (all four parts) in the format ‘Type’.
We have the option to specify one or more formats. If we specify only one format, then that format is used for all entries in that cell: positive numbers, negative numbers, zeroes, and text.
a) Number of formats specified: 1, Format: First, Used for: positive numbers, negative numbers, zeros and text.
b) Number of formats specified: 2, Format: First, Used for: positive numbers and zeros, Format: Second, Used for: negative numbers
c) Numbers of formats specified: 3, Format: First, Used for: positive numbers, Format: Second, Used for: negative numbers, Format: Third, Used for: Zeros
Within a format, we can choose from a number of different characters to display specific formats.
a) # (number sign): displays only significant digits and does not display insignificant zeros.
b) 0 (zero): displays insignificant zeros if a number has fewer digits than there are zeros in the format.
c) . (period): A period separates decimal from the integer portion of the number. The number of zeros determines the number of decimal places displayed.
d) , (comma): A comma is used to separate thousands if it precedes a # sign.
e) , (comma): A comma is used to scale down a number by a factor of 1000 if it follows a zero.
f) ? (question mark): Adds space for insignificant zeros on either side of the decimal point.
Date Formats:
a) D: Displays the day of the month as a number from 1
to 31.
b) Dd: Displays the day of the month as as a number
from 01 to 31. (All the numbers are displayed in two digits. 1 will be displayed as 01).
c) Ddd: Displays the day of the week (using three
characters) e.g., Sun
d) Dddd: Displays the day of the week (in full) e.g.,
Sunday
e) M: Displays the month as a number from 1 to 12
f) Mm: Displays the month as a two digit number from
01 to 12. (All the numbers are displayed in two
digits. January will be shown as 01)
g) Mmm: Displays the month (using three characters)
e.g., Jan
h) Mmmm: Displays the month (in full) e.g., January
i) Mmmmm: Displays the month as J-D (only the first
character)
j) Yy: Displays the last two digits of the year
k) Yyyy: Displays all four digits of the year
Time Formats
a) h: Displays the hour as a number from 0 to 23
b) hh: Displays the hour as a two digit number from 00
to 23
c) [h] or [hh]: Displays the cumulative hours elapsed
d) mm: Displays the minutes as numbers from 0 to
59.
Note that this character only represents minutes if
used within a complete time number format code.
e) mm: Displays the minutes as a number 00-59 and
always displays it as a two-digit number. Note that
this character only represents minutes if used
within a complete time number format code.
f) [m] or [mm]: Displays the cumulative minutes
elapsed
g) s: Displays the seconds as a number from 0 to 59
h) ss: Displays the seconds as a two digit number from 00 to 59.
Displaying numbers along with text in a cell
It is possible to display both text and numbers in a cell (and still have the cell formatted as a number). To do this, we include the text in that section of the format code where we want the text to appear. For
example, if we want to show a positive number as profits and a negative number as losses we can use the format: $0” Profits”;-$0” Losses” to display a positive amount as “$100 Profits” and a negative amount as “-$100 Losses.”
The last part of a number format is used to show text. We can use the @ symbol to combine what we type in the cell with the text that is included in the number format. For instance, we can type “Loss of “@” in
2015” as the last part of a number format. Then, when we type $1000 in the cell, it is formatted as “Loss of $1000 in 2015”.
We can also make the inclusion of text conditional on the value entered in the cell.
* 0 [<=70] “is poor”; * 0 [>=90] “is excellent”; * 0”is
good”
If : We enter, say 55, we get “55 is poor”
ElseIf: we enter, say 95, we get “95 is excellent”
Else: For all other values, we get “## is good” (where ## = value entered)
We see that formats based on conditions in a cell can be built into the number format itself. On occasion, we may want to apply one or more formats to a large number of cells. The format that will be applied
may depend on the value entered in the cell. For such cases, modifying the number format may not be practical.
Conditional Formatting
Conditional formatting refers to formatting one or
more cells when a specified condition is true.
How to introduce a conditional format
a) Mouse: Home ribbon -> Styles group -> Conditional
formatting -> Highlight Cells Rules -> Greater Than
[desired value]
b) The Conditional Formatting dialog box gives us
several built-in options for both the condition and
the format. If the condition is true, the format will
apply. We can choose values that are greater,
equal to, or less than a given value; we can
choose the top 10, or top 10%, of values and so
forth. We also have the flexibility of creating our
own rule using a formula. We can use the New
Formatting Rule to create a condition of our
choice.
c) Once we have created a rule, we may want to edit,
delete, or manage that rule. To do this, we can use
the Conditional Formatting Rules Manager.
Keyboard: ALT-H, L, R Mouse: Home ribbon -> Styles Group -> Conditional Formatting -> Manage Rules
d) Conditional Formatting is helpful in highlighting
given values. It also facilitates accurate data entry,
for instance by highlighting values outside an
acceptable range. Once we have created a
conditional format, we can apply this format to
other cells by using the format painter. Another
way to apply the format is to use Copy Paste-
Special.
COPY PASTE-SPECIAL
To apply Copy Paste-Special:
a) Select the cell range with the format that you wish
to copy
b) Copy this range
c) Select the cell range to which you wish to apply
this format
d) Then apply the format as follows
How to apply a format (after it has been copied from another cell range)
Mouse: Right click on the selected cell range
Paste Special -> Paste Special -> Formats
Keyboard: ALT-e, s, t
Copy-Paste-Special is useful for more than copying formats and conditional formats. In addition to formats, it can be used to copy formulas, values, comments, data validation rules, number formats and
other options.
Operations with a constant
Copy Paste-Special can also be used to add, subtract, multiply, or divide a range of values by a constant.
Transposing a set of numbers
We can also use Copy Paste-Special to transpose a set of numbers. Transposing refers to copying values originally in rows and columns to columns and rows. To do this:
a) Copy the values we want to transpose
b) Invoke Copy Paste-Special and select transpose
Using Paste Special to Skip Blanks in Microsoft Excel
The ‘Paste Special Skip Blanks’ option enables users to paste the range skipping the blank cells.
How can we copy only the cell formatting (without the cell values)?
Use Copy Paste-Special
What formatting is helpful in highlighting a specific range of values?
Conditional Formatting
