FJCC is right: the best way to store data is in a universal format, like CSV or JSON.
However, sometimes we don't get a choice. Many of us have to adapt our projects to the Excel workbooks coworkers share. We could prep them and save them in a nicer format, but that's boring and manual work. If we expect to do this over and over (e.g., for a monthly report), we should make computers do the boring work.
The readxl package is good for pulling data from Excel files, even poorly formatted ones. You'll probably need to clean up the results a little more in R, but that can be done in a script.