June 05, 2004

Hiding Stuff, Part 2

Hiding and unhiding rows or columns is quite easy. After dragging across your selection with the mouse, you can use the Format menu to select Rows or Columns and then Hide or Unhide, or you can just use the right click menu which is a lot faster.

This is fine for hiding (or protecting) formulas or data on a semi-permanent basis which you do not need access often. But you may find yourself with rows or columns that you want use regularly but still want to hide them to keep them out of the way at least some of the time (not to mention the bother of continuously hiding or unhiding a large number of rows of columns if you are working with a large file) One example I can use is from real experience - I usually refer to my company's part numbers rather than customer's part numbers for ease of inner company communication but on occasion I may want to look at the customer's part numbers to confirm I am referring to the right one. In other words, I want them within easy reach but usually kept out of the way.

There is an easier way! Open the Customize dialog box from the Tools menu and make a toolbar as per this tip. Then on the Commands tab scroll down to the Data menu where you can see the Outline commands as per the below picture and drag the Show Outline Symbol, Group and Ungroup buttons to your new toolbar.

Select the rows you want to hide and then push the Group button (right facing arrow button). You will see Group number buttons appear automatically in the top left corner. You can make groups and subgroups (subgroups being a smaller selection of an existing group of rows or columns).

In the next picture you can see an example. Columns C to E are a subgroup of Columns B to F. You'll notice that Rows 4 to 5 are already hidden. To open or close these rows and columns I can either push the cross buttons to hide or unhide individual groups or subgroups, or I can use the numbered buttons to hide or unhide all of the groups (subgroups) with just one click.

How handy is this? Very! Once you finish with your initial grouping, you will be amazed at how simple and quick it is to hide and unhide the rows and columns you choose. Just use the Show Outline Symbol to access the Outline buttons whenever you want to do so.

June 02, 2004

Hiding Stuff, Part 1

Those Pesky Errors
I don't like errors cluttering up my workbooks so I thought I would mention a way to get them out of sight and out of mind. (Turning my computer off is not an option!)

Here's a quick way using Conditional Formatting. Assuming the error I want to hide is in cell A1, I enter the formula shown below and format using a white font. It will hide errors but not normal imput (numbers or text).

Those Pesky Zeros
These aren't as bad as errors but can still be an eyesore (then again they can be very useful depending on what you are doing). You could use Conditional Formatting to do this but the easiest way is to open up the Options dialog box and select the View tab, then untick the Zero Values checkbox. They're history!

Hiding Everything?
There are many ways to do this. Probably the easiest way is using white as your Font Color thoughout the Worksheet or Workbook, but next time I'll look at a more selective way to only hide what you want to.

Other tips are always welcome!

June 01, 2004

Color Me Whatever

A very simple tip today.

If you are not satisfied with the colors available with Fill Color, Line Color or Font Color, you can change them by navigating to the Options dialog box in the Tools Menu.

Select the Color tab, then choose any color you want to modify (Don't choose a color that you will need later on) Push the Modify button and you will see a range of 127 colors and 15 shades of grey on the Standard tab.

Pick the one you want. The color you orignally selected will now be replaced with the new color. Keep in mind that this change will be limited to your current Workbook. Any changes you make can also be undone using the Reset button, this will revert all colors throughout the Workbook.

If this many colors are still not enough, you can select the Custom tab and make your own. Good luck!

May 30, 2004

Concatenate Concentrate

The CONCATENATE function is used to join text or numbers (known as Strings. It can join up to 30 strings although I suppose you could nest two or more CONCATENATE functions inside one another, but I have more important things to do, such as writing the remainder of this post.

I'll give you 2 examples.

Example 1 (Joining 2 names together)
In range B3:B12 I have a list of first names and in range C3:C12 I have a list of last names. Concatenating makes joining these names together a snap. In cell D3 I enter =CONCATENATE(B3," ",C3) and drag down to cell D12 where the formula will become =CONCATENATE(B12," ",C12)

Note the apostrophe marks, they are used to include spaces or text. Cell references (such as B3) or numbers do not use them. (I could also ampersands to concatenate as in =B3&" "&C3 but I've found after getting used to spelling CONCATENATE, it's easier for me to enter several strings into a formula quickly)

Example 2 (Making an email address)
To do this I am going to use a combination of 3 formulas, CONCATENATE, LOWER and LEFT. Let's start with LEFT.

It seems to me that many email addresses use the first letter of the first name and then all of the last name combined before the @ mark. To get the first letter of Tom in cell B3 I use =LEFT(B3,1). For more letters I can just increase the number after B3, these letters will always be read from the left side, hence the name LEFT

Another simple function, it is used to convert all text to Lower Case so that TSmith will appear as tsmith. (The UPPER function is used for Upper Case)

I should say that I don't really need to use this function as either upper and lower case is still okay for email addresses. I just want to make it easier to recognize an email address as opposed to normal text.

Refer to the above. Now lets look at the entire formula,


This will give me tsmith@andrewsexceltips.com (you'll need to use Hyperlinks to make them 'clickable' email addresses, use Ctrl and K to bring forth the dialog box)

Now that we have a list of email addresses, let's make a directory using a Combo Box. Right click any toolbar and then tick the Forms toolbar.

Drag the Combo Box to your preferred location and then right click it to select the Format Control dialog box.

Your Input Range will be D3:D12. Push the little arrow button on the right side and drag across these cells, the $ marks will appear automatically to make this an 'absolute' reference (look at the Help files to understand if you are not sure what this means)

Next up is your Cell Link. I usually hide these under my Combo Boxes so they don't stand out. You can see that I am using cell D14.

Finally we have Drop Down Lines, I've increased to 10 from a default number of 8 so that my entire list of email addresses can be seen.

When you use the Combo Box, the Cell Link will change. If you select the fourth name from the top (Jane Williams), the value in cell D14 will become 4 (1 for first, 2 for second, 3 for third, etc)

All I need is some way to reference the actual addresses. I've chosen the INDEX function to do this.


The range F3:F12 contains the email addresses, D14 is the Cell Link that will give me the vertical (row) reference as above and 1 is all that is needed for the horizontal reference as we are just dealing with one column.

Simple once you know how.

This page is powered by Blogger. Isn't yours?