Hello everyone, welcome to Other Level's today you will learn how to create a dashboard for schools with dynamically changing student photos using Microsoft Excel without VBA codes So let's start.. Welcome to Other Level's these are the color codes and font types used in the design you can get all our templates fully customizable by visiting the website www.other-levels.com this excel workbook containing four main parts first a sheet for the database the second sheet that contains all the pivot tables that are connected to the dashboard's values and charts third a sheet that contains everything we need to
implement a dynamic image changer finally the rest of the dashboards and we will only implement three of them main dashboard and events and teachers let's start creating the main dashboard for the dashboard we don't want the gridline and heading this design contains two backgrounds and we will implement the first background insert a rectangular shape colors and dimensions are always shown in all our videos select all cells and change the color to light purple for the second background first we need to insert a round same side corner rectangle shape press control plus c then drag the
shape to the right now we will create the design of the buttons for the side menu in order to create it we will use powerpoint to use the fragment tool insert the plaque shape next to the round same side corner rectangle then cover half of the plaque shape using a rectangle now we will use the fragment tool to divide the shape group them together great insert text boxes contain the dashboard's titles and your school logo on top for all values and charts we want to have a white background so it is easy to read first
we will insert a rectangle for the main title of the dashboard and for the main values we need two rectangles that are rounded at the same side angles copy the shape with control plus c then drag to the right next insert white equidistant rounded rectangles for all values and charts insert a text box for the title then insert icons from the excel icon library now we will put all the titles for each part check our colors codes and the font size and thickness it's time to insert pivot tables choose database then click insert pivottable choose
to place the pivot table in the existing worksheet that will be the pivot table sheet to learn more about pivot tables please watch the playlist shown above we do not want to repeat the previous steps to insert the pivot table you can just select the whole pivot table then copy it and paste it into another place select the values you want we want the sum of parents teachers and students to connect the text box with its correct value in the pivot table type in the formula bar equal sign then select the correct cell in the
pivot table sheet the background needs a picture that adds more beauty so include the picture you prefer from the excel library or a real picture of your school send it to the background of the menu on the left and then we will make the menu colors gradient so the color at the top will have a 100 percent of transparency and on the bottom of the image it has a sixteen percent transparency then crop the image but use the rounded rectangle shape to cut so that the edges of the image are matching with the edges of
the menu now we will insert a column chart that shows the number of students at each educational level delete all chart elements and keep only the vertical aux to change the shape of the columns we will insert a 3d box shape and specify a color for each educational stage all you have to do is copy the shape and then choose the column then paste it the beginning of the columns from the bottom does not look nice so we will cover them using a white rectangle we will show the value and education stage name of each
column in the chart but to the left side as green as the primary school yellow for the elementary school and violet will be the preschool the total student number will show below each education stage name excellent we are done here we will move to the part of the activity and events we will start by creating a view all button we will add a hyperlink to it later to moving us to the events dashboard when clicking on it we will create a pivot table containing only the names of events and activities we only need to show
four events because we want to show only a sample part of all events not all events therefore we will connect the text boxes to cells and not directly to the pivottable to avoid errors to ensure spaces between text boxes use the distribute vertically feature separate them using horizontal lines then also use the distribute vertically feature as we explained earlier that the view all button will move us to the events report so now we will start copying the current layout design in this sheet to another right-click and then choose move or copy check on create a
copy change the name of the new sheet tab now we will delete every part we don't need and keep only the parts we need as you can see i am now merging the white rectangles together instead of inserting one large rectangle to make sure 100 percent that the edges and dimensions of the background are uniform with the main dashboard drag it down and change the font color we will add a hyperlink to the dashboard so that it directs us to the main dashboard when we click on it with the change of important point type a1
in type in the cell reference field to ensure that the report is shown when moving to it in a correct and fully clear position insert the report into text boxes according to the size of your data with that we have finished this report now we will add the teachers sheet and it is better to make a copy of the event to start adding pictures for teachers we will use any pictures from the excel picture library to learn you how to modify images and appear them creatively crop the face as much as possible and insert a
circular shape as a background for the image if your images have a background so please remove it using the background removal feature in excel and to learn more about background removal please check the video above then insert a donut shape to cover the circle and image borders make the teachers names in bold font and the job title in thin then add the data you want to show to each teacher now we will move on to the slicer and timeline select any pivot table then insert a timeline choose a preferred design for you and you can
learn how to modify the timeline and pivot table by watching the video above choose the month's filter then hide all timeline options we do not want to show the year so we will cover it with a white rectangle for your information the version available for sale on our website has been replaced the day's slicer with a year slicer from slicer settings choose hide items with no data then hide the slider header now it's time to create formulas that allow us to change the students pictures according to choose the month and year from the slicers prefer
to create a separate sheet to implement and let's call it dynamic images we will enlarge the students pictures to appear them in high quality in the dashboard and to be able to control these large images we have to zoom out the page by sixty percent then we need to create a table with three important columns let's take a look for something first you can see in the database that we replace the spaces in the names of the students with the underscore sign we will explain why shortly first the names of the students in the current
names form in a database table that contains an underscore sign secondly a column for the names of the same students in column a but without the underscore sign because it's not a professional to appear the names with underscore on the dashboard a third column for the students pictures insert the photos then crop it using the rounded rectangular shape now insert a white circular as abackground for the photo then we will create a photo frame using a donut shape select all and align to the middle and center set a dark gray color to the frame repeat
the previous steps for all remaining images now press right click and save as image then re-insert the photo while making sure that all images are inside the center of the cell copy the student's name and put it as the name of the cell containing his picture in the cell name field box at the top left of the screen as you see that the current cell name is c2 paste the student name then press the enter that's why we add underscore sign because you can't type texts that contain spaces in cell names change the names of
all cells now we are preparing the place where the names and photos of the top students will be displayed on the dashboard make the small rounded rectangle a lighter color than the big one next we need to create a table that will show the final correct results for the top three students let's go back to the pivot table sheet and start first we need a pivot table that's showing the year that have been selected from the slicer and another showing the month then we will create a table containing the students rank column from the first
to the third place the second column contains an formula that combines the choices from the two slicers and the current ranking in rank column and the formula as concatenate select the month then the rank and the year close the bracket and press enter i will explain to you what this step means if we look at the database we will find on the part related to the top students it contains the student rank then the student finder then his name then his percentage now every month of the year we have three students get first places from
january february march and so on and to find the names of the students by choosing the year and month of the slicers we combine the month and rank and year so we have a name that is the same as a code that cannot be repeated in the student finder column therefore in the last column here to find the student's name we will use the vlookup formula with the code name to find it to make sure the names are correct let's review this example april 2021 has been chosen from the slicers the apparent name is rovan
in the first place then ronnie in second place and finally adam in third place so the idea is 100 percent successful now we will link the cells in the names column to the correct cells in the pivot table sheet in this column we will convert the student's name to the correct form to show it on the dashboard regard to the student percentage we will use the vlookup formula with the code name in the database to find the correct percentage now copy the image and put it on the dashboard this picture will be for the student
who in the first place open the name manager menu by pressing ctrl and f1 add a name for the data range for example name it first then in refers to field write this formula indirect then select the cell that contains the student's name in the first place select the student photo and the type in the formula bar equals first and press enter now the image working dynamically depend on your slicer selection do the same previous steps for the second and third places link each text box to the correct cell names and percentages hide sheet tabs
now we will protect the dashboard press ctrl a to select all the objects and shapes then press ctrl 1 to open the shape options menu select lock except for the slicers must be unlocked go to protect the sheet uncheck all options except select unlock cells and use pivot table report then set the password do the same steps as above for all sheets i hope you learned something useful in your work thank you for watching
