Well, first of all I do not consider colonising a place a synonymous with living there. Space offers revolutionary technology that can tangibly affect what happens on Earth.
Planets that are substantially smaller than Earth (which is pretty much all the rocky planets in our solar system bar Venus) are far less painful to mine even without getting into the fact that there is (or so we hope) no ecosystem to destroy from our mining practices so it's not just asteroids that are attractive targets for resources. It's not just the lower gravity, it's that smaller bodies tend to have less stratification of geological layers so instead of having nearly all metals concentrated underneath gigapascals of pressure at the core (like on Earth), smaller bodies will have such valuable booty more evenly distributed across the crust. That's not even getting into the abundance of gases like hydrogen and helium that can be scooped from gas giants that are often quite expensive on Earth.
Most heavy industrial processes and power generation have substantial ecological consequences so I envision nearly all of this being taken off world into orbital factories and power collectors. Microwave wireless energy transfer has achieved greater than 80% efficiencies in the lab and solar power collectors in space can achieve a 24/7 operation and park themselves in geosynchronous orbits so a mini dyson swarm around Earth could power the world's economy comfortably and be far more distributed and safe against shutdowns.
Manufacturing in microgravity offers unique opportunities and advantages for many processes in industry and the advantages of floating in vacuum cannot be understate for bulk transportation of goods.
There is a trick that exploits the conservation of momentum that allows for very cheap transfer of cargo and people from and to Earth without having to rely on the magically strong materials of space elevators: by having a ring of ferromagnetic material around the Earth that rotates and greater than orbital velocity, you can then have an outer shell around it that is either at rest or moving in the opposite direction (preferably at the rotational speed of the Earth that can be kept suspended from the inner coil via a magnetic field.
As long as the net momentum of the inner and outer ring is zero, it will remain stable and now you have a platform that is at rest relative to the ground and from which you can have cable cars running to the ground from without requiring fictional materials. One thing to note is that with these largely stationary (save for counterbalancing against the rotation of the earth) platforms is that you are still experiencing most of the earth's gravity when standing on them. This makes it a good option for long term habitats for baseline humans. Of course, because it has a coil that winds around the entire circumference of the Earth, you can use it to propel one hell of a mass driver for cargo to the point where you could just not bother with chemical rockets altogether and rely on magnetic trains to do most of the heavy lifting into LEO with ion drives or solar sails are for long term maintenance.
This formula works for every planet but it makes the most sense to target Earth because of the highly challenging escape velocity it imposes on our current launch capabilities. This allows you to skip the rocket equation and as a consequence make travel to space genuinely affordable with potentially less than a dollar per kg costs. Once you get to space, travel is actually incredibly easy since you have mostly empty space between you and your target and there is no air resistance to slow you down.
We can also protect the Earth from coronal mass ejections and asteroid strikes and even counteract global warming by dimming the sunlight received from earth by a percent or two. This won't magically turn the clock back on climate change but it would be one hell of a slowdown to allow for other changes in our manufacturing and energy generation processes.
Besides that, extrapolating from very recent trends in population dynamics in "developed" (which is such a relative term anyway) countries that overpopulation is not going to be a problem anymore is absurd. People do on average want to have kids and the real reason why some countries are beginning to go below replacement rates of growth has more to do with the collapse of the middle class and the uncertain economic future of many young adults. Once we have radical life extension technology (something I consider largely inevitable) and people in their late 80's are still as sexually virulent as if they were in their 20's, we are likely to see another population boom within this century. While we could probably continue to house more people past a trillion within Earth, you are going to have to start either living more frugally or damage the ecosystem or at least reduce it's priority.
An Ecumenopolis (planet-wide city) could actually be a very comfortable place to live and house many trillions but it's a process that is inherently destructive to the landscape and life as it is now. You are essentially turning the entire surface (and ideally some or all of the interior) of a planet into a machine - and a lot of that would be necessary once we are talking multiple trillions because at that scale, we have a very different heating problem from the comparatively measly effects of greenhouse gases that are already wrecking havoc on our ecosystems. When you have that much waste heat being generated on a planet, you need to start constructing giant radiators that extend out of the atmosphere on space towers. Once you have such numbers, even if less than a percent of the population have any wish of colonising other worlds, you will have billions of colonists and our entire solar system will be colonised.
There is also the undeniable appeals of claiming territory over a truly virgin land without the evil of colonialism over past natives. That land might be hostile as it is but the opportunity to transform it into an oasis of life is going to become a more and more attractive proposition once have a truly abundant and clean source of energy because matter then becomes the real bottleneck. Land prices on Earth will continue to rise to the point where suburbs in the form of O' Neil Cylinder habitats will become the suburbs and rural area of humanity. Or if we switch to a form of land allocation that does not involve money, we would still commission the construction of rotating habitats to keep the population density of Earth below a amount (what amount that is depends both on your values as a civilisation and the degree of verticality of your settlements.