12. TEXT: Format text to be all pretty. Startup and investor excel model training
Intro to Excel model training- TEXT
This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.
This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.
To get the excel example for this and all the other instalments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.
You can join up to the training here.
What’s the point of the TEXT?
In our last class we stuck stuff together using & (ampersand). I alluded to making stuff a bit prettier by including something called TEXT in a GIF so that dates would show.
Since we only stuck stuff together, it was ugly. This class you will do the exercise again but everything is going to look awesome.
You can apply this method to pretty much anything from numbers to dates. Rememeber though, your goal is to get things done. Don’t play with this stuff too much as it’s not a massive use of time. These things should net net save you time.
How do I do these things you speak of?
Well watch the GIFs above to see examples.
Simply put, you do this:
=TEXT(
Cell – what you want to format
” format code ” ) – Between the inverted commas you insert your format code.
=TEXT(Value you want to format, “Format code you want to apply”)
Nothing will work if you don’t put the format between the inverted commas. You will forget this from time to time and yes you will get pissed off 😉
Here are some examples I jacked from Microsoft.
Obviously the numbers in the ‘value you want to format’ are links to cells, to make this valuable…
Some of these examples are totally pointless (for most people) but it serves to show the extent of the possibilities.
| Formula | Description |
| =TEXT(1234.567,“$#,##0.00”) | Currency with a thousands separator and 2 decimals, like $1,234.57. Note that Excel rounds the value to 2 decimal places. |
| =TEXT(TODAY(),“MM/DD/YY”) | Today’s date in MM/DD/YY format, like 03/14/12 |
| =TEXT(TODAY(),“DDDD”) | Today’s day of the week, like Monday |
| =TEXT(NOW(),“H:MM AM/PM”) | Current time, like 1:29 PM |
| =TEXT(0.285,“0.0%”) | Percentage, like 28.5% |
| =TEXT(4.34 ,“# ?/?”) | Fraction, like 4 1/3 |
| =TRIM(TEXT(0.34,“# ?/?”)) | Fraction, like 1/3. Note this uses the TRIM function to remove the leading space with a decimal value. |
| =TEXT(12200000,“0.00E+00”) | Scientific notation, like 1.22E+07 |
| =TEXT(1234567898,“[<=9999999]###-####;(###) ###-####”) | Special (Phone number), like (123) 456-7898 |
Example
The excel example sheet will help you to learn this with an example.
To get the training model, subscribe to the series below.
