Detectar errores en fórmulas

La tabla siguiente contiene un resumen de los errores más comunes que puede realizar un usuario al escribir una fórmula y se explica cómo corregirlos.

Asegúrese de que	Más información
Empiece todas las funciones con el signo igual (=)	Si omite el signo igual, lo que escriba podría mostrarse como texto o como una fecha. Por ejemplo, si escribe SUMA(A1:A10), Excel mostrará la cadena de texto SUMA(A1:A10) y no realizará el cálculo. Si escribe 11/2, Excel mostrará la fecha 2-nov (si el formato de celda es General), en lugar de dividir 11 por 2.
Escriba todos los paréntesis de apertura y de cierre	Asegúrese de que siempre haya dos paréntesis, uno de apertura y otro de cierre. Al usar una función en una fórmula, es importante que cada paréntesis esté en su posición correcta para que la función funcione correctamente. Por ejemplo, la fórmula =SI(B5<0);"No válido";B5*1,05) no funcionará, ya que hay dos paréntesis de cierre y solo uno de apertura, cuando solo tendría que haber uno de cada. La fórmula correcta será similar a esta: =SI(B5<0;"No válido";B5*1,05).
Use dos puntos para indicar un rango	Para hacer referencia a un rango de celdas, use los dos puntos (:) para separar la referencia de la primera celda y la referencia de la última celda del rango. Por ejemplo, =SUMA(A1:A5), no =SUMA(A1 A5), lo que devolvería un #NULL! Error.
Escriba todos los argumentos necesarios	Algunas funciones requieren argumentos necesarios. De igual modo, asegúrese de no haber introducido demasiados argumentos.
Escriba el tipo de argumento correcto	Algunas funciones, como SUMA, requieren argumentos numéricos. Otras funciones, como REEMPLAZAR, requieren un valor de texto para, al menos, uno de sus argumentos. Si usa el tipo incorrecto de datos como argumento, Excel podría devolver resultados inesperados o mostrar un error.
Anide 64 funciones como máximo	No escriba o anide más de 64 niveles de funciones dentro de una función.
Escriba los nombres de otras hojas entre comillas simples	Si la fórmula hace referencia a valores o celdas de otras hojas de cálculo o libros cuyos nombres contienen espacios o caracteres no alfabéticos, escriba el nombre entre comillas simples ('), como ='Datos trimestrales'!D3 o =‘123’!A1.
Al hacer referencia a un nombre de hoja de cálculo en una fórmula, escriba un signo de exclamación (!) después del nombre	Por ejemplo, para obtener el valor de la celda D3 en una hoja de cálculo denominada Datos trimestrales en el mismo libro, use esta fórmula: ='Datos trimestrales'!D3.
Incluya la ruta de acceso a libros externos	Asegúrese de que todas las referencias externas contengan el nombre del libro y la ruta de acceso al libro. La referencia a un libro contiene el nombre del libro y tiene que escribirse entre corchetes ([NombreDeLibro.xlsx]). La referencia también necesita contener el nombre de la hoja de cálculo del libro. Aunque el libro al que quiere hacer referencia no esté abierto en Excel, puede incluir una referencia al libro en una fórmula. Tendrá que proporcionar la ruta de acceso completa al archivo, como en el ejemplo siguiente: =FILAS('C:\Mis documentos\[Operaciones T2.xlsx]Ventas'!A1:A8). Esta fórmula devuelve el número de filas en el rango que incluye las celdas A1 a A8 en el otro libro (8). Nota: Si la ruta de acceso completa contiene caracteres de espacio, como sucede en el ejemplo anterior, 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).
Escriba los números sin formato	No escriba números con formato en las fórmulas. Por ejemplo, si el valor que quiere escribir es 1000 €, escriba 1000 en la fórmula. Si escribe una coma como parte del número, Excel lo interpretará como un carácter separador. Si quiere que los números se muestren con separadores de miles o millones, o con símbolos de moneda, aplique el formato a las celdas después de escribir los números. Por ejemplo, si desea sumar 3100 al valor de la celda A3 y escribe la fórmula =SUMA(3,100,A3), Excel suma los números 3 y 100 y luego suma ese total al valor de A3, en lugar de sumar 3100 a A3, que sería =SUMA(3100,A3). Si escribe la fórmula =ABS(-2;134), Excel mostrará un error, ya que la función ABS solo admite un argumento: =ABS(-2134).

