Excel 2007, Formatting a workbook

 

1. Format text, numbers, and dates
2. Change font colors and fill colors
3. Merge a range into a single cell
4. Apply a built-in cell style.
5. Select a different theme.
6. Apply a built-in table style.
7. Add conditional formats to tables with highlight rules and data bars.
8. Hide worksheet rows.
9. Insert print titles, set print areas, and insert page breaks
10. Enter headers and footers.

Important Points.

1. Formatting is the process of changing the appearance of your workbook by defining the fonts, styles, colors, and decorative features.
2. A theme is a collection of formatting that specifies the fonts, colors, and graphical effects.
3. Live preview shows the effects of formatting options on the workbook before you apply them.
4. Typeface is the specific design used for the characters, including letters, numbers, punctuation marks, and symbols.
5. Font is a set of characters the employ the same typeface.
6. Theme font is associated with a particular theme and used for heading and body text in the workbook.
7. A non-theme font retains its appearance no matter what the them is.
8. To format text
a. In font group on the home tab, click the font arrow to display a list of fonts.
b. In the font group click the font size arrow and select the size
c. in the font group click the bold or Italic button or both.
9. Working with color
a. Theme colors are the 12 colors that belong to the workbook's theme. 4 for text and background, 6 for accents and highlights, and 2 for hyperlinks.
b. Standard colors- 10- dark red, red, orange, yellow, light green, green, light blue, blue, dark blue, and purple.
c. Custom colorr can be created by specifying a mixture of red, blue, and green color values 16.7 million possible.
d. Automatic color uses your windows default text and background color values.
10. To change font and fill colors
a. in the font group on the home tab click the font color button arrow, point to the color you want.
b. Select the range of cell to be formatted.
c. In the font group click the fill color button arrow, select the color.
d. in the font group click the font color button arrow, select the color.
11. The mini toolbar appears when you select text and contains buttons for commonly used formats.
12. Setting a background image.
a. click page layout tab on the ribbon
b. in the page setup group click background button
c. navigate to the folder where the image resides
d. click the image
e. click the insert button.
13. General number format displays number exactly as you enter them.
14. Formatting numbers.
a. Select the range of cell to be formatted.
b. Click the one of the style buttons in the number group on the home tab.
c. Comma Style - adds the comma separator to each of the values in the table with two digits to the right of the decimal point.
d Decrease decimal. - to remove decimal places.
e. Currency Style - adds the dollar signs, commas, and keeps tow decimal places. dollar sign at left of cell and decimals lined up vertically.
f .Percent Style- values appear with percent signs and without zeros.
g. Increase Decimal - to display additional decimal places.
h. Dates and time - in number group on the home tab click the number format arrow and select the data and time format of your choice.
15. Aligning Cell content

Align the cell content with the cells top edge
Vertically center the cell content within the cell
Align the cell content with the cells bottom edge
Align the cell content with the cells left edge

Horizontally center the cell content within the cell
Align the cell content with the cells right edge
Decrease the size of the indentation used in the cell
Increase the size of the indentation used in the cell
Rotate the cell content to an angle within the cell
Force the cell text to wrap within the cell borders
Merge the selected cells into a single cell

16. Adding cell borders - Select the cells, in the font group on the home tab click border button arrow, then select the border type.
17. Format Cells dialog box.
18. In the number group on the home tab click the Dialog Box Launcher which has six tabs.
Number-provides options for formatting the appearance f numbers, dates, etc.
Alignment-provide option for how data is aligned within a cell
Font-provides option for selecting font type, sizes, styles, etc.
Border-Provides options for adding cell borders
Fill-Provides options for creating and applying background colors and patterns to cells
Protection-provides options for locking or hiding cells to prevent other users from modifying their contents.

