How do I hide rows in a table generated from lookup function, where the value returned is zero

Question

in Excel I have an invoice collecting data from a database, using lookup & if functions in a table format. i.e lookup invoice number, show item & qty, etc. How do I hide the rows/items that have zero quantities, so that the table does not have say ten rows, wher only 2 has value in qty column?

Answer 1

To hide rows in an Excel table where the quantity returned from a lookup function is zero, you can use a combination of filtering and conditional formatting. Here’s how you can do it:

1. Use Filtering:
   • Select the header of your quantity column.
   • Go to the Data tab on the Ribbon and click on Filter.
   • Click the drop-down arrow in the quantity column header, uncheck the box for 0, and click OK. This will hide all rows where the quantity is zero.
2. Conditional Formatting (optional):
   • You can also apply conditional formatting to visually highlight or hide rows based on the quantity. Select your table, go to the Home tab, click on Conditional Formatting, and choose New Rule. Use a formula to determine which cells to format, such as =A1=0 (replace A1 with the first cell in your quantity column). Set the formatting to make the text color the same as the background color to effectively hide it.

By using these methods, you can manage the visibility of rows based on the quantity values returned from your lookup functions.