El error "demasiados formatos de celdas" en Excel

Todo quien haya trabajado suficiente tiempo con Excel se ha topado en alguna oportunidad con el error "demasiados formatos de celda diferentes" (too many different cell formats)

o, en Excel 2003, con

Excel ha encontrado un error y se tuvieron que quitar parte del formato para evitar dañar el libro

o, en Excel 2007-2013, con

Excel encontró contenido no legible en el archivo 

 Otros síntomas ligados a este error pueden ser:

• al abrir un archivo se elimina todo el formato;
• incremento en el tamaño del archivo después de copiar-pegar entre libros
• imposibilidad de pegar lo que se ha copiado al portapapeles (aparece el mensaje "Microsoft Excel no puede pegar datos")

De acuerdo a la base de datos de conocimientos de Microsoft:

Este problema se produce cuando el libro contiene más de aproximadamente 4.000 combinaciones diferentes de formatos de celda en Excel 2003 o 64.000 en Excel 2007 y posteriores. Una combinación se define como un conjunto de elementos que se aplican a una celda de formato único. Una combinación incluye todo el formato de fuente (por ejemplo: tipo de letra, tamaño de fuente, cursiva, negrita y subrayado), bordes (por ejemplo: ubicación, grosor y color), los patrones de celdas, formato, alineación y protección de celda de número.

Hay todo tipo de medidas que pueden y deben tomarse para evitar este problema. Pero si nos topamos con él, la mejor herramienta es XLStylesTool. Esta herramienta, que se puede descargar en forma gratuita,  fue desarrollada por Sergei Gundorov, quien se merece todo nuestro agradecimiento.

Uso de Tablas en Excel - referencias estructuradas

El tema del uso de Tablas (Listas en Excel 2003) no es nuevo en este blog (un clic en la etiqueta Tablas/Listas en la nube de etiquetas les mostrará todas las notas). En mi opinión es una de las mejores innovaciones en Excel en los últimos años. Sin embargo no creo equivocarme si digo que relativamente pocos usuarios sacan provecho de esta herramienta.
Un posible motivo para esta reticencia sea la nueva sintaxis empleada para referenciar las celdas y rangos dentro de la tabla. Al principio esta sintaxis parece un tanto críptica para el usuario habituado a las referencias clásicas "columna/fila" (por ejemplo, A1).

Consideremos esta tabla de ventas por sucursal y zona

En la celda G4 usamos esta fórmula para extraer de la tabla las ventas de cierta zona

=SUMAR.SI($C$3:$C$12,G3,$D$3:$D$12)

Ahora convirtamos esta tabla en  una Tabla (cuando uso tabla con minúscula me refiero a un rango normal de datos) y veamos cómo aparecen las referencias en la fórmula

La fórmula aparece ahora así

=SUMAR.SI(TablaVentas[Zona],G3,TablaVentas[Ventas])

La sintaxis es evidente:

"TablaVentas" se refiere al nombre de la tabla

[Zona] es la referencia al campo (columna) que define la zona de cada sucursal; de la misma manera [Ventas] se refiere a la columna de las ventas.

Para referirnos a otros elementos de la tabla, como la fila de los encabezamientos, usamos distintas formas que resumo en esta tabla

Objeto	Sintaxis
Referencia a la tabla	=TablaVentas
La celda de la fila en la columna	=[@Ventas]
Fila de encabezados	TablaVentas[#Encabezados]
Toda la tabla	=SUMA(TablaVentas[#Todo])
Fila de totales	=TablaVentas[[#Totales],[Ventas]]

Una ventaja evidente de esta sintaxis es la claridad. No hace falta ver el rango referenciado para saber qué calcula la fórmula.
Esto nos permite crear fórmulas con mucha facilidad, como podemos ver

Al comenzar a escribir la fórmula Excel nos muestra las tablas como un rango definido; pero la mejor parte viene ahora

 Al poner el "["Excel nos muestra todos los rangos disponibles para nuestra fórmula. Así que si queremos calcular el total de ventas elegimos Ventas, ponemos el "]" para cerrar la definición del campo, cerramos el o los paréntesis necesarios y apretamos Enter. 
Para referirse a una celda dentro de la Tabla, Excel usa la notación [@nombre del campo]. Por ejemplo, si tenemos una columna de descuentos en nuestra tabla y queremos calcular las ventas neto

Más sobre la sintaxis y el uso de referencias  estructuradas pueden leer en esta nota del sitio Office de Microsoft.

Como sumar los ultimos n valores de un rango con celdas vacías

Aún no se ha secado la tinta de mi último post sobre la función INDICE y, por una de esas fantásticas casualidades, recibo esta consulta:

¿cómo puedo sumar los últimos tres valores de un rango que contiene celdas vacías?

La casualidad consiste en que la solución nos permitirá demostrar que INDICE da como resultado referencias a rangos y como ésto nos permite crear rangos dinámicos.

Supongamos estos datos

La idea es sumar los últimos tres valores de cada fila; en H2 debe ser 280, en H3 debe ser 130, etc. En el pasado he publicado un post sobre cálculos con los últimos n valores de un rango, pero esa técnica suponía que en el rango no había celdas vacías.

La solución es usar esta fórmula matricial (fórmulas que se ingresan apretando simultáneamente Ctrl-Mayúsuculas-Enter)

=SUMA(G2:INDICE(A2:G2,K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)))

Analizamos la fórmula de adentro hacia afuera:

La expresión COLUMNA(A2:G2)*(A2:G2<>"") produce un vector de valores númericos que pueden ser el número de columna o 0 si la celda del rango está vacía. Podemos mostrar el resultado de esta expresión en forma no matricial, es decir, aplicada a cada celda del rango

El vector de valores creado es el argumento de la expresión

K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)

que da como resultado el tercer valor en orden decreciente, es decir 3 (los valores del vector son 6,5,3,2,1 y 0).

Nuestra expresión se ha reducido ahora a INDICE(A2:G2,3) y aquí es donde podemos ver que INDICE da como resultado un rango, no un valor. El resultado de esta expresión es $C$2 y por lo tanto nuestra fórmula es ahora =SUMA(G2:$C$2), es decir =SUMA($C$2:$G$2), lo que nos da el resultado deseado.


De la misma manera podemos aplicar esta fórmula a las columnas en lugar de las filas, utilizando la función FILA() en lugar de la COLUMNA() y corrigiendo los rangos consecuentemente

En este video explico el funcionamiento de la fórmula usando la herramienta Auditoría de fórmula