Remember decoupling has several purposes.
On a load which draws transient, spiky currents like a CPU, decoupling caps store energy locally and close to the load, so it is available quickly (ie, with low inductance). The idea is that on each clock cycle, the cpu will gobble up a certain amount of charge (coulombs), which means the caps must both have large enough value and low enough inductance to provide needed charge without letting voltage sag out of its specified allowable range.
This brings us to their second role, which is to close high di/dt loops locally. This is needed to make the circuit work, as too much inductance will result in supply sagging, but it is also very beneficial, as it avoids injecting HF currents into the GND plane. Our CPU draws very fast currents, thus local, low inductance caps will be the first to respond. They then average out the current drawn from the larger, slower, higher inductance, and further away bulk caps. These in turn must provide charge while the usually slow regulator responds.
Same for the input of a buck regulator. It draws a fast square wave current, and the input decoupling caps' role is to make it flow in a tight local loop, and only draw a much less noisy, averaged current from the main supply.
On analog stuff like opamps, decoupling caps also filter out HF noise on the supplies. If your opamp goes into class B, slews, or draws a current spike as it wrangles a capacitive load, it will draws a distorted current or a current spike, which will inject distortion into the supplies. The resulting distortion at the output depends on supply impedance and PSRR at the relevant frequency.
And on the supply side, obviously the caps should make your regulator happy! Check its datasheet. Not all "stable with 1µF ceramic" LDOs are equal. Some have admirable transient responses. Others are horrendous. Same if there is a ferrite bead in the supply. Don't make a LC tank that resonates at a frequency you use...
Excess inductance in supply lines causes voltage sag on transient current demands. Digital stuff reacts to this by crashing, computing incorrect values, triggering UVLO or brownout detectors, and all kinds of fun stuff. Opamps and analog bits react by oscillating, taking forever to settle, increasing distortion, etc.
Excess inductance will also cause voltage spikes (positive or negative) when forcing large currents into it, as occurs on switching of a DC-DC converter. This will blow your FETs, your FET drivers... I've seen it several times on this website already.
Now, it's a bit involved and there are several approaches.
You like soldering 0805 because 0603 is to small. Being aware that inductance depends on package size and not value, you purchase a few hundreds of the largest caps you can get in 0805, probably 1-10 µF depending on voltage, and get a nice quantity discount. They you stick one on each power pin, without worrying, and it just works. You could put 100nF, but the price isn't that different for a hobbyist, and honestly, better put a cap which is 5c more expensive than actually think about the value you need, eh? I mean, if you value your time, spending 5c to save an extra minute thinking about the value you actually need is a no-brainer. Just like spending 50€ for chinese 4 layer boards versus slaving for two weekends painstakingly fitting the damn mess on two layers? Heck yeah.
Adding a 10c electrolytic will also save you the pain of debugging an oscillating regulator sometimes, a worthy investment when you make only a few boards.
Note: parallelling 100nF with 1µF is only useful if the 100nF is much smaller. If they're the same package, they got the same inductance. The smaller cap is only gonna be faster if it is smaller physically, and closer to the pins/planes.
This guy has a good idea of the supply impedance he needs, and creates it by sticking various caps in parallel, taking into account package and via inductance, the fact that C0G works better at HF, perhaps exploit the self-resonant frequencies, make sure the supply doesn't resonate on the wrong frequency, stick a ferrite bead or two in the mix to add some filtering, etc. Here, impedance is more important than capacitor value.
He does basically the opposite, X7R SMD cap is about 1nH, thru-hole WIMA red boxes with 5.08mm pin spacing are about 6-8 nH mounted, and thus the HF noise on the rails is multiplied by the same factor, but who cares, it looks good! Also, the unstable LDO makes the treble fizzle, for that little extra something.
- The baddass motherboard guy
In this case the manufacturer gives a recommended impedance profile for their chip's supply. And a software tool to make it easier. The result usually involves tons of low-value caps, because they have to be tiny, low ESL and fit between BGA vias or the like. Then he'll stagger values to get his impedance profile, check the thing with a VNA (under rated voltage). Then the cost-cutters will depopulate half the caps, of course.
Get an opamp with "100ns settling time to 0.1%". Stick it on a board with decoupling caps like 10nF//1µF. Every time it slews and draws a current spike, a LC resonance occurs between the two caps, overcomes the opamp's PSRR which is low at this frequency, and the settling time becomes 100x longer than it should. Solution is to use only ONE low-ESR cap, which will be ceramic, so it will be the small and local one. And use caps with actual ESR for the larger ones, which will dampen the impedance and not resonate. Like tantalums or electrolytics.
Rememeber your chip has very very thin bondwires, so it has like 0.1-0.5 ohm in the supplies anyway, so your opamp doesn't need, or care about the fact that the MLCC cap has 0.01 ohms ESR! This kind of ultra low ESR is a pain in the ... because of resonances.
Actually low ESR ceramics are such a pain, that some people like SUN came up with insane ways to stick some printed annular ring resistors on the internal layers to add some ESR!
Now they're starting to make "controlled ESR" ones. About time.
Note that I'm not talking about values a lot. The RF guy and the motherboard guy have an impedance target to meet, so it's more about how many caps, inductance, how to choose the staggered values, what type of caps to use, for the lowest cost. The analog guy will most likely use a small ceramic cap to make sure his opamp has a nice low inductance supply, and pick a value that fits in the package he needs...