Welcome to Excel Basics video number 17.
And in this video, we got to talk about style formatting.
And we'll do it by hand with cell formatting,
table formatting, and conditional formatting.
And we have seven examples.
And we're going to go all the way from creating accounting
templates with the correct type of borders and dollar signs
to conditional formatting.
All right.
Let's go over to the sheet Accounting Style.
Now here we have Net Sales, Expenses, Total Expenses,
and Net Income.
There are some numbers and formulas here.
And we want to format this in a proper accounting format.
Now I'm going to highlight the months, then hold Control.
Highlight the account categories,
and use Control-B to add bold.
Now sometimes in accounting, we have
a list of expenses, and then total expenses.
And we want to indent the expense
names to emphasize the sales, total expenses, and net income.
Well no problem.
Up in Alignment, there it is.
Perfect for accounting.
Increase indent.
So I'm going to click it once.
Now I want to highlight all of the numbers.
And I'm going to use Control-1 to open up Format Cells.
And on the Number tab, I'm first going to select Currency.
There it is two decimal symbol.
And you can choose your negative number.
Click OK.
Now in many reports, especially in accounting,
we don't want to clutter up every number with dollar signs.
We want the main categories--
Net Sales, Total Expenses, and Net Income--
to have the dollar signs.
But the rest do not need dollar signs.
So we're going to highlight the inside part, Control-1, and now
I'm going to, with currency, come over and change the symbol
to none.
Click OK.
Now another thing that's very important in accounting--
and many reports--
is borders.
Now specifically in accounting, right here
there needs to be a line right at the top
of this highlighted range.
And that line means, please do a calculation on the numbers
above.
This cell right here, of course, is adding the above expenses.
We're also going to need a dark line
at the top of this highlighted range
to indicate that that number comes from calculations
on the numbers above.
But it's not always adding.
F2.
This one is subtracting.
Right?
All right.
So we're going to highlight actually
the label and the four numbers.
Control-1 to open up Format Cells.
Go over to Border.
And we always want to select our line, select the color.
We're not going to select color.
Then you can either use these buttons down here, up here,
or you can simply click in the right location to draw.
Now if you click in the wrong place,
then click it again to get rid of it.
Now actually if you look over here,
we want a dark line at the top.
But I'm going to put a dark line at the bottom too, which
will really serve as the dark line for the next calculation.
So I'm going to click twice.
Click OK.
If we come off to the side, we can see there is our line.
That line right there means adding,
and that line right there means, please
calculate Net Income by Net Sales minus Total Expenses.
Now we highlight the last line.
Control-1.
We select a double line.
And a double line in accounting means, this is the bottom line.
This is the number we are trying to calculate with this report.
Click on the bottom.
Click OK.
Now the bottom line in this report was Net Income.
But other reports, like Balance Sheet,
we have a double line to indicate Total Assets or Equity
Plus Liabilities.
In Cost of Goods Sold or Ending Inventory reports,
we might have a bottom line of Cost
of Goods Sold or Inventory.
And there is our accounting report.
Proper borders and proper dollar signs.
Now I'm going to highlight all this.
And since this is a video about formatting,
right-clicking on the mini toolbar,
one of the most important formatting options--
Format Painter.
I always call it the paintbrush because when I click it,
it loads up my cursor with a paintbrush.
Now you've got to be careful.
Wherever you click, it's going apply.
And since I have a whole range of cells,
I want to make sure and click up in the upper left-hand corner.
And just like that, it applies all
of that beautiful accounting format.
Now I'm going to highlight all the numbers,
and we're going to change this number formatting
to accounting, not currency.
Control-1, Number.
Accounting.
I'm going to select OK.
Now I'm going to do the opposite of what we did up here.
I'm going to highlight just the lines using my Control
key that get the dollar sign.
Control-1, Symbol, Dollar Sign, click OK.
Now we can click off to the side.
So the only difference-- currency, accounting.
And we talked about this difference earlier.
Negative numbers in currency, you
have the choice of how to display them.
In accounting, it's parentheses.
0 is a dash, like we zeroed out the accounts.
Currency, it's a zero.
Dollar sign is floating, however big the number is.
Accounting, it's fixed on the outside.
Now I want to highlight the second report we did.
And right-click on the money toolbar,
I want to double-click Format Painter.
Double-clicking is sometimes hard.
But once you get the double-click,
then your paintbrush is loaded up.
And you can use it over and over.
And I'm going to click once, and then twice.
And just like that, I've applied it twice.
Now I have to turn off the double-click,
so I use the Escape key.
Now I want to show you an alternative for cell border.
So I'm going to highlight both of those rows, Control-1.
And an important border trick is to know to go and click None
to remove all borders.
Click OK.
Now an alternative to cell border is to use underline.
So I selected Other in Total Expense, and Control-1.
And we want to see how to use Accounting Underline.
So in the font group Underline, we can click the dropdown.
And there's Single, Double, Single Accounting,
and Single Double.
I'm going to select Single.
And when I click OK, we can see there
is an underline just on the numbers, not the entire cell.
Now let's highlight the last row, Control-1,
and we're going to select Underline Double.
Click OK.
Now we want to come down and look
at the difference between the Single/Double,
and then the Accounting Single and Double.
Highlight, Control-1, Borders, None.
Click OK.
Now I'm going to highlight Other and Total Expenses, Control-1,
Font, Underline, and Single Accounting.
Click OK.
Now we're going to come to the last row, Control-1,
and Double Accounting.
Now the only difference here is the Accounting
will have the full border when you have that dash.
This one doesn't.
Sometimes, also, the Accounting will give you
just a little bit more breathing room
and is a little bit neater than the Single and Double
without the Accounting.
So there's 1, 2, 3, 4 different income
statements with the correct dollar signs and borders.
One other important accounting trick is sometimes you
want to have a very dark border around the outside,
but only the inside vertical lines you need double.
So no problem.
Control-1.
We have full control over borders in the Border tab.
I'm going to select the very darkest line
and select Outline.
I see around only the outside of the report will get that.
Now I select the Double Line, and I want
to click only the vertical.
When I click OK, just like that I
have my specialized accounting borders.
Now we want to go look at our next example, column width.
Now this is just a quick reminder.
Sometimes people create reports or hand in things on tests,
and they forget to change the column width.
That is just as important as applying the correct style
or number formatting.
Now one problem is if I come up between A and B
and double-click, it best fits the whole column.
So it finds the very widest thing.
So for this first column, I want to just simply click and drag.
Maybe click and drag a little bit.
But for the remaining two columns,
I'm going to select B and C, and simply double-click.
And it will best fit both columns.
That means it'll find the biggest thing in the column
and fit it.
So don't forget column widths.
Let's go over to Wrap Text.
Wrap Text, very important.
Sometimes we want multiple lines in a single cell.
So we can simply highlight, and up in the Alignment group,
there is Wrap Text.
Now that works fine.
And watch this.
If I change the width of this column,
this Wrap Text button is automatic.
Control-Z. But if you want a manual line break,
then let's add it with the keyboard.
I'm going to click before P. Backspace.
And we use Alt+Enter.
to add a manual break.
Now it doesn't matter how wide this is.
It'll always break right after Boomerang.
Also, when you do a manual break,
you actually have to do both.
You have to add your Wrap Text, and then a manual break.
If you were typing it out like over here, Boomerang,
Alt+Enter.
Then as I'm typing, Alt+Enter would properly add a line
break.
But up here, if we didn't have that Wrap Text,
it wouldn't work.
All right.
Now we need to go over to the sheet Table.
And we want to look at up in Styles, Format as a Table.
Now this is sort of deceiving.
And I never use it, especially when
I teach people about the Table feature.
Lots of people click this, and they
don't realize that if you use this style,
it actually converts it to an Excel Table
and has all the benefits that we talked about
in an earlier video.
So if I'm using the styles from here,
it asks me, Format as a Table?
Click OK.
I don't like that.
It should say, Create an Excel Table.
But there it is.
It formats as a Table.
And now it's an official Excel Table.
Don't forget, if you format it as a Table or use Control-T
like we learned earlier in the class,
please come up to Table Tools Design, over to Properties,
Table Name.
Table 2 is not a good name.
And I'm going to call it something like Sales Table.
And remember, no spaces and Enter.
Now if you wanted to format that and customize it,
you can come down and create a new style
and actually build your own style.
We're not going to do that in this class.
Now I want to scroll over a little bit.
Let's go over to the Sheet cell.
And now we want to talk about cell styles.
Now let's just add some formatting to this.
I'm going to use the Font Group.
I'm going to add a dark blue fill, white font, Wrap Text
and Border.
Now I've added some formatting.
And I want to use it over and over in this workbook.
So I can come up to the Styles group,
click the dropdown for more.
And over here we have New Cell Style.
And it already picked up all of the formatting that I've done.
You can click this Format button and use the Format Cells dialog
box to add what you want.
I'm simply going to call this Blue Header or something
like that, and click OK.
And now if I highlight a number of cells,
I can come up and click Blue Header.
Using the Control key, I'm selecting those cells
and adding blue header.
All right.
The last formatting we want to talk about,
and it's on the CF sheet.
And this is the Christmas Tree Light feature in Excel.
Now I would like to add what's called Data Bars--
that means an in-cell bar where the biggest value will
have the longest bar, and the smallest value
will have the shortest bar.
So I select the cells, go up to Home, over
to Styles, Conditional Formatting.
And there's a bunch of amazing built-in rules.
Let's try Data Bars.
Look at that.
You already have a preview, Gradient or Solid.
I'm going to select Gradient.
It And it's totally dynamic.
If I change this to 16,000 and hit Enter,
instantly the bar changes.
Now I'm going to Control-Z to undo that.
Top 25%.
I only want to format the cells where
the values are in the Top 25%.
So I come up to Conditional Formatting, Top and Bottom
Rules, top 10%, and I'm going to select 25.
2, 5.
And I'm not going to limit myself to built-in.
Click the dropdown.
Custom Format we can use on Format Cells dialog box.
Not all the tabs, just these four.
I'm going to say fill with something like yellow.
But you can add whatever you want.
Click OK.
We can also highlight values that are above a hurdle
or below a hurdle or other options to highlight the cells.
Conditional Formatting, highlight Cell Rules, and I'm
going to say Greater Than.
Notice there's Less Than, Between, Equal To,
all sorts of amazing options.
Greater Than.
And I can connect this text box to a hurdle.
That means I'm following Excel's golden rule.
Since that number can change, I put it in a cell,
label it, and refer to it in our text box.
Or, of course, as we've seen earlier, Formulas.
All right.
I'm going to add some custom number formatting.
I'm going to be boring and add that same yellow.
Click OK.
And just like that, only the values above 18,000.
But now I can change this to above 10,000.
And when I hit Enter, poom.
Just like that, the formatting updates.
Control-Z.
And our last conditional formatting,
we want to conditionally format values when there are losses--
that means less than 0.
So I'm going to come up to Conditional
Formatting, highlights Cell Rules, Less Than--
And this number is not going to change so I'm simply
going to type it in--
0.
And whatever formatting you want.
Fill red, font white.
Click OK.
And there are some examples of built-in conditional
formatting.
All right.
So this video we talked about style formatting.
We saw conditional formatting.
We talked about cell formatting.
Over here we talked about table formatting.
Remember, it just really converts it to a table.
We talked about wrap text, both the button and Alt+Enter to do
it manually.
Column widths.
Don't forget to change your column widths.
And over here we talked about some important accounting
styles with borders and dollar signs.
All right.
If you liked that video, click that thumbs up,
leave a comment, and sub.
Because there's always lots more videos
to come from ExcelIsFun, including our next video
Excel Basics 18, where we'll talk about Defined Names.
All right.
We'll see you next video.