Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Introduction to Alteryx
      • Power BI
        • Power BI – Beyond the Basics
        • Power BI
      • Qlik Sense
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > How to Format Numbers in a Microsoft Excel 2016 Spreadsheet

How to Format Numbers in a Microsoft Excel 2016 Spreadsheet

During this Microsoft Excel 2016 training tutorial video, we will show you how to modify numbers entered in a cell into various formats such as fractions, scientific, accounting, special and time formats. We will also demonstrate how to format number as text, as well as how to edit contents and delete characters in a cell.

Facebook Linkedin Twitter

Like what you see? Get our complete Microsoft Excel 2016 training courses for beginner, intermediate and advanced learners. Click here to learn more. >>

Video Transcript

I’m starting this section with the Business Expenses workbook that I created in the previous section and I’m going to look first of all at some of the other available formats in Excel 2016. Then I’m going to turn my attention back to entering and editing data and point out some other very important things about that.

Now given that this is supposed to be an expenses sheet if I wanted to now start putting in the costs against the train fare York to London on Monday, March the 9th, I’m going to say that it was 127.50. So I’m going to put in 127.5. Now look what’s happened. Because I inadvertently had left a Date Format on that cell I come up with that really strange old date. So what I will normally need to do is to make sure either before I enter data or after enter data that I’ve selected the right sort of format.

Now if I had put 127.5 in the next one, well actually it’s the taxi fare so let’s not go too far. Let’s say 32.5. If I’d put that in that cell bear in mind that I haven’t formatted this cell as a Date Cell. It just stays as 32.5.

32.5, let’s look at what format has been assigned by Excel. It actually has assigned the General Format and it will very often assign the General Format. Now there are many number formats that can be applied to a cell with a number in it. So for instance I could assign a Fraction Format. Watch what happens if I apply the Fraction Format to that. I can choose the number of digits. I can choose whether I want it as say eighths or sixteenths or quarters. Supposing I wanted to do it as halves. Note the format isn’t applied until I click on OK and it becomes 32½.

Let’s format it again. This time I’m going to try Scientific Format. Note that I can choose the number of decimal places. Let’s stick with two decimal places. Click on OK and it becomes 3.25E+01. That’s 3.25 times 10 to the 1.
Let’s try another one. Now this time I’m going to apply a Counting Format. Notice that each time I change the category I have a different set of options on the right. So in the case of Accounting Format I can choose the number of decimal places and I can choose the symbol. If I were say a U.S. based accountant I may generally work in U.S. dollars but I may have clients around the world and for each client I would need to use the accounting symbols that are appropriate to that client.

So I’m going to stick with the default here, decimal places two, symbol dollar. Accounting Format is actually very similar to Currency Format. The main visible difference is that the currency symbol appears on the left of the cell. But also the way that negative amounts are dealt with is a standard way. So click on OK and I’ve now got $32.50 with the dollar currency symbol on the left of the cell.

Now could I apply that format to the figure above? Now bear in mind that I typed in a number there earlier on. It was converted to a date by Excel because the cell already had a Date Format applied to it. Let’s try changing that now to Accounting Format. Now watch carefully what happens here because if I select Accounting Format all seems to be okay but the symbol says None and the reason is that when Excel applied Accounting Format it looked at that cell and say that it already had a number in it although it was formatted as Date. And because there was no symbol, no currency symbol it stuck with none. So I need to be careful that I select my dollar symbol, click on OK and now I have my format applied.

Now I’d like to talk about a couple of the other formats now. Let’s go back into the Format Cells Dialogue. Towards the bottom of the list of categories, you’ve already seen Custom Category where you can actually make up your own formats. That’s out of scope for the course, certainly in these early stages. There are some custom formats already there and of course you can use those. And it’s pretty straightforward to create your own. But as I say that’s not something for this stage of the course.

Want More? Get Started With a Free Excel 2016 Course! Click Here

But I want to look at Special because depending on your locale you may have some special formats. Now I’m not going to go through any of these in detail at the moment but it’s worth being aware of them. And it’s also worth being aware of the fact that although you can apply these formats to cells what actually happens in the case of each format will depend not only on the format itself but also on what’s in the cell. So if I apply Zip Code Format to the description of the expense, Train Fare York to London there, click on OK, nothing really happens because as far as Excel is concerned that can’t be a zip code so it just doesn’t really do anything in this case. If I applied that same format to $127.50, don’t forget I typed a number in there, and applied Zip Code Format to that watch what happens. It does actually turn it into what it considers to be a reasonable go at that zip code. Of course I typed in 127.5 so it’s called it zip code 00128. So you have to be really careful about these formats in terms of what happens with many different types of cell content. I’m just going to undo that one.

Now as I mentioned earlier the available special formats depend on locale. So let’s just go back into Format Cells again and click on Special. I’m going to change my locale to, so let’s go for French (France) and I get a nice long list of available special formats there as well corresponding to various French special formats.

So that’s Special.

