June 30, 2004

Those Pesky Zeros Again

For the last couple of nights I've been lurking around a certain Excel forum, part trying to help, part trying to learn, and part trying not to make too big a fool of myself (one can only wish).

One thing I noticed were a couple of questions about how to make zeros reappear. (Please see here for one way to make them disappear)

Let's say you have a part number that starts with a zero or two or three. With a General format, those zeroes just keep disappearing (the ones that the part number starts with, not ones entered later) Here are some ways to make them come back to life, all shiny and new.

Make Them Text
Just enter an apostrophe in front like this '012345 and you will get 012345. Very simple, very quick.



If you have lots of numbers to enter, right-click all cells that contain or will contain the zeroes, to select the Format Cells dialog box. Then choose the Number tab and push Text, OK. Finished.



One possible problem in both the above solutions is that these part numbers are now text. Although they still look like numbers, they are no longer recognized and are ignored by numeric functions.

Custom Formatting
Here's another way. The part number 012345 has six digits. Using the Format Cells dialog box, this time select Custom on the Number tab and enter six zeroes, 000000. This will force the the number to always appear as six digits regardless if they start with a zero or not. A bonus is that they are still numbers and can be calculated as such.

Last one. One method I thought up (though I'm quite sure others have preceded me) is to merely add the required number of zeros in front, enclosed in quote marks as in "000", and then add a zero behind to make it a number.

This is not a bad format. You can always have the same number of zeros in front, it adapts to any number of digits (although a fixed number digits as above may be what you are after), and still remains a calculable number. You can also do things like "000"0.00 for 2 decimals or "000"#,### (Be aware that the zeroes in front are now treated as text and not affected by the numerical formatting, so commas will not appear in their case)

Hopefully this picture will explain.



G'night!  

Posted by andrewe at 01:46

June 28, 2004

Morse Code Mk. 2

My friend Jan Nordgreen has written a post on his blog jan's diary about my Morse Code demonstration in addition to including his own coding process.

Jan writes "Your morse code program made me think. It made me think how different it is from a normal computer program where the data and program are hidden from the user.

You display the array you use and the column of cells where one letter after another is substituted with its morse equivalent is the loop displaying its temporary values.

It is like you have a cpu sitting in every cell doing some stuff based on the other cpus. I know there is stuff to be read on this cell automata idea.

Your program tempted me to two things.

1. I wrote a similar program to yours, but used the vlookup function and substituted letters one at a time from the left.

2. I wrote a program where the data and program is hidden from the user (actually the array is not hidden, but it could have been) and where any change you make in the word you want translated immediately is reflected in the translation to morse code. I used the keyup event for this so I had to use a textbox for data entry and not a cell.

I have sent you the programs in an email.

Thanks for the inspiration!
"

Praise from Jan is inspiration enough for me! :-)

Jan's version uses VLOOKUP and some nice VBA code to do the job. I uploaded the Workbook he kindly sent me here so please look to see how he did it.  
Posted by andrewe at 22:22

June 27, 2004

Morse Code

The other day I saw a post about Morse Code on the J-Walk Blog.

I wondered about using Excel to code and decode text into Morse and back. Although it's been on the back-burner for a while, I finally had time to make this.

It's a bit rough but it does the trick for demonstation purposes. Have a good look at how I was able to distinguish between periods and dots, hyphens and dashes. "K" and "4" also gave me a problem but I worked out an inelegant solution to overcome.

Please note that the sheet is protected but there is no password. You can also enter text without having to unprotect the sheet.

If you must know, formatting the file took longer than writing the formulas!  
Posted by andrewe at 00:06

June 26, 2004

More on Comments

Here are some more comment tips that I think are quite handy. (I found them on the Contextures site run by Debra Dalgleish)

Changing a comment's shape
Right-click the comment's cell and select Edit Comment. Click on the border of the comment and the white pull tabs should appear like this.



On the Drawing toolbar, click the Draw button and then Change AutoShape. (The Draw button should be on the bottom left if Drawing toolbar is displayed, otherwise right-click any toolbar to select it)



You can also do things like change colors, font or shadow, either right-clicking the comment and selecting the Format Comment dialog box or pushing the appropriate buttons on toolbars.

Here's a picture of me selecting No Shadow on the Drawing toolbar with the Shadow Style button.




Drag the handles to re-size when you're finished. Here's the finished product.



Inserting a Picture
Add a bit of novelty by using a picture in a comment by using the Format Comment dialog box. Right-Click the comment to select it.



Select the Colors and Shapes tab, then push the Color dropdown arrow to find Fill Effects. Use the Picture tab to find the Select Picture button which enable you to browse your files and choose a suitable picture.

  
Posted by andrewe at 20:49

June 23, 2004

