NOTEBOOK OF

EXERCISES AND PRACTICES

ADVANCING EXCEL

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 1

Content
Exercise 2.1.1 – Automatic Schemes.........................................................................................................................4
Exercise 2.1.2 - Manual Schemes...............................................................................................................................4
Exercise 2.2 – Subtotals.....................................................................................................................................................5
Exercise 2.3 – Database Functions....................................................................................................................6
Exercise 2.4 - VLOOKUP..........................................................................................................................................................7
Exercise 2.5 - Scenarios...................................................................................................................................................10
PRACTICE 1 - Schematization.......................................................................................................................................11
PRACTICE 2 - Schematization.......................................................................................................................................13
PRACTICE 3 – Subtotals....................................................................................................................................................15
PRACTICE 4 - Statistical Functions and Database Functions........................................................................16
Some Statistical Functions..........................................................................................................................16
Some database functions...............................................................................................................17
Practice 4.1 - Exercise....................................................................................................................................................18
Practice 4.2 - Exercise....................................................................................................................................................19
PRACTICE 5 - VLOOKUP Function........................................................................................................................................20
PRACTICE 6 - Search Function........................................................................................................................................21
PRACTICE 7 - Function HLookup or VLookup...................................................................................................................22
PRACTICE 8 – Scenarios: Vacation............................................................................................................................23
PRACTICE 9 – Scenarios: Product Currencies.........................................................................................................23
PRACTICE 10 – Scenarios: Analysis of Carolina.........................................................................................................24
EVALUATION BLOCK 2.....................................................................................................................................................25
Exercise 3.1 - Search Objective.........................................................................................................................................27
Exercise 3.2 - Data Table...........................................................................................................................................27
PRACTICE 11 - Search Objective.......................................................................................................................................29
Exercise 3.3 If Function.......................................................................................................................................................29
Exercise 3.4 - Nested If Function...................................................................................................................................30
PRACTICE 12 – Evaluation.................................................................................................................................................30
Exercise 3.4.1 - Sum If...................................................................................................................................................31
PRACTICE 13 – Conditional function...............................................................................................................................31
Exercise 3.5 – Conditional Formatting................................................................................................................................32
PRACTICE 14 - Conditional format..............................................................................................................................32
Ejercicio 3.5 Tablas Dinamicas..........................................................................................................................................32

CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Page 2

PRACTICE 15 - Pivot Tables.....................................................................................................................................34
Exercise 3.7 - Dynamic Graphics..................................................................................................................................35
PRACTICE 16 - Dynamic Graph....................................................................................................................................36
Exercise 3.8 – Add images to a graph.............................................................................................................36
PRACTICE 17 – Add image to graph........................................................................................................................37
PRACTICE 18 - Search for objectives......................................................................................................................................38

Practice 18.1 - Exercise..................................................................................................................................................39

Solver...................................................................................................................................................................................40
Practice 18.2 - Exercise..................................................................................................................................................42
Practice 18.3 - Exercise..................................................................................................................................................44
Practice 18.4 - Exercise..................................................................................................................................................45
PRACTICE 19 – Logical Conditions vs If() Function...................................................................................................48
PRACTICE 20 - Logical Condition AND..................................................................................................................................48
PRACTICE 21 - VLOOKUP Function.....................................................................................................................................49
PRACTICE 22 - Database, VLOOKUP, Sorting, Pivot Table................................................................50
Practice 23 - Management of Scenarios.............................................................................................................51
Practice 23.1 - Exercise..................................................................................................................................................52
Practice 23.2 - Exercise..................................................................................................................................................53
PRACTICE 24 – Conditional and Lookup Functions........................................................................................55
Practice 24.1 - Exercise..................................................................................................................................................63
Practice 24.2 - Exercise..................................................................................................................................................64
Practice 24.3 - Exercise..................................................................................................................................................68
Practice 24.4 - Exercise..................................................................................................................................................69
Practice 24.5 - Exercise..................................................................................................................................................71
Practice 24.6 - Exercise.................................................................................................................................................73

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 3

Exercise 2.1.1 - Automatic diagrams
You will learn to create automated charts in Excel.
Create the following table in Excel:
Expense Report
January February March April May June Total
Water 200 180 210 590
Light 180 180 180 180 180 180 1080
Telephone 250 270 272 275 275 281 1623
Rent 1500 1500 1500 1500 1500 1500 9000
Total 2130 1950 2132 1955 2165 1961 12293

2. Select the entire range of the table, including the columns and total rows.
3. Go to the DATA tab, locate the GROUP icon, click on the down arrow and
select AUTO SCHEMA.
NOTE: See that Excel automatically selects all the selected rows and applies the
corresponding group.

Exercise 2.1.2 – Manual diagrams

You will learn to make manual diagrams in Excel.
Create the following table in Excel:
Municipio o Entity
Delegation Federation Habitantes Porcentaje
Tijuana Lower California 1410700 1.37
Juarez Chihuahua 1313338 1.27
Izcatepec DF 1820888 1.76
Gustavo A Madero DF 193161 1.16
Ecatepec de Morelos State of Mexico 1688258 1.63
Nezahualcoyotl Edo Mex 1140528 1.1
Leon Guanajuato 1278087 1.24
Guadalajara Jalisco 1600940 1.66
Zapopan Jalisco 155790 1.12
Puebla Puebla 1485941 1.44

2. Select a range of rows such as Jalisco.

3. Go to the DATA tab and look for the GROUP icon. Click to group the specified rows.

NOTE: Manually grouping means that you will need to select which rows you want.
scheme. However, these rows must be contiguous; if there are scattered rows throughout
the table, it is advisable to first SORT by the column that you will group for
to be able to create the diagram.

ADVANCED EXCEL EXERCISES AND PRACTICE NOTEBOOK Page 4

Exercise 2.2 - Subtotals
Practice on how to put subtotals.

Create the following table in Excel:

Team Student Registration

CNCI Adjust 12301656 SOFIA LIZETH RENDON RENDON
CNCI Adjust 12301936 FERNANDO ULISES BARRON GONZALEZ
CNCI Aragon 1800873 MARIAN ANABEL RODRIGUEZ GOMEZ
CNCI Aragon 1801225 FERNANDO RICO MONTOYA
CNCI Aragon 1801305 JAVIER RAMIREZ NAJERA
CNCI Aragon 1800728 ARGENIS GONZALEZ HERNANDEZ
CNCI Aragon 1801758 RICARDO IVAN MATA GRANADOS
CNCI Atlatclo 9201440 JUAN CARLOS VELAZQUEZ ROMERO
CNCI Atlatclo 9200442 JESUS FRANCO CASTAÑEDA
CNCI Atzcaoitzalco 4601638 FRANCISCO ALFONSO GRUZ GARCIA
CNCI Atzcaoitzalco 4601632 KARLA IRENE BERLIN RODRIGUEZ
CNCI Atzcaoitzalco 4600938 ANDREA GONZALEZ CONTRERAS
CNCI Atzcaoitzalco 4601821 REYNALDO LOPEZ HERNANDEZ
CNCI Cuicuilco 6302167 MAXIMILIANO VILLEGAS BATALLA
CNCI Cuicuilco 6301817 MATILDE APARICIO CALVILLO
CNCI Cuicuilco 6301708 ALEJANDRO GONZALEZ PEREZ
CNCI Cuicuilco 6301832 ALDO ROSAS RANGEL
CNCI Cuicuilco 6302150 JOSE FRANCISCO PALACIO ACEVEDO
CNCI Cuicuilco 6301031 RENE OROPEZA SOTELO
CNCI Cuicuilco 6301059 HUGO ALBERTO CABALLERO ORTEGA
CNCI Cuicuilco 6301174 ODON JORGE ALEJANDRO DIAZ
CNCI Cuicuilco 6300903 DAVID MORENTIEL JOSE
CNCI Cuicuilco 6301590 NANCY MAR ALVAREZ
Cuicuilco CNCI 3602301 ANGEL URIEL RIVERA NUÑEZ
CNCI Cuicuilco 6301720 CLAUDIA MICHELLE LIRACHUNUÑEZ
CNCI Cuicuilco 6301865 MIGUEL ALVARO MARTINEZ ARROYO
CNCI Cuicuilco 6301931 DIEGO ALONSO AGUILA CASTAÑON
Cuicuilco CNCI 6302060 JOSE LUIS CUELLAS CHILD
CNCI Cuicuilco 6302072 JESUS EDUARDO PALACIOS JUAREZ
CNCI Cuicuilco 6301965 ADRIAN ARANDA DEITA
CNCI Cuicuilco 6301389 ENRIQUE JESUS LEON MEDINA
CNCI Cuicuilco 6301874 JAIME HECTOR TOVAR VIVAR
CNCI Cuicuilco 6301837 GAMALIEL GUSTAVO GARCIA FLORES

NOTEBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 5

CNCI Cuicuilco 3602740 VIRIDIANA CASTILLO CHAVEZ
CNCI Cuicuilco 6302045 LILIANA RAMON DAMIAN
CNCI Cuicuilco 6301842 JOS HIRVING POMPA RODRIGUEZ
CNCI Cuicuilco 6302140 ALEXANDRA OVALLE RODRIGUEZ
CNCI Cuicuilco 6302074 SERGIO OMAR ESPINOZA GOMEZ

2. Next, select the entire range of the table

3. Go to the DATA tab, locate the SUBTOTAL icon. Click there.
A dialog box will appear asking you for some options:
a. FOR CHANGE IN. This indicates the grouping according to the data change in a row. If
we select PLANTEL, it will group by plantels.
b. USE FUNCTION. Here we indicate to Excel what type of subtotal we want: COUNT,
SUMA, PROMEDIO, MAXIMO, MINIMO, PRODUCTO.
c. ADD SUBTOTAL A. Here we indicate in which column we want it to appear
subtotal. Check all that apply.
d. To ACCEPT.
5. Now Excel will apply the changes and display the table with subtotals by inserting rows according to the
time of the roster, and grouping them as a scheme.

Exercise 2.3 - Database Functions

You will learn to use some of the database functions available in Excel to manipulate
the information and obtain it based on query criteria.

1. Create the following table:

Type of
Nombre Age Sex Grade Qualification Evaluation
Emmanuel Rodriguez 16 m 1 9 extraordinary
Jonatan Vazquez 17 m 2 7 ordinary
Abraham Gaytan 20 m 3 8 ordinary
Paola Linderos 16 f 1 8 extraordinary
Alejandra Bautsta 20 f 3 8 extraordinary
Adolfo Ferruzca 18 m 2 7 ordinary
Rolando Campos 18 m 3 6 ordinary
Daniela Peña 16 f 1 7 extraordinary

AGE SEX GRADE RATING EVALUATION

17 m 2 8 ordinary
BDCOUNT

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 6

BDCOUNTER
BDMAX
BDMIN
BDSUMA
BDAVERAGE
BDPRODUCTO

You will see that at the top there is the information table. In the next section, there is another table.
small that is where the CRITERIA for consultation are. Let's note that we have the same titles that
In the top columns and in the bottom cell, we have the data we want to query. In the part
Below, we have a list of the functions that we are going to apply.

Almost all functions are built like this:

Function(table_range,column_to_search,criteria_range)
BDCOUNT(A5:F13,B5,B15:B16)

2. Perform the indicated functions following the same example.

NOTE: Consult your Study Guide where each of the functions is explained in detail.
Alternatively, check the Excel help section on functions. You can also ask your teacher.
help with any question.

Exercise 2.4 - VLOOKUP

You will learn to use the search functions available in Excel.

1. Create the following table:

Key Author Title Price

123321 Alejandro Dumas The Three Musketeers 350.00

135426 Arthur Conan Doyle The Adventures of Sherlock Holmes 345.00

124578 Benito Perez Galdos Marianela 234.00

235678 Charles Dickens A Christmas Carol 345.00

987654 Charles Dickens A Tale of Two Cities 234.00

986532 Charles Dickens Oliver Twist 432.00

NOTEBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 7

 876521 Edgar Allan Poe The black cat 34.00

124567 Edgar Allan Poe The Murders in the Rue Morgue 123.00

234590 Fyodor Dostoevsky Crime and punishment 234.00

102938 Fernando de Rojas The matchmaker 345.00

457812 Franz Kafka The Metamorphosis 456.00

567890 Gustave Flaubert Madame Bovary 321.00

124576 Jack Londo White Fang 432.00

