How to make a table automatically expand in Excel

When you simply enter your data and structure it as though it’s a table, Excel doesn’t quite follow. However, there is still a means to make Excel acknowledge your data as a table. After doing so, you can reap the benefits of automated expansion. 


  1. Select your table (all of it) 
  2. Click format as table 
  3. If your table has headers check my table has headers 
  4. Select OK 

In this post, we will be offering a step-by-step guide on formatting your data as a table in Excel. As always, we will also be providing images to further assist you. Follow along when you are ready. 

Quick links: 

Formatting your data as a table in Excel and how to make it automatically expand

Step 1 – Select your data 

  • Click and drag from the start of your data until you reach the end — making sure you encompass everything. 

Step 2 – Select format as table and choose a theme 

  • From the Home tab select Format as table and choose a theme. You’ll receive a chance again right after, so it doesn’t matter if you just choose any. If you’ve already coloured your table, we recommend selecting the first option. 

Step 3 – Configure and confirm 

  • If you have added headers to your table, you’ll need to check the My table has headers box before proceeding. Additionally, be sure to check Excel has mapped your full table and has thus missed nothing. Afterwards, you may click OK

End note 


Microsoft Excel offers a means to format your data as a table. In doing so you allow Excel to grant you benefits such as automatic formula input and table expansion formatting. Many users don’t notice this due to often referring to their data as already being a table. 


As usual, this section is dedicated to thanking readers for their support. We’d be pleased to know you found this post useful and/or informative. If you have any more Office 365 related queries, please consult our related articles (listed below). However, if you have other queries, consider looking at the rest of the site. We’re frequently posting new stuff each week and may just have the guide or troubleshooting tips you need. Regardless, we wish you a good day/night. Farewell readers. 

Our related articles: 

  • A complete guide to using OneDrive to transfer files to a new computer – OneDrive is a cloud storage platform offered as part of the Microsoft 365 suite. With it, your various folders and files may be stored online and downloaded for use from anywhere with an internet connection. As such, OneDrive is very useful if you’re looking to get files from one computer to another without wanting to mess around with storage devices. 
  • A complete guide to fixing OneDrive sync issues on Mac – OneDrive sometimes has issues, and many users have claimed that OneDrive isn’t synchronizing on their Mac OS devices, however, there are solutions available. In this blog, we will cover the most effective approaches to help users fix sync issues on their OneDrive devices. We will address some of the causes of why OneDrive won’t sync correctly and what users can do to help resolve these issues. Use the guide below to see how you can resolve OneDrive sync issues on Mac OS devices. 
  • Ways to delete a OneDrive shared library – OneDrive’s Shared libraries (or SharePoint sites) serve as large collections of various documents, media and content for your team/organization. In this post we’ll be giving you a short image guided walk through on deleting your OneDrive shared libraries. Additionally, to ease any potential confusion, we’ll also go over why you may still be seeing your shared library afterwards. 
  • Guide to deleting document libraries in SharePoint Online – SharePoint Online uses document libraries to store various files and folders created by you (the user). If a user for some reason decided they no longer required a document library, they could exercise the ability to delete it. Albeit, that requires actually knowing how to go about it. Fortunately for us, it’s quite straightforward. In this short post we’ll be guiding you through deleting document libraries with an image assisted walkthrough. We’ll additionally let you know why you may be unable to delete a certain library and under what circumstances your lost data can be restored. 

Jack Mitchell

Jack Mitchell has been the Operations manager at telecoms and MSP Optionbox for more than 4 years. He has played a crucial role in the company, from marketing to helpdesk, and ensures that the IT requirements of over 300 clients are continuously met. With his innate passion for technology and troubleshooting and a particular interest in Apple products, Jack now delivers the most comprehensive tech guides to make your life easier. You can connect with Jack on LinkedIn.

Recent Posts