No Comment? Yes Comment!

Comments in real life may not always please, but in Excel they are usually a good thing. They remind and they instruct.

Apart from the usual Insert Comment, here are a couple of other ways to make comments work to your advantage.

Input Message using Data Validation
Select Data, Validation from the top menu and then to the tab shown as Input Message. (make sure Allow: Any value is selected on the Settings tab)



Write whatever you like for the Title and Input Message. (There seems to be a limit of 32 characters for the Title, but no limit for the Input Message)

Once completed, select any cell you entered the above Data Validation and watch your message appear (the message can also be dragged to wherever you like)



It's a good way to tell another user what should be entered in a cell without them having to guess.

Enter a comment into a formula
How about this? You can enter a formula using the N function (I'm not sure that's what it's really called, I think it stands for Numeric)

Let's say I want a comment or reminder to cell D6 where I already used the SUM formula to add cells D2:D5. As you can see, I've used the N function to add a comment at the end of the formula.



Beware it's limitations. It does not work with certain functions such as used in logic formulas (Refer to the Help section for more detailed information)

As an added tip, do long and complex formulas give you a headache? Why not add some spaces between the individual functions to make them easier to read?



This would be a good time to write a comment!  
Posted by andrewe at 23:23

June 21, 2004

Alternative VALUE Formulas

Yesterday I received an email from Alan M. Robertson who suggested a alternative to my formula that I used in my The VALUE Function tip that I posted on June 19.

Here's what he was kind enough to write.

I always preferred to use (something like)

=VALUE(RIGHT(D3,LEN(D3)-4))

Assuming that the cell is well behaved (letters followed by a space followed by a number), the formula can be modified for any currency.

=VALUE(RIGHT(D3,LEN(D3)-FIND(" ",D3)))

ActionResult
Original=VALUE(RIGHT(D3,LEN(D3)-FIND(" ",D3)))
Substitute value of cell D3=VALUE(RIGHT("Eur 500.00",LEN("Eur 500.00")-FIND(" ","Eur 500.00")))
Resolve LEN and FIND functions=VALUE(RIGHT("Eur 500.00",10-4))
Resolve RIGHT function=VALUE("500.00")
Resolve VALUE function=500

This is a good formula because as Alan mentions, it adapts to any currency. It uses the LEN function (that determines the length of the string or characters entered in a cell), the FIND function to locate the position of the space " ", and the RIGHT function to read everything right of the " " space. Pretty neat!

Well, there's more than one way to skin a cat (figure of speech I assure you!) Here's my modification of the formula that I used originally.

=VALUE(SUBSTITUTE(D3,LEFT(D3,FIND(" ",D3,1)),""))

Whew! Glad that's over!

Anyway, thanks very much Alan for the great formula, but don't expect me to make a post like this all the time or I'll never to get to bed on time! ;-)  
Posted by andrewe at 22:48

Row, row, row your boat...

I've seen a couple of different ways to color rows and columns.

I remember the joy of discovering the ROW function for the first time. I can't remember my exact formula but playing around with Excel just now, I came up with this one.

=ODD(ROW())=ROW()

Place this in the Conditional Formatting dialog box and watch every odd row come up the format you choose. (Just replace ODD with EVEN to get every even row instead)

How does it work? =ROW() will give you that row's number as will =COLUMN will give you the number of the column.

=ROW()&COLUMN() returns 49 in the case of cell I4, the 4th row down and the 9th column across.

=ODD(ROW()) raises the row number to the next odd number, so that =ODD(ROW()) placed in cell I4 or alternatively =ODD(ROW(I4)) will give you the number 5.

Here's a couple of more examples.

=ODD(2) is 3

=ODD(3) is 3

=EVEN(3) is 4

and so forth...

By now you should be able to understand that =ODD(ROW())=ROW() will be a TRUE statement for every odd row and FALSE for every even row.

How about every third or fourth rows or columns? Well, here is a formula that is more versatile than above.

=MOD(ROW(),2)<>0 for odd rows and =MOD(ROW(),2)=0 for even rows. The MOD formula gives the remainder of a division such as =MOD(5,2) will return 1 because dividing 2 (Divisor) into 5 (Number) leaves a remainder of 1 whereas =MOD(6,2) will return 0 because 2 is divisible by 6 with no remainder.

The advantage of this function is that you can adjust the rows or columns to be formatted simply by changing the divisor.

=MOD(ROW(),3)=0 colors every 3rd row and =MOD(COLUMN(),4)=0 will color every 4th column.

Make up your own combinations and have some fun!

BTW, here is a VBA equivalent that Colo showed me that colors even rows in a selection. (Change the 2 in lStartRow Mod 2 to 1 for odd rows. Also you can change the ColorIndex to other colors, look in your Visual Basic Editor for Help on other colors available)

