[0:00]Hello and welcome to the dashboard course by Trump Excel. I am Sumit Bansal and in this video I'll show you how you can use symbols in your dashboard. Now, symbols can be used to show trends in your table, in your charts and you can color your symbols as well to show a positive trend or a negative trend. These could be really helpful to add another layer of analysis to your dashboard. So let's see how you can use symbols in your dashboards. So here I have some dummy data for the number of arrivals for these top 10 countries. This is not real data, I've just made it up for this video, and now I'll show you how you can use symbols to show trends and add another layer of analysis to your data or your dashboard. So here I have 2014 and 2015. Now, let me also calculate what change has happened in percentages in this year over 2014 to 2015. So to do that I would use this simple formula C3 minus B3, which is the difference of these two numbers, divided by B3, which is 2014 number. And now when I hit enter, it gives me this value. I would drag it down for all the countries. Now, I would press control one to change the formatting and here in number, I would use percentage formatting with one decimal place. So you can see now this gives me a percentage, which means that 1.8% is the increase of the number of arrivals in France in 2015. Now, these are all numbers, but I can also add trends to it. So I would use symbols to do that. You can find symbols if you go to the insert tab and here, you have symbols option here, so there is a group called symbols and there, there is this symbol sign. If you click on it, it will open this dialog box where you would find a lot of options. Here in this case I have selected aerial and within aerial I already have these arrows pre-selected, but if you open it and it is not there, then you can select aerial and then you can scroll down and up a bit so that you would find those symbols. I already have used it a couple of times, so it also appears in my recently used symbols. Now, if I have to insert this symbol, I would simply click on it and press insert. And you can see it comes here. Similarly, I would use this symbol as well, I would say insert. And now I close it because these are the two symbols that I'm going to I'm going to use. You can use any symbol you want from that list, but to show trend whether it's increasing or decreasing, I would use these symbols. Now, to apply these symbols here, there are two techniques to it. So the first technique I would show you is custom number formatting. Oops. Let me apply the same format here just so that it looks better. And I also need to convert it into percentage, so I would again press control one, get to this dialog box and convert it into percentage. Now you can see there are positive percentage values and negative percentage values. And if you just look at this data, then it may not be very intuitive to read this data, you'll probably have to go through each of these numbers. So I would use these symbols to add a trend to it so that the data reading this data is easier and you can use it in your dashboards to show this analysis quickly. So to do that, I would first select these symbols and now I would go and select these numbers. And I would press control one. It opens the format cells dialog box. Here I would go to custom. And within custom, you can see that as of now it says 0.0% and this is in the format in which the numbers are currently displayed. I would change this format and I would say, if it's a positive number, then give me an upward arrow along with 0.0%, which is the format in which I want to see this value. So if I use this format, it will show me an upward arrow along with the number for all the positive numbers. And if I also add to it the formatting for negative number, which would be downward arrow in double quotes, along with 0.0%, it would show me a downward arrow for all the numbers that are negative. So any number which is positive or zero would have an upward arrow along with this number and any number which is negative would have a downward arrow. Now when I hit okay, you can see that these arrows have been inserted. Here I have a downward arrow here, a downward arrow here and a downward arrow here. You can again improve the readability by color coding these numbers. So for example, I may want to show these downward arrow and these values in in red color. So to do that I would again press control one, and here I would simply add red in square brackets. And if you want to learn more about custom number formatting, I've covered it in one of the videos for custom number formatting and everything is covered in detail, so I would request you to go back and watch that video. But in essence what happens is, in custom number formatting, you can specify the format for positive number, negative numbers, zeros and text values. So you can specify four different formats. In this case, I'm only specifying two different formats, which means that the first one would be applied to positive numbers and zeros and the second one would be applied to negative numbers. And when I mention this color in square brackets and there are a set of colors that you can use like red, green, blue, black, that color would be applied to the formatting. So here if I click okay, you can see that instantly it has colored these three values as negative as red because these are negative values. So you would instantly know in United States, in Turkey and in UK, the number of visitors fell in 2015. So this is how you can do this using custom number formatting. Now, let me show you another way of doing this. So let me copy the format from here.
[6:30]And in this part of the video I will show you how you can use formula to create such custom number such a format. I'll not be using custom number formatting, rather I'll be using a formula, and I would use these symbols in the formula. So I've copied these symbols as of now. And now I would create that formula. First I would use an if condition to check whether the value is positive or negative. So I would use this by simply saying C3 minus B3 and I would put it in brackets, divided by B3. And if this value is greater than zero, then I would say give me an upward arrow. Let me oops, let me show you here because there's not enough space in the cell. So here I would show you, then show me this upward arrow and if it is negative, then show me the downward arrow. Now when I hit enter, it gives me an upward arrow here, and if I apply it for all the cells, you can see it has it is giving me these arrows. Upward arrow for any number which is positive and downward arrow for which any number which is negative, you can see I have downward arrow here, I have one here and I have one here. Now, I also need the value, similarly what I have here, so to do that, I would now again change the formula and I would use text function.
[8:34]And text function takes that value and shows it in the specified format. So it takes two argument, it takes the value and then you specify the format and the format here would be 0.0%. And now when I hit enter, you can see it gives me the same thing here in a different format, using a different technique which is a formula technique. You can see that both these things are same, apart from a minor difference here, it shows me a negative sign, here it doesn't. If you want to show a negative sign here, you can change the custom number format, and one minor thing is you cannot color this in red. You can do that by using a a different approach. I can do it by using text formulas and some other formulas and going to conditional formatting, but it's not as straightforward as this, so I would leave it up to you to see how you can color code these in red and obviously you can use a simple conditional formatting anytime. But these are two ways where you can use these symbols to show a trend in your tabular data. Now, let me show you how you can use it in charts. So to do that I would insert a chart here. I would go here and I would insert a simple chart. Let me make these a bit smaller and in this chart I would put these values, 2015 values. So I would simply copy and paste it here and it comes here. I need to change these access, so I would go to design, select data, and here I would change the horizontal category access labels. I would put these country names here. Now I have the data, but if I add data labels here, you can see what it is giving me. I can anyway go and format these data labels to show me 83 million or whatever the number is. But my idea is that this chart only gives me the values for 2015, but what if I want to also see the trend here? So to do that I would have to again use let me use the formula technique here. So I would again simply use the formula and I would say if, and the logical test would be in a bracket, this value minus this value, divide by B3. If this is greater than zero, then okay, I'll have to copy the symbols, so I would remove this, copy these symbols so that I can put it in the formula, and then put the equal to again. And here I would say give me an upward arrow if this is more than zero, else give me a downward arrow. And again, as you saw a couple of minutes ago, it will give me these numbers. And what I would simply do is I would append it to this number here. And if I want, I can simply change the format by using text. So I would use this number, but I can change the format by simply using 0,,. And you can see it gives me 83. By introducing two commas, I've ensured that it gives me only the numbers in millions. If you want, you can use 0.0, so that it will give you one decimal place. Now, instead of plotting this data as an instead of having this data as data labels, I would change the data labels. So to do that, I would double click on these and it opens the label options. I would go to label options here, which says you can select the labels from values from cells. And if I click on it, it asks me, you want to select the data label, what is the range? I can simply select this range till F2, F12. And now when I click okay, you can see that I have these numbers displayed. I would go back to the label and I would deselect values because I don't want to show values. I only want to show these numbers and I can also go back and change this format here to 0.0,,. And when I add it, it will also show me these numbers in millions. And now you can see that it gives me the number for France. The current number for 2015 is 83 and it also gives me a trend arrow. It shows me an upper arrow indicating that as compared to the last year, the number has increased in France, while in United States it has gone down. Similarly, you can see that it shows me a trend arrow. So you can use it in charts. If you're using these data labels, you can use these trend symbols so that you would instantly know that this is the value for the current year and this is the trend, maybe as compared to last three years, five years or the previous year. Now, what if I don't want to show this trend here? Let me delete the symbols. What if I want to show the trend here at the bottom? So instead of showing France, I want to show France and then the symbol. Again, to do that, it's a simple thing. I would simply copy this formula and I would insert a row here. So I would go and I would insert a column, sorry, I would insert a column here. And I would use the formula here. And I need to change the references, so I would simply change the references, D3 minus C3 divided by C3. So now it gives me the arrow. And all I need to do here is instead of selecting this as my access label, so I would go to design, select data and instead of selecting this, I would select both of these. And as soon as I do this, you can see that I have the trend here. I can extend this a bit so that it shows me these as horizontal names. And you can see that I have the trend here. Now, what if I change the number here? So maybe I make this number as not 83, but 73. And you can see instantly this arrow has changed and it reflects that in France, these numbers have gone down. I can quickly go and add the data labels, put it whatever I want to in whatever format I want to, but these trends arrow would be displayed at the bottom. So again, this is a technique where where you're not showing a lot of data, rather, you're using symbols to show a trend in your data, which makes your dashboard much more intuitive and easy to read. So this is how you can use symbols. There are many more symbols. I would suggest you go to insert and this portion and check out all these symbols, you can use a lot of things. And they these are simple techniques that you can use. One is custom number formatting and one is using a formula, and you can try it out with different kinds of charts. So that's it in this video. I hope you found this useful. Thank you and have a nice day.
