How to hide negative numbers in Excel?
When working with numerical data in Excel, you may sometimes want to prevent negative values from being displayed, either for clarity during presentations, preparing reports, or simply to meet specific formatting requirements. For example, when sharing a budget overview, you might not want to visually distract readers with negative numbers, or you may temporarily need to hide them for certain calculations. While manually removing negative values is tedious and can disrupt your data integrity, Excel offers several effective ways to automatically hide negative numbers while preserving the original data. This article introduces practical methods suitable for different skill levels and requirements, so you can select the most appropriate solution for your specific scenario.
Common applicable scenarios: preparing financial reports, sharing data with clients to emphasize positive achievements, visualizing only non-negative trends, or temporarily hiding errors represented as negative numbers.
Hide negative numbers in Excel with Conditional Formatting
Hide negative numbers in Excel with Format Cells
Hide negative numbers in Excel with Kutools for Excel
Hide or clear negative numbers with VBA macro
Display blank for negative numbers using Excel IF formula
Hide negative numbers in Excel with Conditional Formatting
Conditional Formatting is a flexible feature that allows you to dynamically apply formatting changes based on cell values. By using this feature, you can make any negative values invisible by matching their font color with the cell background, without altering your data or underlying calculations.
1. Select the data range you want to hide the negative numbers in.
2. On the ribbon, click Home > Conditional Formatting > Highlight Cells Rules > Less Than, as shown in the screenshot:
3. In the Less Than dialog box, enter 0 in the value field. Click the drop-down next to the formatting option and choose Custom Format to customize the appearance of qualifying cells, as illustrated below:
4. In the pop-up Format Cells dialog, under the Font tab, select a font color that matches your worksheet background (commonly white for standard sheets). This will effectively render any negative values invisible:
5. Click OK twice to close both dialogs. All negative values in your selected range will now be effectively hidden from view, while remaining present for calculations or further editing.
 |  |  |