Puede implementar algunas reglas para comprobar si se producen errores en las fórmulas. Estas reglas no garantizan que la hoja de cálculo esté libre de errores, pero pueden resultar muy útiles para detectar errores comunes. Puede activar o desactivar cualquiera de estas reglas de forma individual.

Los errores se pueden marcar y corregir de dos formas: de uno en uno (como un corrector ortográfico), o bien inmediatamente cuando se produzcan en la hoja de cálculo al escribir datos.

Puede resolver un error mediante las opciones que muestra Excel o puede ignorarlo seleccionando Omitir error. Si ignora un error en una celda en particular, el error de esa celda no aparece en más comprobaciones de errores. Pero puede restablecer todos los errores que ha omitido previamente de modo que vuelvan a aparecer.

1. Para Excel en Windows, vaya a Opciones > archivo > Fórmulas o
   para Excel en Mac, seleccione el menú Excel > Preferencias > Comprobación de errores.

2. En Comprobación de errores, active la opción Habilitar comprobación de errores en segundo plano. Cualquier error encontrado se marca con un triángulo en la esquina superior izquierda de la celda.

   

3. Para cambiar el color del triángulo que indica dónde se produce un error, seleccione el color que prefiera en el cuadro Indicar errores con el color.

4. En Reglas de verificación de Excel, active o desactive las casillas de las reglas siguientes:

   • Celdas que contienen fórmulas que dan como resultado un error: una fórmula no usa la sintaxis, los argumentos o los tipos de datos esperados. Los valores de error incluyen #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, y #VALUE!. Cada uno de estos valores de error tiene causas diferentes y se resuelve de diferentes maneras.

     Nota: Si escribe un valor de error directamente en una celda, se almacena como valor de error pero no se marca como error. Pero, si una fórmula en otra celda hace referencia a esa celda, la fórmula devuelve el valor de error de esa celda.

   • Fórmula de columna calculada incoherente en las tablas: una columna calculada puede incluir fórmulas individuales que son diferentes de la fórmula de columna principal, que crea una excepción. Las excepciones de columna calculada se crean al realizar las acciones siguientes:

     • Escribir datos que no son fórmulas en una celda de columna calculada.

     • Escriba una fórmula en una celda de columna calculada y, a continuación, use Ctrl +Z o seleccione Deshacer en la barra de herramientas de acceso rápido.

     • Escribir una nueva fórmula en una columna calculada que ya contiene una o varias excepciones.

     • Copiar datos en la columna calculada que no coinciden con la fórmula de columna calculada. Si los datos copiados contienen una fórmula, esta fórmula sobrescribe los datos en la columna calculada.

     • Mover o eliminar una celda de otra área de hoja de cálculo a la que hace referencia una de las filas de una columna calculada.

   • Celdas que contienen años representados con 2 dígitos: la celda contiene una fecha de texto en la que el siglo se puede interpretar de forma incorrecta si se usa en fórmulas. Por ejemplo, la fecha de la fórmula =AÑO("1/1/31") podría ser 1931 o 2031. Use esta regla para comprobar fechas de texto ambiguas.

   • Números con formato de texto o precedidos por un apóstrofo: la celda contiene números guardados como texto. Esto suele ocurrir cuando se importan datos de otros orígenes. Los números almacenados como texto pueden causar resultados de ordenación inesperados, por lo que es mejor convertirlos en números. ‘=SUMA(A1:A10) se mostrará como texto.

   • Fórmulas incoherentes con otras fórmulas de la región: la fórmula no coincide con el patrón de las demás fórmulas cercanas. En muchos casos, las fórmulas adyacentes a otras fórmulas solo difieren en las referencias usadas. En el ejemplo siguiente de cuatro fórmulas adyacentes, Excel muestra un error junto a la fórmula =SUMA(A10:C10) en la celda D4, ya que las fórmulas adyacentes se incrementan en una fila y esta se incrementa en 8 filas, cuando Excel espera la fórmula =SUMA(A4:C4).

     

     Si las referencias usadas en una fórmula no son coherentes con las de las fórmulas adyacentes, Excel muestra un error.

   • Fórmulas que omiten celdas en una región: es posible que en una fórmula no se incluyan automáticamente referencias a datos que se inserten entre el rango de datos original y la celda que contiene la fórmula. Esta regla compara la referencia de una fórmula con el rango de celdas real que es adyacente a la celda que contiene la fórmula. Si las celdas adyacentes contienen más valores y no son celdas en blanco, Excel muestra un error junto a la fórmula.

     Por ejemplo, Cuando se aplica esta regla, Excel inserta un error junto a la fórmula =SUMA(D2:D4), ya que las celdas D5, D6 y D7 son adyacentes a las celdas a las que se hace referencia en la fórmula y a la celda que contiene la fórmula (D8), y esas celdas contienen datos a los que se debería haber hecho referencia en la fórmula.

     

   • Celdas desbloqueadas que contengan fórmulas: la fórmula no está bloqueada para su protección. De forma predeterminada, todas las celdas de una hoja de cálculo están bloqueadas para que no se puedan modificar cuando la hoja de cálculo esté protegida. Esto puede resultar útil para evitar errores inadvertidos, como eliminar o modificar fórmulas por error. Este error indica que la celda se desbloqueó, pero la hoja no está protegida. Asegúrese de que no desea que la celda esté bloqueada.

   • Fórmulas que se refieran a celdas vacías: la fórmula contiene una referencia a una celda vacía. Esto puede causar resultados no deseados, como se muestra en el ejemplo siguiente.

     Suponga que desea calcular el promedio de los números que figuran en la siguiente columna de celdas. Si la tercera celda está en blanco, no se incluye en el cálculo y el resultado es 22,75. Si la tercera celda contiene 0 el resultado es 18,2.

     

   • Los datos de una tabla no son válidos: hay un error de validación en una tabla. Compruebe la configuración de validación de la celda en la pestaña Datos > grupo Herramientas de datos > Validación de datos.

