Watch Video – Find out how to Disguise Zero Values in Excel + Find out how to Take away Zero Values

In case you like studying over watching a video, under is the whole written tutorial.

Generally in Excel, you could need to conceal zero values in your dataset and present these cells as blanks.

Suppose you have got a dataset as proven under and also you need to conceal the worth Zero in all these cells (or need to change it with one thing akin to a touch or the textual content ‘Not Obtainable’).

Data set that has zeroes in it

Data set that has zeroes in it

Whereas you are able to do this manually with a small dataset, you’ll want higher methods when working with massive datasets.

On this tutorial, I’ll present you a number of methods to cover zero values in Excel and one technique to pick out and take away all of the zero values from the dataset.

Necessary Word: Once you conceal a Zero in a cell utilizing the strategies proven on this tutorial, it’ll solely conceal the Zero and never take away it. Whereas the cells might look empty, these cells nonetheless comprise the 0s. And in case you utilize these cells (which have hidden zeroes) in any formulation, these can be used within the formulation.

Let’s get began and dive into every of those strategies!

Routinely Disguise Zero Worth in Cells

Excel has an inbuilt performance that means that you can robotically conceal all of the zero values for the complete worksheet.

All you need to do is uncheck a field in Excel choices, and the change can be utilized to the complete worksheet.

Suppose you have got the gross sales dataset as proven under and also you need to conceal all of the zero values and present a clean cells as a substitute.

Data set that has zeroes in it

Data set that has zeroes in it

Under are the steps to cover zeros from all of the cells in a workbook in Excel:

Open the workbook through which you need to conceal all of the zerosClick the File tabClick the File option in the ribbon in ExcelClick the File option in the ribbon in ExcelClick on on ChoicesClick on OptionsClick on OptionsWithin the Excel Choices dialog field that opens, click on on the ‘Superior’ choice within the left paneClick the Advanced option in the Left pane in Excel Options dialog boxClick the Advanced option in the Left pane in Excel Options dialog boxScroll right down to the part that claims ‘Show choice for this worksheet’, and choose the worksheet through which you need to conceal the zeros.Select the worksheet in which you want to hide zeroesSelect the worksheet in which you want to hide zeroesUncheck the ‘Present a zero in cells which have zero worth’ choiceUncheck the option - Show a zero in cells that have zero valueUncheck the option - Show a zero in cells that have zero valueClick on Okay

The above steps would immediately conceal zeros in all of the cells within the chosen worksheet.

This transformation can also be utilized to cells the place zero is a results of a formulation.

Keep in mind that this technique solely hides the Zero worth within the cells and doesn’t take away these. The Zero continues to be there, it’s simply hidden.

That is the most effective and quickest technique to cover zero values in Excel. However that is helpful solely if you wish to conceal zero values in the complete worksheet. In case you solely need this to cover zero values in a particular vary of cells, it’s higher to make use of different strategies coated subsequent on this tutorial.

Disguise Zero Worth in Cells utilizing Conditional Formatting

Whereas the above technique is probably the most handy one, it doesn’t permit you to conceal zeros solely in a particular vary (relatively it hides zero values in the complete worksheet).

In case you solely need to conceal zeros for a particular dataset (or a number of datasets), you should utilize conditional formatting.

With conditional formatting, you may apply a particular format based mostly on the worth within the cells. So, if the worth in some cells is Zero, you may merely program conditional formatting to cover it (and even spotlight it if you would like).

Suppose you have got a dataset as proven under and also you need to conceal zeros within the cells.

Data set that has zeroes in it

Data set that has zeroes in it

Under are the steps to cover zeros in Excel utilizing conditional formatting:

Choose the datasetClick the ‘House’ tabClick the home tab in the ribbonClick the home tab in the ribbonWithin the ‘Kinds’ group, click on on ‘Conditional Formatting’.Click on Conditional FormattingClick on Conditional FormattingPlace the cursor over the ‘Spotlight Cells Guidelines’ choices and within the choices that present up, click on on the ‘Equal to’ choice.Click on the Equal to option in Conditional FormattingClick on the Equal to option in Conditional FormattingWithin the ‘Equal To’ dialog field, enter ‘Zero’ within the left discipline.Enter 0 in the Equal to fieldEnter 0 in the Equal to fieldChoose the Format drop-down and click on on ‘Customized Format’.Click on Custom Formatting in the formatting options dialog boxClick on Custom Formatting in the formatting options dialog boxWithin the ‘Format Cells’ dialog field, choose the ‘Font’ tab.From the Colour drop-down, select the white colour.Select the white font color to hide the zeroes using conditional formattingSelect the white font color to hide the zeroes using conditional formattingClick on OK.Click on OK.

