Cómo evitar la ruptura de las fórmulas

Cómo evitar la ruptura de las fórmulas

Si Excel no puede resolver una fórmula que intenta crear, es posible que vea un mensaje de error similar a este:

Imagen del diálogo "Error en esta fórmula" de Excel

Desafortunadamente, esto significa que Excel no puede entender lo que está intentando hacer, por lo que es posible que quiera empezar de nuevo.

Para empezar, seleccione Aceptar o presione ESC para cerrar el mensaje de error.

Volverá a la celda con la fórmula errónea, que estará en modo de edición, y Excel resaltará la parte que contenga el problema. Si todavía no sabe qué hacer desde allí y desea volver a empezar, puede presionar ESC de nuevo o seleccionar el botón Cancelar en la barra de fórmulas, lo que le sacará del modo de edición.

Imagen del botón Cancelar en la barra de fórmulas

Si desea avanzar, la siguiente lista de comprobación proporciona pasos de solución de problemas para ayudarle a averiguar qué puede haber salido mal.

Nota: Si usaOffice para la Web, es posible que no vea los mismos errores o que las soluciones no se apliquen.

Excel produce una variedad de errores de libra (#) como #VALUE!, #REF!, #NUM, #N/A, #DIV/0!, #NAME?, y #NULL!, para indicar que algo de la fórmula no funciona correctamente. Por ejemplo, el #VALUE! se debe a un formato incorrecto o a tipos de datos no admitidos en los argumentos. O bien, verá el #REF. si una fórmula hace referencia a celdas que se han eliminado o sustituido por otros datos. Las instrucciones de solución de problemas serán diferentes para cada error.

Nota: #### no es un error relacionado con una fórmula. Solo significa que la columna no tiene el ancho suficiente para mostrar el contenido de la celda. Tan solo tiene que arrastrar la columna para ampliarla o ir a Inicio > Formato > Autoajustar ancho de columna.

Imagen de Inicio > Formato > Autoajustar ancho de columna

Consulte cualquiera de los temas siguientes correspondientes al error de libra que ve:

Cada vez que abra una hoja de cálculo que contenga fórmulas que hacen referencia a valores de otras hojas de cálculo, se le pedirá que actualice las referencias o las deje tal y como están.

Cuadro de diálogo de referencias erróneas en Excel

Excel muestra el cuadro de diálogo anterior para asegurarse de que las fórmulas de la hoja de cálculo actual siempre apuntan al valor más actualizado en caso de que el valor de referencia haya cambiado. Puede elegir actualizar las referencias u omitirlas si no quiere actualizarlas. Aunque decida no actualizar las referencias, siempre puede actualizar los vínculos de forma manual en la hoja de cálculo cuando quiera.

Siempre puede deshabilitar el cuadro de diálogo para que no aparezcan en el inicio. Para ello, vaya a Opciones de > de > De > Generaly desactive el cuadro Preguntar por actualizar vínculos automáticos.

Imagen de la opción Consultar al actualizar vínculos automáticos

Importante: Si es la primera vez que trabaja con vínculos rotos en fórmulas, si necesita un actualizador para resolver vínculos rotos o si no sabe si actualizar las referencias, vea Controlar cuándo se actualizan las referencias externas (vínculos).

Si la fórmula no muestra el valor, siga estos pasos:

  • Asegúrese de que Excel está configurado para mostrar fórmulas en la hoja de cálculo. Para ello, seleccione la pestaña Fórmulas y, en el grupo Auditoría de fórmulas, seleccione Mostrar fórmulas.

    Sugerencia: También puede usar el método abreviado de teclado Ctrl + ` (la tecla situada encima de la tecla TAB). Al hacerlo, las columnas se ampliarán automáticamente para mostrar las fórmulas, pero no se preocupe, cuando vuelva a la vista normal, las columnas cambiarán de tamaño.

  • Si el paso anterior sigue sin resolver el problema, es posible que la celda tenga formato de texto. Haga clic con el botón derecho en la celda, seleccione Formato de celdas > General (o Ctrl + 1) y, después, presione F2 > Entrar para cambiar el formato.

  • Si tiene una columna con un gran rango de celdas con formato de texto, puede seleccionar el rango, aplicar el formato de número que prefiera y ir a Datos > Texto a columna > Finalizar. De esta forma se aplicará el formato a todas las celdas seleccionadas.

    Imagen del diálogo Datos > Texto en columnas

Cuando una fórmula no calcula, tendrá que comprobar si el cálculo automático está habilitado en Excel. Las fórmulas no calcularán si el cálculo manual está habilitado. Siga estos pasos para comprobar si hay cálculo automático.

  1. Seleccione la pestaña Archivo, seleccione Opcionesy, a continuación, seleccione la categoría Fórmulas.

  2. En la sección Opciones de cálculo, en Cálculo de libro, compruebe que esté seleccionada la opción Automático.

    Imagen de las opciones de cálculo automático y manual

Para obtener más información sobre los cálculos, vea Cambiar el recálculo, la iteración o la precisión de la fórmula.

Una referencia circular se produce cuando una fórmula hace referencia a la celda en la que se encuentra. La corrección es mover la fórmula a otra celda o cambiar la sintaxis de la fórmula a una que evite las referencias circulares. Pero en algunos escenarios quizás necesite las referencias circulares porque hace que las funciones iteren: se repitan hasta que se cumpla una condición numérica específica. En estos casos, deberá habilitar Quitar o permitir una referencia circular.

Para obtener más información sobre las referencias circulares, vea Quitar o permitir una referencia circular.

Si la entrada no empieza por un signo igual, no es una fórmula y no se realizará ningún cálculo (un error común).

Cuando escribe algo como SUMA(A1:A10), Excel muestra la cadena de texto SUMA(A1:A10) en lugar del resultado de una fórmula. Como alternativa, si escribe 11/2, Excel muestra una fecha, como 2-Nov o 11/02/2009, en lugar de dividir 11 por 2.

Para evitar estos resultados inesperados, la función siempre debe comenzar con un signo igual. Por ejemplo, escriba: =SUMA(A1:A10) y =11/2.

Si usa una función en una fórmula, cada paréntesis de apertura necesita uno de cierre para que la función se realice correctamente. Asegúrese de que todos los paréntesis corresponden a un par coincidente. Por ejemplo, la fórmula =SI(B5<0),"No válida",B5*1,05) no funcionará porque hay dos paréntesis de cierre, pero solo un paréntesis de apertura. La fórmula correcta es la siguiente: =SI(B5<0;"No válido";B5*1,05).

Las funciones de Excel necesitan argumentos (valores que deben proporcionarse para que la función funcione correctamente). Solo algunas funciones (como PI u HOY) no los necesitan. Para asegurarse de que la función tiene todos los argumentos necesarios, compruebe la sintaxis de la fórmula al comenzar a escribir la función.

Por ejemplo, la función MAYUSC solo acepta una cadena de texto o una referencia de celda como argumento: =MAYUSC("hola") o =MAYUSC(C2).

Nota: Verá los argumentos de la función en una barra de herramientas de referencia de función flotante debajo de la fórmula mientras la escribe.

Captura de pantalla de la barra de herramientas Referencia de funciones

Además, algunas funciones (como SUMA) solo precisan argumentos numéricos, mientras que otras (como REEMPLAZAR) requieren un valor de texto para, al menos, uno de sus argumentos. Si usa el tipo de datos incorrecto, las funciones pueden devolver resultados inesperados o mostrar un error #VALUE!

Si necesita buscar rápidamente la sintaxis de una determinada función, consulte la lista de funciones de Excel (por categoría).

No escriba números con formato de signos de dólar ($) o separadores decimales (,) en fórmulas porque los signos de dólar indican referencias absolutas y las comas son separadores de argumentos. En lugar de escribir en la fórmula 1,000 $, escriba 1000.

Si usa números con formato en argumentos, verá resultados de cálculo inesperados, pero también puede ver el error #NUM! Por ejemplo, si escribe la fórmula =ABS(-2.134) para buscar el valor absoluto de -2134, Excel muestra la #NUM! porque la función ABS solo acepta un argumento y ve los argumentos -2 y 134 como argumentos independientes.

Nota: Puede aplicar formato al resultado obtenido con separadores decimales y símbolos de moneda después de escribir la fórmula con números sin formato (constantes). Por lo general, no es buena idea colocar constantes en fórmulas, ya que pueden ser difíciles de encontrar si necesita actualizar más adelante y son más propensas a que se escriban incorrectamente. Es mucho mejor colocar las constantes en celdas, donde están abiertas y a las que se les hace referencia fácilmente.

Una fórmula no devolverá el resultado esperado si el tipo de datos de la celda no se puede usar al hacer cálculos. Por ejemplo, si escribe una fórmula simple, como =2+3, en una celda con formato de texto, Excel no podrá calcular los datos que ha escrito. En la celda solo aparecerá =2+3. Para corregir este problema, cambie el tipo de datos de la celda de Texto a General del modo siguiente:

  1. Seleccione la celda.

  2. Seleccione Inicio y seleccione la flecha para expandir el grupo Formato de número o número (o presione Ctrl + 1). A continuación, seleccione General.

  3. Pulse F2 para poner la celda en modo de edición y presione Entrar para aceptar la fórmula.

Si escribe una fecha en una celda con el tipo de datos Número, se mostrará como un valor de fecha numérico en lugar de como una fecha. Para mostrar este número como una fecha, elija un formato de Fecha en la galería de Formato de número.

Aunque es habitual usar x como operador de multiplicación en una fórmula, Excel solo acepta el asterisco (*). Si usa constantes en la fórmula, Excel muestra un mensaje de error y la fórmula se puede corregir reemplazando la x por el asterisco (*).

Cuadro de mensaje donde se le pregunta si quiere reemplazar x por * para la multiplicación

Sin embargo, si usa referencias de celda, Excel devolverá un #NAME? .

Error #¿NOMBRE? al usar x con referencias de celda en lugar de * para la multiplicación

Si crea un fórmula que incluye texto, escríbalo entre comillas.

Por ejemplo, la fórmula ="Hoy es " & TEXTO(HOY(),"dddd, mmmm dd") combina el texto "Hoy es " con el resultado de las funciones TEXTO y HOY para devolver Hoy es lunes, 30 de mayo.

En la fórmula, "Hoy es" tiene un espacio antes de la comilla final para proporcionar el espacio en blanco que desea entre las palabras "Hoy es" y "Lunes, 30 de mayo". Sin comillas alrededor del texto, la fórmula puede mostrar el error #NAME?.

Si lo desea, puede combinar (o anidar) hasta 64 niveles de funciones en una fórmula.

Por ejemplo, la fórmula =SI(SQRT(PI())<2,"Menos de dos!", "Más de dos!") tiene 3 niveles de funciones; la función PI está anidada dentro de la función SQRT,que a su vez está anidada dentro de la función SI.

Cuando escriba una referencia a valores o celdas de otra hoja de cálculo, si el nombre de esa hoja tiene un carácter no alfabético (por ejemplo un espacio), escriba el nombre entre comillas simples (').

Por ejemplo, para devolver el valor de la celda D3 en una hoja de cálculo denominada Datos trimestrales en el libro, escriba: ='Datos trimestrales'. D3. Sin las comillas alrededor del nombre de la hoja, la fórmula muestra el error #NAME?.

También puede seleccionar los valores o celdas de otra hoja para hacer referencia a ellos en la fórmula. Excel agrega automáticamente las comillas en torno a los nombres de hoja.

Cuando escriba una referencia a valores o celdas de otro libro, escriba el nombre del libro entre corchetes ( [ ]) seguido del nombre de la hoja de cálculo que contenga los valores o las celdas.

Por ejemplo, para hacer referencia a las celdas A1 a A8 de la hoja Ventas del libro de operaciones de Q2 abierto en Excel, escriba: =[Q2 Operations.xlsx]Ventas! A1:A8. Sin los corchetes, la fórmula muestra el error #REF!.

Si el libro no está abierto en Excel, escriba la ruta de acceso completa del archivo.

Por ejemplo, =FILAS('C:\Mis documentos\[Operaciones T2.xlsx]Ventas'!A1:A8).

Nota: Si la ruta de acceso completa contiene caracteres de espacio, debe escribir la ruta de acceso entre comillas simples (al principio de la ruta de acceso y después del nombre de la hoja de cálculo, antes del signo de exclamación).

Sugerencia: La forma más sencilla de obtener la ruta de acceso al otro libro es abrir el otro libro y, a continuación, desde el libro original, escriba =y use Alt+Tab para desplazarse al otro libro. Seleccione cualquier celda de la hoja que desee y cierre el libro de origen. La fórmula se actualizará automáticamente para mostrar la ruta de acceso completa al archivo y el nombre de la hoja con la sintaxis necesaria. Incluso puede copiar y pegar la ruta de acceso y usarla donde lo necesite.

Dividir una celda por otra celda que tenga cero (0) o ningún valor da como resultado un error #DIV/0!.

Para evitar este error, puede tener acceso a ella directamente y comprobar la existencia del denominador. Puede usar: 

=SI(B1;A1/B1;0)

Es decir, SI (B1 existe, entonces divide A1 por B1, de otro modo devuelve un valor 0).

Compruebe siempre si tiene fórmulas que hacen referencia a datos en celdas, rangos, nombres definidos, hojas de cálculo o libros antes de eliminar nada. A continuación, puede reemplazar esas fórmulas con sus resultados antes de quitar los datos de referencia.

Si no puede reemplazar las fórmulas con sus resultados, consulte esta información sobre errores y posibles soluciones:

  • Si una fórmula hace referencia a celdas que se han eliminado o sustituido por otros datos, y si devuelve un error #REF!, selecciona la celda con la #REF! . En la barra de fórmulas, seleccione #REF! y elimínelo. A continuación, vuelva a escribir el rango de la fórmula.

  • Si falta un nombre definido y una fórmula que hace referencia a ese nombre devuelve un error #NAME?, define un nombre nuevo que haga referencia al rango que quieras o cambia la fórmula para hacer referencia directamente al rango de celdas (por ejemplo, A2:D8).

  • Si falta una hoja de cálculo y una fórmula que hace referencia a ella devuelve un #REF! no hay ninguna manera de corregir esto, desafortunadamente, una hoja de cálculo que se ha eliminado no se puede recuperar.

  • Si falta un libro, una fórmula que haga referencia a él permanece intacta hasta que actualiza la fórmula.

    Por ejemplo, si la fórmula es =[Libro1.xlsx]Hoja1'!A1 y ya no tiene Libro1.xlsx, los valores a los que se hace referencia en ese libro siguen estando disponibles. Sin embargo, si modifica y guarda la fórmula que remite a dicho libro, Excel muestra el cuadro de diálogo Actualizar valores y pide que se escriba un nombre de archivo. Seleccione Cancelary, después, asegúrese de que estos datos no se pierden reemplazando las fórmulas que hacen referencia al libro que falta por los resultados de la fórmula.

A veces, al copiar el contenido de una celda, desea pegar solo el valor y no la fórmula subyacente que se muestra en el barra de fórmulas.

Por ejemplo, podría desear copiar el valor resultante de una fórmula en una celda en otra hoja de cálculo. O bien, es posible que desee eliminar los valores que ha usado en una fórmula después de haber copiado el valor resultante en otra celda en la hoja de cálculo. Ambas acciones provocan un error de referencia de celda no válida (#¡REF!) para que aparezcan en la celda de destino, ya que ya no se puede hacer referencia a las celdas que contienen los valores que usó en la fórmula.

Puede evitar este error pegando los valores resultantes de las fórmulas, sin la fórmula, en celdas de destino.

  1. En una hoja de cálculo, seleccione las celdas que contienen los valores resultantes de una fórmula que desea copiar.

  2. En la pestaña Inicio, en el grupo Portapapeles, seleccione Copiar Imagen del botón .

    Imagen de la cinta de Excel

    Método abreviado de teclado: Presione CTRL+C.

  3. Seleccione la celda superior izquierda del área de pegado.

    Sugerencia: Para mover o copiar una selección a otra hoja de cálculo o libro, seleccione otra pestaña de hoja de cálculo o cambie a otro libro y, a continuación, seleccione la celda superior izquierda del área de pegado.

  4. En la pestaña Inicio, en el grupo Portapapeles, seleccione Pegar Imagen del botón y, a continuación, seleccione Pegar valores, o presione Alt > E > S > V > Entrar para Windows u Opción > Comando > V > V > Entrar en un equipo Mac.

Para entender cómo calcula el resultado final una fórmula compleja o anidada, puede evaluar dicha fórmula.

  1. Seleccione la fórmula que se desea evaluar.

  2. Seleccione Fórmulas >Evaluar fórmula.

    Grupo Auditoría de fórmulas de la pestaña Fórmula

  3. Seleccione Evaluar para examinar el valor de la referencia subrayada. El resultado de la evaluación se muestra en cursiva.

    cuadro de diálogo Evaluar fórmula

  4. Si la parte subrayada de la fórmula es una referencia a otra fórmula, seleccione Paso a paso para mostrar la otra fórmula en el cuadro Evaluación. Seleccione Salir para volver a la celda y la fórmula anteriores.

    El botón Paso a paso no está disponible la segunda vez que aparece la referencia en la fórmula, o si la fórmula hace referencia a una celda de otro libro.

  5. Continúe hasta que se haya evaluado cada parte de la fórmula.

    La herramienta Evaluar fórmula no necesariamente le indica por qué la fórmula está rota, pero puede ayudar a señalar dónde. Esta herramienta puede resultar muy útil en fórmulas de mayor tamaño donde puede ser difícil encontrar el problema.

    Notas: 

    • Algunas partes de las funciones SI y ELEGIR no se evaluarán y el error #N/A podría mostrarse en el cuadro Evaluación.

    • Las referencias en blanco se muestran como valores cero (0) en el cuadro Evaluación.

    • Algunas funciones se recalculan cada vez que cambia la hoja de cálculo. Esas funciones, incluidas ALEATORIO, ÁREAS, ÍNDICE, DESPLAZAMIENTO, CELDA, INDIRECTO, FILAS, COLUMNAS, AHORA, HOY y ALEATORIO.ENTRE, pueden hacer que el cuadro de diálogo Evaluar fórmula muestre resultados distintos a los resultados reales de la celda de la hoja de cálculo.

¿Necesitas más ayuda?

Siempre puede preguntar a un experto en la Excel Tech Community u obtener soporte técnico en la Comunidad de respuestas.

Vea también

Información general sobre fórmulas en Excel

Detectar errores en fórmulas

Funciones de Excel (por orden alfabético)

Funciones de Excel (por categoría)

¿Necesita más ayuda?

Ampliar sus conocimientos de Office
Explorar los cursos
Obtener nuevas características primero
Únase a los participantes de Office Insider

¿Le ha sido útil esta información?

×