113366 JM Barrie Peter Pan 321.00

124577 Johann Wolfgang Goethe Fausto 21.00

987066 Jules Verne From the Earth to the Moon 21.00

667788 Jules Verne Around the World in 80 Days 321.00

335578 Leo Tolstoy Anna Karenina 231.00

123345 Leo Tolstoy War and Peace 231.00

345677 Lewis Carroll Alice in Wonderland 234.00

345789 Lope de Vega Fuenteovejuna 234.00

987234 Mark Twain The Prince and the Pauper 343.00

567432 Mark Twain The Adventures of Huckleberry Finn 34.00

789654 Mark Twain The Adventures of Tom Sawyer 54.00

786655 Mary Shelley Frankenstein 223.00

443366 Miguel de Cervantes Saavedra Don Quijote de la Mancha 123.00

347890 Miguel de Unamuno Lata Tula 123.00

235473 Miguel de Unamuno Fog 189.00

123654 Moliere The ridiculous precious ones 178.00

876555 Moliere Truffle

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 8

 289.00
445677 Oscar Wilde The Canterville Ghost 389.00

665523 Oscar Wilde The Picture of Dorian Gray 289.00

907866 Oscar Wilde The Importance of Being Earnest 186.00

678955 Ruben Dario Blue 278.00

456733 Thomas More Utopia 378.00

554677 Victor Hugo The lookables 27.00

334466 William Shakespeare The Merchant of Venice 387.00

889906 William Shakespeare King Lear 188.00

234567 William Shakespeare Romeo and Juliet 288.00

986543 William Shakespeare A Midsummer Night's Dream 178.00

Note: you can shorten the size of the table by not listing all the books.

2. On a second sheet of the Excel book, create the following table:

Key Author Title Price

124567 Edgar Allan Poe The Murders in the Rue Morgue
124577 Johan Wolfgang Goethe Fausto
347890 Miguel de Unamuno Lata Tula
443366 Miguel de Cervantes Saavedra Don Quijote de la Mancha
554677 Victor Hugo The Miserables
567432 Mark Twain Las Aventuras de Huckleberry Finn
789654 Mark Twain The Adventures of Tom Sawyer
875521 Edgar Allan Poe The black cat
987066 Jules Verne From the Earth to the Moon

3. What we are going to look for is the price of the books that appear on this second sheet, so that
the formula must go in the PRICE cell of each row.
4. The function to use is VLOOKUP, so the formula becomes: =VLOOKUP(A3,Data!
A2:D41,4), where A3 is the searched value - in this case, the book key - , DATA!A2:D41, is the
range of the entire table that is in sheet1, which has been renamed as DATA; and 4, is the column -
price - what I want it to return.
We have to do the same with the rest of the table.

ADVANCED EXCEL EXERCISE AND PRACTICE NOTEBOOK Page 9

Exercise 2.5 – Scenarios

You will learn to create scenarios to answer the question WHAT HAPPENS IF? The scenarios show
different situations based on the same information.

We are planning our vacation and we have a budget of 10,000 pesos. We are going to
select 3 different destinations to find out which one suits us best.
2. We start with the first destination. Create the following table:

Budget for travel Huatulco

Days 3 2
Transfer 3000 3280
Lodging 2000
Foods 1000
Events 3000 3000
Souvenirs 500 500
9500 6780

3. Now, we go to the DATA tab, and we find the ANALYSIS AND WHAT IF? icon.
4. We select the option SCENARIO MANAGEMENT and a dialog box will appear with
a list box that will contain the different scenarios. (It will initially appear empty). We give
a click on ADD
5. Another dialog box appears asking us what this scenario will be called. We will type
DESTINATION1.
6. Then, a box appears indicating the changing cells, we will only select the
cells in column C (number 3), including subtitulo (to identify it). Example: C1:C7
7. And we click ACCEPT. Our first scenario has been created.
8. We will now modify column C, changing the values:

Chiapas
3
5400

3000
500
8900

9. Once finished, we repeat steps 3 to 7. Naming the scenario DESTINO2.

10. Lastly, let's change the values in column C again, with this information:

NOTEBOOK OF EXERCISES AND ADVANCED EXCEL PRACTICES Page 10

Maya River
3 days 2
nights
11250

500
11750

11. And we repeat the steps from 3 to 7 naming the scenario DESTINO3.
12. Now, in our SCENARIO MANAGEMENT dialog box, we have 3 destinations. In
at the bottom of this window, we have a button that says SHOW. If we select
DESTINO1 and then we press SHOW, we will see the values we entered at the beginning.
13. In this way, we can interpret the question: What HAPPENS IF I GO ON VACATION TO
DESTINO1? ¿Qué PASA SI VOY DE VACACIONES AL DESTINO2?

PRACTICE 1 - Outlining.

You work in a factory that has 3 production plants. You are tasked with making a summary of the
production of the 3 plants to show to the manager. As you know, this is a summary,
You need to group the information and only show the details if requested.

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 11

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 12
PRACTICE 2 – Schematization

You work as a teacher at a university and need to submit your students' grades. During
the course, you taught some practices and exercises, as well as some other activities that together we
of the student's assessment. However, the format you used (horizontal) is not suitable for
the school that requires it vertically.

Crea primero la hoja de DATOS ORIGINAL y copiaras esta a otra hoja donde haras el esquema. Pero al
You should use the TRANSPOSE option so that the scheme is correct.

This is the original sheet, and we see that the names of the students are in rows and the information with the
practice in columns. And in the end it should look like this:

ADVANCED EXCEL WORKBOOK OF EXERCISES AND PRACTICES Page 13

Using the TRANSPOSE option, we make the students appear as columns and the points
how to queue in order to do the schematization correctly.

NOTEBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 14

PRACTICE 3 – Subtotals

Now let's practice again with the subtotals. In this case, we have a list of our
musica favorita:

Make some subtotals to know:

a) How many songs/melodies do you have according to the genre.
b) How many songs according to the interpreter.

ADVANCED EXCEL WORKBOOK OF EXERCISES AND PRACTICES Page 15

PRACTICE 4 - Statistical Functions and Database Functions

A contnuacion debera practcar con las funciones de base de datos que vio en los ejercicios anteriores.
This practice requires 2 Excel workbooks: [Link] and [Link] that are practically
they contain the same information. Although in practice it is mentioned that they already exist, otherwise
You must create the same by typing the information that appears in the image below.

Some Statistical Functions.

=COUNT(range of cells).
Count the number of numerical boxes in the range.

=COUNTA(range of cells).
Count the number of alphanumeric cells in the indicated range.

=MODE(Range of cells).
Return the most common value of the range.

=AVERAGE(Range of cells).
Returns the average of the values in the indicated range of cells.

NOTEBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 16

Some functions of databases.

=COUNTIFS(Range or table name; Column number; Criteria).

Range or Name of the table = Range of boxes or name of the table.

Column number = Column where the data on which the operation is performed is located.
Criterio= Rango donde se encuentra el criterio,(previamente indicado en forma de tabla).

Count the number of cells in the Column Number of the table that meet the criterion condition.

=BDSUMIF(range or table name; column number; criteria).

Sum the numeric cells in the Column Number of the table that meet the condition of the criterion.

=AVERAGEIFS(range or table name; column number; criteria).

Calculate the average of the values in the Number column of the table that meet the criteria.

=BDMAX(range or table name; column number; criteria).

Returns the maximum value of the specified column Number of the table that meets the condition of
criterion.

=BDMIN(Range or table name;Column Number;Criteria).

Returns the minimum value of the Number column of the table that meets the condition of the criterion.

Criteria are always at least two fields, one which is the name of the column and another which is the
condition that must be fulfilled.

Column Number is the position that a specific column occupies within the table. For example in
exercise that will be seen later, Language occupies column four.

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 17

 Practice 4.1 - Exercise.

Retrieve the [Link] file, perform the following operations:

Name the table, there's no need to include the Average column.

Count the number of exams for all subjects, function =COUNT.

Count number of students, function =COUNTA.

Show the most common grades for each subject, function =MODE.

Calculate the average of each subject, function =AVERAGE.

Name Surnames Language Class Mathematics Historia Media

Txetxu Arazabaleta A 5 7 9 7.00
Itziar Bengoetxea B 7 5 5 5.67
Prudence Garage C 3 8 5 5.33
Leocadia Garcia B 6 5 8 6.33
Little star Gutiérrez C 6 6 7 6.33
Romualdo Gutiérrez B 8 4 6 6.00
Godofredo Martinetti A 7 5 6 6,00
Sigfrido Martinez C 5 7 8 6.67
Gumersindo Martínez C 7 6 5 6.00
Leovigildo Cress B 8 9 8 8.33
Heladio Parralo A 6 3 9 6,00
Urko Pérez A 7 4 5 5.33
Hyginia Pérez A 3 4 7 4,67
Theophilus It forbids A 6 3 9 6.00
Macarena Pitu B 6 5 6 5.67
Quintilian Sánchez C 3 8 5 5.33
Romualdo Santemesmasses B 9 3 7 6.33
Ataulfo Segarra A 5 5 6 5.33
Demetrius Sinforoso C 7 7 3 5.67
Abunciana Torrelles A 8 7 3 6.00
Hug Urdiales B 8 6 7 7.00
Idoia Zumalacarregi C 4 6 8 6.00

Media General Mathematical Language History

s
6.1 5.6 6.5
Exam No. 66

Nº Alumnos 22
Notes more Mathematical Language History

Common s
7 5 5
WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 18
 Practice 4.2 - Exercise.

On the same table, retrieve [Link].

Insert the following criteria table:

Mathematics Language History

<=5 <=5 <=5
Mathematics Language History
>5 >5 >5

Show the pass rates for each subject.

Show the failures of each subject,

Use the function =BDCONTAR(Table;Subject Column;Criteria Range). Remember that

criteria are at least two boxes. For example, to count the passes for Language,
the range of the criterion will be the cells occupied by Language and >5.

Notes
Suspense Approved
os the
Language 7 15
Mathematician 11 11
s
History 7 15

Insert the criteria table:

Class Class Class

A B C

Show the average by class and subject.

Function =AVERAGEIF(Table; subject column; Criteria Range).

Show the highest grade by class and subject.

Function =BDMAX(Table;Subject Column; Criteria Range).

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 19

 Average By Class and
Subject Clase A Clase B Clase C
Language
5.9 7.4 5.0
Mathematics 4.8 5.3 6.9
History 6.8 6,7 5.9

Maximum Grade Per Class and Subject

Language Clase A Clase B Clase C

8 9 7
Mathematician 7 9 8
s
History 9 8 8

PRACTICE 5 – Function SearchV

You work in a sales company and have a group of salespeople who report to you every month.
They report the amount sold. Based on that, we will apply an extra bonus according to the quantity that there is.
sold as an incentive for their work. Although the amounts presented in the example do not
correspond to reality, what we seek is to identify the amount sold per month to apply
said bond and identify our seller.

The data to search for is the salary.

You must show the percentage and the name of the seller as seen in the image.

ADVANCED EXCEL EXERCISE AND PRACTICE NOTEBOOK Page 20

PRACTICE 6 - Search Function H
Copy the following data to sheet 1 of the workbook:

- Use this data to create the same invoice, but instead of using the VLOOKUP function, use the
H. Search function.

- Once the entire invoice has been filled out using the VLOOKUP function.

- Make a transpose of this matrix in sheet 3 of the workbook, using the function
Transpose.

After transposing the data on sheet 3, transpose the data again in the same sheet 3 to
that its appearance is the same as on sheet 1.

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 21

PRACTICE 7 – Search Function H or Search Function V

Create the following in a new workbook.

This is an invoice, which although it is in English can be easily understood. What you should do is
that when the person types in the product number, its description automatically appears,
unit price. When entering the quantity, it will show us the final price, the subtotal, and the rest.

On a second sheet of the book, we will place the following information about our product inventory:

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 22

PRACTICE 8 - Scenarios: Vacations

Continuing with the travel example (see Scenarios exercise), establish three scenarios or destinations.
tourist destinations you can go to with a budget of 10,000.00 pesos; breaking down how much you would spend on
transportation, accommodation, food, etc.

PRACTICE 9 – Scenarios: Product Divisions

Create a table with the case of a Mexican company that makes all its sales in Europe, but
their raw material is bought in the United States; therefore, their income is in Euros, their expenses of
Production is in Dollars and since it is in Mexico, the rest of its income and expenses are in Pesos.

