ExcelInsider Team
When you create an Excel table, Excel applies a default color scheme with specific font colors. However, you can customize the table colors by changing the fill, border, and font colors of your table data. Moreover, the conditional formatting feature allows you to change the table color based on its cell values.
In general, we use the Table Design tab to apply alternate shading and a fill color to every other row of an Excel table. You’ll have to manually change the color to change the font or fill color of the entire table.
Steps to change table colors, styles, and customized formatting:
➤ Click on any cell in your table and go to the Table Design tab. In the Table Styles/Quick Styles group, hover over different styles to preview them. Press on any style to apply it instantly.
➤ Use the New Table Style button in the style gallery to change colors of the entire table, first few rows or columns, second few rows or columns, the header, or the total row.
➤ Go to the Home tab >> Conditional Formatting drop-down >> New Rule >> Use a Formula to Determine Which Cells to Format. Use a formula to set colors for table cells containing specific values, such as those equal to, greater than, or less than a certain value.
In this article, we’ll cover all the ways of changing table colors using the Table Design tab, New Table Style, Conditional Formatting, Fill icon, and Theme Colors.
Changing Colors of a Table Using the Table Design Tab
Our sample dataset has columns for product Code, Salesperson, Product names, and Prices. We’ll click on any cell of the range and press Ctrl + T to convert it into a table. Excel creates a table with a default color scheme for alternating rows. Our goal is to replace the colors.
In the Table Design tab, you can access a variety of pre-built color schemes and apply them to your table instantly. Here’s how:
➤ Click on any cell of your table and go to the Table Design tab.
➤ In the Table Styles group, click on the downward arrow sign to expand the Quick Styles.
➤ As the Style Gallery opens, you can see the Light, Medium, and Dark color categories.
➤ Hover over any style to see a live preview on your table. Click on a color scheme to apply it.
➤ To get more colors in the Style Gallery, go to the Page Layout tab and click on the Colors drop-down. Select any color scheme you like and the table color will be updated.
Applying Customized Colors to the Table
In the Style Gallery, you only get alternating shades for your table. Also, they come with fixed font colors and styles. If you want to customize a New Table Style with your preferred fill, border, and font colors, follow the steps given below:
➤ Select any table cell and go to the Table Design tab and navigate to the Table Styles group. Expand it by pressing the downward arrow and choose New Table Style.
➤ In the New Table Style dialog box, type a suitable name for the new style in the Name box.
➤ From the Table Element group, choose whether you want to apply the formatting to the Whole Table, First Column Stripe, Second Column Stripe, First Row Stripe, Second Row Stripe, or Header Row. Scroll down to find more such options.
➤ If you choose to color row or column stripes, click on Stripe Size to decide how many stripes you want to cover at a time.
➤ After selecting your preferred element, click on Format and use the Font, Border, and Fill tabs to customize the table color and format. Press Ok once you’re done.
➤ Check the style Preview and press the Set As Default Table Style for This Document if you want to apply the same colors to all your tables. Or, simply keep it unchecked. Finally, press Ok.
➤ To apply the custom design you created, go to the Table Design tab >> Table Styles group >> Custom.
➤ Here’s the final result for our dataset:
Manually Changing Table Fill Colors
For maximum control over individual cells, rows, or columns, you can change the table color manually using the Fill tab. Here’s how:
➤ First, you need to remove the current color scheme. For this, click on a table cell, go to the Table Design tab >> Table Styles group. Expand the Style Gallery and click on Clear.
➤ Select the table cell(s), row, or column and go to the Home tab. Click the Fill drop-down (paint bucket icon) in the Font group.
➤ Choose your desired color from the menu and Excel will immediately add change the background color for the selected table cells.
➤ You can repeat the process multi[le times to apply different colors in different parts of the table. Here’s our final output:
Changing Page Theme to Replace the Entire Table’s Color Palette
Excel table styles adapt to the workbook’s theme. So, if you want different colors in the Style Gallery, you can change the workbook theme colors. It updates the colors available in the Style Gallery, Chart colors, and the Fill color palette. Below are the steps:
➤ Click on the Page Layout tab on the top ribbon. In the Themes group, click the Themes button.
➤ Choose from built-in themes like Ion, Slice, or Retrospect. Each theme has a coordinated set of colors, fonts, and effects.
➤ To customize further, click Colors next to the Themes button to create your own custom color set. These custom colors will then appear in all your color pickers and table styles.
➤ Here’s our changed table and styles after changing the theme:
Using Conditional Formatting to Change Table Colors Based on Values
If you want to allow cell colors to change automatically based on the cell’s value, use Conditional Formatting in the following way:
➤ To remove the existing colors, select a table cell, open the Table Design tab >> Table Styles group >> expand the Style Gallery >> Clear. You can highlight the header to make it stand out using the Fill option.
➤ Highlight the cells you want to format dynamically (A2:D10). On the Home tab, click on the Conditional Formatting drop-down >> New Rule.
➤ In the New Formatting Rule dialog box, click on Use a Formula to Determine Which Cells to Format.
➤ Use a suitable formula to highlight the table cells based on specific values or conditions. For example, to format any cell in Column D with a value greater than $100, select D2:D10 and enter:
=D2>100
To format the entire row, select the entire range without headers (A2:D10) use a $ sign like this:
=$D2>100
Or, to highlight if Column C if it contains Laptop, select C2:C10 and insert:
=C2="Laptop"
Make sure you use the first cell of the row, excluding the header.
➤ In our dataset, we’ll highlight rows containing greater than $100 in Column D. So, we entered the second formula.
➤ Now, click on Format and choose your preferred Font, Border, and Fill. Press Ok once done.
➤ Click Ok again to apply the formatting.
➤ Here’s the final result:
Frequently Asked Questions
How to set the default table color in Excel?
Excel automatically applies a default style when you insert a table. To change it, click on a table cell, go to the Table Design tab. In the Table Styles group, pick your preferred style. Right-click on the style and select Set As Default. Now, every new table you create will use this color scheme.
How to apply colors in alternate table columns?
By default, Excel allows alternate rows shading (banded rows). To color alternate columns instead, select the entire table and go to the Table Design tab. In Table Style Options, check Banded Columns. Excel will now apply different colors to every other column of your table.
How to color the top 3 values in an Excel table column?
To highlight the top 3 values in a column (e.g., Price in Column D), select the data range (D2:D10). Go to the Home tab >> Conditional Formatting drop-down >> Top/Bottom Rules >> Top 10 Items.
In the new window, change 10 to 3 and apply a fill color.
Concluding Words
While clicking on banded colored styles is the easiest way to change table color, it doesn’t offer much flexibility. Create a custom style and set it as default to apply your preferred colors and fonts in just a few clicks. As we’ve converted our range in a table, the changes are always dynamic. So, you don’t have to reapply the color when you add new rows or columns to your table.
