To change the size, select the cells, then click the downward arrow beside the current size.

Font Attributes

You can choose the standard underline, which is a single line. You can also choose the double underline, which is two lines.

Add Borders to Cells

Select the cells for which you want to change the font color, then click on the button to choose the color you want to apply to the selected text.

Add Color to a Cell or Range of Cells

 

Please note that the cell borders that show up in Excel by default are no longer displayed when we add color. If you want borders, you have to add them. 

We selected All Borders from the Borders dropdown menu, then dragged over the cells in column A to apply the borders.

The Format Cells Dialogue Box

Click the arrow in the right side bottom corner of the Font group to access the Format Cells dialogue box. 

The dialogue box looks like this:

From this dialogue box, you can format your data just as you did from the Ribbon. The Preview section of the dialogue box lets you preview your changes before you apply them.

The mini-bar appears whenever you right click within a cell.

Merging cells simply means that you merge a group of cells into one cell. It is not the same as combining cells because when you combine cells, the data in those cells is also combined. 

When you merge cells, the information in the upper left cell is centered in the merged cell. If the content you want in the merged cells is not in the upper left cell, then you must copy and paste the data into the upper left cell.

Note: It's important to remember that the cells you merge must be adjacent. In addition, the cells that you merge cannot be active or the Merge and Center feature will not work. 

To merge cells, first select the cells to be merged.

Go to the Alignment group under the Home tab, then click Merge and Center.

Select an option from the dropdown menu.

Apply Number Formats and Create Custom Number Formats

You can change the appearance of numbers in MS Excel 2016 without changing the value behind those numbers. The actual value is always displayed in the Formula Bar. 

For example, we can have a numbers formatted as currency in the worksheet:

Yet, if we click on a cell in the Formula Bar, the number is displayed as a general number.

You can apply a number format to a cell by selecting the cell(s) that you want to format, right clicking, and selecting Format Cells and select the Number tab. 

You can also to the Home tab, go to the Number group, then click on the arrow in the bottom right corner.

You will then see the Format Cells dialogue box. Click on the Number tab.

In the dialogue box above, you can choose the type of number formatting that you want from the Category column. An explanation of how the formatting is used appears at the bottom of the dialogue box when you click on a specific type of number formatting.

We chose Currency from the Category column.

We wanted two numbers to appear after the decimal point.

We wanted to use the American dollar symbol.

We wanted negative numbers to appear with the minus (-) sign before them.   We could have also chosen to have them appear in red, black with parentheses, or red with parentheses.

If the number formats the Excel provides won't work for your data, you can create a custom number format.   To do this, go to the Format Cells dialogue box again, and click Custom n the category column.

In the Type list, select the format that you want to customize. As you can see in the snapshot above, we chose the currency format.

Now go to the Type field and customize the format by entering the format you want to use.  

You can align the data in a cell to the left, right, or center.  

To align data to the left means to align it to the left side of the cell. Simply select the cell(s) that contain the data that you want to align to the left. 

Go to the Alignment group under the Home tab.

Click:

 To align to the left.

To align to the right.

To align to the center.

  To align data to the top of a cell.

  to align data to the middle of the cell.

You can also indent data in cells. When you increase indent, you increase the margin between data in the cell and the left cell border.

The cell pictured below has text data in it.

Select the cells that contain data that you want to indent. Then, go to the Alignment group under the Home tab.

The two buttons highlighted below allow you to decrease indent (first button), or increase indent. 

We're going to increase the indent (second button).

About Text Wraps

Text wrap will wrap the entries from selected cells that have data that spills over their right borders.

Here's an example:

Select the cell(s) you want to apply text wrap to, then click the Wrap Text button in the Alignment group under the Home tab.

The text is then wrapped within the cell. 

Instead of wrapping text in cells, you can also change the orientation of the text by rotating the text up or down.   This can work well with labels in worksheets.

In the example below, we're going to change the orientation of the data that contains the days of the week. 

To start with, we've selected the cells that contain this data:

Next, we go to the Alignment group under the Home tab and click the Orientation button. It looks like this:

 

From the Orientation dropdown menu, we now we get to choose the new orientation:

We're going to choose Rotate Text Up.

The Format as Table Gallery is a way to format your cells without having to select the cells first. Think of it as a shortcut to formatting cells. Your cell cursor just has to be within the table of data right before you click the Format as Table button that's located in the Styles group under the Home tab (pictured below). 

We're going to put our mouse cursor in a cell by clicking on the cell.

Now we're going to go to the Format as Table button and look at the gallery. 

Choose a formatting style that you want.

