What's an alternate pin function on a microcontroller?
Many pins of your microcontroller have different functions. The 'normal' function would refer to GPIO, General Purpose Input/Output. In that case, you can use these pins directly by writing to and reading from the relevant registers.

'Alternate' functions would refer to other functions, that may include I2C, SPI, USART,  CCP, PWM, Clock, ADC, etc... How you control the pins when in an alternate function depends on the peripheral, but it generally comes down to writing to and reading from special function registers (SFR); the peripheral takes care of the rest. 

Which function is standard after a RESET depends (it is not always GPIO!), and you can find that in the relevant datasheet. Most of the time, you can select the function you want to use on-the-fly, so you can switch between peripherals. 

By using one pin for several peripherals, you can make microcontrollers with very much features. However, because you most of the time want that peripheral on that pin all the time (and don't want to switch functions on-the-fly) you can't use all peripherals in one program, or at least not at the same time. On the other hand, that isn't really often needed anyway.



As Connor points out, 'alternate function' can refer to something else as well, in just a slightly different context: here it isn't about what function you put on a pin, but about what pin you use for a function. This is called Peripheral Pin Select, and basically means you can select which pin your peripheral is using. You could, for example, do RS232 over RA1 and RA2 or over RB1 and RB2. 

See Connor's answer for a more detailed description (and upvote him for this).