Murph,
You could simply type the worksheet name into any cell you choose. Or you can highlight it and cut and paste it into a cell.
If you mean you want to rename the 'tab' itself, just hold your cursor over the tab and right click. Select rename and then type or paste the new name in.
I guess you could rename each tab with colored text once you were done with it ....but I don't know of another way to do that.
Hope this helps.
ps ....i'm not an excel guru by any means ....but I get by.