Excel

Nifty Nesting
Summary Notes
 2

Contents

3 Introduction

3 Lesson objectives

3 Conditional formatting

4 Formulas

5 OFFSET function

7 References

EXCEL
3

Lesson objectives
By the end of this lesson, you should be able to:

• Explore advanced conditional formatting

Conditional formatting
Disclaimer
Our data collected is about the movies and TV shows you can watch in 2021 on the platform: Netflix. Netflix tends to
change their movies and tv shows very often, so it is vital to keep in mind the last update from our data was the
18/07/2021. We got our data from the website Kaggle. You can visit the website and take the files from there. I encourage
you to look and practice in your free time. The more you practice, the better you will become using your new skillset.

Demo
When we first look at the data, it may look very unorganised. When we apply some formatting to the files, they will look a
lot better. The file you are viewing right now is more than likely what you will see when working with real-life data.

We start by creating or selecting headings for our headings and making them bold. Increase the size of the columns to
ensure that you can read the titles.

Here we have 13 columns. The movie ID, movie title, description, director, genre, case, production city, country, release
date, rating, duration, IMDb score, vote, content types, and the date added

Do you know what the IMBD score means? It is a score that is voted on by the IMDb registered users. They can cast their
vote on every released movie title in the given database. The is from one to ten, one being the lowest and ten the highest.
Average votes summarised into a single rating. The rating is then visible on the title page. Did you know a movie or TV
show needs a minimum number of votes before the score shows?

Remember, it is vital to make sure you understand all of your data. If you do not understand or know something, do
research, or ask the creator. Do not ignore any of the data you are working with, as it may be relevant to your results.

By pressing the control and down arrow, we can see the number of rows. In this example, we can see 5 967 movies and TV
shows on the platform.

By quickly reviewing the data, we can see a few empty cells in the last column. If we take a closer look, we notice that the
TV shows do not have a ‘date added’. There is also missing information regarding the IMDb score.

First, we apply filters. Select all your data using your control A function. In the home-tab ribbon, look for the group called
editing on the right-hand side. Find sort & filter and select filter. You can use conditional formatting if you prefer to view all
the data and not hide cells. But as the data, we are working with consists of 5 968 rows hiding the irrelevant data makes
more sense. When looking for rules, such as the top or bottom ten, imagine scrolling through all the data. There is so much
data that you may not even notice the changes when applying these rules.

EXCEL
4

Use shortcut control, shift, and the down arrow to select the column. You then apply your conditional formatting to it.
Please note, the selection will stop where there is a gap in the data. So, make sure you are scrolling down and selecting it
all. You may need to click two or three times.

Select your data, go to the home ribbon, click on the conditional formatting option. Select manage rules and click on a
'new rule'. The new rule dialog box will appear. Choose the format only top or bottom ranked values. For the top value,
select 100 and choose blue as the fill colour.

If we are filtering the content type, for example, we are more interested in watching a movie than a TV show, we can apply
filters too. We also add the additional filter of the country where the movie was produced. In this case, Italy.

In some instances, as you are analysing your data, you may find additional facts. We are looking for the country of
production, and we notice that some movies are produced in more than one country. When we add the Italy filter, we see a
few options with other countries named too.

Now that we have refined our data, we can go back to adding the conditional formatting.

If we go to Home > Conditional Formatting > Manage Rules depending on our position. We can see the rule we have
created. When we apply rules, they are applied to the data range, columns, or rows that we have selected.

As you are filtering and formatting the sheet, your data should become a lot smaller. We will be refining the data slightly
more by applying a new rule that will help us find the names of the directors that directed more than one move.

In our new rule selection, we choose the option “Format unique or duplicate values.”

We aim to find a director with more than one so we choose 'duplicate' and a colour we would like those cells to be.

In this sheet, there are only two duplicates from a director of Italian movies. Now, this intrigues us about how many purely
Italian movies are there. So, we highlight the cells of the production country and apply the same rule. We chose the format
of the cell with unique or duplicate values. We select red colour for the unique cells.

Even if we remove our filters from the data range, the conditional formatting will remain. Now we can see which directors
work on more than one movie.

If we select any cell from the column Release date. Go Home > Conditional Formatting > Manage Rules. Select the rule
we previously created here and choose the option Duplicate Rule. Edit the rule, select the bottom and a different colour for
our second rule of this column.

Finally, click Home > Conditional Formatting > Manage Rules. In the top Show formatting rules, by default, we have
Current Selection. But we can choose: This Worksheet and visualize all the rules we applied through the topic. We can add,
edit, or eliminate it from here.

