Diagrams help in explaining the data
and relations in large tables. But they
also take up extra space in the data
sheets.
Excel supports conditional formatting of data cells, which helps in increasing the visual presentation of values. Till now, the options for this were limited, but with the 2007 version, Excel has expanded the conditional formatting so that you can often do without additional diagrams. Select a data field to be presented by Excel and click on ‘Conditional Formatting’ in the ‘Home’ tab in the multi-function toolbar. Position the cursor on the ‘Color Scales’ and then click on one of the symbolized color gradients. All the selected cells thus get a gradational background color that depends on the relevant numeric value. Excel automatically determines the required value range so that the color scale with the default values is sufficient in a lot of cases.
The conditional formatting is equally
easy for ‘Data bars’ in the relevant
color as well as for ‘Symbol sets’,
wherein you can choose from a wide
range of variants. This includes signal
symbols, tendency arrows or level
indicators. Several variants can also be
combined with each other as long as the
presentation is not impractical.
The default setting of the color scale is not always suitable, especially in tables containing just one or a few outliers, i.e. high or low values. In such a case, you can define for yourself which values should have which format. Select ‘Conditional Formatting | Color scales | More rules’ or ‘Conditional Formatting | More rules’. The ‘New Formatting rows’ dialog provides access to all settings. Set the row type to ‘Format all cells as per their values’. In the combination fi eld ‘Format type’, change the setting to ‘3-color scale’, for instance. All further options are automatically changed to the relevant elements. Now, define further configuration for Minimum, Average and Maximum separately. For Average, the type ‘Percentile’ with a value of ‘50’ percent is often practical. For linear or absolute scaling, you can also select the ‘Percent’ or ‘Number’ type and provide it with suitable values.
Excel supports conditional formatting of data cells, which helps in increasing the visual presentation of values. Till now, the options for this were limited, but with the 2007 version, Excel has expanded the conditional formatting so that you can often do without additional diagrams. Select a data field to be presented by Excel and click on ‘Conditional Formatting’ in the ‘Home’ tab in the multi-function toolbar. Position the cursor on the ‘Color Scales’ and then click on one of the symbolized color gradients. All the selected cells thus get a gradational background color that depends on the relevant numeric value. Excel automatically determines the required value range so that the color scale with the default values is sufficient in a lot of cases.
The default setting of the color scale is not always suitable, especially in tables containing just one or a few outliers, i.e. high or low values. In such a case, you can define for yourself which values should have which format. Select ‘Conditional Formatting | Color scales | More rules’ or ‘Conditional Formatting | More rules’. The ‘New Formatting rows’ dialog provides access to all settings. Set the row type to ‘Format all cells as per their values’. In the combination fi eld ‘Format type’, change the setting to ‘3-color scale’, for instance. All further options are automatically changed to the relevant elements. Now, define further configuration for Minimum, Average and Maximum separately. For Average, the type ‘Percentile’ with a value of ‘50’ percent is often practical. For linear or absolute scaling, you can also select the ‘Percent’ or ‘Number’ type and provide it with suitable values.
0 comments:
Post a Comment
please write your comment
Note: Only a member of this blog may post a comment.