Para mostrar el promedio como una línea horizontal en un gráfico de Excel, agrega una columna auxiliar con =PROMEDIO($B$2:$B$13) en todas las filas, selecciona los datos junto con esa columna y elige Insertar > Gráfico combinado: los valores como columnas y el promedio como línea. Como todas las celdas auxiliares tienen el mismo valor, la línea sale recta y se actualiza sola cuando cambian los datos. Excel no tiene un botón de «línea de promedio» para gráficos de columnas; la columna auxiliar es el método estándar.
Las fórmulas que necesitas
| Columna | Contenido | Fórmula en la fila 2 (arrastrar) |
|---|---|---|
| A | Categoría o periodo | (datos) |
| B | Valor | (datos) |
| C | Promedio para la línea | =PROMEDIO($B$2:$B$13) |
| D (opcional) | Valores sobre el promedio | =SI(B2>=C2,B2,NOD()) |
| E (opcional) | Valores bajo el promedio | =SI(B2<C2,B2,NOD()) |
Los $ son esenciales: fijan el rango para que todas las filas calculen el mismo promedio. NOD() (NA en inglés) produce #N/D, que el gráfico no dibuja; un "" o un 0 sí aparecerían como barras de valor cero.
Paso a paso
- En C1 escribe «Promedio» y en C2
=PROMEDIO($B$2:$B$13). Arrastra hasta C13. - Selecciona A1:C13.
- Ve a Insertar > Gráficos > Insertar gráfico combinado > Columna agrupada - Línea.
- Si Excel no lo asigna así, haz clic derecho en el gráfico > Cambiar tipo de gráfico, ve a Cuadro combinado y elige Columna agrupada para la serie de valores y Línea para la de promedio. Deja ambas en el eje principal.
- Da formato a la línea: clic derecho > Formato de serie de datos, color contrastante y línea discontinua si quieres que se lea como referencia.
- Agrega una etiqueta solo en el último punto de la línea (clic en el punto > Agregar etiqueta de datos) para que muestre el valor del promedio.
Ejemplos resueltos
Ejemplo 1: ventas mensuales con su promedio anual
Ventas de enero a diciembre (miles de pesos): 120, 135, 128, 150, 162, 158, 171, 180, 176, 190, 205 y 198. La columna C devuelve 1,973 / 12 = 164.42 en las doce filas.
El gráfico combinado muestra doce columnas y una línea plana en 164.42. Se ve de un vistazo que de enero a junio todos los meses quedaron por debajo del promedio y de julio a diciembre todos por encima: una tendencia creciente que la cifra sola no cuenta. Para mostrar esa tendencia con una curva, prueba una línea de tendencia de promedio móvil.
Ejemplo 2: barras de otro color sobre y bajo el promedio
Diez vendedores con ventas de 1,250, 980, 1,430, 1,100, 1,675, 2,010, 890, 1,540, 1,320 y 1,205; el promedio es 1,340. En lugar de graficar la columna B, grafica las columnas D y E de la tabla de fórmulas, más la C como línea.
- Selecciona A1:A11 y, con Ctrl, C1:E11.
- Inserta un gráfico combinado con D y E como Columna apilada y C como Línea.
- Pinta D de un color y E de otro.
Como cada fila tiene número en D o en E (nunca en ambas), cada vendedor aparece una sola vez, con el color que le corresponde: 4 por encima del promedio y 6 por debajo. Si usaras "" en lugar de NOD(), el gráfico dibujaría barras de cero que estorban a las etiquetas.
Ejemplo 3: un gráfico que crece con los datos
Convierte los datos en tabla con Insertar > Tabla y escribe en la columna auxiliar =PROMEDIO([Ventas]). Esta referencia estructurada siempre abarca toda la columna de la tabla.
Al agregar un nuevo mes en la fila siguiente, la tabla se extiende, la fórmula de la columna auxiliar se copia sola, el promedio se recalcula con el nuevo dato y el gráfico incorpora el punto. Si tus datos están filtrados y quieres que la línea muestre solo el promedio visible, usa =SUBTOTALES(101,[Ventas]), explicado en promedio de celdas visibles.
Errores comunes
- Olvidar los
$. Con=PROMEDIO(B2:B13)arrastrada, cada fila promedia un rango distinto y la «línea» sube y baja. - Poner el promedio en el eje secundario. Si Excel lo asigna a un eje propio, la línea queda a otra escala y parece estar en el lugar equivocado. Usa el eje principal para ambas series.
- Graficar el promedio como columna. Doce barras iguales junto a los datos confunden. Cambia esa serie a línea.
- Usar una línea de tendencia lineal como «promedio». La tendencia lineal es una recta de regresión, inclinada; no es el promedio.
- Línea que no llega a los bordes. En columnas, la línea va del centro de la primera categoría al centro de la última. Es normal; si necesitas que cruce todo el gráfico, usa un gráfico de líneas para ambas series.
Si prefieres marcar los valores en la tabla en lugar de graficar, mira formato condicional sobre el promedio. En Google Sheets el procedimiento es parecido pero el editor cambia: está en gráficos con promedio en Google Sheets. La calculadora de promedio de ventas te da la cifra para comprobar la línea.