Cómo evitar la ruptura de las fórmulas de Excel

Si Excel no puede resolver una fórmula que está intentando crear, es posible que reciba un mensaje de error como este:

Desafortunadamente, esto significa que Excel no entiende lo que está intentando hacer, por lo que tendrá que actualizar la fórmula o asegurarse de que está usando la función correctamente.

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

Si quiere avanzar, la siguiente lista de comprobación proporciona pasos de solución de problemas para ayudarle a averiguar qué puede haber fallado. Seleccione los encabezados para obtener más información.

¿Está usando los separadores de lista correctos en la fórmula?

Las fórmulas con más de un argumento usan separadores de lista para separar sus argumentos. El separador que se use puede variar en función de la configuración regional del sistema operativo y la configuración de Excel. Los separadores de lista más comunes son coma "," y punto y coma ";".

Una fórmula se interrumpirá si alguna de sus funciones usa delimitadores incorrectos.

Para obtener más información, vea: Errores de fórmula cuando el separador de lista no está establecido correctamente

¿Ve un error de almohadilla (#) en las fórmulas de Excel?

Excel produce una variedad de errores de almohadilla (#) 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! el error se produce por un formato incorrecto o el uso de tipos de datos no compatibles en los argumentos. O bien, verá el #REF! error si una fórmula hace referencia a celdas que se han eliminado o sustituido por otros datos. Los consejos de solución de problemas serán diferentes para cada error.

Consulte cualquiera de los siguientes temas correspondientes al error de almohadilla que se muestra:

• Corregir un error #¡NUM! error
• Corregir un error #¡VALOR! error
• Corregir un error #N/A
• Corregir un error #¡DIV/0! error
• Corregir un error #¡REF! error
• Corregir un error #¿NOMBRE?
• Corregir un error #¡NULO! error

Corregir vínculos rotos en fórmulas de Excel

Cada vez que abre una hoja de cálculo que contiene fórmulas que hacen referencias a valores de otras hojas de cálculo, se le solicitará que actualice las referencias o las deje como están.

Excel muestra el cuadro de diálogo anterior para asegurarse de que las fórmulas de la hoja de cálculo actual siempre apunten 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 archivo > Generales avanzados >y desactive la casilla Preguntar para actualizar vínculos automáticos.

La fórmula muestra la sintaxis en lugar del valor en Excel

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 hacer esto, seleccione la pestaña Fórmulas y en el grupo Auditoría de Fórmulas seleccione Mostrar fórmulas.

  Recomendación

  También puede usar el método abreviado de teclado Ctrl + ` (la tecla situada encima de la tecla TAB). Al hacer esto, 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 no le permite solucionar el problema, es posible que la celda tenga formato de texto. Puede hacer clic con el botón derecho en la celda y seleccionar Formato de celdas > generales (o Ctrl + 1) y, después, presionar 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, después, ir a Texto de datos > para finalizar columna>. De esta forma se aplicará el formato a todas las celdas seleccionadas.

  

Habilitar cálculo de libro automático si la fórmula no se calcula en Excel

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 está habilitado el cálculo manual. Siga estos pasos para comprobar el Cálculo automático.

1. Seleccione la pestaña Archivo, seleccione Opciones y luego seleccione la categoría Fórmulas.

2. En la sección Opciones de cálculo , en Cálculo del libro, asegúrese de que la opción Automático está seleccionada.

   

Para más información sobre los cálculos, vea Cambiar la precisión, la iteración y los nuevos cálculos de fórmulas.

Hay una o más referencias circulares en la fórmula

Una referencia circular se produce cuando una fórmula hace referencia a la celda en la que se encuentra. Para solucionar esto se puede 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 tales casos, deberá habilitar Quitar o permitir una referencia circular.

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

¿La función comienza con un signo igual (=)?

Si la entrada no empieza con un signo igual, no es una fórmula y no se calculará(es 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.

¿Coinciden todos los paréntesis de apertura y de cierre?

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álido";B5*1,05) no funcionará porque hay dos paréntesis de cierre, pero solo uno de apertura. La fórmula correcta tendría el siguiente aspecto: =SI(B5<0;"No válido";B5*1,05).

¿Están presentes en la sintaxis todos los argumentos necesarios?

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).

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

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

Controlar números sin formato en fórmulas de Excel

No escriba números con formato con signos de dólar ($) o separadores decimales (,) en las 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 los argumentos, obtendrá resultados inesperados en el cálculo, pero es posible que también vea el error #NUM! . Por ejemplo, si escribe la fórmula =ABS(-2,134) para buscar el valor absoluto de -2134, Excel mostrará el error #¡NUM! error, porque la función ABS acepta solo un argumento y ve los argumentos -2 y 134 como argumentos independientes.

¿Las celdas a las que se hace referencia pertenecen al tipo de datos correcto?

Es posible que la fórmula no devuelva los resultados esperados si el tipo de datos de la celda no se puede usar en los 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 error, cambie el tipo de datos de la celda de Texto a General de esta forma:

1. Seleccione la celda.
2. Seleccione Inicio y seleccione la flecha para expandir el grupo Número o Formato de número (o presione Ctrl + 1). Luego seleccione General.
3. Pulse F2 para poner la celda en modo de edición y presione Entrar para aceptar la fórmula.

Una fecha especificada en una celda que usa el tipo de datos Número puede mostrarse 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.

¿Está intentando multiplicar sin usar el símbolo *?

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 (*).

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

¿Faltan las comillas antes y después del texto de las fórmulas?

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 " se usa un espacio en blanco antes de las comillas de cierre para mantener este espacio entre las palabras "Hoy es" y "lunes, 30 de mayo". Si no se usan las comillas antes y después del texto, la fórmula indica el error #¿NOMBRE?.

¿Hay más de 64 funciones en una fórmula?

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

Por ejemplo, la fórmula =SI(RAIZ(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 RAIZ, que a su vez está anidada dentro de la función SI.

¿Los nombres de las hojas están escritos entre comillas simples?

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 obtener el valor de la celda D3 en una hoja de cálculo denominada Datos trimestrales en su 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 las 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.

Corregir rutas de libro externas en fórmulas de Excel

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 Operaciones del segundo trimestre que está abierto en Excel, escriba: =[T2 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 al archivo.

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

¿Ha dividido valores numéricos por cero?

Si divide una celda por otra que contiene cero (0) o ningún valor, aparecerá el 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).

¿La fórmula hace referencia a datos eliminados?

Compruebe siempre si tiene fórmulas que hacen referencia a los datos de 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, revise esta información sobre los errores y posibles soluciones:

• Si una fórmula hace referencia a celdas que se han eliminado o reemplazado por otros datos y devuelve un error #REF!, seleccione la celda con el #REF! #VALOR!. 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 el error #¿NOMBRE?, defina otro nombre que haga referencia al rango deseado o cambie la fórmula de forma que haga 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 el error #¡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 Cancelar y, a continuación, asegúrese de que estos datos no se pierden reemplazando las fórmulas que hacen referencia al libro que falta con los resultados de las fórmulas.

¿Ha copiado y pegado celdas asociadas a una fórmula en la hoja de cálculo?

A veces, al copiar el contenido de una celda, quiere pegar solo el valor y no la fórmula subyacente que se muestra en la 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 hacen que aparezca un error de referencia de celda no válida (#REF!) en la celda de destino, porque ya no se puede hacer referencia a las celdas que contienen los valores que usó en la fórmula.

Puede evitar este error si pega los valores resultantes de las fórmulas sin la fórmula en las 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 .
   
   Método abreviado de teclado: Presione CTRL+C.

3. Seleccione la celda superior izquierda de la zona en la que desea pegar la selección.

   Sugerencia

   Para mover o copiar una selección a otra hoja de cálculo o libro, seleccione la pestaña de otra hoja o pase a otro libro y después seleccione la celda superior izquierda del área de pegado.

4. En la pestaña Inicio, en el grupo Portapapeles, seleccione Pegar y, después, seleccione Pegar valores, o presione Alt > E > S > V > Entrar para Windows o Comando de > opción > V > V > Entrar en un equipo Mac.

Si tiene una fórmula anidada, debe evaluarla paso a paso

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.
   

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

4. Si la parte subrayada de la fórmula es una referencia a otra fórmula, seleccione Paso a paso para entrar para mostrar la otra fórmula en el cuadro Evaluación. Seleccione Paso a paso para salir para volver a la celda y fórmula anteriores.
   El botón Paso a paso en no está disponible la segunda vez que la referencia aparece 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 tiene por qué indicar si una fórmula es errónea, pero puede ayudar a señalar la causa. Esta herramienta puede resultar muy útil en fórmulas de mayor tamaño donde puede ser difícil encontrar el problema.

   Nota

   • Algunas partes de las funciones SI y ELEGIR no se evaluarán y el error #N/A podría aparecer en el cuadro Evaluación .
   • Las referencias en blanco se muestran como valores cero (0) en el cuadro Evaluación.
   • Algunas funciones se vuelven a calcular cada vez que cambia la hoja. 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.

¿Necesita más ayuda?

Siempre puede preguntar a un experto en Excel Tech Community u obtener soporte técnico en Comunidades.

Consulte también

Información general sobre fórmulas en Excel

Ayuda de Excel & aprendizaje