Why do Number Formats Matter?
When working in Microsoft Excel you may have noticed that the data you enter does not have a Number Format when you first enter it, rather it has a General Format. You may be wondering why it matters what kind of format the data has.
Excel spreadsheets are set out as a grid and can be used for many different purposes. Some people use them for lists, checklists, trackers etc. However, the most frequent use is for making things add up.
Excel can add up numbers that have a General Format however if you want to show Currency or decimals and get them adding up properly then you do need to apply one of the Numeric Formats.
The Number Group
On the Home Ribbon you will see the Number Group. This is where you choose the format for the data you are entering into your spreadsheet, or you can change the format after you have entered the data.
Click the drop-down arrow to see the immediately available options.
|Format||What it does|
|General||No specific format – will add up numbers and display text. Does not display decimals or currency notation|
|Number||12.45 Displays a number with two decimal places|
|Currency||£12.45 Shows the currency symbol and two decimal places – the currency symbol is aligned close to the numbers|
|Accounting||Shows the currency symbol and two decimal places but the currency symbol is aligned to the far left of the cell
|Short Date||12/08/2018 – displays the data like this|
|Long Date||12 August 2018 is how a long date displays|
|Time||12:08:34 displays time in hours minutes and seconds|
|Percentage||12% shows a number as a whole number with a percent sign|
|Fraction||Turns 1/4 into ¼|
|Scientific||Long scientific numbers are displayed using this format|
|Text||Anything that is absolutely Text should be formatted this way.|
|More Formats||Get to even more formats and the ability to customise how certain formas look|
Make your numbers look good
Number formats also help make the spreadsheet make sense so that when you first look at a sea of numbers you can differentiate between them. Everyone knows that £12.45 denotes money whereas 12.45 is simply a number.
The Number Format Icons
Just underneath the Number Format drop down, are five icons. Here is what they do.
|Icon||What it does|
|Choose which currency format you wish to apply. However, be aware that this only displays the currency with the correct symbol, it won’t do any conversions!|
|The percentage format. This add a percent sign to a number. If you have a decimal like .5 it will turn into 50%.|
|The comma format. This applies a comma to separate the thousands. For example –
12344.00 will be 1,2344.00
|Increase and decrease Decimal places. Choose how many decimal places you want to see by clicking on these icons.|
Use Number Formats to make your spreadsheet make sense
When you need to present your spreadsheet to other people, using Number Formats can make all the difference between them understanding your worksheet or not.
To learn more about Number Formats and making your spreadsheets add up, head over to my course Essential Excel Skills for VAs – there is a Windows version and a Mac version. You will find them at https://courses.tomorrowsva.com/