Cmdlet

Add-OfficeExcelConditionalFormat

Aliases: ExcelConditionalFormat

Module: PSWriteOffice
Aliases: ExcelConditionalFormat
Inputs: OfficeIMO.Excel.ExcelDocument
Source: AddOfficeExcelConditionalFormatCommand.cs:53 Edit on GitHub

Adds a conditional format: a rule, color scale, data bar or icon set.

Syntax

Add-OfficeExcelConditionalFormat
[-BackgroundColor <OfficeColor>]
[-Color <OfficeColor>]
[-Document <ExcelDocument>]
[-EqualAverage]
[[-Operator] <ExcelConditionalFormattingOperator>]
[-PassThru]
[-Percent]
[-Range] <String>
[-Rank <UInt32>]
[-Rule <OfficeExcelConditionalRuleType>]
[-SecondValue <String>]
[-Sheet <OfficeSheetSelector>]
[-StandardDeviation <UInt32>]
[-StopIfTrue]
[-Text <String>]
[-TimePeriod <ExcelConditionalTimePeriod>]
[[-Value] <String>]
[<CommonParameters>]

Parameter set: RuleRange

Add-OfficeExcelConditionalFormat
[-Document <ExcelDocument>]
-EndColor <OfficeColor>
[-MidColor <OfficeColor>]
[-PassThru]
[-Range] <String>
[-Sheet <OfficeSheetSelector>]
-StartColor <OfficeColor>
[<CommonParameters>]

Parameter set: ColorScaleRange

Add-OfficeExcelConditionalFormat
[-Color <OfficeColor>]
-DataBar
[-Document <ExcelDocument>]
[-PassThru]
[-Range] <String>
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]

Parameter set: DataBarRange

Add-OfficeExcelConditionalFormat
[-Document <ExcelDocument>]
-IconSet <ExcelIconSet>
[-NoValue]
[-NumberThreshold <Double[]>]
[-PassThru]
[-PercentThreshold <Double[]>]
[-Range] <String>
[-Reverse]
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]

Parameter set: IconSetRange

Add-OfficeExcelConditionalFormat
[-BackgroundColor <OfficeColor>]
[-Color <OfficeColor>]
[-Document <ExcelDocument>]
[-EqualAverage]
-Header <String>
[-HeaderRow <Int32>]
[-IncludeHeader]
[[-Operator] <ExcelConditionalFormattingOperator>]
[-PassThru]
[-Percent]
[-Rank <UInt32>]
[-Rule <OfficeExcelConditionalRuleType>]
[-SecondValue <String>]
[-Sheet <OfficeSheetSelector>]
[-StandardDeviation <UInt32>]
[-StopIfTrue]
[-Table <String>]
[-Text <String>]
[-TimePeriod <ExcelConditionalTimePeriod>]
[[-Value] <String>]
[<CommonParameters>]

Parameter set: RuleHeader

Add-OfficeExcelConditionalFormat
[-BackgroundColor <OfficeColor>]
[-Color <OfficeColor>]
[-Document <ExcelDocument>]
[-EqualAverage]
[[-Operator] <ExcelConditionalFormattingOperator>]
[-PassThru]
[-Percent]
-PivotTable <String>
[-PivotWholeTable]
[-Rank <UInt32>]
[-Rule <OfficeExcelConditionalRuleType>]
[-SecondValue <String>]
[-Sheet <OfficeSheetSelector>]
[-StandardDeviation <UInt32>]
[-StopIfTrue]
[-Text <String>]
[-TimePeriod <ExcelConditionalTimePeriod>]
[[-Value] <String>]
[<CommonParameters>]

Parameter set: RulePivotTable

Add-OfficeExcelConditionalFormat
[-Color <OfficeColor>]
-DataBar
[-Document <ExcelDocument>]
-Header <String>
[-HeaderRow <Int32>]
[-IncludeHeader]
[-PassThru]
[-Sheet <OfficeSheetSelector>]
[-Table <String>]
[<CommonParameters>]

Parameter set: DataBarHeader

Add-OfficeExcelConditionalFormat
[-Color <OfficeColor>]
-DataBar
[-Document <ExcelDocument>]
[-PassThru]
-PivotTable <String>
[-PivotWholeTable]
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]

Parameter set: DataBarPivotTable

Add-OfficeExcelConditionalFormat
[-Document <ExcelDocument>]
-EndColor <OfficeColor>
-Header <String>
[-HeaderRow <Int32>]
[-IncludeHeader]
[-MidColor <OfficeColor>]
[-PassThru]
[-Sheet <OfficeSheetSelector>]
-StartColor <OfficeColor>
[-Table <String>]
[<CommonParameters>]

