Use of Masks in Excelby Heather Wilkins
Stop the hassle of manually formatting all your spreadsheets every time you update data. Excel 2013 has many formatting options, but you can also customize your own formatting. Custom formatting "masks" the actual values in the cells and presents them in a more readable format. This formatting doesn't change the values, though, so your data is safe. Data sets change over time, and custom formatting helps you keep your spreadsheet formatted correctly, even when negative numbers become positive numbers.
The formula for a custom format is <Positive>;<Negative>;<Zero>;<Text>. Each section is separated by a semi-colon. If you decide to skip a section, leave the section blank but include its semi-colon. Fill in each section with the formatting code you want to apply. For instance, your code could look like this: $0.0 "M";$(-0.0) "M";"-";@. This displays positive values with a dollar sign, one decimal place and an "M." Negative values have a dollar sign, parentheses, a negative sign, one decimal place and an "M." Cells with a zero value show a dash instead of a zero, and cells with text in them display the text. This formula enables you to apply one custom format to an entire table or set of data.
Rounding and Decimal Places
You can format how a number appears on the Excel spreadsheet and how the decimal places line up. To round for thousands, include a comma. Each comma you include rounds three decimal places. So, to display 3,589,475,000 as 3.6, your formatting code is 0.0,,,. Apply the same formatting to the negative numbers, too: 0.0,,,;-0.0,,,. To align your decimal places, use a question mark in your formatting code. For instance, to line up the decimal places in 58.245 and 1254.2, the formatting code is ????.???. To only display one decimal place, change the code to ????.?.
Add Text, Colors and Indents
You can use custom formatting to add text after numbers. For instance, to display 3,589,475,000 as 3.6 B, use this code: 0.0,,, "B". To display positive numbers in green and negative numbers in red, use this code: [Green] 0.0; [Red] 0.0. To automatically add an indent before a number, use an underscore and parentheses before the value. You can also add an indent from the right side of the cell by adding an underscore and parentheses after the value. For instance, _(0.0;0.0_) adds an indent before the positive value and an indent after the negative value. This prevents numbers in different cells from running up against each other.
Add Custom Formatting
To add custom formatting to an Excel spreadsheet, open the worksheet and select the cells you want to format. Press "Ctrl-1" to open the Format Cells box, and then scroll through the Type box and select a format code to get you started. You can then adjust it in the Type field, or simply enter in your own values. Click "OK" to apply the formatting. To remove custom formatting from a cell, open the Format Cells box and select "General" in the Category pane to remove all formatting. To change the cell's format, select another category or another custom format. If you no longer need a custom format, click the formula in the Type box, and then click "Delete."
- photo_camera Stockbyte/Stockbyte/Getty Images
Click here to provide feedback on this article