Also Read |  Instruments to Improve Trainer-Guardian Communication

The above steps change the font colour of the cells which have the zeros to white, which make these cells look clean.

This workaround works solely when you have got a white background in your worksheet. In case there may be another background-color within the cells, the white colour should still hold the zeros seen.

In case you need to actually conceal the zeros within the cells, the place the cells look clean it doesn’t matter what background colour is given to the cells, you should utilize the steps under.

Choose the datasetClick the ‘House’ tabClick the home tab in the ribbonClick the home tab in the ribbonWithin the ‘Kinds’ group, click on on ‘Conditional Formatting’.Click on Conditional FormattingClick on Conditional FormattingPlace the cursor over the ‘Spotlight Cells Guidelines’ choices and within the choices that present up, click on on the ‘Equal to’ choice.Within the ‘Equal To’ dialog field, enter Zero within the left discipline.Enter 0 in the Equal to fieldEnter 0 in the Equal to fieldChoose the Format drop-down and click on on Customized Format.Click on Custom Formatting in the formatting options dialog boxClick on Custom Formatting in the formatting options dialog boxWithin the ‘Format Cells’ dialog field, choose the ‘Quantity’ tab and click on on the ‘Customized’ choice within the left pane.Within the Customized format choice, enter the next within the Sort discipline: ;;; (three semi-colons with out house)Custom format to hide zeroes using conditional formattingCustom format to hide zeroes using conditional formattingClick on OK.Click on OK.

The above steps change the customized formatting of cells which have the worth Zero.

Once you use three semi-colons because the format, it hides the whole lot within the cell (be it numeric or textual content). And since this format is utilized solely to these cells which have the worth Zero in it, it solely hides the zeros and never the remainder of the dataset.

You can even use this technique to focus on these cells in a special colour (akin to purple or yellow). To do that, simply add one other step (after Step eight) the place you have to choose the Fill tab within the ‘Format cells’ dialog field after which choose the colour through which you need to spotlight the cells.

Each the strategies coated above additionally work when Zero is a results of a formulation in a cell.

Word: Each the strategy coated on this part solely conceal the zero values. It doesn’t take away these zeros. Which means in case you utilize these cells in calculations, all of the zeros can be used as nicely.

Disguise Zero Worth utilizing Customized Formatting

One other solution to conceal zero values from a dataset is by making a customized format that hides the worth in cells which have 0s, whereas another worth is displayed as anticipated.

Under are the steps to make use of a customized format to cover zeros in cells:

Choose the complete dataset for which you need to apply this format. If you would like this for the complete worksheet, choose all of the cellsWith the cells chosen, click on the House tabClick the home tab in the ribbonClick the home tab in the ribbonWithin the Cells group, click on the Format choiceClick the Format option in the ribbonClick the Format option in the ribbonFrom the drop-down choice, click on on Format Cells. It will open the Format cells dialog fieldClick on Format Cells option in the drop-downClick on Format Cells option in the drop-downWithin the Format Cells dialog field, choose the Quantity tabSelect the Number tab in the Format Cells dialog boxSelect the Number tab in the Format Cells dialog boxWithin the choice on the left pane, click on on the Customized choice.Click the Custom option in the Format Cells dialog boxClick the Custom option in the Format Cells dialog boxWithin the choices on the appropriate, enter the next format within the Sort discipline: Zero;-Zero;;@Enter the custom format to hide zeroesEnter the custom format to hide zeroesClick on OK

The above steps would apply a customized format to the chosen cells the place solely these cells which have the worth Zero are formatted to cover the worth, whereas the remainder of the cells show the info as anticipated.

How does this tradition format work?

In Excel, there are 4 sorts of information codecs that you just specify:

Constructive numbersNegative numbersZerosText

And for every of those information sorts, you may specify a format.

Generally, there’s a default format – Basic, which retains all these information sorts seen. However you have got the pliability of making a particular format for every of those information sorts.

Under is the customized format construction you have to observe in case you might be making use of a customized format to every of those information sorts:

;;;

Within the technique coated above, I’ve specified the format to:

Zero;-Zero;;@

The above format does the next:

Constructive numbers are proven as isNegative numbers are proven with a detrimental signZeros are hidden (as no format is specified)Textual content is proven as is

Professional Tip: If you would like detrimental numbers to indicate up as purple in your dataset, use the next customized format: Zero;[Red]Zero;Zero;@

Exchange Zeros with Sprint or Some Particular Textual content

In some instances, you could need to not simply conceal the zeros within the cells, however change these with one thing extra which means.

Also Read |  Calculating Shifting Common in Excel [Simple, Weighted, & Exponential]

