The other day I had to make an excel sheet for tracking all errors across one of the applications we are doing for our customer. The format was something like this,
We wanted to use a consistent message id format [4 digits: 0001, 0002, … , 1000 etc.]. Now I do not want to type “0001” in excel, instead I wanted to type 1 and I want excel to convert that to 0001 for me. So I started looking for a custom cell format and dug a little deeper to understand those. I thought it would be nice to share them to you all.
First take a look at how the cell formatting dialog box – number tab looks like:
Now apart from the built in types General (leave excel to guess the data format), number, currency, accounting (uses the separators, () notation etc.), date, time, percentage, fraction, scientific, text there are 2 interesting types of formating.
Special: Used for phone number, zipcode, social security number formats depending on the locale you select. For eg. for US they would be phone number [xxx-xxx-xxxx], ssn [xxx-xx-xxxx], zipcode[xxxxx, xxxxx-xxxx].
Custom: Used for creating your own cell formatting structure. This is a bit like regular expressions but in entire microsoftish way. Any cell custom format code will be divided in to 4 parts : positive numbers ; negative numbers ; zeros ; text. If your formatting codes have less number of parts (say 1 or 2 or 3) excel will use some common sense to find out which ones are for what.
Ok, without further confusion, this is probably how you can use the custom cell formatting feature in Microsoft excel.
Some explanation that you can skip if you already get it
- For formatting a number [eg. 1] to fixed number of digits [eg. 0001] you have to use 0000 as the custom formatting code
- For formatting a phone number [eg. 18003333333] to a standard phone number format [eg. 1 800-333-3333] you have to use 0 000-000-0000 as the custom formatting code
- To fill rest of the cell with a character of your choice [eg. *] you have to use @**(this applies for text inputs)
What are your favorite data formatting tricks? [Also read : Creating cool dashboards in excel using conditional cell formatting]
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).