·
Formatting Worksheets
·
Excel - Sheet Options
·
Excel - Adjust Margins
·
Excel - Page Orientation
·
Excel - Header and Footer
·
Excel - Insert Page
Breaks
·
Excel - Set Background
·
Excel - Freeze Panes
·
Excel - Conditional
Format
Sheet Options in Excel
2010
Sheet Options
MS Excel provides
various sheet options for printing purpose like generally cell gridlines aren’t
printed. If you want your printout to include the gridlines, Choose Page
Layout » Sheet Options group » Gridlines » Check Print.
Options in Sheet Options Dialogue
·
Print
Area − You can set the
print area with this option.
·
Print
Titles − You can set
titles to appear at the top for rows and at the left for columns.
·
Print −
o Gridlines − Gridlines to appear while printing worksheet.
o Black & White − Select this check box to have your color
printer print the chart in black and white.
o Draft quality − Select this check box to print the chart using your
printer’s draft-quality setting.
o Rows & Column Heading − Select this check box to have rows and
column heading to print.
·
Page
Order −
o Down, then Over − It prints the down pages first and then
the right pages.
o Over, then Down − It prints right pages first and then
comes to print the down pages.
Adjust Margins in
Excel 2010
Margins
Margins are the
unprinted areas along the sides, top, and bottom of a printed page. All printed
pages in MS Excel have the same margins. You can’t specify different margins
for different pages.
You can set margins by
various ways as explained below.
·
Choose Page Layout »
Page Setup » Margins drop-down list, you can select Normal, Wide, Narrow, or
the custom Setting.
·
These options are also
available when you choose File » Print.
If none of these
settings does the job, choose Custom Margins to display the Margins tab of the
Page Setup dialog box, as shown below.
Center on Page
By default, Excel aligns
the printed page at the top and left margins. If you want the output to be
centered vertically or horizontally, select the appropriate check box in the
Center on Page section of the Margins tab as shown in the above screenshot.
Page Orientation in
Excel 2010
Page Orientation
Page orientation refers
to how output is printed on the page. If you change the orientation, the
onscreen page breaks adjust automatically to accommodate the new paper
orientation.
Types of Page Orientation
·
Portrait − Portrait to print tall pages (the
default).
·
Landscape − Landscape to print wide pages. Landscape
orientation is useful when you have a wide range that doesn’t fit on a
vertically oriented page.
Changing Page Orientation
·
Choose Page Layout »
Page Setup » Orientation » Portrait or Landscape.
·
Choose File » Print.
Header and Footer in
Excel 2010
Header and Footer
A header is the
information that appears at the top of each printed page and a footer is the
information that appears at the bottom of each printed page. By default, new
workbooks do not have headers or footers.
Adding Header and Footer
·
Choose Page Setup dialog
box » Header or Footer tab.
You can choose the
predefined header and footer or create your custom ones.
·
&[Page] − Displays the page number.
·
&[Pages] − Displays the total number of pages to be
printed.
·
&[Date] − Displays the current date.
·
&[Time] − Displays the current time.
·
&[Path]&[File] − Displays the workbook’s complete path
and filename.
·
&[File] − Displays the workbook name.
·
&[Tab] − Displays the sheet’s name.
Other Header and Footer Options
When a header or footer
is selected in Page Layout view, the Header & Footer » Design »
Options group contains controls that let you specify other options −
·
Different
First Page − Check this to
specify a different header or footer for the first printed page.
·
Different
Odd & Even Pages − Check this to specify
a different header or footer for odd and even pages.
·
Scale
with Document − If checked, the
font size in the header and footer will be sized. Accordingly if the document
is scaled when printed. This option is enabled, by default.
·
Align
with Page Margins − If checked, the
left header and footer will be aligned with the left margin, and the right
header and footer will be aligned with the right margin. This option is
enabled, by default.
Insert Page Break in
Excel 2010
Page Breaks
If you don’t want a row
to print on a page by itself or you don't want a table header row to be the
last line on a page. MS Excel gives you precise control over page
breaks.
MS Excel handles page
breaks automatically, but sometimes you may want to force a page break either
a vertical or a horizontal one. so that the report prints the way you want.
For example, if your
worksheet consists of several distinct sections, you may want to print each
section on a separate sheet of paper.
Inserting Page Breaks
Insert Horizontal Page
Break − For example, if
you want row 14 to be the first row of a new page, select cell A14. Then
choose Page Layout » Page Setup Group » Breaks » Insert Page Break.
Insert vertical Page
break − In this case,
make sure to place the pointer in row 1. Choose Page Layout » Page
Setup » Breaks » Insert Page Break to create the page break.
Removing Page Breaks
·
Remove
a page break you’ve added −
Move the cell pointer to the first row beneath the manual page break and then
choose Page Layout » Page Setup » Breaks » Remove Page Break.
·
Remove
all manual page breaks −
Choose Page Layout » Page Setup » Breaks » Reset All Page Breaks.
Set Background in
Excel 2010
Background Image
Unfortunately, you
cannot have a background image on your printouts. You may have noticed
the Page Layout » Page Setup » Background command. This button
displays a dialogue box that lets you select an image to display as a background.
Placing this control among the other print-related commands is very misleading.
Background images placed on a worksheet are never printed.
Alternative to Placing Background
·
You can insert a Shape,
WordArt, or a picture on your worksheet and then adjust its transparency. Then
copy the image to all printed pages.
·
You can insert an object
in a page header or footer.
Freeze Panes in Excel
2010
Freezing Panes
If you set up a
worksheet with row or column headings, these headings will not be visible when
you scroll down or to the right. MS Excel provides a handy solution to this
problem with freezing panes. Freezing panes keeps the headings visible while
you’re scrolling through the worksheet.
Using Freeze Panes
Follow the steps
mentioned below to freeze panes.
·
Select the First row or
First Column or the row Below, which you want to freeze, or Column right to
area, which you want to freeze.
·
Choose View Tab
» Freeze Panes.
·
Select the suitable
option −
o Freeze Panes − To freeze area of cells.
o Freeze Top Row − To freeze first row of worksheet.
o Freeze First Column − To freeze first Column of worksheet.
·
If you have selected
Freeze top row you can see the first row appears at the top, after scrolling
also. See the below screen-shot.
Unfreeze Panes
To unfreeze Panes,
choose View Tab » Unfreeze Panes.
Conditional Format in
Excel 2010
Conditional Formatting
MS Excel 2010
Conditional Formatting feature enables you to format a range of values so that
the values outside certain limits, are automatically formatted.
Choose Home Tab
» Style group » Conditional Formatting dropdown.
Various Conditional Formatting Options
·
Highlight
Cells Rules − It opens a
continuation menu with various options for defining the formatting rules that
highlight the cells in the cell selection that contain certain values, text, or
dates, or that have values greater or less than a particular value, or that fall
within a certain ranges of values.
Suppose you want to find
cell with Amount 0 and Mark them as red.Choose Range of cell » Home Tab »
Conditional Formatting DropDown » Highlight Cell Rules » Equal To.
After Clicking ok, the
cells with value zero are marked as red.
·
Top/Bottom
Rules − It opens a
continuation menu with various options for defining the formatting rules that
highlight the top and bottom values, percentages, and above and below average
values in the cell selection.
Suppose you want to
highlight the top 10% rows you can do this with these Top/Bottom rules.
·
Data
Bars − It opens a
palette with different color data bars that you can apply to the cell selection
to indicate their values relative to each other by clicking the data bar
thumbnail.
With this conditional
Formatting data Bars will appear in each cell.
·
Color
Scales − It opens a
palette with different three- and two-colored scales that you can apply to the
cell selection to indicate their values relative to each other by clicking the
color scale thumbnail.
See the below screenshot
with Color Scales, conditional formatting applied.
·
Icon
Sets − It opens a
palette with different sets of icons that you can apply to the cell selection
to indicate their values relative to each other by clicking the icon set.
See the below screenshot
with Icon Sets conditional formatting applied.
·
New
Rule − It opens the New
Formatting Rule dialog box, where you define a custom conditional formatting
rule to apply to the cell selection.
·
Clear
Rules − It opens a continuation
menu, where you can remove the conditional formatting rules for the cell
selection by clicking the Selected Cells option, for the entire worksheet by
clicking the Entire Sheet option, or for just the current data table by
clicking the This Table option.
·
Manage
Rules − It opens the
Conditional Formatting Rules Manager dialog box, where you edit and delete
particular rules as well as adjust their rule precedence by moving them up or
down in the Rules list box.
