Not to be pedantic, but S stands for "S Position":
View attachment 452845
The way I read the owner's manual, Sequential Mode has less to do with the D or S gear selection, and more to do with manually changing gears with the paddles. It's just a confusing coincidence that "Sequential" and "S Position" both start with "S".
If you are in D, shifting with the paddles enters Sequential mode temporarily. When in S, shifting with the paddles changes to Sequential mode permanently (until you cancel by holding the gear up paddle, re-enter D, or turn off the vehicle).
View attachment 452844
To make things even more confusing, Honda's service info calls it "Sport":
View attachment 452846
Clear as mud? 😉
To chime in on the original topic, I don't often drive around in normal S Position, but I will switch to M and shift manually pretty frequently. I find that regular Drive is often too quick to upshift and I end up having to really put my foot in it to get it to downshift enough when I need a quick burst of acceleration. I find regular S Position to be a little bit too much on the other end of spectrum, so I'll usually switch to M and keep the RPM's in a usable range for acceleration, but also not completely kill fuel economy.