Now that we take a look at inputting and editing data.
Let's take a look at adding basic formulas. Right here,
I'm going to add a total column where I want to get the total sales value for each product
for both of the months. And here let's add percentage change.
And notice Excel already tries to figure out the type of formatting I want by taking a look at these
cells.
It didn't apply the border to it so I'm just going to do it quickly by using the Format Painter.
I'm also going to do the same thing for these numbers.
Let's just apply the same formatting to these two columns as well.
So click on Format Painter and just draw it out here. To calculate the total
I have different options.
First off, I have to start with an equal sign.
This means I'm going to be doing a mathematical operation in this cell.
Now I need to use my cell references. So I have to go and click on the cell I want to add. I can either
use my mouse and go and click here or I can work with the arrow key. If I click on the left arrow key
I can already see my selection has shifted to B6. I can use the mathematical operation plus and then
use my arrow key again and select a value in C6.
Once I'm happy with my formula I can press enter or control+enter if I want to stay in the same cell.
What this has done is it's taken the value in this cell and added it to the value in C6.
Now check what happens when I go on the fill handle here and I drag this down.
What do you notice here?
It didn't copy 210 down.
Take a look at the formula behind this.
It also didn't directly copy the exact same formulas I had because this cell is not adding B6 and C6.
It looks still the same in the formula bar but that's because my first selection is still the first
cell.
If I click on the second cell notice we see B7 plus C7.
That's the advantage of working with cell references in Excel.
Because when you drag down formulas they adjust accordingly.
Now notice something else happened. When I drag this down
the formatting came with it.
I have an option to reverse that by clicking here and selecting fill without formatting. But when I pull
this down here again we see it update to B8 and C8.
Now another way of writing this, I'm just gonna remove these, is to use the SUM function.
Excel has inbuilt functions that we're going to take a look at in more detail in another section.
One of the simplest and most used functions in Excel is the SUM function.
The advantage of it is that you can use a range.
So instead of clicking on each cell, I can select an entire range. That range can be many cells so in
this case I have these two.
It could also be something like this. So if I just close the bracket when I'm done writing my function
and I press control+enter.
I see, that's the sum of all these values.
There is another way of inputting the SUM function and that's to use AutoSum.
You can see AutoSum.
It's on the Home tab.
It's sitting right here.
So sitting on the most expensive real state because it's a feature that most people need when they
work with Excel.
If you just click directly on AutoSum it tries to create entire formula for you by trying to guess
what numbers you probably need to sum.
And in this case it's guessed correctly.
So I'm fine to click control+enter or enter.
Now did you notice the shortcut is Alt and the equals sign.
So that's another way you can input AutoSum. Go to your cell,
hold down Alt
and type in the equals sign. And then press control+enter to stay on the same cell. Now, to apply this
to the other cells you can either drag this down or you can also double click. What the double click
does is it applies the formula to all the cells that it thinks need applying.
So my values go down to row 9 and it applies the formula all the way down to D9. Again to fill this
without formatting I can select fill without formatting. There is a shortcut that you can use for this.
First you need to highlight the area that you want to copy your formulas to so let's say I didn't
have these values, I'm just going to remove them. I'm going to highlight this area press F2.
So go to edit mode and then press control+enter, that copies your formula without the formatting.
So this just saves you a step so that you don't have to remove the formatting after you've copied down.
Now let's also add
Total here so that we can see the total value by month.
I'm just gonna copy the formatting and apply it to this one.
Let's see the auto sum part in one go.
What was the shortcut key for AutoSum? Alt and the equal sign and that's it.
And it tries to figure everything out by itself.
I'm just going to double check.
It's done it correctly. So it's taking this range to add these values. For this one, it's taking the correct
range.
Let's calculate the percentage change.
Start off with the equals sign.
Now to calculate a percentage change.
I need to get my February value minus the January value and I need to divide this result with my January
value. Because I want the answer of this first before the division I have to keep this part in brackets
and then divided by my January value.
This is not a percent yet so my change is 0.1 but I'm going to turn it into a percentage
in a second.
Next,
I just want to drag this down and let's go and fill without formatting.
I'm going to add a thick border to the bottom.
Now let's take care of the formatting.
So first off I want to have these numbers with a thousand separator.
I can use the number formatting options that are provided here.
So if I click on this, I get the dollar icon.
I get two decimal places and I get the thousand separator there.
If I click this down arrow I can change my currency to other currencies.
If want to remove the decimals, select decrease decimal and increase decimal
If I want to get my decimals back. Another option is to use this comma style number format.
So when I click on it that adds the thousand separator.
It has two decimal places but it doesn't have the currency.
In this case let's say I don't need the currency in there so I'm gonna go with this option and I'm going
to take away the decimal places. From this dropdown here,
You have some more options that you can select from and you can go to more number formats and select
a number format from here.
So what I usually do is I come here to format cells to number formatting and I add a thousand separator
and I take away the decimal places.
You can also select how you want your negative numbers to be shown.
If you want them in brackets, if you want them red in brackets, just red without the minus, or to have
them as just minus and the number and then click on OK.
Now for the percentage, what do I need to select? The percentage number format and that's it.
So you don't have to multiply it by a hundred.
You can just select the percentage number format.
Let's just remove that border we have here.
Just the bottom border.
How do we do it?
I can go back to borders, go to more borders, from here
Just remove the bottom borders.
So either click on this directly here or click on this toggle to take away the border completely and
then click on OK and that borders gone.
Before we wrap up this lecture let's also make the total and percentage change standout.
I'm gonna highlight this.
I also want to make these standout.
I'm gonna hold down to control key and highlight these as well.
I'm gonna switch these to bold. In addition, let's change the alignment of these.
I'm just gonna push them to the right.
So they stand on top of the numbers here.
I can also make these a bit smaller so if I want them to auto fit I just have to double click on the side
to get them auto fit.
I can also expand them a little bit further. And as long as I have all three highlighted it's going to
apply that to the selected columns.
Okay so that's the basics of creating simple basic formulas in Excel and doing basic formatting.
In the next lecture
I'm going to take you through a hidden tip that not so many people know of in Excel.