fill blank with the previous row in excel

Posted on 2012-08-29
Last Modified: 2012-09-06
how can create column A from columB but fill the blank in column B

columA             ColumnB

england            england
canada               canada
china                  china
thailand              thailand
* it shold be
Question by:teera
    LVL 26

    Accepted Solution

    Hi teera.

    I'm not sure that I understand what you want, but the steps below describe how to fill in the blanks in Column A...

    (1) In A2 (assuming that's the first blank cell)...
    (2) Copy A2.
    (3) Select from A2 to the last used cell in Column A.
    (4) Hold down the Ctrl button and click on "G". This displays the "Go To" dialogue.
    (5) Click on "Special...".This displays the "Go To Special" dialogue.
    (6) Click on the "Blanks radio button and then on the "OK" button.

    That's it.

    If you want to replace the formulas then...
    (7) Select Column A.
    (8) Copy.
    (9) Right-Click and click on "Paste Special...". This displays the "Paste Special" dialogue.
    (10) Click on the "Values" Radio Button and then on the OK button.

    LVL 33

    Assisted Solution

    Select the column with the blanks.

    Then Goto Special...Blanks...

    Hit F2 and enter this formula with CTRL + ENTER.


    A1 is the first cell with a value, change it if needed.

    Finally copy and paste special values to get rid of the formulas.
    LVL 7

    Assisted Solution

    set a Filter on the range, and in column B untick (Blanks)
    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    If your data starts in row 2 then enter this formula in B2 and then copy it down.

    LVL 16

    Expert Comment

    not sure what you are aiming at.
    It seems your values are identical
    Can you post a sample of HOW the data IS in Columns A and B and How you WANT it to be in Columns A and B?
    LVL 26

    Expert Comment

    Thanks, teera.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now