Detectar qué celdas dependen de otras hojas o ficheros con formato condicional

Detectar qué celdas dependen de otras hojas o ficheros con formato condicional

Te pasan un fichero que no has hecho tú y la primera pregunta siempre es la misma: ¿de dónde salen estos números? Algunos están escritos a mano, otros vienen de otra hoja y otros de otro libro que a lo mejor ni tienes.

Este truco resalta de golpe, con un color, todas las celdas que dependen de algo externo a la hoja en la que estás.

La idea aprovecha un detalle de la sintaxis de Excel: toda referencia a otra hoja o a otro fichero lleva obligatoriamente un signo de exclamación (Hoja2!B4), porque es lo que separa el origen de la celda concreta. Así que basta con buscar ese carácter dentro de la fórmula:

- FORMULATEXTO devuelve la fórmula de una celda como texto, en vez de su resultado
- ENCONTRAR busca el signo de exclamación dentro de ese texto
- ESNUMERO convierte el resultado en VERDADERO o FALSO, porque ENCONTRAR devuelve un error cuando no halla nada
- Ese VERDADERO/FALSO es justo lo que necesita una regla de formato condicional aplicada a toda la hoja

El resultado es un mapa instantáneo del fichero: ves qué zonas son entrada manual y cuáles arrastran datos de fuera, que son las que se rompen al mover o renombrar una hoja.

Cuando abres un fichero que no has construido tú, la pregunta que retrasa todo lo demás es de dónde salen los números. Unos están escritos a mano, otros vienen de otra hoja del mismo libro y otros de un fichero externo que puede que ni tengas delante. Hasta que no sabes cuál es cuál, no te puedes fiar de nada de lo que hay.

Esto se resuelve con una fórmula corta y una regla de formato condicional, y deja el fichero entero mapeado de un vistazo.

La observación en la que se apoya todo

Excel obliga a que toda referencia a otra hoja o a otro libro lleve un signo de exclamación. Hoja2!B4, '2023'!C10, [Presupuesto.xlsx]Datos!A1. Ese carácter es el que separa el origen de la celda concreta, así que no hay forma de referenciar algo externo sin él.

Dicho de otro modo: buscar el signo de exclamación dentro de una fórmula equivale a preguntar "¿esto mira fuera de esta hoja?". Y eso sí se puede automatizar.

El problema de mirar dentro de una fórmula

Para buscar dentro de una fórmula hay que poder leerla como texto, no como resultado. Si preguntas por el contenido de la celda obtienes el número que devuelve, que no sirve de nada.

Ahí entra FORMULATEXTO, que devuelve literalmente lo que está escrito en la celda:

=FORMULATEXTO(B4)

Si B4 contiene ='2023'!C10, esto devuelve el texto ='2023'!C10. Ya se puede buscar dentro.

Construyendo la condición

Sobre ese texto se aplica ENCONTRAR, que devuelve la posición del carácter buscado:

=ENCONTRAR("!";FORMULATEXTO(B4))

Si la fórmula referencia otra hoja, devuelve un número. Si no lo encuentra, devuelve error, y ese comportamiento es justo lo que hace falta: convierte "no hay referencia externa" en algo distinguible.

Solo queda traducir esas dos salidas a VERDADERO y FALSO, que es lo que entiende el formato condicional:

=ESNUMERO(ENCONTRAR("!";FORMULATEXTO(B4)))

Devuelve VERDADERO en las celdas que dependen de fuera y FALSO —o error, que el formato condicional trata como no cumplido— en el resto.

Aplicarlo a la hoja entera

Se selecciona todo el rango que quieras auditar, se crea una regla de formato condicional del tipo "utilice una fórmula" y se pega esa expresión referida a la primera celda del rango seleccionado, sin fijar con dólares. Excel la propaga al resto relativizándola, igual que al arrastrar una fórmula.

Con un relleno llamativo, el fichero queda mapeado: las celdas de color son las que arrastran datos de fuera y las que no, entrada manual o cálculo local.

Por qué merece la pena tenerlo a mano

  • Antes de mover o renombrar una hoja, sabes exactamente qué se va a romper.
  • Al heredar un fichero, distingues en segundos la zona de entrada de datos de la zona calculada.
  • Al detectar vínculos externos, ves qué celdas concretas los usan, no solo que el libro los tiene.

Este último punto complementa a Datos > Editar vínculos, que dice que hay vínculos pero no dónde están. Ojo a su límite: esta técnica encuentra lo que vive en las fórmulas de las celdas. Los vínculos escondidos en nombres definidos, en reglas de formato condicional o en validaciones de datos no llevan fórmula en ninguna celda, así que no se pintan.

Funciones clave

  • FORMULATEXTO — devuelve la fórmula de una celda como texto; sin ella no hay nada que buscar
  • ENCONTRAR — localiza el signo de exclamación y da error si no está, que es la señal aprovechable
  • ESNUMERO — convierte "lo he encontrado" en VERDADERO y el error en FALSO
  • Formato condicional con fórmula — aplica la condición celda a celda sobre todo el rango

Conclusión

Es de esos trucos que caben en una línea y ahorran una tarde. La clave no está en las funciones, que son básicas, sino en darse cuenta de que la sintaxis de Excel deja una huella obligatoria —el signo de exclamación— y que FORMULATEXTO permite leerla. A partir de ahí, auditar un fichero ajeno deja de ser ir celda por celda con F2.

Más contenido de Excel en InflueXcel