How to clear an Excel Table Format – how you can convert your Excel Table back to a range of cells
This week’s hint and tip is about how to clear an Excel Table format in your spreadsheets. This is on our Basic Excel training course and we also cover how to create them too. We decided to do a separate hint and tip on it though as well. We are going to go through it now below.
Using the format as Excel Table option
Most data in Excel is just formatted as a standard range of cells, with no formatting applied to it. However you can also format your data to be an Excel Table. There are many reasons why this might be more beneficial for you than a standard range. One being that if you name a range formatted as an Excel Table, it will automatically include any new rows added. If your data is just a standard range, then you will have to amend this in the ‘Name Manager’ option.
How to make your data into an Excel Table
To make your data an Excel Table, first select an active cell in your data. On the Home Tab there is an option called ‘Format as Table’, click on this and a selection of format styles will appear to choose from.
Once you click OK, it will apply the formatting selected to your data and format your range into an Excel Table.
How to clear an Excel Table format
To clear the Excel Table format and return your data to just a range with no formatting, first click on the Table Design tab. Along this ribbon there is a ‘Convert to Range’ option, click on this. This will bring up a message asking if you want to convert to a normal range, click Yes.
This will then return your data to a normal range of cells. It will still have the colour formatting applied though (fill colours and borders). To remove these, select the cells and then on the Home Tab, click on ‘Clear’ option and select ‘Clear Formats’. Your data will then be back to just a normal range with no colours or formatting applied to it.
This option can also be found by right hand clicking in your Excel Table and choose the ‘Table’ option in the list and then ‘convert to range’ option. The steps are then the same as shown above.
The video below goes through a recap on making your data an Excel Table and then goes into how to clear that format and return it back to a normal range. Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on a how to clear a Excel Table Format in your spreadsheets. Why not take a look at our previous video hint and tip on slicer errors in PivotTables in Excel spreadsheets?