excel formatting manual
TRANSCRIPT
-
8/12/2019 Excel Formatting Manual
1/8
1
Microsoft Excel FormattingTips & Techniques
Excel design concepts
The Goal
Your goal is always to designing a clean and readable spreadsheetby:
Enhancing and emphasizing your datawhere appropriate
Creating contrastbetween the various sections of yourspreadsheet:
o document titleo column headerso row headerso data of different kinds
The Tools
Use these tools to create contrast and emphasis in your documents.
colors
borders
fonts
text stylesUse no more than two fonts in your document. To create contrastwithin data using a single font, use text styles like boldinganditalicizing. Most documents will use these two kinds of fonts incombination:
A serif font(like Times,shown below, left) for textin yourspreadsheet.
A sans seriffont (like Tahoma,shown below, right) for your
titles and headers.
a a
-
8/12/2019 Excel Formatting Manual
2/8
2
The Rules
Dont use too many fonts in your spreadsheet.
Only use color only if youll print or display your spreadsheeton a color device; otherwise, use black and white shadinginstead.
Use color, borders, and styles conservatively,unless youraudience demands radical design.
Let your design enhance the presentation of your data, notoverwhelm it.
Only create a design element that has a purpose. Dontsimply create one because it looks cool.
Know when to break the rules.
Formatting CellsAll about number formatsExcel permits numbers to be formatted in many different ways.Without changing the value of the number in a cell, numberformats allow numbers to be represented so that they can be usedin many different kinds of projects.
Applying number formats
Select the cellscontaining your number(s).
From theFormat menu, choose Cells.
Click on the Number tab. From the list of categories, select the number format that is
best suited to your project. Use the Samplebox to decidewhether the format you chose best represents your number.
-
8/12/2019 Excel Formatting Manual
3/8
3
Common Number Formats
Format Name Number looks like. OptionsGeneral 0.33 None
Number 0.33 Number of decimal placesshown, negative number
formatCurrency $0.33 Number of decimal places
shown, currency symbol,negative number format
Accounting $0.33 (lines up decimalpoints in a column of data)
Number of decimal placesshown, currency symbol
Percentage 33% Number of decimal placesshown
Fraction 1/3 Number of digits indenominator, type offraction
Text 0.33 Number is displayedexactly as entered
Other Formats
Format Name Data looks like. OptionsDate 1/1/00 Several date formats
available
Time 15:30; 1:30 pm; etc. Several time formatsavailable
Special Varies Zip code, phone number,social security number
Custom User-defined
-
8/12/2019 Excel Formatting Manual
4/8
4
Applying text formats
Select the cellsto receive the new text formatting.
From theFormat menu, choose Cells.
Click on the Fonttab.
To change basic text attributes:o Click on the name of the fontyou wish to use.o Click on the name of the font styleyou want.o Click on the font sizeyou wish to use.
To add or change the underlining in the selected cells, clickon the down-facing arrow next to the Underlinebox.
From the Underlinedrop-down menu, select the underlinestyle that you wish.
To change the color of your text, click on the down-facingarrow next to the Colorbox.
From the Colordrop-down menu, select the text color thatyou wish.
-
8/12/2019 Excel Formatting Manual
5/8
5
Applying cell borders
Select the cellsto receive the new border formatting.
From theFormat menu, choose Cells.
Click on theBorderstab.
To add a basic borderoutside the selected cell range,click on the Outlinebutton.
To add borders between cells within the selected cell range,click on the Insidebutton.
Click OK.
To add a custom border, first choose a style for your
border. In the Stylebox at the right side of the Format Cells
window, click on the line style you want for your cellborder.
Click on the down-facing arrownext to the Colorbox todisplay the Color menu. Click on the color that you wantfor your border.
Click on the buttons in the Border area of the FormatCellswindow to add sections of the cell border using thestyle and color you selected. Click OKto apply yourcustom border to the selected cell range.
-
8/12/2019 Excel Formatting Manual
6/8
6
Tip: Removing all font formatting
To remove all font formatting, click to place a check mark in theNormal fontcheckbox in the Format Cell swindows Fonttab.This will return the text in the selected cell or cell range to your
default font and style.
Tip: Applying existing formats to unformatted cell s
Select the formatted cell (or cell range).
Click on the Format Painterbutton on the standardtoolbar.
Click on an unformatted cell (or drag across anunformatted cell range) to apply the formatting.
Applying text alignment formatting
Select the cellsto receive the new formatting.
From theFormat menu, choose Cells.
Click on theAlignmenttab.
Click on the drop-down arrow next to the Horizontalbox
to display a drop-down menu of horizontal alignmentoptions.
Click OKto apply the alignment formatting to text within
the selected cells.
-
8/12/2019 Excel Formatting Manual
7/8
7
Formatting long text phrases within a cell
In many situations, the line of text you enter in a cell will be widerthan the cell itself. In these situations, the text may be hiddenbeyond the edge of the cell. Although one solution to this problemis to resize the cell, there are two additional solutions: shrinking
the text to fit the cell, and wrapping the textso that it displays onmultiple lines within the cell.
Select the cellsto receive the new formatting.
From theFormat menu, choose Cells.
Click on theAlignmenttab and go to the Text controlareaof the Format Cellswindow.
To shrink the text, click in the Shrink to fitbox.
To wrap text within the cell, click in the Wrap textbox.
Merging cells
Another solution for handling long text phrases is to merge severalcells together so that the text can be fully displayed. To mergeseveral cells:
Select the cellsto be merged.
From theFormat menu, choose Cells. Click on theAlignmenttab.
In the Text controlarea, click in the Merge cellsbox.
-
8/12/2019 Excel Formatting Manual
8/8
8
Applying conditional formattingExcels conditional formatting can apply specific formatting tohighlight cells in your worksheet that meet a certain condition. Toset up conditional formatting:
Select the cells that you wish to conditionally format.
From the Formatmenu, choose Conditional Formatting.
Next, specify the condition that will trigger yourformatting.
Click on the second down-facing arrowfrom the left to
display the operatorsmenu. Select the operator that youwant.
Next, enter the condition (or conditions) that will trigger
the formatting. In the example below, any selected cellwhose value is greater than 10 will receive special
formatting.
Finally, click on the Formatbutton to specify the
formatting that will be applied to cells that meet yourcondition. Youll be able to apply special font formatting,borders, and colors to these cells.
When you have finished selecting your formatting, clickOKto close the Format Cellswindow. Click OKagain toapply the conditional formatting to your cells.