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 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in 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 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • 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
        • Python Object-Oriented Programming
        • 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
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • 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
  • Get Started
Home > Microsoft Excel > How to Stop Excel From Rounding? 4 Easy Ways

How to Stop Excel From Rounding? 4 Easy Ways

(Note: This guide on how to stop Excel from rounding is suitable for all Excel versions including Office 365)

In Excel, numbers play a very major role. Large numbers can be quite bothersome. But, it is even more annoying when Excel automatically rounds off a larger value into a smaller value.

Though in some cases you might need to round off the numbers, in other cases it is unnecessary to round off the values. For instance, a credit card number or transaction ID has a sequence of 15 characters or more. These digits are specific and cannot be rounded off. But, Excel automatically rounds off the values. So what do you do? 

In this article, I will show you why Excel rounds the values and how to stop Excel from rounding the values using 4 easy methods.

You’ll Learn:

  • Why Does Excel Round Larger Numeric Values?
  • How to Stop Excel From Rounding Values?
    • By Changing the Format of the Cells
      • By Changing Format to Currency
    • By Changing the Decimal Places
    • By Adding Apostrophe
    • By Extending the Width of the Column

Watch our video on how to stop Excel from rounding numbers

Related:

How to Use the ROUND Function in Excel?

How to Convert Excel to Word? 2 Easy Methods

How to Sort Dates in Excel? 6 Easy Methods

First, let us see why and how Excel rounds the values.

Why Does Excel Round Larger Numeric Values?

Consider an example, enter a number consisting of more than 18 digits and press Enter. 

In a number sequence consisting of many digits, Excel automatically rounds off the numeric values greater than 15 digits. Only the first six digits are appear and the other exceeding digits are shown as exponential(E) values.

However, in the formula bar, the whole number is displayed up to 15 digits and the digits after that are converted to zero.  

Numbers rounded in Excel
Numbers rounded in Excel

You might think that, maybe Excel displays only 15 digits in the front end but stores the complete input value. However, even in the backend Excel only takes the first 15 digits into account and the rest are converted to zero.

Other reasons Excel rounds values might be due to change in decimal places, shorter column width, and improper formatting of the cell.

Now, let’s see how to stop Excel from rounding the values.

How to Stop Excel From Rounding Values?

By Changing the Format of the Cells

Excel rounds off larger numbers because of the different formatting of the cell. In Excel, the default cell formatting is General. When the cell format is general, Excel only shows a specific number of digits. However, if the cell formatting is Number, Excel can show more digits in the cells.

To change the cell formatting from General to Number, select the cell you want to change the format.

Go to Home and in the Number section from the dropdown select Number.

How to stop rounding in Excel by changing the number format
How to stop rounding in Excel by changing the number format

Now you can see the format of the cell changed, and the value exhibited as a complete number.

Excel displays the whole numbers
Excel displays the whole numbers

You can also use a keyboard shortcut to change the format of the cells. 

Select the cells to change the formatting. Now press Ctrl +1. 

This opens up a Format Cells dialog box. 

Select Number. You can also specify the decimal places and significant digits and click OK. 

Format cell as Number
Format cell as Number

This converts the format of the cells and prevents Excel from rounding the values.

Excel displays the complete number
Excel displays the complete number

By Changing Format to Currency

When numbers are to be represented as currencies for banking or accounting purposes, the numbers have to be accurate to achieve proper outcomes. In such cases, rounding off the values will cause inaccuracies in calculations and totals. 

Also, currency values contain multiple commas to differentiate the number of places. So, to avoid this you can change the format of the cell to Currency or Accounting.

You can change the format of the cell by going to Home. Under the Number section, select Currency or Accounting from the dropdown. Or, you can use the keyboard shortcut Ctrl+1 to choose the formatting. You can select the significant places after the decimal point and choose the symbol of currency. 

Change the format as Currency
Change the format as Currency

Now click OK. This changes the format of the cells into currency and applies the separators and decimal places. 

Number represented as a currency
Number represented as a currency

Also Read:

How to Indent in Excel? 3 Easy Methods

How to Delete a Pivot Table in Excel? 4 Best Methods

How to Unmerge Cells in Excel? 3 Best Methods

By Changing the Decimal Places

You can also prevent rounding the numbers in Excel by changing the decimal places. 

Consider a number having an 18-digit value. If you press enter, the number rounds up and shows up in an exponential format. Here, you can change the decimal place to display the original number. 

To change the decimal place, select the cell with the rounded value. Navigate to Home. Under the Number section, click on the Increase Decimal option to extend the significant digits. As the decimal places increases, the significant digits also increases.

How to stop Excel from rounding by changing the Decimal values
How to stop Excel from rounding by changing the Decimal values

This option gives you the liberty to choose the number of digits you want to display. 

Excel displays the complete number
Excel displays the complete number

By Adding Apostrophe

We know Excel does not display numbers with more than 15 digits. But, in case you want to display more numbers with more digits, what would you do?

In some cases, denoting numbers consisting of more than 15 digits is necessary. Values like bank balances or telephone numbers might not be displayed in Excel.

To overcome this, you can add an apostrophe (‘) in front of the number. Adding an apostrophe converts the number to a string.

Add apostrophe to convert a number to string
Add an apostrophe to convert a number to a string

Excel only has a display restriction for numbers, whereas a string does not have any restrictions.

However, you cannot perform any functions or operations on the numbers. In case you want to perform any function, you can convert the text to a number. 

By Extending the Width of the Column

The reason Excel was not able to display the full range might be because of the width of the column. 

Even after performing operations to prevent rounding off, sometimes Excel throws an error because column width is less than the number. 

Display number error because of shorter column width
Display number error because of shorter column width

To overcome this error, you just have to extend the column tab. 

Place the mouse pointer in the column header. You can see the mouse pointer change to a double-arrowed pointer. Click on the column and drag the column to your desired width.

How to stop Excel from rounding by increasing the column width
How to stop Excel from rounding by increasing the column width

Suggested Reads:

How to Make Excel Track Changes in a Workbook? 4 Easy Tips

How to Apply the Accounting Number Format in Excel? (3 Best methods)

How to Add Error Bars in Excel? 7 Best Methods

Closing Thoughts

Preventing Excel from rounding off the values can help you keep track of a larger range of values and provides maximum accuracy for performing operations.

In this article, we saw multiple methods to stop Excel from rounding off the values. Choose the method that suits you best.

If you need more high-quality Excel guides, please check out our free Excel resources center.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

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!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • 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

Similar Posts

How to Assign a Fixed Cost to a Task in Microsoft Project 2016?

How to Auto Fill Data in Microsoft Excel 2016

How to Delete Rows in Excel? 6 Efficient Ways

How to Navigate a PowerPoint Presentation in PowerPoint 2016

How to Use the TEXTJOIN Excel Function? 3 Easy Examples

Semi-Automatic Creation of an Org Chart in Visio 2010

Course Categories

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

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

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