Category Archives: Interface

Excel Hints and Tips Interface

Listing all names and ranges in Excel #2

Usually, if I write a piece of utility code I check online at some point to see if it hasn’t already been done.  It’s always good to see other ways of doing something.  I try to always write the code myself first as a challenge.

Anyway, yesterday after posting my code about using VBA to list names in Excel I checked and actually found that Excel already has a way to do this built in:-

Click Insert > Name > Paste… and you get this handy little dialog.

Clicking on the ‘Paste List’ button on this form does exactly the same as yesterday’s code, barring making a new worksheet and adding bold and underlined headers!

I guess you do learn something new everyday!

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:-