19. Format Painter- allows you to copy the format of cells to other cells.- one click to one range or cell, two click to multiple ranges or cells.
20. In the clipboard group on the home tab click format painter.
21. Paste Option Button
Keep source formatting
Use Destination Theme
Match Destination Formatting
Values only
Values and Number Formatting
Values and Source Formatting
Keep Source Column Widths
Formatting only
Link Cells
22. Paste Special - select and copy range, select where you want to paste, click the past button arrow in the clipboard group on the home tab, then click Paste Special.

All

All using source theme

Formulas

All except borders

Values

Column widths

Formats

Formulas and number formats

Comments

Values and number formats

Validation

None

Multiply

Add

Divide

Subtract

Skip Blanks

Transpose

23. A style is a collection of formatting.
24. Applying Styles :Select the cell or range- in the styles group on the home tab click the cell styles button, select the style you want.
25. Working with Themes - click page layout tab on the ribbon, in the themes group0 click the themes button, click the theme you want.
26. To copy a theme from one workbook to another - open the workbook with the styles you want to cop, open the workbook in which you want to copy those styles, in the styles group on the home tab, click the cell styles button, then click merge styles., select the workbook with the styles you want to copy and then click Ok.
27. To save a workbook theme - In the themes group on the page layout tab click the themes button, click save current theme, select a save location, type a descriptive name in the file name box, click save.
28. Excel table is a range of data.
29. Apply a table style - select the range, in the styles group on the home tab click the format as tale button, click the table style that you want,
30. Banded rows display alternate rows with different fill colors.
31. Table style options -
a. Header row- formats the first row of the table.
b. Total row -inserts new row at bottom of table for column values
c. First Column - formats the first column of the table
d. Last Column - formats the last column of the table
e. Banded Rows - formats alternating rows in different colors:
f. Banded Columns - formats alternating columns in different colors.
32. Conditional Formatting - applies formatting only when a cell's value meets a specified condition.
33. Applying Conditional Formatting - select the range or ranges, in the styles group on the home tab, click conditional formatting button, pint to data bars, click a data bar color.
34. A data bar is a horizontal bar added to the background of a cell to provide a visual indicator of the cells value.
35. Clear conditional formatting- select table, n styles group on the home tab click conditional formatting button, point to click rules, click clear rules from this table.
36. Highlighting Cells
37. Highlighting rules

Rule

Highlights

Greater Than >

cells that are greater than a specified number

Less Than <

cells that are less than a specified number

Between

cells that are between two specified numbers

Equal to =

cells that are equal to a specified number

Text that contains

cells that contain specified text

A date occurring

cells that contain a specified date

Duplicate values

cells that contain duplicate or unique values
38. To highlight cells - select range of cells, in styles group in the home tab, click conditional formatting, pint to highlight cells rules, select the rule, enter the data, select the color, click ok.
39. A legend is a key that show each color used in the worksheet and what it means. Good idea to always have a legend when using conditional formatting.
40. Hiding Worksheet Data. Select the data to be hidden, in the cells group on the home tab click the format button and point to hide & unhide, click hide rows.
41. formatting the worksheet for printing.
42. Page orientation to landscape: click page layout tab on the ribbon, in the page setup group click the orientation button , click landscape.
43. Defining a Print area: Select the range of cells to be printed, in page setup group on the page layout tab click print area button, click set print area.
44. clearing a print area. In page setup group, click print area button, click clear print area.
45. Manual Page breaks specify exactly where the page breaks occur.
46. To set a page break- select the first cell below the row where you want to insert a page break, in the page setup group on the page layout tab, click the breaks button click insert page break.
47. To remove a page break: select any cell below or to the right of the page break you want to remove., in the page setup group on the page layout tab, click the breaks button, click remove page break or click reset all page breaks.
48. Adding print titles that appear on each page. in the page setup group on the page layout tab, click print titles button, click the row to repeat at the top, select the range of cells, click ok.
49. Adding headers and footers- header is the text printed in the top margin of each page, a footer is the text printed in the bottom margin of each page. a margin is the space between the page content and the edges of the page.
50. To insert header or footer- click the left section of the header directly above cell A1. Fill in the left, center and right sections.