For instance, you could need to take away the zeros and change it with a touch or a textual content akin to “Not Obtainable” or “Information But to Come”

That is once more made potential utilizing customized quantity formatting, the place you may specify what a cell ought to show as a substitute of a zero.

Under are the steps to transform a Zero to a touch in Excel:

Choose the complete dataset for which you need to apply this format. If you would like this for the complete worksheet, choose all of the cellsWith the cells chosen, click on the House tabClick the home tab in the ribbonClick the home tab in the ribbonWithin the Cells group, click on the Format choiceClick the Format option in the ribbonClick the Format option in the ribbonFrom the drop-down choice, click on on Format Cells. It will open the Format cells dialog fieldClick on Format Cells option in the drop-downClick on Format Cells option in the drop-downWithin the Format Cells dialog field, choose the Quantity tabSelect the Number tab in the Format Cells dialog boxSelect the Number tab in the Format Cells dialog boxWithin the choice on the left pane, click on on the Customized choice.Within the choices on the appropriate, enter the next format within the Sort discipline: Zero;-Zero;–;@Enter the format to replace zero with dashEnter the format to replace zero with dashClick on OK

The above steps would hold all the opposite cells as is and alter the cells with the worth Zero to indicate a touch (–).

Word: On this instance, I’ve chosen to switch the zero with a touch, however you should utilize any textual content as nicely. For instance, if you wish to present the NA within the cells, you should utilize the next format: Zero;-Zero;”NA”;@

Professional Tip: You can even specify a couple of textual content colours in customized quantity formatting. For instance, if you would like the cells with zeros to indicate the textual content NA in purple colour, you should utilize the next format: Zero;-Zero;[Red]”NA”;@

Disguise Zero Values in Pivot Tables

There could be two situations the place a Pivot Desk reveals the worth as Zero:

The supply information cells which might be summarized within the Pivot Desk has Zero valuesThe supply information cells which might be summarized within the Pivot Desk are blanks and the Pivot desk has been formatted to indicate the empty cells as zero

Let’s see the best way to conceal the zeros within the Pivot desk in every situation:

When Supply Information cells have 0s

Under is a pattern dataset that I’ve used to create a Pivot desk. Word that the Amount/Income/Revenue numbers for all of the data for ‘West’ are Zero.

Pivot Table Data which has 0 in the records

Pivot Table Data which has 0 in the records

The Pivot desk created utilizing this information set seems to be as proven under:

Pivot Table that has 0

Pivot Table that has 0

Since there have been 0s within the dataset for all of the data for West area, you see a Zero within the Pivot desk for west right here.

If you wish to conceal zero on this Pivot Desk, you should utilize conditional formatting to do that.

Nevertheless, conditional formatting within the Pivot Desk in Excel works slightly totally different than common information.

Listed here are the steps to use conditional formatting to Pivot Desk to cover zeros:

Choose any cell within the column that has 0s within the Pivot Desk abstract (Don’t choose all of the cells, however just one cell)Click on the House tabIn the ‘Kinds’ group, click on on ‘Conditional Formatting’.Click on Conditional FormattingClick on Conditional FormattingPlace the cursor over the ‘Spotlight Cells Guidelines’ choices and within the choices that present up, click on on the ‘Equal to’ choice.Click on the Equal to option in Conditional FormattingClick on the Equal to option in Conditional FormattingWithin the ‘Equal To’ dialog field, enter Zero within the left discipline.Enter 0 in the Equal to fieldEnter 0 in the Equal to fieldChoose the Format drop-down and click on on Customized Format.Click on Custom Formatting in the formatting options dialog boxClick on Custom Formatting in the formatting options dialog boxWithin the ‘Format Cells’ dialog field, choose the Quantity tab and click on on the ‘Customized’ choice within the left pane.Within the Customized format choice, enter the next within the Sort discipline: ;;; (three semi-colons with out house)Custom format to hide zeroes using conditional formattingCustom format to hide zeroes using conditional formattingClick on OK. You will notice small Formatting Choices icon on the proper of the chosen cell.Click on on this ‘Formatting Choices’ iconFormatting Option icon when conditional formatting is applied to Pivot TableFormatting Option icon when conditional formatting is applied to Pivot TableWithin the choices that develop into obtainable, select – All cells displaying “Sum of Income” values for “Area”.Select All cells showing Sum of Revenue values for Region optionSelect All cells showing Sum of Revenue values for Region option

The above steps conceal the zero within the Pivot Desk in such a method that should you change the Pivot Desk construction (by including extra rows/columns headers to it, the zeros will proceed to be hidden).

If you wish to study extra about how this works, here’s a detailed tutorial on the appropriate solution to apply conditional formatting to Pivot Tables.

When Supply Information cells have empty cells

