I still can't understand the physical reason why small ceramic capacitors, when placed very close to the power pins of an IC, effectively filter out noise. And if the capacitance value is smaller, they perform even better. But why doesn't this happen with large electrolytic capacitors? If we take a high frequency, let's say 10 kHz, a 1nF MLCC capacitor filters out the noise much better than a 100uF electrolytic or MLCC capacitor, even though according to the capacitive impedance formula, the 100uF capacitor has 0.16 ohms and the 1nF capacitor has 15.9 kohms. In my designs, I have noticed that it is much better to use a low capacitance capacitor near the VCC pin to filter out noise, but I can't understand why. Could someone explain why this happens?
Think about a one-turn inductor, basically a circle of wire: its inductance is proportional to the area inside the circle. The formula for a rectangle loop is much more complicated but the idea is the same: a larger loop with more area means higher inductance.
The important thing about your decoupling cap is its impedance. When the load draws variable current (like a microcontroller) then voltage noise on your supply is, by ohm's law, ZI with I being the current and Z the impedance of the power supply.
At high frequency, inductance of PCB traces and components dominate, so the supply impedance Z seen by your chip depends more on the shape of traces, planes, and components than on the component values.
If the decoupling cap is close to the pins, then connection inductance will be low. Not I wrote pins not pin: supply current flows in loops, from supply pin to ground pin, so the total impedance to consider is the sum of both.
The inductance of an electrolytic cap (not counting traces) is mostly determined by the area of the current loop, ie the thickness of the plug on the bottom of the cap multiplied by pin spacing. This means a thru hole ceramic cap, a thru hole electrolytic cap, or a bit of wire bent in the shape of that loop have about the same inductance.
SMD caps (MLCCs) are much smaller and allow much shorter connections, so the total inductance will be lower, that's all there is to it.
For example a 2.5mm pin spacing aluminium thru hole cap has an inductance about 2-3nH, and a 0805 MLCC about 1nH. And you can use many more MLCCs in parallel in the same space, which reduces inductance even more.
A higher value MLCC in the same size will be more effective due to having higher capacitance and lower ESR. High frequency performance depends mostly on inductance, ie size, not value. Lower frequency performance (like 1MHz) depends a lot more on value.
For DIY microcontroller stuff, 1µF 0805 on the pins is ideal: it's cheap, has the same HF performance as 100nF 0805 so you don't need to use both in parallel, has enough capacitance to behave well with most LDOs or the nearby aluminium cap.
If we take a high frequency, let's say 10 kHz, a 1nF MLCC capacitor filters out the noise much better than a 100uF electrolytic
I guess you were using a square wave. In this case what matters is not the frequency of the square wave, it's the steepness of the edges, ie the rise time. Intuitively, if you have a square wave with a rise time of a few nanoseconds, then stuff is going to happen on a nanosecond timescale, so you can expect to find harmonics up to hundreds of MHz.
If your circuit draws square wave current, and you use a large capacity high inductance electrolytic capacitor, it will absorb the low frequency components of the square wave, which is the second trace. But it will be less effective on the high frequency components, around the edges, so the noise on your supply will look like the third trace.