You may have already noticed that Excel allows you to type just about anything
into your worksheet. Along with text, you can use many different kinds of numbers
such as decimals, percentages, dollar amounts, dates, and more.
To do this, Excel uses a feature called number formats and it's really important
to understand how they work.
Number formats tell Excel exactly what type of data you're using. When you enter
something into a cell, Excel try to apply the right number format automatically.
For example, watch what happens when we enter a calendar day into this sell such
as 1/1/2015 for January 1st.
if you look at the number format drop-down menu on the Home tab, you'll
see that this is now using the date number format.
This tells Excel that we're working with a calendar day and not another type of number.
If you click the drop-down menu, you'll see that there are several other number
format to choose from. The general format at the top is the default number format
for every cell in your spreadsheet. If Excel isn't able to guess what type of
data you're using, it will usually just leave the general number applied to that cell.
You know, in this example, I think I'd like to modify the number format to use
the long date option instead; that way, it'll be easier to read in our spreadsheet.
And there we go!
When you select a cell what number formatting you'll see the actual value
of that cell in the formula bar. So the long date number format hasn't changed
the actual data—it only changes the way it's displayed.
And number formats don't just make our data easier to read. For example, since
Excel understands that this is a calendar date, we can use the fill handle
in the lower right corner of the cell to continue this series of dates.
So now we have January 2nd, January 3rd, and so on.
Definitely a lot easier than entering each of these one by one!
Unfortunately, Excel doesn't always understand what we're trying to type the
same way that a person would. Because of this, Excel requires you to enter your
data in a pretty specific way.
For example, watch what happens when we type January 1st in this cell.
As you can see, the number format has not changed. This cell is still just using the
general number format, and that means Excel didn't understand what we wanted.
It also means our fill handle isn't going to work correctly.
Instead, it just copies the data from this first cell, which is not what we want.
To fix this, we'll need to change this into something Excel can understand, such as 1/1/2015
or just January 1 without the "st".
There we go!
So you can see that Excel has applied a custom date format to this cell and that's because
it understood what we wanted.
And now, just like before, we can see the cell's actual value in the formula bar
and we can even use the fill handle to continue this series of dates.
Let's take a look at another useful, but potentially confusing, number format: percentages.
Remember how we said before that certain data needs to be
entered in a certain way?
Well, when you type a number followed by the percent sign (%) and press Enter,
the percentage number format will be applied automatically to that cell.
In this example, let's say that we're working with an 8% sales tax rate.
As you may remember from math class, percentages can also be written as a
decimal number. For example, if I change the number format for this cell back to
general, it'll appear as 0.08, which is just another way of writing 8%.
I point this out mainly because it's easy to
make a mistake with percentage formatting if you're not being careful.
For example, if we type 8 into an empty cell,
and then apply percentage formatting afterward,
Excel with think that we meant 800%, which which isn't even close to what we wanted!
In our experience, and it's best just to include the percent sign whenever you're
entering a percentage into Excel; that way there's much less risk of making a mistake like this.
Ok, there's just one last thing we'd like to point out. Below the number format drop-down menu,
you'll see these small buttons with a left and right arrow.
These commands allow you to control how many decimal places are displayed in your cell.
As an example, let's say I'm working with a currency value like $19.95.
Now as you can see Excel, applied currency formatting when we included the
dollar sign in this cell.
You can show more decimal places to increase precision or show fewer to
simplify your data or even round to the nearest whole number.
Just like before, we're not changing the actual value of this cell, as we can see
in the formula bar; we're just making it easier to read in our spreadsheet.
Now that you know how number formats work, take some time to ensure that
you're using them correctly in your spreadsheet.