Formatted dynamic chart titles in Excel | Achieve the IMPOSSIBLE | Excel Off The Grid

in Excel it's easy to create manually formatted chart titles or dynamic unformatted chart titles but in this video we're going to achieve the impossible and create Dynamic formatted chart titles so let's fire Excel and let's go let's start by looking at the example file that we're working with so over here in Excel we have a table and that table is called data we also have two data validation drop-down lists which are based on that data so we have one for the product and we also have one for the quarter now to get the data that we want to display in our chart we are using the filter function so wherever our product is equal to the item in our data validation list they are the values which are returned into our filter function this is then used to create our chart so if we change the item in our data validation list you'll see that our chart updates accordingly now below that we have some calculations these are the numbers that we want to use inside our text so first of all we have a lookup that picks up the value based on the product and also the quarter then we look up the prior period value we calculate a percentage change and we also calculate the percentage of the total and you can see those formulas in those sales there now let's look at the text that we want to appear at the top of our chart so here in cell h27 it says Delta sold 81 units during Q4 26.3% of total sales and minus 3.66% versus Q3 now this is a Formula if I press f2 it now highlights all the sections that this formula refers to so our selection is in cell H3 so H3 sold i22 81 units during K3 which is Q4 we then use the Char 10 function to create a line break then we use the text function on our percentage while our percentage here displays us 26.3% this is because we've formatted this cell what we need is to display our percentage so say just one decimal place so we use the text function and say we want to display that to one decimal place so we have our percentage of total sales then if our selector if our quarter is q1 we display an empty text string otherwise we then display the variance to the previous quarter so we've got the text and we say either a positive or negative percentage versus and then we calculate the previous quarter number now what this means is that if our quarter is quarter 1 there is no previous quarter to compare against so therefore by using this if function this piece of text only displays if we have selected Q2 to Q4 so that's our formula now if we change any one of these elements you can see that our formula changes and updates accordingly if you want to get this formula into our chart title we click on that we come to the formula bar type equals and then click on the cell that contains our chart title and then I'll press return so that now gives us our text and it is dynamic so that when we change the elements that our chart title also updates hi I'm Mark from Excel Off the Grid that's the place where we show people how to reclaim their time by automating Excel so that means they don't have to work late anymore and they get to spend more time doing what they love so if that sounds good to you why not head over to Excel ofthe grid.com and check out our training program the next step is to create the list of words that we want to format and the formatting that we want to apply to those words I've called these power words you can see them here we have a list of the text that we want to format and you can see the formulas which we've used to generate that so all we want to do is now format these words so let's say we want to apply this green formatting make this bold and then size 14 and let's apply this to our two other values as well we now have our percentages our first percentage can't be negative it can only be a positive number so I'm just going to make that bold our second two percentages both refer to the total change now this could be positive or negative so if it's negative we want this to be red for example and if it's positive we want it to be green so I'll select those colors now the moment only - 3.6% highlights and that's because we have a negative variance if I change this so that it's a positive variance you can see that that now is green and a positive value and the red value turns to not applicable so this means that depending on whether our value is positive or negative our formatting will apply differently to apply the formatting we're going to use a VBA user defined function if you look at the descriptions box below you'll find a link to the blog post for this video if you click that link you'll be able to find the blog post and that contains all of the code that we need so go ahead and copy that code and then come back to excel now before we go any further we just need to understand how this VBA user defined function works so it's called FX format chart title it has four arguments the first argument is chart name this is the name of the chart that we want to format the second argument is title text this is the text that we want to apply to the Chart title the third argument is default text style this is a cell reference that contains the default text format that we want to apply to our chart title the fourth argument is power words this is a reference to the range of sales that contain the words and the formatting that we want to apply to those words okay now let's head back to Excel and press alt f11 and let's open up the Visual Basic editor I'll select my workbook click insert and then insert a new module it's called module one and then in there I can paste the code that we've copied now let's head back to excel if I click on my chart you can see in the name box that my chart is called chart one so when we come to apply our formula equals FX you can see format chart title now the first argument that we want is the name of our chart so we saw that that was chart one the second AR of this function is the text that we want to enter into our chart title we have that there that's cell h27 the next argument is the cell that contains the default text format so whatever font or color with it's bold or italic what cell contains that format well let's select cell h27 again and then the last argument is the power words so the list of words and formatting that we want to apply so I'm going to select that entire range I'll close that bracket now watch the chart title as soon as I press return you see that has now formatted to be exactly as per our text and also with our power words so what happens if we change our product fantastic that recalculates and that works now let's change our quarter yes that works as well so we now have a fully Dynamic chart title that is formatted have you ever seen that technique before let me know in the comments let me know whether you have used it before or whether it's completely new to you if you like this video click here to subscribe and click there for more Excel goodness

Channel: Excel Off The Grid

Views: 3,754

Keywords:

Id: _4YUC6_EocQ

Length: 8min 16sec (496 seconds)

Published: Thu May 30 2024