Use Number Formats in Excel 2016

Formatting numbers in Excel 2016 helps you display data neatly and clearly. Whether you’re dealing with currency, percentages, or dates, using the correct number format ensures that your spreadsheet looks professional and your data is easy to understand. Let’s learn how to use number formats correctly in Excel 2016.

Why Number Formats Matter

Excel automatically applies number formatting depending on how you enter data. For example, if you type “10%”, Excel understands it as a percentage. If you type “₹500”, it recognizes it as currency. But sometimes, you may need to manually set the correct format to ensure your data displays properly.

Using the wrong format can cause confusion or calculation errors. So it’s always a good idea to double-check the number formats used in your worksheet.

How to Apply Number Formats in Excel 2016

Step 1: Select the Cells

First, select the cells that you want to format. You can drag over multiple cells or click the column letter to select the entire column.

Step 2: Use the Number Format Menu

  • Go to the Home tab on the ribbon.
  • In the Number group, click the small dropdown menu next to the format box.
  • Choose a predefined format like General, Number, Currency, Accounting, Percentage, or Date.

Each option changes the way your data appears. For example, “Currency” adds a symbol like ₹ or $, while “Percentage” multiplies the number by 100 and adds a % sign.

Tip: You can quickly open the Format Cells dialog box by pressing Ctrl + 1 on your keyboard.

Using the Format Cells Dialog Box

For more control, open the Format Cells dialog box. Here’s how:

  • Select your cells.
  • Press Ctrl + 1 or right-click and choose Format Cells.
  • Go to the Number tab.
  • Choose from categories such as Number, Currency, Date, Time, Percentage, Fraction, Scientific, or Custom.

You can adjust decimal places, choose a currency symbol, or decide how negative numbers should display (for example, with red color or brackets).

Creating Custom Number Formats

Custom formats let you design how numbers look exactly as you want. To create one:

  • Select the cells you want to format.
  • Open the Format Cells dialog box.
  • Go to the Custom category.
  • In the Type box, enter your custom format code. For example:
    • 000-000-0000 – displays numbers as phone numbers.
    • ₹#,##0.00 – displays currency with two decimal places.
    • dd-mmm-yyyy – shows dates in a readable format like 21-Oct-2025.

Click OK to apply your changes. You’ll see the data instantly take on the new style.

Important Tips for Using Number Formats

  • Apply to entire columns: Select the column letter to apply formatting to all future entries.
  • Check your data: After formatting, scan your data to make sure it appears correctly.
  • Formatting vs. Value: Remember that formatting only changes how data looks—not its real value. The original number is always visible in the formula bar.
  • New data inherits formatting: Any new value you type into a formatted cell will automatically follow the same format.
Pro Tip: If your numbers show as “#####” or appear incorrect, simply widen the column or recheck your number format.

5 FAQs on Using Number Formats in Excel 2016

1. What is the default number format in Excel 2016?

The default format is “General,” which shows numbers as they are entered without any special formatting.

2. How can I remove a number format?

Select the formatted cells, go to the Home tab, and choose “General” from the Number Format dropdown to remove custom formatting.

3. Why does my number display as “#######”?

This usually happens when the column is too narrow. Increase the column width to make the number visible.

4. Can I apply number formats automatically?

Yes. When you enter data like “10%” or “₹200,” Excel automatically applies percentage or currency formatting.

5. Do number formats affect calculations?

No, formatting changes only how the value looks, not the actual value used in calculations.

Conclusion

Using number formats correctly in Excel 2016 is essential for creating clean, easy-to-read, and professional-looking spreadsheets. Whether you’re dealing with financial data, dates, or simple numbers, applying the right format helps you present information clearly and avoid errors. Once you understand how to apply and customize formats, your Excel sheets will not only look better but also work more efficiently.

You May Also Like

Loading...