In Excel, you can format cells to add a professional appearance to your data. This can be a useful tool for reports or for organizing information to make it easier to read. In this post, we will show you how to format cells in Microsoft Excel 2010.
Formatting Cells
Note that in the “Home” tab of the Ribbon, you have the “Font” group, the “Alignment” group, and the “Number” group. These groups contain buttons that allow you to format the appearance of selected cells. To do this, just start by selecting the cells you want to format. Then click the desired button.
Note that some of the buttons have a small drop-down arrow next to them. If you click one of these small drop-down arrows, a menu will appear and allow you to specify more button settings. For instance, if you click the drop-down arrow to the right of the “Fill Color” button, it will display the palette of colors in a drop-down menu that you can use to set a new fill color. Once you change the color, the color strip underneath the paint can will change to reflect the new selection. In the future, all you need to do is click the “Fill Color” button to apply your previous color choice.
Let’s examine the buttons that you will find in each group on the “Home” tab. As with the other groups in the Ribbon, not every possible aspect of formatting is shown in the button groupings. For more advanced formatting of cells, you can click the small dialog box buttons in the lower right corner of either the “Font,” “Alignment” or “Number” button groups to open the “Format Cells” dialog box, where you have all formatting options available. Note that you can also open this dialog box by clicking the “Format” button in the “Cells” group on the “Home” tab in the Ribbon, and then selecting the “Format Cells…” command from the drop-down menu that appears.
In the “Format Cells” dialog box, each button group is displayed as a separate tab. You can click the tabs to switch between the formatting options that are available. We will discuss the options available on each tab in a separate lesson. For now, just note how to open the dialog box. You can close the “Format Cells” dialog box by clicking the “x” in the upper right corner of the dialog box.
Back in the “Home” tab of the Ribbon, you have several common formatting options available. In the “Font” group, you have the “Font,” “Font Size,” “Increase Font Size,” “Decrease Font Size,” “Bold,” “Italic,” “Underline,” “Borders,” “Fill Color,” and “Font Color” buttons available.
In the “Alignment” group, you have the “Top Align,” “Middle Align,” “Bottom Align,” “Orientation,” “Wrap Text,” “Align Text Left,” “Center,” “Align Text Right,” “Decrease Indent,” “Increase Indent,” and “Merge and Center” buttons.
In the “Number” group, you have the “Number Format,” “Accounting Number Format,” “Percent Style,” “Comma Style,” “Increase Decimal,” and “Decrease Decimal” buttons available.
The Format Cells Dialog Box
You can use the “Format Cells” dialog box in Excel to control all aspects of cell formatting for the currently selected cells in your worksheet. The “Format Cells” dialog box has six tabs that allow you to change cell properties. The sixth tab, “Protection,” is related to worksheet security and is discussed in “Chapter 28- Security Features.” It has no bearing on cell formatting. The other five tabs in this dialog box that do control cell formatting are: “Number,” “Alignment,” “Font,” “Border,” and “Fill.”
To apply formatting using this dialog box, first select the cell or cell range to which you want to apply formatting. Then click the “Format” button in the “Cells” group on the “Home” tab in the Ribbon. Select the “Format Cells…” command from the button’s drop-down menu to display the “Format Cells” dialog box. Within this dialog box, you then select the tab that contains the formatting aspects that you want to apply. Change any settings that you need to within each tab, and then click the “OK” button to close this dialog box and apply all of your settings to the selected cell or cell range. Now we will review what formatting options are available in each tab, from left to right, within this dialog box.
You can click the “Number” tab to set the formatting for number display in the selected cells. You can click on the names of the different types of numeric formatting in the list at the left side of this tab to view a description of the format and examples of the format to the right. This can also be used to remove an unwanted number formatting. To do this, first select the cells from which you want to remove the number formatting. Then select the “General” number format from the list on this tab to set the display of numbers in the selected cells back to their default, which is “General.” As always, click the “OK” button when you are done making your formatting changes to apply your formatting to the selected cells.
On the “Alignment” tab, you can set both the horizontal and vertical alignment of cell contents, set cell content indentation, rotate cell contents, and either select or deselect the check boxes in the “Text Control” section to shrink the text to fit in the cell, merge the selected cells together, or wrap text to fit within a cell. One very useful thing that this tab allows you to do is unmerge a merged cell. If you want to do this, select the merged cell that you wish to unmerge. Next, open the “Format Cells” dialog box, and select the “Alignment” tab. The check box for “Merge Cells” will be checked. Click it to remove the check mark and unmerge the cells when you next click the “OK” button.
Also in this tab, you also have the ability to change the text direction. Some languages like English and other European languages print from left-to-right. Other languages like Hebrew and Arabic print from right-to-left. You can change the order of the letters in the text box cells here for the language that you are using. It’s not really a frequently used function, but it is located in this tab if you ever need it.
In the “Font” tab, you can set all font display options. You can select the desired font face to use for the cell’s display by selecting the desired font to use from the “Font:” list. Select the desired font style to apply to the selected cells from the “Font style:” list. Use the “Size:” list to set the font size.
You can select a style of underlining from the “Underline:” drop-down. Use the “Color:” drop-down to set the font color. In the “Effects” section check or uncheck any of the three special effects: “Strikethrough,” “Superscript,” or “Subscript.” There is a preview window that lets you preview the font that you have created before applying it.
In the “Border” tab, you can set printed border display options for your selected cells. You can start by clicking on a line style in the “Line” section to select a line style. Then click the “Color:” drop-down below that to set the color of the border lines. To determine where the selected line will be applied, click the desired border button that surround the preview of the cell border shown in the “Border” section. If you just want to apply one of the preset borders that are available, then you can click the “None,” “Outline,” or “Inside” buttons that are available in the “Presets” section to apply a preset cell border instead. Note that you can select the “None” choice to remove all cell borders from the selected cells.
On the “Fill” tab, you can set the fill color, fill effects, or fill pattern for any selected cells. To do this, simply pick the fill color that you’d like to apply from the available palette shown in the “Background Color:” section to apply the selected fill color to the selected cells. Note that if you need a more specific color, you can click the “More Colors…” button to open the “Colors” dialog box. In the “Colors” dialog box, you can create almost any color you desire. This dialog box is available in almost all of the places where you can choose a color. You can either click the “Standard” tab and then select one of the colors shown in the honeycomb of color choices, or you can click the “Custom” tab and then select the color you want. If you opened the “Color” dialog box to select a color, then click the “OK” button once you have made a choice to return to the “Format Cells” dialog box.
If you want, you can now apply a simple two-color gradient as the fill effect for your selected cells. To do this, just click the “Fill Effects…” button to open the “Fill Effects” dialog box and display the “Gradient” tab. Use the “Color 1:” and “Color 2:” drop-down color pickers to select the two colors to use for your gradient. In the “Shading styles” section, choose a direction for the gradient. Then, depending upon your selection made in the “Shading styles” section, choose the specific variant of that style you want to use from the choices shown in the “Variants” section. Once you have made your choices, click the “OK” button at the bottom of the “Fill Effects” dialog box to return to the “Fill” tab in the “Format Cells” dialog box.
If you want to apply a pattern to your selected cells, use the “Pattern Color:” drop-down to select the pattern color that you want to use, and then select the pattern style to use from the “Pattern Style:” drop-down. Be aware that applying a pattern to a cell that has content within it will make the cell’s contents almost impossible to read when printed.
When you have set all of the desired formatting that you want to apply to your selected cells in all of the tabs in the “Format Cells” dialog box, just click the “OK” button to apply all of your changes to the selected cells in your worksheet.