Interfaz y conceptos básicos de Excel 2016

1. La ventana de Excel

1.1 Qué contiene cada ficha

• Inicio — Portapapeles, Fuente, Alineación, Número, Estilos (formato condicional, dar formato como tabla y estilos de celda), Celdas y Modificar.

• Insertar — Tablas, Ilustraciones, Gráficos, Minigráficos, Filtros, Vínculos, Texto y Símbolos.

• Diseño de página — Temas, Configurar página, Ajustar área de impresión, Opciones de la hoja y Organizar.

• Fórmulas — Biblioteca de funciones, Nombres definidos, Auditoría de fórmulas y Cálculo.

• Datos — Obtener datos externos, Conexiones, Ordenar y filtrar, Herramientas de datos, Previsión y Esquema.

• Revisar — Revisión, Idioma, Comentarios y Proteger (Proteger hoja y Proteger libro).

• Vista — Vistas de libro, Mostrar, Zoom, Ventana y Macros.

Proteger hoja está en la ficha Revisar, no en Inicio ni en Datos. Y los estilos de celda están en Inicio.

2. Conceptos básicos

Tipos de datos admitidos: números, texto, fechas y horas, valores lógicos, valores de error y fórmulas.

2.1 Texto que no cabe en la celda

Si se escribe un texto más largo que el ancho de la celda y la celda de al lado está vacía, el texto se desborda visualmente sobre la celda adyacente: se ve entero, pero sigue perteneciendo únicamente a la celda original. Si la celda contigua tiene contenido, el texto se recorta en pantalla (y aparece completo en la barra de fórmulas). Con Ajustar texto se reparte en varias líneas dentro de la propia celda.

2.2 Tipos de referencia

Las referencias relativas son las que Excel utiliza de forma predeterminada para identificar una celda: se escriben sin $ y se adaptan solas cuando la fórmula se copia a otro sitio.

3. Operaciones elementales

Toda fórmula empieza por =. Para restar el contenido de D6 al de D5 se escribe =D5-D6: primero la celda minuendo, después el operador y luego el sustraendo. No hay ninguna función «RESTA» en Excel.

4. Categorías de funciones

Excel clasifica las funciones por categorías, y en examen se pregunta a cuál pertenece cada una:

4.1 Ejemplos que conviene tener resueltos

• =HORA("16:45:30") devuelve 16: la función HORA extrae solo la hora de un valor de tiempo, como número entre 0 y 23.

• =EXTRAE("Vence";4;7) devuelve ce. Empieza en el carácter 4 y pide 7, pero la cadena solo tiene 5 caracteres: Excel devuelve los que hay a partir de esa posición, sin error.

• =FECHA(2024;13;5) no genera ningún error. FECHA admite valores fuera de rango y los reajusta: el mes 13 de 2024 es enero de 2025, así que devuelve el 05/01/2025.

• =FIN.MES(11-3-2022;2) devuelve #¡NUM!. Al escribir la fecha sin comillas, Excel no la interpreta como fecha sino como una resta: 11 − 3 − 2022 = −2014, un número de serie negativo que no corresponde a ninguna fecha válida. La forma correcta sería =FIN.MES("11/3/2022";2) o =FIN.MES(FECHA(2022;3;11);2).

5. Imprimir

En Archivo ▸ Imprimir, la sección Configuración ofrece:

• Qué se imprime: Imprimir hojas activas · Imprimir todo el libro · Imprimir selección · Imprimir área de impresión.

• Orientación, tamaño del papel, márgenes y ordenación de copias.

• Escalado: Sin ajuste de escala · Ajustar hoja en una página · Ajustar todas las columnas en una página · Ajustar todas las filas en una página.

Dos matices que se preguntan:

• Para que la hoja salga con una sola página de ancho (sin partir las columnas a la derecha), la opción es «Ajustar todas las columnas en una página».

