Excel – Total at the Top

It’s that time of year again. Tax time. Now that I’ve made you sad, lets check out a formula which is very useful when adding up long columns of numbers (deductions anyone?)

Column of numbers, with accompanying total
Put the sum of the column at the TOP of the page, making it easy to find

When you have long columns of numbers of irregular length, it is often easier to have the totals display at the TOP of the page. That way, you don’t have to scroll all over the place to find them. The other advantage of this formula is that you can use it to keep an eye on your total as it accumulates, since you don’t have to know how many rows long the column will be.

The formula here is =SUM(OFFSET(A1,1,0,COUNT($A:$A),1))

Where the OFFSET & COUNT functions are used to create the range that will be summed.

The syntax for OFFSET
OFFSET(reference, rows, cols, [height], [width])
OFFSET(A1,1,0,COUNT($A:$A),1)

  • A1 is the reference cell,
  • Rows – the range begins 1 row down from A1,
  • Cols – the range begins in the same column 0,
  • Height – the number of rows to be included are counted using the COUNT function, COUNT($A:$A) counts the cells in Column A with numbers in them
  • Width – is set to 1 column (column A) wide

I have to say there is something very satisfying about seeing that total increase as each number is entered in the column.

featured image by
Antoine Dautry

Generating Random Numbers and Letters

Need to generate a random number? Excel has two formula variations that can do that for you.

The first is RAND, it generates a random value between 0 and 1. But if you need larger values, try RANDBETWEEN.

RANDBETWEEN generates random numbers between a bottom and top value that you specify.

Lastly, if you need to generate a random letter value – try this formula: =CHAR(RANDBETWEEN(65,90))

In this formula the CHAR function returns the character symbol specified by a number. Letters A-Z are between 65 and 90.

 

 

Flash Fill

Flash Fill is a new tool introduced in Excel 2013. Its a simple tool to handle a frequent problem. You have a small set of data and you need to break it apart into separate columns or join separate columns of data together.

Previously, I would have handled it with a series of text functions in Excel (LEFT, RIGHT, MID) combined with FIND and LEN if the data was complex enough. But if the data set is small, writing a formula sometimes seems like an overly complicated answer to a simple problem (why not just retype?).

Now Flash Fill is stepping in to help you handle this problem. If you give it a series of data (column orientation only) and an example of the pattern you want to extract, it will extract the data for you.

Flash Fill in action
Flash Fill captures the first names only from the adjacent column

You can see once the second name is typed in the column adjacent to the list of full names, Flash Fill is able to see the pattern and offer all the first names in the list. Pressing enter autocompletes the action and the names are filled in. To do this, there can not be more than two blank columns between the source data and the resulting column. You can use the Ctrl + e shortcut to start flash fill.

Flash Fill Icon
Note the Flash Fill Icon displaying after the Ctrl + e shortcut was used.

You can click the Flash Fill icon to display the menu, accepting the suggestions will have all the names autocomplete.

The Flash Fill menu will display if the icon is clicked
The Flash Fill Menu

Here is an even trickier scenario, in the list above some names have two middle initials. Using the “default” flash fill means only the second initial will display in those names. However if I return to any of the names on the list (with two initials) and correct the example to two initials, all of the two initials examples will be extracted.

Two initials with Flash fill
Flash Fill double initial example

I find that seriously impressive.

I can split data in a cell into multiple columns and I can also use Flash Fill to join multiple columns of data together.

Flash Fill concatenation
Joining separate columns using Flash Fill

The same technique used above. Note that I’ve been able to add commas and periods to the text as well.

Formatting via Flash Fill
Using Flash Fill to apply formatting

In the same way, I can use Flash Fill to apply formatting, in this case putting a space between the first and second part of a postal code. You can also use it to format telephone numbers and date information.

PowerPoint – Organizing your colours

I find it useful when creating a presentation that has a custom colour palette to create a custom layout like the one below:

Slide Master containing information about the Colour Palette used in presentation
Colour Palette Assignment Slide Layout – accessible via Master view, or by selecting the layout

You’ll note that the RGB values for the colours are listed, and this is because prior to PowerPoint 2013, the eyedropper tool was not available. I also find it tremendously helpful to note what I use each colour for, so that when I open this file in a couple of years from now there will be a little less detective work.