Data formatting & custom settings

18/07/2018
| 2,431 Comments

Data formatting & custom settings

Data formatting is inbuilt features which is applied on available data to for number, text, date, day, month, year, currency settings.

Excel Formatting – number formatting

Number formatting refers to the addition of currency symbols, decimal markers, percent signs, and other symbols that help to identify the type of data present in a cell and to make it easier to read.

Steps to Apply Number Formatting

  • Select data table -> Right Click on the cell -> Choose Cell Formatting options or press Ctrl+1 Short cut key

Add Rs. Symbol using custom settings.

Adding lakh Separator in MS Excel using custom settings

Eg.

10,23,876.00– ##”,”##”,”###.00

28/01/2012 – DD/MM/YYYY

20-JAN-2012 –DD-MMM-YYYY

Formatting a Table

Excel provides several predefined table styles (also called quick styles) that you can use to quickly and easily format a table. Table styles include professional looking combinations of fill colors, fonts, borders, font colors and patterns. If none of the predefined table styles meet your needs, you can create your own custom styles.

Table Styles are found on the contextual Design tab under Table Tools. The Ribbon displays only a few of the available styles. To scroll the style gallery, click the previous or next buttons. To display the entire table gallery, click the More button.

You can further modify the formatting of your table by modifying table style options such as hiding or displaying the header row, adding special formatting to the first or last columns, or displaying banded rows or columns, in which the even rows or columns are formatting differently from the odd rows and columns, much like an accounting greenbar report.

To remove a table style, click the More button on the Table Styles group and choose Clear from the menu. The table will display in the default table format.

To Format a Table

  1. Click anywhere within the table to activate it.
  2. Click the contextual Design tab under Table Tools on the Ribbon.
  3. Click the More button on the Table Styles group to display the styles gallery.
  4. Move your mouse pointer over any of the styles to display a preview of the style.
  5. Click the style that you want.
  6. To remove a table style, click the More button on the Table Styles group and click Clear on the menu.

To Format Table Elements

  1. Click anywhere within the table to activate it.
  2. Click the contextual Design tab under Table Tools on the Ribbon.
  3. On the Table Options group, do one of the following:
  4. To turn the header row on or off, select or clear the Header Row check box.
  5. To display special formatting for the first or last column of the table, select the First
    Column     or Last Column check box.
  6. To display odd and even rows with different formatting, select the Banded Rows
    check box.
  7. To display odd and even columns with different formatting, select the
    Banded Columns check box.
  8. To turn the totals row on or off, select or clear the Totals Row
    check box.
MS Excel Training