• Para imprimir solo unas tablas concretas de la hoja activa, se seleccionan y se elige «Imprimir selección».

• No existe ninguna opción llamada «imprimir el área impar»: el desplegable no distingue páginas pares e impares.

6. Diferencias con Excel 365

Excel 2016 no dispone de BUSCARX, FILTRAR, ORDENAR, UNICOS ni SECUENCIA, ni de matrices dinámicas, ni de coautoría en tiempo real, ni de tipos de datos enriquecidos. Sí tiene BUSCARV, BUSCARH, SI, CONTAR.SI, SUMAR.SI, tablas dinámicas, Power Pivot y Power Query (Obtener y transformar).

7. Personalizar la cinta de opciones

En Archivo ▸ Opciones ▸ Personalizar cinta de opciones se añaden fichas y grupos propios. El botón Restablecer elimina todas las personalizaciones y devuelve la cinta a su estado predeterminado.

Formato de celdas en Excel 2016

El cuadro completo se abre con Inicio ▸ Formato ▸ Formato de celdas o con Ctrl+1, y tiene seis pestañas: Número, Alineación, Fuente, Bordes, Relleno y Proteger.

1. Pestaña Número: categorías

1.1 Qué formato aplica Excel solo

Excel interpreta lo que se escribe y aplica el formato correspondiente, incluso en una celda con formato General:

• Al escribir 20/2 en español, Excel lo entiende como una fecha y la muestra como 20-feb.

• Al escribir 1/21 o ene-21, lo muestra como ene-21.

• Al introducir 01/01/2021, Excel aplica el formato de fecha corta d-mm-aaaa.

