Para rellenar una Base de Datos

podemos pasar de un dato a otro de la misma fila con <TAB> y si después de introducir el último dato de la fila pulsamos <INTRO> el cursor se posicionará en el primer dato de la siguiente fila.

Para separar un sector circular en un gráfico

se pulsa con el ratón sobre él y luego se separa. También se puede cambiar el color de un sector pulsando sobre él con el botón derecho del ratón y eligiendo 'Formato de punto de datos...'.

Para incluir una nueva serie de datos en un gráfico

se copia el rango de datos nuevo y se pega sobre el gráfico.

Para seleccionar una tabla completa

basta con posicionarse en cualquier celda de la tabla y pulsar [CTRL]+*. (Si usa el asterisco del teclado general no olvide que se consigue pulsando también la tecla [MAYUSCULAS]).

Para poder ver las fórmulas de una hoja

puede hacer lo siguiente: Herramientas, Opciones, pestaña Ver, y en Opciones de Ventana marcar Fórmulas.

Pero también existe un método de Teclado Abreviado que consiste en pulsar [ALT]+º y si se vuelven a pulsar estas dos teclas se muestra la hoja como habitualmente la vemos.

Personal.xls

Podemos crear una macro para que este disponible en todas las hojas de cálculo. Esto se hace con el libro PERSONAL.XLS

Personal.xls es un Libro que se carga cada vez que se inicia Excel, pero no esta visible ya que tiene el atributo de Escondido (Hidden), por lo que si necesitamos modificarlo lo debemos hacer a través del comando "Mostrar" del menú "Ventana". En caso que no exista lo podemos crear con un libro de trabajo nuevo, salvarlo con este nombre y dejarlo en la carpeta "InicioXL" (XLStart), que se encuentra en la carpeta "Office" del directorio donde esta instalado MSOffice. Cada vez que dejamos de trabajar con este archivo, lo debemos salvar y luego activar el comando "Ocultar" del menú "Ventana", para que no se vea cuando iniciamos la aplicación.

La ruta de mi fichero es:

C:\Documents and Settings\Adolfo\Datos de programa\Microsoft\Excel\XLSTART\[PERSONAL.XLS]Hoja1

Protección de una hoja

Las celdas por defecto vienen bloqueadas. Si deseamos desbloquear algunas de ellas debemos hacer lo siguiente: Formato, Celda, pestaña Proteger, quitar la casilla de verificación que esta puesta por defecto en 'Bloqueada'. Si marcamos 'Oculta' no se verá el contenido de la celda en la barra de fórmulas. Esto se hace cuando no se desea que alguien vea la fórmula de una celda. Después se debe proteger la hoja con: Herramientas, Proteger, Proteger hoja. Se le pueden permitir más o menos acciones al usuario. Si no le permitimos seleccionar las celdas bloqueadas y desbloqueadas no podrá posicionarse con el cursor sobre ellas. Si únicamente le dejamos seleccionar las desbloqueadas nos encontraremos con algo parecido a un formulario.

Propiedad EnableSelection

Para reforzar el bloqueo del paso anterior podemos hacer lo siguiente:

Ir al Editor de Visual Basic. En el Explorador de proyectos ir a la hoja que queramos proteger. En la Ventana de propiedades buscar la propiedad EnableSelection y ponerle el valor 1. Cerrar el editor, volver a Excel y grabar la plantilla.

Con la doble opción (protección de hoja y la propiedad EnableSelection) será imposible seleccionar una celda, y por lo tanto copiar o modificar cualquier valor. Este procedimiento no funciona en Excel 2000 y en las versiones anteriores, porque la propiedad EnableSelection no conserva el valor modificado al cerrar la plantilla. Aunque lo cierto es que pocos usuarios conocen la citada propiedad.

EnableSelection = xlUnlockedCells

Para paliar el inconveniente citado anteriormente para las versiones de Excel 2000 y anteriores se puede hacer lo siguiente:

Ir al Editor de Visual Basic. En el Explorador de proyectos ir a la hoja que queramos proteger. En la Ventana de propiedades buscar la propiedad EnableSelection y ponerle el valor 1. Cerrar el editor, volver a Excel y grabar la plantilla.

