Use of Masks in Excel

by Heather Wilkins
Have the confidence that your report is easy to read by using custom formatting.

Have the confidence that your report is easy to read by using custom formatting.

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.

Guidelines

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."

About the Author

Heather Wilkins has five years of professional copyediting experience. She worked for Pearson Education, copyediting books about computer skills, the Internet, and general subjects. She also edits for online entry-level college courses. Wilkins holds a Professional Writing degree from Purdue University.

Photo Credits

  • photo_camera Stockbyte/Stockbyte/Getty Images