When working with MS Excel’s Power Query/ Get&Transform you may find that you want to change the Load format. We normally load it to a sheet while working out what needs to happen but eventually we want it to be a connection only (or the other way around!). However you may find yourself asking why Power Query close and load to is greyed out.
Power Query close and load to greyed out
So below a simple Power Query set up. The input Table is in one sheet and we Closed and Loaded to a New Sheet so that we could see the data and test it a bit.
Now we are happy so we want to change it to a connection only.
The problem is when we go back into Power Query and click on the Close & Load dropdown, we can’t change this option, it is greyed out.
Changing the ‘Close and Load to’ Option in Power Query
You need to go back into Excel, and
- in the Queries and Connections sidebar (if you don’t see it go to the DATA ribbon and switch on Queries & Connections)
- right click on your query (as show below- Table 1)
- Click on the Load To option in the dropdown
- Change your choice
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.