First, we need to know.
Pre-defined Table style
Predefined table style offers a quick way to apply a standard format to all or part of a worksheet. Before using Predefined table style, select the formatting cells. choose Home Tab ⇒ style group ⇒Format as Table and select any desired style from the display list.
In office 2013 you may do this by selecting your table
click on Quick Analysis (Ctrl + Q) then go to the table tab and select table. Select your table then press Ctrl + L or Ctrl + T.
Using Styles for Easier Formatting
One of the most underused features in Excel is named styles. Named styles make it very easy to apply a set of pre-defined formatting options to a cell or range. In addition to saving time, the use of named styles also helps to ensure a consistent look. A style may consist of settings for up to six different attributes:
- Number format
Applying cell styles
Go To Home Tab ⇒ Style group ⇒ Cell Styles. Note that this display is a “live preview”- As you move your mouse over style choices, the style is temporarily displaced by the selected cell or range. When you see a style that you like. Click on it to select the style.
Modifying an existing cell style:
Creating new styles:
In addition to using the built-in styles of Excel, you can create your own styles. This flexibility can be quite handy because it allows you to apply your favorite formatting options very quickly and consistently.
Click on Cell Styles icon then select New Cell Style…, StyIe Dialog box appears, Click the Format button Format Cell dialog box, select the formatting required and click the OK button to return to the Style dialog box, then click the OK button to finish the creation.
Steps in Short:
- Select a cell and apply all the formatting you want to include in the new style. You can use any of the formatting available in the Format Cells dialog box.
- After formatting the cell to your liking, choose Cell Styles group Home Tab Styles and choose New Cell Style. Excel displays its Style dialog box, along with a proposed basic style name. Note that Excel displays the words By Example to indicate that it bases the style on the current cell.
- Enter the name of a new style in the Style Name box. 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.
When you create a new style, you can use it only in the workbook where you create it — the new style is saved in the current workbook and will not appear in other workbooks. You can copy or merge styles from other workbooks into the current workbook, however.
Merging is a powerful tool, but with some caution, you should use it. If the workbook in which you merge styles has matching style names, the new styles can override those existing styles and be applied throughout your workbook. After creating the style, it will be enlisted in the drop-down list of Cell Style. If you click the Cell Style icon, you will see the newly created style.
Deleting existing styles
- Right click on the style you want to delete.
- Select Delete option from the pop-up menu that appeared
Most of the 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 limited to fixed values. For example, if your formula refers to cell Al and you change the value contained in Al, the result of the formula changes to reflect the new value. If you did not use references in your formulas, you would need to edit the formulas themselves to change the values used in the formulas. We will discuss the following cell references here:
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 containing the formula. The references to the row and column may change when you copy the formula to another cell because the references are actually offset from the current row and column.
- Absolute: It refers to the cell by its position in relation to the cell containing the formula. The references to the row and column do not change when you copy the formula because the reference is to the actual address of the cell.
- Mixed: Any of the row or column reference is relative, and the other is absolute.
- Circular: When a formula refers back to its own cell, either directly or indirectly, it is called a circular.
Relative Cell References
Excel creates relative cell references in formulas by default. When you copy a formula to another cell, the distinction becomes apparent. The formula in cell D2, multiplying the quantity by the price, is= B2*C2. This formula uses references to relative cells. Therefore, when the formula is copied to the cells below it, the references will adjust in a relative manner. For instance, the formula in cell D3 is B3*C3
Absolute Cell References
An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$ 5). Now I will extend the example to calculate Vat, which is stored in cell B6 (see Figure above). The formula in cell D2 in this situation is= B2*C2*$B$6. The quantity is multiplied by the price, and the result is multiplied by the rate of Vat stored in cell B6. Note that the B6 reference is an absolute reference. Cell D3 will contain this formula when the formula in D2 is copied to the cells below it:
Mixed Cell References
The figure demonstrates the use of mixed references. The formulas in the C3: E7 ranges calculate the phone bill for a various call made and phone rate. The formula in cell C3 is =$A3*C$2
Circular Cell References
Look at the figure, Cell Bl is the revenue, B2 is the cost, B3 you need to calculate bonus 5 percent of cell B4 (Pre Tax Income) and deduct costs (B2) and bonus (B3). If we simply put the formula, the result will not be updated in cell B3.
Now go to File Tab ⇒ Option⇒ activate “Enable iterative calculation checkbox from Formulas Tab. After that, it will give you results.