How to Format Cells in Excel: Numbers, Text, Scientific Notation, and More
The tutorial covers the essentials of Excel formatting for numbers, text, currency, percentages, accounting numbers, scientific notation, and more. It also demonstrates quick methods to format cells across all versions of Excel, including 365, 2021, 2019, 2016, 2013, 2010, 2007, and earlier.
While most users are familiar with basic text and numeric formatting in Excel, do you know how to specify the exact number of decimal places, apply a particular currency symbol, or use the correct scientific notation or accounting number format? And are you aware of the Excel number format shortcuts that enable you to apply the desired formatting with a single click?
Understanding Excel Format Basics
By default, Microsoft Excel assigns the General format to all cells in its worksheets. With this default setting, any data entered into a cell appears exactly as typed.
However, there are instances where Excel may alter how a cell’s value is displayed while keeping the format as General. For example, entering a large number in a narrow column might cause Excel to display it in Scientific notation (e.g., 2.5E+07), even though the original number (25000000) remains visible in the formula bar.
Excel can also automatically adjust the format of a cell based on the content entered. For instance, typing dates like 1/4/2016 or shorthand dates like 1/4 will prompt Excel to recognize them as dates and switch the cell format accordingly.
To quickly verify the format applied to a specific cell, select the cell and inspect the Number Format box located in the Number group on the Home tab.
Image
It’s crucial to note that formatting cells in Excel alters only how a cell’s value is visually presented, without modifying the value itself.
For instance, if a cell contains the number 0.5678 and you apply a format to display only two decimal places, it will appear as 0.57. However, the original value remains 0.5678, which Excel utilizes in all calculations.
Similarly, you can customize how date and time values are displayed, but Excel retains the original value (serial numbers for dates and decimal fractions for times) for use in Date and Time functions and formulas.
To view the actual value behind a formatted number, select the cell and check the formula bar.
How to Format Cells in Excel
To modify the appearance of numbers or dates, display cell borders, adjust text alignment and orientation, or make other formatting changes, you’ll use the Format Cells dialog. This dialog is the primary tool for cell formatting in Excel and is conveniently accessible through multiple methods due to its frequent use.
4 Ways to Access the Format Cells Dialog To change the formatting of a single cell or a group of cells, follow these steps:
- Press the shortcut Ctrl + 1.
- Right-click the cell (or press Shift + F10), then choose Format Cells from the context menu.
- Click the small arrow in the bottom right corner of the Number, Alignment, or Font group on the Home tab, then select Format Cells.
- On the Home tab, in the Cells group, click the Format button, then choose Format Cells from the dropdown menu.
Once the Format Cells dialog appears, you can begin formatting the selected cell(s) using the various options available across its six tabs.