Therefore, the change team is very important.

There are three possibilities or scenarios for change:
1. Euro = 16.50; Dólar = 12.30
2. Euro = 16.80; Dólar = 12.10
3. Euro = 15.10; Dólar = 12.30

For more information on how to carry out this practice, consult your Study Guide (page 66).

EXCEL ADVANCED EXERCISES AND PRACTICE NOTEBOOK Page 23

PRACTICA 10 – Escenários: Análisis de Cároliná

Carolina is making her semester budget and wants to know how she will fare for the rest of the year. To do this,
have the following questions:
What happens if I have low income and high expenses?
2. What happens if I have many income and few expenses?
3. What happens if I have low income and low expenses?
4. What happens if I have normal income and normal expenses?

To make this projection, we need scenarios based on income and expenses. First, we need
put the information as follows:

Ahora necesita crear 2 escenarios: INGRESOS NORMALES y GASTOS NORMALES.

Modify the information in such a way that the income is low and the expenses are also low. Create 2.
scenarios more for LOW INCOME and LOW EXPENSES.
Modifique nuevamente la informacion para que ahora los ingresos sean altos y los gastos igual. Cree 2
scenarios for HIGH INCOME and HIGH EXPENSES.

Finally, show the scenarios to the teacher as:

HIGH INCOME and LOW EXPENSES

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 24

LOW INCOME and HIGH EXPENSES
NORMAL INCOME and HIGH EXPENSES
NORMAL INCOME and LOW EXPENSES
LOW INCOME and NORMAL EXPENSES.
Etc.

EVALUATION BLOCK 2

Retoma la relacion de libros, con su clave, nombre de autor,ttulo y precio para aplicarle los siguientes
themes:

Group in a scheme, obtaining a subtotal of books written by each author.

2. Apply the functions BDCONTAR and BDCONTARA
3. Develop the scenarios in the price list, assuming that the prices are in dollars and
you have three different exchange rates.
a. Escenario 1: dólar = 12.60
b. Scenario 2: dollar = 12.20
c. Escenario 3: dólar = 13.00

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 25

 BLOCK 3

NOTEBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 26

Exercise 3.1 – Search Objective

Let's assume that we need to fill out an invoice, and that at the bottom we must write the
following data:

The problem is that we know the value of the merchandise is 116 pesos already INCLUDING VAT.
In another way, we know that the total is 116 and that the VAT is 16%. Can you tell us what the price is?
of the merchandise. It's like doing the formula backwards.

Check your Study Guide from page 70 to 72.

Ejercicio 3.2 – Táblá de Dátos

We will create a table of two values, with the following example:

STEP 1:
Using the Payment() function, we capture the following information:

ADVANCED EXCEL EXERCISE AND PRACTICE NOTEBOOK Page 27

STEP 2:

Establish the rows and columns of our table.

The idea is that the formula we are using takes various values and does not give the result.
How much should we pay by modifying the interest rate and the number of months to pay, for example to
6, 12 and 24 months?

STEP 3:
Select the range of the table and go to the DATA tab. Select the arrow of the ANALYSIS AND IF icon.
to open the menu and select DATA TABLE.
Enter the input cell for the row. In this case, it is the number of months to pay, and it is in B2.
Enter the input cell for the column. The annual interest rate is in B1. Apply and go.
the results.

EXCEL ADVANCED EXERCISES AND PRACTICE NOTEBOOK Page 28

PRACTICE 11 – Search Objective

In the following balance, apply searching for the objective to find the amount you are missing to achieve it.
total sea equal to ZERO.

Asignale formato de numeros negatvos con color rojo, como aprendiste anteriormente.

Exercise 3.3 If Function

We have the following list of students with their respective final grades. The condition is: If the
If the grade is greater than 7.0, it should say PASSED; otherwise, it should say FAILED.

The formula is:

=IF( C2>7,"APPROVED","NOT APPROVED")

How do you modify it so that the first student is not failing?

EXCEL ADVANCED EXERCISES AND PRACTICES WORKBOOK Page 29

Exercise 3.4 - Nested If Function
We have a list of students and their final grades, the conditions to obtain a scholarship are:
If the final grade is between 9.5 and 10, you receive an 80% scholarship.
2. If the final grade is between 8.5 and 9.4, you will receive a 40% scholarship.
3. If the final grade is between 8.0 and 8.4, you receive a 25% scholarship.
4. If the final grade is less than 7.9, no scholarship is awarded.

The syntax is as follows for the formula:

=SI(D7>=9.5,”80%”,SI(D7>=8.5,”40%”,SI(D7>=8,”25%”,”sin beca”)))

PRACTICE 12 – Evaluation
Resolve the following:

Condition Formula in Excel

When the two cells are the same, it is shown the
word "equal". When the two cells are different,
It is not the same
If C6 is greater than 100, show C6. Otherwise
show 100
If B5 is less than or equal to 10, show B5. Of that
opposite show the word 'Maximum'
If the largest value in the range is greater than or
equal to half of the sum of the range, then
show the largest value. Otherwise show
half of the sum of the range.
If B8 is not equal to D6, check to see if B8 is
less than 10. Show 10 if it is and B8 if it is not. From
the opposite, show D6, which in case will be equal to
B8

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 30

Exercise 3.4.1 – Summarize If

Another conditional function is the SUMIF() function, which allows for conditional summation.

The function is: = SUMIF( range, criteria, sum_range)

=SUMIF(A1:A9, "Alejandra", B1:B9)

PRACTICE 13 – Conditional function.

Develop the conditional function for the following problem:
Given the following score of some football matches, create a formula that qualifies if the
the HOME team had: VICTORY, DEFEAT or DRAW.

LOCAL TEAM MARKER VISITING TEAM MARKER RESULT

Spain 1 Uruguay 2 Defeat
Chile 5 Mexico 1 Victoria
Switzerland 3 South Africa 0 Victoria
Honduras 0 France 0 Draw
Brazil 2 Argentina 3 Defeat
Portugal 0 South Korea 0 Draw
Ivory Coast 3 Greece 1 Victoria
North Korea 0 Nigeria 2 Defeat

EXERCISE AND PRACTICE NOTEBOOK ADVANCED EXCEL Page 31

Exercise 3.5 - Conditional format

This option in the Format menu makes data analysis simple, as it is enough to indicate a
condition and the desired format of the text in case it is met.

Here we are applying the format to the following conditions:

If the rating is between 1 and 6.9 the text is italicized

2. If the rating is between 7.0 and 8.5, the background is red
3. If the rating is between 8.6 and 10, the background is green.

NOTE: The conditional formatting appeared starting from Office 2003.

NOTE 2: You can review your Study Guide (pp. 80-82) for more information.

PRACTICE 14 - Conditional format

Create a list of all the members of your group, choose a list of subjects that they have.
taken with their respective grades, and apply a conditional format like the previous one.

Exercise 3.5 Dynamic Tables

Pivot table reports allow presenting the same information from different points of view.
information, using functions such as sum or average.

We have the following data table:

What is the total that each seller has sold?

2. What is the total sales by country?

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 32

 3. Who are the five best sellers?
4. ¿Cuáles son las ventas por mes?

Move the data to answer the previous questions. Check your Study Guide pages 83 to 87.
to see the steps.

ADVANCED EXCEL EXERCISES AND PRACTICE WORKBOOK Page 33

PRACTICE 15 – Dynamic Tables

Applying the previous topic, create a pivot table to find out how many students we have by location.
birth and by shift.

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 34

Exercise 3.7 - Dynamic Charts

It's the same idea as pivot tables but with a graph. It is recommended that for the graphs
they should be easy to understand, the pivot table should have no more than two row fields and two of
columns.

Create a dynamic chart of the student list from the previous practice. It should look like this:

Illuminate the data range

2. Go to the INSERT tab, locate the PIVOT TABLE icon, and click on the downward arrow.
right, to display DYNAMIC GRAPH.
3. You apply the options according to the dialog box.
4. Drag the field PLACE OF BIRTH as a series axis (or the first column of the table)
dynamic).
5. Drag the SEX field into the second column and we will see our chart.

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 35

PRACTICE 16 - Dynamic Chart
Create a dynamic chart with the following data.

Exercise 3.8 – Add images to a graph.

We will use the fill effects option within the graphs. The procedure is the same as creating
a graphic, the difference is found in the TRAMAS tab in the fill effects button

WORKBOOK OF EXERCISES AND ADVANCED EXCEL PRACTICES Page 36

It should end up looking something like this:

PRACTICE 17 – Add image to chart

With the following information, create a graph like the one shown:

Y el resultado seria como:

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 37

PRACTICE 18 - Seeking objectives

Search target.

With this tool, Excel automatically adjusts the value of a cell to achieve a
result determined in another. Evidently, the cell where you want to obtain the result must be
depend directly or indirectly on the cell to which the value is adjusted. The cell that
Change must contain a number; a cell with a formula cannot be used.

Ejemplo. Copie la tabla [Link] Compra, Total Venta y Beneficios se han de calcular.

ADVANCED EXCEL EXERCISE AND PRACTICE NOTEBOOK Page 38

 Calculate how much would be needed to obtain a profit of 55,000 pesetas.

[Link] option Tools/ Search Objective.

2. In the dialog box that appears, in the text box Define the cell: put the checkbox in the
that wants a certain result, in this case $B$6, Benefits. In this section, only
You can use cells that contain formulas.

3. In the following text box, With the value: enter the value you want to obtain, 55,000.

4. Changing cell: this text box is used to define which cell can be adjusted for
get the result in the indicated cell in Define the cell.
Remember that here you can only indicate boxes with numerical values that directly intervene.
or indirectly in the final result. Place the box where the amount D2 is.

5. Press on Accept.

Excel adjusts the value of cell D2 so that cell B6 results in 55,000.

Calculate what the selling price should be to achieve a profit of

120,000 points.

Calculate at what price it should be bought to obtain a profit of

90,000 pts.

For sheets with a large number of formulas, it can be very helpful to use the tools of
Audit. This way you will quickly see which boxes the box you want depends on.
get the result.

Practice 18.1 - Exercise.

Recover the file [Link]

Calculations.

Total Sales. Sales * Selling Price.

Total Purchases * Purchase Price.
Purchases.
Total Salarios. Trabajadores * Salario.
Benefits. Total Sales - (Total Purchases + Total
Salaries)

Use the Find Goals option to calculate some benefits of

450,000, changing:

Salary

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 39

 Sales Quantity
Selling Price

Remember to put back the original value in the variable box before making the next calculation.
For example, before defining the variable sales quantity, reinsert the value.
130,000 in the Salary box.

Sales Purchases Price Purchase Price Workers Salary

Sale
25000 7900 220 150 30 130000
Total Ventas 5500000
Total Purchases 1185000
Total Salario 3900000
Benefits 415000

Solver
Solver is similar to Goal Seek; it is also used to obtain a specific result.
in a box. This tool allows you to set more than one adjustable box. Also
allows for setting constraints, with this, it indicates to Solver that when making adjustments in the
Variable boxes must be limited to the conditions established in each restriction. From the same
in such a way that in Search Objective, the variable boxes must contain numerical values and must
intervene directly or indirectly in the formula of the cell where you want to obtain the
final result.

To use this method, follow the steps below:

1. Activate the Tools/Solver option, this box will open:

ADVANCED EXCEL EXERCISES AND PRACTICE NOTEBOOK Page 40

 Objective cell: Box where the result is desired.

Maximum: That the highest possible value is obtained in Target Cell.

Minimum: That it obtains the minimum possible value in Target Cell.

Equal to: Value to be obtained.

Changing the cells: Boxes that can vary their content to obtain the value
searched in Cell Objective, if there is more than one cell, they are separated by a point and
It can also define a range with the notation Initial Cell:Final Cell.

Estmar: If this option is activated, Excel will use all the cells without a formula that
involve the result of Target Cell.

Subject to restrictions: This section includes the conditions that must be

respect Solver when making adjustments to the variable cells.

2. After filling in the previous sections, press the Resolve button.

3. A window will appear showing the results calculated by Solver, click on

Accept if you want to keep this result, Cancel to revert the values
original.

· It will not always be possible to find a solution.

Example:
Copy or recover the exercise [Link].

You need to achieve profits of 550,000 pts., varying the following boxes:
.Precio Compra
Workers
Salary

And by putting the following restrictions:

Purchase Price, >=120 and <=160

