Format Cell Contents

FormatCellContents.SK

This function is used to apply formatting to the content present in the cell like formatting the colour of the cell, making it bold etc.

To access these format functions, go to the more section and then the Format tab. (See figure)

The following guidelines describe how to format data as desired before exporting it.

Step 1:

Select the Cell Contents Button in the Format tab to open this dialogue box.

Step 2:

Choose the range of cells which have the desired formats to be applied to the data range.

Step 3:

Select the data range to which the format has to be applied.

The Precedent option is used if some other function has to be calculated before applying this format function.

The Conditions option is used if the format that is to be applied is conditional (Based on some criteria, some cells of the data range are not to be formatted). (shown in Figure)

Note: The range of the conditions should be of the same size as the data range.

Format Cell Contents using Offset

We have to format the given data based on the account number.

Step 1: Remove the duplicates of account number

Step 2: Get the serial number and find its mod value which is further used in offset.

Step 3: Write the formatcells function using the offset function for the source range. The OFFSET function returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.

Here we use the $J$1 as the reference cell and offset function will select the source range with reference to M.

Step 4: Fill down the formula for the remaining formats on the data.

Result: The formats are applied on the data using formatcellcontents with offset function.