May 15, 2004

Error! Part 2

Conditional Formatting. It can be used for many things. Let's have a look at how we can use it for error spotting.

In a certain range of cells, all numbers entered must be greater than or equal to 0 and smaller than or equal to 10. One possible formula for the Conditional Formatting in cell B1 for example is =OR(B1<0,B1>10)

Hmmm, let's make it a little more interesting and say that all entries must be multiples of 0.5. Here's my modified formula =OR(B1<0,B1>10,ROUND((B1/0.5),0)<>B1/0.5)

There you have it. Any cell that does not fit all of the above criteria will be highlighted like this.

Have a nice weekend.

May 14, 2004

Error! Part 1

There are many ways to show a user that he or she has entered an incorrect value into a cell.

I like to show an example using Data Validation. Let's assume that a person's date of birth must be entered into cell A1 as an entry requirement for a club where membership is restricted to people 20 years old or more.

Here's one possible formula. (I added the ISNUMBER function just in case someone enters something besides a date. Don't forget that dates in Excel are just numbers formatted in a special way)


Open the Validation dialog box from the Data menu at the top and select Custom for the Validation criteria. Then enter the above formula into the text box provided.

Next, select the Error Alert tab at the top and enter your custom Title and Error message.

Here what appears if you make an error.

Although my Error message doesn't really apply if someone has done something like entered text instead of a number, it should still get the point across.

May 13, 2004

Now You See it, Now You Don't

Sometimes you'll find a certain cell or cells are on 'prime real-estate'. You might want to enter a formula in a particular cell, but at the same time you might want that same cell to display a message of some type under certain conditions.

Let's say I want a cell to display the message "Check Payments" 10 days or less before the end of every month. Here's a formula that will do that for me.

=IF(TODAY()>DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)-10,"Check Payments","")

At the same time, I want to enter a different formula into the same cell, but because it's not possible to enter more than 1 formula, how can I overcome this this limitation? By using some Conditional Formatting and a clear AutoShape.

First of all, let's use part of the above formula and insert it in the Conditional Formatting dialog box with some modifications. (You'll notice there is no FALSE in the below formula, it isn't necessary in this case)


Also, all I want is for the background color and font color to change at this time, the "Check Payments" message is entered at a later stage. (I've chosen a black background and black font because I intend to use white text for the clear AustoShape to the "Check Payments" message stand out)

Next, enter the actual formula you want in the cell. Lets say you want the current year, so try =YEAR(TODAY()).

After this, place a rectangular AutoShape over the cell, using the ALT key to align it automatically against the sides of the cell. Use the Fill Color menu to make it clear by selecting No Fill, then hide the lines with the Line Color menu by selecting No Line. After this enter the text "Check Payments" into the AutoShape.

After this make the text of the AutoShape white and you are finished. When the end of the month approaches, this is what you'll see. (The "Check Payments" message has appeared, and as you can see, the =YEAR(TODAY()) formula is still entered in the cell underneath)


May 12, 2004

Getting into Shape, Part 2

Even an artistically challenged person like myself can make reasonably complex drawings with Excel. (Am I being vain?)

One toolbar I always display is the Drawing toolbar. As you can see, I've configured mine with the Group, Ungroup, Bring Forward and Send Backward buttons.

To make a complex drawing, start with component shapes, choosing from the AutoShapes selection. Edit them as you like with features like the Fill Color or Line Color buttons on the toolbar or try right-clicking or double-clicking the shape itself to bring up the Format Object(AutoShape) dialog box. (I've selected Fill Effects, Gradient to give my shapes a metallic look)

Some component shapes

The Format Object (AutoShape) dialog box

Start dragging the shapes together to put them in place while using the Bring Forward and Send Backward buttons to get them in the right perspective, then click the Select Objects button (looks like a mouse pointer on the Drawing toolbar) Sweep around the shapes while pushing your left mouse button or click them one at a time while pushing the Ctrl key.

They should appear something like this.

Now push the Group button to join them together. If you not satisfied, you can use the Ungroup button so that you can move, resize, recolor them at will.

So what is this a drawing of? Actually I have no idea, but I'd say it looks like an automotive part, so let's keep it at that.

May 10, 2004

Getting into Shape, Part 1
Here are 3 simple things you should know when using Excel's multitude of drawing features.

The Ctrl Key
Normally when you 'draw' a shape using Excel, it will expand from the top left corner. However, if you push the Ctrl key while drawing, it will expand from the center outwards. This might come in handy if your are drawing things like circles and want to place the center in a certain position with precision.

The Shift Key
Push this while drawing and it will expand with equal dimensions on either side. For example, an oval will become a circle, a rectangle will become a square. You can also use it to expand (contract) any shape to the same height:width ration by pushing Shift while dragging the corner handles on the shape's sides.

The Alt Key
The Alt key will position your shape against the sides of the nearest cell or cells, either while drawing normally or expanding using the handles on the shape's sides.

Combinations are also possible. Try using Ctrl and Shift or Shift and Alt.

A square aligned against surrounding cells that was drawn while pushing Shift and Alt.

A circle that was drawn while pushing Shift and Alt.

Have fun!

May 09, 2004

Making it all Add Up, Part 2
Previously we looked at SUM and COUNT functions. You can also use SUMIF and COUNTIF to add or count based on criteria of your choice.

Let's say you want to add sales figures from ABC Co., Ltd. Look at cell A13, you can see I have used ranges A2:A11 and B2:B11 with the criteria "ABC Co., Ltd.".

You can also use cell references instead of writing text for the criteria. In this instance, I have used cell C2 to replace "ABC Co., Ltd.".

COUNTIF works the same way as SUMIF but the second range (B2:B11) is omitted.

Simple Arrays
It's also possible to use an array formulas for SUMIF such as =SUM(IF(A2:A11=C2,B2:B11)). Enter the formula then push Ctrl, Shift and Enter at the same time to make it an array formula. (2 braces will appear automatically at either end of the formula)

You make your own combinations such as COUNTIF, AVERAGEIF, MINIF and MAXIF.

Sure beats doing it all the hard way!

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