When you enter a value into your worksheet, Excel determines what type of value it is—number, text, date, and so on—and assigns the cell a format based on that data’s type.
- [Instructor] When you enter a value into a worksheet, Excel determines what type of value it is, it could be number, text, date, and so on. Excel, then sends, the cell format based on the data's type. If you want to assign a cell or format yourself, or if you need to change a format Excel assigned, you can do so using the controls on the Home tab of the ribbon. I'll demonstrate how to apply Number formats in this movie. My sample file is the NumberFormats workbook. You can find it in the Chapter Two folder of the exercise files collection.
When I discuss number formats, I'm actually expanding that to include dates, and percentages, and similar values. In this workbook, I am summarizing wind turbine performance. I have the date, its efficiency, the power generated in kilowatt hours, and the projected annual revenue for that specific window. My data is unformatted, which means that I will need to apply formats. Let's start in the cell B2 with the date. Now, you notice that the value is 43,291.
Excel starts counting from January 1st of 1900 and the date that is represented here is the 43,291st day since then. To apply a data format, click the cell that contains the value you want to represent as a date, and then, on the Home tab of the ribbon, click the Number Format controls down arrow, and select the type of date format you want, and you can have it do a Short Date, which is, in the US, the month, day, and the year, two-digit day, two-digit month, two-digit year.
Long Date, which includes the weekday, or Time. In this case, I'll use Short Date and you can see that it's July 10 of 2018. Efficiency would be presented as a percentage. I currently have 0.24. To represent that as a percentage, I will select cell B4, and then, beneath the Number Format control, I will click the Percentage button or Percent Style button, and you can see it's changed to 24%.
Power Generated, on the other hand, is a number. In this case, a whole number. In the US, the convention is to use commas to separate thousands from hundreds, so, I will click cell B5, and then, again, below the Number Format control, I will click the Comma Style. Two things to note, we have the Power Generated of 1800 and that's 1,800 with a comma separator, but we also have two decimal places. In this case, I know that I will never have any values on the decimal, so, I can decrease the decimal point values that displayed.
To do that, I go to the right of the Comma Style button and I can either increase decimal, in which case, it goes up to three, or decrease, and with every click, it takes one away, and I'm down to 1800. Finally, I have my projected annual revenue. That's in cell B6, which I'll click now. I can apply Accounting Format to that. That will always have comma separators for thousands, and millions, and so on. Also, two digits to the right of the decimal point, and a currency symbol.
If you want to apply the Default Format, just click the button here. In my case, it is US dollars, for the Accounting Number Format. On the other hand, if you have a different local currency set, perhaps, British pounds, you'll see that there. To select a different currency symbol, click the Accounting Format's down arrow, and you'll see English for the US, English for United Kingdom, the euro, the Chinese yen, French frank and so on. In this case, let's say that I'm looking at a European generator, so I will click Euro and the format is applied.
Proper formatting makes your data easier to understand. Take the time to experiment with your number formats and change them, so you can present your data effectively.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.