To make a plain table from data in Excel for Microsoft 365 on Windows, put the data in a continuous range with a header in each column, select a cell in the range, and choose Insert > Table. Confirm the range and that My table has headers is selected, then click OK. To keep the table looking simple, select it, open Table Design, and choose a plain white style or turn off Banded Rows. The result is still an Excel table, with useful features such as filter buttons and automatic expansion.
If by “just data” you mean a normal cell range without Excel table features, you can convert the table back to a range instead. The steps below explain both options, so you can choose between a plain-looking table and data with no table behavior.
What You’ll Need
- Excel for Microsoft 365 on Windows. The main commands are also available in Excel for Mac and Excel for the web, though ribbon labels or placement can vary slightly.
- A rectangular block of data: one header row followed by records, with each column used for one kind of information.
- A saved copy of the workbook if the data is important. Creating a table is usually reversible, but a backup makes it easier to recover from unintended changes.
Step-by-Step
- Prepare the range. Put one field in each column, such as Date, Item, Quantity, and Status. Give every column a distinct, nonblank header. Keep the records together in adjacent rows; remove completely blank rows or columns from the middle of the data because they can cause Excel to detect only part of the range. A blank row above the data is fine, but do not include it when selecting the range. Avoid merged cells in the data area, as they do not work well with tables.
- Check what the cells contain. Each row should normally describe one record, and values in a column should be the same kind of thing—for example, dates in a Date column and numbers in a Quantity column. You can include formulas, but first check that they calculate as intended. A table may automatically fill a formula down a calculated column, which is useful but can surprise you if you meant to leave some rows different.
- Select the data. Click any cell inside the block. Excel will usually detect the connected data range automatically. If you prefer, select the complete block, including the headers, by dragging across it or using the keyboard. On Windows, Ctrl+A while a cell in a data region is selected can select the current region; check the highlighted edges before continuing, especially if there are blank rows or columns.
- Insert the table. Choose Insert > Table on the ribbon. In the Create Table dialog, confirm that the range shown covers every header and data row, and select My table has headers. Click OK. On Windows, Ctrl+T is a shortcut for opening the Create Table dialog. On Mac, use Insert > Table if the shortcut differs with your keyboard settings. In Excel for the web, use Insert > Table.
- Verify the header row. Look at the first row of the new table. It should show your actual column names, not generic labels such as Column1. If the names appear as data and Excel has added generic headers, undo immediately with Ctrl+Z on Windows or Command+Z on Mac, select the range again, and repeat with My table has headers selected. If the first row really is data rather than headers, add a proper header row before creating the table.
- Choose a plain appearance. Click inside the table so the contextual Table Design tab appears. Open the Table Styles gallery and choose a simple white style. If the table has alternating shaded rows, clear the Banded Rows checkbox in Table Design > Table Style Options. You can also turn off First Column or Last Column emphasis if those options are selected. Keep Header Row selected if you want the column names visible as the table’s headers.
- Remove any leftover cell formatting if needed. A plain table style may not remove formatting that was applied directly to cells before the table was made. If a cell still has an unwanted fill, font, or border, select the affected cells and use Home > Clear > Clear Formats. This removes formatting but leaves the values and formulas in place. Review the result before applying it to the entire table, since Clear Formats also removes useful number formats such as date or currency display.
- Check that the table is still a table. Click one of its cells and look for Table Design on the ribbon. Header cells normally have filter arrows, and typing in the row immediately below the table will usually extend it. These behaviors confirm that you have a real Excel table with a plain style, rather than just a formatted block of cells. If you want to use filters, leave the header row and filter buttons enabled.
- Convert it to a regular range only if that is what you want. If you want cells and values without table behavior, click inside the table and choose Table Design > Tools > Convert to Range. Confirm the prompt. The contents remain in the worksheet, but the range no longer has table features such as automatic expansion and table-specific filter controls. Existing colors or other formatting may remain, so converting to a range does not necessarily make the cells look plain. Use Home > Clear > Clear Formats separately if you also want to remove cell formatting, and then check number formats and formulas.
The important distinction is that style and structure are separate. A white style changes the table’s appearance without removing its table features. Convert to Range removes the table structure but does not erase the data. Choose the first option if you want sorting, filtering, and automatic table behavior; choose the second if you need an ordinary range for a particular workflow.
Common Mistakes
- Leaving a header blank or repeating a header name. Table headers need useful labels. Excel may replace a blank header with a generic name, and it may adjust duplicate names. Rename the headers to make them clear and distinct before relying on them in filters or formulas.
- Selecting only part of the data. A table that omits the last rows or a column will not include those records in sorting and filtering. Check the range displayed in the Create Table dialog before clicking OK. If you discover the omission afterward, resize the table from Table Design > Resize Table and include the missing cells.
- Thinking a plain style removes table features. A white table can still have filter arrows, structured references, and automatic expansion. That is often the best way to get a clean appearance while keeping the useful behavior. Convert to Range only when you specifically want to remove the table structure.
- Expecting Convert to Range to erase all formatting. It changes the object back to a cell range; it does not guarantee that fills, borders, or fonts disappear. Clear cell formats separately if needed, but avoid clearing date or number formats accidentally.
- Using merged cells or blank separator rows inside the data. These interrupt the rectangular list and can make Excel select the wrong area or prevent reliable sorting. Keep titles and notes outside the table, and use one record per row without separator rows.
Tips
- Use short, descriptive headers such as Customer, Order Date, and Region. These are easier to scan and make table references more understandable.
- Keep notes, report titles, and totals outside the data block when possible. Put a title above the table with a blank row between it and the table, rather than merging cells across the headers.
- To see the full list of styles, click the Table Styles gallery arrow. A very light style plus Banded Rows turned off often gives a simple, readable appearance without manually formatting every row.
- Use the filter arrows for quick sorting and filtering. For example, the arrow in a Status header can show only records marked Open. Filtering hides rows temporarily; it does not delete them.
- Give the table a meaningful name if you expect to refer to it in formulas. Select the table, open Table Design, and edit the Table Name box. Avoid spaces in the table name; use a short name such as SalesData.
- When adding records, type directly into the row below the table or press Tab from the last cell in the last row. Excel generally expands the table to include the new record. After adding data, confirm that the table border includes it.
Troubleshooting
The Create Table dialog shows the wrong range: Cancel the dialog, select the complete block again, and make sure there are no blank separator rows dividing it. You can also edit the range in the dialog before confirming. If the table already exists, select it and use Table Design > Resize Table to adjust its boundaries.
Excel treats the first row as data: Undo the table creation, then select the range again and check My table has headers. If there is no header row yet, insert one with a name for each column before making the table. Avoid adding a title row to the selected range.
The table still has color or stripes: Choose a plainer style in Table Design and turn off Banded Rows. If only some cells remain colored, that may be direct formatting or conditional formatting rather than the table style. For direct formatting, select the cells and use Home > Clear > Clear Formats. To inspect conditional formatting, select the affected cells and choose Home > Conditional Formatting > Manage Rules.
The Table Design tab is missing: Click inside the table. Contextual tabs appear only when the relevant table is selected. If the tab still does not appear, the range may be a normal cell range rather than an Excel table; create one with Insert > Table.
Sorting moves values out of alignment: Undo the sort at once. Make sure the entire dataset is one table and that each record occupies a single row. Sorting a single column outside the table can separate values from their related records. Re-create the table over the full range, then sort using the header filter arrow or the sorting commands while a table cell is selected.
Frequently Asked Questions
How do I make an Excel table with no colors?
Choose a plain white option in Table Design > Table Styles, and turn off Banded Rows. If a few cells retain a fill, clear their direct formatting separately. The table remains a table unless you use Convert to Range.
Can I keep the filter arrows but remove the table style?
Yes. Use a plain style and adjust the table style options; do not convert the table to a range. Filter arrows are part of the table’s header controls and can remain available with a simple appearance.
How do I turn an Excel table back into data?
Select a table cell, then choose Table Design > Tools > Convert to Range and confirm. The cell contents remain, but table features are removed. If you also want a plain visual format, clear cell formatting afterward as needed.
Will converting a table to a range delete the data?
No. It leaves the cell values and formulas in the worksheet, but removes the table object. Check formulas and formatting afterward, especially if other workbook content refers to the table by name.
Can I make a table in Excel for Mac or Excel for the web?
Yes. In both, select the data and use Insert > Table, then confirm whether the range has headers. On Mac, the ribbon includes table design controls when the table is selected. In Excel for the web, the available style controls can differ slightly from the Windows desktop app.
Can I add a total row without turning the table into a report?
Yes. Select a table cell, open Table Design, and select Total Row in Table Style Options. Excel adds a row at the bottom that can display totals or other summaries. Clear the option to remove the total row later; the table data remains intact.
Related
- Goal Seek in Excel Browser: Limits and Workarounds
- Goal Seek Button in Excel: Where It Is and How to Use It
- Goal Seek in Excel: How to Find the Input You Need
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text to Columns Wizard: Split Data EasilyExcel’s Text to Columns wizard splits the contents of one column into separate columns. In Microsoft Excel for…
- Excel Time Minus Time: Calculate Hours Between TimesTo subtract one time from another in Excel, enter the start time in A2 and the end time…
- How to Create a Poster Template in PowerPoint That WorksCreate a reusable poster template in PowerPoint by setting the final page size, arranging editable design elements on…
- Password Protect an Excel Sheet from Editing SafelyTo password protect an Excel sheet from editing in Microsoft 365 for Windows, open the worksheet and select…
- Freeze Panes in Excel: Keep Rows and Columns VisibleIn Excel for Microsoft 365 on Windows, select the cell immediately below the rows and immediately to the…
- How to Freeze Top Row and First Column in ExcelFreeze the top row and first column together in Excel with Freeze Panes, plus fixes for common issues…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.