DOC: update wording about when xlrd engine can be used
See https://github.com/pandas-dev/pandas/issues/38424#issuecomment-744043899.

> these changes look fine to me. but don't we still have the question of what happens when > 2 xlrd is installed and our error conditions are triggered? IIUC this will simply raise the value error from _inside_ xlrd, which is fine as the message is helpful, but are we _also_ triggering the warning? maybe we should _catch_ the exception and raise a nice warning say you should just use `openpyxl`?

Unfortunately, the discussion around this is a little fragmented, I think https://github.com/pandas-dev/pandas/issues/38424#issuecomment-744062773 addresses this. If I worked up a PR for the code I suggest there, would that be accepted? Also, would it be okay to add it as a separate commit to this PR or would you prefer a separate one? Let's keep the actual discussion about what *behaviour* we want in https://github.com/pandas-dev/pandas/issues/38424 (since most happened there). @cjw296 would you be fine to focus this PR to only better document the current situation? (i.e. the change to clarify that xlrd only supports xls files) I think this update is fine (and for 1.2), unless objections for THIS PR, @jorisvandenbossche @simonjayhawkins ok merging (as I think this is the only outstanding issue for 1.2) @jreback - This PR changes the documented behavior but does not change the code; it will make the code and docs out of sync. @jreback - ideally, I'd like to land https://github.com/pandas-dev/pandas/pull/38522, then update this PR with whatever its outcome is, then land that. @jreback To be clear, this PR is *not* ready to merge, as long as we don't decide on the discussion points (please respond in  #38424 ), as the current state of this PR does not reflect the situation as it is in master. @jorisvandenbossche - I think #38522 is the place to bottom things out now. I removed the subset of the changes that doesn't match with the actual behaviour in master, so now it should be good to go 
(I know this is also somewhat included in https://github.com/pandas-dev/pandas/pull/38571, but I think it is nice to attribute it to @cjw296 ) @cjw296 thanks for the update. I just removed the `.. warning` because it was already in a warning box (otherwise we could get a box in a box ..); and I copied the full warning box also to the user guide (so it's not only in the whatsnew, but also in the main documentation) Ah sorry, yes, that was a mistake on my part. I do feel you've watered down the warning though: I feel people should be warned that opening xlsx with xlrd is explicitly unsupported, and to upgrade rather than logging issues about that. Is it about the "no longer" that I added? Happy to remove that again (for the rest I kept your sentence intact. But of course it's now within a larger warning box, instead of only that sentence, so therefore maybe a bit less standing out) @jorisvandenbossche - not really sure why you're doing stuff on this PR, #38522 has been closed in favour of the, quite frankly, inferior #38571. I'm kinda done with helping pandas, this has been an extremely negative experience for me, I would welcome a discussion in private, but I couldn't find your email address anywhere. Mine is chris@python.org if you fancy a chat. > not really sure why you're doing stuff on this PR, #38522 has been closed

This PR is not tied to #38522 or #38571. The reason I am pushing to this PR is because I think there were valuable improvements here to the docs, which I wanted to get merged. But for that, the subset that didn't match the actual behaviour in master needed to be removed. So that's why I started pushing that (thinking you would be reluctant to do it yourself) @meeseeksdev backport 1.2.x