Con la doble opción (protección de hoja y la propiedad EnableSelection) será imposible seleccionar una celda, y por lo tanto copiar o modificar cualquier valor. Este procedimiento no funciona en Excel 2000 y en las versiones anteriores, porque la propiedad EnableSelection no conserva el valor modificado al cerrar la plantilla.

Ese procedimiento se puede realizar también mediante una macro:

Private Sub Workbook_Open()
  Worksheets("Hoja1").EnableSelection = xlUnlockedCells
End Sub

El valor por defecto es xlNoRestrictions

Opciones de Ordenación

Al ordenar un rango de datos podemos elegir en Opciones que la ordenación sea Normal (alfabética) o según una de las Listas Personalizadas de Excel.

Puede distinguir mayúsculas de minúsculas.

Puede ordenar en vertical (que es lo normal) o en horizontal.

Criterios utilizados para filtrar una base de datos

Un criterio está formado al menos por dos celdas. La primera es un nombre de campo y la segunda que estará debajo es el criterio propiamente dicho.

Cuando usamos varios criterios estos se relacionan entre si mediante concatenadores lógicos Y u O. Por ejemplo, supongamos que deseamos filtrar de una base de datos los registros correspondientes a trabajadores que cumplan que su salario esta comprendido entre 30.000 y 40.000 euros, o bien su edad es de 30 años y se llaman Maria. En ese caso el criterio esta formado por múltiples celdas, de forma que los criterios que estan en la misma fila se han de cumplir ambos (operador Y), y si están en fila distinta se han de cumplir unos u otros (operador O).

Varias líneas en una celda

Para escribir varias líneas en la misma celda puedes escribir lo que quieras y para abrir un espacio hacia abajo en la misma celda pulsar Alt+Enter.

Mover celdas entre hojas

Seleccione el rango a mover. Llévelo hasta la pestaña de la hoja de destino, presione ALT y mantenga presionado hasta que deje el rango en la parte de la hoja de destino que desee.

Insertar filas o columnas rápidamente

En una hoja de Excel que ya tengas rellena, prueba a hacer esto:

Vete al principio de lo que tengas escrito

Pon el ratón sobre el control de relleno (es el puntito negro que tiene el cursor abajo a la derecha). Sabes que estás encima del control de relleno, porque la cruz blanca que es normalmente el ratón se cambia a color negro

Ahora pulsa la tecla de mayúsculas (no la de bloq. mayús) y sin soltarla pulsa el botón izquierdo del ratón y arrástralo hacia abajo o hacia la derecha, verás que se insertan filas o columnas.

Copiar en Excel XP arrastrando con Ctrl

Se puede copiar una celda arrastrando con Ctrl desde alguno de los 4 lados del borde de la celda activa.

Si no se emplea el Ctrl la celda no se copia, se mueve.

Edición, Rellenar, Series

Poner el primer número y arrastrar el controlador de relleno con la tecla Ctrl pulsada. Esto nos dará una serie con incrementos unitarios.

Algunas teclas de navegación   

Para ir al extremo superior de un bloque de datos, haga doble clic en el extremo superior de la celda seleccionada. Con los demás bloques puede ir a los demás extremos del bloque. Y si lo hace mientras presiona la tecla Shift podrá seleccionar rangos.

Cálculo automático en la barra de estado

Algunas abreviaturas de teclado

Al pulsar Intro se desplaza el cursor hacia abajo, pero se puede cambiar

Puede cambiarlo en Herramientas, Opciones, Modificar, Mover selección después de ENTRAR.

F11

Pulsando F11 sobre una serie de datos se construye un gráfico completo y de forma automática.

Proteger celdas

Con Formato, Celda, Protege, quitar la casilla de verificación en Bloqueada se consigue que en estas celdas se pueda escribir después de ejecutar Herramientas, Proteger, Proteger hoja.

Con TAB se puede saltar de una a otra celda de las desbloqueadas. Y con Mayúsculas + TAB se salta hacia atrás.

Ocultar la formula en una celda

Con Formato, Celda, Proteger, Oculta se marcan las celdas cuyas fórmulas o contenido visto en la línea de edición se desee ocultar. Después se ha de proteger la hoja, con Herramientas, Proteger, Proteger hoja.

Copiar una hoja

Para copiar una hoja completa dispone de dos sistemas:

