Turmeric is actually a ginger like rhizome in the raw state. The inside is deep orange yellow in color. The rhizome can be grated (fresh) and may be used in cooking. Raw turmeric is often times ground into a paste and used to improve skin and complexion. It also has very strong medicinal properties.
However in for everyday cooking, the rhizome is dried and ground for the purpose of cooking. It gives the food a yellowish color. Turmeric is also known for its antiseptic qualities.
Grocery stores usually sell the dried powdered form of turmeric.