Copiar datos de una hoja a otra en Excel sin macros

Frecuentemente recibo consultas sobre cómo copiar datos de una hoja a otra en Excel de una manera automática pero sin utilizar macros, y para atender ambos requerimientos solo tenemos la opción de utilizar ampliamente las fórmulas de Excel.

Antes de continuar debo mencionar que no existe una función de Excel que copie mágica y automáticamente los datos de una hoja a otra, así que será necesario crear varias fórmulas para lograr nuestro objetivo. Debido a la cantidad de fórmulas que podemos llegar a tener, esta alternativa de solución no será la más óptima para grandes cantidades de datos porque ocasionará que el libro de trabajo tome un tiempo considerable en el cálculo de las fórmulas. Así que, conociendo de antemano esta recomendación, iniciemos con el planteamiento del problema.

Planteamiento del problema

En una hoja de Excel tengo una base de datos de productos cuyos datos deseo copiar a una hoja independiente en base a su categoría. Para los datos de ejemplo necesitaré tres hojas adicionales para las categorías Fotografía, Libros y Software.

Copiar datos de una hoja a otra en Excel sin macros

Al agregar nuevos registros a la hoja principal, los datos deberán reflejarse automáticamente en la hoja de su categoría.

Estrategia de solución

La estrategia que seguiremos para copiar datos de una hoja de Excel a otra será la siguiente:

Como pasar datos de una hoja a otra en Excel

Partiremos de una hoja que contiene todos nuestros datos y a partir de ella crearemos una hoja temporal para cada categoría de datos. El único inconveniente que tendrán las hojas temporales serán las filas entre los registros, pero dichas filas serán eliminadas al crear la ficha final dejando solo los registros con los datos que nos interesan.

Copiar datos a una hoja temporal

En base a los datos mostrados en la imagen inicial, he creado una hoja temporal llamada Temp_Fotografia y en la cual extraeré los datos de la categoría Fotografía ubicados en la hoja Datos. La fórmula que colocaré en la celda A2 será la siguiente:

=SI(Datos!$C2="Fotografía",Datos!A2,NOD())

Esta fórmula evalúa el valor de la celda C2 de la hoja Datos para saber si pertenece a la categoría “Fotografía” y en caso de ser verdadero trae el valor de la celda A2 de la hoja Datos. En caso contrario, obtendremos un error #N/A originado por la función NOD. Observa el resultado de esta fórmula en la celda A2 de la hoja Temp_Fotografia:

Pasar datos de una hoja Excel a otra

La fórmula nos devuelve correctamente el valor de la primera columna del primer registro ya que efectivamente pertenece a la categoría Fotografía. Para obtener el resto de las columnas será suficiente con arrastrar la fórmula hacia la derecha para obtener el siguiente resultado:

Importar datos de una hoja de Excel a otra

Quiero que pongas especial atención a la referencia mixta que he colocado en la fórmula como Datos!$C2 la cual fija solamente la columna C lo cual es de suma importancia al momento de copiar la fórmula hacia la derecha. Para saber si el resto de registros pertenecen a la categoría Fotografía solo debo arrastrar la fórmula hacia abajo y obtendré el siguiente resultado:

Vincular datos de una hoja a otra en Excel

Si comparas los datos de la hoja temporal con la hoja de datos original observarás que solamente estarán presentes aquellos registros que pertenecen a la categoría Fotografía y el resto de filas se mostrarán con el error #N/A. Observa que he copiado la fórmula hasta la fila 20 haciendo previsión para cuando los datos originales también aumenten hasta la fila 20.

Siguiendo los mismos pasos mostrados hasta ahora debemos crear una hoja temporal para cada categoría de datos que deseemos copiar. La única modificación a la fórmula deberá ser el nombre de la categoría, por ejemplo, para la hoja temporal de la categoría Libros utilizaré la siguiente fórmula en la celda A2:

=SI(Datos!$C2="Libros",Datos!A2,NOD())

