• Ir al contenido principal
  • Ir a la barra lateral primaria
Tu Tiempo es oro - Jose Manuel Lodeiro Consultor y Formador Microsoft Excel y Macros VBA

Excel, Zapier y otras herramientas

  • Blog
  • Quién soy
  • Contacto

Formato para Miles y Millones en Microsoft Excel

28 mayo 2021 by Dejar un comentario

Jim wonders how he can get Excel to automatically display numbers using «k» for thousands and «m» for millions. As an example, if a cell contains the value $470,000 he would like it displayed as $470k; if it contains the value $1,107,432 he would like it displayed as $1.1m.

One obvious method is to create a formula that will display the information as desired. The following formula will take into account the magnitude of the number in cell B2 and then provide a formatted text string appropriate to that magnitude:

=IF(B2 < 1000,B2,IF(B2 < 1000000,

«$» & ROUND(B2/1000,1) & «k»,

«$» & ROUND(B2/1000000,1) & «m»))

Remember that this is a single formula and should be entered entirely on one line. The drawback with such an approach, of course, is that the formula takes up space within your worksheet. To get around this you could, instead, create a custom format that will simply affect the display of the number in the cell.

To create a custom format, display the Home tab of the ribbon and click the small icon at the lower-right corner of the Number group. In the resulting dialog box, click Custom at the left side. Here’s the custom format you should create in the dialog box:

[>1000000]$#.0,,»m»;[>1000]$#,»k»;$#,##0

This format will display both millions and thousands using the desired notation. If the number is below a thousand then it will be displayed without any special notation. As appropriate, values are rounded to one decimal place.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6146) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

https://excelribbon.tips.net/T006146_Notation_for_Thousands_and_Millions.html

Archivado en:Uncategorized

Interacciones con los lectores

Deja una respuesta Cancelar la respuesta

Lo siento, debes estar conectado para publicar un comentario.

© 2022 by TuTiempoEsOro.com. Política de Privacidad. Términos y condiciones. Política de cookies. Ilustraciones: Ouch

Guía de 200 Atajos de Teclado en Excel

Mejora tu productividad con la guía en pdf con más de 200 atajos de teclado. ¡Descargar Ahora!

x