How do you add a rule to cells in excel?


I have a set of data extracted from Hyperion Essbase in which I would like to add a rule over those cells. I've tried conditional formatting, but I'm not getting what I want.

For example some cells contain 0.000 or 13.5232 and the rule I want over them is IF(ROUND(ABS(Cell Value),2)<=0.01,0,Cell Value)

I'm not sure if my formula is the one that doesn't make sense or I'm not using conditional formatting correctly. I don't need any cells highlighted, I would just like the data in the cells rounded if they fit the criteria in my formula.


From your question, it seems like you want to use conditional formatting to change how the numbers are formatted (for numbers whose absolute value, rounded to 2 decimal places, is <= 0.01, they should be formatted with 0 decimal places, i.e., show as 0).

This can absolutely be achieved with conditional formatting (no VBA required).

<ol><li>Select the cells you wish to apply the conditional formatting to.</li> <li>Open the <strong>Conditional Formatting Rules Manager</strong>, and create a <a href="https://i.stack.imgur.com/eviIl.png" rel="nofollow"><img alt="new rule" class="b-lazy" data-src="https://i.stack.imgur.com/eviIl.png" data-original="https://i.stack.imgur.com/eviIl.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>.</li> <li>Select <strong>Use a formula to determine which cells to format</strong>, enter the formula that identifies the cells you wish to format (make sure you eschew absolute references). <a href="https://i.stack.imgur.com/CBHDO.png" rel="nofollow"><img alt="add formatting rule" class="b-lazy" data-src="https://i.stack.imgur.com/CBHDO.png" data-original="https://i.stack.imgur.com/CBHDO.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a></li> <li>Click the <strong>Format</strong> button and select <strong>Number</strong> > <strong>0 Decimal places</strong> (<a href="https://i.stack.imgur.com/nKKQl.png" rel="nofollow"><img alt="here" class="b-lazy" data-src="https://i.stack.imgur.com/nKKQl.png" data-original="https://i.stack.imgur.com/nKKQl.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>)</li> <li>Click OK 3 times to return back to the spreadsheet, and you should see the values formatted as desired: <a href="https://i.stack.imgur.com/3xJy3.png" rel="nofollow"><img alt="final data" class="b-lazy" data-src="https://i.stack.imgur.com/3xJy3.png" data-original="https://i.stack.imgur.com/3xJy3.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a></li> </ol>


If you just want to change the displayed number format then this should do.

Sub cfrZeroes() With Worksheets("sheet2").UsedRange.Cells .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=ROUND(ABS(" & .Cells(1).Address(0, 0) & "),2)<=0.01" .FormatConditions(.FormatConditions.Count).NumberFormat = "\0" End With End Sub

That will change -0.015 to 0.015 to look like 0 but it won't change the underlying raw value.


  • spark udf with data frame
  • How to parse given date string using moment.js?
  • Find text(A) and replace it with text(B) + HTML
  • how to sort the field in the mongo document which is inside array
  • Swift: Validate Username Input
  • Nhibernate QueryOver Orderby
  • Oracle: Using CTE with update clause
  • Simplifying the use of meshgrid in Matlab
  • How to resize image if the image is bigger than Textbox.But it not increase scale if smaller
  • jParallax trouble
  • Symfony2 redirect to https route fails (uses wrong port)
  • PostgreSQL 9.x - pg_read_binary_file & inserting files into bytea
  • custom string delimiters stringtemplate-4
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • Making header act like google chrome mobile app address bar
  • How to merge keras sequential models with same input?
  • Prevent page break in text block with iText, XMLWorker
  • Can I read an iPhone beacon with Windows.Devices.Bluetooth.Advertisement.BluetoothLEManufacturerData
  • How to detect interior vertices in groups of 2d polygons? (E.g. ZIP Codes to determine a territory)
  • Convert Type Decimal to Hex (string) in .NET 3.5
  • Jackson Parser: ignore deserializing for type mismatch
  • Ajax Loaded meta Tags
  • Xamarin Forms - UWP Fonts
  • Splitting given String into two variables - php
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Is my CUDA kernel really runs on device or is being mistekenly executed by host in emulation?
  • Sending data from AppleScript to FileMaker records
  • Deserializing XML into class C#
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • Build own AppleScript numerical error handling
  • Calling of Constructors in a Java
  • Arrays break string types in Julia
  • Traverse Array and Display in markup
  • How to format a variable of double type
  • Transpose CSV data with awk (pivot transformation)
  • Buffer size for converting unsigned long to string
  • KeystoneJS: Relationships in Admin UI not updating
  • AngularJs get employee from factory
  • Load html files in TinyMce
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?