Copiar datos de una hoja a otra

El paso final es tomar como base las hojas temporales que hemos creado y eliminar las filas que tienen los errores #N/A para dejar solamente los registros que contienen datos. Para ello crearé una nueva hoja con el nombre de la categoría Fotografía y en la celda A2 colocaré la siguiente fórmula para copiar datos de una hoja a otra descartando las celdas con errores de la hoja temporal:

=SI.ERROR(INDICE(Temp_Fotografia!A$2:A$20, K.ESIMO.MENOR(SI(ESERROR(Temp_Fotografia!A$2:A$20), "", FILA(Temp_Fotografia!A$2:A$20)-MIN(FILA(Temp_Fotografia!A$2:A$20))+1), FILA(1:1))),"")

Esta no es una fórmula sencilla de explicar, pero la función principal es la función INDICE la cual obtiene el valor de un rango con solo especificar la fila. El rango sobre el cual se buscará el valor es el rango A2:A20 de la hoja Temp_Fotografia y que está indicado como el primer argumento de la función. El número de fila que será devuelto es calculado por la función K.ESIMO.MENOR que con la ayuda de la función ESERROR descarta las filas que contienen el error #N/A y solamente considera aquellas que tienen datos. Observa también que el resultado de la función INDICE está dentro de la función SI.ERROR para mostrar una cadena vacía en caso de encontrar un error.

Además, es necesario que esta fórmula sea ingresada como una fórmula matricial lo cual implica colocarla en la barra de fórmulas y pulsar las teclas Ctrl + Mayús + Entrar. En la siguiente imagen puedes observar el resultado de ingresar esta fórmula en la celda A2:

Fórmula para copiar datos de una hoja a otra en Excel

Después de haber ingresado esta fórmula en la primera celda la arrastraré hacia la derecha y posteriormente hacia abajo hasta la fila 20 y obtendré el siguiente resultado:

Datos de una hoja de Excel a otra sin utilizar macros

Puedes notar que las filas intermedias con errores que aparecían en la hoja temporal han sido eliminadas y ahora solo se muestran los registros con datos de la categoría correspondiente. De igual manera he copiado la fórmula hasta la fila 20 para que se reflejen los resultados automáticamente hasta un máximo de 20 registros. El procedimiento para las otras hojas será el mismo que hemos seguido hasta ahora y solo debemos cambiar la hoja temporal a la cual hace referencia la fórmula. Por ejemplo, para la hoja Libros utilizaré la siguiente fórmula en la celda A2:

=SI.ERROR(INDICE(Temp_Libros!A$2:A$20, K.ESIMO.MENOR(SI(ESERROR(Temp_Libros!A$2:A$20), "", FILA(Temp_Libros!A$2:A$20)-MIN(FILA(Temp_Libros!A$2:A$20))+1), FILA(1:1))),"")

Algo que no debes olvidar es que todos los rangos de las fórmulas que hemos creado hasta ahora los he limitado hasta la fila 20, pero si tienes una mayor cantidad de datos en tu libro, deberás adecuar dicho rango a la cantidad de filas necesarias.

Comprobar el funcionamiento de las fórmulas

Para comprobar que nuestras fórmulas funcionan correctamente será suficiente agregar nuevos registros en la hoja Datos y validar que se reflejen automáticamente en las hojas correspondientes. En la siguiente animación puedes observar cómo realizo esta comprobación:

Copiando datos de una hoja a otra en Excel

Puedes descargar el libro de trabajo que utilicé en este ejemplo y comprobar que al agregar datos para las demás categorías se verán reflejados en sus propias hojas. Es así como hemos aprendido a copiar datos de una hoja a otra en Excel sin macros, solo recuerda que entre más datos tengas mayor será la cantidad de fórmulas contenidas en el libro lo cual podría en algún momento llegar a  impactar en el desempeño de la hoja de cálculo.

