Un examen tipo test en Excel que se corrige solo (listas desplegables y nota automática)
Escribe las preguntas en una columna, añade en la de al lado una lista desplegable con Datos › Validación de datos › Permitir: Lista y Origen a;b;c;d, y pon la clave en otra columna. Puntúa cada fila con =SI(C2=E2;1;0), suma los aciertos y calcula la nota con REDONDEAR. Al final oculta la clave y protege la hoja en Revisar › Proteger hoja.
Corregir 30 tests a mano es contar aciertos, restar, dividir y volver a contar porque no te cuadra. Si el test ya está en Excel, la hoja puede hacer ese trabajo por ti: el alumno o el compañero elige sus respuestas y la nota aparece sola.
La idea es sencilla, como la plantilla transparente que algunos profesores ponen encima de la hoja de respuestas: una columna con las respuestas del alumno, otra escondida con las correctas, y una fórmula que compara fila a fila. Solo necesitas tres herramientas que quizá ya conoces: una lista desplegable, la función SI y la protección de hoja.
En esta guía montas un test de 10 preguntas con nota sobre 10, colores de acierto y fallo que se encienden al final, y la clave oculta. Sirve igual para una clase, una formación interna o un repaso de oposiciones.
Qué vas a aprender
- Montar la hoja del test con respuestas a, b, c y d en una lista desplegable
- Puntuar cada pregunta con SI y sumar los aciertos con SUMA o SUMAPRODUCTO
- Calcular una nota sobre 10 redondeada con REDONDEAR
- Colorear aciertos y fallos solo cuando tú quieras
- Ocultar la clave, proteger la hoja y repartir el test
Paso a paso
Prepara la hoja: pregunta, respuesta, punto y clave
Crea cinco columnas: Nº (A), Pregunta (B), Respuesta (C), Punto (D) y Clave (E). Escribe las 10 preguntas en B2:B11. Si quieres que las opciones se vean en la propia celda, escríbelas debajo de la pregunta con Alt+Intro (salto de línea dentro de la celda): a) Sídney b) Canberra c) Melbourne d) Perth.
Deja la columna C en blanco con un relleno suave: es la única donde escribirá quien haga el test.
Si le pides las preguntas a ChatGPT o a Copilot, pide también la clave en una lista aparte y compruébala tú: la IA a veces marca como correcta una opción que no lo es.
Crea la lista desplegable a, b, c, d en las respuestas
Selecciona C2:C11 y ve a Datos › Herramientas de datos › Validación de datos. En la pestaña Configuración, elige Permitir: Lista y escribe en Origen a;b;c;d. Deja marcada Celda con lista desplegable.
En la pestaña Mensaje de error, deja el estilo Alto y escribe «Elige a, b, c o d». Así nadie puede contestar «la b» o «Canberra», que la fórmula no sabría corregir. Si quieres ver todas las opciones de la validación, tienes la guía de listas desplegables y validación de datos.
Las mayúsculas no importan: si alguien escribe B, Excel lo acepta y la corrección lo cuenta igual que b.
Escribe la clave de respuestas en la columna E
En E2:E11 escribe la letra correcta de cada pregunta, en minúscula y sin espacios: en este ejemplo, b, c, a, d, b, c, b, a, d, a. Esta columna es la plantilla de corrección: la ocultarás antes de repartir el test.
Revisa la clave dos veces. Un error aquí resta un punto injusto a todo el mundo, y en una hoja que se corrige sola nadie lo nota hasta que alguien reclama.
Un espacio detrás de la letra («b ») hace que la respuesta nunca coincida. Si una pregunta da 0 a todo el mundo, revisa esa celda de la clave.
Puntúa cada pregunta con SI
En D2 escribe =SI(C2=E2;1;0): si la respuesta coincide con la clave, vale 1; si no, 0. Una pregunta sin contestar también da 0, porque la celda vacía no coincide con ninguna letra. Arrastra el controlador de relleno hasta D11.
En el ejemplo, la persona falló la 4 (marcó c, era d) y la 7 (marcó d, era b). Si la función SI es nueva para ti, mira la función SI paso a paso.
1 punto si la respuesta coincide con la clave; 0 si no coincide o está en blanco.
Suma los aciertos y calcula la nota sobre 10
En D13 suma los puntos con =SUMA(D2:D11). Si prefieres no tener la columna de puntos, una sola fórmula hace lo mismo: =SUMAPRODUCTO(--(C2:C11=E2:E11)). La comparación devuelve una lista de VERDADERO y FALSO; el doble signo menos (--) los convierte en 1 y 0, y SUMAPRODUCTO los suma. Otra opción es =CONTAR.SI(D2:D11;1).
En D14 calcula la nota con =REDONDEAR(D13*10/FILAS(E2:E11);1). FILAS cuenta las preguntas, así que la fórmula sirve aunque tengas 12 o 25: con 8 aciertos de 12 daría 6,7. En el ejemplo, 7 aciertos de 10 dan un 7.
Cuenta los aciertos sin necesitar la columna de puntos.
Nota sobre 10 con un decimal, válida para cualquier número de preguntas.
Colorea aciertos y fallos cuando quieras revelarlos
Escribe Corregir en G1 y pon en H1 una lista desplegable con Sí;No (valor inicial No). Selecciona C2:C11 y ve a Inicio › Formato condicional › Nueva regla.... Elige Utilizar una fórmula que determine las celdas para aplicar formato, escribe la fórmula del verde, pulsa Formato... y elige un relleno verde. Repite con una segunda regla para el rojo.
Mientras H1 diga No, no se ve nada. Cuando cambies a Sí, cada respuesta se pinta de verde o rojo: perfecto para repasar el test en clase. Si prefieres que los colores salgan al momento, quita la parte $H$1="Sí". Más ejemplos en formato condicional con fórmulas.
Verde: la corrección está activada y la respuesta coincide con la clave.
Rojo: la corrección está activada y la respuesta no coincide.
Desbloquea las respuestas y oculta la clave
Primero, selecciona C2:C11, pulsa Ctrl+1 y, en la pestaña Proteger, desmarca Bloqueada: serán las únicas celdas editables. Después selecciona las fórmulas de D2:D14, vuelve a Formato de celdas › Proteger y marca Oculta, para que la barra de fórmulas no enseñe a qué celdas apuntan.
Por último, haz clic derecho en el encabezado de la columna E › Ocultar. Si es un examen con nota, oculta también la columna D: así nadie ve sus puntos mientras contesta y no puede ir probando letras hasta acertar. Si es un test de repaso, deja D a la vista y el ejercicio se autocorrige al instante.
Protege la hoja y reparte una copia a cada persona
Ve a Revisar › Proteger hoja.... Desmarca Seleccionar celdas bloqueadas (así solo se puede hacer clic en las respuestas y Tab salta de una a otra) y deja desmarcada Aplicar formato a columnas, que es lo que impide volver a mostrar las columnas ocultas. Pon una contraseña y pulsa Aceptar. Todo el detalle está en proteger hojas y celdas.
Guarda el archivo y envíalo como adjunto: cada persona recibe su propia copia, la rellena y te la devuelve. Al recibirla, desprotege la hoja con tu contraseña y muestra las columnas ocultas para ver la nota.
No compartas un único enlace de OneDrive con permiso de edición para toda la clase: todos escribirían sus respuestas en el mismo archivo, encima de las del anterior.
Trucos de experto
- Errores que restan. Si cada fallo resta un tercio de acierto, como en muchas oposiciones, usa
=MAX(0;REDONDEAR((D13-(CONTARA(C2:C11)-D13)/3)*10/FILAS(E2:E11);1)).CONTARAcuenta las preguntas contestadas; las que se dejan en blanco no restan. En el ejemplo: 7 aciertos y 2 fallos dan un 6,3. - Pon la clave en otra hoja. Si guardas la clave en una hoja llamada Clave, compara con
=SI(C2=Clave!B2;1;0), oculta esa hoja (clic derecho en la pestaña › Ocultar) y protege el libro con Revisar › Proteger libro para que no se pueda volver a mostrar. - Nota con texto. Añade una calificación junto a la nota:
=SI(D14>=9;"Sobresaliente";SI(D14>=7;"Notable";SI(D14>=5;"Aprobado";"Suspenso"))). - Reutiliza la plantilla. Guarda el test vacío como plantilla y, para cada examen nuevo, cambia solo las preguntas y la clave: la validación, las fórmulas y los colores ya están hechos.
Errores comunes y cómo arreglarlos
Una pregunta da 0 a todo el mundo aunque la respuesta sea correcta.
En la celda de la clave hay un espacio de más o una letra distinta. Desprotege la hoja, muestra la columna E y escribe la letra sola. También puedes limpiarla con =ESPACIOS(E2).
La nota sale como 6,66666667 o con muchos decimales.
Falta REDONDEAR. Usa =REDONDEAR(D13*10/FILAS(E2:E11);1) para quedarte con un decimal. Cambiar el formato de número solo oculta los decimales, no los quita.
El alumno ve si acierta en cuanto elige una letra y va probando hasta dar con la buena.
Los puntos de la columna D están a la vista. Oculta también la columna D antes de proteger la hoja, o deja la corrección en color apagada con H1 en No.
Proteges la hoja y nadie puede elegir respuestas.
No desbloqueaste C2:C11. Desprotege la hoja, abre Formato de celdas (Ctrl+1) › Proteger, desmarca Bloqueada en esas celdas y vuelve a proteger.
En Excel para Mac el proceso es el mismo: Datos › Validación de datos para la lista, Formato de celdas (⌘+1) › Proteger para Bloqueada y Oculta, y Revisar › Proteger hoja. Las fórmulas no cambian.
Preguntas frecuentes
¿Puede alguien ver la clave aunque esté oculta?
Quien sepa bastante Excel, sí: proteger una hoja evita despistes y curiosos, pero no es una medida de seguridad. Para un examen con consecuencias serias, usa una herramienta pensada para ello, como un cuestionario de Microsoft Forms, que también se corrige solo y cuyos resultados puedes abrir en Excel.
¿Y si mi Excel da error con el punto y coma?
Tu Excel usa la coma como separador (pasa en México y otros países). Escribe =SI(C2=E2,1,0) y, en el origen de la lista, a,b,c,d. El botón de esta página cambia todos los ; por , de una vez.
¿Puedo dar más puntos a unas preguntas que a otras?
Sí. Añade una columna Valor (por ejemplo en F2:F11) y cambia la fórmula de puntos por =SI(C2=E2;F2;0). Para la nota, divide entre SUMA(F2:F11) en lugar de FILAS(E2:E11).
¿Cómo veo las notas de toda la clase juntas?
Cuando te devuelvan los archivos, copia la nota de cada uno en una hoja resumen, o pide que te devuelvan solo la nota. Si el test se repite a menudo con mucha gente, un cuestionario de Microsoft Forms recoge todas las respuestas en una sola tabla.
Fuentes oficiales
Documentación de Microsoft consultada para esta guía.
Se guarda solo en este navegador.
Sigue aprendiendo
Listas desplegables y validación de datos: que nadie escriba lo que no debe
Crea listas desplegables en Excel con Validación de datos, añade mensajes de ayuda y de error, y limita números, fechas y duplicados. Paso a paso.
Proteger hojas y celdas: que solo se pueda escribir donde toca
Desbloquea las celdas de entrada y protege la hoja en Excel para que nadie borre tus fórmulas por error. Paso a paso, con contraseña opcional y sus límites.
La función SI explicada con ejemplos (y SI anidados sin perderte)
Aprende la función SI de Excel con ejemplos reales: textos, cálculos y SI anidados para más de dos resultados, sin errores. Paso a paso con imágenes.
Formato condicional con fórmulas: resalta filas enteras, fechas vencidas y duplicados
Crea reglas de formato condicional con fórmulas para pintar filas enteras, facturas vencidas y duplicados, y entiende de una vez el uso del $. Paso a paso.
Casillas para marcar en Excel: formularios, listas de tareas y recuentos automáticos
Inserta casillas para marcar en Excel, cuenta las tareas hechas, calcula el % completado y tacha las filas terminadas solas. Paso a paso, con fórmulas.