• Skip to main content
  • Skip to primary sidebar

Technology Simplified.

How to Replace Zeros With Blank, Dash or Text in Excel

By: Waseem Patwegar | May 31, 2021

Sometimes the presence of zeros in Excel data field can make histograms and other type of graphs look inaccurate. You will find below the steps to replace zeros with blank, Dash or Text in Excel.

Replace Zeros With Blank, Dash or Text in Excel

How to Replace Zeros With Blank in Excel

Before going ahead with the steps to replace zeros with blank in Excel, you need to understand that replacing zeros with blank amounts to removing the data from cells with zero values.

When you remove zeros in Excel, all the cells containing zeros will lose their data and they will be treated as empty cells having no data in them.

If you replace zeros with Dash (-) or a Text in Excel, you may sometimes encounter errors, if cells with Dash or Text Field are referenced in certain formulas.

With this understanding, let us go ahead and take a look at the steps to replace zeros with blank, dash or text field in Excel.

1. Replace Zeros With Blank in Excel

Follow the steps below to replace zeros with blanks in Excel.

1. Select the Entire Data in which you want to replace Zeros with blank cells.

2. Click on the Home tab > click on Find & Select in ‘Editing’ section and select the Replace option in the drop-down menu.

Find and Replace Option in Excel

3. In ‘Find and Replace’ dialog box, enter 0 in ‘Find what’ Field > leave the ‘Replace with’ field empty (enter nothing in it) and click on Options .

Open Find and Replace Option

4. In the expanded ‘Find and Replace’ box, select Match entire cell contents option and click on Replace All button.

Replace All Zeros in Excel With Blank

Once you click on ‘Replace All’ button, all the cells in your data having zeros will immediately become blank.

2. Replace Zeros with Dash in Excel

Another way of dealing with zero values in Excel is to replace zeros with dash (-). This can be achieved using custom number formatting option as available in Excel.

1. Select the Entire Data in which you want to replace zeros with dash.

2. Click on the Home tab > select Format option in ‘Cells’ group > click on Format Cells… in the drop-down menu.

Format Cells Option in Excel

3. In Format Cells dialog box, click on the Number tab and select Custom option in left-pane. In the right-pane, enter 0;-0;”–” in the ‘Type’ field.

Replace Zeros with Dash in Excel

4. Click on OK to save and implement this change.

Once you click on OK, Cells with 0 values will be showing a dash (–). Also, if you enter zero in any cell, it will be replaced with Dash (-).

3. Replace Zeros with Text in Excel

You can also replace zeros with any text field in Excel. For example, you can replace zeros with ‘NA’, ‘Not Available’ or any other text.

1. Select the Entire Data in which you want to replace zeros with Text.

3. In Format Cells dialog box, click on the Number tab and select Custom option in left-pane. In the right-pane, enter 0;-0;[Red]”NA” in the ‘Type’ field.

Replace Zeros with Text in Excel

Once you click on OK, all the Cells with 0 values will be showing the text ‘NA’ in red colour.

Note: If you want to show ‘NA’ in regular colour, simply type 0;-0;”NA” .

4. Find Zeros in Excel

In case you do not want to remove the zeros right away, you can first find zeros in the data field, select all zeros and deal with them as required.

1. Select the Entire Data having Zero values

4. In the expanded ‘Find and Replace’ box, select Match entire cell contents option and click on Find All  button.

Find All Zeros in Excel

5. Once you click on Find All, the cells with zero values will become highlighted.

6. Hold the Control Key and Press the A Key to select all the cells that have 0 in them.

Now, that you have selected all the cells having 0 values, you can delete the zeros, replace zeros with any character or highlight cells having zeros.

  • How to Find Duplicates in Excel Using Conditional Formatting
  • How to Create Pivot Table in Excel
  • Kutools for Excel
  • Kutools for Outlook
  • Kutools for Word
  • EULA End User License Agreement
  • Setup Made Simple Easily Install or Uninstall
  • Get 4 Sotware Package
  • 60-Day Refund 60-Day Risk-Free Guarantee
  • Tips & Tricks for Excel (3000+)
  • Tips & Tricks for Outlook (1200+)
  • Tips & Tricks for Word (300+)
  • Excel Functions (498)
  • Excel Formulas (350)
  • Excel Charts
  • Outlook Tutorials
  • About Us Our Team

Feature Tutorials

  • Search Search more
  • Retrieve License Lost License?
  • Report a Bug Bug Report
  • Forum Post in Forum

How to change – (dashes) to 0 (zero) in Excel?

Supposing you have a range of data mixed with dashes, and now you want to change all dashes cells to zeros in Excel, what would you do? You can achieve this with the following methods we provide in this article.

Change – to 0 with Find and Replace in Excel Change – to 0 with Kutools for Excel in Excel

Change – to 0 with Find and Replace in Excel

In this section, you can change all dashes to zeros in selected range with the Find and Replace command in Excel.

1. Select the range with the dashes cells you want to replace with zeros.

2. Press Ctrl + H keys simultaneously to open the Find and Replace dialog box. In this dialog box, under the Replace tab, enter – and 0 to the Find what and Replaces with boxes separately, Then click the Options button. See screenshot:

change to 0 excel

3. In the expanded section, check the Match entire cell contents box, and click the Replace All button.

change to 0 excel

4. Click the OK button in the following popping up dialog box.

change to 0 excel

5. Close the Find and Replace dialog box.

Now all dashes in cells are replaced with zeros. See screenshots:

change to 0 excel

Change – to 0 with Kutools for Excel in Excel

With the Select Specific Cells utility of Kutools for Excel , you can quickly select all cells which contain dashes and then change all dashes to zeros with shortcuts.

Before applying Kutools for Excel , please download and install it firstly .

1. Select the range with the dashes cells you want to change to 0. And click Kutools > Select > Select Specific Cells .

change to 0 excel

2. In the Select Specific Cells dialog box, select the Cell option in the Selection typ e section, select Equals in the Specific type drop-down list, and enter – to the box. Finally click the OK button. See screenshot:

change to 0 excel

3. Now all cells with dashes are selected immediately. Then enter the number 0 directly, and press the Ctrl + Enter keys simultaneously.

Then all selected cell contents are changed to 0 at once.

  If you want to have a free trial (30-day) of this utility, please click to download it , and then go to apply the operation according above steps.

Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365  |  Available in 44 Languages  |  Easy to Uninstall Completely

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!

change to 0 excel

  • Ablebits blog
  • Excel formatting

How to add leading zeros in Excel

Svetlana Cheusheva

This tutorial shows different ways to add leading zeros in Excel: how to keep zeros as you type, show leading zeros in cells, remove or hide zeros.

If you use Excel not only to calculate numbers, but also maintain records such as zip codes, security numbers or employee ids, you may need to keep leading zeros in cells. However if you try typing a zip code like "00123" in a cell, Excel will immediately truncate it to "123".

The point is that Microsoft Excel treats postal codes, phone numbers and other similar entries as numbers, applies the General or Number format to them, and automatically removes preceding zeroes. Luckily, Excel also provides the means to keep leading zeros in cells, and further on in this tutorial you will find a handful of ways to do it.

How to keep leading zeros in Excel as you type

For starters, let's see how you can put 0 in front of a number in Excel, for example type 01 in a cell. For this, simply change the cell format to Text :

  • Select the cell(s) where you want to prefix numbers with 0.
  • Go to the Home tab > Number group, and select Text in the Number Format box.

Type numbers with leading zeros in Excel.

