Tag Archives: columns

Excel Functions and Formulas

A to Z of Excel Functions: COLUMNS

Yesterday we had the COLUMN function, and today we have the COLUMNS function.  COLUMNS takes a range and returns the number of columns there are in that range.  It looks like this:-

=COLUMNS(range)

It’s really that simple.  Use a range (either a reference or named) as the criteria and COLUMNS will return the number of columns:-

=COLUMNS(A1:C3)
=3

And, just so you get it, here’s another:-

=COLUMNS(M10:Q10)
=5

Got it?  Good.  COLUMNS can also be used to count the number of columns in an array of values.  Obviously these aren’t columns like those found in cells, but the function returns the same.  If, for example you typed:-

=COLUMNS({1,2,3,4;1,2,3,4})

You have asked the COLUMNS function to count how many ‘columns’ of data there are in the array (defined within the curly brackets).  In the example above, the comma symbols separate the columns and the semicolon the ‘rows’ of the array.  Pressing the enter key will return a value of 4, as you’d expect.  I’ll post about arrays at a future time, so you don’t have to worry too much about them for now if what I’ve just said doesn’t make sense.

Excel Interface

Excel: Hiding and unhiding rows and columns

Sometimes if you’ve got a lot of data in an Excel spreadsheet you might find it useful to hide certain content, especially if you’re using cells to perform calculations that you don’t want to appear in a final report.  Excel makes it really easy to hide content in entire columns or rows to this end.  I’ll show you how.

Hiding Columns

To hide columns you need to highlight the columns that you want to hide.  If you want to hide just one column, just click on the column header of the column you want to hide.

Excel Column HeadingIf you want to select multiple columns then click and drag the column header from the first to last column you want to hide.  For example you might click and drag from B to D if those were the columns you wanted hiding.

When you’ve done this, right click anywhere in the selected columns and choose ‘Hide’ from the pop-up menu, as shown:-

Popup menu

And that will do it.  If you were to hide columns B,C and D, the column headers should now look like this:

Hidden Column

You might notice that there is a slightly thicker dividing line between the cells A and E, which shows that they are indeed hidden.

Unhiding columns

Unhiding columns uses more or less the same method as hiding them.  To unhide you need to highlight the columns on either side of the hidden columns.  In this case click and drag from columns A to E shown above to highlight, then right click again.  From the same drop-down menu shown earlier select ‘Unhide’ and your columns will reappear.  Hello, columns!

Hiding and unhiding rows

To do the same with rows you carry out the same steps, except you need to highlight the row headers, shown below:-

Row Header

It really is that simple.

I can’t seem to unhide row 1 or column A.

This one seems to trip a few people up, as there isn’t an actual column before A or row before 1.  It’s actually very easy to do – the box which sits at the top left corner of a worksheet acts in the same way as a row or column header for this purpose.  You just need to start your click and drag to highlight rows and columns from the header to the right or below the box and drag into it.  It might not look like you’ve done much here, but if you right-click and choose ‘Unhide’ your row or column should appear as expected:-

Unhide