Learning this function was life changing for me. Truly. Life. Changing. There are so many scenarios where I use it. I’ll walk you through a couple:
Scenario 1:
You get a report that is in a format more appropriate for printing than for Excel; meaning you can’t really sort the data, or apply formulas. There are blank rows and weird headings in the middle. You need to reformat it to get it into more of a table format. In my old life – before my life changed – I would use various formulas to move data around, or scroll around to remove random rows and headings. It took a long time.
Here’s a section of a report from QuickBooks Enterprise – the Open Invoice report – that is not formatted well for analysis:
BEFORE
It’s easy to fix this using “Go To Special”.
Highlight a row – like the header row – that has data in columns you want to keep, and nothing in the columns you want to delete.
Click F5, “Special”, “Blanks”. This highlights only the blank columns.
Now you can copy to just the blank columns or delete them, or whatever you need to do, and the actions only affect the blank rows.
This works for extra rows as well.
Highlight a column that has data in rows you want to keep, and blanks in rows you want to delete.
Click F5, “Special”, “Blanks”. The blank rows are highlighted so you can work with just those
AFTER
Scenario 2:
You’ve used the subtotal function on a large table, and want to copy only the subtotal lines over to another sheet. If you just copy and paste the table after it is collapsed, you will get the entire data set. Instead, use “Go To Special – Visible”.
Highlight the collapsed table, select F5, “special”, “visible cells only”
Copy and paste the table to a new sheet. Now, only the subtotal rows will appear on the new page.
WRONG RIGHT
In this version, where only the visible cells were copied, the outline buttons are gone, and the table is only the subtotal rows.
