jump to last post 1-2 of 2 discussions (4 posts)

How to auto fill all the blank cells with some predefined values in Microsoft Ex

  1. rancidTaste profile image60
    rancidTasteposted 5 years ago

    How to auto fill all the blank cells with some predefined values in Microsoft Excel 2007 or 2010?

    I am working a large excel file which consists many rows and many columns. There are several cells which is totally empty i,e. blank and has no value. I would fill all the blank cells with the value "N/A". How to do this quickly?

  2. Window Pain profile image54
    Window Painposted 5 years ago

    To do it quickly you need to Sort the data. To be able to Sort, the Data must be in a "Table"

    A Table is a group of Data which has continuous Header cells across the top and continuous Row cells along its left side. Any interruption in the Top row and Left column will be seen as the end of the Table.

    IMPORTANT: the row immediately above the Table must be empty. Insert a Row just above the Table. Hide the row if you don't want to see it. (same rule for the column just to the left of the leftmost column, it must be empty)

    Then Sort the Table. (you must 'select' a cell within the Table or Sort won't know what to sort)

    BEFORE YOU SORT, be sure the leftmost column has values that will allow you to Unsort when you're done! Call the first Column 'Unsort' and fill it with numbers 1 to whatever.

    And always SAVE before you Sort, in case it goes awry.


    Sort the Table.

    When all the empty cells are together, enter whatever you want in the topmost empty cell of a column.

    Select the value you have just entered, hold Shift, press End then down arrow, (then Up arrow once to Unselect the next piece of data)

    Ctrl + D is the fasted way to Fill Down the value in the topmost cell of the selection. Repeat for each column.

    Unsort when done using the numerical series in the Leftmost column.

    1. rancidTaste profile image60
      rancidTasteposted 5 years agoin reply to this

      Is there any shortcut to do this. The process is much lengthy!

    2. Window Pain profile image54
      Window Painposted 5 years agoin reply to this

      I could do all the above steps in 90 seconds. Even if you just entered them manually you should have it done by now!

      How big is this spreadsheet?

 
working