In SQL Management Studio I tried to export some data in Excel 2007 format and got this error:

the 'microsoft.ace.oledb.12.0' provider is not registered on the local machine

To fix it I installed 2007 Office System Driver: Data Connectivity Components then I retried and it worked like a charm.

Note: to export big amount of data, e.g. a table with half a million records you need to export to Excel 2007 (XLSX) format, thus you need the OLEDB 12 provider installed.

Note March 2019: I tried to install Microsoft Access Database Engine 2010 Redistributable (x64) and it didn't work, I had to install the 2007 version listed above and it worked.