Excel Basics - Autofill

Ever noticed the tiny little square on the bottom right of the rectangle that goes around the cell you have selected?   This is called Autofill and it's super useful!  It's circled in the image below:

So what does it do?  Hover over it and a little black plus appears.  Click and drag it down (or across) and it acts like copy and paste.  Here's a simple example.  I typed 100 in cell A1, clicked and dragged the Autofill control down to A9 and the value 100 was populated in every cell:

But autofill is cleverer than that.  I put a 1 in cell B1, a 2 in cell B2, selected both cells, clicked the Autofill control and dragged down:

Here autofill has said "hang on a minute, there's a 1 in the first row, a 2 in the second row, I'll continue the sequence".

You can use it to Autofill formulae in cells.  In the image below I put "=A1+B1" in cell C1 then used Autofill to copy it down:

Autofill will also make intelligent decisions if you have text in a cell.  In the example below, I typed "Row 1" in cell D1 then Autofilled down.  Autofill decides to increment the number at the end of the text as you go down:

If you've been watching carefully you'll have seen this little icon appear at the bottom of the set of cells you've been using Autofill on: 

Click on it and you get a bunch of different options for how Autofill works.  The example above is "Fill Series".  If you select "Copy Cells" you get:

...so a straight copy of what you first entered.

If you select "Fill Formatting Only" you get:

...so in this case no obvious formatting.  But if you did have some specific formatting in cell D1 it would be replicated to the cells below.

"Fill Without Formatting" looks to give you the same as "Fill Series" but (assuming you had some obvious formatting in cell D1) it would not be replicated down.

The final "Flash Fill" option is very cool.  Say you have set up a worksheet like this; here you have a name of James Bond actors in column A and you've given Excel as hint as to what you want to do with these values in cells B1 and C1:

Autofill down and you get a default "Copy Cells" view:

But then select "Flash Fill" and you get:

Repeat for the second name and you get:

Clever Excel!

Excel Hack - Getting a Newline in an Excel Cell When Entering Text

Excel is best for numbers but sometimes you find yourself entering lots of text in a cell.  To format the text it's helpful to be able to put in a newline character (aka do a carriage return).  So here's how a bunch of text can look without doing this:

Put in newline characters and hey presto, all a lot easier to read:

So how to do this?  The answer is:

1. Position the cursor where you want the newline, 
2. Hold down the alt key, 
3. Press the return key, 
4. Release the alt key.

Here's where to find the alt key:

...and here's return (also known as Enter):

Hope this is useful.