This method is fast, keeps your data intact, and can be undone at any time. However, note that if your worksheet background is not white or uses conditional fill colors, you should choose a font color that exactly matches the desired background to make negative values fully invisible. Hidden negative values may still be visible if cell backgrounds are changed in the future.
Hide negative numbers in Excel with Format Cells
The Format Cells option allows you to directly control how numbers are displayed, including customizing formats so that negative values appear blank. This approach is best when you want to control number presentation uniformly across a range without using additional layers of formatting.
1. Select the range of cells that you want to hide negative values in.
2. Right-click in the selection and pick Format Cells from the context menu. Reference screenshot:
3. In the opened Format Cells dialog, go to the Number tab, select Custom from the Category list, and in the Type box, enter: 0;""
4. Click OK to apply the changes. All negative numbers in the selected range will now be hidden, displaying empty cells where negative values existed.
This method is simple to implement and does not require any formula or VBA knowledge. Be aware that while hidden, negative values still exist in the cells and may affect totals or further calculations. Also, negative numbers will be hidden in both the display and printing of the sheet. If your data may include numbers with decimals, consider using a custom format such as 0.00;"" to maintain consistent appearance.
Hide negative numbers in Excel with Kutools for Excel
For users who have installed Kutools for Excel, hiding negative numbers can be streamlined with specialized utilities. Using the Select Specific Cells feature, you can quickly isolate negative values and then easily make them invisible by formatting the font color. This workflow is intuitive for users who prefer working with graphical tools over formulas or formatting codes.
After installing Kutools for Excel, follow these steps:
1. Select your data range of interest.
2. Click Kutools > Select > Select Specific Cells to open the selection dialog:
3. In the dialog, select Cell under Selection type, and choose Less than in Specific type. Enter 0 in the value field, as shown in the screenshot:
4. Confirm with OK or Apply; all negative number cells are selected simultaneously:
5. With the negative values still selected, go to the Home tab, and from the Font Color dropdown, choose a color that matches the cell's background (typically white).
Tips: The font color you choose should exactly match the cell background for best results – otherwise, hidden text may become visible if the background color changes.
6. All negative values in the selected range will now be visually hidden.
Download and free trial Kutools for Excel Now !
In case you need to reveal the numbers again, simply select the relevant cells and set the font color back to automatic or black. This method is especially helpful for those who regularly work with large data sets and need an efficient, click-based way to manage data display.
Hide or clear negative numbers using VBA macro
For more advanced or repetitive tasks, you can use a VBA macro to automatically hide or even clear negative values within a selected range. This approach is useful for users dealing with large datasets or when you need to automate the operation for reports delivered regularly. Below are two customizable VBA macros: one hides negative numbers by matching the font color to the background, and the other clears negative numbers (sets them to blank) entirely.
Advantages: Fully automated, flexible for batch operations, can be reused or adjusted for different criteria. Limitations: Macros require users must enable macros for them to run; beginners should be cautious and always back up data before applying VBA changes.
1. To add the macro, click Developer > Visual Basic (or press Alt + F11) to open the VBA editor. In the left pane, right-click your workbook, select Insert > Module, and paste the desired code below into the module window.
To hide negative numbers (by making them invisible):
Sub HideNegativeNumbers()
Dim WorkRng As Range
Dim Rng As Range
Dim xTitleId As String
Dim bgColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to hide negative numbers", xTitleId, WorkRng.Address, Type:=8)
bgColor = WorkRng.Cells(1, 1).Interior.Color
For Each Rng In WorkRng
If IsNumeric(Rng.Value) And Rng.Value < 0 Then
Rng.Font.Color = bgColor
End If
Next
End Sub2. Click the 
button in the VBA toolbar or press F5 to run. You'll be prompted to select a range — choose your target cells and proceed. The macro will set the font color for negative numbers to match the background of the first cell in the selection, thus hiding them.
Troubleshooting tips: If only part of the range gets "hidden" or background colors don’t match, ensure the selected range is uniformly colored, or manually set the background color to your preferred color before running the macro.
To clear negative numbers (set them as blank):
Sub ClearNegativeNumbers()
Dim WorkRng As Range
Dim Rng As Range
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to clear negative numbers", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If IsNumeric(Rng.Value) And Rng.Value < 0 Then
Rng.Value = ""
End If
Next
End Sub2. Run this macro as above; after selecting your target range, all negative values in that range will be deleted, leaving those cells blank.
These macros are best used when negative values are not needed for later calculation or auditing, and you require a permanent (or easily repeated) solution for formatting output reports or cleaning exported data.
Display blank for negative numbers using Excel IF formula
If you prefer using Excel formulas, an efficient way to visually hide negative numbers is by using the IF function. This approach is especially effective if you want to create a new column or table without negative values while keeping your original data intact. The IF function can return a blank cell for negative numbers and show the original value for non-negative numbers, which is helpful for printing, reporting, and data analysis scenarios where negative values are not to be displayed.
Typical use cases: financial dashboards, data exports, shared summaries, performance monitoring tables, and preparation of clean non-negative lists.
1. Suppose your values are in column A. In an adjacent cell (e.g., B1), enter the following formula:
=IF(A1<0,"",A1)2. Press Enter to confirm. The formula will display a blank when the value in A1 is negative, and otherwise show the original value. To apply this for an entire list, drag the formula down from B1 to cover your dataset, or copy and paste it as needed. Adjust cell references if your data starts from a different cell or column.
If your data includes decimals or you want to retain original formatting for positive values, you can use a formula such as =IF(A1<0,"",TEXT(A1,"0.00")) for two decimal places. Remember, this method creates a new column with the filtered data, so you may need to use the results in subsequent charts or calculations as needed.
Note: Because the IF formula creates a new dataset column with negative values hidden as blanks, cell formatting and formulas referencing these results will treat negative numbers as empty or zero (depending on additional formula usage), which may impact certain summary statistics or totals.
In summary, there are several approaches to hiding negative numbers in Excel, and each method has its unique advantages. Visual formatting solutions like Conditional Formatting or Format Cells are quick and preserve your original data. Kutools for Excel provides a convenient graphical workflow, suitable for frequent or large-scale use. VBA macros offer batch operations and automation but should be used with proper caution and backups. The IF formula solution is perfect for creating new views of your data without negative numbers, facilitating cleaner reports and analyses. When choosing an approach, consider whether you need a reversible display method or a more permanent removal, and ensure your method fits with how you intend to process, share, or further analyze your Excel worksheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in