Before we study how we can use Table Style, lets understand how to change the color of the text, background of the cell or customize it.
To Change the color of the Text
- Select the cell, range of cells, text, or characters that you want to format with a different text color.
- Go to the Home tab-> Font group, and do one of the following:
To Change the background of the text
1. Select the cell, range of cells, text, or characters you want to format with a different background color.
2. On the Home tab, in the Font group, do one of the following:-
• To change the background color, click the arrow next to Fill Color, and then under Theme Colors or Standard Colors, Click the background color you want to use.
• To apply the most recently selected background color, click Fill Color.
• To apply a color other than the available theme and standard colors, click More Colors, and then define the color you want to use on the Standard tab or Custom tab of the Colors dialog box.
Apply a pattern or fill effect to a background color
- Select the cell, range of cells, text, or characters to which you want to apply a background color with fill effects.
- On the Home tab, in the Font group, click the Format Cell Font dialog box launcher next to Font, and then click the Fill tab.
- Under Background Color, click the background color that you want to use.
- Do one of the following:
- To use a pattern with two colors, click another color in the Pattern Color box, and then click a pattern style in the Pattern style box.
- To use a pattern with special effects, click fill effects, and then click the options that you want on the Gradient tab.
Pre-defined Table Style
Pre-defined table style offers a quick way to apply a standard format to all or part of a worksheet. Before you use the Pre-defined table style, select the cells to be formatted. Choose Home Tab-> Styles group-> Format as table and then select any desired style from the display list.
Using Styles for Easier Formatting
One of the most underutilized features in Excel is named styles. Named styles make it very easy to apply a set of predefined formatting options to a cell or range. In addition to saving time, using named styles also helps to ensure a consistent look.
A style can consist of settings for up to six different attributes:
- Number Format
- Font (type, size, and color)
- Alignment (vertical & horizontal)
- Borders
- Pattern Protection (locked & hidden)
The real power of styles is apparent when you change a component of a style. All cells that use that named style automatically incorporate the change
Creating New Styles:
In addition to using Excel’s built-in styles, you can create your own styles. This flexibility can be quite handy because it enables you to apply your favorite formatting options very quickly and consistently.
Steps in short
- Select a cell and apply all the formatting that you want to include in the new style. You can use any of the formatting that is available in the format cells dialog box.
- After you format the cell to your liking, choose Home tab-> Styles group-> Cell styles, and chose New cell style. Excel displays its style dialog box, along with a proposed basic name of the style. Note that Excel displays the words by example to indicate that it’s basing the style on the current cell.
- Enter a new style name in the style name box. The checkboxes display the current formats for the cell. By default, all checkboxes are checked.
- If you don’t want the style to include one or more format categories, remove the check(s) from the appropriate box(s).
- Click OK to create the style and close the dialog box.
Organize Worksheet
Rename Move or Copy Sheet and Tab Color
Go to Home Tab-> Cells group-> Format-> choose Rename Sheet/Move or copy sheet or any required option to change the format of a sheet
Rename Sheet- Use this option to change the name of the worksheet as well as the sheet tab. You can do the same thing by double-clicking on the sheet tab or right click on the sheet tab, and from the popup select Rename option. Excel Highlights the name of the sheet tab so that you can edit the name or replace it with a new name. Sheet names can be up to 31 characters, and spaces are allowed. However, you can’t use the following characters in sheet names
(:) Colon, (/) Front slash, (/) Backslash,(?) Question Mark, (*) Asterisk
Move or copy sheet- Use this option to move or copy a worksheet in the same or another workbook.(To move a worksheet to a different workbook, both workbooks must be open.
Tab color- Use this option to change the color of your worksheet tabs. For example, you may prefer to color-code the sheet tabs to make identifying the worksheet’s contents easier.
Hide sheet & Unhide Sheet- Use this option to hide and unhide the current worksheet.
Cell References
Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges rather than being restricted to fixed values. For example, if your formula refers to cell A1 and you change the value contained in A1, the formula result changes to reflect the new value. If you didn’t use references in your formula, you would need to edit the formulas themselves in order to change the values used in the formulas.
Using relative, absolute, and Mixed references
When you use a cell (or range) reference in a formula, you can use three types of references.:
- Relative: It refers to the cell by its position in relation to the cell that contains the formula. The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column.
- Absolute: It refers to the cell by its position in relation to the cell that contains the formula. The row and column references do not change when you copy the formula because the reference is to an actual cell address.
- Mixed: Any of the row or column references is relative, and the other is absolute.