1. Seleccione la hoja de cálculo cuyos errores desee revisar.

2. Si la hoja de cálculo se calcula manualmente, presione F9 para actualizarla.

   Si no se muestra el cuadro de diálogo Comprobación de errores, seleccione Fórmulas > Auditoría de fórmulas > Comprobación de errores.

3. Si previamente ha omitido los errores, puede volver a comprobarlos haciendo lo siguiente: vaya a Opciones de > archivo > Fórmulas. Para Excel en Mac, seleccione el menú Excel > Preferencias > Comprobación de errores.

   En la sección Comprobación de errores , seleccione Restablecer errores omitidos > Aceptar.

   

   Nota: Al restablecer los errores omitidos, se restablecerán todos los errores en todas las hojas de cálculo del libro activo.

   Sugerencia: Desplácese hasta el cuadro de diálogo Comprobación de errores, justo debajo de la barra de fórmulas.

   

4. Seleccione uno de los botones de acción en el lado derecho del cuadro de diálogo. Las acciones disponibles son diferentes para cada tipo de error.

5. Selecciona Siguiente.

1. Junto a la celda, seleccione Comprobación de errores y, después, seleccione la opción que desee. Los comandos disponibles varían según el tipo de error y en la primera entrada se describe el error.

   Si selecciona Omitir error, el error se marcará para omitirlo en cada comprobación consecutiva.

   

Si una fórmula no puede evaluar correctamente un resultado, Excel muestra un valor de error, como #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! y #VALUE!. Cada tipo de error tiene causas diferentes y soluciones diferentes.

