In excel, if I have blank cells in a particular column of a table, can I then include the whole row with the blank cell in a separate sheet?

Amy Alksnis 20 Reputation points

**See image for a screen shot of the spreadsheet

I am looking to create a separate sheet in the same workbook that only contains orders without invoices. In my invoice column some cells have invoice numbers and some do not. I want to show ship tickets without invoices.Screenshot 2023-11-30 144628

A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,681 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiajing Hua-MFST 7,625 Reputation points Microsoft Vendor

    Hi @Amy Alksnis

    You may try Excel Power Query.

    • Select the data range > Data tab > Get & Transform Data group > From Table/Range. 24
    • Set the filter for 'Invoice Number', select 'null'. 25
    • Click 'Close & Load', the results will load a separate sheet in this workbook.
    • Once there is the change for Invoice Number, you may click Data tab > Queries & Connections > Refresh All. 26

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Barry Schwarz 2,511 Reputation points

    It is pretty simple to do manually:

    • Select the entire region containing your data.
    • Sort the selection using the invoice column.
    • The rows without invoices will appear at the top or bottom of the selection.
    • Select the rows of without invoices.
    • Ctrl-C to copy.
    • Ctrl-V to paste into the new worksheet.
    • If desired, select the entire region and sort on ship ticket to restore the display.

    If this is something you need to repeatedly, record a macro performing these steps.

    0 comments No comments