When you click on a style that you want, you'll see the Format as Table dialogue box.

This contains the cells referenced for the formatting. In the dialogue box pictured above, Excel has the referenced cells listed as D1 through O10. If this is incorrect, you can change it.  

Put a checkmark beside "My table has headers" if your data has headers. Ours does. The headers are the column labels. In our example, the headers are the months of the year. 

Click OK when you're finished.

The Table Tools Design tab then opens in the Ribbon.

Located under the Table Tools Design tab is a Table Styles Options group that allow you customize your table even further.

Header Row adds formatting and filter buttons to each of the headings in the first row. Ours already has filter buttons (the down arrow)

• Total Row goes at the bottom of the table for totals.

• Banded Rows means shading will be applied to every other row.

• First Column puts row headings in the first column in boldface.

• Last Column puts row headings in the last column of the table in boldface.

• Banded Columns applies shading to every other column.

In the Table Styles group, you can also change the style of table that you applied when you chose a format for your table from the gallery.

Our table now looks like this:

Conditional Formatting

Conditional formatting is a neat little feature of Excel 2016 because it helps you do your job better. Let's say that you're entering in sales figures for each employee into a spreadsheet. Your boss has told you to let him know if anyone sells less than $300.00 worth of products in a given month. Did you know that you Excel can notify you each time this happens? You can program Excel 2016 to give you a "red flag" every time a certain situation exists. 

To apply conditional formatting, go to the Styles group under the Home tab. Click the Conditional Formatting button.   You will see a dropdown menu.

Let's learn what each of the options does for you:

• Highlight Cells Rules allows you to define rules that highlight the cells in the cell selection when certain values such as text or dates that have greater or less than a value that you specify or fall within a range of values.

• Top/Bottom Rules gives you options for defining rules that highlight the top and bottom values, percentages, and above and below average values in a cell selection.

• Data Bars opens a palette with different colors of data bars. You can apply these to a cell selection to indicate their values relative to each other.

• Color Scales opens a palette with different color scales that you can apply to a cell selection to indicate their values relative to each other.

• Icon Sets will open a palette with icons. You can apply these icons to a cell selection to indicate values relative to each other by clicking the color scale thumbnail.

• New Rule allows you to create new rules.

• Clear Rules allows you to remove conditional formatting rules for the cell selection.

• Manage Rules opens a dialogue box where you can edit and delete rules.

Let's apply conditional formatting to our spreadsheet.

We're going to use Highlight Cell Rules.

First, we select the cells. This is our cell selection.

Now, we go to the Conditional Formatting button on the Ribbon and choose Highlight Cell Rules.

We want to cells to be highlighted when a value is less than 300. We chose Less Than from the side menu that appears when you click Highlight Cell Rules.

We want to format cells that are less than 300, so we're going to enter that into the box.

Next, we choose what colors we want to use to highlight the cells that have values less than $300.

We are going to choose Yellow Fill with Dark Red Text.

Click OK when you're finished.

If you frequently use the same formatting options for the cells in your worksheets, you may want to create a formatting style to save you time. A formatting style is a collection of formatting choices. It may include-- but not be limited to-- font, font size, and color.

To create a new style, first format a cell with the selection of styles that you want. 

Select the cell.

We've selected the cell below as our example:

Next, go to the Home tab to the Styles group, and click the downward arrow to the right of the Styles gallery.

Select New Cell Style from the dropdown menu.

Create a name for the style in the Style Name field, then choose the formatting options that you want to include in the style.

Click OK when you're finished.

As you can see, our new style now appears as the first style on the left in the Style Gallery.

Quick Analysis is a feature that was new to Excel 2013. It can make doing things such as creating charts, adding sums, or even formatting cells easier than ever before. In fact, you can do it in as little as two clicks of the mouse.

To format cells using Quick Analysis, first select the cells that you want to format.

When you select your cells, you'll see the Quick Analysis button appear at the bottom right of the selection. We highlighted it in red below.

Click on it and the Quick Analysis tool opens. We have now highlighted it in red.

Let's zoom in and get a better look at it:

The Format Painter tool is located under the Home tab in the Clipboard group.

The Format Painter looks like a broom, but it acts more like a paintbrush. 

Using it, you can "borrow" the formatting from cells or selections of cells and apply the same formatting somewhere else.    It's operates a lot like the "copy" function in Excel, except instead of copying text, you're copying formatting.

To use the Format Painter, select the cell or cells for which you want to copy formatting. 

Now, click the Format Painter button. You'll notice that the cursor changes to a paintbrush.

Next, select the cell(s) you want to change to paint with the borrowed format.