Here is an interesting question someone asked me recently,
If I have to delete all rows with “John” in it. Do you know how to do it?
Well, it looks like they really hate John. But it is none of my business.
So lets go ahead and understand a dead-simple way to get rid of all cells with John or whoever else you fancy.
- Select all your data
- Press CTRL+F and search for “John”
- Click on “Find all” button
- Now, select the first result.
- Scroll down, holding SHIFT key, select the last result. This will select all search results.
- Close the Find box.
- Press CTRL – (to delete cells)
- Select “Entire row”
- Click ok
- Now poor John is history!
See this demo to understand:
More tutorials on removing stuff…
If you often find yourself with more data than you need & spend a lot of time deleting Johns from your data, then you will find below tutorials very useful.
- Remove duplicate values from Excel [More ways to do it]
- Remove blank rows in your tables
- Extracting specific data using advanced filter
- Remove un-necessary portions form text values
PS: As I was traveling in last 2 weeks, I could not write often. Starting Monday, you will see your favorite awesome Excel tips regularly.
5 Responses to “Preparing Profit / Loss Pivot Reports [Part 2 of 6]”
[...] Preparing Pivot Table P&L using Data sheet [...]
[...] Preparing Pivot Table P&L using Data sheet [...]
[...] Preparing Pivot Table P&L using Data sheet [...]
I am not getting sound from the videos. I have checked all the settings and spent several hours searching the Internet to no avail.
Has anyone else had this problem?
Is there anyway to get the Grand Total to be broken out in the same fashion as the items above it? For instance, if you have in column 1, widget a, widget b, and have their sales by month in column 2, I'd like to see the grand total also be by month, for widget a & b combined.
I can't get anything other than a single line for the grand total, rather than the same format as the data above.
Widget A Month Sales
Jan 100
Feb 200
Widget B
Jan 150
Feb 250
Grand total - here I would also like to have Jan, Feb.
Jan 250
Feb 450