In this lesson we'll study how to format the headers or
the column titles in Excel by freezing the very first row.
We will also use the sum function to sum across a data range and
the sum if function to selectively sum across a data range.
We have the Excel file open, Store Sense.
So this file contains data on sales
made by a store in Canada which sells office supplies only.
So to start, let's increase the zoom level of this file from 100% to 150%.
So at the bottom right hand corner Increase the zoom level to 150 plus.
Next notice that this file has headers.
Every column has a header.
So let's freeze the very first so
what it'll do is it'll help us as we scroll down the file.
So let's select the file, select the first row Go to View,
Freeze Panes, Freeze Top Row.
So what it does now is, it has frozen the top row, so
as you scroll down it's easy to see what that particular column consists of.
We will now calculate the grand total sales made by the store.
Across all this time periods.
With simply as the sum of all the sales in column E.
So let us go at the bottom of this file by pressing End and then down arrow.
So it takes us to the very last row of the file.
Notice we still see the headers, so
we still know that column E is a Sales column.
So let us calculate the sum of this particular column.
Let me put on the title here as Grand Total Sales.
And that is nothing but Sum of column E
to the command to calculate the sum is equal to SUM open parentheses.
Then I select this entire column E.
So one easy way to select that is to put your cursor in the very last
cell of that column we will shift, control, and alt out.
So it selects.
Notice down here its select,
it has selected all the cells from E1 through E8 400.
Now notice the very first cell in E1 is a cell.
We don't really need that.
So let's change it to E2 and then we close parenthesis.
So this is the Grand Total sales made by the store during this time period,
it's about $14.91 million.
Next is calculate total sales, broken up aspect of some segments.
This store categorizes its customers into three segments or four segments,
the Home Office segment, the Corporate segment, Consumer segment and
Small business segment.
So let's calculate sales across these four segments.
So I'll put down Total Sales.
Corporate segment.
Total Sales.
The Home Office segment.
[NOISE] Total sales for
the consumer segment and total sales for
the small business segment.
I can calculate total sales specifically for
the corporate segment we use the sum f command.
So we do is equal to.
Sum f open parenthesis.
Now this command requires three inputs.
The very first input as the range which the IF command refers to,
which is nothing but column I.
So let's select the entire column I.
I put my cursor there.
Shift+Control+up arrow.
I really don't want it to start from I1, so I change it to I2.
So that's my first input.
The range, which the IF command refers to, Second import is the criteria,
so within that range, I wish to sum up sales for the corporate segment.
So I give a quotation mark,
Corporate, close quotation and a comma.
And the last import require is what particular column, or
what particular range needs to be selectively summed.
Which, in our case, is the sales column, which is column E.
So, I select the entire column E, Shift, Ctrl, upper arrow.
Again, I don't need to start from E1.
So I'll change it, because E1 is my header.
I change it to E two and that completes my sum if command.
Close parentheses.
And notice this is the sales during the entire period
specific to the corporate segment.
So this sales is about $5.49 million.
We can also format these numbers by selecting them.
Doing a right click, format cells, choosing the currency format,
and choosing an appropriate format to put the commas in doing an okay.
And maybe increase the size of, spread of the cult.
Formatted as per the dollar format.
We can follow the same process to get total sales for
the home office, consumer and the small business segments.
And you can confirm that the sum of sales for all the four segments taken together
is indeed equal to the grand total sales.