Forum Discussion
Solved
Creating a Custom Formatted Table in Excel
I am creating a spreadsheet and it is growing rapidly, however, I would like certain aspects differentiated using different colors, and the current quick actions of Excel don't allow me to format the table how I would like.
Is there a way to define the color for not just the first column, but the second as well? I am also interested in adding banding to the first, second, and last columns in their respective colors to match the banding on the rest of the table, but I don't see an option, and Google has not been helpful.
I would rather not re-band the list by hand every time I add to it, as it is rather large. Any and all ideas are welcome.
Edit: To clarify, I do not want the second column colors to band across the spreadsheet, which there is an option to do.
To achieve custom formatting in Excel that includes different colors for specific columns and banding, you can use a combination of Conditional Formatting and Custom Table Styles.
Here is a step-by-step guide:
- Select the range of cells that you want to format in your spreadsheet.
- Go to the "Home" tab in the ribbon and click on "Conditional Formatting" in the "Styles" group.
- Choose "New Rule" from the dropdown menu.
- In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
- In the "Format values where this formula is true" field, enter the formula for formatting the first column. For example, if your first column starts in cell A2, you can use the formula: =MOD(ROW(),2)=1 This formula applies the formatting to every other row in the first column.
- Click on the "Format" button to specify the formatting options for the first column. Choose the desired font color, fill color, and other formatting settings.
- Click "OK" to confirm the formatting for the first column.
- Now, to format the second column differently, repeat steps 3 to 7 with a new formula and formatting options. For example, you can use the formula: =MOD(ROW(),2)=0 This formula applies the formatting to the alternate rows in the second column.
- Once you have applied the conditional formatting to the desired columns, you can create a custom table style to incorporate banding for the first, second, and last columns.
- Select any cell within your formatted range and go to the "Home" tab in the ribbon.
- Click on the "Format as Table" button in the "Styles" group and choose a table style that closely matches your desired formatting.
- With the table still selected, go to the "Design" tab in the ribbon.
- Click on the "More" button in the "Table Styles" group to expand the style options.
- At the bottom of the styles list, click on "New Table Style".
- In the "New Table Style" dialog box, you can customize various aspects of the table style, including banding options.
- To add banding to the first, second, and last columns, click on the "Band Rows" tab. Adjust the formatting options to match the banding style you want.
- Click "OK" to create the custom table style.
Now, when you add new data to your table, the custom formatting, including the different colors for the first and second columns and the banding, will be automatically applied to the new rows.
Note that these instructions are based on the desktop version of Excel. The options and features may vary slightly in other versions of Excel.
additional info:
Excel table styles and formatting: how to apply, change and remove
To achieve custom formatting in Excel that includes different colors for specific columns and banding, you can use a combination of Conditional Formatting and Custom Table Styles.
Here is a step-by-step guide:
- Select the range of cells that you want to format in your spreadsheet.
- Go to the "Home" tab in the ribbon and click on "Conditional Formatting" in the "Styles" group.
- Choose "New Rule" from the dropdown menu.
- In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
- In the "Format values where this formula is true" field, enter the formula for formatting the first column. For example, if your first column starts in cell A2, you can use the formula: =MOD(ROW(),2)=1 This formula applies the formatting to every other row in the first column.
- Click on the "Format" button to specify the formatting options for the first column. Choose the desired font color, fill color, and other formatting settings.
- Click "OK" to confirm the formatting for the first column.
- Now, to format the second column differently, repeat steps 3 to 7 with a new formula and formatting options. For example, you can use the formula: =MOD(ROW(),2)=0 This formula applies the formatting to the alternate rows in the second column.
- Once you have applied the conditional formatting to the desired columns, you can create a custom table style to incorporate banding for the first, second, and last columns.
- Select any cell within your formatted range and go to the "Home" tab in the ribbon.
- Click on the "Format as Table" button in the "Styles" group and choose a table style that closely matches your desired formatting.
- With the table still selected, go to the "Design" tab in the ribbon.
- Click on the "More" button in the "Table Styles" group to expand the style options.
- At the bottom of the styles list, click on "New Table Style".
- In the "New Table Style" dialog box, you can customize various aspects of the table style, including banding options.
- To add banding to the first, second, and last columns, click on the "Band Rows" tab. Adjust the formatting options to match the banding style you want.
- Click "OK" to create the custom table style.
Now, when you add new data to your table, the custom formatting, including the different colors for the first and second columns and the banding, will be automatically applied to the new rows.
Note that these instructions are based on the desktop version of Excel. The options and features may vary slightly in other versions of Excel.
additional info:
Excel table styles and formatting: how to apply, change and remove
- This worked well for me, thank you.
