Category: Advanced Spreadsheets

Create and apply custom number formats.

We have a variety of number formats which can be used to format the data in a cell in Calc. But sometimes we might need to format our data in some format which might not be available in Calc by default. In such situations, we can create our own custom number formats.

[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-formatting-numbers]
[the_ad id=”12356″]
To create and apply custom number formats, first of all select the cell to which the custom format needs to be applied. After this, click on ‘Format’ from the main menu bar, and from the resulting drop-down, click on ‘Cells’. This will open the ‘Format Cells’ dialog box.

In this dialog box, under the ‘Numbers’ tab, select ‘User-defined’ under ‘Category’. This gives a ‘Format code’ input field where you can define the custom number format. After defining the format, click on ‘OK’. The custom format which has been just defined will get applied to the cell.
[the_ad id=”12397″]

Apply conditional formatting based on cell content.

Conditional formatting helps us in formatting only those particular cells which satisfy the specified conditions. This means that in any selected cell range, we will define some condition and the cells which satisfy this condition will get formatted while the others will remain as it is.

To apply conditional formatting, first of all make sure that ‘AutoCalculate’ is enabled. This can be checked by clicking ‘Tools’ on the main menu bar.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-conditional-formatting]
[the_ad id=”12356″]

From the resulting drop-down, click on ‘Cell Contents’, and from the submenu, enable ‘AutoCalculate’.

Now select the data range on which the formatting needs to be applied.

After this, click on ‘Format’ from the main menu bar. From the drop-down, click on ‘Conditional Formatting’, and from the sub-menu select either one of ‘Condition’, ‘Color Scale’ or ‘Data Bar’. All of these will open the ‘Conditional Formatting’ dialog.

Create the conditions from the options available and then click on ‘Add’ to add the newly created condition. Create as many conditions as required by using different values and color schemes and styles and finally click on ‘OK’ after creating and adding all the required conditions. On click of ‘OK’, the formatting of the cells which satisfy the added conditions will change as per the defined styles.
[the_ad id=”12397″]

Apply an auto-format/table style to a cell range.

Auto formatting for a cell range formats that particular cell range as per the selected options automatically. This means that the complete selected cell range will get formatted in the selected manner without having to manually format each individual cell.
[the_ad id=”12355″]
[show_slider name=advanced-spreadsheets-formatting-cells]
[the_ad id=”12356″]

To apply auto-formatting to a cell range, select the cell range containing the cells on which the formatting needs to be applied. The cells selected should contain at least three columns and rows, including column and row headers.

After selecting the data, click on ‘Format’ from the main menu bar, and from the resulting drop-down click on ‘AutoFormat’. This opens the ‘AutoFormat’ dialog box.

Select the formatting properties which need to be included for the data, and after making all the changes, click on ‘OK’. This will make the selected formatting changes to the selected data.

[the_ad id=”12397″]