I can answer your question #1
Before we wrote our own Excel driver we used the Microsoft Access 2010 driver to do all Excel reading and writing (of xlsx files).
One of the things that driver did that caused confusion like what you're seeing is that when you wrote a Sheet to a file, the driver actually wrote both a sheet and a named range with the same name! The named range defines the *original* rectangle of the data written when the Sheet was created. So, if you later add more data to the sheet, the named range doesn't reflect all of the new data. Our new driver does not do this, so when you write a sheet you get only a sheet now. However, there are still lots of files out there that were written with the Access driver. We still support that driver as "Excel Legacy" in the format list.
So, when you specify Sheet1$ vs Sheet1 you are actually identifying the sheet itself (with the '$') and the name range (without the '$').
In Excel you can see these named ranges by going to the Formula Tab and clicking on the Name Manager. That will show you all of the named ranges defined in the file.
There is also a dropdown list above cell A1 that will contain the named ranges. If you select a named range from the dropdown list, Excel will select the cells that make up the range. I would bet that if you did this, you'll see that the original named range only includes a subset of your present data.
You can manually delete the named range in the Name Manager and then back in Alteryx you will only see the sheet (with the '$' on the end).
I hope this helps clear up your first question.