The primary purpose of Excel’s formatting tools is to make your worksheet more readable. For example, you can change the display of the numbers so that they don’t contain decimal places. This allows your audience to see the needed data without being inundated with superfluous numbers.
Excel’s formatting options can be partitioned into two buckets: cell formatting and data formatting. Cell formatting is essentially the act of changing the look and feel of the cells themselves. For instance, you can change the cell background colors, add borders, change alignment, add underline or bold effects to cell contents, and wrap text. Data formatting is the act of formatting the data in the most readable and appropriate way. For example, you can format a number to show as currency complete with the dollar symbol. Or you can format a date so that it shows as Jan-10 instead of 01/01/2010.
In this chapter, you’ll explore the various types of formatting you can apply to both your cells and your data.
One way to format data in your worksheet is to change the font used to display it. This gives data a different look and feel, which can help differentiate the type of data a cell contains. You can also change the font’s size for added emphasis.
Select the cells whose font and font size you want to change, or click the All Cells button (to the left of column A and above row 1) to format all the cells in the worksheet.
Click the Font field down arrow in the Home tab and scroll through the available fonts. When you find the one you want to use, click it to select it.
Click the Font Size field down arrow and scroll through the available sizes (in points). When you find the size you want to use, click it to select it.
The font and font size you selected are applied.
If you know the name of the font you want to apply, select the down arrow next to the Font field and type the first letter of the font name. You will immediately be moved to the portion of the list that starts with the typed letter.
There might be times when data is too wide to be displayed within a cell, particularly if you just applied formatting to it. Excel provides several alternatives for fixing this problem. You can select columns and specify a width, or force Excel to automatically adjust the width of a cell to exactly fit its contents.
Move the mouse pointer over one side of the column header and then click and drag the column edge to the desired width (the column size displays in the Name Box).
To resize multiple columns simultaneously, select the columns that you want to alter.
Click and drag one of the selected columns’ header edges to the desired width and release it.
All the selected columns are resized to the same width.
Here’s a quick and easy way to automatically make all columns fit their individual contents. Select the columns you want to alter, then move the cursor over the right side of the column header and double-click when the cursor changes to a two-headed arrow. Your columns will automatically snap tightly around their contents.
Generally, cells present a white background for displaying data, but you can apply other colors or shading to the background. You can even combine these colors with various patterns for a more attractive effect. In addition, you can change the color of the data contained within your worksheet’s cells.
Select the cells whose background color and/or font color you want to change.
To change the color of the text in the selected cells, click the Font Color down arrow on the Home tab and choose a color from the list (here, white).
To change the color of the selected cells’ background, click the Fill Color down arrow and choose a color from the list (here, blue). Excel applies the colors you chose.
Be sure a shading or color pattern doesn’t interfere with the readability of your data. To improve readability, you might need to make the text bold or select a text color that goes well with your cells’ background color.
If you print the worksheet to a non-color printer, the color you select prints gray—and the darker the gray, the less readable the data. Yellows generally print as a pleasing light gray that doesn’t compete with the data.
You can alter the display of different numbers depending on the type of data the cells contain. By formatting numeric data, you can display data in a familiar format to make it easier to read. For example, sales numbers can be displayed in a currency format, and scientific data can displayed with commas and decimals.
After you select the cells you want to format, click the Increase Decimal command on the Home tab twice (once for each decimal place you want displayed).
Click the Comma Style command on the Home tab to add a comma to the numeric data.
Click the Currency Style command on the Home tab to format numbers in the selected cells with a dollar sign ($), commas, a decimal point, and two decimal places.
If you click the Percent Style command on the Home tab, your numbers will be converted to a percentage, displayed with a % symbol.
If, after you apply a style to cells, any cells display the error #########, it simply means that the data in the cell exceeds the current cell width. Refer to the task “Changing Column Width” in Chapter 2, “Managing Workbooks and Worksheets,” to fix the problem.
When you enter numbers into Excel cells, the General format is the default. No specific number format (discussed in the next task) is applied. The General format is used when you are recording counts of items, incrementing numbers, or do not require any particular format. If you have applied another format to your cells, but want to return to this default General format, follow the steps in this task.
After you select the cells you want to format, click on the down arrow in the Number Format drop-down box (on the Home tab).
Click the General option in the Category list.
Excel changes the format.
Every now and then you may need to clear all the formatting on a sheet and start fresh. You can quickly remove all formatting by going to the Home tab and clicking Clear -> Clear Formats.
When you apply the Number format in Excel, it uses two decimal places by default. You have the option to alter the number of decimal places, use a comma separator, and even determine the way you want negative numbers to appear (for example, with a minus sign, in red, in parentheses, or some combination of the three).
After you select the cells you want to format, click on the down arrow in the Number Format drop-down found on the Home tab.
Click the Number option in the Category list.
Excel changes the format.
Under the Number group on the Home tab, you’ll find the Increase Decimal command and Decrease Decimal command. These commands allow you to quickly change the number of decimal places used in your numbers. You can use these to easily tweak your numbers.
When you apply the Currency format in Excel, it uses two decimal places and a dollar sign by default. You have the option to alter the number of decimal places, display a symbol for a different currency, and even determine the way you want negative numbers to appear.
After you select the cells you want to format, click on the down arrow in the Number Format drop-down box on the Home tab.
Click the Currency option in the Category list.
Excel changes the format.
Use the Decimal Places field in the Number tab of the Format Cells dialog box to change the number of decimal places used. To choose a different currency symbol, select it from the Symbol drop-down list.
The Accounting format automatically lines up the currency symbols and decimal points for the cells in a column. The Percentage format multiplies the cell value by 100 and displays the result with a percent symbol.
When you apply the Date format in Excel, it displays the date and time serial numbers as date values. There are numerous date types you can assign to your dates. For example, you might find it easier to skim through dates as numbers with or without the assigned year visible. Or, perhaps you would rather use the actual name of the month (as opposed to a numeral) for reference.
After you select the cells you want to format, go to the Home tab and click on the down arrow in the Number Format drop-down box.
Click the Short Date option in the Category list.
Excel changes the format.
You can use the Time format if you want to display just the time (not the date) in your spreadsheet. In addition, you can use the Custom format option to create a Date and Time format all your own.
When you type numeric data into a cell, the display defaults to a Number format. When you apply the Text format in Excel, it displays numbers as text regardless of whether the data in the cell is numeric or text-based. This can be convenient when you want to enter a number that isn’t meant to be calculated or used in a mathematical operation. For instance, you may need to enter a customer number which has leading zeros. Formatting this number as text ensures that the leading zeroes remain visible.
After you select the cells you want to format, click on the down arrow in the Number Format drop-down box (on the Home tab).
Click the Text option in the Category list.
Excel changes the format.
Another way to immediately make a number a textual cell entry is to type an apostrophe (‘) before you type the number. This tells Excel that the number is to be treated as text.
You can format the data contained in one or more cells as bold, italic, or underlined (or some combination of the three) to draw attention to it or make it easier to find. Indicating summary values, questionable data, or any other cells is easy with this type of formatting.
Select the cells in which you want to apply bold formatting and click the Bold command.
Select the cells in which you want to apply italic formatting and click the Italic command.
Select the cells in which you want to apply underline formatting and click the Underline command.
The bold, italics, and underlining are applied to the selected cells.
You can use several formatting techniques in combination, such as applying bold, italic, and underlining all at the same time. Simply select the text you want to format and click each of the commands on the Home tab.
Using Excel’s Merge and Center feature, you can group similar data under one heading. Columns of data usually have column headers, but they can also have group header information representing multiple columns.
Select the cells you want to merge together, including the cells that don’t contain any data.
Click the Merge and Center command on the Home tab.
The cells in the group header are merged, and the data is centered. Repeat the steps in this task as needed to group additional columns in your worksheet.
To unmerge or separate cells that have been merged, place your cursor in the merged cell, go to the Home tab, and click the Merge and Center command.
Excel provides several ways to format data, and one way is to align it. The most common alignment changes you make will probably be to center data in a cell, align data with a cell’s right edge (right-aligned), or align data with a cell’s left edge (left-aligned). The default alignment for numbers is right-aligned; the default alignment for text is left-aligned.
Select the cells in which you want to align the data to the left and click the Align Left command on the Home tab.
Select the cells in which you want to align the data to the right and click the Align Right command.
Select the cells in which you want to center the data and click the Center command.
The alignments are applied to the selected cells.
Depending on the formatting changes you make to a cell, data might not display properly. Increasing the font size or forcing data to wrap within a cell might prevent data from being entirely displayed or cause it to run over into other cells. You can frequently avoid these problems by resizing rows.
Move the mouse pointer over the bottom edge of the row header. Click and drag the row to the desired height; the row size is displayed in the Name Box.
To resize multiple rows simultaneously, select the rows you want to alter.
Click and drag one of the selected row’s bottom edges to the desired height, then release it. All the selected rows are resized to the same height.
Here’s a quick and easy way to automatically make all rows fit their individual contents. Select the rows you want to alter, then move the cursor over the top border of any of the row numbers and double-click when the cursor changes to a two-headed arrow. Your rows automatically snap tightly around their contents.
In addition to aligning the data in your cells horizontally, you can align your cell data in a vertical format. Perhaps you want the data in your cells to align to the top of the cell, the bottom of the cell, or the center of the cell, or to justify within the cell. Cell data defaults to the bottom of the cell, but you can change this according to the look you are going for.
Select the cells in which you want to align the data.
Go to the Home tab and click the Middle Align command.
The data is vertically aligned within the cell.
Excel lets you alter the orientation of cells—that is, the angle at which a cell displays information. The main reason for doing this is to help draw attention to important or special text. This feature can be convenient when you have a lot of columns in a worksheet and you don’t want your column headers to take up much horizontal space, or if you simply want the information to stand out.
After you select the cell or cells whose orientation you want to change, click the Orientation command in the Alignment group on the Home tab.
Choose Angle Counterclockwise as shown here.
The data reorients within the cell. (You might need to increase or decrease the height and width of the cells.)
Click the half circle in the Orientation group of the Alignment tab to quickly change the angle at which data is rotated within the selected cell(s).
Another way to format data is to allow text to wrap in a cell. For example, suppose a heading (row or column, for example) is longer than the width of the cell holding the data. If you are trying to make your worksheet organized and readable, it is a good idea to wrap the text in the heading so it is completely visible in a cell.
After you select the cell or cells whose text you want to wrap, click on the Wrap Text command in the Alignment group on the Home tab.
The data in the selected cells is automatically wrapped.
You might need to alter the column width to have the data wrap at the location you desire. Note that you also can align data that has been wrapped, which gives your text a cleaner look. Refer to the tasks “Changing Horizontal Data Alignment” and “Changing Vertical Data Alignment” earlier in this chapter to learn how to align data in cells.
Each side of a cell is considered a border. These borders provide a visual cue as to where a cell begins and ends. You can customize borders to indicate other beginnings and endings, such as grouping similar data or separating headings from data. For example, a double line is often used to separate a summary value from the data being totaled. Changing the bottom of the border for the last number before the total accomplishes this effect.
Select the cells to which you want to add some type of border.
Go to the Home tab and click the down arrow next to the Borders command, then choose an option from the list that appears—for example, All Borders.
The border is applied.
To remove a border, select the bordered cells, click the down arrow next to the Borders command, and choose the No Border option from the list that appears. Be careful, though; you might eliminate an intended border in a nearby cell. That’s why there are all kinds of border options on the drop-down list.
Another alignment option you might want to use is to indent entries within a cell. Doing so can show the organization of entries—for example, subcategories of a budget category.
After you select the cell or range whose data you want to indent, click the Increase Indent command the number of times you want the entries indented.
To decrease the indent, select the cell or range whose indent you want to decrease.
Click the Decrease Indent command to decrease the number of indents.
The indentation is decreased.
To make the effect of the indentation stand out, you might need to increase the width of the indented column. To do so, refer to the task “Changing Column Width” earlier in this chapter.
Excel allows you to quickly clear all the formatting you have added to cell data, returning numbers and text to their original format.
Select the cells whose formatting you want to clear.
Go to the Home tab and click on the down arrow next to the Clear command, and then choose Clear Formats.
The cell data remains, but all the formatting is gone.
If you choose Clear Contents, the formatting remains intact, but the text and data (contents) will be deleted (just as if you simply pressed the Delete key on the keyboard). If you choose Clear All, all the formatting and contents (and comments) are removed from the cell.
Hiding rows is a good way to hide calculations that aren’t really critical for your audience to see. You also can hide other rows that you want to include in the worksheet but don’t want to display. It’s kind of tricky to unhide a row because you need a way of selecting the hidden row; you’ll learn how here.
Click any cell in the row or the whole row that you want to hide.
To hide the row, click the drop-down arrow next to the Format command on the Home tab. Select Hide & Unhide, Hide Rows. (You can tell that row 5 is hidden by the jump in the row-header numbering.)
To unhide the row, click the drop-down arrow next to the Format command on the Home tab. Select Hide & Unhide, Unhide Rows.
The row is unhidden.
Hidden elements, whether they’re rows, columns, or worksheets, don’t print when you print the worksheet.
You also can hide a row by dragging its bottom border past the top border of the row you want to hide.
Hiding columns is a good way to hide calculations that aren’t really critical for your audience to see. You also can hide columns that you want to include in the worksheet but don’t want to display. It’s kind of tricky to unhide a column because you need a way of selecting the hidden column; you’ll learn how here.
Click any cell in the column you want to hide.
To hide the column, click the drop-down arrow next to the Format command on the Home tab. Select Hide & Unhide, Hide Columns. (You can tell that column C is hidden by the jump in the column-header lettering.)
To unhide the column, click the drop-down arrow next to the Format command on the Home tab. Select Hide & Unhide, Unhide Columns.
The column is unhidden.
You also can hide a column by dragging its right border past the left border of the column you want to hide.
Often, you’ll have worksheets that are meant for your eyes only. An example of this would be a worksheet where you document changes in the workbook over the course of development. This kind of worksheet would be administrative in nature and not meant for your audience. In this scenario, hiding the worksheet would be ideal.
After you select the tab of any sheet you want to hide, right-click on the tab and choose Hide. Excel hides the sheet.
To unhide the sheet, right-click on any tab and choose Unhide.
The Unhide dialog box opens, listing sheets that are hidden in your workbook. Double-click the worksheet name you want to unhide.
Be aware that savvy users will know how to look for and unhide your hidden sheets. Do not count on hidden sheets to reliably protect sensitive information. For data protection, consider password protecting your workbook as demonstrated in Chapter 2.
Using all the formatting capabilities discussed to this point, you could format your worksheets in a very effective and professional manner—but it might take a while to get good at it. In the meantime, you can use Excel’s Format As Table feature, which can format selected cells using predefined formats. This feature is a quick way to format large amounts of data and provides ideas on how to manually format data.
Select the cells to which you want to apply Format As Table.
Click on the down arrow next to the Format As Table command (found on the Home tab), then scroll through the available formats and click the one you want to apply to your data.
Click OK on the next pop-up to confirm that the selected range is correct.
The AutoFormat is applied.
If you go to the Design tab and then run your cursor over the different styles in the Table Styles group, the selected ranges change accordingly so that you can see a preview of what that style does to the data.
If you have taken the time to format a specific cell just so, you might decide you want to apply those same formatting options to other cells. Instead of repeating each step in the format process over and over again, you can simply use the Format Painter command.
Click the cell with the formatting that you want to copy and apply to other cells.
Click the Format Painter command on the Home tab; the mouse pointer changes to a Format Painter pointer (paintbrush symbol).
Click and drag the mouse pointer to select the cells to which you want to apply the copied formatting.
Release the mouse command. The formatting is applied to the data in the selected cells.
Double-click on the Format Painter command, (instead of single-click) and the Format Painter remains active, allowing you to format multiple areas without the need to constantly reactivate it. To turn Format Painter off, click it again.
Instead of assigning your data an existing Excel style (for example, Normal, Currency, Percent, and so forth), you can create your own style and apply it to cells. You begin by applying the specific formatting (for example, font, font style, font size, font color, and cell color) that you want the style to have, and then give the style a specific name.
Apply any specific cell formatting that you want the style to use in your worksheet (here, Arial, Bold, 12 pt, White text, Red fill color).
With the cell that contains the desired formatting selected, click on the down arrow next to the Cell Styles command on the Home tab then go to the bottom and choose New Cell Style.
Type a descriptive name for the new style in the Style name field (for example, Sales Header) and click OK.
The next time you exit Excel, you will be notified that you made a change to your global template and asked if you want to save the changes. If you want to keep the style you just created, click the Yes button; otherwise, click the No button.
Select the cell(s) to which you want to apply your newly created style.
With the cells that you want to apply the custom style to selected, click on the down arrow next to the Cell Styles command on the Home tab and then choose the Sales Header style in the Custom style group at the top.
The style is applied to the cells you selected.
Notice that there are also default styles for Data and Model, Titles and Headings, Themed Cell Styles, and Number Format. You can run your cursor over each option to see how it changes your data prior to selecting it.
There might be times when you want the formatting of a cell to depend on the value it contains. For this, use conditional formatting, which lets you specify conditions that, when met, cause the cell to be formatted in the manner defined for that condition. If none of the conditions are met, the cell keeps its original formatting. For example, you can set a conditional format such that if sales for a particular month are above $30,000, the data in the cell is bold and red.
Select the cells to which you want to apply conditional formatting, then click on the down arrow next to the Conditional Formatting command on the Home tab and choose New Rule.
In the conditional Formatting dialog box, choose the Format only cells that contain option.
Leave the first drop-down list as Cell Value. Display the second drop-down list to select the type of condition (for example, greater than).
You can copy the conditional formatting from one cell to another. To do so, click the cell whose formatting you want to copy and hen click the Format Painter command. Finally, drag over the cells to which you want to copy the formatting.
Type the value of the condition (the number that the cells must be “greater than”).
Click the Format command to set the format to use when the condition is met.
Click the options you want to set in the Format Cells dialog box (for example, Pink in the Color field and Bold in the Font style list), and click OK.
Click OK in the Conditional Formatting dialog box.
Excel applies the formatting to any cells that meet the condition you specified.
18.218.135.227