Parameter set: ColorScaleHeader

Add-OfficeExcelConditionalFormat
[-Document <ExcelDocument>]
-Header <String>
[-HeaderRow <Int32>]
-IconSet <ExcelIconSet>
[-IncludeHeader]
[-NoValue]
[-NumberThreshold <Double[]>]
[-PassThru]
[-PercentThreshold <Double[]>]
[-Reverse]
[-Sheet <OfficeSheetSelector>]
[-Table <String>]
[<CommonParameters>]

Parameter set: IconSetHeader

Add-OfficeExcelConditionalFormat
[-Document <ExcelDocument>]
-EndColor <OfficeColor>
[-MidColor <OfficeColor>]
[-PassThru]
-PivotTable <String>
[-PivotWholeTable]
[-Sheet <OfficeSheetSelector>]
-StartColor <OfficeColor>
[<CommonParameters>]

Parameter set: ColorScalePivotTable

Add-OfficeExcelConditionalFormat
[-Document <ExcelDocument>]
-IconSet <ExcelIconSet>
[-NoValue]
[-NumberThreshold <Double[]>]
[-PassThru]
[-PercentThreshold <Double[]>]
-PivotTable <String>
[-PivotWholeTable]
[-Reverse]
[-Sheet <OfficeSheetSelector>]
[<CommonParameters>]

Parameter set: IconSetPivotTable

Examples

Add data bars to a column

Open-OfficeExcel -Path .\Reports\Q3-Sales.xlsx {
    ExcelSheet 'Orders' {
        ExcelConditionalFormat -Header Amount -Table Orders -DataBar
    }
}

Draws data bars in the Amount column of the Orders table in the first accent colour.

Colour attainment from red to green

Open-OfficeExcel -Path .\Reports\Q3-Performance.xlsx {
    ExcelSheet 'Performance' {
        ExcelConditionalFormat -Header Attainment -Table Actuals -StartColor Danger -MidColor Warning -EndColor Success
        ExcelConditionalFormat -Header Change -Table Actuals -IconSet ThreeArrows
    }
}

Applies a three-colour scale from the theme's danger to success colours to target attainment, and arrows to the change against last quarter.

Flag negative margins with a rule

Open-OfficeExcel -Path .\Reports\Q3-Sales.xlsx {
    ExcelSheet 'Orders' {
        ExcelConditionalFormat E2:E500 -Rule CellIs -Operator LessThan -Value 0 -BackgroundColor '#FEE2E2' -Color Danger
    }
}

Highlights negative margins with a light red fill and red text.

Highlight accounts with no logon in 90 days

Open-OfficeExcel -Path .\Reports\Users.xlsx {
    ExcelSheet 'Users' {
        ExcelConditionalFormat -Header LastLogon -Table Users -Rule CellIs -Operator LessThan -Value '=TODAY()-90' -BackgroundColor Danger
    }
}

Colours the LastLogon cell when the date is more than 90 days old; -Value takes an Excel formula when it starts with '='.

Parameters