Under is an instance the place I’ve a dataset that has empty cells for all of the data for West.

Pivot Table where there are blank cells in source data

Pivot Table where there are blank cells in source data

If I create a Pivot Desk utilizing this information, I’ll get the consequence as proven under.

Also Read |  What Is Edcamp? An Overview For Lecturers

Pivot table when there are blank cells in source data

Pivot table when there are blank cells in source data

By default, the Pivot Desk reveals you empty cells when all of the cells within the supply information are clean. However in case you continue to see a Zero on this case, observe the under steps to cover the zero and present clean cells:

Proper-click on any of the cells within the Pivot TableClick on ‘Pivot Desk Choices’Right click on any cell in Pivot Table and then click on Pivot Table optionsRight click on any cell in Pivot Table and then click on Pivot Table optionsWithin the ‘PivotTable Choices’ dialog field, click on on the ‘Format & Format’ tabIn the Format choices, examine the choices and ‘For empty cells present:’ and go away it clean.Check the option for empty cells show and keep it blankCheck the option for empty cells show and keep it blankClick on OK.

The above steps would conceal the zeros within the Pivot Desk and present a clean cell as a substitute.

In case you need the Pivot Desk to indicate one thing as a substitute of the Zero, you may specify that in step four. For instance, if you wish to present the textual content – ‘Information not Obtainable’, as a substitute of the Zero, sort this textual content within the discipline (as proven under).

Show Data Not Available in Pivot Table instead of 0

Show Data Not Available in Pivot Table instead of 0

Discover and Take away Zeros in Excel

In all of the strategies above, I’ve proven you methods to cover the Zero worth within the cell, however the worth nonetheless stays within the cell.

In case you need to take away the zero values from the dataset (which would go away you with clean cells), use the under steps:

Choose the dataset (or the complete worksheet)Click on the House tabIn the ‘Modifying’ group, click on on ‘Discover & Choose’Within the choices which might be proven within the drop-down, click on on ‘Exchange’Click on Replace in Excel RibbonClick on Replace in Excel RibbonWithin the ‘Discover and Exchange’ dialog field, make the next modifications:Within the ‘Discover what’ discipline, enter 0In the ‘Exchange with’ discipline, enter nothing (go away it empty)Click on on Choices button and examine the ‘Match complete cell contents’ choiceFind and Replace options to replace 0 with blankFind and Replace options to replace 0 with blankClick on on Exchange All

The above steps would immediately change all of the cells that had zero worth with a clean.

In case you don’t need to take away the zeros instantly and first choose the cells, use the next steps:

Choose the dataset (or the complete worksheet)Click on the House tabIn the ‘Modifying’ group, click on on ‘Discover & Choose’Within the choices which might be proven within the drop-down, click on on ‘Exchange’Within the ‘Discover and Exchange’ dialog field, make the next modifications:Within the ‘Discover what’ discipline, enter 0In the ‘Exchange with’ discipline, enter nothing (go away it empty)Click on on Choices button and examine the ‘Match complete cell contents’ choiceFind and Replace options to replace 0 with blankFind and Replace options to replace 0 with blankClick on on the ‘Discover All’ button. This will provide you with an inventory of all of the cells which have Zero in itClick on Find All to find all cells with 0 in itClick on Find All to find all cells with 0 in itPress Management-A (maintain the management key and press A). It will choose all of the cells which have Zero in it.Find and Select all cells with 0 in itFind and Select all cells with 0 in it

Now, that you’ve all of the cells with Zero chosen, you may delete these, change these with one thing else, or change the colour and spotlight these.

Hiding the Zeros Vs. Eradicating The Zeros

When treating the cells with 0s in it, you have to know the distinction between hiding these and eradicating these.

Once you conceal the zero worth in a cell, it’s not seen to you, nevertheless it nonetheless stays within the cell. Which means in case you have a dataset and you utilize it for calculation, that Zero worth can even be used within the calculation.

Quite the opposite, when you have got a clean cell, utilizing it in formulation might imply that Excel robotically ignores these cells (relying on which formulation you’re utilizing).

For instance, under is what I get once I use the AVERAGE formulation to get the typical for 2 columns (the result’s in row 14). Column A has cells the place the Zero worth has been hidden (however continues to be there within the cell) and Column B has cells the place the Zero worth has been eliminated.

Difference between hiding zero values and removing zero values

Difference between hiding zero values and removing zero values

You’ll be able to see the distinction within the consequence although the dataset for each datasets seems to be the identical.

This occurs because the AVERAGE formulation ignores clean cells in column B, nevertheless it makes use of those in Column A (as those in column A nonetheless has the worth Zero in it).

You may additionally like the next Excel tutorials: