Let's look at a usage example. A lot of multi-player video games (as an example, Counter Strike) allow you to run a game server on your computer that other people can connect to in order to play with you. Your computer doesn't know all the people that want to play, so it' can't connect to them - instead, they have to send new connection requests to your computer from the internet.
If you didn't have anything set up on the router, it would receive these connection requests but it wouldn't know which computer inside the network had the game server, so it would just ignore them (or, more specifically, it would send back a packet indicating that it can't connect). Luckily, you know the port number that will be on connection requests for the game server. So, on the router, you set a port forward with the port number that the game server expects (for example, 27015) and the IP address of the computer with the game server (for example, 192.168.1.105).
The router will know to forward the incoming connection requests to 192.168.1.105 inside the network, and computers outside will be able to connect in.
Another example would be a local network with two machines, where the second one with the IP 192.168.1.10 hosts a website using Apache. Therefore the router should forward incoming port 80 requests to this machine. Using port forwarding, both machines can be run in the same network at the same time.
Video games are perhaps the most common place
everyday users will encounter port forwarding, although most modern games use UPnP so that you don't have to do this manually (instead, it's fully automatic). You'll need to do this whenever you want to be able to connect directly to something in your network though (rather than through some intermediary on the internet). This might include running your own web server or connecting via Remote Desktop Protocol to one of your computers.
A note on security
One of the nice things about NAT is that it provides some effort-free, built-in security. A lot of people wander the internet looking for machines that are vulnerable. and they do this by attempting to open connections with various ports. These are incoming connections, so, as discussed above, the router will drop them. This means that in a NAT configuration, only the router itself is vulnerable to attacks involving incoming connections. This is a good thing, because the router is much simpler (and thus less likely to be vulnerable) than a computer running a full operating system with a lot of software. You should keep in mind, then, that by DMZing a computer inside your network (setting it as the DMZ destination) you lose that layer of security for that computer: it is now completely open to incoming connections from the internet, so you need to secure it as if it was directly connected. Of course, any time you forward a port, the computer at the receiving end becomes vulnerable on that specific port. So make sure you run up-to-date software that is well configured.