In this tutorials we will cover Basics of excel 2016 like Simple fractions and decimal fractions, Currency and accounting format, Custom number formatting and How Excel stores date and time.
Simple fractions and decimals fractions
You can find two types of fractions in Excel: simple fractions (e.g. 4/5) and decimal fractions (e.g. 1.34). In this lesson, I will show you how to use them.
Decimal fraction (12.432) has the decimal part and the fractional part. They are separated by a dot (comma in some countries).
Excel will automatically recognize the value as a decimal as soon as you confirm your entry. By default, when you type a fraction, Excel will treat it as of a General type, so each number will be saved with different precision.
Number of decimal places
If you want these numbers to be treated as decimal fractions, select them and open the Format Cells window (Ctrl + Shift + F). In the Number tab, in the Category box, click Number from the list.
Set the number of decimal places (in this example I used 2 decimal places).
Notice that the value in cell B3 has been rounded. If you click it, you will see that in the formula bar it is still 78.5678. It means that when you change the display precision the information is not lost.
If you want to change the number of decimal places, you can perform the same operation again, this time choosing a different number.
However, there is another, faster method you can use to achieve the same result. You can change precision by using one of two buttons: Increase Decimal and Decrease Decimal. You’ll find them in HOME >> Number.
Select the values from B2 to B4 and click one of the buttons to increase or decrease decimal places.
To enter a simple fraction (e.g. 2 3/5), select a cell and enter the value from the keyboard. After you accept, Excel will display it in the same way in which it was entered. If you click this cell, notice that the value is only displayed as a simple fraction, but Excel still remembers it as a decimal fraction.
When Excel treats the fraction as a date
If you enter a simple fraction which doesn’t contain an integer value (e.g. 2/3), and the cell is of a general type, then Excel will recognize this value as date (February 3). If you want this value to be treated as a simple fraction, you need to do one of two things.
- Before you enter this value, format the cell to the fractional type,
- Enter 0 in the decimal place (e.g. 0 2/3).
Excel converts a simple fraction to the lowest denominator
After you enter the number 1 4/8, Excel will automatically convert this value to the form of the smallest denominator (1 1/2). If you want this value to be stored as 1 4/8, go to the number formatting, click the Fraction category and select As eighth (4/8).
Converting simple fractions to decimal fractions
Simple fractions can be converted to decimal fractions as well as decimal fractions to simple fractions. But in both cases, there may be problems with precision.
First, look how to convert a simple fraction to a decimal fraction.
- 1 2/5 will be changed to 1.4. In this case, both fractions are exactly the same.
- Try to convert 2 2/3 to a decimal fraction. If you choose the Number format, then the value will be converted to 2.67. Notice that when you click this value, in the formula bar it will be stored with much higher precision 2.66666666666667. But even this number won’t be exactly the same as (2 2/3).
Converting decimal fractions to simple fractions
The situation gets even more complicated if you try to change a decimal fraction to a simple fraction. Take, for example, the number 1.2345. Go to Format Cells… (Ctrl + Shift + F). In the Number tab, click Fraction. At the top, you will find three positions: with one, two and three digits in the denominator. The more numbers in the denominator, the greater the precision.
Here are some examples:
Experiment with other positions and check which option in a particular situation is best for you.
Currency and accounting format
When you work with monetary values, there are two different formats you can use: Currency and Accounting. You can find them in HOME >> Number >> Number Format.
You can also find the accounting format under the Accounting Number Format.
When you expand this button, you will see a list of the most popular currencies.
If you need more control over the formatting. For example, you want to set the number of decimal places, you can right-click the cell that contains the value, then select Format Cells….
On the left side, in the category box, you will find two items: Currency and Accounting.
Which type of formatting to choose
Accounting format is based on the currency format. Both allow you to select a currency and set the number of decimal points.
Besides these similarities, there are also a few differences:
- In the accounting format, the currency sign is located just off the left edge of the cell and in the currency format at the beginning of the value.
- In the accounting format, there is an extra space between the value and the right edge of the cell.
- Accounting format displays “-“ when the value is 0.
Custom number formatting
When you have numerical values in your worksheet, and you want to format them in a specific way that is not present in the default format types, you can always create your own by using the Custom Formatting feature.
You probably noticed that such numbers as phone numbers or social security numbers are divided into small portions, usually separated by spaces or hyphens.
That’s because the human brain is actually better in remembering a larger number of digits or characters when they are divided into small groups.
In Excel, you don’t have to create a new format for zip code, social security number or a phone number. But sometimes you need to work with other formats, for example, VAT identification number. You won’t find it in Excel, you have to create your own.
Look at the following example. You have two names with two VAT identification numbers.
To format those number, first select cells C3 and C4, open the Format Cells window (from the contextual menu), and in the Numbers tab select the Custom format. In the Type textbox enter “000-000-00-00” and click OK.
Thanks to the custom formatting we achieved the desired effect. Now, the numbers are easier to read and remember.
How excel stores date and time
If you want to use date and time efficiently in Excel, you first have to learn how Excel stores these values.
At first, it may seem that Excel stores dates and times as text values (for example January 12, 2014). In fact, these values are nothing but numbers, formatted in a way that is easily recognizable by a person.
The date is stored as an integer. Excel starts counting dates from January 1, 1900 24:00:00, so the number 1 is treated as January 1, 1900, 2 is treated as January 2, 1900 and so on. Look at the following example.
January 1, 1900 = 1
January 3, 1900 = 3
February 2, 1901 = 399
March 1, 2014 = 41699
While the date is stored as an integer, time is stored as a decimal fraction. You can find a few examples below.
24:00:00 = 0
12:00:00 = 0.5
11:00:00 p.m. = 0.958333333
11:59:59 p.m. = 0.999988426
When a cell contains both the date and time, then the date will be represented by a total and the time by a fractional part of the number.
March 5th, 1920 13:51 = 7370.577731
December 11, 2000 18:11 = 36871.75803
In next tutorial i will Write about Basics of excel 2016 like Mathematical operations on date and time, Formatting date and time, Saving files, Recovering files, Print Preview. Hope that it helps for you guys.