Workers, >=28 and <=35
Salary, >=120,000 and <=130,000

1. Activate the Tools/Solver option.

2. In the Objective Cell section, $B$7, Benefits cell.

3. In the Target Cell Value section, click on the radio button Values of: put the
amount of, 550,000.

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 41

 4. In Changing Cells: put the boxes $D$2; $E$2; $F$2.

5. Subject to Restrictions: click Add,

[Link] Cell Reference: put the first variant cell, $D$2. Puede hacer clic
about this box.
5.2. Choose the symbol <=

5.3 A Restriction: set the value to 160 and Add Button.

Do the same to set the value greater than or equal to 120.

6. Repeat the sections of step number 5 to set the rest of the constraints.

7. Press Accept.

8. Press the Resolve button, and in the box that appears, click Accept if you want to save the
results and Cancel if you want to restore the original values.

Following the previous steps, obtain the maximum value.

Vary all the cells involved in the result of the Target Cell,
Estimate button.

Indicate the following restrictions:

. Sale Price >=200 and <=300.

. Purchase Price >= 120 and <= 160.
. Workers >= 28 and <= 30
. Salary >= 120,000 and <= 130,000
. Sales <=30,000
. Purchases >=7,000

Practice 18.2 - Exercise

Recover file [Link]

The exercise is about seeing how many weeks it will take to pay for a sound system.
count the weekly expenses and savings.

WORKBOOK OF EXERCISES AND ADVANCED EXCEL PRACTICES Page 42

 Calculations.

Total Beverages Refrescos * Precio Refresco.

Total No No Refresco * Precio No Refrescos
Sodas
Total Food Pizzas, Hamburgers * Food Price.
Expenses Total Soft Drinks + Total Non-Soft Drinks + Total
Food
Savings Weekly Assignment - Expenses
Total Semanas Hi-Fi Equipment Price / Expenses.

The previous calculations show that to pay for the Hi-Fi equipment with what is saved
currently, it will take 500 weeks.

Use the solver to get a final result between 40 and 52 weeks.

The variable boxes will correspond to the number of Soft Drinks,
No Soft Drinks and Pizzas Burgers. The restrictions are:

Soft drinks, >=3

No soft drinks, >=2
Pizzas, Hamburgers, >=2
Total Weeks, >=45 and <=52

Solution before applying Solver.

Weekly Assignment Totals

6500 Total Soft Drinks 1500
Total No 1950
Soft drinks
Consumed Quantities Total Food 2800
Soft drinks 5 Expenses 6250
No Soft Drinks 3 Savings 250
Pizzas, 4
Hamburger
s

Prices Equipment Price 125000

Hi-Fi
Price 300 Weeks 500

CUADERNO DE EJERCICIOS Y PRACTICAS EXCEL AVANZADO Page 43

 Soft drinks
Price No 650
Soft drinks
Food Price 700
Practice 18.3 - Exercise.

Recover file [Link]

Calculations.

Total Salario Salary1 + Salary2

Pension Plan Total Salary * %Pensions
Holidays Total Salary * %Vacation
Total Expenses Month Monthly Expense Summary
Total Gastos Año Car Insurance + Home Insurance
Total Ingresos Total Salary * 12
Total Expenses (Total Expenses Month * 12) + Total Expenses Year.
Benefits Total Ingresos -Total Gastos

Use the solver to find the maximum profit by establishing

the following restrictions.

Food expenses will exceed 20,000.

The electricity and phone expenses will exceed 18,000.
The pension plan will be an amount between 30,000 and 35,000.
The amount allocated monthly for vacations has to be greater than or equal to
25,000
The percentage dedicated to the pension plan must be equal to or greater than 10% of
income.
The benefits will be less than or equal to 280,000.

The values of the following boxes can be changed.

Nutrition.
.Light and phone.
Pension plan.
Vacations.

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 44

 Solution before applying Solver.

Income
Salary 1 140000 % Plan 15%
Pensions
Salary 2 95000 % Vacation 10%
Total Salaries 235000

Expenses Annual Expenses

Monthly
Mortgage 60000 Car Insurance 80000
Car 45000 Safe Floor 53000
Food 25000 Total Gastos Año 133000
Luz,Teléfono 23000
Plan of 35250
pensions
Holidays 23500
Total Gastos Mes 211750

Total Income 2820000

Total Gastos 2674000
Benefits 146000

Practice 18.4 - Exercise.

Recover file [Link]

Calculations.

Price Hour If Daily Hours > 8 it will be paid at 825 otherwise it

will pay 800.
Kg. Collected Person / If Number of People > 20 each collects 20
Hour. Kg. Who collects 30 Kg.
Kg. Collected Day Personal * Horas Diarias * Kg. Recogidos
Person/ Time.
Pickup Days.
1st Pass Kg. Forecasted 1st Pass/ Total Kg. Day.
2nd Pass Kg. Forecasted 2nd Pass / Total Kg. Day

Total 1st Pass + 2nd Pass

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 45

 Kg. Lost Day.
1st Pass Yes Days Pickup > 7 Loss of 0.8% of the
Kg collected per day. Otherwise, 0.5% of the
Kg. Collected per Day.
2nd Passed
If Collection Days > 4 Loss of 3% of the
Kg. Collected day Sino 5% of the Kg.
Collected Day.
Total Kg. Lost.
1st Pass. Kg. Lost day 1st Pass * Collection Days
1st Pass.
2nd Round.
Kg. Perdidos día 2ª Pasada * Días Recogida
2nd Pass

Income
1st Pass (Kg. Collected 1st Pass - Total Kg. Lost
1st Pass) * Price per Kg. 1st Pass.
2nd Pass (Kg. Collected 2nd Pass - Total Kg. Lost

2nd Pass) * Price per Kg. 2nd Pass.

Total 1st Pass Revenues + 2nd Pass Revenues.

Total Hours Personal * Total Días Recogida * Horas

daily
Total Expenses Total Hours * Price Per Hour
Benefits Total Income - Total Expenses.

Use the IF function in hourly price to determine if it is paid at 800 or at

825.

Use the function=IF in Kg. Collected Person/Hour.

Use the function=IF in Kg. Lost per day.

Use the solver to calculate the maximum value in benefits.

The restrictions are the following.

The personal contracted must be between 15 and 23 people.

The total number of days to collect you must be between 15 and 20.
Daily hours can be 8 or 9.

The variable boxes are.

Contracted personnel.

NOTEBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 46

 Daily hours.

Fruit Campaign

Kg. Forecasted Price per kg.

1st Pass 55000 1st Pass 65
2nd Passed 23000 2nd Pass 35

Personal Price per hour Daily Hours

17 800 8

Kg. Collected
Person/Time Kg. Recogidos Día
30 4080

Lost Kg.
Collection days DíaTotal Kg. Perdidos
1st Pass 13.4803922 32.64 440
2nd Pass 5,6372549 122.4 690
Total Días 19.1176471

Income Total Hours Total Expenses

1st Pass 3546400 2600 2080000
2nd Pass 780850
Total 4327250

Benefits 2247250

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 47

PRACTICE 19 - Logical Conditions vs IF() Function

In a company, we have a salesperson John Smith who promotes 3 products. Each month, we will see if
In addition to their base salary, they deserve a commission if the sales of those products exceed 50,000.
That is, a bonus of 1000 will be given for each sale exceeding 50,000. You could get up to 3 bonuses!
Al empleado se le paga el 10% del total de lo vendido mas un bono si hubiera.

Here you must apply the bonus condition or the IF() function to determine if you obtain it or not.

PRACTICE 20 - Logical Condition AND

In the same case as the previous practice, now for the employee to receive their bonus, in addition to
exceeding product sales of 50,000 should have brought 20 new customers. Only then will you obtain your
bonus.

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 48

PRACTICE 21 – VLOOKUP Function

On the other hand, we have an employee who will have a tax deduction based on his
salary. The higher the salary, the higher the discount percentage. Find out what that percentage would be and
how much would I receive in the end

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 49

PRACTICA 22 – Báse de Dátos, BuscárV,Ordenácion, Táblá dinámicá
1- Copy the following data into the spreadsheet:

2- Calculate the columns of Selling Price, Sale Amount, and Commission Amount using the functions that
you have learned during the course. (VLOOKUP)

3-Put all the numerical data from the sheet in the formats you deem appropriate.

4-Sort the sheet by the Seller column.

5-Create a pivot table in a new sheet that summarizes the sum of the Sales fields.
Sales Amount and Commission Amount, grouping the fields Zone and Group in the columns
Product, and in the rows, the field Year.

6-Create another pivot table like the previous one, in a new one, but summarizing the averages.

7-Create a pivot table, on the same sheet, where the MAX of Sales is summarized.
grouping in rows the Seller field and in columns, the Product field.

WORKBOOK OF EXERCISES AND ADVANCED EXCEL PRACTICES Page 50

PRACTICE 23 - Management of Scenarios

Scenario administrator.

The scenarios are used to enter different data within the same range of the sheet. Each scenario
it will be identified by a name.

Create a scenario.

1. Select the range of boxes that the stage will occupy. If the stage you want to create
it occupies non-adjacent ranges, select these different ranges while holding the key of
CONTROL pressed.

[Link] option Tools/Scenario Manager, click on the Add button.

3. In the Scenario Name section, assign a name to the scenario you want to create.
press the Accept button.

Now a dialog box will appear with as many edit boxes as there are checkboxes.
selected for the stage.

4. Among the values for each box and Add.

5. Repeat steps 3 and 4 for each scenario.

[Link] sobre Aceptar cuando haya entrado el últmo escenario. Observe que en la
The Scenario Manager window displays a list with the name of each scenario.
created.

Show a scene.

1. Active option in the Tools menu bar / Scenario Manager.

[Link] la ventana del Administrador de escenarios, seleccione el escenario y pulse sobre el

Show button.

Delete a scenario.

ADVANCED EXCEL EXERCISES AND PRACTICE NOTEBOOK Page 51

 [Link] option of the menu bar Tools/Scenario Manager.

2. Select the scenario to delete, press the Delete key.

Example. (Corresponding to the exercise [Link])

[Link] the range of boxes from B2 to E3.

[Link] option Tools/Admin. Scenarios.

3. Click on the Add button.

4. Put the name Esc1. Accept.

5. Among the following values:

B2 = 500 C2 = 450 D2= 600 E2= 320

B3 = 50 C3 = 50 D3= 65 E3 = 65.

6. Click on the Add button.

7. Enter the name Esc2 and click on Accept.

8. Among the following values:

B2 = 700 C2 = 850 D2= 500 E2= 700

B3 = 60 C3 = 60 D3= 45 E3 = 65.

9. Press the Accept button.

Note that a list appears with the names of the created scenarios.

10. Select a scenario and press Show.

Practice 23.1 - Exercise.

Retrieve the file [Link].

Create the following scenarios:

Esc1.
Quantity 600 650 550 400
Price 50 60 70 80
Unit

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 52

 Esc2.
Quantity 500 450 300 350
Price 60 70 80 90
Unit

Calculations.

Total Price * Quantity

Expenses Total * 20%
Total Benefits - Expenses

Show the different scenarios.

1990 1992 1994 1995

Quantity 500 450 300 350
Price 60 70 80 90
Unit
Total 30000 31500 24000 31500
Expenses 6000 6300 4800 6300
Benefits 24000 25200 19200 25200

Gastos =Total*20%

Practice 23.2 - Exercise.

Retrieve the file [Link].

A salesperson has three price lists: normal price, regular customer price, and another for promotions.

Calculations.

Scenarios. Create three scenarios to place in each of them

they a price list; Regular Price, Customer
Habitual, Offer. See price list table in the
next page, it's the one you need to use for
put values in the different scenarios.
Product Locate the product name in the List table
of Prices according to the value entered in the box
Code.
Cantdad Between a Quantity.

ADVANCED EXCEL EXERCISE AND PRACTICE NOTEBOOK Page 53

 Price Locate the price of the product in the table List of
Unit Prices according to the value entered in the box
Code.
Total Quantity * Unit Price.
Total a Sum of the Total column.
Pay.

Name the range that the price list table occupies.

Use the =VLOOKUP function to find values for the columns.

Producto y Precio Unidad.

Use the =SUM function to calculate the Total Amount to Pay cell.

Price lists

CódigoProducto PrecioCliente Offer

