I have a report format downloaded data which is difficult to analyse further. I will prefer it to show as tabular data format (by repeating the previous row data) Can anyone advise me how to do it? thanks!



These days, I will use Power Query to clean up data like this. Power Query is a free add in from Microsoft for Excel 2010 and 2013 and built into Excel 2016 as Get & Transform. You can click a few ribbon buttons to fill data down and create logic to remove rows you don't need. The beauty of Power Query is that once the routine is set up, you can repeat it with new data by just refreshing the query.

Without Power Query, I would use the following approach with your data set:

  • Select the first three columns
  • hit F5 to open the Go To dialog and click the "Special" button
  • tick Blanks and hit OK. That will select all blank cells.
  • without changing the selection, type a = sign, then hit the up arrow on your keyboard
  • hold down the Ctrl key and hit Enter

Now you have formulas that reference the cell above in all cells that were previously blank.

Select the first three columns again, copy them and use paste special > Values to overwrite the formulas with their values.

To remove unwanted rows, use a helper column after the Qty column. Enter a formula like =if(isnumber(F3),"","delete me") (assuming that the Qty column is in column F. Adjust as required). Then sort the data by the helper column so all rows with "delete me" are together. Select and delete them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.