Sub Color()
Dim oCell As Range
Dim lStartRow As Long
lStartRow = Selection.Item(1).Row
For Each oCell In Selection
If oCell.Row Mod 2 <> lStartRow Mod 2 Then
oCell.Interior.ColorIndex = 3
End If
Next
End Sub

One advantage of using VBA is that you can keep your options for Conditional Formatting open, they may come in handy for something else.  
Posted by andrewe at 22:14

June 20, 2004

Welcome!

Today my new site is officially open.

I've been getting it ready over the last few days, thanks largely due to my good buddy Colo.

I hope you like the new look. (Please excuse the photo on the left, I'm a lot better looking once you've had a few beers)  
Posted by andrewe at 03:29

June 19, 2004

The VALUE function

Last time I showed how to change numbers to text. Although this is a very useful function, once a number becomes text, functions that add, count, multiply etc are no longer valid.

This is a case where the Value function comes in very handy.

In the TEXT function post below, the numbers have become text with the first four characters either "EUR " or "USD " in front (this includes the space in between the currency and the number)

In addition to converting the text to a value, we must remove these four characters which I will do with the LEFT and SUBSTITUTE functions.

=LEFT(D3,4) will give me the first four letters, "EUR " in cell D3. (Fortunately both "EUR " and "USD " have four characters we can use the same formulas in either case)

Then I use SUBSTITUTE to remove the characters like this.

=SUBSTITUTE(D3,LEFT(D3,4),"")

It works like this, =SUBSTITUTE(original text, text to be converted, replacement text), so if the D3 entry was Money makes the world go round, then =SUBSTITUTE(D3,"Money","Love") will give me Love makes the world go round. (Note: Text must be entered with apostrophe marks, but not cell references and certain other exceptions. It is also case sensitive, although there are ways around this limitation)

The quotation marks "" will leave a blank instead of either "EUR " or "USD ".

EUR 500:00 in cell D3 has now become 500:00 using the above formula, but it is still text and not a value that we can use numeric formulas with.

The final part of the formula calls for the VALUE function as in my example below

=VALUE(SUBSTITUTE(D3,LEFT(D3,4),""))

Here's a pic to make the above easier to follow.



Finished and none too soon, it's way past my bedtime...G'night!  
Posted by andrewe at 02:34

June 14, 2004

The TEXT function

When is a number not a number? When it becomes text.

Let me explain, although you can do an similar thing with the Format Cells dialog box, formatting by use of the TEXT function enables to you choose formatting under certain conditions, including referencing other cells.

Suppose you have copied some monetary amounts from another Worksheet or Workbook. You can copy the values easily enough, but because you are using different currencies for certain customers, you may find yourself having problems in retaining the original format Although there are other ways to avoid this, using the TEXT function must be one of the more easier choices.

As an example, look at the picture below.



I have customers listed in column B:B and some amounts in column C:C. How did I change these amounts to Euro for Company ABC and US Dollars for Company XYZ in column D:D?

Let's look at the formats first. For Euro I have used [$EUR] #,##0.00, and for US Dollars I have used [$USD] #,##0.00. (Notice I have used square brackets and $ signs for EUR and USD for an Accounting format, whereas with the Format Cells dialog box, I could use either this format or just use inverted commas as in "EUR" #,##0.00 or "USD" #,##0.00. Either will do the job, although there may be some slight differences in spacing)

The TEXT formula is written as =TEXT(value, format_text), so for cell D3 I want to reference C3 which shows the amount or value, then I write the format I want the amount to change to, EUR or USD.

There are only 2 companies involved in my example so a simple IF function is enough to decide between the EUR and USD formats, the company names in cell B3 being the deciding factor

=IF(B3="Company ABC",TEXT(C3,"[$EUR] #,##0.00"),TEXT(C3,"[$USD] #,##0.00"))

This formula will usually work, but if I have blank cells in column B, the above formula will automatically determine the format must be for US Dollars, so as a precaution I'll add an extra condition to be on the safe side.

=IF(B3="Company ABC",TEXT(C3,"[$EUR] #,##0.00"),IF(B3="Company XYZ",TEXT(C3,"[$USD] #,##0.00"),""))

The formula is now a nested IF formula which determines that if the customer is Company ABC to format in Euro, otherwise if the customer is Company XYZ to format in US Dollars, otherwise the two inverted commas shown as "" at the end of the formula will just leave a blank cell.

Next time I'll have a look at the "opposite" of the TEXT function...the VALUE function.  
Posted by andrewe at 09:28

June 13, 2004

Mastermind

I present my very first Excel game (built mostly by me, but with some useful VBA provided by Colo and also Juan Pablo Gonzalez)