Normal Habitual
Flour P. 65 60 50
2Flour C. 40 35 30
3Butter 120 115 100
a
4Milk E. 56 45 40
5Milk 67 60 45
SD.
6Milk D. 80 65 50
7Minute C. 75 70 65
8MinuteF. 75 70 60

Delivery note from a regular customer.

CódigProducto Amount Price Total

o Unit
1FlourP. 12 60 720
3Butter 5 115 575
a
5Milk 7 60 420
SD.
7Minutes C. 10 70 700

Total a 2415
Pay

ADVANCED EXCEL EXERCISE AND PRACTICE NOTEBOOK Page 54

PRACTICE 24 – Conditional and Search Functions

Conditional functions.

Function prototypes are used to perform one action or another depending on the result of evaluating a
condition. They always consist of a condition argument and depending on whether this condition is
whether or not it is fulfilled, one action or another will be executed.

Functions.

Conditional Functions.

=IF(Condition; value or expression1; value or expression2).

This function evaluates a condition. If the condition is true, it executes value or expression1, otherwise (the condition is
false), the value or expression2 is executed.

=COUNTIF(Range; "Condition")

This function counts how many range cells meet the condition.

=SUMIF(range to evaluate, "condition", range to sum)

Sum the cells in the Range to Sum that meet the Condition in their corresponding Range to evaluate.

Note: Keep in mind that both the COUNTIF and SUMIF functions require a condition to be specified.
write in quotes or use a box as such.

Example of the function =If.

A 5% discount is applied to the total (B1) in the discount box (B2) if it exceeds 100,000.
pesetas, otherwise, no discount applies.

ADVANCED EXCEL EXERCISES AND PRACTICE NOTEBOOK Page 55

 Copy the exercise, put a total less than 100,000 in B1, you will see that in B2 the discount that
it appears to be 0%, since the condition is not met.
B1 > 100,000

Change the total to an amount greater than 100,000. You will see that the discount for B2 is 5%.

Example of function =COUNTIF

In cell B10, the function =[Link] has been used to find out the number of
approved students

Count how many cells in the range B2:B9 meet the condition of being >=5.

Example of SUMIF function