El primero y más fácil es señalar con el ratón la pestaña de la hoja a copiar, pulsar Ctrl y sin soltar arrastrar la pestaña con el ratón. Al soltar se habrá duplicado completamente la hoja. Si la hoja a copiar se llamaba 'Hoja 1', la nueva hoja se llamará  'Hoja 1 (2)'

Es segundo método consiste en situar el ratón en el cuadradito que queda como intersección del indicador de filas y columnas. Esto selecciona la hoja completamente. Seguidamente se copia al portapapeles, por ejemplo con Ctrl + C. Se inserta una hoja nueva y pulsando sobre ella en el citado cuadradito se pega el contenido del portapapeles, por ejemplo con Ctrl + V.

Insertar Comentarios

Se pueden insertar comentarios en las celdas. Para ello, pulse con el botón derecho del ratón y elija 'Insertar Comentario'. Puede verlos completamente, solo la indicación de que la celta tiene un comentario, o no verlos en absoluto según elija una opción u otra en Herramientas, Opciones, Ver, Comentarios.

Texto con diversos formatos en una misma celda

Se pueden dar distintos formatos a los caracteres o palabras de una misma celda.

Por ejemplo: puede poner en una celda la siguiente expresión:   Volumen (m³)

Véase el distinto color de la fuente y el 3 como superíndice.

Esto se consigue señalando en la barra de fórmulas el carácter o caracteres que se quieren cambiar y aplicándoles el formato deseado.

Trabajar simultáneamente en varias hojas

Antes de escribir texto, formulas, dar formato, etc.. en varias hojas simultáneamente ha de señalarlas primero.

Si son un conjunto de hojas correlativas, señale la primera haciendo clic con el ratón sobre la pestaña que indica el nombre de la primera y haga Mayúsculas + clic en la última.

Si las hojas no son consecutivas selecciones la primera con un clic y las restantes con un Ctrl + clic en cada una.

Cuando haya terminado de introducir la información y formatos comunes haga clic en cualquier pestaña y así se desactivará el modo de trabajo conjunto.

Escribir en varias celdas simultáneamente

Señale las celdas donde quiere escribir una misma expresión. Incluso si son de rangos separados. Escriba dicha expresión. Y valide su introducción con Ctrl + Intro.

Ver todas las fórmulas de una hoja

Se consigue pulsando Alt + º  (esto es, la tecla Alt y simultáneamente la tecla de 1º que está a la izquierda del número 1). Para volver a la situación anterior volver a pulsar.

También se puede conseguir mediante: Herramientas, Opciones, Ver, Fórmulas.

Borrar

Con el comando Edición, Borrar se puede elegir entre Todo, Formatos, Contenido (Supr) o Comentarios.

Para borrar un rango primero seleccione el rango y luego presione Shift mientras arrastra con el ratón el indicador de relleno hacia el extremo superior izquierdo del rango. Si no presiona Shift conseguirá borrar el contenido pero no los formatos.

La fuente por defecto en Excel es la Arial 10 pero se puede cambiar

Se puede cambiar para todos los libros nuevos mediante: Herramientas, Opciones, General y elegir la Fuente Estándar y el tamaño.

Pero también se puede cambiar la fuente sólo en el libro activo de la siguiente forma. Activar el libro en el que se quiere cambiar la fuente por defecto. Ejecute Formato, Estilo y dentro del formato de estilo Normal elegir Modificar, Fuente y proceder a cambiar la fuente. Finalmente Aceptar.

Plantillas   *.xlt

Las plantillas permiten predefinir formatos, estilos, textos, fórmulas,  barras de herramientas personalizadas e incluso macros, que se utilizarán en todos los nuevos libros abiertos (si la plantilla se llama libro.xlt) u hojas nuevas insertadas (si la plantilla se llama hoja.xlt). También pueden existir otras plantillas para diferentes usos, por ejemplo, para predefinir una factura, cierto informe mensual, un balance, etc.

La plantilla de libro predeterminada (libro.xlt) y la plantilla de hoja predeterminada (hoja.xlt) se han de grabar como plantilla en el directorio INICIAR que para la versión XP suele estar en:

C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\INICIAR

o bien en:

C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\XLSTART