-BackgroundColor OfficeColor optionalsets: RuleRange, RuleHeader, RulePivotTable: Cell fill colour expressed as a literal OfficeColor or theme role.
-Color OfficeColor optionalsets: RuleRange, DataBarRange, RuleHeader, RulePivotTable, DataBarHeader, DataBarPivotTable: Text or rule colour expressed as a literal OfficeColor or theme role.
-Document ExcelDocument optionalpipeline: True (ByValue): Caller-owned workbook; omission uses the enclosing Excel content block.
-EqualAverage SwitchParameter optionalsets: RuleRange, RuleHeader, RulePivotTable: Include values equal to the average for average rules.
-Operator ExcelConditionalFormattingOperator optionalposition: 1sets: RuleRange, RuleHeader, RulePivotTable: Conditional formatting operator.; Possible values: LessThan, LessThanOrEqual, Equal, NotEqual, GreaterThanOrEqual, GreaterThan, Between, NotBetween, ContainsText, NotContains, BeginsWith, EndsWith
-PassThru SwitchParameter optional: Emits the native result after the operation.
-Percent SwitchParameter optionalsets: RuleRange, RuleHeader, RulePivotTable: Treat top/bottom rank as a percent.
-Range String requiredposition: 0sets: RuleRange, ColorScaleRange, DataBarRange, IconSetRange: A1 cell range on the selected worksheet.
-Rank UInt32 optionalsets: RuleRange, RuleHeader, RulePivotTable: Rank used by top/bottom rules.
-Rule OfficeExcelConditionalRuleType optionalsets: RuleRange, RuleHeader, RulePivotTable: Native conditional-format rule family; the default compares cell values.; Possible values: CellIs, Expression, Formula, DuplicateValues, UniqueValues, Top, Top10, Bottom, Bottom10, AboveAverage, BelowAverage, ContainsText, NotContainsText, BeginsWith, EndsWith, ContainsBlanks, NotContainsBlanks, ContainsErrors, NotContainsErrors, TimePeriod
-SecondValue String optionalsets: RuleRange, RuleHeader, RulePivotTable: Upper bound or second formula used by between comparisons.
-Sheet OfficeSheetSelector optional: Worksheet name, one-based position or native worksheet object. Multi-sheet commands also accept wildcard names.
-StandardDeviation UInt32 optionalsets: RuleRange, RuleHeader, RulePivotTable: Optional standard deviation threshold for average rules.
-StopIfTrue SwitchParameter optionalsets: RuleRange, RuleHeader, RulePivotTable: Stop evaluating later rules when this rule is true.
-Text String optionalsets: RuleRange, RuleHeader, RulePivotTable: Text used by text-matching rule types.
-TimePeriod ExcelConditionalTimePeriod optionalsets: RuleRange, RuleHeader, RulePivotTable: Time period used by time-period rules.; Possible values: Today, Yesterday, Tomorrow, Last7Days, ThisMonth, LastMonth, NextMonth, ThisWeek, LastWeek, NextWeek
-Value String optionalposition: 2sets: RuleRange, RuleHeader, RulePivotTable: Value or values consumed by the selected operation.
-EndColor OfficeColor requiredsets: ColorScaleRange, ColorScaleHeader, ColorScalePivotTable: End color. Named colors and hexadecimal values are accepted.
-MidColor OfficeColor optionalsets: ColorScaleRange, ColorScaleHeader, ColorScalePivotTable: Middle colour of a three-colour scale at the 50th percentile. Omission uses a two-colour scale.
-StartColor OfficeColor requiredsets: ColorScaleRange, ColorScaleHeader, ColorScalePivotTable: Start color. Named colors and hexadecimal values are accepted.
-DataBar SwitchParameter requiredsets: DataBarRange, DataBarHeader, DataBarPivotTable: Selects the native data-bar conditional-format family.
-IconSet ExcelIconSet requiredsets: IconSetRange, IconSetHeader, IconSetPivotTable: Icon set to apply.; Possible values: ThreeArrows, ThreeArrowsGray, ThreeFlags, ThreeTrafficLights1, ThreeTrafficLights2, ThreeSigns, ThreeSymbols, ThreeSymbols2, FourArrows, FourArrowsGray, FourRedToBlack, FourRating, FourTrafficLights, FiveArrows, FiveArrowsGray, FiveRating, FiveQuarters
-NoValue SwitchParameter optionalsets: IconSetRange, IconSetHeader, IconSetPivotTable: Hides cell values behind conditional-format icons.
-NumberThreshold Double[] optionalsets: IconSetRange, IconSetHeader, IconSetPivotTable: Numeric thresholds for native conditional-format icons.
-PercentThreshold Double[] optionalsets: IconSetRange, IconSetHeader, IconSetPivotTable: Percentage thresholds for native conditional-format icons.
-Reverse SwitchParameter optionalsets: IconSetRange, IconSetHeader, IconSetPivotTable: Reverse the icon order.
-Header String requiredsets: RuleHeader, DataBarHeader, ColorScaleHeader, IconSetHeader: Header text identifying the target column.
-HeaderRow Int32 optionalsets: RuleHeader, DataBarHeader, ColorScaleHeader, IconSetHeader: One-based row containing the column headers.
-IncludeHeader SwitchParameter optionalsets: RuleHeader, DataBarHeader, ColorScaleHeader, IconSetHeader: Includes the header cell in the resolved column range.
-Table String optionalsets: RuleHeader, DataBarHeader, ColorScaleHeader, IconSetHeader: Native Excel table used to resolve the source range or header.
-PivotTable String requiredsets: RulePivotTable, DataBarPivotTable, ColorScalePivotTable, IconSetPivotTable: Native pivot table whose data or entire range is targeted.
-PivotWholeTable SwitchParameter optionalsets: RulePivotTable, DataBarPivotTable, ColorScalePivotTable, IconSetPivotTable: Use the full pivot output range instead of the default data body range.

Common Parameters

This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.

For more information, see about_CommonParameters.