We would like to get a full comprehensive view of all our projects including their states so we know all the Open and all Closed projects.
Excel add-in can be of major help for this. You can run a simple query that will export all your projects to a spreadsheet, providing you a comprehensive view including all states. You can further use Excel's capabilities to sort and further filter what you may need.
Here is an example of how you can do that. In this example we're executing a query named: "AllProjects" that exports the status along with some additional information of all the projects: