Excel 2013 has a wealth of numeric formatting options which gives users the ability to perform number-crunching easily. In fact, Excel is designed to perform complex calculations, which includes an array of formulas and numbers. Special formatting options allow you to format your tables when certain conditions are met, but in my opinion these simple features are often underused as users either don’t know they are there, or are even a bit scared of trying them out!
In this article, we are going to take a look at a fairly simple table, which consists of various numeric values under different sections, but you will notice that not all of these numeric values are in the correct format, and it doesn’t necessarily look professional or give a good impression, especially if you were sending this back to a customer, or a supplier. Here we’ll take a small glimpse into the multiple numeric formatting options Excel 2013 offers to the users. We will be touching upon the most commonly used functions for numeric and special formatting below:
Working with monetary values:
1. In this example, we have a column that contains salary amounts. We know that salary column should be represented by a currency sign. So to do that, first select the entire salary column, then simply click the ‘currency’ dropdown located under Home tab > Numbers group
2. Within this dropdown, you’ll see a list of frequently used currencies. From here you can either select the English (United Kingdom) currency or English (United States) currency. Hey presto! The symbol will appear next to the numbers. If you don’t see the currency you need in the dropdown menu, you can also choose ‘more accounting formats’ to view other currency formats.
3. Now that we have added the correct currency, you might also need to edit the formatting further. In the example above, you can see that by default the numbers are now displayed correct to 2 decimal places, which isn’t necessary in this case. To access the formatting options just right click on the selected cell and click Format cells… or simply press CTRL+1
5. From the resulting dialogue box, you can access the currency formatting settings by selecting Currency from the Category options. You options will then be displayed in the box to the right. Here you can change the number of decimal places to zero, change the currency symbol if you wish and specify way s to format negative numbers.
Here I am going to choose to display negative values witha minus sign and highlighted in red. This is particularly seful for accounting and reporting so that you can easily identify the negative values in a table of data. Choose your options and click ‘OK’
Next, we’ll look at the special formatting options. In Excel 2013, unfortunately there aren’t any yet any special formats for English use, however for our readers based in the US, you may find soecial formatting particularly useful. As you can see in column B, we have a list of social security numbers, and you can see the hyphens are missing from these numbers. In excel, there’s an yeasy way to format these. Simply select this column and open the dialogue box by simply pressing CTRL+1 or right click and select Format Cells… as we did before
Next, from the dialogue box choose the Special category > Social Security Number. You can see there are also other options you could choose, such as Zip Code or Phone number. When you are done, click ok and you will see that the social security number has now changed into the appropriate format. This will save bags of time and relieves the need for you to individually format such data.
Whilst the special number formatting options aren’t yet available for use in UK formats, there is a way to replicate this yourself using a custom format. Simply follow the steps above to open the format cells dialogue box and choose Custom from the list of categories. You will then see a window where you can enter any possible kind of format you would like, which will be applied to the selected cells.
For example, to format a string of numbers as a UK telephone number, use the format (“0″####) ######
You can also create a number of other weird and wonderful cell formats using the custom formatting option – and that’s a whole other story on its own! Click here to read more about using custom formatting in Excel.