Forum Discussion
How to filter to include blanks without selecting blanks.....
It appears the issue is you have formulas filled down all those rows just to 'accomodate' new data. The way to better handle this is to conver this all to a Table then the Table will automatically expand with a new line and the formulas will automatically be filled down into that new row.
I made that change in your sample sheet so you can check it out.
Also note that best practice would be to update the formulas to use the table based stuctured references. I upgraded the formula in the Total formula accordingly:
=IFERROR([@Quantity]*[@[Price (each excl VAT)]],"£0.00")
It looks 'messy' until you understand it and then it reads much better. Basically the '@' symbol means the corresponding row so simply ... the value in the column called 'Quantity' on this row * the value in 'Price (each excl VAT)' also on this row but if there is an error then return "£0.00" and I "know" this without scanning across the file to file some column E and looking down to the header to see what it is.
As for how to 'update' using the table references (structured reference) you can simply highlight the reference in the formula (e.g. E7) and then click on that corresponding cell(s) and excell will update it for you.
One last note. To update that table, I deleted all the unused rows then clicked on a cell in the table. Then clicked on Home tab and the 'Format as a Table' button and selected a format. It then autoselectes the range and let's you confirm that is the correct range and if the header is in the first row (yes). Lastly, I highlighted those 3 columns with formulas and clicked fill down again to let excel know to use those formulas as the default for those columns (I don't remember needing to do that in the past and/or when using desktop version, but just in case that is what I did here)