First, we need to know.
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 Pre-defined table style, select the cells to be formatting. 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 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
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 the style choices, the selected cell or range temporarily displays the style. When you see a style you like. Click it to apply the style to the selection.
Modifying an existing cell style:
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.
Click on Cell Styles icon then select New Cell Style…, StyIe Dialog box appears, Click on Format Button Format Cell Dialog box appears, Choose the required formatting and click on an ok button, you will return back to Style dialog box now click on ok button to finish the creation.
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 formattings 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 choose New Cell Style. Excel displays its Style dialog box, along with a proposed basic name for 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 check boxes 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 to 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 won’t appear in other workbooks. However, you can copy or merge styles from other workbooks into the current workbook.
Merging is a powerful tool, but you should use it with some caution. If the workbook into 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 Cell
Style dropdown list. If you click on Cell Style icon you will view 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 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 Al and you change the value contained in Al, the formula result changes to reflect the new value. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas. Here we will discuss the following cell references:
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 their 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 offset from the current row and column.
- Absolute: It refers to the cell by their 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 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
By default, Excel creates relative cell references in formulas. The distinction becomes apparent when you copy a formula to another cell. The formula in cell D2, which multiplies the quantity by the price, is =B2*C2. This formula uses relative cell references. Therefore, when the formula is copied to the cells below it, the references adjust in a relative manner. For example, the formula in cell D3 is B3*C3
Absolute Cell References
An absolute reference use two dollar signs in its address: one for the column letter and one for the row number (for example, $A$5).Now I’ll extend the
example to calculate Vat, which is stored in cell B6 (see Figure above). In this situation, the formula in cell D2 is =B2*C2*$B$6. The quantity is multiplied by the price, and the result is multiplied by the Vat rate stored in cell B6. Notice that the reference to B6 is an absolute reference. When the formula in D2 is copied to the cells below it, cell D3 will contain this formula:
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 revenues, B2 is the cost, B3 you need to calculate bonus 5% of cell B4 (Pre Tax Income) and in you need to deduct cost (B2) and bonus (B3). If we simply put the formula then the result will not update in cell B3.
Now go to File Tab ⇒ Option⇒ activate “Enable iterative calculation checkbox from Formulas Tab. After that, it will give you results.