Today’s topic is custom number formats.
Custom number formatting allows you to take control of how your data is displayed.
Showing all the cool things you can do with custom number formatting is a huge topic that is outside the scope of this blog. Therefore, I will be sharing just one of the numerous things you could do with custom number formatting.
Let’s jump straight into the example:
Pretend you are working for “Company Awesome” which expects total sales across its four stores to be $2 million. The percentage sales for each store have already been estimated, you just need to create a table to show these numbers. Here is how you could do it:
Can you tell where the custom number formatting happened? It is cell B2. That cell is actually being linked to by cells C4, C5, C6, & C7 even though it is a mix of numbers and text. Below is the same table in the “show formulas” view (Bonus Tip! Hit Ctrl + ~ to see stuff in formula view).
Notice in cell B2 you no longer see the words “Total sales”. This is because those words are part of the formatting of the cell and not actually in the cell. It’s like in The Matrix. There is no spoon.
Mind=Blown
Here is how to do it:
PC: Sticking with the above example, click into cell B2. In the ribbon click “Home” then in the “Cells” group (far right) click “Format.” In the menu that drops down click “Format Cells.” The keyboard shortcut is Ctrl+1.
Mac: Click into cell B2. In the menu bar click “Format” then click “Cells” the keyboard shortcut is CMD + 1.
You should see a dialogue box pop up. In the tabs along the top, make sure you are on the “Number” tab. In the Category box, click on “Custom.” Your box should now look something like this:
In the box directly below where it says “Type” you are able to click in and type whatever you want. To achieve the numbers plus text trick, delete the text code that is there and replace it with the following:
$#,### “Total sales”
The dollar sign, the pound signs, and comma tell Excel what the number part should look like. To add the text type whatever you want and then make sure it is in quotation marks.
As I said earlier there are a lot of different things you can do with this stuff. For a more comprehensive view of custom number formatting check out this tutorial.
Got something you want me to cover? Tell me in the comments.
Loading ...