Freeze Panes vs. Window Split

Posted on March 20, 2012 by Dan

Often in Excel, you'll want columns or rows to continue to appear when you scroll off to the right or down the page.

You may have a table of employees with names down the left-hand side, with data pertaining the individuals disappearing off the right-hand side of the screen. Scrolling to the right, you'll want the names to continue to show when you hunt across for their NI numbers, stock plans, start dates.

And when you scroll down the page to get to the individual you're interested in, you'll want the column titles to continue to appear, so that you know which column you're looking in.

There are two ways to do this: the right way and the wrong way.

First: the wrong way. A feature has existed in Excel since I can remember called Split. It used to be on the Window menu, and now sits on the View ribbon. As its name suggests, it splits the window. You can either split the screen into two sections (a top and a bottom; or a left and a right); or you can split it into four.

The trouble with this method is that all of the panes are still navigable. So you can scroll up or down, left or right in any of the two or four panes that appear.

Just try it. Try splitting the screen and then do some scrolling in each of the resulting panes. Confused? You betcha.

Another minor annoyance with this technique is that the bars that separate the panes are distressingly ugly.

The Split function does have its uses—it can be useful to show multiple areas of data at the same time, to allow data entry that relies on other data in the worksheet. But more often than not, it's not the right tool.

Now: the right way.

The right way is through Freeze Panes. It can be found right next to the Split function. The main difference between this and the Split function is that with Freeze Panes, you can only navigate in the bottom-right pane. The top section remains frozen, as does the left section. This reduced functionality is a blessing. Trust me on this.

Oh, and the dividers are much more pleasing to the eye.

With either method, you invoke the dividers by navigating to the cell immediately below and immediately to the right of where you want them to appear. With Split, you also have the option of dragging a little divider immediately above the vertical scroll bar, and a similar one immediately to the right of the horizontal one.

VLOOKUP and HLOOKUP

Posted on March 12, 2012 by Dan

VLOOKUP is arguably the most useful power-function in Excel. Time and time again, I call upon its beauty to bring back a value from a simple array of data.

But we shouldn't forget about its lesser-used sibling, HLOOKUP.

First, VLOOKUP. For those not in the know, it allows you to look for a value in a range, and bring back a value associated with that value from the range in which you're looking. So you may want to have a cell in which you select an employee's name from a dropdown and for their salary to appear in the next cell. Or you may want to bring back the stock levels for a range of stock IDs.

Below is the syntax of the VLOOKUP function:

=VLOOKUP(cell,range,number,boolean)

