Tag: excel options

How to Display or Hide Zero Values in Excel

This post will guide you how to display or hide zero (0) values in cells in Excel. How do I hide all zero values in a selected range of cells in Microsoft Worksheet.

Display or Hide Zero Values with Format Cells

Assuming that you have a list a data in range A1:C4, which contain numeric values. And you do not want to display zero values in this range of cells. How to achieve it. You can try to Format cells with custom type to achieve the result of displaying or hiding zero values in the selected range. Just do the following steps:

#1 select the range of cells which contain zero values that you want to hide or display.

hide or display zero values1

#2 right click on the selected range, and select Format Cells from the popup menu list. And the Format Cells dialog will open.

hide or display zero values2

#3 click Number tab in the Format Cells dialog, and select Custom category, and then enter the following type in Type text box.

0;-0;;@

hide or display zero values3

#4 click OK button. You will see that all zero values should be hidden in the selected range.

hide or display zero values4

 

Display or Hide Zero Values with Excel Options

You can also change the Excel Setting in the Excel Options to Display or Hide All zero values in the current worksheet. Do the following steps:

#1 click File Tab, and select Options. The Excel Options dialog will open.

hide or display zero values5

#2 click Advanced menu in the Excel Options dialog, and scroll down to Display options for this worksheet section, uncheck or check Show a zero in cells that have zero value option. (If unchecked, all zero values would be hidden.)

hide or display zero values6

#3 click OK button.

Video: Display or Hide Zero Values

 

How to Hide and Unhide Horizontal and Vertical Scroll Bar in Excel

This post will guide you how to hide or unhide horizontal and vertical scroll bar in your current worksheet in Excel. How do I control Display of the Scroll Bars in Microsoft Excel 2013/2016.

Hide and Unhide Horizontal and Vertical Scroll Bar with Excel Options

If you want to hide both horizontal and vertical scroll bar, or just only hide one in your worksheet, you can change the excel setting in Excel Options dialog box. Just do the following steps:

#1 click File tab, and select Options menu. the Excel Options dialog will open.

hide or unhide scroll bar1

#2 select Advanced category, and scroll down to the Display options for this workbook section, then uncheck show horizontal scroll bar and show vertical scroll bar check boxes to hide all scroll bar in your workbook.

hide or unhide scroll bar2

#3 click Ok button, you will see that all scroll bar will disappear in your workbook.
#4 if you want to unhide or show all scroll bar (horizontal bar and vertical bar), you just need to check those two check boxes in the Display options for this workbook section.

Hide and Unhide Horizontal and Vertical Scroll Bar with VBA Macro

You can also use an Excel VBA Macro to achieve the result of hiding or unhiding horizontal and vertical scroll bars quickly. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Hide Scroll Bars:

Private Sub Workbook_Activate()
 With ActiveWindow
 .DisplayHorizontalScrollBar = False
 .DisplayVerticalScrollBar = False
 End With
 End Sub

Unhide Scroll Bars:

Private Sub Workbook_Deactivate()
 With ActiveWindow
 .DisplayHorizontalScrollBar = True
 .DisplayVerticalScrollBar = True
 End With
 End Sub

#5 press F5 key in your keyboard, or click Run button in the VBA editor.

You will see that the horizontal and vertical scroll bar will be hidden or shown in your workbook.

video: Hide and Unhide Horizontal and Vertical Scroll Bar