Transcript Class 04 Excel CA202.ppt

CA202
Spreadsheet Application
Changing Document
Appearance
Lecture # 4
1
Objectives
•
•
•
•
Change the appearance of data
Apply an existing format to data
Make numbers easier to read
Change data’s appearance based on its
value
• Make printout easier to follow
• Position data in a document
2
Changing Document Appearance
• Make your data easier to understand, by
changing font, letter size, or color of cell
• How your data appears on the printed
page? By changing margin, add header &
footer
3
Changing Data Appearance
• Formatting data labels helps in reading
data
• To make your data labels or data visible ,
you can change the format of the cells in
which the labels or data is stored
• Use Bold, Italic, Underline or other
options on formatting toolbar to make
them high up
• Add Borders to make cell stand apart
4
Formatting
• You can format your spreadsheet by the
Formatting toolbars
– Choose typeface, point size, bold, italic, underline,
left align, right align, center align, apply number
format, percent, comment, increase/decrease indent,
add border, add a background, fill or font color or
customize format
5
Changing Data Appearance
• The standard font of
Excel is Arial, you can
choose another font,
Click Tools 
Options  General
and use standard
Font and Size for
your future
workbooks
6
Formatting Shortcut Keys
To Do This …
Press This …
Display Format →Style dialog Box
Alt + ‘
Display Format →Cell dialog Box
Ctrl + 1
Apply General Number Format
Ctrl + Shift + ~
Currency format with 2 decimals
Ctrl + Shift + $
%age format with no decimal
Ctrl + Shift + %
Exponential number format w/2 decimals
Ctrl + Shift + ^
Date Format dd/mm/yy
Ctrl + Shift + #
Time Format hh:mm with AM/PM
Ctrl + Shift + @
Number Format with 2 decimals
Ctrl + Shift + !
7
Formatting Shortcut Keys
To Do This …
Press This …
Apply an Outline border
Ctrl+ Shift + &
Remove all borders
Ctrl+ Shift + _
Toggle Bold Formatting
Ctrl+ B
Toggle Italic Formatting
Ctrl+ I
Toggle Underlining
Ctrl+ U
Strikethrough
Ctrl+ 5
Hide Row
Ctrl+ 9
Unhide Row
Ctrl + Shift + (
Hide Columns
Ctrl+ 0
Hide Columns
Ctrl + Shift + )
8
Start Activity on Page 60
9
Apply Existing Format to Data
• You can develop your
own formats and store
them, and recall as
needed
• You can find predefined
formats available to you
in the Style dialog box.
• If none of the existing
styles are what you want,
you can create your own
by typing the name of
your new style in the
Style name box and then
clicking Modify
10
Apply Existing Format to Data
• The quickest way to apply formatting
changes you made to a cell to the content
of another cell, is to use Format Painter
• Single click on Format Painter will help to
past the format to one cell only. For
multiple pasting use double click on
Format Painter
11
Format Painter Button
• Choose the cell to copy format
• Click of Format Painter, with a Click or
Drag, it will paint the format over the range
• Format painter can also be used for
multiple paste
– Double click the Format Painter button to
paste as many as different cells and ranges
as you like
– Press Esc to get out of paint mode
12
AutoFormat
• To apply an AutoFormat, you select the cells
you want to format and click AutoFormat on the
Format menu.
• Format → AutoFormat
• Choose one of the 17 different design to
decorate your data
• The Option button let you choose only a portion
of formatting e.g. you like font not the
background color
13
Start Activity on Page 63
14
Making Number Easier to Read
• Changing the format of
the cells in your
worksheet can make your
data much easier to read
• Adding borders to define
the boundaries between
labels and data
• Using formatting options
for different data types
such as dates, time,
phone no, or currency
15
Custom Number Format
• Choose Format 
Cells  Number Tab
• The number format
you apply doesn’t
effect the actual value
that Excel uses for
calculation
16
Rounding
• Choose Format → Cell → Number Tab
• Choose decimal places, commas and so
forth
• No built-in formats will round numbers to
the nearest specified whole number
• Round function does this trick, but you
can do it by choosing Custom Format
17
Rounding
•
•
•
•
Choose Format → Cells → Number Tab
Choose Custom format
Type the following code
#, (# with comma) round to nearest thousand
54,223 become 54
• #,”000” (# with comma & zero in quot) round
to nearest thousand, 54,223 become 54,000
• 0.0, round the nearest thousands 54,223
becomes 54.2
18
Aligning Data
• Format→ Cells →
Alignment Tab
• Choose Left, Center,
Right
• You can turn on the
following options
– Shrink to Fit (instead of
changing the font size)
– #### (If you see these
number sign, means
number is bigger than
column)
19
Aligning Data
• You can rotate the text
• You can merge the cells,
both vertically and
horizontally
– In MERGE only upper cell
data survive
• To include all data in the
range in the merged cell,
move it to the upper left
most cell before merging
20
Activity on Page 66
21
Conditional Formatting
• Another way you can make your data easier to
interpret is to have Excel change the
appearance of your data based on its value.
These formats are called conditional formats
22
Conditional Formatting
• Format → Conditional
• You can set certain
Format, when value fall
outside of the set range,
or when a particular
formula meets certain
criteria
Student ID
Attendance
Status
1
10
DN
2
30
OK
3
45
OK
4
12
DN
5
33
OK
6
25
OK
Total Attendance
45
23
Finding cell with conditional
Formatting
• Edit → GOTO
• Choose Special and turn on conditional Format
near the bottom
• Choose All to find the cell in all worksheet that
have conditional Formatting
24
Copying Conditional Formatting
• Select the cell to receive the formatting,
plus one cell that already has the condition
you want to copy
• Go To Format → Conditional Formatting
• Click OK
25
Past Special Command
• After Copying the cell, Choose “past
special”
• It will let you choose variety of cell
attributes to apply, including formula with
or without formatting, resulting values,
formatting only, comments or validation
restrictions
• Transpose the new range, create a Hot
link between source and destination
26
Activity on Page 71
27
Making Printout Easier to Follow
• To ensure that the same information appears at
the top or bottom of every printed page, you can
do so using headers or footers
• To create a header or footer in Excel, you open
the Page Setup dialog box to the Header/Footer
tab
• Adding a graphic to a header or footer
• After you insert a graphic into a header or footer,
the Format Picture button will become available
28
Activity on Page 74
29
Positioning Data on a Printout
• Change is its margins, or the boundaries
between different sections of the printed
page
• Portrait or Landscape mode
• On Page Setup you can reduce the size
of the worksheet’s contents until the entire
worksheet can be printed on a single page
30
Activity on Page 78
31
Chapter 4 Key Points
1.
2.
3.
4.
5.
6.
7.
You can change excel default font and its size
You can use cell formatting, including borders,
alignment, and fill colors, to emphasize certain cells
in your worksheets.
Excel comes with a number of existing styles
If you want to apply the formatting from one cell to
another cell, use the Format Painter to copy the
format quickly
There are quite a few AutoFormats
Conditional formats let you set rules so that Excel
will change the appearance of a cell’s contents
based on its value
Use header, footer, graphic, alignment, and margin
settings to make your data look great on the page
32