The cell is the thing that you want to look up. The needle, if you will. The range is the area in which you want to hunt for it. The haystack if you will. The number is the column number in the target range that you want to return. And the boolean is an indicator to tell Excel whether to only find exact matches (0) or to also return close matches (1). (In my 16 years of using Excel, I've never once set this value to 1.)

The main two rules to understand are:

  • The thing being looked up must appear in the first column of the range in which you're looking it up
  • The number is the column number containing the desired data. So if your range runs from column B to column G, setting this to 3 will bring back data in column D (column B is 1, column C is 2, column D is 3). It must be positive, so the data you return must be to the right of the column you're looking up in.

So take the following formula:

=VLOOKUP(A1,$G:$Z,4,0)

This will take the value in cell A1, look for it in column G (the first column of the range), and bring back the associated data item from column J (the fourth column of the range). If A1 appears more than once in column G, it will look solely for the first occurrence. If it doesn't appear at all, it will bring back an #N/A error.

The range doesn't have to be a full set of columns, by the way. It can instead be a square range.

So now to the HLOOKUP.

It's exactly the same, apart from rows and columns being switched.

=HLOOKUP(A1,$7:$26,4,0)

This will take the value in cell A1, look for it in row 7 (the first row of the range), and bring back the associated data item from row 10 (the fourth row of the range). If A1 appears more than once in row 7, it will look solely for the first occurrence. If it doesn't appear at all, it will bring back an #N/A error.

I find I rarely use the HLOOKUP function. But it's comforting to know it's there if I might need it. Which I did today.

My friend Will had a bunch of columns representing sequential dates, each row representing a milestone. There would be a single X appearing where those milestones were to hit. He wanted that milestone date appearing as a column next to the milestone name.

The solution involved replicating the date headings at the bottom of the range, given the rule stipulating that the data you need to return must be below the row you're looking up in. We put it in row 100.

And the solution was to look up the value X in the row in which the formula was appearing, and bring back the equivalent value in that newly-created row 100. So in row 5, we put the following formula:

=HLOOKUP("X",5:$100,101-row(A5),0)

Let's break that down.

  • X. This is the thing that is being looked up.
  • 5:$100. This may look odd. It's a reference to rows 5 to 100. The $ against the 100 will keep it fixed when the formula is copied down to subsequent rows. But by not having a $ against the 5, it will float. So copying that same formula down to row 6 will make it change to 6:$100.
  • 101-row(A5). This takes the current row number away from 101. So in this case, yields 96. You want to return the 96th row of data (in this case row 100). By making this a variable, it changes when the formula is copied to the other rows in the spreadsheet. Row 99, for example, will want to bring back the second row's data (i.e. still that in row 100)

So there you have it. VLOOKUP and its lesser-known sibling, HLOOKUP.

COUNTIFS and SUMIFS

Posted on March 8, 2012 by Dan

Yesterday, my friend Alan posed a problem. He wanted to count the number of rows that met criteria in two separate columns. He was frustrated that COUNTIF wouldn't allow him to do that.

Since Excel 2007, there have been a couple of functions that have allowed you to check against multiple criteria being satisfied: COUNTIFS and SUMIFS.

COUNTIFS

COUNTIFS is exactly the same as COUNTIF, except that instead of checking against one range, it checks against many. All of the conditions must be true for the entry to be counted.

For example, in a list of employees you may want to count all people in the New York office earning over $50,000. Or in a risks and issues register, you may want to count all risks with a high likelihood and high impact.

The syntax is as follows:

=COUNTIFS(Range1,Criterion1,Range2,Criterion2,…)

So you basically list the ranges contain the things that you want to check, each followed by the condition that you want to check for. And as long as the areas match up, you're golden.

=COUNTIFS(A:A,"New York",B:B,">=50000")

This will count all rows in which column A contains the value "New York" and column B contains a value in excess of 50,000. Note that any check other than an exact match must be encased in double quotes.

SUMIFS

This behaves in much the same way. And the syntax is only subtly different.

=SUMIFS(SumRange,Range1,Criterion1,Range2,Criterion2,…)

You merely plonk your sum range in front of your list of ranges and criteria.

=SUMIFS(C:C,A:A,"New York",B:B,">=50000")

If stock options are housed in column C, then the above formula will total the stock options for all New York employees earning over $50,000.

Conditional Formatting: How to

Posted on February 28, 2012 by Dan

First: what is Conditional Formatting?

Conditional Formatting is a feature that allows you to change how a cell looks based on the contents of your workbook. You may have a cube of random numbers, and want to shade everything over 0.8. Or you may have a list of employees, and want to embolden those that earn more than £50,000.

Conditional Formatting has been made rather daunting since the launch of Excel 2007.

In the days before 2007, there was a single Conditional Formatting menu option, from which you could do whatever you needed.

The newer version comes with Data Bars, Color Scales and Icon Sets. There's lots of fun to be had in those areas. But 99 times out of a hundred, what we really want is to define the rules ourselves.

And this means that the bottom three menu items are all we need:

  • New Rule…
  • Clear Rules
  • Manage Rules…

If you know how to use the New Rule feature, then the other two become straightforward. So let's dive in. First, highlight the area that you want to be formatted. And on the Home ribbon, hit the Conditional Formatting button in the Styles area.

Again, the pop-up is littered with stuff that you're unlikely to use. Almost always, I resort to one of two menu items here:

  • Format only cells that contain
  • Use a formula to determine which cells to format.

The first of these options allows you to format each cell based on its contents. The latter allows you to format each cell based on the contents of others.

Format only cells that contain

This one's relatively straightforward.

The first dropdown allows you to select attributes of the cell that you want to base your formatting on.

More often than not, you'll choose "Cell Value" here. The Date option can be useful, but otherwise I rarely venture beyond the default.

Once selected, the second dropdown is self explanatory. You select the conditions that need to be met for the formatting to kick in.

Then you select the formats that you want to show when the conditions are met (a richer set than was included in earlier versions of Excel) and you're done.

You can add extra Conditional Formats to allow different scenarios to show different results. Or to allow different cells to be formatted in a multitude of ways. You may, for example, colour numbers under 0.2 green, and those over 0.8 red.

Use a formula to determine which cells to format

This one uses the basics of the above, but takes it to the next level. The key here is that you can format cells based on the contents of other cells. So you can shade an entire row based on the associated value in column K. Or you can insert an upper border if the reference number of the row is different from that in the row above.

(This latter example is useful in visually separating groups of rows. So let's do it.

Let's say I have a sorted list of unique reference numbers with an associated list of fruit.

And I want to insert a rule (or a line, to those of more modest age) in between the 1s and the 2s; the 2s and the 3s and so on. But I want the rule to go across the entire row, not just in between the cells of column A.

To do this, I first highlight rows 2 through 12. Note that the uppermost and leftmost cell highlighted is cell A2. This will appear in the little box above the column A title, just beneath the Clipboard area of the Home ribbon. This is important to note.

Just as before, select the New Rule option from the Conditional Formatting option. But this time, select "Use a formula to determine which cells to format.

Here, you are being invited to type a formula whose answer will be either TRUE or FALSE. If TRUE, then the format will kick in. Otherwise, it won't. It's as simple as that.

In the Formula bar, type the following:

=$A2<>$A1

The formula that you type should relate to that uppermost, leftmost cell, in this case A2. But you should use dollars to ensure that the formula is relevant to the other cells that are being given the formats.

In the above formula, we're comparing the value of A2 with the value of the cell directly above it. If they are different (i.e. the reference number has incremented from that in the previous row), then the formula is true, and the conditional format will kick in. Otherwise, it won't.

A dollar has been placed before each of the references to column A to ensure that the conditions that refer to columns B, C, D etc. still refer back to the values in column A. That is, the only things that are ever being compared are the reference numbers. Dollars have not been placed before the row numbers in the formula to ensure that the row references float. So the formatting in row 8 will be based on a comparison between the reference in row 8 and that in row 7, not those in rows 2 and 1.

And lastly, choose your format. In this instance, we choose an upper border as the format of choice. This puts a rule in between rows 1 and 2; 3 and 4; 6 and 7; 7 and 8; 9 and 10.

Give it a try. It really is rather lovely.

Ever right-clicked and dragged? Start now

Posted on February 20, 2012 by Dan

There are some things that we do intrinsically. And there are some that grate.

When left-clicking your mouse, you're happy to drag. You might want to select a block of cells, highlight a range of text or group select a bunch of objects.

When right-clicking your mouse however, you never drag. It's not the done thing. Instead, you expect a little pop-up menu to appear inviting you to do something to the thing that you've selected.

But there is one specific time when right-clicking can be accompanied with a drag to achieve some wonderful results. But here's a word of warning: it will feel as odd as putting your socks on in the opposite order. (With respect to socks, try it. Go on.)

Type "January 2012" in cell A1. Now hover over the bottom-right corner of that cell and your cursor will turn into what looks like a plus sign.

If you left-click and drag all the way down to A12, then the column will auto-populate with February 2012, March 2012 […] December 2012. Rather neat. Excel does the same with days of the week and dates.

Now instead try doing exactly the same action, but instead of holding down the left mouse button, hold down the right one. It'll feel very odd, as likelihood is you'll never have dragged in that way before.

Drag the corner of cell A1 all the way down to A12. Let go of the right-click and you'll be presented with a pop-up menu.

You're given the option of doing the following tasks:

  • Copy Cells: This will simply copy cell A1 down the column. (You probably didn't want to do this. If you did, then CTRL+C, CTRL+V would work just as well.)
  • Fill Series: This will replicate what would have happened had you left-click-dragged instead. It will use its intelligence to auto-fill the series.
  • Fill Formatting Only: This acts the same as Format Painter. (If you wanted to do this, you'd probably choose to use Format Painter instead, right?)

But then it gets interesting. You're presented with some options that are simply delightful.

  • Fill Days: This will fill with sequential days. So if A1 contained 1 January 2012, it will fill through to 12 January 2012
  • Fill Weekdays. Oh hello! This will skip weekend days. 2, 3, 4, 5, 6, 9, 10, 11, 12 13, 16 January. Neat.
  • Fill Months: 1 January 2012 in A1 will fill with the first of each month thereafter. Cleverly, 31 January 2012 will fill with the last calendar day of each month. Beautiful.
  • Fill Years. You get the picture.

If these options aren't sufficiently varied, then hit "Series…".

This gives you a range of other options, the most useful of which is the step value. If you change this to -1, it will do everything backwards. So you can make it fill with the last day of each calendar month, but going backwards in time. Or choose a step value of 2 to show every other weekday.

The function doesn't only work for dates. You can also use it for numeric series.

So go on. Have a play. And try to get used to right-clicking while dragging.

PureText: the paste-as-text shortcut

Posted on February 13, 2012 by Dan

Last year, I was looking for a tool that could shortcut the "paste as values" command. My requirement fell outside of Excel – I wanted it to paste into WordPress, MS Word, into Google Mail messages etc. I never even thought of using such a tool in Excel.

After a brief search, I found PureText. It is without doubt the neatest piece of software I've installed in the last few years. And it's free.

It's simple. It launches in the background when you start your machine. And the only thing it does is create a shortcut that pastes whatever's sitting on your clipboard as plain text.

So to copy something, you hit CTRL+C in the usual way. But to paste as plain text, instead of hitting CTRL+V, you hit Window+V. (The Window key is present on most non-Mac keyboards, somewhere to the left of the space bar.)

As well as working in the above-mentioned packages, it's also sublime when it comes to Excel. It bypasses the two mouse clicks that you'd usually need to paste values.

If you're a fan of the keyboard shortcut, then this app's just for you.

Don’t over-engineer when industrial strength is not warranted

Posted on February 7, 2012 by Dan

I would estimate that 80–85% of my spreadsheets are throwaway. I create them for a specific purpose: to understand some data; to create an import file for one-off use; to prove someone wrong. (Ha!) After its creation date, I'll never use the spreadsheet again.

The other 15–20% of the time, I'm creating a spreadsheet that will last: a model that will be used to run scenarios; a template for analysis month-on-month; a risks and issues register.

Before you set out developing a spreadsheet, it's important to understand which of the above categories your efforts are likely to fall into. Are you creating it to address a burning issue that will be gone tomorrow? Or are you creating a piece of the furniture that needs to stand the test of time?

If you're doing the former, your formulae don't need to be particularly efficient. Error messages don't need to be hidden. Data doesn't need to be streamlined. And exceptions don't necessarily need to be dealt with.

It's easy to adopt a purist mentality, and make your spreadsheet sing and dance at every turn. But if you don't have to, don't bother. Life's too short.

On the odd occasion, one of the spreadsheets you think is temporary becomes strategic and long-standing. (Every week, I use a spreadsheet that I created in 2004 that I thought would be used once.) If this ever happens, you can rewrite or re-jig it in less time than the time you'll have saved cutting corners on the other ones.

Dating in the new year

Posted on January 13, 2012 by Dan

Some people in the office are whinging about the fact that typing a December date without appending the year defaults to storing it as a 2012 date. They are of the view that Excel should know that they meant December 2011.

You see, if you type a date without bothering with the year, Excel will assume that you mean the current year. So type "12 December" today, and it will store this as 12 December 2012.

I questioned what logic Excel should use. Should it start using the current year in March? June? October? They couldn't answer.

While the behaviour can be a bit annoying early in the new year, there is no better suggestion out there. Maybe Excel ask each user a few questions about what they use Excel for ("do you bill in arrears or advance?") to determine the default behaviour. Or not.

So deal with it.

The odd syntax of the COUNTIF function

Posted on January 12, 2012 by Dan

My friend Sharon yesterday was trying to get her head around the COUNTIF function. Its syntax is odd, so I sympathise. Here's why.

COUNTIF takes two arguments: a range and a condition.

COUNTIF(Range,Condition)

The range is the thing that you'll count a subset of. The condition is used to evaluate whether or not to count each entry in that range. If you have a range containing the values 1, 2, 3, 4 and 5, a condition of greater than or equal to two will yield a count of four, for example.

The range is easy to specify. It works like any range that you'll find in any formula. Use dollars or don't, it's up to you.

The condition is a little more tricky. While I kind of understand why, most people find it odd that the condition must be encased in double quotation marks. So the above condition would necessitate ">=2", including the quotes

=COUNTIF(A:A,">=2")

Not encasing it in quotes would leave symbols that can do dangerous stuff unrestrained in the middle of formulae, which would be tantamount to madness. But on the other hand, I don't think I've ever seen such behaviour in other walks of programming. It's almost as if you want it to print: >=2

It gets even more odd when you need to put arguments into the condition. For example, instead of referring to a 2, you may want to refer to cell A2:

=COUNTIF(A:A,">="&A2)

This concatenates the value of cell A2 on to the end of the ">=" to form the inequality.

Hope this helps in your counting.

Saving a fixed-width import layout

Posted on September 28, 2011 by Dan

Excel has an in-built ability to import fixed-width text files. If you try to open a text file, a wizard will appear asking whether it's fixed-width or delimited.

Delimited files are easily processed. With fixed-width files, Excel has a go at figuring out where to insert the column breaks, but more often than not, you'll have to add some, delete others and move some more to get the desired result.

  • To insert a break, just click where you want it
  • To move a break, just drag it to where you want it
  • To delete a break, double-click it.

The frustrating part about it all is that there's no way of saving the layout that you've defined. If you're importing files of the same layout time and time again, this can be a pain.

But there is a solution.

Create a new workbook containing two sheets. One will house the raw data; the other will split it into columns.

Copy your raw data into the top of the first sheet using the traditional copy/paste method. This will all be stacked up in column A.

In the second sheet, we're going to create a frame for separating the data out into its constituent parts.

In row 3, put your column headers. In row 1 put that column's start position in the file. And in row 2, put the length of the field.

Now we'll use the MID function, together with dollars to hold our references, to put data in row 4.

 

You only need to create the formula in cell B4. Once you've done that, copy it across to the right-most column. Then copy that row down to cover as many records you copied into the original sheet.

I use a spreadsheet just like this regularly, calling it Template. And I pin it to my Excel Recent Documents list so that it's always only two clicks away.