Interested in becoming an Affiliate Trainer? We are now accepting applications for local and international positions. No need for cold calling! Receive referrals of people looking for specialized training in your area and get access to all of the training curriculum I have created and continue to create to use at your appointments.

Excel 2010 Home Tab
ClipboardFontAlignmentNumber – Styles – Cells – Editing

Styles

You can apply predefined formatting to tables or selected cells within your worksheets.

This can quickly and easily approve the appearance and the functionality of your worksheet. You can use the Format as Table function to quickly format selected cells and also convert them into a table. You can also apply this to any existing tables you have created in your worksheet. If you simply want to apply formatting without converting the selected cells into a table, then use the cell styles command instead.

To do either task, first select the cells you want to apply a predefined format. In our example to the right, we will select cells A1 through C5.

 

In the Styles section of the Home tab, select the drop down box for the predefined settings (shown in the image to the left).

In your drop down box, you will find predefined settings such as Normal, Bad, Good, and Neutral. You will also see predefined options for Data and Model, Titles and Headings and Number Format. As you scroll over these options, your selected cells will automatically change. Scroll through your options to see how it impacts your selected cells.

We selected 40% - Accent 3 within the Themed Cells Styles (as shown in the image below). To update other sections, you will have to select the drop down box again and click on additional options.

While your cells are still selected, click on Format as Table. As shown in the image below, a drop down box will open with predefined table styles. Quickly format your selected cells and convert it to a table by choosing from the formatting options.

Once you have selected your desired style, you will see the Format as Table dialog box appear (as shown in the image below). This will convert the selected cells into a table. If your selected cells has headers, meaning the selected cells that contain labels at the top of the columns, then check the My table has headers check box.





Then click the OK button to apply the selected formatting to the cells and simultaneously convert it into a table.

 

As you can see in the image below, we now have our table. We could do some additional formatting to help the title and headings stand out a bit more. Continue to practice with the different formatting options until you get your desired look.

Next, we will review how to use custom Conditional Formatting in Excel. You can utilize Conditional Formatting to highlight interesting cells, emphasize unusual values and visualize data using Data Bars, Color Scales and Icon Sets based on criteria.

We will start by selecting our data. Click on the first cell and drag your mouse until your entire data range is selected. We selected cells B4 through G13 in our example below. Now select the Conditional Formatting button in the Styles section of the Home tab. A drop down menu will open with all the different formatting options.



The first option is Highlight Cells Rules. When hovering over this option an additional menu will appear with different rules to select from.

Review the different rules now and select which one(s) apply to your data.

 



Next you will find the Top/Bottom Rules. Hover over this option to view all of the top/bottom rules.

Review the different options now and select which rule, if any, apply to your data.

While your data is still selected, click on Conditional Formatting again and select Data Bars. An additional menu will open with your data bar options. You can see from the image below, that we are hovering over the first option in the Gradient Fill section and it reflects in our selected data. It gives us a visual of which expenses are largest and smallest.

Now from the Conditional Formatting menu, select Color Scales. When the additional menu opens, hover over the different options to see how it impacts your selected data range. As shown in the image below, the shade of the color represents the value in the cell.

Let’s review the Icon Sets within the Conditional Formatting menu. As you can see in the image below, we chose a Directional option form the additional menu that opened. Here each icon represents a value in the cell. Review your different options now and select which Icon Sets works for your selected data.

In order to remove the formatting, go back to the Conditional Formatting button and click on Clear Rules. You have the option to Clear rules from selected cells or Clear rules from entire worksheet.