OneNote – the Connected Learner

Here is another SWAY showing us how OneNote can be used for collaboration and in the classroom.

Just note that this teacher is using the CLASS NOTEBOOK app that exists within Office 365, and thus there are a few features that don’t exist in the standard OneNote app, such as read only content areas, and private student sections, which exist in the CLASS and STAFF notebook versions.

You’re welcome to work with the CLASS Notebook or STAFF Notebook apps within Office 365 but there’s just a small (ok, not so small) glitch with those: you are unable to transfer the ownership of a CLASS or STAFF NOTEBOOK to another person. This is a big deal to me: I’d like Microsoft to realize people do switch jobs occasionally and that these notebooks need to be transferred in a secure and seamless way. Hopefully they will work on that soon.

In the meantime, if you like what this presentation is about, and you want to use CLASS or STAFF NOTEBOOK apps, just know that while you can back up the Notebook, it loses its connections with the online setup.


How to Turn a Teacher into a OneNote Ninja

As much as I try to explain how OneNote can be used, sometimes you just need examples, and so here is another Pinterest find for me. I thought that it was too good to keep a secret. This is a great use of SWAY to show you how to use OneNote in the classroom.

I have attended a few “Tweet Meets” surrounding use of OneNote in education and it’s amazing how much this tool is being used in the classroom at all levels. Check out this neat SWAY on How to Turn a Teacher into a OneNote Ninja.

OneNote – let me count the ways…

OneNote is my personal favorite application in the Office365 suite.

Back in 2007, when it was just a desktop version, I couldn’t really find a good use for it because it was only on my computer at work or at home, and there was no way to connect them, so they lived in silos. As a result, I left it behind when Evernote came along, because Evernote understood that when we’re out and about is when we need our notes more than if we’re at the house.

Now that OneNote lives online in the cloud, I can access it everywhere from any of my machines, and now it’s become more useful to me. For my personal side of things I am still using Evernote though, just because I spent 5 years creating my notebooks and notes in there, though for work I find OneNote a very strong contender, and with its integration to the rest of the Microsoft Office Suite, it is the clear winner.

In all of my trainings about Office365 I talk about how it’s my go-to program. I love working in it. I love that it saves on the fly, and that it automatically syncs no matter which computer I happen to be using. I love that I can write up instructions for a video in OneNote, and then when I am recording that video I can pull up those instructions on my phone so that I can read them while I am clicking and moving the mouse along. That way I make sure I don’t miss a step and the users get a better quality video out of it.

In the next few posts I’m going to talk about why I love OneNote, and how you can incorporate it in your daily operations, with some exceptional integration with Outlook.


Whereas COUNT counts only the cells that have numbers in them, COUNTA and COUNTBLANK are a little more loose with the definition of what they count. They count all cells and report on the information.


COUNTA counts cells that are not blank.

You might use blank spaces to create a view that separates certain types of data so that it’s easier on the eye, such as the T-shirt example below.

However, if you want to know the total number of sizes and colors, you don’t want to count those spaces.

In that case, you would use =COUNTA(B2:B18), which would give you a number of 15

Excel: COUNTA example


COUNTBLANK does exactly what you would expect it to do: it counts the number of blank cells.

=COUNTBLANK(A1:C18) gives me 6 because I have 6 blank cells.


Many times in Excel you will want to get an idea of how many records you might have, or you want to count certain things within your records. To do that you can use COUNT, COUNTIF, COUNTIFS, COUNTA, and COUNTBLANK.

I will cover COUNTA and COUNTBLANK in another post.


The COUNT formula counts numbers and ignores empty cells, text fields, TRUE/FALSE logical values. It only counts numerical values.

=COUNT(1,3,”VT”,5,8,”Hokie”) would return 4, as there were 4 numbers within those values.

=COUNT(B1:B18) in the T-shirt example below would return 0 as there are no numbers in the B column.

=COUNT(C1:C18) in the T-shirt example would return 15 as there are 15 numbers in the column.

Excel: COUNTA example


The COUNTIF formula gives you a number count for any criteria you set, text or numerical.

For example, Housing and Residence Life might collect data showing incident reports for their residence halls.

Excel: COUNTIF formula example

Using COUNTIF, we can take all the incident reports from all the buildings, and then pull out information by building, by day of the week, or by type of incident.

The way it works, is you first choose the range or values you want the formula to examine, and then select the criteria.

=COUNTIF(range, criteria)

The formula looking at the number of incidents in Lee would read as


You could also type it as =COUNTIF($B:$B,”Lee”)


The COUNTIFS formula lets you set multiple criteria on any cell content, numerical or text.

The way it works, is you first choose the range or values you want the formula to examine, and then select the criteria, then the next range, then the next criteria and so on.

=COUNTIF(range, criteria, range, criteria)

So using the above example, a formula that reads


would return 2 because in the full list of data, two alcohol incidents occurred in the Lee building.

Excel formula: SUM vs SUMIF

Excel is a useful tool any time you want to analyze or manipulate data. I use it regularly and thought I would create a few posts about its usefulness.

Excel uses formulae to perform its calculations. You begin a formula with the equal sign (=) and then you can tell Excel to do a bunch of things, one of which is add numbers in a row or column.

In order to do that, you use the SUM formula.

The SUM formula is generally used on a range of cells.

For example the formula =SUM(A1:A5) will add the first five numbers column A.

If you had the numbers 10, 20, -15, 25, and 30 in the first column, using =SUM(A1:A5) would generate 60 as your total.

Now let’s imagine that you only want the positive numbers to be added. You can use the SUMIF formula to accomplish that. Taking the same numbers above: 10, 20, -15, 25, and 30, using =SUMIF(A1:A5,>0) would generate 85 as your total.

There is a third piece to the SUMIF function that lets you compare non-numerical information, and then add the numbers that match. For example if you had a list of T-shirts in different colors and sizes, and you wanted to grab your totals by size or by color, you would use SUMIF.

Excel: SUMIF function

To give an example, the formula in F4 would read:


So let’s take the parts of that:

  • The first part is – look at the information in column A, which is the color column
  • The second part is – compare that information against E4, which is Maroon
  • The third part is – take the numbers from the quantity column to add up the total number of Maroon T-shirts

Excel: what does the dollar sign do?

Oftentimes when working with Excel, you’ll notice that dollar signs ($) appear in front of, or in the middle of cell references.

For example:

What does Excel do with this information and why do you need it?

It means that the row or column which comes after the dollar sign is anchored or absolute, and therefore will stay the same as you copy and paste formulae to another cell.

Excel by default uses “relative” reference to a cell, which means that the formula changes as you copy it to another cell.

For example, a formula in column D that states =SUM(A1:A5) will become =SUM(B1:B5) when it is copied into a cell in column E.

By using the dollar sign, you can ensure that either the row stays the same, the column stays the same, or that you want a specific cell referenced.

So using the references from the above example:

$A1 means that the formula will always pull information from column A, regardless of which column it appears, but it can change the row depending on where the formula appears.

A$1 means that the formula will always pull from the first row, regardless of which row it appears, but it can change the column depending on where the formula appears.

$A$1 means that no matter where the formula is placed on the sheet, it will always reference the A1 cell.

Why do we care?

We generally care if we’re trying to reference information in a list. We don’t want the number of items changing in the list, so if you have information in rows 1 through 20, then you don’t want the first row to have a formula looking at rows 1:20, and the second row to have a formula looking at rows 2:21, etc. You would want to make sure your range at least has dollar signs in front of the row numbers, so that A$1:A$20 will always pull rows 1:20 from the column.