Tanto las plantillas que tenga en la carpeta de inicio predeterminada de Excel como las que haya definido en la carpeta de plantillas (C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Plantillas) las tendrá disponibles cuando haga Archivo, Nuevo y salga el Panel de Tareas y en el elija Plantillas Generales.

Puede trabajar con Estilos

En Formato, Estilo puede modificar el estilo normal o crear nuevos estilos, pero sólo estarán disponibles en el Libro Activo.

Si se desea que los estilos estén disponibles de forma permanente sigua los siguientes pasos: Abra el libro cuyos Estilos quiere guardar permanentemente. Abra un libro nuevo y combine los Estilos en éste. Ejecute Archivo, Guardar como, elija como tipo Plantilla (*.xlt). Escriba como nombre de fichero Libro.xlt o Hoja.xlt y guárdelo en la carpeta de inicio de Excel (C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\INICIAR). De esta forma cada vez que abra un libro u hoja nuevos se dispondrá de estos Estilos.

Puede convertir el estilo de una celda en un tipo de estilo personalizado. Sitúese en la celda patrón y elija Formato, Estilo y cambie el nombre por uno nuevo de su elección.

Puede transferir los estilos de un libro a otro mediante Formato, Estilo, Combinar. Para ello abra los dos ficheros (o más) y ejecute los pasos señalados desde el fichero hacia el que quiere llevar los nuevos estilos.

Libros que se abren al iniciar Excel

Los libros que coloque en la carpeta de inicio de Excel:

C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\INICIAR

o bien en

C:\Documents and Settings\nombreusuario\Datos de programa\Microsoft\Excel\XLSTART

Se abrirán al iniciar Excel.

También puede definir una carpeta de inicio de Excel en Herramientas, Opciones, General, "Al inicio, abrir todos los libros en:"

La función =CELDA

La función CELDA es la típica función que sirve para varias cosas según que argumentos se utilicen.

Si se usa con "nombrearchivo"

=CELDA("nombrearchivo")

proporciona el nombre y ruta del archivo (siempre que esté grabado). En la ayuda hay un error y en lugar de poner "nombrearchivo" pone "archivo".

Otro argumento interesante es "contenido".

=CELDA("contenido";A3)      Proporciona el contenido de la celda A3.

La función =TEXTO

Esta función permite convertir un valor numérico en un texto. Su función contraria es =VALOR.

Al escribir un número en una celda queda alineado a la derecha, si se escribe como texto (anteponiéndole una comilla simple) quedará alineado a la izquierda. Ejemplo: '54

La función TEXTO tiene dos argumentos que según la ayuda son: =TEXTO(valor;formato)

valor: es el número que queremos convertir a texto

formato: es el formato que queremos darle. Es un argumento optativo

Pero la ayuda no se corresponde con la realidad de la función, ya que en la práctica esta función tiene los argumentos cambiados de orden.

Pruebe =TEXTO(;"43")

Pero si prueba =TEXTO("43") obtendrá un error

Curiosidades de alguna funciones con valor propio

Algunos nombres de función tienen valor propio si se escriben un una celda y sin argumentos. O al menos eso es lo que he podido comprobar en la versión XP.

Ponga en una celda cualquiera +PRECIO y al pulsar intro verá que aparece un valor. A mi me da -1965686714.

Las funciones que he visto que tienen valor propio son las siguientes:

y muchas más. Por lo que veo, son las que aparece al ampliar las funciones al instalar las complementarias, con

Herramientas, Complementos, Herramientas para Análisis.

Sintaxis para hacer referencia a un Rango:

Si el rango está en la misma Hoja:

Rango

Si está en otra Hoja del mismo Libro:

'Nombre de Hoja'!Rango

Si está en una Hoja de otro Libro, y el Libro se encuentra en la misma carpeta que el actual:

'[Nombre de Libro.xls]Nombre de Hoja'!Rango

Si está en una Hoja de otro Libro, y el Libro se encuentra en diferente carpeta que el actual:

'c:\Carpeta\SubCarpeta\[Nombre de Libro.xls]Nombre de Hoja'!Rango

Si el rango está en otro ordenador conectado al nuestro por una Intranet:

'\\Servidor\Carpeta\[Nombre de Libro.xls]Nombre de Hoja'!Rango

Si el rango está en otro ordenador conectado al nuestro por Intrernet:

