I have been told it is Scholarly however this is impossible to type on a US keyboard. Could anyone please enlighten me as to what is the most common, or perhaps the most well understood by the average Russian?
There is none, really, unless you have to abide by a legal standard (e.g. when making a passport). People use whatever they think is right.
From the table on your link, I would suggest that the "Passport (1997)" is the most common and clear. (Newer passport standards are actually worse; they were made primarily to conform to the older ICAO standard).
The Scholarly is more common for the academic use; but for the same reason as yours (difficulty of typing) it is not commonly used elsewhere.
Nevertheless, the differences between these systems are relatively minor, and all can be read with comparable difficulty. There are more drastic schemes where, for example, similarities between the scripts (glyphs) are used, e.g.
kolxo3 or even
колхоз), but please don't use them, it really sucks.
That said, reading translit is always a pain, and in this day and age there is little justification for using it in normal writing. There are enough online services (for example) that let you type Cyrillic text (using some form of a translit) live without installing a Russian keyboard.
As far as I know, the transliteration system depends on the language into which you transliterate the Russian name. For example, Пётр Ильич Чайковский is transliterated as Pyotr Ilyich Tchaikovsky in English, Pjotr Iljitsch Tschaikowski in German, Piotr Ilitch Tchaïkovski in French, Piotr Ilich Chaikovski in Spanish, Pëtr Il'ič Čajkovskij in Italian, etc., etc. Transliteration of the Russian alphabet contains a comparative overview of translitations of Cyrillic in 12 languages.
If you look at the Wikipedia article Romanization of Russian you will notice that there are different transliteration system just for English, for example, the system used by the American Library Association and the Library of Congress (ALA-LC), British Standard BS 2979:1958, etc.
Passports are not the only area where consistent transliterations are important. Library catalogues are another one (although they can contain synonyms that "redirect" to the library's "official" transliteration system).
Update: ISO has a number of standards related to transliteration, one of which is ISO 9:1995: Information and documentation -- Transliteration of Cyrillic characters into Latin characters -- Slavic and non-Slavic languages.