Saturday, 3 April 2021

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: a straight copy of what you first entered.

If you select "Fill Formatting Only" you get:

 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!

Friday, 2 April 2021

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.

Tuesday, 30 March 2021

Excel Hack - Using a "Magic" Apostrophe to Force a Cell to be Text

In Excel you can prefix values in cells with an apostrophe to force Excel to interpret them as text.  Why do this you may ask?

To explain, let's say I want to enter James Bond's telephone number into a spreadsheet.  All I have to do is type 01234007007 (which everyone knows is his number) into a cell, right?  Wrong!  Let's see it in action.  First I type his number (numbers in the UK always start with a 0, that includes MI6 spies):

Because of the leading zero, when I press return, Excel strips the first 0 from what I've typed:

I can get around this by prefixing 007's number with an apostrophe (so a ').  Here we go:

In the image above you can see how the apostrophe is not visible in the cell itself but is in the formula bar at the top right.

Note you also get a little helpful hint warning you there's an apostrophe there.  If you hover over the exclamation mark you get a pop up that says "The number in this cell is formatted as text or preceded by an apostrophe".

So I often use the apostrophe trick for phone numbers or to put formatting in cells that Excel may complain about.  Say I want to write some text in a cell with several sub-points.  You can't do bullet points in a cell so I often prefix lines with a hyphen.  So I type this in:

Hit return and I get:

Oh no!  Because I start with a hyphen, Excel thinks it's a formula with a minus sign, can't interpret the rest of the formula and throws an error.  Put in a magic apostrophe and you get:

Again you can see the apostrophe in the formula bar (top right of the image) but not the cell itself.

Hope that's useful!

Sunday, 28 March 2021

Excel Hack - Concatenating (Adding Together) Text in Different Cells

Sometimes in Excel you have values (generally text) in different cells that you want to piece together into a single cell.  In the example below I have a set of fruits and tasty things I want to join together into a single cell:

Lovely old Excel gives us two ways to do this.  The first way, and the one I always remember, is the "&" operator.  In the example below I've already put the formula in cell C1 and am showing the formula structure in cell C2:

The formula simply means "takes what's in cell A2, add a space to it, then add what's in cell B2".

The second way is the "CONCAT" formula.  Here's an example:

Here the CONCAT formula takes a set of cell references and actual strings and joins them all together into a single result.

For (Geek Dad style) fun I can first transpose the column B values to a row (using this method) and then use mixed references (explained here) to create a crazy set of food ideas:

The mixed references in the formula here are used to keep to column A and row 1 as you copy the formula to other cells.  Specifically:

  • $A2 - Keep to column A as you copy across columns, allow the row to increment as you copy down.
  • B$1 - Allow the column to increment as you copy across, keep the row as 1 as you copy down.

Resulting in:

Comment below if you've used CONCAT or &. 

Excel Basics - Paste Special + Transpose to Turn a Column of Values into A Row

Sometimes in Excel you have values lined up in a single column and you want to get all the values in a single row.  Maybe you want to create a matrix where rows and columns are compared with each other.

Here's an image with made up data to illustrate this:

Let's say I want all the values in column B to actual be across row 1.  So for example "Turnover" goes in cell B1, "Juice" goes in cell C1, "Split" goes in cell D1 and so on.  I could move them one by one but that would be a real pain, especially as the number of values in the column grows.  Instead I use Paste Special and Transpose.  Transpose is the fancy name for changing a row to a column and vice versa.

First, copy the values you want to transpose by selecting them, right clicking and selecting "Copy":

Next select the cell where you want the row of values to be Transposed to. In my case I want the first value in cell B1 so I select that.  I then right click and select "Paste Special...".  The form shown below comes up.  The part of the form I'm interested in is circled:

Tick the "Transpose" box then hit OK.  Lo and behold, what was in the column is now in the row!

You still have your original column of data but you can delete those values and start doing some form of row column comparison!

...and so on!

Note you can also transpose rows to columns.  Comment below if you use this feature!

Excel Hack - Error Checking

A wise person once said "To err is human".  Well Excel sometimes creates errors as well.  Luckily there's a built-in way to check for errors and deal with them.  A common error in computing is dividing by zero.  Here's an example:

Cell C2 shows the formula that's been used.  The "/" part of the formula is how you do divide in Excel and so "=A2/B2" means "take the value in cell A2 and divide it by the value in cell B2".  

You can see how in cell B5 there is the number 0.  In maths (or math for our American friends), a divide by zero is either called infinity or "undefined".  Either way, it can't be computed.  Hence Excel puts an error in a cell where you do a divide by zero, in this case "#DIV/0!".

You can test for a divide by zero using the "ISERROR" formula.  Here's an example:

So the formula "=ISERROR(C2)" simply means "look at the value in cell C2, if it's an error print TRUE, if it's not an error print FALSE".

You can then use an IF formula (see here for an explanation) to test for the error like this:

So the formula above says "look at the value in cell D2.  If it's TRUE print a blank cell, otherwise print the result of the division that was in cell C2.

In the above image I've applied the formula to row 5 where we have the divide by zero and it's come out as blank.  What to set the cell to where there is an error is a design choice.  For example:

  • Where there is a divide by zero error you could set the result cell to 0. However if you're also computing something like an average of the column overall you will get a skewed result.  (The average of 3, 3, 9 and 0 is 5.25, the average of 3, 3, 9 and blank is 7).
  • You could set the cell to something like "ERROR - FIX ME" then you can search and fix these errors.

What I've shown in the image above is something I often do in Excel to make things more maintainable.  Specifically there is one formula per column (so divide in column C, error check in column D, IF statement in column E).  

You can also chain together more than one formula in a cell like this:

In the above formula in column F we have a error check inside an IF statement.  The way to interpret the formula is:

  • So "ISERROR(A2/B2)" to first check if the result is an error.
  • If it is an error, make the cell blank.
  • If it is not an error, compute A2 / B2 and put it in the cell.

It's up to you how you choose to work.  You can use multiple columns to build up the logic one column at a time or do one big multiple formula entry in a single cell.  I choose the multiple columns option as it makes it easier weeks or months later when you come back to a spreadsheet and try and work out what you actually did!

There's also an "IFERROR" formula that does what I've done above in a single formula.  Additionally there's a bunch of other errors you can check for.  Have a play, see what you can come up with and comment below!

Excel basics - The IF Formula

Occasionally when using Excel you want to analyse the value in a cell and either calculate something differently as a result of the analysis or write something to a cell.  To do this, your friend is the "IF formula".  

Here's an example where I want to analyse the values in column A and write "TRUE" or "FALSE" to column B depending on whether the column A number is positive or negative.  In the image below I show some simple data I've setup in column A and where I've started typing the IF formula in column B to show the different elements of the formula:

Taking each element of the formula in turn:

  • logical_test - This is the test you're doing on the values in column A.  So for this formula I'll write "A2>0" meaning "test if A2 is greater than zero".
  • value_if_true - This is what to put in column B if the logical_test is true.
  • value_if_false - This is what to put in column B if the logical_test is false.

Filling out the first formula I now have:

...and copying the formula to the rest of column B I see:

Note how Excel has used it's clever relative references capability to update the formula as it's copied down to refer to the correct row.

So that's it.  Let me know in the comments below how you use the IF formula.