You can either watch the video and follow the directions or scroll down and read/look at what to do. If you want to watch the video, you NEED HEADPHONES and should only watch a bit at a time. Watch until I do something, pause the video, and go do what I did. Come back and watch some more after you’re done or review if you’re lost.
Switch to the COUNT tab
Excel can count for you any cells that have numbers in them. On the COUNT sheet, we’ve got a sample of a survey. Every time someone chooses a favourite flavour, we put a 1 in the box, and then we’re going to ask Excel to count those up for us. In this example, it wouldn’t be hard to count them yourself, but again, if the answers changed for whatever reason, now Excel will keep track of those numbers as they change.
First, pretty up your sheet. One thing that we haven’t discussed is how to use a title across different columns.
This one has a title that says Favourite Ice Cream, but right now, it’s only showing in the first cell. I want it to go across four columns.
Select the 4 cells you want to join (A to D)
On the Home tab/menu, you’ll see a Merge & Center button:
Clicking that will make that one cell into a wide cell that spans 4 columns:
For this one, I want to know how many people prefer each type of ice cream. In this case, we could easily count them, but on a bigger sheet, or if the answers were to change, a formula would work much better.
The formula we want on this sheet is called COUNT. This formula will only count NUMBERS. In my weird example, the answer would be the same as if we added them, but even if the numbers were larger, all it would do is count the number of numbers, not add them together.
After the formula, you have to put your cell choices in brackets, so type an open bracket)
Then go select the cells you want to count. For Chocolate ice cream, we’d choose B4 and drag down to B19
Close the bracket to finish the formula
Answer: 
Since we’re applying the same formula to other nearby cells, Fill Handles are your friend!
Your finished formulas will look like this:
Switch to the COUNTIF sheet
Pretty it up!
The COUNT formula we used last time only works with numbers. This time we’re going to count cells with text. The formula is written down below in cell A22
You can write it out or use the miracle of Copy and Paste
But we need to modify the formula as it stands, because right now it won’t do anything. We need to tell it where to look and what to look for.
The first part of the formula inside the brackets tells it where to look, so click right inside the brackets
Then either type in the cells you want to check or just click and drag to select
The second part tells the formula what to look for. In this case, we want to look for the letter c, so inside the quotation marks, type a c
The Vanilla column is basically the same, except it looks in column C and looks for a letter v
And strawberry looks in column D and looks for an s
What if we want to count all of the cells that have a letter in them (or any text, really)? Well, just stick with that asterisk. It’s a “wildcard” that looks for any text at all
So here you can select columns B, C, and D to look for c, v, or s
Your finished sheet should look something like this
With the formulas, it looks like this:
Switch to the CONCAT sheet
Here you will learn to concatenate, or join two things together.
Let’s say for some reason we want to combine whatever is in column A (Name) with whatever is in column B (Favourite) and have column C display them both.
Your formula starter is in cell A26
In cell C4, we want a formula that looks like this:
That says that we want to take whatever is in A4, adds a space ” ” then combines it with B4. Separate the parts with commas.
And your result should look like this:
A4, space, B4
Our old friend the Fill Handle would come in really handy here
Now let’s fill in some totals down below
Start with B22 and count how many cells have text to get our total number of responses
Move down below and count how many cells contain the word chocolate
Then vanilla
and strawberry
Your finished sheet will look like this:
And the formulas look like this:
Move on to the SORT sheet to learn about one of Excel’s other really valuable and important functions
Sometimes you have a list of data and you want to organize it. Right now, I have a list of all of the students in my previous school and it’s organized/sorted by first name
But what if I want to see class lists? Well, all you have to do is go into the Data menu/tab
and click Sort
A box will pop up and it has a few important options
First, make sure you check off My data has headers. This means that we don’t want to sort the first row, so we want to leave it alone
It usually comes up with a suggested sort order, but you may have to click Add Level
The first way we want to sort is by Class/Gender
That will put all of the classes together and the girls and boys together, but we also want them to be sorted by their first name. So the data should be sorted by Class/Gender, Then by Name
Mine came out like this (not sure why)
So I want to select and delete those empty rows
Now we have a list that’s sorted the way we want!
This final sheet is one that I’d like you to do on your own. I decided to be a nice guy (it does happen from time to time) and walk you through an example before you do the last one on your own.
Good luck!
Drop this off in your OneDrive folder, inside the shared folder with your name, when you’re done!