Formulas
It is time to use our formulas. Imagine you are at dinner with your friends last weekend, all of you have Netflix. You are
talking about the movies you have watched. You choose to create a list of the movies that you have not watched, and your
friends recommended them. You decide to use the Excel file that you already have to sort through the data.

Demo
It is time to use our formulas. Imagine you at dinner with your friends last weekend. All of you have Netflix. You are talking
about the movies you have watched. You choose to create a list of the movies you have not watched. These are the movies
your friends recommended. You decide to use the Excel file that you already have to sort through the data.

You start by arranging the moves that have a release date before 2020. You can do this by using the IF function. You can
create a personalised message for those movies released in 2020 or after. This message could read ‘watch’. Or you could
have a personalised message, stating ‘Disregard’.

EXCEL
5

So the syntax will be =IF(K2>=8;’WATCH’;DISREGARD’)

It means that you will disregard any movies under a rating of eight and watch those up to 10.

Now, remember there is missing data in the IMDb score and the date (when it comes to TV shows.) It is now the best time
to use the IFNA and the IFERROR function to help us find the missing data.

Starting with IFNA. Type =IFN(M2;K2). To apply this formula to the whole column up need to click and drag. The formula
will present us with a zero where the cells are empty.

The IFERROR can also be used. Type =IFERROR(M2;K2). Drop and drag the equation. But you may have noticed that the
result is the same. Remember you can customise your functions to. Instead of a zero, we can use a personalised message
such as ‘missing information.’ Having a text message in between numbers also makes it a lot easier to identify.

The AND function is also a great way to filter out movies to the movies produced in the United States. Type =AND(G2;
movies) Instead of highlighting the information or showing it in the same column. It will create a new column saying TRUE
or FALSE. In other words if it contains both the criteria, then it is TRUE. If not, it will display FALSE.

Similar to our AND Function, OR function can help us to highlight specific data. It needs to match with our filters. Type
=OR(H3>=2020,K3>7) and drag our formula. It is saying that if the release date is greater or equal to the year 2020, or if the
IMD Score is greater than 7 then display TRUE. It is opposite to the AND function one of the two scenarios has to be TRUE.

Now you want to know the name and description of the movie. You have the movie ID. You decide to use the VLOOKUP
formula. We also can use the search on filters. But this will take time if we have to do that for each movie.

Type =VLOOKUP(S2,A1:$M$5968,2,FALSE) Now we got the movie name, we will drag our formula to the rest of the cells.

For the description, we will use the same formula. But this time, we will type 3 instead of 2. Excel will bring the information
located in column 3 =VLOOKUP(S2,A1:M500,3,FALSE)

OFFSET
Syntax
The offset function shows us the reference to a range that is a specified number of rows and columns from a cell or a range
of cells. It can be a single cell or a range of cells. You are also able to specify the number of rows and columns that are
returned.

Reference is a required field; it refers to the references from which you would like to base your offset from. It needs to refer
to a cell or a range or adjacent cells. If it does not, then you will get the #VALUE! Error.
Rows is also a required field. This is the number of rows you would like the upper left cell to refer to. These rows can either
be up or down. For example, if we use 2 as the row’s argument, it specifies that the upper left cell reference is 2 rows below
the reference. Rows can be negative (which means they are above the starting reference) or positive, meaning that they
are below the starting reference)

Columns is the number of columns that you want the upper-left cell the upper left cell to refer to. These columns can
either be to the left or the right. When the cols are negative it means that they are to the left of the starting reference. If
they are to the right, it means they will be positive.

Height is an optional argument, and it refers to height in terms of the number of rows which you would like the returned
reference to be. It needs to be a positive number.

Width is also optional, and it refers to the number of columns you want to the returned reference to be.

EXCEL
6

Demo
For our last example we will create a dropdown list with the movie names. Select the cell where you would like the list to
appear. Go to the Data tab, in the data group select data validation. A data validation dialog box will appear where you can
enter the validation criteria. Choose list. In the source box select the data range. As we have a long list of movies it is
always a fantastic idea to know your shortcuts. We do not want the title in our list so selecting the first movie after. Press
Control, shift, and the down arrow. It selects all the cells in that column.

Now we have our dropdown list, we can see we have all the movies on the range chosen listed right here. We can select
one of those to add it to the cell. We might want to copy to the other cells in this column as that they contain data
validation lists too. So one easy way to do that we just select the cell that contains the data validation then select the rest
of the cells in the column, It is the active cell so we can see the data validation list there. Go to the tab Data, in the group
Data Tools we will select the Data Validation bottom. Excel will warn us that the selection contains cells without data
validation. Do you want to extend the data validation to these cells? Click Yes. In the Data validation dialogue box, we
confirm our range and click Ok. It will extend the data validation into the rest of the cells in our column. We also can copy
and paste cells. If we want to search through this list, unfortunately, there is no built-in way to do that in Excel.