I want to talk about a couple of other very specific format options here now. I typed 7.2 in cell O13. I can format that as text. Now if I format it as text it’s absolutely fine. 7.2 can be text. You will notice there’s something unusual about it because it’s aligned on the left in the cell. But I won’t be able to use that in the same way for calculations as I could say a currency figure of 32.50. It is treated as text and of course text can contain numbers as text but it’s a very important distinction that it is treated as text in this case.

The other format that I could apply to that 7.2, again a very important one to be aware of, is Time Format. If I click on that as time it comes up with the rather mysterious time of 4:48. Now you might think how did it turn that into 4:48? Well the answer to that question is going to sound a little bit implausible but believe me it is true. The way that Excel stores times is that it stores the time as a fraction of a day. So in this case when it sees something like 7.2 it takes the 0.2 as the time. Now the 0.2 is a fifth, 0.2, of a day. So it divides 24 hours by 5 and comes up with 4.8 hours and that corresponds to a time of 4 hours and 48 minutes. So that’s where 4:48 comes from. So because of this way that Excel stores times 4:48 is a fifth of a day and therefore it corresponds to a numeric value of 0.2 of a day.

Now when you’ve got something in Time Format you have a number of important options. So here, for instance, you can choose the Time Format for the locale. Let’s go back to English (United States) and notice how it is on a twelve hour clock. So we have 1:30 PM Note the PM marker there. So let’s click on OK and what it comes up with, a fifth of a day, of course that’s in the morning. So it’s 4:48:00 AM.

If I wanted to do it on the 24 hour clock, go back into Format Cells again. Let’s choose the next format which is 13:30, that’s 24 hour clock format. That’s 4:48. If I apply that format to a cell and then I’m going to now type in that cell 2 PM what do you think will happen? I formatted the cell as 24 hour clock and I’ve typed 2 PM what do you think that’s going to say when I tick it? That’s right, 14:00. Excel looks at 2 PM and it says yep that looks like a time to me but the format applied to this cell is 24 hour clock format so I’m actually going to display it as 14:00. So Excel is pretty clever at that sort of thing.

The next thing I want to talk about in this section is editing the contents of a cell.

Now I’ve selected cell N9 and the contents of N9 are displayed in the Formula Bar. To edit those contents I can either go up to the Formula Bar and edit away in the usual way. I normally use the keyboard so I’d use the arrow keys to go left and right and the Backspace key to delete what’s to the left of the cursor, the Delete key to delete what’s to the right of the cursor. If you want to work within the cell and you may want to do this for example if you don’t use the Formula Bar or haven’t got it displayed at the moment. If you just double click somewhere within the cell the cell basically comes to the forefront. It looks as though the contents of O9 have been deleted there. They haven’t. It’s just the contents of cell N9 are now in front of those. You can use the arrow keys to go left and right. I can even go beyond the range of N9. I can go way off to the right. I could delete the word London and change it to say Bristol. And then when I finish I can either click elsewhere or I can use the tick mark next to the Formula Bar and my changes are saved.

If I’m using Touch it’s pretty much the same situation. Instead of a double click you do a double tap. You may want to make the screen a bit bigger if you’re going to do editing on a worksheet with as many columns and rows as this displayed. So let me just stretch this out. Now let me double tap on cell N12 and you can see I’ve got a cursor off to the right there. I can tap somewhere else, do my editing using my onscreen keyboard or my external keyboard if I’m using one, and other than that it works pretty much the same way that it works when you’re editing with a touch device anyway.

Want More? Get Started With a Free Excel 2016 Course! Click Here

And the next thing is pretty similar with touch or mouse and keyboard as well. If I want to delete individual characters within the contents of a cell I first of all need to make sure that I’ve got a cursor there. I can do this of course using either the Formula Bar or within the cell. As I’ve got the cursor in cell N12 here and I’m using touch at the moment I’ll demonstrate this with touch. It’d be the same with a keyboard. Press the Delete key and it’s the character to the right of the cursor that’s deleted. So press Delete once, that character is gone. If I want to delete the character to the left I use the Backspace key. If on the other hand I select a cell, let me select M10 using touch, and all I’ve got is the cell selected. Note I haven’t got a cursor within the cell. I can tell the cell is selected because I get a border. When I’m using touch I get those two little circles as well. If I now press either the Delete key or the Backspace key I will delete the whole contents of the cell. So let me press the Backspace key and the whole contents of that cell is deleted.

And that’s it for this section. I’ll see you in the next one.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

30 day vertical banner

Most Popular Posts

  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

START LEARNING How to Format Numbers in a Microsoft Excel 2016 Spreadsheet ON SIMONSEZIT.COM

START MY MEMBERSHIP

Similar Posts

Sharing OneNote: How to Share OneNote Notebooks.

How to Protect Cells in Excel Workbooks —5 Easy Ways

Microsoft Project 2019 – Entering Tasks

The Publisher 2013 Interface: Navigating the Ribbon

Creating PDF and XPS documents in Excel 2010

A Guide to Jira Issue Types [2022]

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2022 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)