Artículos relacionados
Cómo copiar y pegar celdas con VBA

33 comentarios en “Copiar datos de una hoja a otra en Excel sin macros

  1. walter

    excelente gracias es de mucha ayuda. sigan adelante

  2. Ricardo Palma

    Muy bueno esto me es de mucha utilidad.
    Seria fantástico que publicaran contenido de Tablas dinámicas.

  3. OSCAR MAURICIO BETANCUR

    Excelente Excel Total.
    Deseo cursos presenciales

  4. Jorge

    como se logra que las filas vayan marcandose de un color como ocurre en el ejemplo de Copiar datos de una hoja a otra??

    1. Moises Ortiz Autor

      Hola Jorge, eso sucedió en este ejemplo porque inicialmente había aplicado un formato de tabla al rango y aunque después lo removí, Excel siguió aplicando dicho formato a las nuevas filas.

  5. Horacio Carmona

    Excelente!!!

  6. Miguel

    Como dice un conocido, magia, gracias por todo

  7. ALEXIS

    Muy buena información creo que me sera muy útil

    Gracias

  8. Felipe Anaya

    Excelente aporte, mis respetos, aprendi algo nuevo gracias.

  9. Pascual Vàsquez E.

    Hola Amigo; Me ha resultado muy interesante los consejos y datos enviados, me gustaría seguir recibiendo estos.
    Saludos.

  10. Gerardo

    Es un aprendizaje continuo,muchas gracias

  11. Pascual Vàsquez E.

    Muy interesante y oportuno todos los datos que han enviado gracias.

  12. Gerardo Hernández.

    Hola Moisés Ortiz, He estado recibiendo las prácticas de Excel Total y esta presentación es una de las que para mi son mas complejas de realizar por la cantidad de formulas anidadas y el resultado final, seguiré atento a sus correos y prácticas para seguir aprendiendo con su aportación, le enviaré un correo para hacer una consulta personal y de ser posible informarme misma vía o por sus presentaciones, muchas gracias.

  13. silvio reyes

    Buen material, con ejemplos muy precisos. gracias por el aporte

  14. manuel

    Excelente explicación de como copiar datos justo lo que estaba necesitando gracias, saludos.

  15. reili

    Muy interesante. Francamente bueno.

  16. Ivan M.

    Muy bueno. Demasiado útil
    Gracias

  17. Luis Fernando Parra

    Excelente instrucción.
    Excel es un microuniverso que tiene múltiples aplicaciones y ha servido mucho en el mundo.

    Gracias por compartir estos conocimientos

  18. larry

    mioses, no se como haces para pone la formula entre llaves y que excel lo reconosca como formula ya que al hacerlo en mi pc [ {=SI.ERROR(INDICE(Temp_Fotografia!A$2:A$20, K.ESIMO.MENOR(SI(ESERROR(Temp_Fotografia!A$2:A$20), “”, FILA(Temp_Fotografia!A$2:A$20)-MIN(FILA(Temp_Fotografia!A$2:A$20))+1), FILA(1:1))),””)} ],excel lo toma como texto, si no escribo la formula entre llaves [ =SI.ERROR(INDICE(Temp_Fotografia!A$2:A$20, K.ESIMO.MENOR(SI(ESERROR(Temp_Fotografia!A$2:A$20), “”, FILA(Temp_Fotografia!A$2:A$20)-MIN(FILA(Temp_Fotografia!A$2:A$20))+1), FILA(1:1))),””) ],el primer valos lo resuelve pero para los tres siguientes no lo los resuelve los deja en blanco, o estoy aplicando mal la formula, muchas gracia y muy bueno tu aporte ojala ayan mas temas como este que son muy utiles para el desarrollo de algunos trabajos que nos demorarias dias e inclusive semanas. MUCHAS GRACIAS POR EL APORTE Y POR LA RESPUESTA

    1. Moises Ortiz Autor

      Hola Larry, no debes ingresar tú mismo las llaves porque eso no funcionará. Excel colocará automáticamente las llaves después de que pulses la combinación de teclas Ctrl+ Mayús + Entrar.
      Generalmente ingresamos una fórmula en la barra y pulsamos Entrar, pero para esta fórmula, en lugar de pulsar solo Entrar debes utilizar Ctrl+ Mayús + Entrar.

  19. ADAB

    ESTO ES LO MÁXIMO

  20. Elizabeth

    Excelente!!! muchas gracias.

  21. ANDRES

    Muy bueno muchas gracias por sus aportes

  22. Cesar

    Excelente… no cabe duda que dia a dia se aprende algo nuevo. Gracias nuevamente por compartir tus conocimientos. Rgrds!

  23. Mauricio Jiménez

    Muchas gracias Moises, excelente artículo

  24. Emilio Jimenez

    Gracias por el aporte, me es de gran utilidad.

  25. Jairo

    Muchas gracias me ha ayudado muchísimo!!!!!

    Todo me ha salido perfecto a exepción del ID PRODUCTO. En mi caso en ese apartado pongo la fecha.

    El problema es que no aparece la fecha si no que aparecen números, por ejemplo:

    En ID PRODUCTO pongo estas fechas:
    12/11/2013
    13/11/2013
    14/11/2013

    Y luego tanto en la hoja temporal como en la oficial me sale esto:
    41590
    41591
    41592

    No se porque pasará las fechas están en formato fecha.

    Sabrías explicarme porque pasa esto y si hay alguna solución? Muchsísimas gracias de ante mano.

    Saludos.

    1. Moisés Ortíz Autor

      Hola Jairo, todas las fechas en Excel son números, así que solo es cuestión del formato de las celdas. Esto se debe a que al momento de copiar las celdas a la hoja “destino”, dichas celdas tienen un formato General y por lo tanto la fecha se muestra con su valor numérico. La única solución es que des formato de fecha a las celdas antes (o después) de hacer la copia.

  26. Fernando

    Hola Moises, Excelente ayuda. Sin embargo, te comento que, al momento de ingresar la formula a la hoja final, en la barra de formulas, no me esta validando el comando “CTRL+MAYUS+ENTER”, ya que no esta añadiendo los “Corchetes” y pueda validar la información solicitada.

    Agradezco tu gran colaboración con este caso, ya que no se si las celdas deben tener algun tipo de formato o si, en las opciones de Excel se debe modificar algo.

    Cordial saludo.

    1. Moisés Ortíz Autor

      Hola Fernando, no es necesario aplicar algún formato ni tampoco modificar alguna opción de Excel. El comando Ctrl+Mayús+Entrar es predeterminado de la herramienta y debe funcionar.
      ¿En alguna otra ocasión has ingresado alguna fórmula matricial sin problema en tu versión de Excel?

  27. Bayron Arita

    Hola Moises son Bayron (Hondureño) Dios te siga bendiciendo y te siga llenando de sabiduria, quiero decirte que me fue mucha ayuda estas formulas que ahora estoy aplicando para un proyecto de trabajo en la empresa para la cual laboro, saudos

    Post Data : Siempre estoy pendiente de lo nuevo que mandas.

  28. Irina

    Hola,
    Me ha ayudado mucho tu publicación, tengo una duda, estas formulas son solo para office 2007, es que aún trabajo con excel 2003 y no me reconoce la formula?

    1. Moisés Ortíz Autor

      Hola Irina, el ejemplo está hecho en Excel 2010. La única función que no está disponible en Excel 2003 es SI.ERROR pero puedes reemplazarla por la combinación de la función SI y la función ESERROR. De hecho puedes ver que utilice esa combinación de funciones dentro de la función K.ESIMO.MENOR. Si quieres leer un poco más sobre esta equivalencia de funciones para el manejo de errores te recomiendo leer el siguiente artículo:
      http://exceltotal.com/manejo-de-errores-en-excel/