Another way to keep leading zeros in Excel is to prefix a number with an apostrophe ('). For example, instead of typing 01, type '01. In this case, you don't need to change the cell's format.

How to show leading zeroes in Excel with custom number format

To display leading zeroes, apply a custom number format by performing these steps:

  • Select a cell(s) where you want to show leading zeros, and press Ctrl+1 to open the Format Cells dialog.
  • Under Category , select Custom .
  • Click OK to save the changes.

Show leading zeroes by applying a custom number format.

By using Excel custom numbers formats, you can add leading zeros to create fixed-length numbers, like in the above example, and variable-length numbers. It all boils down to which placeholder you use in the format code:

  • 0 - displays extra zeros
  • # - does not display extra zeros

For example, if you apply the 000# format to some cell, any number you type in that cell will have up to 3 leading zeros.

Your custom number formats can also include spaces, hyphens, parentheses, etc. The detailed explanation can be found here: Custom Excel number format .

The following spreadsheet gives a few more examples of custom formats that can show leading zeros in Excel.

And the following format codes can be used to display numbers in special formats such us zip codes, phone numbers, credit card numbers, and social security numbers.

Special formats for postal codes, telephone numbers and social security numbers

How to add leading zeros in Excel with the TEXT function

While a custom number format shows zero in front of a number without actually changing the underlying value, the Excel TEXT function pads numbers with zeros by "physically" inserting leading zeros in cells.

To add leading zeros with a TEXT( value , format_text ) formula, you use the same format codes as in custom number formats. However, the result of the TEXT function is always a text string, even if it looks much like a number.

For example, to insert 0 before a value in cell A2, use this formula:

=TEXT(A2, "0#")

To create a zero-prefixed string of a fixed length, say a 5-character string, use this one:

=TEXT(A2, "000000")

Please pay attention that the TEXT function requires enclosing the format codes in quotation marks. And this is how the results will look like in Excel:

For more information about Text formulas, please see How to use the TEXT function in Excel .

How to add leading zeros to text strings

In the previous examples, you learned how to add zero before a number in Excel. But what if you need to put zero(s) in front of a text string like 0A102? In that case, neither TEXT nor custom format will work because they deal with numeric values only.

If the value to be padded with zero contains letters or other text characters, use one of the following formulas, which offer a universal solution applicable to both numbers and text strings .

Formula 1. Add leading zeros using the RIGHT function

The easiest way to put leading zeros before text strings in Excel is using the RIGHT function:

  • "0000" is the maximum number of zeros you want to add. For example, to add 2 zeros, you type "00".
  • Cell is a reference to the cell containing the original value.
  • String_length is how many characters the resulting string should contain.

For example, to make a zero-prefixed 6-character string based on a value in cell A2, use this formula:

=RIGHT("000000"&A2, 6)

Add leading zeros using the RIGHT function.

In the above example, the maximum number of zeros equals the total string length (6 characters), and therefore all of the resulting strings are 6-character long (fixed length). If applied to a blank cell, the formula would return a string consisting of 6 zeros.

Depending on your business logic, you can supply different numbers of zeros and total characters, for example:

=RIGHT("00"&A2, 6)

Add leading zeros to create variable-length strings.

Formula 2. Pad leading zeros using the REPT and LEN functions

Another way to insert leading zeros before a text string in Excel is using this combination of REPT and LEN functions:

For example, to add leading zeroes to the value in A2 to create a 6-character string, this formula goes as follows:

=REPT(0, 6-LEN(A2))&A2

How this formula works:

Knowing that the REPT function repeats a given character a specified number of times, and LEN returns the total length of the string, the formula's logic is easy to understand:

  • LEN(A2) gets the total number of characters in cell A2.
  • REPT(0, 6-LEN(A2)) adds the required number of zeros. To calculate how many zeros should be added, you subtract the length of the string in A2 from the maximum number of zeros.
  • Finally, you concatenate zeros with the A2 value, and get the following result:

Pad leading zeros using the REPT and LEN functions.

How to add a fixed number of preceding zeros

To prefix all values in a column (numbers or text strings) with a certain number of zeros, use the CONCATENATE function, or the CONCAT function in Excel 365 - 2019, or the ampersand operator .

For example, to put 0 before a number in cell A2, use one of these formulas:

=CONCATENATE(0,A2)

Add a fixed number of preceding zeros in Excel.

In the same manner, you can insert 2 leading zeros (00), 3 zeros (000) or as many zeros as you want before numbers and text strings.

How to remove leading zeros in Excel

The method you use to remove leading zeros in Excel depends on how those zeros were added:

  • If preceding zeroes were added with a custom number format (zeros are visible in a cell, but not in the formula bar), apply another custom format or revert back General as shown here .
  • If zeros were typed or otherwise entered in cells formatted as Text (a small green triangle is displayed in the top-left corner of the cell), convert text to number .
  • If leading zeroes were added by using a formula (the formula appears in the formula bar when the cell is selected), use the VALUE function to remove them.

Different ways to add  leading zeros in Excel

Remove leading zeros by changing the cell format

Remove leading zeros by applying the General format.

Remove leading zeros by converting text to number

Remove leading zeros by converting text to number.

Remove leading zeros by using a formula

In case a preceding zero(s) is added with a formula, use another formula to remove it. The zero-removing formula is as simple as:

Where A2 is the cell from which you want to remove preceding zeros.

This method can also be used to get rid of zeros typed directly in cells (like in the previous example) or imported to Excel from some external source. Overall, if you are dealing with a zero-prefixed string that represents a number, you can use the VALUE function to convert text to number and remove leading zeros along the way.

The following screenshot shows two formulas:

  • The Text formula in B2 adds zeros to the value in A2, and
  • The Value formula in C2 removes the leading zeros from the value in B2.

Excel formula to remove leading zeros

How to hide zeros in Excel

If you don't want to display zero values in your Excel sheet, you have the following two options:

Hide zero values across the entire sheet.

  • To hide zero values in certain cells, apply the following custom number format to those cells: #;#;;@

For this, select the cells where you want to hide zeros, click Ctrl+1 to open the Format Cells dialog, select Custom under Category , and type the above format code in the Type box.

Hide zero values in certain cells.

Add and remove zeros in Excel an easy way

Add/Remove Leading Zeros for Excel

As usual, we've strived to reduce the number of moves to an absolute minimum :)

To add leading zeros , here's what you do:

  • Select the target cells and run the Add/Remove Leading Zeros tool.
  • Specify the total number of characters that should be displayed.
  • Click Apply .

Adding leading zeros

To remove leading zeros , the steps are very much alike:

  • Select the cells with your numbers and run the add-in.
  • Specify how many characters should be displayed. To get the maximum number of significant digits in the selected range, click the Get Max Length

The add-in can add leading zeros to both numbers and strings:

  • For numbers , a custom number format is set, i.e. only a visual representation of a number is changed, not the underlying value.
  • Alpha-numeric strings are prefixed with leading zeros, i.e. zeroes are physically inserted in cells.

This is how you can add, remove and hide zeros in Excel. To better understand the techniques described in this tutorial, you are welcome to download the sample workbook. I thank you for reading and hope to see you on our blog next week!

Available downloads

You may also be interested in.

  • How to format cells in Excel
  • Custom Excel number format
  • How to create border in Excel
  • How to convert date to text using Excel TEXT function and no-formula ways
  • How to swap columns in Excel by drag-n-drop and other ways to move columns
  • How to subscript and superscript in Excel

Table of contents

Ablebits.com website logo

99 comments

change to 0 excel

For 12345_1, display 12345_001

change to 0 excel

Hi! Insert the characters "00" into the text as described in these instructions: How to add text or character to every cell in Excel . Try this formula:

=LEFT(A2, SEARCH("_", A2)) &"00"& RIGHT(A2, LEN(A2) - SEARCH("_", A2))

Or you can extract the number after the "_" character with the MID function and convert it to text in the desired format with the TEXT function . For example:

=LEFT(A2,SEARCH("_",A2))&TEXT(--MID(A2,SEARCH("_",A2)+1,10),"000")

I hope it’ll be helpful.

change to 0 excel

Could you please help me anyone to change the format .

How to do this in Excel- "00 ASDF 123 TO 00123 ASDF"

Thanks in advance

Hi! Split the text in any of the ways described in this guide: How to split text string in Excel by comma, space, character or mask . Then concatenate text strings in the order you want.

I don't want split the text. I want to rearrange word place in one cell.

For Example: EMPLOYEE FILE ; EMPLOYEE MARTIN 123

I want like this: EMPLOYEE FILE ; EMPLOYEE 123 MARTIN

please guide me

To rearrange words, you can at first split text into separate words.

for 1:2 Display 001:02 for 13:8 Display 013:08

Hi! These are not numbers, but text. Therefore, you cannot automatically add leading zeros using format cell.

change to 0 excel

Saya punya data C1b2 A1 A1b2c3

Mau diinsert 0 disetiap angka yg ada di tiap barisnya menjadi

C01b02 A01 A01b02c03

Apakah ada formula langsungnya? Terima kasih

I'm really sorry, looks like this is not possible with the standard Excel options.

Hi Sir, I want to remove only one leading zero from this number: 0004879658954. How can i do it sir? I appreciate your kind consideration. Thanks!

Hi! To remove the first character from the text, try the REPLACE function .

=REPLACE(A1,1,1,"")

Formula worked. Thanks a lot, Sir.

change to 0 excel

Hi, is it possible to add zeros between letters and numbers? From: LK1 LK3 LK10 LK12 LK100

I would like to get: LK001 LK003 LK010 LK012 LK100

Hello! You can extract characters from a text string starting from a certain position with the MID function . Then use the recommendations from the article above.

=LEFT(A1,2)&TEXT(--MID(A1,3,5),"000")

change to 0 excel

Once the original data has come into my excel CSV file, and excel has already dropped the leading zero(s), how can I restore the leading zeros back?

For example, if 00329384 comes in but excel displays 329384 For example, if 0329384 comes in but excel displays 329384

Even when I change the format to "text", the zero's are still gone. Is the original data with the leading zero's still contained in the excel file somewhere?

Hello! To preserve leading zeros, treat data as text. You cannot recover leading zeros in numbers.

change to 0 excel

This page helped me solve my work problem. Thanks so much.

change to 0 excel

Excelente information

Regards from Honduras CA-

change to 0 excel

Hi, I'm trying to create a formula that will turn a cell such as "3.5.12.5 Introduction" into "03.05.12.05" for a Table of Contents solution. The formula I have functions properly before the first decimal, but I'm having trouble altering it to apply to all.

=IF(ISNUMBER(VALUE(LEFT(B5,1))),IF(VALUE(LEFT(B5,FIND(".",B5)-1))>9,LEFT(B5,FIND(" ",B5)-1),"0"&LEFT(B5,FIND(" ",B5)-1)))

Any help would be appreciated! The solution below works in excel online but I need it to function in excel desktop/SharePoint- =TEXTJOIN(".",TRUE,IF(ISNUMBER(TEXTSPLIT(B5,{".";" "})+0),TEXT(TEXTSPLIT(B5,{".";" "}),"00"),""))

Hi! The result of the formula is "03.05.12.05". If this is not what you need, explain in detail what result you want to get.

change to 0 excel

Hi, I'm hoping to use a formula that doesn't use TEXTSPLIT. Thanks!

How can I define 1 decimal at most (ıf the number does not have any decimals, display should be a whole number). "#.#" does not give the format I want since if there is no decimals then it displays a dot after the whole number.

For "125.2" display "125.5" For "125,24" display "125.2" For "125" display "125" (not "125.")

Hello! To avoid showing the decimal separator in integers, use conditional formatting . Conditional formatting formula:

=A1=INT(A1)

Apply number format when condition is met: #,###

change to 0 excel

I wonder if anyone can help me. I am doing a project and I come across a brick wall. What I would like to do on my spreadsheet is to add a blank row above using a button (VBA) then I would want the cells in column A to automatically have a serial number in this format... ABCD001. When I click a button it will add the row above this and it will generate a value of ABCD002.

I'd appreciate if you could share your expertise please.

change to 0 excel

It was easy to locate what I wanted to do. The instructions were clear and the example formula showed exactly what to do. Thank you!

change to 0 excel

Hi, thanks for this article.

Do you have any suggestions on adding 0's before dates?

I usually receive them in the text format, so I split it up by the forward slash (/), add in the 0's manually by carefully sorting each column, and then concatenate them back together. Is there a better way?

Also, is there a way to convert the dates into text without them turning into their raw values?

Hello! Please check out this article to learn how to convert date to text. With the & operator, you can add a zero before the text date.

Thank you for your help! I am now using `=TEXT(CELL,"MM/DD/YYYY")` and it works like a charm.

I have a table A B C 01 01 01

If i concatenate it 111 is coming. But i need 010101. Can you tell me the process?

Hi! If "01" is written in the cell, then this is text. Use the & operator or the CONCATENATE function . You can write "01" in a cell with an apostrophe . Re-check also the paragraph above: How to add leading zeros in Excel with the TEXT function

change to 0 excel

ALIAS NAME 72019195 2093573 72010822 72014127 1191408 72010821 1191407 10021026 10021007 10021000 72016400 72016401 10019242 72013368 2030021 2030285 2030042 2030151 2030164 2030142 72000538 2060217 72016399 2060129 2060112 4033444 2060061 2060164 1110318 1191437 1150012

I want to add ''0" in the front for those 7 digit number. Not all

Any Formula pls

Post a comment

Dash for a zero in Excel

Dash for a zero in Excel

How to display a dash ( - ) instead of a zero in your Excel cells?

Don't replace manually your zeros with a dash in Excel ⛔

This remark is very important . If you replace manually each 0 with a dash, you will create confusion between the text and numeric values ​​in the same column ☹️

  • A dash is considered as text
  • 0 is a number

For example, in this document, because of the dashes, Excel considers that not all the cells have a numeric value .

The COUNT function counts the number of numeric values. This is why here, you can see that the dashes are not included in the result .

Dash is a text and not a numeric value by default in Excel

How to display a dash automatically instead of a zero in Excel?

To display a dash instead of the number 0, you have to customize the format of your numbers.

  • First, open the Format number dialog box
  • Go to the Custom Category

How to customize the display of the numbers in Excel?

Excel offers 4 displays for numbers, in this order

  • Display of positive numbers
  • Display of negative numbers
  • Number zero

Positive number;Negative number;0;Text

You also need to specify how the number will be displayed.

  • Use 0 to display all the 0 (important to keep the leading 0 )
  • Use # to display the numbers except the 0 if not necessary

For instance, if you have the number 123

  • The code #### ( 4 # ) will display 123 in your cell
  • The code 0000 ( 4 zeros ) displays the value 0123
  • And with the code ###0.00 the display will be 123.00 (no leading 0 but 0 for the decimals 😉)

Negative numbers

The display of negative numbers can be represented in red with the following code

#,##0.00; [Red] - #,##0.00

Or between parenthesis  the code

#, ##0.00; [Red](#,##0.00)

Negative numbers in red with parenthesis

Related articles

  • Highlight the min and max values automatically
  • Display negative number in parenthesis
  • Quizzes for Excel
  • Exercises Online for Excel
  • Start Excel with your custom format number

Replace the 0 by a dash in Excel

To change a zero to a dash, you have to play on the 3rd argument of the code of a number.

So, by adding just a dash between quotation marks in the 3rd parameter, all your 0s will be replaced by dashes.

#,##0.00; [Red] - #,##0.00 ;"-"

Format code to display dash for 0

Zeros are now displayed with a dash and the COUNT function returns 8 numeric values ​​for each column 😀

The zeros are displayed with a dash in Excel

Frédéric LE GUEN

Leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

MVP_logo_2017

Most Viewed Posts

  • Displaying Negative Numbers in Parentheses – Excel (271,925)
  • The 10 most useful Excel Functions in 2023 (199,232)
  • How to make automatic calendar in Excel (171,447)
  • Dash for a zero in Excel (139,666)
  • Convert YYYYMMDD to DD/MM/YYYY with Excel (124,678)
  • Date Format in Excel (120,413)
  • Excel Split Date and Time (98,401)
  • How to calculate VAT in Excel? (94,012)
  • Function XLOOKUP in Excel (88,268)
  • Exchange rate in Excel (87,454)
  • UNIQUE Function – Remove duplicates with formulas (73,697)
  • Convert Latitude and Longitude from Decimal to Degree, Minute, Seconde (69,159)
  • Comma or Semicolon in an Excel formula (64,634)
  • Add Emojis to your Excel charts, formulas (64,591)
  • How to Compare 2 Columns in Excel (59,209)

' src=

8 Ways to Show Zero as Blank in Excel

Do you want to hide the zero values in your data? This post is going to show you all the ways you can display zero values as blanks in Microsoft Excel.

A common technique used in Excel is to format zero values as blanks. This can help improve the readability and comprehensibility of the spreadsheet.

It’s difficult to quickly identify which cells contain zero values when you have a lot of data. When you signify zero values as blanks, it gives you a visual indication and makes it simpler to spot patterns in the data.

A zero value in your data might also mean that no data exists. If this is the case, then displaying a blank would be a better option.

Read on to see all the methods for displaying zeros as blanks in Excel. Get your copy of the example workbook used in this post to follow along.

Show Zero as Blank with Custom Format

The best method for showing your zero values as a blank is to use a custom number format. This will allow you to choose exactly how to display your positive, negative, and zero values.

change to 0 excel

Follow these steps to apply a custom format that shows zeros as blanks.

  • Select the range of values to which you want to apply a custom format.

change to 0 excel

  • Go to the Home tab in the ribbon.
  • Click on the Launch icon in the lower right or the Number section.

This will open the Format Cells dialog box which will allow you to adjust a variety of cell formatting options including any number format.

💡 Tip : You can also open the Format Cells menu by using the Ctrl + 1 keyboard shortcut.

change to 0 excel

  • Go to the Number tab in the Format Cells menu.
  • Select the Custom option in the Category section.
  • Enter the $#,##0;-$#,##0; format string into the Type field.
  • Press the OK button.

The format string used in the custom number format consists of three parts separated by a ; character.

The first part determines the format to use for positive numbers, the second part determines the format to use for negative numbers, and the third part determines the format used on zero values.

Notice in this format string the last part after the second ; character is empty? This results in the zero values showing as blanks!

change to 0 excel

This will result in the zeros of your select range appearing blank in the cells.

change to 0 excel

If you select any of these blank cells, you will notice the formula bar still shows the zero value. The cells will still contain their zero value, but the formatting causes them to display as blanks in the grid.

Show Zero as Blank with Excel Options

There is an Excel setting that will allow you to hide zeros for your entire sheet.

This way you don’t need to select a specific range to apply format, all the zero values in your sheet will be hidden and appear as blanks.

change to 0 excel

Follow these steps to hide zero values for an entire sheet.

  • Go to the File tab.
  • Select Options in the bottom left of the backstage area.

This will open the Excel Options menu which contains a variety of customizable settings for your Excel app.

  • Go to the Advanced tab in the Excel Options menu.
  • Scroll down to the Display options for this worksheet section.
  • Select the sheet on which you want to hide your zero values.
  • Uncheck the Show a zero in cells that have zero value option.

📝 Note : This option is checked by default for any new sheet in your workbook. You will have to update this setting for each sheet if you want hidden zeros across your entire workbook.

All your zero values are now hidden across the entire sheet!

If you want to show a few zeros in such a sheet, it is still possible. You can use this #,##0;-#,##0;0 custom format to override the setting on individual cells.

Show Zero as Blank with the TEXT Function

You can also use the same custom format strings with an Excel function.

The TEXT function allows you to format numbers as text and apply any special format.

change to 0 excel

The above formula will format the content of cell C3 based on the supplied format where zero values are hidden. The "$#,##0;-$#,##0;" format string is the exact same as the custom format solution.

The results from this formula will be text values, so this may not be the ideal solution depending on what you need to do with the formatted values. You won’t be able to use the results in any further numerical calculations since they are text.

Show Zero as Blank with the IF Function

You can also use the IF function to hide any zero values.

You can test if a cell has a zero value and show a blank when it does.

change to 0 excel

The above formula will test if the value in cell C3 is zero and return the empty string "" if it is. Otherwise, it will return the original value.

This will keep the non-zero values as numbers, but the blank cells will actually be text values.

Show Zero as Blank with Find and Replace

You might decide you want to remove the zero values from your data entirely.

This is possible to do with a simple find and replace. You can search for the zeros and then replace them with a blank. This way you will have truly blank cells instead of zeros.

Follow these steps to replace your zero values from any range.

  • Select the cells from which you want to remove zeros.
  • Press Ctrl + H to open the Find and Replace menu.

change to 0 excel

  • Add 0 to the Find what input.
  • Leave the Replace with input empty.

This will essentially remove all the zeros from your values because you will find all the 0 cells and replace them with nothing!

  • Check the option to Match entire cell contents .

This is a key part of the process. Matching the entire cell contents means you won’t inadvertently change a value such as 520 to 52 .

  • Press the Replace All button.

Excel will replace all the zero values in your selection with a blank.

Show Zero as Blank in Power Query

Power Query is an amazing tool in Excel for any sort of data import or transformation task.

You will easily be able to remove any zeros and replace them with blanks in your queries.

change to 0 excel

You will first need to get your data into the Power Query editor.

  • Select your dataset.
  • Go to the Data tab.
  • Press the From Table/Range command to import the data into Power Query.

This will open up the Power Query editor with your data.

change to 0 excel

  • Click on the column heading containing the zeros to replace.
  • Go to the Transform tab.
  • Press the Replace Values command.

change to 0 excel

This will open the Replace Values menu where you will be able to define what to find and what to replace it with.

  • Enter 0 in the Value To Find input.
  • Enter null in the Replace With input.

📝 Note : The null value is Power Query’s equivalent to a blank in Excel. It represents the absence of a value.

change to 0 excel

You will notice data now shows null values instead of any zeros. Power Query has performed this by automatically creating an M code formula like the above.

You can now load the data back into Excel. Go to the Home tab and click on Close and Load .

change to 0 excel

The data can be loaded into a table in a location of your choice and the null values will show as blanks.

📝 Note : This is a great option for any data that will be continually updated, as you can refresh the query and the results for the new data will appear! Go to the Data tab and use the Refresh All command to rerun the query.

Show Zero as Blank with VBA

VBA is a great way to automate most tasks in Excel.

You can use VBA to remove zeros as well in a similar fashion to the Find and Replace feature. But this way you won’t need to input any values to find, or update settings to get the job done. The code will take care of that!

You can create a macro that will loop through the selected range and identify any cells with a zero and remove them.

change to 0 excel

Follow these steps to open the visual basic editor.

  • Go to the Developer tab.
  • Select the Visual Basic command to open the editor.

You might not see the Developer tab, as it’s hidden by default. You can enable the Developer tab in your ribbon if you don’t see it already. You can also use the Alt + F11 keyboard shortcut to directly open the visual basic editor.

change to 0 excel

  • Go to the Insert menu of the visual basic editor.
  • Select the Module option.

change to 0 excel

This will create a new module where you can copy and paste the above code.

This code will loop through each cell in the selected range and check if its value is zero. If the value is zero then the cell content is cleared. This will remove the zero entirely and not just hide it from view.

Now you can select any range in the workbook and run the VBA code to remove the zeros!

Show Zero as Blank with Office Scripts

VBA isn’t the only way to automate things in Excel. You can also use Office Scripts if you’re using Excel online with a Microsoft 365 business plan.

change to 0 excel

Open your Excel workbook in the web browser and go to the Automate tab then select the New Script option.

change to 0 excel

This will open the Code Editor on the right side of the workbook. You can then copy and paste the above code into the Code Editor and press the Save script button.

This code will loop through the selected range and clear the content of any cell that contains a zero.

Select the range with zeros and press the Run button in the Code Editor and your zeros will be removed leaving you with blank cells in their place.

Conclusions

There are many reasons why you might want to hide the zeros in your Excel workbooks. Displaying your zeros as blanks can help visually declutter your data and improve readability.

Excel has several great ways to hide zeros. You can apply custom formats , use the Excel Options menu, or use functions such as TEXT and IF .

Another option might be to remove any zero values from your data. This can be achieved quickly with Find and Replace , VBA , or Office Scripts .

If you are importing or transforming your data on a frequent basis, then Power Query should be your go-to method and you will also be able to remove any zero values in your query.

Do you hide the zeros in your workbook? What methods do you use? Let me know in the comments below!

About the Author

John MacDougall

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

change to 0 excel

I’m John , and my goal is to help you Excel!

You’ll find a ton of awesome tips , tricks , tutorials , and templates here to help you save time and effort in your work.

  • Pivot Table Tips and Tricks You Need to Know
  • Everything You Need to Know About Excel Tables
  • The Complete Guide to Power Query
  • Introduction To Power Query M Code
  • The Complete List of Keyboard Shortcuts in Microsoft Excel
  • The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

change to 0 excel

Related Posts

10 Ways to Add Borders in Microsoft Excel

10 Ways to Add Borders in Microsoft Excel

Jan 29, 2024

If you wish to improve the visibility, readability, and organization of your...

10 Ways to Remove Borders in Microsoft Excel

10 Ways to Remove Borders in Microsoft Excel

Jan 19, 2024

Do you need to remove cell borders in your Excel sheet? Though cell borders in...

How to Use Conditional Format in Microsoft Excel

How to Use Conditional Format in Microsoft Excel

Jan 8, 2024

Suppose you've seen a colleague's or friend's spreadsheet that dynamically...

Get the Latest Microsoft Excel Tips

change to 0 excel

Follow us to stay up to date with the latest in Microsoft Excel!

Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

how to change 0's to -'s for a selected block of cells

I want to change the formatting on an excel sheet so that 0's will show up as dashes for only a specific block of cells on a sheet. Does anyone know how to do this?

Note that I do not want this formatting for the entire sheet, only for a certain block of cells.

  • microsoft-excel

chuck taylor's user avatar

6 Answers 6

If you only want to change the formatting, not the content, of the cells you should look at the Accounting format type, but with no symbol selected.

All cells with a value of 0 will be displayed as - while keeping the value 0 in the cell.

But Accounting does introduce some "special" spacing in to the cell. So instead I'd suggest using a custom format. Custom formats can be split in to up to four sections, divided by a semi-colon, meaning you can define a format as such:

So, if you happen to need a percentage to two decimal places, but replacement of zeros, you could do:

Where "-" will entirely replace all zero values and @ will display text as-is. Make sure you remember to prefix the second value (negative numbers) with a - symbol!

If you don't have a format string handy for the positive and negative entries, note that you can obtain the format string for any standard type by first selecting the desired type on the format page and then moving directly to the custom view.

To implement:

  • Select all the cells / rows / columns required.
  • Right-click and choose Format Cells...
  • Select Custom (or maybe Accounting)
  • Enter the format string required

DMA57361's user avatar

  • This is really close, but I am already displaying the numbers as percentages. Is there a way to get the zero replacement of the accounting format with the display properties of one of the other formats? –  chuck taylor Nov 24, 2010 at 17:05
  • Try swapping both instances of the word General with the format string that you require - if you need to find the format string just select the required format normally and then select custom, and it should give you the format string you need to then transplant in. –  DMA57361 Nov 24, 2010 at 17:07
  • Actually, I've just discovered it's simpler than this - updating my answer.... –  DMA57361 Nov 24, 2010 at 17:09
  • Highlight the Cells that are not formatted incorrectly
  • Then right click on those cells and choose "Format Cells"
  • Choose "Custom" from the Category Menu
  • In the "Type" field paste this code _($* "-"??_);_($* "-"??_);_($* "-"??_);_(@_)

The first set of parenthesis identify how you want positive values to be treated, the 2nd set of parenthesis identify how you want negative values to be formatted, the 3rd set of parenthesis identify how you want zero values to be formatted, and the last set of parenthesis identify how you want text values to be formatted. You need to address all 4 types of values in order for all the bases to be covered. If you do not want the zeros to be reflected as a dash then simply replace the dashes shown in the code above to be a 0 or 0.00 or however you would like them to appear. Hope this helps!

Guest's user avatar

  • Select a block of cells
  • Edit > Replace...
  • In the 'Find what' field, type 0
  • In the 'Replace with' filed, type '-'
  • Hit 'Replace All'

gulbrandr's user avatar

  • That would only do the change once, I am looking for a setting to apply to part of the worksheet that will remain consistent even if I change the values in the cells. –  chuck taylor Nov 24, 2010 at 17:06

You could use conditional formatting to do this. If you use Office 2007, on the Home tab, there is the conditional formatting button in the styles section. From the dropdown you select "Manage Rules". From there, add a new rule and select "Format only cells that contain. Change the targeting to "equal to 0" and you can then setup the formatting any way you like. If you want a bunch of dashes, just go to custom and type in whatever symbols you want it to display instead of 0. Finally, change the Applies to for the column that has the percentages (C column: =$C:$C).

You could also use an IF statement, but that takes up a lot more space and only works when you calculate the percentage.

Hope this helps

Doltknuckle's user avatar

I had a problem where i had zero's with a leading apostrophe (sourced from another reporting system) shown like this '0 (I haven't used quote marks to avoid confusion). these problem '0s where mixed up with thousands of other normal numbers so fixing one by one would have been extremely tedious. I wanted this to show as the number zero i.e. 0 (again no quote marks) so that an NPV formula would see the 0 as a number and not text. Doing a replace doesn't work because the apostrophe is a special character in Excel which makes everything following text, irrespective of the cell format. There are two solutions: 1) Just type the number 1 in a cell and then do a copy>paste special>multiply against all the numbers including the problem '0 and the '0 becomes a 0. 2) Do a goto>special with all the cells you wish to look at already highlighted (otherwise it will find headings etc) and then select 'constant' and then 'text' and then OK. Then immediately type 0 then hit CTL+Enter simultaneously (instead of just enter) and all the cells selected by goto as text will become 0.

Duncan's user avatar

  • 1 Welcome. You have some good information here, but the way it's written it may seem like it doesn't answer the question that was asked. I assume you are talking about applying a method similar to what you used to this problem (changing 0 to -). To make this a good answer, edit it so that it is a direct solution to the problem asked rather than an analogous one. You don't need to refer to your own problem, just write the solution to this one. –  fixer1234 Nov 13, 2014 at 5:59
  • Select the cells you want to change.
  • "Number" Tab => "Custom"
  • #;#;- (Enter in the "Type" bar)
  • OK (or enter)

TSR Advancement LLP's user avatar

  • For step 2, you can make it clear your referring to a keyboard shortcut using the kbd tag e.g. <kbd>ctrl</kbd>+<kbd>1</kbd> (see: developer.mozilla.org/en-US/docs/Web/HTML/Element/kbd ) Also consider stating what the keyboard shortcut does ("Cell Formatting") as it may not be the same as the user's keyboard layout. –  fswings Jun 30, 2020 at 12:43

You must log in to answer this question.

Not the answer you're looking for browse other questions tagged microsoft-excel formatting ..

  • The Overflow Blog
  • Who owns this tool? You need a software component catalog
  • Featured on Meta
  • Upcoming privacy updates: removal of the Activity data section and Google...
  • Changing how community leadership works on Stack Exchange: a proposal and...

Hot Network Questions

  • Can a judge assign a murder victim's paid counsel to represent her murderer?
  • "They don’t speak it so much my side of the park." Which park? Which side is which?
  • Is it true that no philosopher disagrees that everything exists?
  • Function of capacitor on the output of a bridge rectifier
  • Would a giant ball on earth roll towards the poles?
  • Why didn't NASA launch communications relay satellites outside the asteroid belt to communicate easily with interstellar satellite?
  • List of built-in functions written in Wolfram Language
  • How can I control curved edges between vertices?
  • What is the etiquette for applying for multiple PhDs?
  • What's the earliest result (outside of logic) that cannot be proven constructively?
  • Pythagorean pentagons
  • Word request: Sensory words
  • Difference between cp and cp -rf
  • What is the Circular Dependency in signing a chain of unconfirmed transactions?
  • What's wrong with this derivation of the volume of a hemisphere?
  • Can I raise my ceiling in my shed?
  • Is there a word for an object orbiting a brown dwarf?
  • What happens when the runway is unusable at an isolated aerodrome?
  • How can I understand neutrino mixing and the difference between flavour and mass eigenstates?
  • What are the differences between a synagogue and a temple?
  • Understanding the joke, "Make an 'ell, I say" (from The Crux)
  • Are flights in fast jet streams more dangerous?
  • Are Feroli (italy) and Olimp (Greece) legitimate brands of extra virgin olive oil?
  • Hydrazine and liquid fluorine as a semi-cryogenic storable lunar space propellant

change to 0 excel

Convert negative numbers to zero

Related functions .

Excel formula: Convert negative numbers to zero

To convert negative numbers to zero without affecting positive numbers, you can use a formula based on the MAX function . In the example shown, the formula in D5, copied down, is:

If the number in column B is negative, MAX returns zero. Otherwise, MAX returns the original number.

Generic formula

Explanation .

In this example, the goal is to convert negative numbers in column B to zero and leave positive numbers unchanged. Essentially, we want to force negative numbers to zero.

With the MAX function

The MAX function provides an elegant solution:

This formula takes advantage of the fact that the MAX function works fine with small sets of data — even two values. If the value in B5 is a positive number, MAX will return the number as-is, since positive numbers are always greater than zero. However, if the number is B5 is negative, MAX will return zero, since 0 is greater than any negative number.

With the IF function

This problem can also be solved with the IF function  like this:

This formula returns exactly the same result. If B5 is less than zero, IF returns zero. Otherwise, IF returns the original number. Both formulas work fine. The MAX formula above is just an elegant way to replace the slightly more complex IF formula. However, if you need to customize the results returned after a logical test , the IF function is more flexible.

Related formulas

Excel formula: Change negative numbers to positive

  • Change negative numbers to positive

Excel formula: Count cells that contain negative numbers

  • Count cells that contain negative numbers

Excel formula: Cap percentage at specific amount

  • Cap percentage at specific amount

Excel formula: Convert numbers to 1 or 0

  • Convert numbers to 1 or 0

Related functions

Excel MAX function

  • MAX Function

The Excel MAX function returns the largest numeric value in the data provided. MAX ignores empty cells, the logical values TRUE and FALSE, and text values.

Dave Bruns Profile Picture

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

  • Replace ugly IFs with MAX or MIN

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Excel foundational video course

Help us improve Exceljet

Your email address is private and not shared.

Statology

Statistics Made Easy

How to Replace Blank Cells with Zero in Excel

The easiest way to replace blank cells with zeros in Excel is to use the Go To Special function.

The following example shows how to use this function in practice.

Example: Replace Blank Cells with Zero in Excel

Suppose we have the following dataset that shows the points scored by various basketball teams:

change to 0 excel

Suppose we would like to replace the blank cells in the Points column with zeros.

To do so, type Ctrl+G to bring up the Go To window:

change to 0 excel

Then click the Special button in the bottom left corner of the Go To window.

In the new window that appears, select Blanks and then click OK :

replace blanks with zero in Excel

All of the blank values in the Points column will automatically be highlighted:

change to 0 excel

Lastly, type in the value 0 in the formula bar and press Ctrl+Enter .

change to 0 excel

Each of the blank cells in the Points column will automatically be replaced with zeros.

Note : It’s important that you press Ctrl+Enter after typing the zero so that every blank cell will be filled in with a zero instead of just the first blank cell selected.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Replace #N/A Values in Excel How to Interpolate Missing Values in Excel How to Count Duplicates in Excel

' src=

Published by Zach

Leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

Spreadsheet Planet

Fill Blank Cells with 0 in Excel

Sometimes you will have a data set that has blank cells in it that you need to fill with a 0.

I have often found this to be a case with some databases where when you export the data, the cells that contain zero are often left blank. This could also be the case where data is manually entered, and 0’s are not entered, leaving the cell blank.

While you always have an option to manually select and enter 0 in a blank cell, doing so for a large data set would be quite tedious and inefficient.

In this tutorial, I’m going to show you three simple methods you can use to fill blank cells with 0 (zero) in Excel .

Table of Contents

Fill Empty Cells with 0 in Excel

In this section, I will show you three ways to quickly fill all the blank cells with zero in Excel.

Two of these methods (‘Find & Replace’ and ‘Go To Special’) use the in-built Excel functionalities, and the third method uses a simple VBA code.

Using the Find and Replace to Find Blank Cells and Replace it with 0

Let me first show you the best way to fill blank cells with 0 in Excel – using the Find and Replace functionality.

With Find and Replace, you can quickly find all the cells that are blank in your data set, and replace the content of these cells with a 0.

Below I have a data set where I have the sales values for different stores in four quarters . as you would see, there are some cells that are empty, and I want to fill these blank cells with 0.

Data with blank cells

Here are the steps you can use to fill blank cells with 0 using Find and Replace:

  • Select the data set where you have the blank cells that you need to fill with 0
  • Click the Home tab

Click the Home tab

  • In the Editing section, click on ‘Find & Select’
  • Click on ‘Replace’. This will open the Find and Replace dialog box

Click on replace in Find and select

  • Leave the ‘Find what’ field empty (As we need to find all the cells that are empty/blank)

Leave the find what field empty

  • In the ‘Replace with’ field, enter 0

Enter 0 in replace with field

  • Click on Replace All

Click in Replace All

As soon as you click on the replace all button in step 7, it will fill all the blank cells with a 0.

Blank cells filled with 0

It will also show you a prompt to tell you how many cells have been filled with zero.

Prompt that shows replacement is done

One important thing to note when using find and replace to fill all the blank cells with zero is to make sure that the blank cells are in fact empty. They should not have any space character or an apostrophe that may make them look empty while they’re not.

Pro Tip : You can use the keyboard shortcut Control + H to open the Find & Replace dialog box. To do this, select the data set, then hold the Control key, and then press the H key

Using the Go to Special Technique to Select Blank Cells and Enter 0

Another easy way to quickly fill all the blank cells with 0 is by using the go to special dialog box method.

Using the Go To Special functionality, you can quickly select all the cells that are blank. Once these blank cells are selected, you can manually fill these with 0 (with a keyboard shortcut that would fill zero in all the blank cells in one go).

Below again I have the same data set where I have the sales values for four quarters, and there are some blank cells that I need to fill with zeros.

Here are the steps to fill blank cells with 0 using Go To special method:

  • Select the entire data set where you have the blank cells that you want to fill with 0
  • Press the F5 key to open the Go To dialog box
  • Click on the ‘Special’ button, which will open the Go To Special dialog box

Click the Special button

  • In the Go To Special dialog box that opens, select the ‘Blanks’ option button

Select on Blanks option

  • Click OK. You will now see that all the blank cells in the data set have been selected

All blank cells are selected

  • Enter 0 using your keyboard. you will notice that the zero is entered in one of the blank cells (which would be the active cell in the selection)

0 entered in the active cell

  • Hold the Control key and press the Enter key

Doing the above steps would enter 0 in all the blank cells in your selected data set.

zero entered in all the blank cells

In this method, the Go To Special dialog box enables us to select all the blank cells while deselecting all the other cells that are not blank.

When you enter a 0 when all the blank cells are selected, this value is entered only in the active cell, but when you use the keyboard shortcut Control + Enter, it enters the value in the active cell (which is 0), in all the other selected blank cells.

While this may look like a long method as compared to the Find and Replace technique we used earlier, one benefit that this method offers is that it allows you to select all the blank cells in one go.

In our example, once we had all the blank cells selected, we entered 0 in it.

But you can also do other things such as

  • Enter any text value such as “Zero” or “NA” or “Not available”
  • Highlight these blank cells by giving these a background color
  • Delete rows that have the blank cells

VBA Code to Fill Blank Cells with 0 (zero)

You can also use a simple VBA code to quickly fill all the blank cells with zeros.

Here is the VBA code that would go through each cell in the specified data set, and then check whether the cell is blank or not. and in case the cell is blank, it would enter a zero in that cell.

In the above VBA code, I have used the Application.InputBox method that would allow you to choose a range of cells that should be considered while running the code.

So when you execute this code, you will see a simple input box as shown below, that allows you to select the data set where you have the blank cells.

Once you select the range and click OK, the VBA code would be executed. it would go through each cell in the selection, and if it finds any blank cell, it would fill it with a 0.

There are multiple ways you can run this VBA code in Excel:

  • You can add this code to a module in the VBA editor and then run this code by hitting the F5 key or clicking on the run button in the menu in the VBA editor
  • You can assign a keyboard shortcut to this macro, and run this using that keyboard shortcut
  • You can save this macro in the Quick Access Toolbar so that you can run it with a single click

Note: If you’re adding this VBA code in a module in your excel file, make sure to save that excel file as a macro-enabled file (with a .XLSM extension). You can do this by clicking on the File tab, and then clicking on Save as. In the save as dialog box, you will have the option to save the file as a macro-enabled file (in the Save as type drop-down).

If filling blank cells with zeros is something you need to do quite often, then I would recommend you add this VBA code to your Personal Macro Workbook .

Once you do that, this code will be available in all the Excel files on your system. and if you assign a keyboard shortcut to it or add this to your Quick Access Toolbar, you will have easy access to run this macro in any Excel file (with a keyboard shortcut or with a click of a button in the quick access toolbar icon).

If you’re not sure what a personal macro workbook is and how it works, here is a great video I found that explains this in detail:

Why Fill Blank Cells with Zeros?

If you’re wondering why it’s a good idea to fill all the empty cells in your dataset with 0’s, here are some scenarios where it would be needed:

  • Many Excel formulas (such as SUM or COUNT or AVERAGE) ignore blank cells. So if you’re using these formulas, and your input range has blank cells, these cells would be ignored. This also means that formulas such as COUNT or AVERAGE would not give you the right result. In such cases, filling these blank cells with 0 would solve this issue
  • If you’re creating charts with data that has blank cells, some charts (such as line charts) show gaps in places where there are blank cells. and if you fill these blank cells with 0, the same line chart is going to show you a continuous line with no breaks.    
  • This is my personal use case where I want to fill all the blank cells with 0 (auto dash or any other character) just to make sure that I do not have any blank cells in the column. this helps me to quickly jump to the last filled cell in the column by using the keyboard shortcut CTRL and down arrow key (which wouldn’t work if I have blank cells as the cursor would always go to the last filled cell before the blank.

These are some of the reasons that I have come across where it makes sense to fill all the blank cells with 0 (zero), but I’m sure there would be other reasons as well.

In this tutorial I showed you three easy ways you can use to quickly fill all the blank cells with 0 in Excel.

The fastest way would be to use the Find and Replace technique which finds all the blank cells and replace the content of these cells with 0 (or any value that you specify).

You can also use the Go To Special method, which allows you to select all the blank cells and then manually enter 0 in all the blank cells in one go.

And finally, I showed you the VBA method that allows you to quickly select a range of cells that have blank cells and fill these with zero.

I hope this Excel tutorial helps you in your day-to-day Excel tasks.

Other Excel tutorials you may also find useful:

  • How to Highlight Blank Cells in Excel?
  • Fill Blank Cells with Dash (-) in Excel
  • How to Fill Blank Cells with Value above in Excel
  • How to Remove Commas in Excel (from Numbers or Text String)
  • How to Find Merged Cells in Excel
  • How to Remove Leading Zeros in Excel

' src=

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Excel Tutorial World

Learn Excel With Examples

Check also extract substring between parenthesis in excel, check also count number of users who are active in an excel workbook, check also vba to open latest excel workbook, check also print cell height and cell width of a cell in excel, check also delete non numeric values from numeric field in excel, check also create rows from column in excel, check also sum numbers separated by symbol in excel, check also sort comma separated values within a cell in excel, assign/change yes to 1 and no to 0 in excel.

In this post we will see how we could assign/change the value of “Yes” to 1 and “No” to 0.

This post is also relevant if you would like to change True to 1 and False to 0, True or False and Yes or No are available in a column as shown below and we would like to have another column in which the adjacent cells will contain 1/0 depending on the values that we have in column A as shown below.

Excel table showing values 1 in place of yes and 0 in place of No

We will use IF formula in excel to change the values of Yes to 1 and No to 0.

Enter the formula =IF(A1=”Yes”,1,IF(A1=”No”,0))  in the cell B1 as shown below in the figure.

The First IF formula shown here is checking the value of cell A1 and putting a value of 1 if it is Yes and if the value is not Yes, then the second IF formula is checking the value for No and putting 0.

excel formula to change yes to 1 and no to 0

Now press enter and you will observe that the value 1 has appearedin the cell B1 as A1 contains “Yes”.

Now drag the formula in column B till you have the data in column A to get the corresponding values of 1/0 based on Yes/No as shown below.

all the yes have been converted to 1 and No to 0 in excel using formula

Like wise you could change the value of True/False to 1/0 by just changing the “Yes” and “No” to “True” and “False” in the IF formula that  we have entered in cell B1.

=IF(A1=”True”,1,IF(A1=”False”,0))

Also, if you would like to do the opposite of what we have done here i.e. changing the value of 1 to Yes and 0 to No (or changing the value of 1 to True and 0 to False) the just change the formula as per your requirement.

In the formula below I am getting the values “Yes” in case the cell contains 1 and “No” if the  value is 0.

=IF(A1=1,”Yes”,IF(A1=0,”No”))

Notice that we have not put 1 and 0 withing quotes as they are numeric.

In case you have other values too in column A apart from “Yes” and “No” and you want to show them as they are then just modify the formula a bit by inserting A1 in the seconf IF condition as shown below.

=IF(A1=”Yes”,1,IF(A1=”No”,0,A1))

Hope this helped.

' src=

Random Posts

Add Time To A DateTime Value In Excel

add minute to a date time value in excel, add hour to a date time value in excel, add second to a date time value in excel, add time value to a datetime in excel ,excel formula to add time to a datetime value

change to 0 excel

In this post we will see how we could assign/change the value of “Yes” to 1 and “No” to 0. […]

change to 0 excel

In this post we will see how to auto border cell in excel. Bordering cell manually is a time taking […]

change to 0 excel

Let’s see how to hide the content of a cell in excel. Suppose we want to hide the content in […]

Leave a Reply Cancel reply

You must be logged in to post a comment.

  • Popular Post
  • Recent Post

Extract Substring Between Parenthesis In Excel

Calculate Percentage Change in Excel (% Increase/Decrease Formula)

When working with data in Excel, calculating the percentage change is a common task.

Whether you working with professional sales data, resource management, project management, or personal data, knowing how to calculate percentage change would help you make better decisions and do better data analysis in Excel.

It’s really easy, thanks to amazing MS Excel features and functions.

In this tutorial, I will show you how to calculate percentage change in Excel (i.e., percentage increase or decrease over the given time period).

So let’s get started!

This Tutorial Covers:

Calculate Percentage Change Between Two Values (Easy Formula)

The most common scenario where you have to calculate percentage change is when you have two values, and you need to find out how much change has happened from one value to the other.

For example, if the price of an item increases from $60 to $80, this could be a scenario where you have to calculate how much increase in percentage happened in this case.

Let’s have a look at examples.

Percentage Increase

Suppose I have the data set as shown below where I have the old price of an item in cell A2 and the new price in cell B2.

The formula to calculate the percentage increase would be:

Below is the formula to calculate the price percentage increase in Excel:

There’s a possibility that you may get the resulting value in decimals (the value would be correct, but need the right format).

To convert this decimal into a percentage value , select the cell that has the value and then click on the percentage icon (%) in the Number group in the Home tab of the Excel ribbon.

In case you want to increase or decrease the number of digits after the decimal, use the Increase/Decrease decimal icons that are next to the percentage icon.

Important: It is important to note that I have kept the calculation to find out the change in new end old price in brackets. This is important because I first want to calculate the difference and then want to divide it by the original price. in case you don’t put these in brackets, the formula will first divide and then subtract (following the order of precedence of operators)

Percentage Decrease

Calculating a percentage decrease works the same way as a percentage increase.

Suppose you have the below two values where the new price is lower than the old price.

In this case, you can use the below formula to calculate the percentage decrease:

Since we are calculating the percentage decrease, we calculate the difference between the old and the new price and then divide that value from the old price.

Calculate the Value After Percentage Increase/Decrease

Suppose you have a data set as shown below, where I have some values in column A and the percentage change values in column B.

Below is the formula you can use to calculate the final value that would be after incorporating the percentage change in column B:

You need to copy and paste this formula for all the cells in Column C.

In the above formula, I first calculate the overall percentage that needs to be multiplied with the value. to do that, I add the percentage value to 1 (within brackets).

And this final value is then multiplied by the values in column A to get the result.

As you can see, it would work for both percentage increase and percentage decrease.

In case you’re using Excel with Microsoft 365 subscription, you can use the below formula (and you don’t need to worry about copy-pasting the formula:

Increase/Decrease an Entire Column with Specific Percentage Value

Suppose you have a data set as shown below where I have the old values in column A and I want the new values column to be 10% higher than the old values.

This essentially means that I want to increment all the values in Column A by 10%.

You can use the below formula to do this:

The above formula simply multiplies the old value by 110%, which would end up giving you a value that is 10% higher.

Similarly, if you want to decrease the entire column by 10%, you can use the below formula:

Remember that you need to copy and paste this formula for the entire column.

In case you have the value (by which you want to increase or decrease the entire column) in a cell, you can use the cell reference instead of hardcoding it into the formula.

For example, if I have the percentage value in cell D2, I can use the below formula to get the new value after the percentage change:

The benefit of having the percentage change value in a separate cell is that in case you have to change the calculation by changing this value, you just need to do that in one cell. Since all the formulas are linked to the cell, the formulas would automatically update.

Percentage Change in Excel with Zero

While calculating percentage change in excel is quite easy, you will likely face some challenges when there is a zero involved in the calculation.

For example, if your old value is zero and your new value is 100, what do you think is the percentage increase.

If you use the formulas we have used so far, you will have the below formula:

But you can’t divide a number by zero in math. so if you try and do this, Excel will give you a division error (#DIV/0!)

This is not an Excel problem, rather it’s a math problem.

In such cases, a commonly accepted solution is to consider the percentage change as 100% (as the new value has grown by 100% starting from zero).

Now, what if you had the opposite.

What if you have a value that goes from 100 to 0, and you want to calculate the percentage change.

Thankfully, in this case, you can.

The formula would be:

This will give you 100%, which is the correct answer.

So to put it in simple terms, if you calculating percentage change and there is a 0 involved (be it as the new value or the old value), the change would be 100%

Percentage Change With Negative Numbers

If you have negative numbers involved and you want to calculate the percentage change, things get a bit tricky.

With negative numbers, there could be the following two cases:

  • Both the values are negative
  • One of the values is negative and the Other one is Positive

Let’s go through this one by one!

Both the Values are Negative

Suppose you have a dataset as shown below where both the values are negative.

I want to find out what’s the change in percentage when values change from -10 to -50

The good news is that if both the values are negative, you can simply go ahead and use the same logic and formula you use with positive numbers.

So below is the formula that will give the right result:

In case both the numbers have the same sign (positive or negative), the math takes care of it.

One Value is Positive and One is Negative

In this scenario, there are two possibilities:

  • Old value is positive and new value is negative
  • Old value is negative and new value is positive

Let’s look at the first scenario!

Old Value is Positive and New Value is Negative

If the old value is positive, thankfully the math works and you can use the regular percentage formula in Excel.

Suppose you have the dataset as shown below and you want to calculate the percentage change between these values:

The below formula will work:

As you can see, since the new value is negative, this means that there is a decline from the old value, so the result would be a negative percentage change.

So all’s good here!

Now let’s look at the other scenario.

Old Value is Negative and New Value is Positive

This one needs one minor change.

Suppose you’re calculating the change where the old value is -10 and the new value is 10.

If we use the same formulas as before, we will get -200% (which is incorrect as the value change has been positive).

This happens since the denominator in our example is negative. So while the value change is positive, the denominator makes the final result a negative percentage change.

Here is the fix – make the denominator positive .

And here is the new formula you can use in case you have negative values involved:

The ABS function gives the absolute value, so negative values are automatically changes to positive.

So these are some methods that you can use to calculate percentage change in Excel. I have also covered the scenarios where you need to calculate percent change when one of the values could be 0 or negative.

I hope you found this tutorial useful!

Other Excel tutorials you may also like:

  • How To Subtract In Excel (Subtract Cells, Column, Dates/Time)
  • How to Multiply in Excel Using Paste Special
  • How to Stop Excel from Rounding Numbers (Decimals/Fractions)
  • How to Add Decimal Places in Excel (Automatically)
  • How to Convert Serial Numbers to Dates in Excel
  • How to Calculate PERCENTILE in Excel

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Leave a comment cancel reply.

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

change to 0 excel

  • Excel Howtos , Learn Excel

How to fix SPILL Error in Excel Tables (3 easy solutions)

  • Last updated on February 21, 2024

Chandoo

So you have a SPILL error in your Excel tables? In this quick article, let me show you 3 easy fixes to the problem.

Fix 0: See if Excel can auto-fix the formula

This is not really a fix. But if you write certain types of formulas in table, Excel will warn you about the potential spill error and fix it for you. See this example:

Excel auto correcting the SPILL error in tables

One lesson we can take away from this auto-correction of the formula is that if you are using a table column style formula, change it to [@ column] style.

For example:

  • Instead of LEFT([Name]) use LEFT([@Name], 1)
  • =IF(LEN([Value])>6, “Too long”, “Short enough”) can be =IF(LEN([ @ Value])>6, “Too long”, “Short enough”)

Note: This auto-correction of formulas is seems to be a new feature, so may not be active in all Excel 365 versions.

Fix 1: Change to a non-spillable formula

If an Excel formula can result in more than one value, it automatically spills . Such formulas are called Dynamic Array Excel Functions . A simple example is the SEQUENCE function.

=SEQUENCE(10)

will return the numbers 1 to 10.

If you use them in a normal cell in Excel, they work ok.

But when you type the same formula in a table, it gives the SPILL error (see this demo).

So an easy fix is to change your formula to a version that doesn’t spill .

Refer to below table to see non-spillable alternatives for some common situations.

Fix 2: Convert Table to a Range

If you must have a spillable formula in the table column, an easy fix is to convert the table to a range. You will however, loose all the table features (such as structural references, data model connectivity and ability to send data to Power Query).

To convert table to a range, just select the table, go to Table Design ribbon and click on “Convert to Range” button.

convert table to a range using Table Design Ribbon in Excel

Once your table is in a range format,

  • Remove any spill formulas in the row 2 onwards in your range
  • This will fix the #SPILL! error, as demoed below.

Spill error will go away after you convert a table to range.

Fix 3: Move the formula outside the table

Tables do not support any sort of spill behavior. So another easy fix is to move the spill formula outside the table to an adjacent column. Something like this:

Moving the dynamic array formula (spillable) outside the table resolves it!

But what if I need the formula along with my table?

Unfortunately Excel currently doesn’t support having SPILLABLE formulas inside tables. But if you still need a formula result along with your table data (for some calculation purposes), you can use the HSTACK function, like below:

=HSTACT(your_table, spill_formula_here)

For example, I want to add a ID number column to my table. Here is the HSTACK for that:

=HSTACK(my_table, SEQUENCE(ROWS(my_table)))

Why do we even get this error?

It’s not because Excel hates you. There are two things at play here.

  • Dynamic Array Formulas: want to spill the formula results down (or sideways) when there is more than value returned by the formula (example: SEQUENCE(10)). They throw a SIPLL error whenever something is preventing the formula from spilling.
  • Excel Tables: want to apply the same formula for all cells in the table column.

So when you type an array formula in a table cell, Excel tries to apply the same formula for all cells of the table. This creates a situation where each table row has a formula that wants to return multiple values. So while first row’s formula is trying to spill, second row has a formula of its own (as Excel tables automatically apply the same formula across). Thus the SPILL errors.

Know more about Excel Tables & Dynamic Arrays

Please read below articles to understand Excel Tables & Dynamic Array features of Excel.

  • What is Excel Table and how to use it?
  • What is Dynamic Array Function in Excel?
  • How to use XLOOKUP in Excel?
  • More about SPILL Error [Microsoft Help]

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week. Learn & be awesome.

  • No Comments
  • Ask a question or say something...
  • Category: Excel Howtos , Learn Excel

change to 0 excel

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME. Read my story • FREE Excel tips book

change to 0 excel

Want an AWESOME Excel Class?

Advanced Excel & Dashboards training - Excel School is here

– Brenda

FREE Goodies for you...

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

change to 0 excel

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

change to 0 excel

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Loan Amortization Schedule in Excel

Loan Amortization Schedule in Excel – FREE Template

Do you want to calculate loan amortization schedule in Excel? We can use PMT & SEQUENCE functions to quickly and efficiently generate the full loan amortization table for any number of years.

How-to create Dependent Drop Downs in Excel [Dynamic & Multiple]

change to 0 excel

VLOOKUP(), MATCH() and INDEX() – explained in plain English

STAFF-ROSTER-TEMPLATE

Beautiful Staff Roster Excel Template [FREE Download]

time between two dates excel formula

How to calculate time between two dates in Years, Months & Days [Excel Formula]

  • Excel for beginners
  • Advanced Excel Skills
  • Excel Dashboards
  • Complete guide to Pivot Tables
  • Top 10 Excel Formulas
  • Excel Shortcuts
  • #Awesome Budget vs. Actual Chart
  • 40+ VBA Examples

Related Tips

change to 0 excel

35 shortcuts & tricks to make you an #AWESOME Data Analyst

Leave a reply.

Name (required)

Mail (will not be published) (required)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Notify me of when new comments are posted via e-mail

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free. 

Get Started

  • Excel Basics
  • Advanced Excel
  • 100+ Formulas
  • Free Crash Course
  • Pivot Tables

Online Classes

  • Excel School
  • VBA Classes
  • Power BI Classes
  • 50 ways to analyze data
  • Mini Courses
  • Welcome Message
  • Forum & Community
  • 20+ Excel Templates
  • 200+ Dashboards
  • 40+ VBA Code samples
  • Ebooks & More

© All rights reserved.

Avantix Learning

How to Replace Zeros (0) with Blanks in Excel

Replace zeros with blank cells in Excel represented by laptop.

Enter, Return or Display Blanks Instead of Zeros in Excel

by Avantix Learning Team | Updated January 4, 2024

Applies to: Microsoft Ÿ  Excel Ÿ 2016, 2019, 2021 and 365 (Windows)

There are several strategies to replace zero values (0) with blanks in Excel. If you want to replace zero values in cells with blanks, you can use the Replace command or write a formula to return blanks. However, if you simply want to display blanks instead of zeros, you have two formatting options – create a custom number format or a conditional format.

Recommended article: How to Delete Blank Rows in Excel (5 Easy Ways with Shortcuts)

Do you want to learn more about Excel? Check out our virtual classroom or live (in-person) classroom Excel courses >

In this article, we'll review how to:

Replace zeros with blanks using the Replace command

Return blanks instead of zeros using a formula, replace or display blanks instead of zeros using a custom number format, replace or display blanks instead of zeros using a conditional format.

To replace zeros with blanks using the Replace command:

  • Select the cells containing zero values or select one cell only if you want to change the entire worksheet or workbook.
  • Press Ctrl + H. You can also click the Home tab in the Ribbon, click Find & Select in the Editing group and then select Replace from the drop-down menu. A dialog box appears.
  • Enter 0 in the Find what box.
  • Ensure the Replace with box is empty.
  • Check or select the Match entire cell contents check box or Excel will replace all zeros (including those within larger numbers or dates).
  • From the Within drop-down menu, select Sheet or Workbook if you want to replace zeros across the entire worksheet or workbook.
  • Click Replace All.

The Replace dialog box appears as follows in Excel:

Replace dialog box in Excel to replace zeros with blanks.

This method works best with zero values entered in cells as opposed to formulas returning zeros.

Another option is to create a formula to return blanks instead of zeros. A common function to use in a formula of this type is the IF function using the syntax =IF(test, true, false).

To create a formula using the IF function to return blanks:

  • Select the cell where you want to return blanks instead of zeros.
  • Type an equal sign (=).
  • Enter a formula such as =IF(cell reference=0, "", cell reference). For example, in B1, you could enter =IF(A1=0, "", A1). In this example, if A1 is equal to 0, enter a blank, if not, enter the value in A1.
  • If you want to copy the formula down, drag the Fill Handle (bottom right corner handle), down to the cells below. You can also copy the formula to the right by dragging the Fill Handle to the right.

If you want to display blanks, you can create a custom number format.

To display blanks instead of zeros for specific cells:

  • Select the cells that contain the zero (0) values that you want to display as blanks.
  • Press Ctrl + 1 or right-click and select Format Cells. The Format Cells dialog box appears.
  • Click the Number tab.
  • In the categories on the left, select Custom. Custom formats can have up to 4 parts separated by semi-colons (;). The first part affects positive numbers, the second part affects negative numbers, the third part affects zeros and the fourth part affects text.
  • In the Type box, enter a custom format such as #,##0;-#,##0;;"N/A". In this example, the third part is left blank so zeros would display as blank.

The Format Cells dialog box appears with custom number formats as follows:

Format Cells dialog box in Excel with custom number format to display zeros as blanks.

Custom number formats are only saved in the current workbook. The contents of the cells containing zero values would still display in the Formula Bar.

You can also create a conditional format to display zeros as blanks. This strategy involves changing font color (typically to white).

To create a conditional format to display blanks instead of zeros:

  • Select the cells that contain zeros. These cells should have no fill or a white fill.
  • Click the Home tab in the Ribbon.
  • Click Conditional Formatting in the Styles group. A drop-down menu appears.
  • Select New Rule. A dialog box appears.
  • Click Format only cells that contain.
  • To the right of Cell value, select Equal to from the drop-down menu.
  • Enter 0 in the box to the right.
  • Click Format. A dialog box appears.
  • Click the Font tab.
  • From the Color drop-down menu, select white.
  • Click OK twice.

The New Formatting Rule dialog box appears as follows:

New Rule conditional formatting dialog box with rule to display zeros as blank or white text.

Although you can also hide all zeros in a worksheet using File > Options, this method can be problematic as other users may not know that Options have been changed for the worksheet.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

How to Use Flash Fill in Excel (4 Ways with Shortcuts)

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

How to Replace Blank Cells in Excel with a Value from the Cell Above

How to Replace Blank Cells with Zeros (0), Dashes (-) or Other Values in Excel

10 Excel Flash Fill Examples (Extract, Combine, Clean and Format Data with Flash Fill)

Related courses

Microsoft Excel: Introduction

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Power Query (Get & Transform)

Microsoft Excel: Introduction to Power Pivot

Microsoft Excel: New Features and Functions in Excel 365

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at [email protected] if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix Âź Learning

You may also like

What is Power Query in Excel?

What is Power Query in Excel?

Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.

How to Freeze Rows in Excel (One or Multiple Rows)

How to Freeze Rows in Excel (One or Multiple Rows)

You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you freeze rows containing headings, the headings will appear when you scroll down. You can freeze columns as well so when you scroll to the right columns will be frozen.

How to Show or Hide Gridlines in Excel

How to Show or Hide Gridlines in Excel

You can remove or hide gridlines in Excel worksheets to simplify worksheet design. By default, gridlines are displayed but do not print. Gridlines are applied to entire worksheets or workbooks, not to specific cells. If you hide gridlines on one worksheet, it doesn't affect other sheets in the same workbook.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at [email protected]

How to Replace Zeros (0) with Blanks in Excel

Our Courses

Avantix Learning courses are offered online in virtual classroom format or as in-person classroom training. Our hands-on, instructor-led courses are available both as public scheduled courses or on demand as a custom training solution.

All Avantix Learning courses include a comprehensive course manual including tips, tricks and shortcuts as well as sample and exercise files.

VIEW COURSES  >

Contact us at  [email protected]  for more information about any of our courses or to arrange custom training.

Privacy Overview

Pin it on pinterest.

  • Print Friendly

change to 0 excel

Create a form in Word that users can complete or print

In Word, you can create a form that others can fill out and save or print.  To do this, you will start with baseline content in a document, potentially via a form template.  Then you can add content controls for elements such as check boxes, text boxes, date pickers, and drop-down lists. Optionally, these content controls can be linked to database information.  Following are the recommended action steps in sequence.  

Show the Developer tab

In Word, be sure you have the Developer tab displayed in the ribbon.  (See how here:  Show the developer tab .)

Open a template or a blank document on which to base the form

You can start with a template or just start from scratch with a blank document.

Start with a form template

Go to File > New .

In the  Search for online templates  field, type  Forms or the kind of form you want. Then press Enter .

In the displayed results, right-click any item, then select  Create. 

Start with a blank document 

Select Blank document .

Add content to the form

Go to the  Developer  tab Controls section where you can choose controls to add to your document or form. Hover over any icon therein to see what control type it represents. The various control types are described below. You can set properties on a control once it has been inserted.

To delete a content control, right-click it, then select Remove content control  in the pop-up menu. 

Note:  You can print a form that was created via content controls. However, the boxes around the content controls will not print.

Insert a text control

The rich text content control enables users to format text (e.g., bold, italic) and type multiple paragraphs. To limit these capabilities, use the plain text content control . 

Click or tap where you want to insert the control.

Rich text control button

To learn about setting specific properties on these controls, see Set or change properties for content controls .

Insert a picture control

A picture control is most often used for templates, but you can also add a picture control to a form.

Picture control button

Insert a building block control

Use a building block control  when you want users to choose a specific block of text. These are helpful when you need to add different boilerplate text depending on the document's specific purpose. You can create rich text content controls for each version of the boilerplate text, and then use a building block control as the container for the rich text content controls.

building block gallery control

Select Developer and content controls for the building block.

Developer tab showing content controls

Insert a combo box or a drop-down list

In a combo box, users can select from a list of choices that you provide or they can type in their own information. In a drop-down list, users can only select from the list of choices.

combo box button

Select the content control, and then select Properties .

To create a list of choices, select Add under Drop-Down List Properties .

Type a choice in Display Name , such as Yes , No , or Maybe .

Repeat this step until all of the choices are in the drop-down list.

Fill in any other properties that you want.

Note:  If you select the Contents cannot be edited check box, users won’t be able to click a choice.

Insert a date picker

Click or tap where you want to insert the date picker control.

Date picker button

Insert a check box

Click or tap where you want to insert the check box control.

Check box button

Use the legacy form controls

Legacy form controls are for compatibility with older versions of Word and consist of legacy form and Active X controls.

Click or tap where you want to insert a legacy control.

Legacy control button

Select the Legacy Form control or Active X Control that you want to include.

Set or change properties for content controls

Each content control has properties that you can set or change. For example, the Date Picker control offers options for the format you want to use to display the date.

Select the content control that you want to change.

Go to Developer > Properties .

Controls Properties  button

Change the properties that you want.

Add protection to a form

If you want to limit how much others can edit or format a form, use the Restrict Editing command:

Open the form that you want to lock or protect.

Select Developer > Restrict Editing .

Restrict editing button

After selecting restrictions, select Yes, Start Enforcing Protection .

Restrict editing panel

Advanced Tip:

If you want to protect only parts of the document, separate the document into sections and only protect the sections you want.

To do this, choose Select Sections in the Restrict Editing panel. For more info on sections, see Insert a section break .

Sections selector on Resrict sections panel

If the developer tab isn't displayed in the ribbon, see Show the Developer tab .

Open a template or use a blank document

To create a form in Word that others can fill out, start with a template or document and add content controls. Content controls include things like check boxes, text boxes, and drop-down lists. If you’re familiar with databases, these content controls can even be linked to data.

Go to File > New from Template .

New from template option

In Search, type form .

Double-click the template you want to use.

Select File > Save As , and pick a location to save the form.

In Save As , type a file name and then select Save .

Start with a blank document

Go to File > New Document .

New document option

Go to File > Save As .

Go to Developer , and then choose the controls that you want to add to the document or form. To remove a content control, select the control and press Delete. You can set Options on controls once inserted. From Options, you can add entry and exit macros to run when users interact with the controls, as well as list items for combo boxes, .

Adding content controls to your form

In the document, click or tap where you want to add a content control.

On Developer , select Text Box , Check Box , or Combo Box .

Developer tab with content controls

To set specific properties for the control, select Options , and set .

Repeat steps 1 through 3 for each control that you want to add.

Set options

Options let you set common settings, as well as control specific settings. Select a control and then select Options to set up or make changes.

Set common properties.

Select Macro to Run on lets you choose a recorded or custom macro to run on Entry or Exit from the field.

Bookmark Set a unique name or bookmark for each control.

Calculate on exit This forces Word to run or refresh any calculations, such as total price when the user exits the field.

Add Help Text Give hints or instructions for each field.

OK Saves settings and exits the panel.

Cancel Forgets changes and exits the panel.

Set specific properties for a Text box

Type Select form Regular text, Number, Date, Current Date, Current Time, or Calculation.

Default text sets optional instructional text that's displayed in the text box before the user types in the field. Set Text box enabled to allow the user to enter text into the field.

Maximum length sets the length of text that a user can enter. The default is Unlimited .

Text format can set whether text automatically formats to Uppercase , Lowercase , First capital, or Title case .

Text box enabled Lets the user enter text into a field. If there is default text, user text replaces it.

Set specific properties for a Check box .

Default Value Choose between Not checked or checked as default.

Checkbox size Set a size Exactly or Auto to change size as needed.

Check box enabled Lets the user check or clear the text box.

Set specific properties for a Combo box

Drop-down item Type in strings for the list box items. Press + or Enter to add an item to the list.

Items in drop-down list Shows your current list. Select an item and use the up or down arrows to change the order, Press - to remove a selected item.

Drop-down enabled Lets the user open the combo box and make selections.

Protect the form

Go to Developer > Protect Form .

Protect form button on the Developer tab

Note:  To unprotect the form and continue editing, select Protect Form again.

Save and close the form.

Test the form (optional)

If you want, you can test the form before you distribute it.

Protect the form.

Reopen the form, fill it out as the user would, and then save a copy.

Creating fillable forms isn’t available in Word for the web.

You can create the form with the desktop version of Word with the instructions in Create a fillable form .

When you save the document and reopen it in Word for the web, you’ll see the changes you made.

Facebook

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

change to 0 excel

Microsoft 365 subscription benefits

change to 0 excel

Microsoft 365 training

change to 0 excel

Microsoft security

change to 0 excel

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

change to 0 excel

Ask the Microsoft Community

change to 0 excel

Microsoft Tech Community

change to 0 excel

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

  • Search for: Toggle Search

Say What? Chat With RTX Brings Custom Chatbot to NVIDIA RTX AI PCs

Chatbots are used by millions of people around the world every day, powered by NVIDIA GPU-based cloud servers. Now, these groundbreaking tools are coming to Windows PCs powered by NVIDIA RTX for local, fast, custom generative AI .

Chat with RTX , now free to download , is a tech demo that lets users personalize a chatbot with their own content, accelerated by a local NVIDIA GeForce RTX 30 Series GPU or higher with at least 8GB of video random access memory, or VRAM.

Ask Me Anything

Chat with RTX uses retrieval-augmented generation (RAG), NVIDIA TensorRT-LLM software and NVIDIA RTX acceleration to bring generative AI capabilities to local, GeForce-powered Windows PCs. Users can quickly, easily connect local files on a PC as a dataset to an open-source large language model like Mistral or Llama 2, enabling queries for quick, contextually relevant answers.

Rather than searching through notes or saved content, users can simply type queries. For example, one could ask, “What was the restaurant my partner recommended while in Las Vegas?” and Chat with RTX will scan local files the user points it to and provide the answer with context.

The tool supports various file formats, including .txt, .pdf, .doc/.docx and .xml. Point the application at the folder containing these files, and the tool will load them into its library in just seconds.

Users can also include information from YouTube videos and playlists. Adding a video URL to Chat with RTX allows users to integrate this knowledge into their chatbot for contextual queries. For example, ask for travel recommendations based on content from favorite influencer videos, or get quick tutorials and how-tos based on top educational resources.

change to 0 excel

Since Chat with RTX runs locally on Windows RTX PCs and workstations, the provided results are fast — and the user’s data stays on the device. Rather than relying on cloud-based LLM services, Chat with RTX lets users process sensitive data on a local PC without the need to share it with a third party or have an internet connection.

In addition to a GeForce RTX 30 Series GPU or higher with a minimum 8GB of VRAM, Chat with RTX requires Windows 10 or 11, and the latest NVIDIA GPU drivers.

Editor’s note: We have identified an issue in Chat with RTX that causes installation to fail when the user selects a different installation directory. This will be fixed in a future release. For the time being, users should use the default installation directory (“C:\Users\<username>\AppData\Local\NVIDIA\ChatWithRTX”).

Develop LLM-Based Applications With RTX

Chat with RTX shows the potential of accelerating LLMs with RTX GPUs. The app is built from the TensorRT-LLM RAG developer reference project, available on GitHub . Developers can use the reference project to develop and deploy their own RAG-based applications for RTX, accelerated by TensorRT-LLM. Learn more about building LLM-based applications .

Enter a generative AI-powered Windows app or plug-in to the NVIDIA Generative AI on NVIDIA RTX developer contest, running through Friday, Feb. 23, for a chance to win prizes such as a GeForce RTX 4090 GPU, a full, in-person conference pass to NVIDIA GTC and more.

Learn more about Chat with RTX .

NVIDIA websites use cookies to deliver and improve the website experience. See our cookie policy for further details on how we use cookies and how to change your cookie settings.

State and Local Sales Tax Rates, 2024

Key findings.

  • Forty-five states and the District of Columbia collect statewide sales taxes.
  • Local sales taxes are collected in 38 states. In some cases, they can rival or even exceed state rates.
  • The five states with the highest average combined state and local sales tax A sales tax is levied on retail sales of goods and services and, ideally, should apply to all final consumption with few exemptions . Many governments exempt goods like groceries; base broadening , such as including groceries, could keep rates lower. A sales tax should exempt business-to-business transactions which, when taxed, cause tax pyramiding . rates are Louisiana (9.56 percent), Tennessee (9.55 percent), Arkansas (9.45 percent), Washington (9.38 percent), and Alabama (9.29 percent).
  • Local sales tax A tax is a mandatory payment or charge collected by local, state, and national governments from individuals or businesses to cover the costs of general government services, goods, and activities. increases in the Twin Cities metro region of Minnesota , as well as in Milwaukee, Wisconsin , are responsible for the greatest movement in states’ average sales tax rates in the past six months.
  • Sales tax rates differ by state, but sales tax bases also impact how much revenue is collected from a tax and how the tax affects the economy.
  • Sales tax rate differentials can induce consumers to shop across borders or buy products online.

Introduction

Retail sales taxes are an essential part of most states’ revenue toolkits , responsible for 32 percent of state tax collections and 13 percent of local tax collections (24 percent of combined collections). They also benefit from being more pro-growth than the other major state tax, the individual income tax An individual income tax (or personal income tax) is levied on the wages, salaries, investments, or other forms of income an individual or household earns. The U.S. imposes a progressive income tax where rates increase with income . The Federal Income Tax was established in 1913 with the ratification of the 16th Amendment . Though barely 100 years old, individual income taxes are the largest source of tax revenue in the U.S. , because they introduce fewer economic distortions.

Forty-five states impose state-level sales taxes, while consumers also face local sales taxes in 38 states, including Alaska , which does not impose a statewide tax. These local rates can be substantial, and some states with moderate statewide sales tax rates actually impose quite high combined state and local rates compared to other states. This report provides a population-weighted average of local sales taxes as of January 1, 2024, to give a sense of the average local rate for each state. Table 1 provides a full state-by-state listing of state and local sales tax rates.

Combined State and Local Sales Tax Rates

Five states forego statewide sales taxes: Alaska, Delaware , Montana , New Hampshire , and Oregon . Of these, only Alaska allows localities to impose local sales taxes. [1]

The five states with the highest average combined state and local sales tax rates are Louisiana (9.56 percent), Tennessee (9.55 percent), Arkansas (9.45 percent), Washington (9.38 percent), and Alabama (9.29 percent). The five states with the lowest average combined rates are Alaska (1.82 percent), Hawaii (4.50 percent), Wyoming (5.44 percent), Maine (5.50 percent), and Wisconsin (5.70 percent).

2024 sales taxes including 2024 sales tax rates 2024 state and local sales tax rates

State Sales Tax Rates

California has the highest state-level sales tax rate, at 7.25 percent. [2] Four states tie for the second-highest statewide rate, at 7 percent: Indiana , Mississippi , Rhode Island , and Tennessee. The lowest non-zero state-level sales tax is in Colorado , which has a rate of 2.9 percent. Five states follow with 4 percent rates: Alabama, Georgia , Hawaii, New York , and Wyoming. [3]

No state rates have changed since South Dakota cut its state sales tax rate in 2023, a reduction set to expire after 2026. The Mount Rushmore State follows on the heels of New Mexico , which lowered the rate of its state-level sales tax—a hybrid tax the state refers to as its gross receipts tax A gross receipts tax is a tax applied to a company’s gross sales, without deductions for a firm’s business expenses, like costs of goods sold and compensation. Unlike a sales tax , a gross receipts tax is assessed on businesses and apply to business-to-business transactions in addition to final consumer purchases, leading to tax pyramiding . —from 5.125 percent to 5 percent in July 2022. Notably, if the revenue from the gross receipts tax in any single fiscal year from 2026 to 2029 is less than 95 percent of the previous year’s revenue, then the state’s rate will return to 5.125 percent on the following July 1.

Before that, the most recent statewide rate reduction was Louisiana’s cut , from 5.0 to 4.45 percent, in July 2018. State lawmakers have instead prioritized income tax cuts, which yield more economic benefit, reducing individual or corporate income tax A corporate income tax (CIT) is levied by federal and state governments on business profits. Many companies are not subject to the CIT because they are taxed as pass-through businesses , with income reportable under the individual income tax . rates (or both) in more than two dozen states in the past two years alone.

Local Sales Tax Rates

The five states with the highest average local sales tax rates are Alabama (5.29 percent), Louisiana (5.11 percent), Colorado (4.91 percent), New York (4.53 percent), and Oklahoma (4.49 percent).

Only Minnesota and Wisconsin saw a ranking change of more than one place since July, both declining two places due to local tax increases in major population centers. Arizona , California, Colorado, Maine, New Mexico , Ohio , Tennessee, and Wyoming all improved by one place, typically due to rate increases in peer states, while, in addition to movement in Minnesota and Wisconsin, the states of Illinois , Louisiana, Missouri , and Utah all slid one place, due to tax increases in a variety of smaller jurisdictions.

In Minnesota, the seven-county Twin Cities metro area saw a 1 percentage point sales tax increase, split between 0.75 percent for transportation and 0.25 percent for housing , which went into effect October 1, 2023. In neighboring Wisconsin , the city of Milwaukee imposed a new 2 percent sales tax on January 1, 2024, while Milwaukee County increased its sales tax rate from 0.5 to 0.9 percent. In Hawaii, a 0.5 percent surcharge was approved for Maui. Many other states saw a smattering of local sales tax rate changes—mostly increases—as well.

The Role of Competition in Setting Sales Tax Rates

Avoidance of sales tax is most likely to occur in areas where there is a significant difference between jurisdictions’ rates. Research indicates that consumers can and do leave high-tax areas to make major purchases in low-tax areas, such as from cities to suburbs. For example, evidence suggests that Chicago-area consumers make major purchases in surrounding suburbs or online to avoid Chicago’s 10.25 percent sales tax rate.

At the statewide level, businesses sometimes locate just outside the borders of high sales-tax areas to avoid being subjected to their rates. A stark example of this occurs in New England, where even though I-91 runs up the Vermont side of the Connecticut River, many more retail establishments choose to locate on the New Hampshire side to avoid sales taxes. One study shows that per capita sales in border counties in sales tax-free New Hampshire have tripled since the late 1950s, while per capita sales in border counties in Vermont have remained stagnant. At one time, Delaware actually used its highway welcome sign to remind motorists that Delaware is the “Home of Tax-Free Shopping.”

State and local governments should be cautious about raising rates too high relative to their neighbors because doing so will yield less revenue than expected or, in extreme cases, revenue losses despite the higher tax rate.

Sales Tax Bases: The Other Half of the Equation

This report ranks states based on tax rates and does not account for differences in tax bases ( e.g. , the structure of sales taxes, defining what is taxable and nontaxable). States can vary greatly in this regard. For instance, most states exempt groceries from the sales tax, others tax groceries at a limited rate, and still others tax groceries at the same rate as all other products. Some states exempt clothing or tax it at a reduced rate.

Tax experts generally recommend that sales taxes apply to all final retail sales of goods and services but not intermediate business-to-business transactions in the production chain. These recommendations would result in a tax system that is not only broad-based but also “right-sized,” applying once and only once to each product the market produces. Despite agreement in theory, the application of most state sales taxes is far from this ideal .

Hawaii has the broadest sales tax in the United States , but it taxes many products multiple times and, by one estimate, ultimately taxes 119 percent of the state’s personal income. This base is far wider than the national median , where the sales tax applies to 36 percent of personal income.

Methodology

Sales Tax Clearinghouse publishes quarterly sales tax data at the state, county, and city levels by ZIP code. We weight these numbers according to the most recent Census population figures to give a sense of the prevalence of sales tax rates in a particular state. This is a change from previous editions, where we used figures available every decade. While changes due to the new weighting were mostly trivial, we show changes in rank based on January 1 figures recalculated under the new population weighting. Due to the updated population weighting, this report is not strictly comparable to previously published editions, though differences amount to minor rounding errors.

It should also be noted that while the Census Bureau reports population data using a five-digit identifier that looks much like a ZIP code, this is actually a ZIP Code Tabulation Area (ZCTA), which attempts to create a geographical area associated with a given ZIP code. This is done because a surprisingly large number of ZIP codes do not actually have any residents. For example, the National Press Building in Washington, D.C., has its own ZIP code solely for postal reasons.

For our purposes, ZIP codes that do not have a corresponding ZCTA population figure are omitted from calculations. These omissions result in some amount of inexactitude but overall do not have a significant effect on resultant averages because proximate ZIP code areas that do have ZCTA population numbers capture the tax rate of those jurisdictions.

Sales taxes are just one part of an overall tax structure and should be considered in context. For example, Tennessee has high sales taxes but no income tax, whereas Oregon has no sales tax but high income taxes. While many factors influence business location and investment decisions, sales taxes are something within policymakers’ control that can have immediate impacts.

Stay informed on the tax policies impacting you.

Subscribe to get insights from our trusted experts delivered straight to your inbox.

[1] Special taxes in Montana’s resort areas are not included in our analysis.

[2] This number includes mandatory add-on taxes that are collected by the state but distributed to local governments. Because of this, some sources will describe California’s sales tax as 6.0 percent. A similar situation exists in Utah and Virginia .

[3] The sales taxes in Hawaii, New Mexico , and South Dakota have bases that include many business services and so are not strictly comparable to other sales taxes.

Previous Versions

State and local sales tax rates, midyear 2023, state and local sales tax rates, 2023, state and local sales tax rates, midyear 2022, state and local sales tax rates, 2022, state and local sales tax rates, midyear 2021, state and local sales tax rates, 2021, state and local sales tax rates, midyear 2020, state and local sales tax rates, 2020, state and local sales tax rates, midyear 2019, state and local sales tax rates, 2019, state and local sales tax rates, midyear 2018, state and local sales tax rates, 2018, state and local sales tax rates, midyear 2017, state and local sales tax rates, 2017, state and local sales tax rates, midyear 2016, state and local sales tax rates, 2016, state and local sales tax rates, midyear 2015, state and local sales tax rates, 2015, state and local sales tax rates, 2014.

  • Newsletters

Google’s Gemini is now in everything. Here’s how you can try it out.

Gmail, Docs, and more will now come with Gemini baked in. But Europeans will have to wait before they can download the app.

  • Will Douglas Heaven archive page

In the biggest mass-market AI launch yet, Google is rolling out Gemini , its family of large language models, across almost all its products, from Android to the iOS Google app to Gmail to Docs and more. You can also now get your hands on Gemini Ultra, the most powerful version of the model, for the first time.  

With this launch, Google is sunsetting Bard , the company's answer to ChatGPT. Bard, which has been powered by a version of Gemini since December, will now be known as Gemini too.  

ChatGPT , released by Microsoft-backed OpenAI just 14 months ago, changed people’s expectations of what computers could do. Google, which has been racing to catch up ever since, unveiled its Gemini family of models in December. They are multimodal large language models that can interact with you via voice, image, and text. Google claimed that its own benchmarking showed that Gemini could outperform OpenAI's multimodal model, GPT-4, on a range of standard tests. But the margins were slim. 

By baking Gemini into its ubiquitous products, Google is hoping to make up lost ground. “Every launch is big, but this one is the biggest yet,” Sissie Hsiao, Google vice president and general manager of Google Assistant and Bard (now Gemini), said in a press conference yesterday. “We think this is one of the most profound ways that we’re going to advance our company’s mission.”

But some will have to wait longer than others to play with Google’s new toys. The company has announced rollouts in the US and East Asia but said nothing about when the Android and iOS apps will come to the UK or the rest of Europe. This may be because the company is waiting for the EU’s new AI Act to be set in stone, says Dragoș Tudorache, a Romanian politician and member of the European Parliament, who was a key negotiator on the law.

“We’re working with local regulators to make sure that we’re abiding by local regime requirements before we can expand,” Hsiao said. “Rest assured, we are absolutely working on it and I hope we’ll be able to announce expansion very, very soon.”

How can you get it? Gemini Pro, Google’s middle-tier model that has been available via Bard since December, will continue to be available for free on the web at gemini.google.com (rather than bard.google.com). But now there is a mobile app as well.

If you have an Android device, you can either download the Gemini app or opt in to an upgrade in Google Assistant. This will let you call up Gemini in the same way that you use Google Assistant: by pressing the power button, swiping from the corner of the screen, or saying “Hey, Google!” iOS users can download the Google app, which will now include Gemini.

Gemini will pop up as an overlay on your screen, where you can ask it questions or give it instructions about whatever’s on your phone at the time, such as summarizing an article or generating a caption for a photo.  

Finally, Google is launching a paid-for service called Gemini Advanced. This comes bundled in a subscription costing $19.99 a month that the company is calling the Google One Premium AI Plan. It combines the perks of the existing Google One Premium Plan, such as 2TB of extra storage, with access to Google's most powerful model, Gemini Ultra, for the first time. This will compete with OpenAI’s paid-for service, ChatGPT Plus, which buys you access to the more powerful GPT-4 (rather than the default GPT-3.5) for $20 a month.

At some point soon (Google didn't say exactly when) this subscription will also unlock Gemini across Google’s Workspace apps like Docs, Sheets, and Slides, where it works as a smart assistant similar to the GPT-4-powered Copilot that Microsoft is trialing in Office 365.

When can you get it? The free Gemini app (powered by Gemini Pro) is available from today in English in the US. Starting next week, you’ll be able to access it across the Asia Pacific region in English and in Japanese and Korean. But there is no word on when the app will come to the UK, countries in the EU, or Switzerland.

Gemini Advanced (the paid-for service that gives access to Gemini Ultra) is available in English in more than 150 countries, including the UK and EU (but not France). Google says it is analyzing local requirements and fine-tuning Gemini for cultural nuance in different countries. But the company promises that more languages and regions are coming.

What can you do with it? Google says it has developed its Gemini products with the help of more than 100 testers and power users. At the press conference yesterday, Google execs outlined a handful of use cases, such as getting Gemini to help write a cover letter for a job application. “This can help you come across as more professional and increase your relevance to recruiters,” said Google’s vice president for product management, Kristina Behr.

Or you could take a picture of your flat tire and ask Gemini how to fix it. A more elaborate example involved Gemini managing a snack rota for the parents of kids on a soccer team. Gemini would come up with a schedule for who should bring snacks and when, help you email other parents, and then field their replies. In future versions, Gemini will be able to draw on data in your Google Drive that could help manage carpooling around game schedules, Behr said.   

But we should expect people to come up with a lot more uses themselves. “I’m really excited to see how people around the world are going to push the envelope on this AI,” Hsaio said.

Is it safe? Google has been working hard to make sure its products are safe to use. But no amount of testing can anticipate all the ways that tech will get used and misused once it is released. In the last few months, Meta saw people use its image-making app to produce pictures of Mickey Mouse with guns and SpongeBob SquarePants flying a jet into two towers. Others used Microsoft’s image-making software to create fake pornographic images of Taylor Swift .

The AI Act aims to mitigate some—but not all—of these problems. For example, it requires the makers of powerful AI like Gemini to build in safeguards, such as watermarking for generated images and steps to avoid reproducing copyrighted material. Google says that all images generated by its products will include its SynthID watermarks. 

Like most companies, Google was knocked onto the back foot when ChatGPT arrived. Microsoft’s partnership with OpenAI has given it a boost over its old rival. But with Gemini, Google has come back strong: this is the slickest packaging of this generation’s tech yet. 

Artificial intelligence

Ai for everything: 10 breakthrough technologies 2024.

Generative AI tools like ChatGPT reached mass adoption in record time, and reset the course of an entire industry.

What’s next for AI in 2024

Our writers look at the four hot trends to watch out for this year

  • Melissa HeikkilĂ€ archive page

OpenAI teases an amazing new generative video model called Sora

The firm is sharing Sora with a small group of safety testers but the rest of us will have to wait to learn more.

Deploying high-performance, energy-efficient AI

Investments into downsized infrastructure can help enterprises reap the benefits of AI while mitigating energy consumption, says corporate VP and GM of data center platform engineering and architecture at Intel, Zane Ball.

  • MIT Technology Review Insights archive page

Stay connected

Get the latest updates from mit technology review.

Discover special offers, top stories, upcoming events, and more.

Thank you for submitting your email!

It looks like something went wrong.

We’re having trouble saving your preferences. Try refreshing this page and updating them one more time. If you continue to get this message, reach out to us at [email protected] with a list of newsletters you’d like to receive.

Microsoft Power BI Blog

Power bi february 2024 feature summary.

Headshot of article author Saveen Reddy

Welcome to the Power BI February 2024 update. We’ve got a lot of great features this month. Here are some key highlights:

  • Visual calculations make it easier than ever to do calculations that were very hard or even impossible.
  • The Power BI home provides a centralized location for all your Power BI desktop activities.
  • Fabric Copilot for Power BI can now add measure descriptions to your semantic model measures.
  • The Power BI add in now supports shareable links to make it easier for people to consume reports.
  • The new Explore feature gives you a better understanding of what’s in the data you’re exploring.

Fabric Community Conference

Join us at the Microsoft Fabric Community Conference the ultimate Microsoft Data & AI learning event, on March 26-28, 2024, at the MGM Grand, Las Vegas. With over 150 sessions for everyone who works with Power BI, Microsoft Fabric, SQL, Azure AI, and Purview , the conference promises a rich learning experience.

This is a unique opportunity to meet the Microsoft product teams building these technologies, the customers betting their businesses on them, and the partners that are at the forefront of deployment and adoption. Engage with this vibrant community, learn from their real-world experiences, stay abreast of the latest developments.

Please note that this event is in-person only. Sessions will not be recorded, streamed or made available for on-demand consumption.

Register today using code MSCUST for an exclusive discount ! Need help convincing your boss to attend? No problem!  Use this letter  to share with your boss about this unforgettable opportunity.

change to 0 excel

  • Version number: v: 2.126.927.0
  • Date published: 02/16/2024

change to 0 excel

On-object Interaction Updates

  • Enhanced Reference Layer in Power BI Azure Maps Visual 

Data connectivity

Certified connectors updates, storytelling in powerpoint – replace report urls with shareable links in power bi add-in, data overview in explore, directquery connections update, shared device mode is now ga, power bi custom visuals new local storage api, on-object interaction support for custom visuals, tmdl in power bi desktop developer mode, editor’s pick of the quarter, new visuals in appsource, multiple sparklines, rose donut pie chart by powerviz, xviz gantt chart by lumel, control chart xmr by nova silva, drill down graph pro, more users can now collaborate with protected pbix in power bi desktop.

Visual calculations 

A new way of doing calculations has arrived! You can now add calculations directly on your visual using visual calculations, which are DAX calculations that are defined and executed directly on a visual. A calculation can refer to any data in the visual, including columns, measures, or other visual calculations. This approach removes the complexity of the semantic model and simplifies the process of writing DAX. You can use visual calculations to complete common business calculations such as running sums or moving averages. Visual calculations make it easy to do calculations that were previously very hard or even almost impossible to do.

To use visual calculations while in preview, you need to enable it in Options and Settings Â âžĄïžÂ  Options Â âžĄïžÂ  Preview features . Select visual calculations and select OK . Visual calculations will be enabled after Desktop is restarted.

To add a visual calculation, you first need to select a visual. Next, select the New calculation button in the ribbon:

The new calculation button is shown on the Home tab of the ribbon in Power BI Desktop in the Calculations group.

To add a visual calculation, type the expression in the formula bar in the visual calculations edit mode that opens. For example, in a visual that contains Sales Amount and Total Product Cost by Fiscal Year , you can add a visual calculation that calculates the profit for each year by simply typing: Profit = [Sales Amount] – [Total Product Cost].

The visual matrix is updated as you add visual calculations using in the formula bar. New visual calculations are added as columns to the visual matrix.

Additionally, you can easily add a running sum of profit by writing:

Here is a visual with the two visual calculations we have just created:

A screenshot of a graph Description automatically generated

You can use many existing DAX functions in visual calculations. Functions specific to visual calculations are also available, such as RUNNINGSUM , PREVIOUS and MOVINGAVERAGE . Using these and other functions, visual calculations are much easier to read, write and maintain than the current DAX required.

We are only just getting started with this preview. There is a lot more that we have planned, so please stay tuned for updates in future releases. However, we invite you to jump in now!

For more information, read the dedicated blog post and documentation . Please try the preview today and let us know what you think .

Dynamic subscriptions for Power BI reports

Dynamic per recipient subscriptions is now available in Preview for Power BI reports! Like dynamic subscriptions for paginated reports , you can now distribute a personalized copy of a Power BI report to each recipient of an email subscription.

Imagine you have a report that includes sales data for your entire team. You want to schedule an email subscription that sends out a PDF copy of this report to each salesperson on a weekly basis, with the report filtered to only show their sales results.

This can now be done by connecting to a semantic model (previously Power BI dataset) that defines the mapping between recipients and respective filter values. When it’s time to send out the report, the latest data available in your semantic model will determine which employees should receive a report in their inbox, and with what filter values applied.

A screenshot of a computer New dynamic subscription, select and filter data.

See the documentation for dynamic subscriptions here.

This February release we added multi-visual container format support ! Previously, when multi-selecting across different visual types, the format pane did not support any options for formatting the visuals. Now, when multi-selecting different visuals, we’ve added formatting support for container formatting such as changing the size, background color, adding a shadow or turning on/off titles in bulk.

When multi-selecting different visual types (e.g. a line chart and bar chart):

A screenshot of a graph, Visualizations. Order Quantity by Year, Sales by Category.

We’ve also added the ability to format a visual’s container size and position even if it’s empty:

A screenshot of a graph, Visualizations. Select or drag fields to populate the visual.

This month we also bring you a handful of quality improvements to the on-object experience:

  • Bug fix : when working with a non-visual (text box, button, image, shape) the build pane accidentally closing automatically. The build pane now stays open unless explicitly closed regardless of selected item type.
  • Bug fix : style bug where the build pane was showing 5 icons across instead of the usual 6 has been fixed.
  • Bug fix : in some cases, the data flyout was extending beyond the window size making the search box hard to use, this has now been fixed.
  • Enhancement : When choosing a field using the data flyout – you can now click anywhere on the name, not just the checkbox next to it to select the field.

A screenshot of a computer, Data selecting Order Quantity.

5.Enhancement : If replacing a field in a visual that does not use an aggregation or date hierarchy (other dropdowns are disabled), we auto open the data dropdown to save an extra click.

A screenshot of a graph, Data selecting Category.

Power BI Home in Desktop is Enabled by Default  

We are excited to announce the new and improved Power BI Home as the default experience! The Power BI Home has been redesigned to provide a centralized and familiar location for all your Power BI activities within the desktop application. Our aim is to enhance your productivity and make it easier to discover and consume content.

With Power BI Home, you no longer need to navigate through multiple menus or tabs to access your files and reports. This intuitive interface serves as a hub, like other popular office products, where you can effortlessly manage your reports, all from a single location.

Whether you’re a seasoned Power BI user or new to the platform, Power BI Home ensures a consistent and seamless experience across all your Power BI activities.

Now, you can:

  • Initiate a new report directly from the new home screen.
  • Access reports from recommendations that we have curated.
  • Locate your most recent reports through the Quick Access lists.

A screenshot of a computer abilities within the new home screen.

Please continue to submit your feedback directly in the comments of this blog post or in our feedback forum .

Enhanced Reference Layer in Power BI Azure Maps Visual

We’re excited to introduce a significant enhancement to the Power BI Azure Maps visual reference layer feature. In response to valuable user feedback and in alignment with evolving industry standards, we have expanded the capabilities of the reference layer. Now, in addition to supporting the existing GeoJSON format, users can also utilize KML (Keyhole Markup Language) and WKT (Well-Known Text) formats.

We’re also adding URL as a data source alongside file upload. This addition offers users even more flexibility and convenience in importing spatial data into Power BI. Whether your data resides in GeoJSON, KML, WKT, or through a URL link, the Power BI Azure Maps visual seamlessly integrates these formats, ensuring a comprehensive and versatile geospatial analysis experience.

Measure descriptions with Copilot

Add descriptions to your semantic model measures with Fabric Copilot for Power BI! People building reports from your semantic model can see the name and description of your measures, making the description property essential documentation. And Fabric Copilot is here to help!

A screenshot of a computer Description automatically generated

Streamline your semantic model documentation by creating measure descriptions with Copilot .

1. Click on the model measure in the Data pane of Model view to see the measure properties .

2. Click on the Create with Copilot (preview) button under the Description textbox.

3. Review the measure description from Copilot, then click Keep it .

4. Now the measure description is in the Description box. Fine tune the description, as needed.

5. You update the measure later? No worries, just click the button again when you need the description updated!

Try this out today and let us know what you think! Get started today by turning on this public preview feature in Options > Preview features and learning more about how to get access to Fabric Copilot for Power BI on your tenant at https://learn.microsoft.com/power-bi/create-reports/copilot-introduction#copilot-requirements .

DAX query view improvements  

We released the public preview of DAX query view in November 2023, and in this release, we made the following improvements:

A screenshot of a computer, Boolean values are now showing in the Results grid.

  • A share feedback link has been added in Options > Preview features. We would love to hear your feedback on DAX query view!
  • A bug causing active query tab to stop being highlighted is fixed.
  • A bug with close brackets of a nested IFs DAX formula is fixed.

And we have released additional INFO DAX functions.

  • INFO.CHANGEDPROPERTIES()
  • INFO.EXCLUDEDARTIFACTS()
  • INFO.FUNCTIONS()
  • INFO.LINGUISTICMETADATA()

A screenshot of a computer, we have released additional INFO DAX functions.

Learn more about DAX query view at https://learn.microsoft.com/en-us/power-bi/transform-model/dax-query-view .

SingleStore, we’re thrilled to inform you that our connector has now officially moved out of beta. We want to express our gratitude for your valuable feedback and for being an essential part of our beta journey. Your insights have played a crucial role in shaping the enhancements we’ve made.

This upgrade comes with an exciting new feature – you can now cancel running queries, hassle-free. No more queries running in the background after you refresh the UI/visual or navigate across the pages in the report.

Our team is dedicated to continuously improving and adding even more useful features to enhance your experience. Thank you for your ongoing support, and we can’t wait to continue providing you with top-notch features that elevate your data connectivity and reporting capabilities.

When you add the Power BI add-in to a presentation, you can pick a report suggested to you or paste a link to a specific report.

When you paste a standard report link (the URL copied from the browser address bar), and if sharable links are enabled for your organization and allowed for this report, you have re-share permissions to this report, Power BI add-in can replace the link you pasted with shareable link. In that case you will see a checkbox added below the report URL that offers you automatic access to this report. Just mark this checkbox and Power BI add-in will create a shareable link for you.

Using a sharable link ensures that other users viewing the presentation have the required permission to see the report, and do not need to request access when viewing the presentation.

A screenshot of a computer, Using a sharable link ensures that other users viewing the presentation have the required permission to see the report, and do not need to request access when viewing the presentation.

Have you tried out the new Explore feature yet? This month we added a data overview feature to Explore that allows you to get the “gist” of what your data is all about. Powered by Copilot, data overview gives you a summary of what’s contained in the data you’re exploring and highlights some interesting tidbits to get you started. Let us know what you think!

A screenshot of a computer, Powered by Copilot, data overview gives you a summary of what’s contained in the data you’re exploring and highlights some interesting tidbits to get you started.

Maximum connections per data source  is a setting to configure the maximum number of connections DirectQuery opens for each underlying data source. This controls the maximum number of queries that can be executed concurrently against each data source and is configurable per semantic model.

We recently updated the upper limit of the number of concurrent Direct Query connections allowed per semantic model. The updated limits for each SKU are listed in the table below.

The upper limit for Power BI PPU is 100 active connections. Note that there is no change to the Power BI Pro and Report Server limits and the default maximum value remains as 10 concurrent connections.

Introduced last September, shared device mode is now generally available! With shared device mode, organizations can safely deploy the Power BI mobile app across their pool of shared devices.  Check it out !

This API allows Custom Visuals to store data directly in the local browser. Data stored locally is more secure and improves the performance of web apps. The API will be controlled by a global admin setting. Learn more about the API.

Our February release introduces the support of the new on-object interaction. This enhancement allows users to build and customize visuals directly on the visual in Power BI Desktop. It puts common actions for creating and formatting visuals on the visuals themselves, actions such as adding fields, changing visualization types, and formatting text.

The primary objective of Power BI Desktop developer mode is to provide friendly source control and co-development experience. With this objective in mind, you can now save your Power BI Project files (PBIP) using  Tabular Model Definition Language (TMDL)  format. TMDL has been designed from the ground up to be human-friendly, facilitating not only readability but also easy editing in any text editor. This represents a substantial enhancement for source control and collaborative development experiences, particularly when dealing with complex file diffs.  

Saving as a PBIP using TMDL is currently in preview. Before giving it a try, you must first enable this feature in Preview features: go to  File  >  Options and settings  >  Options  >  Preview features  and check the box next to “Store semantic model using TMDL format”.  

After enabling the preview feature, when saving as PBIP, your semantic model will be saved as a TMDL folder named “\definition” with separate files for each table, perspective, role, culture:  

After enabling the preview feature, when saving as PBIP, your semantic model will be saved as a TMDL folder named “\definition” with separate files for each table, perspective, role, culture: 

You can also upgrade existent PBIP files to TMDL , by just opening them and choosing “Upgrade” when you save:  

You can also upgrade existent PBIP files to TMDL, by just opening them and choosing “Upgrade” when you save: 

By default, Fabric Git Integration will still use Tabular Model Scripting Language (TMSL) to export the semantic model during the Public Preview. However, if the semantic model is imported into Fabric using TMDL, then Fabric Git Integration will export the definition into Git using TMDL in the event of any semantic model changes in the service.  

Learn more about TMDL in Power BI Project files  here .  

Visualizations

  • Inforiver Analytics+ (Charts+Cards+Tables)
  • Inforiver Premium Matrix / Table
  • Drill Down Donut PRO (Filter) by ZoomCharts
  • Date Picker
  • Enlighten Aquarium
  • Deneb: Declarative Visualization in Power BI
  • Comment – Dynamics 365 Finance business performance planning
  • Reporting – Dynamics 365 Finance business performance planning
  • Variance – Dynamics 365 Finance business performance planning
  • Matrix planning – Dynamics 365 Finance business performance planning
  • Copy – Dynamics 365 Finance business performance planning
  • Table edit – Dynamics 365 Finance business performance planning
  • Graphical planning – Dynamics 365 Finance business performance planning
  • Waterfall-Visual-Extended
  • Processifier Process Mining
  • flashbi fantail
  • Map by Squillion
  • Charticulator Visual Community (View)

New features were added to Multiple Sparklines on Oct 23

  • When you double click a line chart, it will zoom in to screen size of visual and you can then compare it with any other line chart in that column.
  • You can use different colors for each line chart in a field/column.
  • You can insert ratings with bands.
  • You can add beeswarm / distribution microchart.

A screenshot of a graph New features were added to Multiple Sparklines on Oct 23

Once you double click the line chart, it zooms in to the visual size. You can then compare it with another line chart in the same column. This is shown below:

A graph with purple lines Once you double click the line chart, it zooms in to the visual size. You can then compare it with another line chart in the same column.

Download this visual from APPSOURCE

For more information visit https://www.excelnaccess.com/sparklines/

or contact [email protected]

Rose/Donut/Pie Chart is a powerful visual that lets you build four types of charts – a rose, a rose donut, a donut, and a pie chart. These chart types are commonly used to display part-to-whole relationships, proportions of categorical data, and ratios. Each arc represents the ratio from the total for easy comparison.

Key Features:

  • Chart Options: Rose, donut, pie charts with style customization.
  • Data Colors: Choose from 30+ palettes, including color-blind mode.
  • Fill Patterns: Apply patterns or use custom images.
  • Smart Labels: Improve readability with data and leaf labels.
  • Arc Customization: Easily adjust arc radius, padding, and stroke.
  • Ranking: Filter Top/Bottom N, show others intelligently.
  • Center Circle: Multiple layers, text, icons, and images in the center.
  • Mouseover Text: Display dynamic details when hovering over arcs.
  • Image Labels: Integrate dynamic image URLs for enhanced visuals.
  • Conditional Formatting: Detect outliers and set smart rules for measures/categories.

Other features included are annotation, grid view, show condition, and accessibility support.

Business Use Cases: Finance, Healthcare, E-commerce, Education, Customer Demographics

🔗 Try Rose/Donut/Pie Chart for FREE from AppSource

📊 Check out all features of the visual: Demo file

📃 Step-by-step instructions: Documentation

💡 YouTube Video: Video Link

📍 Learn more about visuals: https://powerviz.ai/

✅ Follow Powerviz : https://lnkd.in/gN_9Sa6U

A screenshot of a chart Rose/Donut/Pie Chart is a powerful visual that lets you build four types of charts - a rose, a rose donut, a donut, and a pie chart. These chart types are commonly used to display part-to-whole relationships, proportions of categorical data, and ratios. Each arc represents the ratio from the total for easy comparison.

xViz Gantt Chart by Lumel is a Microsoft Power BI Certified Visual. As the most feature rich Gantt in Power BI – it is widely used across most Fortune 500 companies world-wide.

Why Large Enterprises Choose xViz Gantt Chart:

Real-time Alerts for Project Managers: Leverage Conditional Formatting to receive color-coded alerts and status flags, ensuring timely awareness of schedule delays or progress issues.

Visualize Task Dependencies: Easily identify causes of delays with the ability to plot task dependencies using connectors within the roadmap view.

Adaptable for Different Users: From Stakeholders tracking yearly progress to Project Managers analysing monthly views and Developers scrutinizing smaller time grains with flexibility across three distinct timeline levels.

Strategic Planning with Reference Lines and Ranges: Utilize Reference Lines and Ranges to mark crucial dates, holidays, sprints, or deadlines across projects.

Customization Galore:   Wide range of customizable options, including adjustable timeline limits, selectable week start days, and indentation customization for ragged hierarchies.

Hassle-Free Licensing:   The visual is free for use in Power BI Desktop. For sharing & collaborating on Power BI service, the licenses can be purchased directly from Microsoft AppSource.

A screenshot of a computer

Try xViz Gantt Chart today after watching the 2-minute video highlights.

Years ago, Stacey Barr introduced us to the magic of Control Charts. Magic it is, because it allows everyone to split their temporal data in two: random noise and real signals. And we all are looking for real signals, and don’t want to be distracted by random noise.

In our last release of the Control Chart XmR we have added several new features to make it even easier to find real signals and ignore random noise in your data.

First, we added a feature to allow any report consumer to override the applied rules. This allows everyone to analyze the effects of one specific rule or set of rules.

A screenshot of a computer First, we added a feature to allow any report consumer to override the applied rules. This allows everyone to analyze the effects of one specific rule or set of rules.

Several customers asked for a possibility to download the calculated values from the visual. Now you can download all values calculated by the Control Chart XmR, like: LCL, CL, UCL, sigmas and signals.

Don’t hesitate and try the new Control Chart XmR now on your own data by downloading it from the AppSource . All features are available for free to evaluate this visual within Power BI Desktop.

Questions or remarks? Visit us at: https://visuals.novasilva.com/ .

Drill Down Graph PRO lets you create elegant and user-friendly graphs to represent complex relationships between nodes. It’s ideal for both small and large network graphs and offers advanced features like cross-chart filtering and vast customization options. You can create hierarchies and explore them using this visual’s intuitive interactions.

Main features include:

  • Multiple layout options – dynamic, hierarchical, and radial
  • Focus nodes mode – for gradual exploration of graphs.
  • Customization options – choose colors, shapes, images, and labels.
  • Bidirectional links – show reciprocal relationships between nodes.
  • Touch device support – explore your data anywhere.

Popular use cases:

  • IT – asset management, IT infrastructure, IoT monitoring
  • Logistics – fleet management, stock management, parcel tracking
  • Sales & Marketing – community detection, account management, web analytics

ZoomCharts Drill Down Visuals are known for interactive drilldowns, smooth animations, and rich customization options. They support interactions, selections, custom and native tooltips, filtering, bookmarks, and context menu. Use them to create visually appealing and intuitive reports that business users will love on any device.

Get Drill Down Graph PRO from AppSource!

Learn more about Drill Down Graph PRO by ZoomCharts.

A screenshot of a computer ZoomCharts Drill Down Visuals are known for interactive drilldowns, smooth animations, and rich customization options. They support interactions, selections, custom and native tooltips, filtering, bookmarks, and context menu. Use them to create visually appealing and intuitive reports that business users will love on any device.

Have you ever wondered how to collaborate with your colleagues on sensitive data without compromising its security? Do you want to learn how to use Microsoft Purview Information Protection sensitivity labels to protect your data ?

If so, you’re in the right place! We’ll show you how to use sensitivity labels with protection to encrypt and protect your data, and how to enable more users to edit and republish encrypted PBIX files. By the end of this article, you’ll be able to collaborate more securely with your data in Power BI.

Protecting your data with Microsoft Purview

Compliance admins in your organization can use Microsoft Purview Information Protection  sensitivity labels  to manage their org’s sensitive data across different apps and services and meet regulatory and compliance requirements.

They define file protection policies for the sensitivity labels, which result in files being encrypted when such labels are applied, allowing only authorized users to open and edit these files in Office apps and Power BI Desktop.

Sensitivity labels are widely adopted by enterprises today and used to label and protect content in  Microsoft 365  apps such as Excel, PowerPoint, Word, and Outlook, and now in Power BI and Fabric as well.

All you have to do is enable Information Protection in Microsoft Fabric’s Admin Portal and let the labels do the rest.

A screenshot of a computer All you have to do is enable Information Protection in Microsoft Fabric’s Admin Portal and let the labels do the rest.

How Power BI Desktop enforces sensitivity label protection

In Power BI Desktop , we enforce label protection on PBIX files. To open a PBIX file, you either must be the label issuer or have one of the following usage rights .

These usage rights are elevated permissions, as they grant permission to change the sensitivity label. Because Power BI and Office apps use the same label policies, compliance admins may prefer not to grant these usage rights for Highly confidential labels. This might block you from collaborating with your colleagues when you’re sharing or downloading Power BI reports and trying to open them in the desktop app.

Collaborating and keeping label protection on PBIX files

By enabling “ Increase the number of users who can edit and republish encrypted PBIX files (preview) ” in your tenant, users that have been assigned with all of the following usage rights should be able to open, edit, and republish the protected PBIX file to the Power BI service:

  • View Content (VIEW)
  • Edit Content (DOCEDIT)
  • Save (EDIT)
  • Copy and extract content (EXTRACT)
  • Allow Macros (OBJMODEL)

Thus, users who were once restricted can now collaborate with protected files, while keeping protection consistent with the organizational policy.

Note: These usage rights are a sub-set of the “Co-Author” permissions preset in Microsoft Purview compliance center.

What are the restrictions and why?

In order to align with compliance requirements, users with these usage rights are lightly restricted while editing a protected PBIX file.

No exporting to unsupported formats –The user won’t be able to export to formats that don’t support sensitivity labels, such as CSV files.

A screenshot of a computer In order to align with compliance requirements, users with these usage rights are lightly restricted while editing a protected PBIX file. No exporting to unsupported formats –The user won’t be able to export to formats that don’t support sensitivity labels, such as CSV files.

No label change – The user can’t change the label on the PBIX file.

A screenshot of a computer No label change - The user can't change the label on the PBIX file.

Republishing to the original workspace only

Republishing to the original workspace only

Why restrict republishing into the original workspace only?

To remain compliant, we must keep users from gaining more permissions, including Power BI permissions (i.e., Read, Write, Reshare and Build). Meaning that a user who wishes to publish should not be able to publish to a workspace that might grant them additional permissions through Workspace roles.

Additionally, this feature is meant for collaborating and sharing items that are more restricted than usual, and confidential data is usually managed in a dedicated workspace. This restriction will prevent users from publishing confidential data across the tenant.

Side note: The file must be published at least once for other users to be able to republish it to that specific workspace. If the file has not yet been published, then the latest label issuer (the one who set the protected label) or a user with sufficient usage rights must publish it and then share the file with the other editors.

How to enable it

Prerequisite: The compliance admin must assign you and your colleagues the proper permissions for that sensitivity label.

Next, Fabric/Power BI admins must enable the feature in Admin Portal > Information protection > Increase the number of users who can edit and republish encrypted PBIX files (preview).

In Power BI Desktop, users who would like to open and edit protected PBIX files must enable the feature by opening File > Options and settings > Options > Preview feature > Less elevated user support.

Final words

With this new feature, users can now collaborate more easily with other users when working on confidential data in Power BI Desktop, without any loss of protection along the way.

That is all for this month! Please continue sending us your feedback and do not forget to vote for other features that you would like to see in Power BI! We hope that you enjoy the update! If you installed Power BI Desktop from the Microsoft Store,  please leave us a review .

Also, don’t forget to vote on your favorite feature this month on our community website. 

As always, keep voting on  Ideas  to help us determine what to build next. We are looking forward to hearing from you!

  • Microsoft Fabric
  • visual calculations

Sign up for the Power BI Newsletter

Sign up below to get the latest from Power BI, direct to your inbox!

I would like to receive the Power BI newsletter. Privacy Statement.

Participation requires transferring your personal data to other countries in which Microsoft operates, including the United States. By submitting this form, you agree to the transfer of your data outside of China. Privacy Statement .

IMAGES

  1. How to Replace Zeros with Blank Cells in an Excel Worksheet

    change to 0 excel

  2. How to add 0 in Excel before Number

    change to 0 excel

  3. How to Convert negative values to zero in Excel

    change to 0 excel

  4. How to start numbers with 0 (zero) in Excel?

    change to 0 excel

  5. 【How to】 Fill 0 Before A Number In Excel

    change to 0 excel

  6. How to Insert 0 Before Any Number in Excel

    change to 0 excel

VIDEO

  1. How to Add "0" Before Number in Excel| #excel #excel_trick

  2. excel board change

  3. How to Add Zero before Numbers in Excel

  4. Excel Trick

  5. Change Case in Excel without Formula

  6. Stop calculating % change outside of a pivot! Do this Instead!

COMMENTS

  1. Display or hide zero values

    You can press Ctrl+1, or on the Home tab, select Format > Format Cells. Select Number > General to apply the default number format, and then select OK. Hide zero values returned by a formula Select the cell that contains the zero (0) value. On the Home tab, select the arrow next to Conditional Formatting > Highlight Cells Rules Equal To.

  2. 8 Ways to Show Zero as Dash in Excel

    Go to the Number tab in the Format Cells menu. Select the Custom option in the Category section. Enter the #,##0;-#,##0;- format string into the Type field. Press the OK button. The format string used in the custom number format consists of three parts separated by a ; character. The first part determines the format to use for positive numbers.

  3. How to Replace Zeros With Blank, Dash or Text in Excel

    1. Replace Zeros With Blank in Excel Follow the steps below to replace zeros with blanks in Excel. 1. Select the Entire Data in which you want to replace Zeros with blank cells. 2. Click on the Home tab > click on Find & Select in 'Editing' section and select the Replace option in the drop-down menu. 3.

  4. How to Show Dash Instead of Zero in Excel (4 Easy Methods)

    First, we need to select the range of cells where we want to show a dash instead of zero. In our case, the cell range is ( C6:E10 ). Next, we need to press Ctrl+1. A dialog box named Format Cells will appear. Then, we need to go to Accounting in the Format cells panel, select None as Symbol, and press OK. In the end, we will see there is Dash ...

  5. How to Replace Zero with Dash in Excel? 7 Easy Ways!

    In the above table, I have to replace all zero values with dashes. I have to follow the below steps for that. Select all the numbers in our data table. Press "Control + H" to open the "Find and Replace" dialog box. Then, enter "0" in the "Find what:" box and enter "-" in the "Replace with;" box.

  6. How to change

    1. Select the range with the dashes cells you want to change to 0. And click Kutools > Select > Select Specific Cells. 2. In the Select Specific Cells dialog box, select the Cell option in the Selection typ e section, select Equals in the Specific type drop-down list, and enter - to the box. Finally click the OK button. See screenshot: 3.

  7. Leading zeros in Excel: how to add, remove and hide

    Select a cell (s) where you want to show leading zeros, and press Ctrl+1 to open the Format Cells dialog. Under Category, select Custom. Type a format code in the Type box. In most cases, you will need a format code consisting of 0 placeholders, like 00.

  8. Hide Zero Values in Excel

    Click the File tab. Click on Options. In the Excel Options dialog box that opens, click on the 'Advanced' option in the left pane. Scroll down to the section that says 'Display option for this worksheet', and select the worksheet in which you want to hide the zeros. Uncheck the 'Show a zero in cells that have zero value' option.

  9. Dash for a zero in Excel

    Number zero Text Positive number;Negative number;0;Text You also need to specify how the number will be displayed. Use 0 to display all the 0 (important to keep the leading 0) Use # to display the numbers except the 0 if not necessary For instance, if you have the number 123 The code #### ( 4 #) will display 123 in your cell

  10. How to Replace Blank Cells in Excel with Zeros (0), Dashes ...

    Alternatively, you can click the Home tab in the Ribbon and then select Go to Special from the Find & Select drop-down menu. Select Blanks in the Go To Special dialog box and click OK. Excel will select all of the blank cells within the range. Type the value you want to enter in the blanks (such as 0, - or text).

  11. 8 Ways to Show Zero as Blank in Excel

    Click on the Launch icon in the lower right or the Number section. This will open the Format Cells dialog box which will allow you to adjust a variety of cell formatting options including any number format. 💡 Tip: You can also open the Format Cells menu by using the Ctrl + 1 keyboard shortcut. Go to the Number tab in the Format Cells menu.

  12. microsoft excel

    6 Answers Sorted by: 15 If you only want to change the formatting, not the content, of the cells you should look at the Accounting format type, but with no symbol selected. All cells with a value of 0 will be displayed as - while keeping the value 0 in the cell. But Accounting does introduce some "special" spacing in to the cell.

  13. How to Replace Blank Cells With 0 (Zero) in Excel & Google Sheets

    Replace Blank Cells With Zero in Google Sheets. Select the range where you want to replace each blank with zero (B2:B11), and in the Menu, go to Edit > Find and replace (or use the keyboard shortcut CTRL + H ). In the Find and replace window, (1) enter "^\s*$" for Find. In Google Sheets, "^\s*$" stands for a blank value, so type that in ...

  14. How to Convert a Zero to a Dash in Excel

    To convert a zero to a dash in Excel using Find & Replace: Open the spreadsheet containing your data. Highlight all of the data that contains the zeroes you want to convert. In the Editing section ...

  15. Convert negative numbers to zero

    Shortcuts Articles Login You are here Formulas Previous Next Miscellaneous Convert negative numbers to zero Related functions Summary To convert negative numbers to zero without affecting positive numbers, you can use a formula based on the MAX function If the number in column B is negative, MAX returns zero.

  16. How to Replace Blank Cells with Zero in Excel

    The easiest way to replace blank cells with zeros in Excel is to use the Go To Special function. The following example shows how to use this function in practice. Example: Replace Blank Cells with Zero in Excel Suppose we have the following dataset that shows the points scored by various basketball teams:

  17. Fill Blank Cells with 0 in Excel (3 Easy Ways)

    Select the data set where you have the blank cells that you need to fill with 0. Click the Home tab. In the Editing section, click on 'Find & Select'. Click on 'Replace'. This will open the Find and Replace dialog box. Leave the 'Find what' field empty (As we need to find all the cells that are empty/blank) In the 'Replace with ...

  18. How to Calculate Percentage Increase from Zero in Excel (4 Methods)

    We can use the IF function to calculate the percentage increase from zero in Excel. First, select cell D5. Then write down the formula. =IF (C4=0,1,C5/C4-1) Here, we have set the logical test that our initial value (C4) is zero. Now, if the statement is TRUE, the output will be 1, which is 100%.

  19. Assign/Change Yes To 1 And No To 0 In Excel

    We will use IF formula in excel to change the values of Yes to 1 and No to 0. Enter the formula =IF (A1="Yes",1,IF (A1="No",0)) in the cell B1 as shown below in the figure. The First IF formula shown here is checking the value of cell A1 and putting a value of 1 if it is Yes and if the value is not Yes, then the second IF formula is ...

  20. Calculate Percentage Change in Excel (% Increase/Decrease Formula)

    The formula to calculate the percentage increase would be: =Change in Price/Original Price. Below is the formula to calculate the price percentage increase in Excel: = (B2-A2)/A2. There's a possibility that you may get the resulting value in decimals (the value would be correct, but need the right format).

  21. How to fix SPILL Error in Excel Tables (3 easy solutions)

    Note: This auto-correction of formulas is seems to be a new feature, so may not be active in all Excel 365 versions. Fix 1: Change to a non-spillable formula. If an Excel formula can result in more than one value, it automatically spills. Such formulas are called Dynamic Array Excel Functions. A simple example is the SEQUENCE function ...

  22. How to Replace Zeros (0) with Blanks in Excel

    To replace zeros with blanks using the Replace command: Select the cells containing zero values or select one cell only if you want to change the entire worksheet or workbook. Press Ctrl + H. You can also click the Home tab in the Ribbon, click Find & Select in the Editing group and then select Replace from the drop-down menu. A dialog box appears.

  23. Create a form in Word that users can complete or print

    In Word, you can create a form that others can fill out and save or print. To do this, you will start with baseline content in a document, potentially via a form template. Then you can add content controls for elements such as check boxes, text boxes, date pickers, and drop-down lists. Optionally, these content controls can be linked to ...

  24. Microsoft Releases February 2024 Patch Tuesday Updates

    February 2024 Patch Tuesday updates fix 72 vulnerabilities. Microsoft has released updates to address 72 vulnerabilities in Windows, Microsoft Office, Exchange Server, Azure Active Directory, and ...

  25. Chat with RTX Now Free to Download

    Chatbots are used by millions of people around the world every day, powered by NVIDIA GPU-based cloud servers. Now, these groundbreaking tools are coming to Windows PCs powered by NVIDIA RTX for local, fast, custom generative AI.. Chat with RTX, now free to download, is a tech demo that lets users personalize a chatbot with their own content, accelerated by a local NVIDIA GeForce RTX 30 Series ...

  26. State and Local Sales Tax Rates, 2024

    State Sales Tax Rates. California has the highest state-level sales tax rate, at 7.25 percent. Four states tie for the second-highest statewide rate, at 7 percent: Indiana, Mississippi, Rhode Island, and Tennessee.The lowest non-zero state-level sales tax is in Colorado, which has a rate of 2.9 percent.Five states follow with 4 percent rates: Alabama, Georgia, Hawaii, New York, and Wyoming.

  27. Google's Gemini is now in everything. Here's how you can try it out

    It combines the perks of the existing Google One Premium Plan, such as 2TB of extra storage, with access to Google's most powerful model, Gemini Ultra, for the first time. This will compete with ...

  28. Power BI February 2024 Feature Summary

    Version number: v: 2.126.927.0; Date published: 02/16/2024; Contents. ... labels are widely adopted by enterprises today and used to label and protect content in Microsoft 365 apps such as Excel, PowerPoint, Word, ... as they grant permission to change the sensitivity label. Because Power BI and Office apps use the same label policies ...