Formatting lets you change the appearance of data to control exactly how Excel will display the data on a worksheet.
An example of formatting is to add dollar signs, percentage signs and decimal places to Excel data.
Understanding number formats
When we apply formatting to a cell we overlay the actual number with a specific look.
Join the 'Excel at Work Insiders' group today!
100% free. Join now and unlock offers exclusive to this group!
Receive tips and guidance to help you SAVE TIME, WORK SMARTER and IMPROVE YOUR UNDERSTANDING of Microsoft® Office.
Every cell within a workbook has a predefined number format of ‘General’. This means when you type data into the cell (in most instances) it will remain without any specific formatting.
However if you would like the data in a cell to display decimal places, a dollar or percentage sign or, in the case of dates, a particular style of date you will need to apply a number format.
Applying a number format
To apply a format to a cell or range of cells first select the cell(s) and then from the Home tab do one of the following.
- Use the Accounting, Percentage, Comma, Increase and Decrease Decimal places buttons to quickly apply formatting to the data. Click the Accounting drop-down to apply different currency symbols. Use Comma to apply the Accounting format without the currency symbol. Percentage will format as a percentage with no decimal places.
- Click the drop-down arrow on the Number Format option box. Select the option that best fits your requirements.
|General||No specific format. Leading 0 (zero) and zeros after a decimal will not be displayed.|
|Number||Displays leading zeros and applies 2 decimal places.|
|Currency||Displays a dollar sign in front of the number. Applies 2 decimal places.|
|Accounting||Displays a dollar sign on the very left of the cell. Applies 2 decimal places.|
|Short/Long Date||Formats a number as a date.|
|Text||Allows you to enter numbers with leading zeros, e.g. 0842. Great for mobile numbers and post codes.|
Note: the Percentage option on the Number Format list formats numbers as a percentage with two decimal places. If you would prefer to format as a percentage without decimal places click the Percent Style button in the Number group.
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
Learn the BEST Excel keyboard shortcuts. Perfect for Beginners and for those who want to save time and work smarter. It doesn’t matter if I’m delivering a Stage 1 or a Stage 3 Excel training course – when I’m demonstrating to the class I whip around the screen, format, select and modify and update using shortcut
If you share Excel files outside of your organisation you’ll enjoy this post where I share with you how to remove Excel formulas and keep the results so that you can confidently send a file knowing you aren’t disclosing confidential information inside of your formulas.
Learn how to move, copy and paste information in Excel. Once you have mastered Cut, Copy and Paste you will find yourself shifting data around easily and reusing copied data, saving you the need to recreate the same information again. All Windows applications have access to the Windows Clipboard. This is a special area used for
Learn how to preview your work before printing and how to print all or part of an Excel worksheet. Join the ‘Excel at Work Insiders’ group today!100% free. Join now and unlock offers exclusive to this group!Receive tips and guidance to help you SAVE TIME, WORK SMARTER and IMPROVE YOUR UNDERSTANDING of Microsoft® Office. envelope envelope Join
Learn how to insert page breaks and scale an Excel worksheet for printing. Once you have your worksheet created and formatted you may want to print it. Printing can be extremely challenging if you don’t know how to control where page breaks occur and how to fit your worksheet to print on a page so that