Para que un dato de ese tipo se conserve como texto hay que aplicar antes la categoría Texto o escribirlo precedido de un apóstrofo ('20/2).

1.2 Obtener partes de una fecha

Con una fecha en A1: =DIA(A1) devuelve el día del mes, =MES(A1) el mes y =AÑO(A1) el año.

2. Fuente, alineación, bordes y relleno

• Fuente: tipo, tamaño, negrita, cursiva, subrayado, color, tachado, superíndice y subíndice.

• Alineación: horizontal y vertical, Ajustar texto, Combinar y centrar, orientación y sangría.

• Bordes y Relleno: contorno, bordes interiores, color y trama.

• Estilos de celda: están en la ficha Inicio ▸ grupo Estilos, junto a Formato condicional y Dar formato como tabla.

La ficha Inicio NO contiene el grupo «Tablas»: insertar una tabla o una tabla dinámica se hace desde la ficha Insertar. En Inicio están Portapapeles, Fuente, Alineación, Número, Estilos, Celdas y Modificar.

3. Formato condicional

Inicio ▸ Estilos ▸ Formato condicional:

• Reglas de resaltado de celdas: es mayor que, es menor que, entre, es igual a, texto que contiene, una fecha, valores duplicados.

• Reglas superiores e inferiores: 10 superiores, 10 % superiores, por encima o por debajo del promedio.

• Barras de datos, Escalas de color y Conjuntos de iconos.

• Nueva regla (con fórmula personalizada) y Administrar reglas.

En el cuadro Nueva regla de formato, el primer tipo de regla es «Aplicar formato a todas las celdas según sus valores»: es el que agrupa Escala de 2 colores, Escala de 3 colores, Barra de datos y Conjunto de iconos, porque todos ellos colorean el rango completo en función del valor relativo de cada celda.

4. Listas desplegables (validación de datos)

Para que una celda solo admita valores de una lista cerrada:

Datos ▸ Herramientas de datos ▸ Validación de datos ▸ pestaña Configuración ▸ Permitir: «Lista», y en Origen se indica el rango o los valores separados por punto y coma.

5. Rellenar series

Para rellenar una columna con una serie de valores hay dos caminos igual de válidos:

1. Arrastrar el controlador de relleno (el cuadradito de la esquina inferior derecha de la selección) tras escribir los dos primeros valores, que es lo que fija el incremento.

2. Inicio ▸ Modificar ▸ Rellenar ▸ Series, donde se indica el tipo (lineal, geométrica, cronológica), el incremento y el límite.

6. Encabezado y pie de página

En Insertar ▸ Texto ▸ Encabezado y pie, o desde Diseño de página ▸ Configurar página ▸ Encabezado y pie, se insertan códigos de autotexto:

Por eso la numeración del tipo «Página 3 de 12» se escribe Página &[Página] de &[Páginas].

Fórmulas y funciones en Excel 2016

Toda fórmula empieza por =. Puede combinar valores constantes, referencias a celdas, operadores, funciones y nombres definidos. Excel la recalcula automáticamente cuando cambia cualquiera de las celdas de las que depende.

1. Qué se puede escribir en una celda

Una celda de Excel admite texto, números, fechas y horas, fórmulas, valores lógicos (VERDADERO/FALSO) y valores de error. Además, sobre la hoja pueden insertarse objetos (imágenes, formas, gráficos, SmartArt), que no ocupan una celda pero se anclan a ella.

Los números, las fechas y las horas se alinean por defecto a la derecha; el texto, a la izquierda. Ese comportamiento es la forma rápida de detectar un número que en realidad ha entrado como texto.

2. Operadores

Orden de precedencia (de mayor a menor): referencia (: ; espacio) → negación (-1) → % → ^ → * y / → + y - → & → operadores de comparación. Los paréntesis alteran ese orden.

El operador espacio es el que produce el error #¡NULO! cuando los dos rangos indicados no se cruzan.

3. Referencias a celdas

El símbolo que fija una referencia es el $: convierte en absoluta la parte a la que precede. La tecla F4, con el cursor sobre la referencia, va alternando los cuatro tipos en este orden: A1 → $A$1 → A$1 → $A1 → A1.

Ejemplo de ajuste al copiar. Si en la celda C5 hay la fórmula =$C4 y se copia y pega 15 filas más abajo (a C20), el resultado es =$C19: la columna estaba fijada con $ y no se mueve, mientras que la fila, relativa, se desplaza las mismas 15 filas.

Otras hojas y otros libros: =Hoja2!A1, ='Hoja de datos'!A1 (comillas simples si el nombre lleva espacios) y =[Libro1.xlsx]Hoja1!A1. Una referencia 3D abarca varias hojas: =SUMA(Hoja1:Hoja3!A1).

4. Categorías de funciones

El cuadro Insertar función (fx, o la ficha Fórmulas) agrupa las funciones en estas categorías:

Dos categorías se confunden a menudo en examen: Ingeniería (Bessel, conversiones de base, números complejos) e Información (preguntar qué hay en una celda). No son intercambiables.

5. Funciones más utilizadas

5.1 Recuento y suma

Ejemplo. =CONTAR.SI(A1:A5;">5") sobre el rango {2; 6; 8; 3; 9} devuelve 3, porque hay tres valores mayores que 5 (6, 8 y 9). El criterio va entre comillas cuando incluye un operador de comparación.

Ejemplo. =CONTARA(A1:A6) cuenta cuántas celdas del rango contienen algo, sin importar el tipo de dato; solo deja fuera las completamente vacías.

5.2 Lógicas

• SI(prueba_lógica; valor_si_verdadero; valor_si_falso)

• Y(...), O(...), NO(...)

• SI.ERROR(valor; valor_si_error) — devuelve el segundo argumento cuando el primero da error

Ejemplo. =SI(A12>1000;"Superior";"Inferior") evalúa si el valor de A12 es mayor que 1000 y devuelve un texto u otro según el resultado.

5.3 Búsqueda y referencia

Ejemplo. =COINCIDIR("azul";{"verde";"rojo";"azul";"blanco"};0) devuelve 3: «azul» es el tercer elemento de la matriz. El tipo 0 exige coincidencia exacta.

INDICE + COINCIDIR es la pareja habitual para sustituir a BUSCARV cuando la columna buscada no es la primera.

5.4 Texto

Ejemplo. =EXTRAE("PreparaTest";1;7) devuelve Prepara: siete caracteres a partir del primero.

LIMPIAR() es la función típica tras importar datos de una fuente externa: quita los caracteres de control que no se ven pero que impiden que los datos se traten bien. Para los espacios de más, la función es ESPACIOS().

5.5 Fecha y hora

HOY() (fecha actual) · AHORA() (fecha y hora) · AÑO, MES, DIA · FECHA(año;mes;día) · DIAS.LAB · DIASEM(fecha; [tipo]), que devuelve el día de la semana en formato numérico (con tipo=1, domingo = 1; con tipo=2, lunes = 1).

5.6 Matemáticas

REDONDEAR, REDONDEAR.MAS, REDONDEAR.MENOS, TRUNCAR, ENTERO, ABS, RAIZ, POTENCIA, ALEATORIO, SUMAPRODUCTO.

• RESIDUO(número; divisor) devuelve el resto de una división. =RESIDUO(10;3) → 1.

• M.C.D(números) devuelve el máximo común divisor. =M.C.D(10;5) → 5.

• M.C.M(números) devuelve el mínimo común múltiplo.

5.7 Estadísticas

MEDIANA, DESVEST.M, VAR, K.ESIMO.MAYOR, JERARQUIA y MODA.UNO(rango), que devuelve el valor que más veces se repite en el rango (MODA es su versión antigua, hoy en la categoría Compatibilidad).

5.8 Financieras

PAGO, PAGOPRIN, VA, VF, TASA, NPER y PAGOINT:

PAGOINT(tasa; período; nper; va; [vf]; [tipo])

El argumento tipo es el que establece cuándo vencen los pagos: 0 u omitido = al final del período; 1 = al principio.

6. Valores de error

##### no es un error: solo indica que la columna es demasiado estrecha para mostrar el número. Se corrige ensanchándola.

7. Herramientas relacionadas

7.1 Pegado especial

Además de pegar todo, permite pegar solo valores, solo formatos, solo fórmulas, ancho de columnas, aplicar una operación (sumar, restar, multiplicar, dividir) y Transponer.

Transponer intercambia la orientación de los datos: lo que estaba en filas pasa a columnas y viceversa.

7.2 Subtotales

En Datos ▸ Esquema ▸ Subtotal se insertan totales por grupos. Para que funcione, los datos deben estar ORDENADOS previamente por el campo por el que se va a agrupar; si no lo están, Excel crea un subtotal cada vez que el valor cambia y el resultado carece de sentido.

7.3 Análisis rápido

Al seleccionar un rango aparece el botón Análisis rápido en su esquina inferior derecha, con cinco categorías: Formato, Gráficos, Totales, Tablas y Minigráficos.

La categoría Totales ofrece Suma, Promedio, Recuento, % del total y Total acumulado, en variantes por columna y por fila. No incluye Máximo ni Mínimo: para eso hay que escribir las funciones MAX y MIN.

8. Límites que conviene recordar

Fuente: documentación oficial de Microsoft para Excel — Especificaciones y límites de Excel y las páginas de referencia de cada función (support.microsoft.com, es-es).

Gráficos y gestión de datos en Excel 2016

1. Gráficos

Se insertan desde Insertar ▸ Gráficos, con el rango de datos seleccionado. Al crearse aparecen dos fichas contextuales, Herramientas de gráficos ▸ Diseño y Formato.

1.1 Tipos de gráfico de Excel 2016

No existe ningún tipo de gráfico llamado «toroidal» en Excel. El gráfico con forma de rosquilla se llama anillo y es un subtipo de los circulares.

El gráfico de dispersión (XY) es el que combina pares de valores en puntos de datos únicos y los representa a intervalos irregulares: se usa para ver la relación entre dos variables numéricas, no para comparar categorías.

1.2 Elementos del gráfico

Título del gráfico, títulos de eje, leyenda, etiquetas de datos, líneas de cuadrícula, líneas de tendencia y barras de error. Se activan con el botón + del gráfico o desde Diseño ▸ Agregar elemento de gráfico.

1.3 Minigráficos (sparklines)

Se insertan desde Insertar ▸ Minigráficos (Línea, Columna o Pérdidas y ganancias).

Diferencia esencial con un gráfico normal: el minigráfico se dibuja DENTRO de una celda, como si fuera su fondo, y no incluye elementos independientes (ni título, ni leyenda, ni ejes ni etiquetas). Un gráfico tradicional es un objeto flotante sobre la hoja con todos esos elementos.

2. Ordenar, filtrar y limpiar datos

• Ordenar (Datos ▸ Ordenar y filtrar): A-Z, Z-A y Orden personalizado con varios niveles.

• Autofiltro: coloca flechas desplegables en los encabezados.

• Quitar duplicados: en Datos ▸ Herramientas de datos. Pero si el rango se ha convertido en tabla, el comando está además en Herramientas de tabla ▸ Diseño ▸ grupo Herramientas ▸ Quitar duplicados.

2.1 Filtro avanzado

Datos ▸ Ordenar y filtrar ▸ Avanzadas permite filtrar con un rango de criterios y elegir entre filtrar la lista en el sitio o copiar a otro lugar. Al copiar a otro lugar se llevan los valores y también los formatos de las celdas de origen si así se ha configurado el copiado; y con la casilla Solo registros únicos se descartan las filas repetidas.

2.2 Validación de datos

Datos ▸ Herramientas de datos ▸ Validación de datos restringe lo que se puede escribir en una celda.

• Para que una celda solo admita valores de un conjunto cerrado hay que crear una lista desplegable: en Permitir se elige Lista y se indica el origen (un rango o los valores separados por ;).

• La pestaña Mensaje de error tiene un desplegable Estilo con exactamente tres opciones: Alto (impide la entrada), Advertencia (avisa pero permite continuar) e Información (solo informa).

2.3 Subtotales

Datos ▸ Esquema ▸ Subtotal es el comando para obtener totales por grupos dentro de una lista larga. Exige ordenar antes por el campo de agrupación.

2.4 Consolidar

Datos ▸ Herramientas de datos ▸ Consolidar sirve para agrupar y resumir los datos de varios rangos —de la misma hoja, de otras hojas o de otros libros— en una única tabla de resultados, aplicando una función (Suma, Promedio, Máx…).

3. Tablas y tablas dinámicas

• Tabla (Insertar ▸ Tabla): encabezados con filtro automático, fila de totales, estilos y referencias estructuradas.

• Tabla dinámica (Insertar ▸ Tabla dinámica): campos en las áreas Filas, Columnas, Valores y Filtros; configuración de campo (Suma, Cuenta, Promedio, Máx, Mín); agrupación por meses, trimestres o años; y segmentación de datos y escala de tiempo para filtrar visualmente.

• Power Pivot es el complemento de modelo de datos: permite combinar grandes volúmenes de datos de orígenes distintos, relacionarlos entre sí y crear medidas con DAX, superando los límites de una tabla dinámica normal.

4. Análisis de hipótesis

Datos ▸ Previsión ▸ Análisis de hipótesis reúne tres herramientas:

4.1 Buscar objetivo

Responde a «sé el resultado que quiero, ¿qué valor necesito?». Es la herramienta indicada cuando, por ejemplo, se conoce la cuota mensual que se puede pagar de un crédito y se quiere saber el importe máximo a solicitar.

Su cuadro de diálogo tiene tres campos:

4.2 Escenarios

El Administrador de escenarios guarda conjuntos de valores de entrada para comparar resultados. Cada escenario admite un máximo de 32 celdas cambiantes.

4.3 Tabla de datos

Calcula los resultados de una o dos variables de entrada sobre una misma fórmula.

5. Auditoría y supervisión

• Fórmulas ▸ Auditoría de fórmulas: Rastrear precedentes, Rastrear dependientes, Quitar flechas, Mostrar fórmulas y Evaluar fórmula.

• Ventana Inspección (mismo grupo): permite ir supervisando el valor de determinadas celdas aunque no estén visibles en pantalla, incluso si pertenecen a otra hoja del libro. Es la herramienta para vigilar celdas clave mientras se trabaja lejos de ellas.

6. Nombres definidos

Fórmulas ▸ Nombres definidos ▸ Administrador de nombres es la herramienta desde la que se gestionan todos los nombres del libro: crear, editar, eliminar y filtrar los nombres definidos y los de tabla, y ver a qué rango se refiere cada uno.

7. Ventanas y vista

En Vista ▸ Ventana:

• Nueva ventana — crea un duplicado exacto de la ventana del libro activo, de modo que puede verse la misma información en dos ventanas a la vez (por ejemplo, dos hojas del mismo libro).

• Cambiar ventanas — muestra la lista de todos los libros abiertos y permite saltar a cualquiera de ellos.

• Organizar todo, Inmovilizar paneles y Dividir completan el grupo.

8. Análisis rápido

Al seleccionar un rango aparece el botón Análisis rápido, con cinco categorías: Formato, Gráficos, Totales, Tablas y Minigráficos.

La categoría Totales ofrece Suma, Promedio, Recuento, % del total y Total acumulado. No incluye Máximo ni Mínimo.

9. Protección

La contraseña es opcional, y ahí está el matiz que se pregunta: si se protege una hoja SIN contraseña, cualquier otro usuario puede desprotegerla sin contraseña desde Revisar ▸ Desproteger hoja. La protección sin contraseña evita cambios accidentales, no intencionados.

10. Personalizar la cinta de opciones

En Archivo ▸ Opciones ▸ Personalizar cinta de opciones se crean fichas y grupos propios. El botón Restablecer elimina todas las personalizaciones y devuelve la cinta a su configuración predeterminada; puede aplicarse solo a la ficha seleccionada o a todas.

11. Condicionales anidadas

Una fórmula del tipo =SI(A12>50;"Aprobado";"Suspenso") evalúa si el valor de la celda A12 es superior a 50 y devuelve un resultado u otro. Anidando varias SI se encadenan tramos; Excel 2016 admite hasta 64 niveles de anidamiento.

Atajos de teclado en Excel 2016

⚠️ Se examina el Excel INSTALADO EN ESPAÑOL, que localiza los atajos de letra por la inicial de la acción en castellano. Entre paréntesis se indica el equivalente de la versión inglesa, que es el distractor habitual y el que aparece en la documentación traducida de Microsoft.

1. Archivo

2. Desplazamiento

3. Selección

4. Edición

5. Formato

6. Fórmulas, filas y columnas

7. La ficha Vista: grupo Mostrar

Contiene las casillas Regla, Líneas de cuadrícula, Barra de fórmulas y Encabezados.

Si en la hoja se ven las celdas pero no las letras de las columnas ni los números de las filas, lo que ha ocurrido es que se ha desactivado la casilla «Encabezados» del grupo Mostrar (ficha Vista). No es un problema de zoom ni de protección.

8. La barra de estado

De forma predeterminada muestra, con un rango seleccionado, el Promedio, el Recuento y la Suma, además del modo de celda, los botones de vista y el zoom. Se personaliza con el botón derecho sobre ella.

9. Un caso de texto frecuente

Para pasar nombres escritos en mayúsculas a la forma «Nombre Apellido» (inicial mayúscula y resto minúsculas) la función es =NOMPROPIO(A2). MAYUSC pondría todo en mayúsculas y MINUSC todo en minúsculas.