La tabla siguiente contiene vínculos a artículos que describen estos errores en detalle y una breve descripción que le ayudará a empezar.

Tema	Descripción
Corregir un error ####	Excel muestra este error cuando una columna no es lo suficientemente ancha para mostrar todos los caracteres de una celda, o una celda contiene valores de fecha u hora negativos. Por ejemplo, una fórmula que resta una fecha en el futuro de una fecha en el pasado, como = 06/15/2008-07/01/2008, da como resultado un valor de fecha negativo. Sugerencia: Haga doble clic entre los encabezados de columna para ajustar automáticamente el tamaño de la celda. Si se muestra ### porque Excel no puede mostrar todos los caracteres, esto lo corrige.
Corregir un error #¡DIV/0! error	Excel muestra este error al dividir un número cualquiera por cero (0) o una celda que no contiene ningún valor. Sugerencia: Agregue un controlador de errores como en el ejemplo siguiente, que es =SI(C2;B2/C2;0)
Corregir un error #N/A	Excel muestra este error cuando un valor no está disponible para una función o fórmula. Si usa una función como BUSCARV, ¿lo que intenta buscar tiene una coincidencia en el rango de búsqueda? Con frecuencia no la tiene. Intente usar SI.ERROR para eliminar #N/A. En este caso usaría lo siguiente: =SI.ERROR(BUSCARV(D2;$D$6:$E$8;2;VERDADERO);0)
Corregir un error #¿NOMBRE? error	Este error se muestra cuando Excel no reconoce texto en una fórmula. Por ejemplo, un nombre de rango o el nombre de una función podrían estar mal escritos. Nota: Si usa una función, asegúrese de que el nombre de la función este escrito correctamente. En este caso, SUMA está escrito de forma incorrecta. Quite la "e" y Excel la corrige.
Corregir un error #¡NULO! error	Excel muestra este error cuando se especifica una intersección de dos áreas que no se intersecan (cruzan). El operador de intersección es un carácter de espacio que separa las referencias en una fórmula. Nota: Asegúrese de que los rangos estén separados correctamente: como las áreas C2:C3 y E4:E6 no forman una intersección, se devuelve el error #¡NULO! al escribir la fórmula =SUMA(C2:C3 E4:E6). Si se coloca una coma entre los rangos C y E, se corregirá =SUMA(C2:C3;E4:E6)
Corregir un error #¡NUM! error	Excel muestra este error cuando una fórmula o función contiene valores numéricos que no son válidos. ¿Usa una función que se repite, como TIR o TASA? En ese caso, es probable que el error #¡NUM! se produzca porque la función no puede encontrar un resultado. Consulte los pasos de resolución en el tema de ayuda.
Corregir un error #¡REF! error	Excel muestra este error cuando una referencia de celda no es válida. Por ejemplo, es posible que haya eliminado celdas a las que hacían referencia otras fórmulas o que hubiera pegado celdas que movió encima de celdas a las que hacían referencia otras fórmulas. ¿Eliminó una fila o columna por error? Eliminamos la columna B en esta fórmula, =SUMA(A2;B2;C2), y ocurrió esto. Use Deshacer (Ctrl+Z) para deshacer la eliminación, vuelva a crear la fórmula o bien use una referencia de rango continua como =SUMA(A2:C2), que se actualizará automáticamente cuando se elimine la columna B.
Corregir un error #¡VALOR! error	Excel puede mostrar este error si en la fórmula se incluyen celdas que contienen diferentes tipos de datos. ¿Usa operadores matemáticos (+, -, *, /, ^) con diferentes tipos de datos? En ese caso, intente usar una función en su lugar. En este caso, =SUMA(F2:F5) corregiría el problema.

Cuando las celdas no están visibles en una hoja de cálculo, puede watch esas celdas y sus fórmulas en la barra de herramientas de la ventana Inspección. La ventana Inspección es una forma útil de inspeccionar, auditar o confirmar cálculos de la fórmula y resultados en hojas de cálculo de gran tamaño. Con la ventana Inspección, no es necesario desplazarse varias veces o ir a diferentes partes de la hoja de cálculo.