'http://www.servidor.com/Carpeta/[Libro.xls]Hoja'!Rango

Los apóstrofes (_') son  necesarios si existen espacios.

Ejemplo: Prueba a poner esto en una celda de una hoja de Excel cualquiera:

=BUSCARV(200;'http://excelavanzado.com/xls/[busquedas.xls]Hoja1'!TAB1;2)

Si todo va bien, y tienes acceso a Internet verás como respuesta la letra B.

La función =EXTRAE y la función =ENCONTRAR

Si en B6 pones el nombre de una persona, por ejemplo: "Andrés Suarez Oliveira" la siguiente función obtiene las tres iniciales (ASO).

=IZQUIERDA(B6;1)&+EXTRAE(B6;ENCONTRAR(" ";B6)+1;1)&EXTRAE(B6;+ENCONTRAR(" ";B6;ENCONTRAR(" ";B6)+1)+1;1)

La función =RESIDUO  permite calcular la parte Fraccionario

En Excel disponemos de =ENTERO que calcula la parte entera, pero no disponemos de FRAC que calcularia la parte fraccionaria. Para calcular la parte Fraccionaria deberíamos usar uno de estos dos métodos:

=A1-ENTERO(A1)

=RESIDUO(A1;1)

Para poner el nombre del fichero en una celda

=EXTRAE(CELDA("NOMBREARCHIVO";A1);ENCONTRAR("[";

CELDA("NOMBREARCHIVO";A1))+1;+(ENCONTRAR("]";

CELDA("NOMBREARCHIVO";A1)))-ENCONTRAR("[";

CELDA("NOMBREARCHIVO";A1))-1)

Requiere grabar el fichero. Las 4 líneas anteriores son una misma fórmula.

Para poner el nombre de la hoja en una celda=EXTRAE(CELDA("nombrearchivo");ENCONTRAR("]";CELDA("nombrearchivo"))+1;255)

Requiere grabar el fichero.

Activar o Desactivar el empleo de la función  IMPORTARDATOSDINAMICOS

Al intentar operar con formulas que hacen referencia a celdas que provienen de una tabla dinámica con la versión XP de Excel nos encontramos con que automáticamente nos pone la función:
IMPORTARDATOSDINAMICOS, y nosotros preferiríamos que esto funcionara como antes.

Esto se debe a que desde Office XP la función IMPORTARDATOSDINAMICOS se activa por defecto cuando nos referimos a celdas que forman parte de una tabla dinámica.

Pero podemos desactivarla.

Para ello primero tendremos que llevar hasta alguna de las barras de herramientas un "botón", llamado "General GetPivotData".

Para llevar este botón hasta una barra de herramientas sigue estos pasos.

Selecciona: Ver, Barra de Herramientas, Personalizar.

Ve a la pestaña "Comandos". Ahora selecciona la Categoría "Datos" y en la ventana derecha (Comandos) busca el botón "General Get Pivot Data" (está cerca del final, aproximadamente el
octavo empezando por el final). Lleva este botón a la barra de herramientas de Excel que prefieras (esto se hace arrastrando el icono y soltándolo en alguna de las barras de herramientas).

Ahora, todo  lo que tendremos que hacer es pinchar este botón para activar / desactivar la función IMPORTARDATOSDINAMICOS, que viene activada por defecto.

Formula referida siempre a un mismo rango
=SUMA(INDIRECTO("A1:B2"))

Esta expresión permite sumar siempre el rango indicado aunque dicho rango se mueva a otra zona de la hoja.

Contar cuantas veces se repite un mismo caracter en un texto
=+LARGO(F3)-LARGO(SUSTITUIR(F3;"-";""))

Esta fórmula cuenta cuantos guiones (-) existen en un texto que esta en la celda F3.

Organizar la vista de varios libros. Extensión .xlw

Podemos tener varios libros abiertos y verlos simultáneamente en mosaico, en vista horizontal o vetical, en cascada. Si deseamos podemos grabar esta forma personalizada de verlos para la próxima vez. Esto se hace grabando un fichero con extensión .xlw

Para organizar las ventanas:
* En Excel 2007: Vista, Organizar todo

Para guardar el fichero xlw
* En Excel 2007: Vista, Guardar área de trabajo