In cell B13, the SUMIF function has been used to calculate the total sales of a
seller (Juan in the example). Note that the range to evaluate is B2:B12 (Column of
Sellers). The condition is B14 (Cell where the seller's name is entered) and the
the range to be summed is C2:C14 (Sales column). The SUMIF function will sum in this
In the case of Juan's sales, to add the sales from the other sellers, it will only be necessary
put your name in the box B14.

The function of B13 could be interpreted in the following way,

SUM the cells in the range C2:C12 whose corresponding cells in the range B2:B12 are
equal to the value of B14.

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 56

 This function can also be written from the assistant, to do the same as in
the example [Link].

1. Activate option Tools/Assistant/Conditional Sum.

A window appears, asking for the range of boxes where the values are.
use $A$3:$C$12. Press Next.

3. This window asks for the name of the box to add, that is the title, select Value
Sales.

Further down, in the Column section, you must choose the title of the column where you want.
set the condition, choose Seller.
In the section Is:, select the sign =, and in This Value, the name of the seller.
whoever wants to count the total sales, in this case, Juan. Click on the button
Add condition and click Next.

In the third window, you must choose between copying only the formula or the formula and
the values, select this last one and Next.

5. In the next window, you need to put a box where the title will appear, in this
case Juan, put B14.

6. And in the last window, put cell A15, which will be the cell where there will be
added values.

Check that the result is the same, but you save typing the data.

ADVANCED EXCEL WORKBOOK OF EXERCISES AND PRACTICES Page 57

 Highlight conditional data.

This option allows you to choose the format and color of the data based on a condition.

1. Select the Sales Value column.

[Link] option Format/Conditional format.

In the first selection box, choose Cell Value, in the second, select
Greater than, and in the third, put 20000.

3. Press the Format button, select the red color and the Italic font style, press
Accept.

4. If you want to add another condition, press the Add >> button, and follow the same step
Press Accept.

Check how all the boxes that exceed the value of 20000 are in red and in
italic.

Functions.

Some search functions.

=VLOOKUP(Value;Range or Range Name;Column Number);

Look in the first column of the Range or range name for the Value, and display the content of its
corresponding in Column to the right.

=CHOOSE(Index; Item1, Item2,...,Item n)

Show the value of the Element that occupies the Index position in the list.

=INDEX(Range or Name of Range; Row; Column)

Show the element of the Range or Name of the Range located at the row, column position of the table.

EXCEL ADVANCED EXERCISES AND PRACTICE NOTEBOOK Page 58

 Example of function =VLOOKUP

En la casilla B9 se ha utlizado la función BUSCARV para encontrar el nombre del

product quetene code 2.

SEARCH for the value of cell B8 in the first column of the Range A2:C5 and display it
corresponding value of the second column of this Range.

Change the value of the code in cell B8 to see how it automatically changes the
nombre del producto y el precio.

Rank Name.

Sometimes, to clarify or simplify operations in a spreadsheet, it is necessary to name

to a cell or range of cells. The VLOOKUP function, for example, has as an argument a
range of cells that contains the table where the values need to be searched. So as not to have to
Remember the references of this table, you can name the range it occupies within.
the sheet, in this way, instead of using range references, you will be able to use its name.

To name a range.

Select the range.

[Link] option from the menu bar Insert/ Name/ Define. The following appears
dialog box.

WORKBOOK OF EXERCISES AND ADVANCED EXCEL PRACTICES Page 59

 In the Name box, type the name for the range.

4. Press the Accept button.

· For example, name the range that includes the table from the example, which is used in the
VLOOKUP function.

Select the range A2:C5.

[Link] option Insert/ Name / Define.

In the Names editing box in the book, type Fruits.

Example of function =VLOOKUP

Observe how the VLOOKUP functions in B9 and B11 look now.

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 60

 Rank names also allow you to quickly navigate to them. Follow the steps of the
next example.

1. Place the cursor in cell A30 (for example).

2. Deploy the list on the left side of the formula bar, which indicates the box.
Stop positioning the cursor. If the formula bar is not visible, activate it with the option
View/ Formula bar. A list similar to this will be displayed.

3. Click the mouse on the Fruits element and you will see how it subsequently remains
selected this range.

· Another quick way to navigate through the sheet is to press the F5 key. Give it a try.

Follow the steps below to see an example of how to use the mouse to enter a named range.
in functions that require it.

Previously I deleted the content of cell B9.

1. Select the box where the function should go. Select B9.

[Link] the name of the function. =VLOOKUP(

3. Click on the box where the value is. Click on box B8.

[Link] a semicolon to separate the arguments.

5. Click on the dropdown list in the formula bar and select item
Fruits.

NOTEBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 61

 [Link] a semicolon to separate the arguments.

[Link] the shift. Type 2.

8. Close the parenthesis and press ENTER.

· Observation. Please note that if the values in the first column of a table are not
sorted in the VLOOKUP function you will need to set a fourth parameter with a value of 0 to
indicate it.

=VLOOKUP(value, table, column, 0)

Example of function =CHOOSE.

En la casilla B7 se mostrará el valor Manzanas.

Choose the element in the position indicated in B6 of the

list B1, B2, B3, B4.

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 62

 Example of function =INDEX

The INDEX function in cell B10 locates the price to be applied according to the values of B8 (row).
y B9(Column). The range B3:D5 has previously been named Prices. The price
located will be 52.

Locate in the Prices table (B3:D5) the element that is in the row indicated by the value of
B8 and the column indicated by the value of B9.

Practice 24.1 - Exercise.

Recupere el ejercicio [Link]. Calcule las casillas en blanco.

Calculations.

Pesetas Price * Quantity

Discount If Pesetas > 250,000 apply 10% otherwise
apply 5%
Total Pesetas-(Pesetas * Discount)
Tax Base Sum of the Total column.
VAT 16% Apply 16% on Taxable Base.
Amount Tax Base + VAT
Tipo Cliente Write 1, 2 or 3
Customer Discount Select the discounts as appropriate
Normal Customer, Regular Customer or
Relative
Total Invoice Amount - (Amount * Discount).
Productos Desc. Count the discounted products

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 63

 10% of 10%.

Use the =IF function in the Discount column.

Use the =CHOOSE function in the Customer Desc. box, the index is the value
from the Customer Type box and the values are from the Customer table
Habitual, Normal Client, Relative.

Use the =SUM function with the range in the Totals column to calculate
the Taxable Base.

Use the =[Link] function in the cell Desc. 10%

Formats.

Apply classic autoformat 2 to the invoice and to the customers table.

Put the columns Price, Quantity, and Pesetas in numeric format with
separator.
The Discount column and the Client Discount box have % format.
The Total column in Monetary format.

Articles Price Quantity Pesetas Discount Total

Television 120,000 7 840.000 10% 756,000 pts
Washing machine 80,000 4 320,000 10% 288,000 points
Microwave 33.000 8 264,000 10% 237,600 Points
Toaster 12.000 12 144.000 5% 136.800 Pts
Refrigerator 140,000 9 1,260,000 10% 1,134,000 Points
Blender 7.500 12 90,000 5% 85.500 Pts
Dryer 4.500 23 103.500 5% 98.325 Pts

Base I.V.A. Amount Tipo Cliente Desc Cliente. Total Factura

Taxable 16%
2.736.225 437.796 3.174.021 2 5% 3,015,320 Points

Normal Client 0% Product Desc. 10% 4

Client 5%
Habitual
Relatives 10%

Practice 24.2 - Exercise.

Retrieve the file [Link].

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 64

 Calculations.

Pesetas. Quantity * Unit Price

Discount. (If Pesetas > 20,000 apply Discount of
box %Discount if not 0) * Pesetas
Total. Pesetas - Descuento
Base Suma de los valores de la columnaTotal.
Taxable
Total VAT Taxable Base * VAT 16%
Amount Taxable Base + VAT
Tipo Desc. Type 1 or 2
Invoice Description Apply discount according to whether it is Normal (1) or
Special(2).
Total Factura Amount - (Amount * Invoice Discount).

In the Discount column, apply the function =IF, the % of discount is

in the discount box and a reference has to be made to this
box.

In the Invoice Desc. box, use the function =CHOOSE, the index is the value
from the box Type Desc. and the values are from the boxes Desc. Normal and
Special desc.

Use the =SUM function with the values from the Total column to calculate.
the Tax Base

Formats. However you want.

ADVANCED EXCEL WORKBOOK OF EXERCISES AND PRACTICES Page 65

 %Discount 7%
VAT 16%
7Desc. 5%

Normal
Desc. 15%
Special
Quantity DescripcióPrecio Pesetas Total Discount
n U.
13 Novels 795 10335 0 10335
15 booklets 395 5925 0 5925
4Encyclopedia 75000 300000 21000 279000
as
24Magazines 215 5160 0 5160
7Dictionary 3500 24500 1715 22785
s
Base Total I.V.A Import Tipo Desc. [Link] Total
Taxable e you Invoice
323205 51712,8374917 1 0.05 356171.91
8

Function =If(;;), with multiple conditions.

Sometimes we find ourselves needing to use more than one condition within the same
formula, for this we will use the IF function, with more than one condition.

Example.

Suppose you want to apply a discount on the products of an invoice to a client.

If you are a regular customer:

If you purchase a quantity of 1000 units or more, a 6% discount will apply.
If you purchase an amount less than 1000 units, apply a 5% discount.

If you are not a regular customer:

If you purchase a quantity of 1000 units or more, apply a 4% discount.
If you purchase an amount less than 1000 units, apply a 3% discount.

Remember the format of the IF function: =IF(Condition;True;False)

Condition: The condition to be controlled.
True: If the condition is true, execute this action.
False: If the condition is not met, perform this action.

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 66

 To calculate the discount of cell D5

B$2=1 (Regular customer)

B$2<>1 (Non-regular customer)
B5>=1000 (Quantity is greater than or equal to 1000 units)
B5<1000 (Quantity is less than 1000 units)

First possibility:
IF(B$2=1, IF(B5>=1000, 6%, 5%), IF(B5>=1000, 4%, 3%))

Second possibility:
IF(AND(B$2=1,B5>=1000),6%,IF(AND(B$2=1,B5<1000),5%,IF(AND(B$2<>1,B10>=1000),4%,3%)))

The function Y(condition1;condition2;..;Condition N) means that all must be met

conditions that close the parentheses. Note how the conditions would be applied:

Calculations.

Discount One of the two possibilities of the function

YES
Total. (quantity*price)-
(quantity*price*discount)
Box B2 Write 1 for regular customer and any
another thing for an irregular customer.

A possible solution to this example:

Change the quantities and the customer type box and you will see how the %
the discount varies.

ADVANCED EXCEL WORKBOOK OF EXERCISES AND PRACTICES Page 67

 Practice 24.3 - Exercise.

Retrieve the file COND3. XLS.

Calculations.

Pesetas. Price * Quantity.

% If Pesetas < 100,000 Discount of 3%
Discount. If Pesetas > 100,000 and Pesetas <= 300,000 Discount
5% off
If Pesetas > 300,000 and Pesetas <= 1,000,000
7% discount
If Pesetas > 1,000,000 Discount of 10%.
Total. Pesetas - (Pesetas * % Discount).
Base Sum of the values in the columnTotal.
Taxable.
VAT 16%. 16% of Taxable Base.
Importe. Tax Base + VAT 16%
Client Type. Choose 1, 2, or 3.
Desc. Apply Discount according to the value of the Type box
Client. client
Total Amount - (Amount * Client Discount).
Invoice

En la columna de %Descuento utilice las funciones =Si =Y para calcular el

corresponding discount according to the discount table values.

In the Customer Desc. box, use the function =CHOOSE, with the index the value
from the Customer Type box and lists the boxes Normal Customer, Customer
Habitual and Relatives separated by semicolon.

In the Taxable Base box, use the function =SUM with the range
corresponding to the Total column.

Articles Price Quantity Pesetas Discount Total

Television 120000 7 840000 0.07 781200
Washing machine 80000 4 320000 0.07 297600
Microwave 33000 8 264000 0.05 250800
Toaster 12000 12 144,000 0.05 136800
Refrigerator 140000 9 1260000 0.1 1134000

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 68

 Blender 7500 12 90000 0.03 87300
Hair dryer 4500 23 103500 0.05 98325
Tax Base I.V.A. 16%Importe Tipo Cliente Client Description Total Factura

2786025 445764 3231789 2 0.05 3070199.55

Normal Client 0%
Frequent Customer 5%
Relatives 10%

Practice 24.4 - Exercise.

Retrieve the file [Link].

Calculations.

Code Choose a code from the Prices Table. Use the

from the example to be able to verify the
results.
Description. Locate the product name in the table
according to the values of the box Code.
Amount. Between a quantity. Use the ones from the example for
check the results.
P.v.p. Locate the product price according to the value
entered in the Code column
% Discount. Locate the % Discount of the product according to the
value entered in the Code column.
Total Quantity *Retail Price - (Quantity *Retail Price *
%Discount).
Type Desc. Between the value of the row where it is located
descuento según Tabla Descuentos.
Office Between the value of the corresponding column
where the position is according to Table
Discounts.
Base Sum of the values in the Total column.
Taxable
Discount Locate the discount according to the values of the
Boxes Type Desc. and Trade
Total Desc. Base Imponible * Descuento
Amount Base Imponible -Total Desc.
I.v.a. Amount * 16%
Total Invoice Amount + VAT

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 69

 Give a name to the price table.

Give a name to the discount table.

In the Description column, use the function =VLOOKUP(cell col.

Código;nombre de la tabla precios; 2).

In the column P.v.p. use the function =VLOOKUP with

displacement 3.

En la columna %Descuento utilice la función =BUSCARV con

displacement 4.

In the Tax Base cell, use the function =SUM.

In the discount box, use the function =INDEX(TableName

discounts; box Type Desc.; box Code Office.

Price table

Code Descripción Precio Desc.

Prod. Uni. Uni.
1 Nut 10 2%
2 Key 300 3%
3 Hammer 450 3%
4 Female 40 2%
5 Hinge 120 2%
6 Unscrewing 360 3%
r
7 Wrench 570 5%
8 Plug 100 2%
9 Cable No. 7 200 3%
10 Cable No. 12 240 3%
Discount Table.

Electrician Bricklayer Mechanic

a he o
Desc. 5% 3% 4%
1
Desc. 10% 7% 8%
2
Desc. 15% 10% 12%
3

EXCEL ADVANCED EXERCISE AND PRACTICE NOTEBOOK Page 70

 Invoice.
CódigoDescripci Cantidad P.v.p. %Desc Total
on .
4Female 10 40 0.02 392
5Hinge 74 120 0.02 8702.4
Screw 8 10 0.02 78.4
8 Plug 69 100 0.02 6762
3Hammer 43 450 0.03 18769.5
2Key 12 300 0.03 3492

Tipo Desc. 2
Code. 3
Office

Base Discount Total Importe Ivy Total

Imp. o desc. 16% Invoice
38196.3 8% 3055,704 35140,5 5622,4 40763,0914
9 9

Practice 24.5 - Exercise.

Recover the file [Link].

Calculations.

Code. Between a code of the Products table.

Product. Locate the name of the product from the table
Products according to the value entered in the box
code
Quantity Between a quantity.
Total. Quantity * Purchase / Sale Price,
located in the Products table according to the value
from the box Code.

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 71

 Give a name to the range occupied by the product table.

In the Product column, use the function =VLOOKUP(cell of col.

Code; name of the Products table; offset 2).

In the Total column, use the function =VLOOKUP(cell in column Code;

name of the Products Table; display 3 if it is purchases and 4 if it is sales
and multiply by the Quantity.

Tabla de Productos.

CódigProduct Precio Price

o o purchase Venda
1Table 33000 38900
2 Wardrobe 67000 86000
3 Chair 7800 9000
4 Sofa 55000 63000
5 Writers 9900 12000
o
6 Lamps 6500 7500
a
7 Comfortable 25000 33000
8 Armchair 43000 51000
9 Village 14000 16000
a
10 Library 87000 99000
11 Shelf 6400 8500
ía
12 Frame 5000 6200
13 Received 36000 42000
or
14 Rincone 19000 23000
ra

EXCEL ADVANCED EXERCISES AND PRACTICE NOTEBOOK Page 72

Shopping Table. Sales Table.

CódigProduct Cantida Total CódigProduct Cantida Total

o o d o o d
2Wardrobe 1 67000 1 Table 2 77800
5 Writers 2 19800 3Chair 6 54000
o
7Comfortable 2 50000 5Desk 4 48000
o
8 Armchair 2 86000 7Dresser 3 99000
12Frame 8 40000 14Rincone 5 11500
ra 0
10Library 1 87000 2Wardrobe 3 25800
0
4Sofa 1 55000 9Tumbon 8 12800
9Tumbon 2 28000 a 0

a
11Shelf 5 32000
ía
13Received 1 36000
or

Practice 24.6 - Exercise.

Retrieve the file [Link].

Calculations.

Name the Sheets. Hoja1= Personal, Hoja2 =

Salaries and Payments, Sheet3 = Tables.
Personal Sheet. Seniority Current Year - Data Entry Year
Contract
Hoja Sueldos y Pagas. Sueldo Locate the Base Salary according to the
Base. value of the Operator box.
Payroll and Payments. Total Locate the price of an hour
Overtime. extra according to the category * Hours
Extra.
Payroll and Payments. Locate the bonus according to the
Extra Salary Category. category.
Payroll and Payments. Pesetas (If Column Seniority > 5 2000

EXCEL ADVANCED EXERCISES AND PRACTICE NOTEBOOK Page 73

 Antiquity. pesetas or 1000 Pesetas) *
Antiquity
Total Base Salary + Total Overtime Hours
+ Sobresueldo Categoría +
Old Pesetas.

Name the ranks that are held by the Base Salary tables and
Increments respectively.

To calculate the seniority column of Personnel, use the functions

=NOW() and =YEAR().=YEAR(NOW()) - YEAR(Contract Date Cell).

Use the function =VLOOKUP to calculate the Base Salary column

sheet Salaries and wages. For the input box, use the values of the
column Worker of Personal Sheet, the range will be the name that is given to it
it has given to the Base Salary table, the displacement is 2. Remember to put
0 in the last parameter in case the Salary Base table does not exist
sorted by the elements of the first column.

Use the function =VLOOKUP to calculate the Total Overtime Hours. As

input box use the values from the Category column of the Sheet
Personally, the range will be the name given to the increments table.
The displacement will be 2. Do not forget to multiply by the number of hours.
extra.

Use the same =VLOOKUP as in the previous section but with

displacement 3 to calculate the Overpayment column category.

To calculate the column Old Pesetas, use the function =IF for
determine what value the years in the column should be multiplied by
seniority of Personal Sheet. If (Column Seniority of Personal Sheet
>=5; cell >=5 from TableSheet else; cell <5 from TableSheet
multiplied by the seniority column of the Personal Sheet.

Personal Sheet.

Name 1st. Operario Categorí EdadFecha Antiquity

Surname a Contract of
Ramón Rodríguez Carpenter B 34 3-ene-89 10
o
Pear Pérez Electrician A 29 20-jul-93 6
a

EXCEL ADVANCED EXERCISES AND PRACTICE NOTEBOOK Page 74

 Marco Menendez Electrician B 51 7-jul-85 14
Gabriel García Welder C 46 November 15 17
82
Paco Portal Duct A 35 12-Mar 9
r 90
Lluís López Accountant B 24 1-feb-94 5
Lucas Lozano Secretary C 21 1-feb-96 3
o
Xavier Jiménez Assistant D 19 1-nov-95 4
Of.
Payroll and Payments.

Hours Total Extra salary Pesetas

Nombre 1 ApellidoSueldo BaseExtra Overtime Category Antiquity Total

Ramón Rodríguez 87000 10 18000 15000 20000 140000

Peer Pérez 90000 12 24000 20000 12000 146000
Marco Menendez 87000 8 14400 15000 28000 144400
Gabriel García 77000 14 21000 10000 34000 142000
Paco Portaz 100000 10 20000 20000 18000 158000
Lluís López 90000 6 10800 15000 10000 125800
Lucas Lozano 85000 6 9000 10000 3000 107000
Xavier Jiménez 63000 9 9000 5000 4000 81000

Table Sheet.

Salary Increments
Base
Worker Base Salary Category Overtime Inc base salary
Assistant 63000 A 2000 20000
Of.
Accountant 90000 B 1800 15000
Conductor 100000 C 1500 10000
Electrician 90000 D 1000 5000
Carpenter 87000
Lampist 87000
Secretary 85000
Welder 77000

Antiquity
greater than or equal
<5 to 5
2000 1000

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 75

Assistant for functions.

The function assistant is activated by pressing the button. of the standard bar or
activating the option in the Insert/Function menu bar. Retrieve the file [Link] and follow the
following examples. First, recalculate all the boxes, except for the corresponding column.
discount and cell E12.

Example.

1. Select the first box E3 corresponding to the Discount column and

Click on the assistant button for functions.

2. Select the Logic category, and the If function. Press the Next button.

3. In the Test-Logic edit box, check the box that must be fulfilled.
condition, D3>250000.

4. If True, set 10%, and If False, 5%. Press Finish.

Calculation of the discount column according to the customer.

Example.

1. Select cell E12 and press the function wizard button.

2. Select the Search and reference category, and the Choose function. Press
about Next.

3. In the Index editing box: place cell D12, which is the box where
hará la selección deltpo de cliente.

4. In the text box Value1: place the box where the first time is.
client, the B15, in Value2: B16, and in Value3: B17, press the Finish button.

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 76

Exercise 3.9 – Macros

To create a macro, use the command Tools > Macro > Record Macro (2003 versions)
Excel and earlier), in version 2007 and later, we go to the DEVELOPERS tab/RECORD
MACRO with which a dialog box will appear where you can give the macro a name as well as
indicate under which key combinations it should be executed.

1. Write your name in cell A1 and press ENTER

2. We return to cell A1, because when ENTER is pressed, it moves down to the next one.
cell
3. Click on the RECORD MACRO icon.
A dialog box appears which will allow you to give a name to the macro and what it will be.
shortcut method to execute it. The shortcut refers to which letter will activate it.
macro. It will be activated with the CONTROL key.
5. Where it says NAME OF THE MACRO, the name that the macro will have already appears. In this case
MACRO1. If you want to change it, just write over it.
6. In the SHORT METHOD option, it shows that it will be activated with the Control (CTRL) key + the letter
that you indicate, place the letter with which the macro will respond; for example, put the letter a.
7. Click on the ACCEPT button. Windows will start recording all the steps in Macro1 and the
the blue wheel button will change shape to now be a blue square. It is called stop
recording. We will use it when we finish indicating the steps to stop the recording.
This icon can be found in the bottom left corner in versions 2007 and later.
8. Change the font type in the FONT button.
9. Change the font size.
10. Press the bold button.
11. Change the color of the text.
12. Press the STOP RECORDING button (from the Visual Basic toolbar in the
versions 2003 and earlier and in the icon at the bottom left in versions 2007 and
posteriors).

Now let's see. The operation.

1. Write another text in cell b4 and press ENTER. Then return to that cell.
2. Press the CONTROL+A keys and Windows will execute all the recorded steps on the cell.
A1.

PRACTICE 25 – Macros
Develop the following macros:

Record a macro that activates with Control+b and allows opening a file.
2. Grabar una macro que se actve con Control+c y que permita insertar un texto artstco
(WordArt)

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 77

 3. Record a macro that activates with Control+t and allows positioning in cell F15
4. Record a macro that is activated with Control+o and that allows creating borders in the cell.
selected

Exercise 3.10 - Macro Editing

Create a new book
2. Place the cursor in A5
3. Press the RECORD MACRO button
4. In the shortcut method, we will put the letter.
5. Once it is recording, move the cursor to cell A1 and there write your name. That ’s all.
6. Stop the recording.
7. Now let's edit the saved macro by pressing ALT+F11
8. Double click on Modules in the Project panel.
9. Click on Module1 and we will see the code of the macro
10. Replace the two lines not marked with ' (asterisk) with the following code:

Select Range("A1")
ActveCell.FormulaR1C1 = "Nombre"
Select cell B1
ActiveCell.FormulaR1C1 = "Address"
Select cell C1
Phone
Select Range('D1')
ActiveCell.FormulaR1C1 = "Status"
Select cell E1
School

11. Exit the editor and return to Excel

12. Run the macro with CTRL+r

PRACTICE 26 - Macros and code

Create the following macros:

1. Generate a Macro that writes a name in a cell and makes it bold and observes the
code
2. Generate a macro that writes a name in a cell and centers it and observe the code

ADVANCED EXCEL EXERCISE AND PRACTICE NOTEBOOK Page 78

 3. Generate a macro that writes a name in a cell and changes the font size to 20.
point and observe the code

Exercise 3.12 - Assign a macro to a button.

About the same exercise 3.10, go to the PROGRAMMER tab in Excel 2007 and later.
Click on the button INSERT
orm controls
Draw utton.
the button in some area of your spreadsheet.
Then a dialog box will open asking which macro to assign. Let's say Macro2.
we press ACCEPT.
Ready. The macro has been assigned to this button.
Delete the entire row 1, and then click the button you just created. You will see that the macro runs.
again.

For instructions in Excel 2003 and earlier, refer to your Study Guide pages 110 to 112.

Exercise 3.13 - Forms

Create a new workbook
2. You draw 3 checkboxes using the INSERT/CONTROLS button
FORM CHECKBOX

3. Once you finish drawing the controls, go back to the first one, and click it.
right mouse button.

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 79

 4. Select the CONTROL FORMAT option
5. A dialog box will appear with several tabs, go to the tab that says CONTROL

6. Link the control to an adjacent cell so that it shows the result. In the example, we see
what does $D$6 say
7. Click ACCEPT.
8. Do the same with the remaining 2 controls.
9. Now, click on a control to mark it, and you will see that it will be placed in the specified cell.
TRUE (if marked) or FALSE (if left blank).

PRACTICE 27 - Variable Data Table.

The following are a series of exercises and practices with instructions in Excel 2003 and
previous. In the 2007 versions and later, the tables or series tables were renamed as
data tables.

Tables of a single variable.

A one-variable table consists of a column or row of values and one or more formulas to
apply to these values. The tables allow for quick updates of the results that

EXCEL ADVANCED EXERCISES AND PRACTICE NOTEBOOK Page 80

 they obtain from the values, simply by changing the formula that needs to be applied,
the obtaining of results is immediate.

Before creating a table, you will learn how to quickly fill a range with series of
values.

Fill ranges with series of values.

1. In the first cell of the series of values, enter the initial value, select the range of
column or row you want to fill (including the initial value cell).

[Link] option Edit/fill/series. The following dialog box appears.

Series En: Choose to fill rows or columns.

Type: Progression step.

Increase: The interval between two consecutive values in the series.

Time unit: A way to increase chronological values.

Limit: Maximum value of the progression.

Example.

1. Place the cursor in cell A1, between a 0.

2. Select the range A1:A10

3. Active option from the Edit menu/ Fill/ series.

EXCEL ADVANCED EXERCISES AND PRACTICES WORKBOOK Page 81

 4. In increase, put 5.

5. Press Accept.

This action can also be done with the mouse.

Example.

1. Place the cursor in cell A1, between a 0.

2. Place the cursor in cell A2, between a 5.

3. Select boxes A1 and A2.

4. Place the pointer over the fill box and drag to A10.

Do not close the document, it will be used for the next example.

Build a table of a variable.

1. Select the box on the right of the value table if they are in a column.
The cell in the bottom row of the value table if they are in a row.

[Link] the formula; the formula must have a reference to the cell where the first
value of the table.

3. Select the range that includes the column or row of values, and the column or row with the
formulas.

[Link] option of the menu bar Data/Table.

5. A dialog box appears. Enter the reference of the cell in the column or
value row that has been used in the editing box Variable cell row (if the values are
in a row) or column (if the values are in a column).

6. Press Accept.

Example.

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 82

 [Link] the cursor over cell B1, type =A1*5.

2. Place the cursor over cell C1, type =A1*13.

3. Select the range of the Table (A1:C10)

[Link] option Data/Table.

In the editing box Variable Cell Column, put A1.

6. Click on Accept.

Change the formula in cell B1, put =A1*23. Notice how new ones are automatically generated.
results from this formula.

Practice 27. 1 - Exercise.

Retrieve the file [Link].

Fill a range with values from 1 to 9.

With tables, calculate (each formula in a column).

Divide the values by 3,

Find the remainder of dividing the values by 3. Function =MOD(Dividend;
Divisor), in this case, put 3 as divisor.
Find the factorial of the values. Function =FACT(Cell)
Find the square root of each of the values. Function =SQRT(Cell).

ValoresDivisión Residue FactorialRoot

Square
1 0 1 1 1
2 0 2 2 1.414213562
3 1 0 6 1.732050808
4 1 1 24 2
5 1 2 120 2,236067977
6 2 0 720 2.449489743
7 2 1 5040 2,645751311
8 2 2 40320 2,828427125
9 3 0 362880 3

WORKBOOK OF EXERCISES AND ADVANCED EXCEL PRACTICES Page 83

 Practice 27.2 - Exercise.

Retrieve the file [Link].

Convert the values to radians. Function =RADIANS.

Find the cosine of the angles. Remember that the function =COS uses the
angles expressed in radians, therefore as variable column cell,
You will need to use the first row of the angles column in radians.

Calculate the sine of the angles. Function =SINE

Make a line graph with the values of the last three columns.

Angles in Angles in Cosine Breast

Degrees Radians
15 ["0.26179939","0.9659258","0.2588190"]
3 5
30 0,52359878 0,8660254 0,5000000
0 0
45 ["0.78539816","0.7071067","0.7071067"]
8 8
60 1,04719755 0,5000000 0,8660254
0 0
75 1.30899694 0.2588190 0.9659258
5 3
90 1,57079633 0,0000000 1,0000000
0 0
105 1.83259571 - 0.9659258
0.2588190 3
5
120 2.09439510 0.8660254
0.5000000 0
0
135 2,35619449 - 0.7071067
0.7071067 8
8
150 2.61799388 - 0.5000000
0.8660254 0
0
165 2.87979327 0.2588190

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 84

 0.9659258 5
3
180 3.14159265 0.0000000
1,000,000 0
0
195 3.40339204 - -
0.9659258 0.2588190
3 5
210 3.66519143 - -
0,8660254 0,5000000
0 0

Functions.

Some financial functions.

=NPER(rate; payment; value)

Calculate the number of payments that need to be made to amortize an investment or loan.

Rate = % interest.
Payment = Fixed amount that is paid.
Value = Value to be amortized.

If the payment is specified as monthly, the Rate value must be divided by 12. The NPER function will return the
period in months. This clarification is valid for the rest of the functions explained in this section.

=PMT(rate, period, num_periods, value)

Calculate the interest paid over a certain period ([Link]) of a loan or of a

investment.

Rate = % interest.
Period = But which period (which month or which year) is used to calculate the amount to be returned (or charged if
it is an investment).
Nú[Link] = Cantdad total de años o meses en la cual se ha devolver el dinero.
Value = Value to be amortized.

If monthly payments are calculated, the Number of Periods must be specified in months, this means, for example,
For 2 years, the Number of Periods will be 24 (2*12 months that a year has). Don't forget to also divide the rate.
by 12 in the case where I calculate periods in months.

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 85

=PMT(rate, nper, pv, [fv], [type])

Calculate the amortized capital in a given period (Period) of a loan or investment.

=PMT(InterestRate%;NumberOfPeriods;Value)

Calculate the capital to be paid per period, to repay an investment. It is the sum of PAGOINT and
PAGOPRIN.

=PV(rate, nper, payment)

Calculate the present value of a capital at a fixed interest rate considering the number of periods. The value you have.
currently a value if during [Link], a Payment amount is paid (or invested) at an interest
Rate.

Tasa = % Interés
Nú[Link] = Número total de periodos en una anualidad.
Payment = Payment made in each period.

PV(Rate;Number of Periods;Payment)

Calculate the future value of a capital at a fixed interest observed in [Link]. The capital that is
You will obtain if during [Link], a Payment amount is paid (or invested) at an interest Rate.

Tasa = % Interés
NumPeriods = Total number of periods in an annuity.
Pago = Pago que se realiza en cada periodo.

Practice 27.3 - Exercise.

Retrieve the file [Link].

Make a table that calculates what will need to be paid each month in interest ([Link]), of
amortization ([Link]), and total (Interest + Amortization), if a loan of
1,500,000 points at an interest rate of 12% with a repayment period of 1.5 years. Note that it

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 86

 monthly calculations are requested, therefore the rate will have to be divided by 12 and in [Link]
you will have to enter the total number of months.

It is recommended to use the function assistant. For example, to calculate the payment of
interests the assistant will display the following dialog box that will facilitate the introduction of the
parameters.

Capital 1,500,000

Interest 12%
Period 1.5
Interest Payment Amortization Monthly Payment

1 -15,000.00 Pts -76.473,07 Pts -91,473.07 Points

2 -14.235,27 Pts -77,237.80 pts -91,473.07 Pts
3 -13,462.89 Pts -78.010.18 Pts -91,473.07 Points
4 -12,682.79 Pts -78.790,28 Pts -91,473.07 Pts
5 -11,894.89 Points -79.578.19 Pts -91,473.07 Pts
6 -11,099.10 Points -80.373,97 Pts -91,473.07 Points
7 -10,295.37 Points -81.177,71 Pts -91,473.07 Points
8 -9,483.59 pts -81.989.48 Pts -91,473.07 Pts
9 -8,663.69 Pts -82,809.38 Points -91,473.07 Points
10 -7,835.60 Pts -83.637,47 Pts -91,473.07 Pts
11 -6,999.22 Pts -84.473,85 Points -91,473.07 Pts
12 -6,154.49 Pts -85.318,59 Pts -91,473.07 Points
13 -5,301.30 Points -86.171.77 Pts -91.473,07 Pts
14 -4,439.58 Points -87,033.49 Points -91,473.07 Pts
15 -3,569.25 Pts -87,903.82 Pts -91,473.07 Points
16 -2,690.21 Pts -88.782,86 Pts -91,473.07 Points
17 -1,802.38 Pts -89,670.69 Pts -91,473.07 Points
18 -905.67 Points -90.567.40 Points -91,473.07 Points

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 87

Tables with two variables.

Build tables with two variables.

The tables with two variables consist of values distributed in a row and a column. A
The formula will operate all the values of each row with all those of each column.

The formula is placed in the cell where the row and column intersect. It is an expression, that is,
just indicate what operation should be performed with the values in the table. To compose this
expression uses references to cells. Any reference is valid except for cell references
that fall within the range of the table.

Example.
Calcule la tabla de multiplicar de los números del 1 al 5.

[Link] cell B1, and with Edit/Fill/Series, put values from 1 to 5, for the
line.

2. Select cell A2, and enter values from 1 to 9 for the column.

3. In cell A1, enter the formula =A25*A26, note that only the operation is indicated.
A25, A26 are arbitrary, any other box could have been chosen (except for the
understood in the range A1:F11, range of the table).

4. Select the entire table.

5. Active option Data/Table.

In variable cell row:, put A25, and in variable cell column:, A26. Excel will replace
all the values from the row and will place them in the location of the formula where there is A25; will do the
same with the values of column y A26. It will calculate the value of the formula and will put the
resultado donde se cruzan el valor de la fila con el valor de la columna.

ADVANCED EXCEL EXERCISE AND PRACTICE NOTEBOOK Page 88

 0 1 2 3 4 5
1 1 2 3 4 5
2 2 4 6 8 10
3 3 6 9 12 15
4 4 8 12 16 20
5 5 10 15 20 25
6 6 12 18 24 30
7 7 14 21 28 35
8 8 16 24 32 40
9 9 18 27 36 45

Practice 27. 4 - Exercise.

Retrieve the file [Link].

Build a table with two variables, in the column, put the values from 45000 up to
85000, with an interval of 5000, and in the row the values 1500000 to 4000000, with a
interval of 500000.

The annual interest rate is 12%.

Using the Nper function, calculate the number of payments that will need to be made to settle.
the quantities of the columns, taking into account the amount paid each month (rows)
and the rate. Remember that in these functions, the amounts paid are in negative.
therefore, the second argument (fixed amount that is paid) must be put in
negative.

RATE 12%

#NUM! 1,500,000 2000000 2500000 3000000 3500000 4000000

4500040,7489072 59,0720724 81,4978143 110,409624 151,1585312 220,819248
5000035,8455361 51,3375516 69,6607169 92,0864588 120,9982685 161,747176
5500032,0043359 45,4241302 60,9161457 79,2393109 101,6650528 130,576863
6000028,9118097 40,7489072 54,1687014 69,6607169 87,98388217 110,409624
65000 26,367391 36,9560355 48,7931329 62,2129271 77,70494264 96,0281079
7000024,2365867 33,8151808 44,4038252 56,2409227 69,66071689 85,1527324
7500022,4257419 31,1703131 40,7489072 51,3375516 63,17464903 76,5944433
80000 20,867584 28,9118097 37,656381 47,234975 57,82361947 69,6607169
8500019,5125185 26,9603082 35,004534 43,7491052 53,32769924 63,9163437

ADVANCED EXCEL EXERCISES AND PRACTICE NOTEBOOK Page 89

 Practice 27.5 - Exercise.

Retrieve the file [Link].

Using the PV (Present Value) function, calculate the present value of investing the amounts of the
bills, for the periods of the columns at an interest of 12%. Remember to include in
negate the payment argument.

0.00 Pts 10000 20000 30000 40000 50000 60000

12112550,775 225101,549 337652,324 450203,099 562753,874 675304.648
24212433,873 424867,745 637301,618 849735,49 1062169,36 1274603,24
36 301075,05 602150,101 903225,151 1204300,2 1505375,25 1806450.3
48379739,595 759479,19 1139218,78 1518958,38 1898697,97 2278437.57
60449550,384 899100,768 1348651,15 1798201,54 2247751,92 2697302.3
72511503,915 1023007,83 1534511,74 2046015,66 2557519,57 3069023,49
84566484,528 1132969,06 1699453,58 2265938,11 2832422,64 3398907.17
96 615277,03 1230554,06 1845831,09 2461108,12 3076385,15 3691662.18
108658577,898 1317155,8 1975733,69 2634311,59 3292889,49 3951467.39

Practice 27. 6 - Exercise.

Retrieve the exercise [Link].

Do the previous exercise but using the FV (Future Value) function.

0.00 Points 10000 20000 30000 40000 50000 60000

12 126825,03 253650,06 380475,09 507300,121 634125,151 760950,181
24269734,649 539469,297 809203,946 1078938,59 1348673,24 1618407,89
36430768,784 861537,567 1292306,35 1723075,13 2153843,92 2584612.7
48612226,078 1224452,16 1836678,23 2448904,31 3061130,39 3673356,47
60816696,699 1633393,4 2450090,1 3266786,79 4083483,49 4900180,19
721047099,31 2094198,62 3141297,94 4188397,25 5235496,56 6282595,87
841306722,74 2613445,49 3920168,23 5226890,98 6533613,72 7840336,46
961599272,93 3198545,85 4797818,78 6397091,7 7996364,63 9595637,55
1081928925,79 3857851,59 5786777,38 7715703,17 9644628,96 11573554,8

ADVANCED EXCEL WORKBOOK OF EXERCISES AND PRACTICES Page 90

PRACTICE 28 - Conditional Format

The conditional formatting for Excel 2007 and later has changed quite a bit in these versions.
making it more attractive. Include bars, icons, and predefined colors in criteria, although of course,
We can always change our conditional criteria and formats.

In this exercise, you will apply conditional formatting to a table of a group of students to find out if they have
approved the subject or not. The conditional formatting will be applied to the last column as shown.
the following:

The criterion is that the grade, summed from all points of practices, attendance, and final exam, is
over 70

PRACTICE 29 - Data Validation

In this practice, we will see data validation when it is entered into the cells. Excel has had since
The 97 version has some data validation functions to detect type, range, etc. But with the
Time, the versions changed to even add form buttons for validation.

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 91

Now, the validation option contains a list box among other improvements to obtain a combo.
dropdown of values.

We start by creating a sheet similar to an invoice, which will contain the validation formulas for
two more data sheets:

Now we will need to create another sheet, which we will call DATA and it will contain the following:

WORKBOOK OF EXERCISES AND ADVANCED EXCEL PRACTICES Page 92

And a third sheet called CLIENTS that will contain:

The problem with the practice is that on the first page of the INVOICE, we link and validate the data that
they are extracted from the other two sheets, so that when a client is entered, it automatically gives us the
address details and tax identification number (RFC).

Give us the current date always.

Let's validate that they don't enter negative data.
And that by entering the code of an item, it gives us the description and the price.
In addition to calculating the subtotal, VAT, and total.

PRACTICE 30 - Convert to HTML

Since the first versions of Excel, it was possible to save the spreadsheet in web format:
HTML. However, since the year 2000, there has been a browser plugin that allows for
that sheet becomes interactive, allowing to capture even values and perform simple calculations.

Perform the same grading practice of the conditional format seen earlier.
Recording in WEB format and then run this last one in your browser. Preferably Internet.
Explorer 6.x and above.

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 93

I might ask you for an update of OfficeWeb so I can view it interactively.

PRACTICE 31 - Obtain data from the Internet

Excel has powerful data linking and importing tools since its version 97.
Excel 2007 and later is not the exception, and we can get data from tables on web pages.
of your preference.

In this practice or exercise, we want to know the current exchange rate and we will take it from a
bank portal. In this way, it will be updated from the internet and will give us the real value of the time of
change.

For this practice, it is necessary that you have an internet connection and can update it. Also, that the
Macros are enabled.

You will make a sheet more or less like this:

Where our information is on the left side. And on the right side (the list) we will obtain it.
directly from the internet ([Link])

EXCEL ADVANCED WORKBOOK OF EXERCISES AND PRACTICES Page 94

PRACTICE 32 - Obtain data from Access

For this practice, it is necessary that you have the data file: [Link] created previously.
in Access. Ask your teacher to provide it before doing the exercise. (Or if you know a little about
Access, you can do it yourself by observing the data columns we will use.

This file is from a car dealership and contains the tables of automobiles, customers, and
reviews or maintenance.

What we want is to import the data from these tables into Excel, to analyze, graph or change.
some information.

NOTE: If you add or delete this data in the spreadsheet with the imported data, you will not update.
nothing in the original file.

This is how the import will look for you:

PRACTICE 33 - Divide text into columns

Excel 2007 and later has a new and particular feature: Convert a text from a cell into
various columns.

You can also do this using Excel functions in versions prior to 2007, but
this new feature greatly facilitates the operation.

Create the following sheet and follow the instructions:

ADVANCED EXCEL WORKSHOP AND PRACTICE NOTEBOOK Page 95

PRACTICE 34 - Macros

1- Create a macro in Excel that allows me to change the paper orientation to Landscape when printing.
call the ImprimirHorizontal macro.

2- Create a custom button for this macro and insert it into the formatting toolbar.

3- Create a macro in Excel that allows me to change the paper orientation to Vertical when printing.
call the macro PrintVertical.

4- Create a custom button for this macro and insert it into the format bar.

5- Create a macro that allows me to insert a new sheet into the workbook. Call it NEWSHEET.

6- Modify the macro so that it appears in the tools menu with the name Insert Sheet.

7-Create a macro that performs the following:

EXCEL ADVANCED EXERCISES AND PRACTICE NOTEBOOK Page 96

- Write the text MACRO IN EXCEL 5.0 in cell C6.
-Place this text with a vertical orientation.
Center it in the cell.
-Change the font to ARRUS BT.
Change the font size to 14 points.
Set the cell background to blue.
Put thick borders on the cell in dark blue.
-Put the text in Italics and bold.
Set the font color to purple.

8- Assign this macro to the toolbar by creating a custom button for it.

9- Run the macro on other sheets of the workbook.

10- Create a macro that writes in row nº1 all the months of the year, and starting from row 2 and in the
Column A every day of the week. All must have the font type Courier with size 12.
to be in bold and double underlined, as well as having borders around each of the cells.

11- Assign the macro to be executed with the keyboard shortcut CTRL + E.

12- Run the macro on other sheets of the workbook.

PRACTICE 35 - Macro Editing

Create a macro called SetTitle that performs the following
actions:

Record the macro in the workbook we are working on.

1. Insertin the cell what we are situated a text what we

we will introduce in a frame what the macro must to ask ourselves;
defaulting to the text "MACRO TEST"
2. Put this text in underline, bold, italic, and a size of
letter 24.
3. Put a blue background in the cell where it is
title.
4. Put the text color in red.
5. Add a top border and a bottom border to the cell.
6. Automatically adjust the column in which it has been inserted.
text.

Test the macro to check that it works.

EXCEL ADVANCED EXERCISES AND PRACTICES NOTEBOOK Page 97

Create a button in the toolbar that, when pressed, executes the
anterior macro.

Test the button you just created to check that it calls the
macro.

Enter the macro code and delete all those lines of

code that is not necessary.

Test the macro to check that it works.

Create a new macro called ColorLineasDivision that performs the

next actions.

1. Change the color of the page division lines to the color.

yellow.

Put the division lines of the sheet in their normal color.

Test the functionality of the macro.

Put the lines dividing the sheet back to their normal color.

Make the necessary changes in the module sheet so that the macro
PutTitle call at the end of the same to the macro ColorLineasDivision.

Test the macro PutTitle in a sheet and check what it does

all assigned steps.

Insert the comments you deem appropriate in the macro lines

to fully understand the steps that the macro takes.

NOTEBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 98

Code of the created macros.

Procedure SetTitle()
TextodelTitulo = CuadroEntr(Mensaje:="Por favor introduzca elttulo."; _
Standard:="Macro test"
[Link] = TextodelTitulo
Selecció[Link] = xlSubrayadoSimple
Selecció[Link] = Verdadero
Selecció[Link] = Verdadero
With [Link]
.ÍndiceColor = 5
Fin Con
[Link] = 3
With [Link](xlTop)
.Grosor = xlMediano
Fin Con
With [Link](xlBottom)
.Grosor = xlMediano
Fin Con
[Link]
DivisionLineColors
End Procedure

'
'ColorLineasDivision Macro
Macro recorded 5/11/97 by jar
'
'
Procedure ColorLineasDivision()
With ActiveWindow
.PresentarLíneasDivisión = Verdadero
.ÍndiceColorLíneasDivisión = 6
Fin Con
End of Procedure

WORKBOOK OF EXERCISES AND PRACTICES ADVANCED EXCEL Page 99

PRACTICE 36 - Forms

exercise 28_controls

PRACTICE 37 - More Macros (7)

exercise 29_2007

PRACTICA 45 – Funciones UDF

Exercise30

ADVANCED EXCEL EXERCISES AND PRACTICES NOTEBOOK Page 100