Now we have our data validation, what happens if we want to add another movie? Let us say Star Wars, we go to the list in
column B in the Sheet named Netflix Topic 2 and we go to the bottom of the column, we type Star Wars. well as you can
imagine it will not be included in our list. So, this is what we want to achieve, upload our list, and that our dropdown list
will be updated as well, so we will go to the tab Data, in the group Data Tools we will select the Data Validation bottom, in
the dialogue box called Data Validation. Notice that our source here is an absolute range so that is why it is not going to
update anything so what we need to do is to use the OFFSET and the COUNTA functions, we can type =OFFSET( but as you
notice the little Excel helper does not pop up here so if we have a hard time remembering all the arguments to enter the
best way for us to do this is to close the Data Validation window and write our function here first, so the function for a
dynamic list is going to be =OFFSET('Netflix Topic 2'!B2,0,0,COUNTA('Netflix Topic 2'!B:B)-1) the reference which
would be the first movie in the list, we don’t select the title cell, comma then is row which we are going to put 0 and the
next column 0, now this two ceros 1 for rows 1 for columns simply means we do not want the reference here to move,
remember if we put something else instead of cero it would tell the OFFSET function that we want our reference cell to be
one to the right or up or down or left, so that is what means, the most important part is the next one so comma, now we
choose the height so how high do we want it to be, well we are going to use the COUNTA function for that so COUNTA open
brackets we select the entire column we close the COUNTA brackets and we are going to do minus one, then close the
OFFSET function, close brackets, the minus one is simple here because the COUNTA function is going to count all non-
empty cells so this right here is going to count the Title Cell, and that means it is going to give us one extra cell that we do
not want in our list basically, because what is going to do is to is going to the reference cell and count down the more of
5000 rows we have of movies names but that includes the Title and we do not want to include that one, so that is that the
minus is for. If we had more than one title cell, we will have to subtract the same number of extra cells we would not want
to be included. We hit enter, we ignore the result, and highlight our formula, we copy it Ctrl C, and now we can go to the
Data Validation window and in the field source we can delete our information and paste our OFFSET function, as you can
see it highlight all of the cells in the list, we click Ok. Now when we add some extra movies to our list, let us say The Lord of
the Ring, we go back to our dropdown list, find our new movie and as we can see it is there if we delete some of the
movies, the dropdown list will update automatically and eliminate it too, it is dynamically updated.

EXCEL
7

References
• Handbook of Data Visualization. Editors: Chun-houh Chen, Wolfgang Karl Härdle, and Antony Unwin.
[Online]
[Link]
zation&ots=hyGCO9Zif9&sig=zU1cFg7KiM11-qrNbjd7-
x5NgCk&redir_esc=y#v=onepage&q&f=false
• Microsoft, 2020. Microsoft Office Use sparklines to show data trends. [Online]
[Link]
008c-4783-926b-5c60e620f5ca
• Microsoft, 2020. Microsoft Office Analyze trends in data using sparklines. [Online]
[Link]
a8e3-471a-a459-873614413ce1
• Microsoft, 2020. Microsoft Office Create a chart from start to finish. [Online]
[Link]
dd61-4e18-8a73-
b3fd5d5680c2#:~:text=You%20can%20create%20a%20chart%20in%20Excel%2C%20Word%2C
%20and%20PowerPoint.&text=Click%20the%20Insert%20tab%2C%20and,a%20table%20of%
20sample%20data.
• Microsoft, 2020. Microsoft Office Add or remove titles in a chart.
[Online][Link]
1482-4908-922a-997c32ea8250
• Microsoft, 2020. Microsoft Office Add or remove a secondary axis in a chart in excel. [Online]
[Link]
excel-91da1e2f-5db1-41e9-8908-e1a2e14dd5a9?ui=en-us&rs=en-us&ad=us
• Microsoft, 2020. Microsoft Office Add a trend or moving average line to a chart. [Online]
[Link]
fa59f86c-5852-4b68-a6d4-901a745842ad
• Microsoft, 2020. Microsoft Office Choose your chart using quick analysis. [Online]
[Link]
7f5e-495a-a8dc-be8225b1bb78
• Microsoft, 2020. Microsoft Office Update the data in an existing chart. [Online]
[Link]
c608-4a7c-9679-5c70e374f9be

EXCEL