%X and %Y are pretty much what they seem to be. In a layout defined as 320 width and 480 height, X is 320 and Y is 480. 50%X would be 160, 20%Y would be 96, etc.
In the designer, I layout the views using any arbitray layout variant (eg, 320 x 480). Then in code, I allow the size and location of the view to adjust to whatever screen size the program happens to be running on. This works because the X in the code equals the width of the actual screen, not the arbitrary width (320) specified in the designer.
For example, in the probability calculator I have a view named Command1. In the designer, I set Left = 0, Top = 230, Width = 107, and Height = 60. This sets the view the way I want it on a 320x480 device. In the spreadsheet I calculate that a Left of 0 is 0%X, Top of 230 = 230/480 = 47.9166666666667%Y, Width of 107 = 107/320 = 33.4375%X, and Height of 60 = 60/48 = 12.5%Y.
In the Sub ResizeViews, the code
Command1.SetLayout(0%X,47.9166666666667%Y,33.4375%X,12.5%Y)
uses these values to the actual device being used. If running on a 320x480 screen, the Left, Top, Width, and Height values would be 0, 230, 107, 60 as originally specified for this size screen. If the program ran on (say) a 480 x 800 screen, then for the Command1 view, the Left would be 0% of 480 = 0, the Top would be 47.9166666666667 of 800 = 383.333, the Width would be 33.4375% of 480 = 160.5, and the Height would be 12.5% of 800 = 100. The view will now appear with about the same relative size and location on the large screen as it did on the old screen.
This example also answers your question
If you were catering for another resolution (e.g. 480x800) would you create a separate tab on the spreadsheet
No, the whole point of using %X and %Y is to avoid having to have different layouts for different devices. You still need separate variants for portrait and landscape however. In the probability calculator, I have both the 320 x 480 and 480 x 320 variants listed. In the code
the logic uses one set of %X and %Y values if in portrait mode, and a different set if in landscape mode. And yes, Android will select the appropriate layout variant according to the current orientation of the device.
I hope this answers your questions.