Here is a quick tip to start the week.
Often, we end up with a situation where a bunch of numbers are stored as text.
In such cases, Excel displays a warning indicator at the top-left corner of the cell. If you click on warning symbol next to the cell, Excel shows a menu offering choices to treat the error.
Converting numbers stored as text back to numbers
Tip #1: Error correction
One easy and quick way to convert all the text-numbers to numbers is,
- Select all the cells
- Click on warning indicator next to top-most cell
- Choose Convert to number option
- Done
Tip#2: Paste Special Convert
Sometimes such text-numbers may be scattered across the worksheet, thus making selection of cells a pain.
In such cases, follow this process (see demo below)
- In a blank cell, just type 1
- Copy this blank cell.
- Select all the cells that have text-numbers.
- Press CTRL+ALT+V to launch Paste Special box.
- Choose Multiply from operation area.
- Click ok & Done!
Enjoy your numbers.
Bonus tip: If you think the error indicators are annoying, you can turn them off. Just go to File > Options > Formulas and turn off all the error checking rules you don’t need.
PS: Thanks to Justin for emailing me the Paste Special Multiply with 1 trick.
3 Responses to “Filter one table if the value is in another table (Formula Trick)”
What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.
Good question. You can check for the =0 as countifs result. for example,
=FILTER(orders, COUNTIFS(products, orders[Product])=0)
should work in this case.
PS: I have added this example to the article now.
Hi there!
Could i check if there was a way to return certain fields of the table only?
so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).