Esta barra de herramientas se puede acoplar como cualquier otra barra de herramientas. Por ejemplo, puede acoplarla en la parte inferior de la ventana. La barra de herramientas realiza un seguimiento de las siguientes propiedades de celdas: 1) Libro, 2) Hoja, 3) Nombre (si la celda tiene un rango con nombre correspondiente), 4) Dirección de celda, 5) Valor y 6) Fórmula.

Agregar celdas en la ventana Inspección

1. Seleccione las celdas que desee desbloquear.

   Para seleccionar todas las celdas de una hoja de cálculo con fórmulas, vaya a Inicio > Edición > seleccione Buscar & Seleccionar (o puede usar Ctrl+G o Control+G en el Mac)> Ir a Fórmulas > especiales.

   

2. Vaya a Fórmulas > Auditoría de fórmulas > seleccione Ventana Inspección.

3. Seleccione Agregar inspección.

   

4. Confirme que ha seleccionado todas las celdas que desea watch y seleccione Agregar.

   

5. Para cambiar el ancho de una columna de la ventana Inspección, arrastre el borde de la parte derecha del encabezado de columna.

6. Para mostrar una celda a la que hace referencia una entrada de la barra de herramientas de la ventana Inspección, haga doble clic en la entrada.

   Nota: Las celdas que tienen referencias externas a otros libros solo se muestran en la barra de herramientas de la ventana Inspección cuando los otros libros están abiertos.

Quitar celdas de la ventana Inspección

1. Si no se muestra la barra de herramientas de la ventana Inspección, vaya a Fórmulas > Auditoría de fórmulas > seleccione Ventana Inspección.

2. Seleccione las celdas que desea quitar.

   Para seleccionar varias celdas, presione CTRL y, después, seleccione las celdas.

3. Seleccione Eliminar inspección.

   

A veces es difícil comprender cómo calcula una fórmula anidada el resultado final, ya que hay varios cálculos intermedios y pruebas lógicas. Pero, si usa el cuadro de diálogo Evaluar fórmula, puede ver las distintas partes de la fórmula anidada evaluadas en el orden en que se calcula la fórmula. Por ejemplo, la fórmula =SI(PROMEDIO(D2:D5)>50,SUMA(E2:E5),0) es más fácil de comprender cuando se pueden ver los siguientes resultados intermedios:

En el cuadro de diálogo Evaluar fórmula	Descripción
=SI(PROMEDIO(D2:D5)>50;SUMA(E2:E5);0)	La fórmula anidada se muestra al principio. Las funciones PROMEDIO y SUMA se anidan dentro de la función SI. El rango de celdas D2:D5 contiene los valores 55, 35, 45 y 25, por lo que el resultado de la función PROMEDIO(D2:D5) es 40.
=SI(40>50;SUMA(E2:E5);0)	El rango de celdas D2:D5 contiene los valores 55, 35, 45 y 25, por lo que el resultado de la función PROMEDIO(D2:D5) es 40.
=SI(Falso;SUMA(E2:E5);0)	Como 40 no es mayor que 50, la expresión en el primer argumento de la función SI (el argumento prueba_lógica) es Falso. La función SI devuelve el valor del tercer argumento (el argumento valor_si_falso). La función SUMA no se evalúa porque es el segundo argumento para la función SI (valor_si_verdadero), y se devuelve solo cuando la expresión es verdadera.

1. Una vez más, seleccione la celda a la que desea dar formato. Solo se puede evaluar una celda a la vez.

2. Vaya a Fórmulas > Auditoría de 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.

   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 Paso a paso para salir para volver a la celda y fórmula anteriores.

   El botón Paso a paso para entrar 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.

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

5. Para volver a ver la evaluación, selecciona Reiniciar.

6. Para finalizar la evaluación, seleccione Cerrar.