The top 10 Excel formatting tips and tricks.
Here are 10 super simple Excel formatting tips and tricks that can save you time when
working with data in your spreadsheet. #1 Pos/Neg Numbers. It's common practice in accounting to
represent positive numbers in black and negative numbers in red inside of parentheses. Let's see
how we can apply that formatting to this list of numbers. I'm going to highlight this, right-click,
and go to Format Cells. That brings up this window. You can get here with the shortcut Ctrl-1
as well. Come down here to Custom and you'll see in the list there's an option right here.
Select that and hit OK and we have our formatting. Now let's take a look at that real quick
and you'll see that it's separated with a semicolon. There are four elements of a custom
format. The first element is what you want to do with positive numbers, the second element is
what you want to do with negative numbers, then what you want to do with zero, and text. So in
this example we're formatting the positive numbers before the semicolon in this type of format which
is just a number with a thousand comma separator and some spacing. The second parameter for
negative is we're going to color it red, inside of brackets and then use the same formatting for the
numbers. We can add a third and a fourth one. Let's just put two semicolons in there with no
values and you'll see what happens when we apply that. That gets rid of the zero value and it gets
rid of the text values. Come back to that list again. We could have said we want zeros to show
up as text - "Zero" and text to show up as nothing and that works this way as well. So you get the
idea about how you can do custom formatting and how you can format positive and negative numbers
in accounting format. #2 Conditional Labeling. You can use this type of custom formatting to do some
basic conditional labeling as well. So let's take this list of numbers. I'm going to hit Ctrl-1 to
go back to that menu, go into Custom, and we're going to change the General format to say this
inside of brackets. If the number is greater than 20 then we want to say "Large".
We'll do a semicolon and anything else we'll say "Small".
Hit OK and you can see that it's labeled based on the value in those cells. Just remember it
breaks down if you try to do complex conditional formatting but it works for basic stuff like this.
#3 Center without Merging. You're probably already aware of the merge and center option.
That's when you have a title like this that you want to span across multiple columns. You click
Merge and Center and it centers it within that span. The problem is it's treated as one cell
and you can't do anything with the individual cells anymore.
There is an alternate method of formatting where you highlight that across that span but select
from the alignment group and then in the alignment menu here choose Center Across Selection. Hit OK
and now it's centered across that span but the individual cells still exist.
#4 Transpose. With transpose you can select a table of data, right-click on it, go to Copy,
select a new target location, right-click,
choose Paste Special, and in the list of formatting options come down here
and select Transpose. You may need to check on the Values or other format first for this
to not be grayed out. Hit OK and it copies that to the new location and swaps the columns and rows.
#5 Special Formatting. Excel also comes with pre-built special formats. Let's take these first
three, hit Ctrl-1 to go to the format menu, choose Special and you can see here it's got types for
zip code, phone number, or social security number. Let's pick phone number and apply that
and you can see it formats that correctly for phone numbers. We'll do the same here
with these special formats for social security number.
So those common special formats are available as well. #6 Hide Zero Values.
There is a quick method to hide the zeros that you have in your data just with formatting.
Let's highlight this and hit Ctrl-1 to go to the formatting menu again. Click on Custom and
you can see that the format is General. Well, as we saw earlier there's four parts to this.
First part is positive, second part is negative. I'm going to skip over the third part which is
the zero and I'm gonna put the fourth part for text as General. That makes the general
format for all the areas except leaving the zeros blank. When you hit OK it gets rid of the zeros.
Just apply that formatting to any group of numbers and it'll hide all the zeros.
#7 Format Painter. A great way to copy formatting quickly is to highlight the area you want to copy
it from and click on the Format Painter. Now you can paint any area you want with that format.
You can also highlight an area and double-click on the Format Painter. That locks it in so that
now you can paint multiple locations and when you're all done click to turn it off.
#8 Clear Formatting. If you've ever highlighted an area and hit Del but it left all the shading
and background formatting there's a quick method to remove that. Let's come back here highlight
that again, go up to the Home tab editing group, and select Clear Formats from the
list. That removes just the formatting. You can also remove the formatting with the data intact
and if you want to remove the formatting and the data both
then highlight it and choose Clear All. That gets rid of everything.
#9 Color Formatting. We saw earlier that you could mark negative numbers red but there are eight
core colors that you can define in the custom formatting. For example, let's do Ctrl-1 to bring
up our formatting. Go to Custom and in this list we're going to put in brackets, the color green.
Hit OK and it marks that as green. These eight basic colors can be defined just by naming them
but there's also 56 other colors in the ColorIndex chart
and that's available on Microsoft site under ColorIndex. I'll put a link in
the description of this video. Let's say we want a color from this list like number 24.
Back in our spreadsheet you just pick any field you want to mark that color, hit Ctrl-1, pick
Custom, and in this case we're going to put in color 24, and hit OK and it marks it that color.
#10 Bullet Formatting. Have you noticed that the Excel menu doesn't have bullet points like
Microsoft word does. Well there is a way you can use formatting in order to insert that
and I want to add a bullet to the beginning of these text items right here. So the first
thing I'm going to do is find the bullet to insert. So I'm going to go over to Symbols
and I'm going to pick from the list something like Dingbats where I can find a bullet point.
Let's pick this one right here and insert it and close that window. Now I have my bullet. I'm going
to copy that with Ctrl-C and then I'm going to go over here and hit Ctrl-1 to bring up my formatting
options. Choose Custom and in the list we're going to Ctrl-V to paste and we've got our bullet point.
Now I'm going to put a space in there between that and the text and I'm going to use the @ symbol
which represents the rest of the text in this list. Hit OK and now you have a bullet point in
front of your text. You can choose different symbols. Just make sure that whatever you do
you use the same font that you have for your text because you can't mix different fonts here.
Thanks for watching the Top 10 Excel Formatting Tips and Tricks. If you're interested in other
Excel tips and tricks be sure to check out the end screen links for my other popular Excel videos.
Hey, if you want to see more videos like this one please subscribe and if you've enjoyed this video
be sure to click the thumbs-up and leave a comment. I really do appreciate your support!