excel insert null into empty cells

Hello
I am working on a project that is accumulating information into a spreadsheet in excel 2010. The spreadsheet currently has a large number of empty cells. I am exporting the spreadsheet to xml and displaying it as an html page so that other people involved in the project can view the information easily. I wanted to know if there is some formula where I could easily enter null values into the empty cells so that the final html page would display more clearly (null is preferable to large empty spaces).
Thanks,
avirAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gašper KamenšekExcel MVPCommented:
Hi,

here's a fast way,

select all the cells (Ctrl + A) (so the ones with data and the ones where you want the zeros)

Press F5 or Ctrl + G, In the GoTo window select Special and then Blanks. Click Ok.

Now this step is very improtant! Do not click anywhere. Now you have only the blanks selected. You wrute a zero and then press Ctrl + Enter!

This is the fastest and cleanest way of doing that.

Have a nice day and good luck.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
avirAuthor Commented:
Thanks for the quick reply although it doesn't seem to be working. When I have all the blank cells highlighted there is one cell selected (not by me) and that cell gets the value(0) entered and nothing happens to the rest of the highlighted blank cells.
0
Gašper KamenšekExcel MVPCommented:
Yes the zero only goes in that one but you do not confirm that by Enter or Tab but by Ctrl + Enter and it will insert the value in all that is selected. Right after you write 0 you press Ctrl+Enter!

Good Luck
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

avirAuthor Commented:
I'm sorry but when I press Ctrl+Enter nothing at all happens.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

what do you actually want to show in the cells? What do you mean by "null value"? A zero, as in 0??

You could enter a non-breaking space, which, unlike a regular space, will not be ignored in html, but will also not show a character just for the character's sake.

The method described above to select all blank cells is a good way to go, but instead of entering a 0, I'd go for

=char(160)

which will insert the equivalent of   into the cell. So, step by step:

- select all cells in the sheet (or the range you want to use)
- hit F5
- Click Special
- Tick "Blanks" and hit OK
- start typing and enter =char(160)
- hold down the Ctrl key and hit Enter

Now all the previously empty cells will contain the non-breaking space character, ANSI code 160.

cheers, teylyn
0
Gašper KamenšekExcel MVPCommented:
Ok, bare with me. Let's try this, Select cells A1:A10, write 0 and press Ctrl+Enter while the cell is still active! The cursor should still be visible and all the cells still selected with a zero in cell A1!. What you should get is a zero in all the cells you selected.

And the same principle applies in your situation... You select only the blank cells nad then by Ctrl+Enter enter the 0 in all of them...

Best wishes

PS: this way of inserting values is one of Excels greatest gems so the effort now will be re-payed a thousand times :)
0
avirAuthor Commented:
I'm starting to feel like an idiot,  but  Ctrl+Enter is not doing anything at all. I did as you suggested, created a separate worksheet, selected A1:A10, wrote 0 (see attached), pressed Ctrl+Enter and nothing happens.
excel.gif
0
Saqib Husain, SyedEngineerCommented:
Try again,

With the above screen shot exactly as-is

press F2
Then press ctrl-enter
0
Gašper KamenšekExcel MVPCommented:
Yeah, should work...
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
That screenshot shows the cell before the Enter key has been hit (or Ctrl-Enter). It shows the state of the selected cells and the 0 character entered into the first cell. The cell is still in edit mode, since the 0 value is clearly left justified. If it had been entered, it would be right-justified, unless the cells are formatted as text, which is unlikely in a new worksheet/workbook. The other indicator that the cell is in Edit mode is the fact that all the ribbon icons are greyed out. Hence, you have not hit Enter or Ctrl-Enter when the screenshot was taken.

If you select several cells, enter anything and hold down the Control key while you press the Enter key, the value or formula will be entered in all the selected cells. You need to HOLD DOWN the Control key (it's the one at the very far left or right of the space key) and while you hold it down, press the Enter key.

0
avirAuthor Commented:
I may be feeling like an idiot right now, but I do know where the Control key is...
0
avirAuthor Commented:
Thanks for your help. The F2 and little bit more patience on my part seemed to do the trick.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
On the same row as the space key, at the very left and again on the very right. Just below the Shift key.  

It normally has the letters

ctrl

written on it.

If you're working on a PC, that is. If you're on a Mac, check out http://newmacuser.com/apple-keyboard-symbols/

cheers, teylyn
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You could have spread the love a bit wider, especially with all the help required to find the Ctrl key.
0
avirAuthor Commented:
I only have so much love to give, although I really do appreciate all that help with the Ctrl key. I wonder how I managed up until now.
0
keyuCommented:
GasperK is right

i have also tried the same and it working perfectly fine.

1) select cell area that you want to check

2) press f5 one window will appear

3) select "special" button

4) select blanks radio button

5) Press "ok"

6) now whihout clicking any key or unselecting anything just press "0" (Note: it will just place 0 inside selected blank tab)

7) now press ctrl+enter

all blanks will get replaced with 0
0
keyuCommented:
Control key place: shift key will be below enter key and control key will be below shift key
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.