Play and enjoy! If you have any feedback, please leave a comment or email me at the link provided below. Thanks.

Download it here!  
Posted by andrewe at 09:57

June 12, 2004

The DATE function, Part 2

It's a funny quirk of business. "Payment at 30 days" doesn't necessarily mean payment at 30 days, it really means exactly one month after. For example, if I add 30 days to today's date (06/12/04), the answer is 07/12/04 only because June has 30 days. Try adding 60 days and the result will be 08/11/04. What should really be calculated is today plus 2 months which is 08/12/04.

With this in mind, there is a very useful function called the EDATE function. It works like this, =EDATE(Date,Months) so if I want to add 2 months to today I can enter =EDATE(TODAY(),2). (Don't forget that =TODAY() gives today's date)

The problem is that the Analysis Toolpak must be installed for EDATE function to work. To check whether it is installed, select Tools, Add-Ins and the dialog box will appear like this.



And if you don't have the Analysis Toolpak? Here is a workaround that I call EDATEPLUS. It uses the DATE function to add or subtract the number of months you want, and if the days of the calculated date exceed the number of days that actually exist in that month, it will calculate to the end of the month instead, this being selected by the IF function. (For example, if today was 01/31/04, then one month later will be 02/29/04, not 03/01/04).

Hold on to your seats...

=IF(DAY(A1)<>DAY(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))),DATE(YEAR(A1),MONTH(A1)+B1+1,0),DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)))

A1 is the original date and B1 is the number of months to add or subtract. Place the formula in any cell other than A1 or B1.

Basically it works like this. If the day of the calculated date (from the original date plus or minus the required months) does not equal the day of the original date, calculate to the end of the month instead. (Actually the end of month can be calculated by using the EOMONTH fuction also available with the Analysis Toolpak, or as =DATE(YEAR(A1),MONTH(A1)+B1+1,0) without the Analysis Toolpak)

Here's a Workbook example to have a look at. If you don't have the Analysis Toolpak installed, the EDATE and EOMONTH formulas will not work and you will get a #NAME? error.  
Posted by andrewe at 11:19

June 10, 2004

The DATE function, Part 1

The DATE function is definitely one of my favorites. It works like this, =DATE(Year,Month,Day). So =DATE(2004,6,10) will give me June 10, 2004.

Here's a couple of useful tricks.

First of the Month
=TODAY will give today's date, so =DATE(YEAR(TODAY()),MONTH(TODAY()),1) will give you the first of the current month. Add or subtract to MONTH(TODAY()) for different months, as in =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1) will give you the first day of next month while =DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) will give you the first of last month.

End of the Month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) will give you the last day of the current month. Add or subtract months as above to go forward or backward in time as you like. Who said time travel was impossible?

A slightly more complex formula next time...  
Posted by andrewe at 11:52

June 07, 2004

Hiding Stuff, Part 3

To hide sheets, you can go to Format, Sheet and then select Hide or Unhide to hide or unhide any sheets you choose.

What's the advantage of hiding sheets? Well, if you are not using all of them, you might want to save some space where the Sheet Tabs are shown (too many tabs means not all will be visible and this will make harder to navigate around your Workbook)

You may also use a sheet for miscellaneous data such as pricing tables for LOOKUP or other reference formulas that are only referred to or updated recently. You might hide this sheet as a form of protection (you could protect the sheet and hide it to make it even safer. It might seem paranoid but just how important is your data?)

Or if you are really concerned about people snooping around you might consider making a sheet very hidden.

Hiding Sheets - Making Sheets Very Hidden

By making your sheets very hidden, you are merely preventing it from being noticed from casual users. It won't appear under sheets to unhide using the Format, Sheets method. But I should say that anyone who knows about VBA (even low life sludge like myself) knows how to find and unhide Very Hidden sheets. That said, it should still be a reasonable measure of protection for around the office and such, but think very hard before you email a Workbook with sensitive information to people you don't want to see it in the first place!

Okay, assuming you still want to know, let's go to work. Open the Visual Basic Editor. There are a few ways to do this. One quick way is to use the shortcut keys Alt + F11. Another way is to right click any Sheet Tab and select View Code.



In the Project Explorer (top left) you will see a list of files. Let's say your Workbook is Book 1, navigate to the sheet you want. In this case I have chosen Sheet 3.



In the Properties Window (bottom left) select Visible. (It should appear in both the Alphabetic and Categorized tabs) Click on the right and a drop down list should appear. Select Very Hidden and you're done.



So now you know. I just hope I haven't broken some little known taboo in the Excel community. If I don't post within the next few days, do me a favor and call Missing Persons!  
Posted by andrewe at 11:54

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.  
Posted by andrewe at 12:07

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!  
Posted by andrewe at 18:32

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!  
Posted by andrewe at 16:27