Awesome IMAGE function techniques you need to know! | Excel Off The Grid

hello there I'm Mark from Excel Off the Grid and in this video we're looking at awesome things you can do with the image function that you probably didn't know we could do so if you're ready let's get started image is one of the newer functions which means it's only available inside Excel 365 so if you've never seen it before don't worry we're going to start with a quick recap so we understand all the syntax and arguments the image function has five arguments Source alt text sign sizing height and width though only the first argument is required and that must be a web URL that starts with https so if I wanted to get my logo from Excel Off the Grid into Excel I would right click on the image and come down to copy image address then I'll head back into Excel and I'll paste that address into cell E6 then to use that URL I'll come across into cell B6 type equal image and then in Brackets I'll select cell E6 and when I press return that now goes and gets that image from Excel Off the Grid in its basic form image is that simple but we have all these other optional arguments first of all we have the alt text this is the text which appears for people who use screen readers but we can also use this alt text as we'll see later in this video so for this I'm going to enter eotg logo the next argument is sizing if we enter zero that means the image will fit inside the cell and maintain the aspect ratio if we enter one it means it will fill the cell and stretch the aspect ratio to fill that cell if we enter two it means the original image size will be maintained no matter how big that image is and if we provide number three we can then use the height and width arguments but they're only relevant if we have selected three as the sizing option so for example if I enter one and commit that function the logo is currently stretched if I change that back to zero that logo now fills that cell and is not stretched so that's the basics of the image function now let's go and see some of the cool things that we can do with it the first thing we're looking at is image generating API these are websites that generate Dynamic images that we can then use inside the image function the first example that we're looking at is quick chart. how it works is that we provide a URL and then quick chart returns an image back to us if we go into the chart Gallery you can see that we have lots of options available in here to start with we're going to create a QR code so I'll scroll down to that section and then click QR codes here we can see all of the documentation about how we create a QR code in its simplest form we can just take the example URL at the top and then apply that to our requirements so I'm going to take that text copy it and then come back to Excel and paste it in Cel D6 for this QR code I want it to go to excel offthegridsurvival equals image and then select cell D5 I'll close that bracket and press return and there we go we now have a functioning QR code for our second API we're going to look at Excel api.org and it has an API which converts formulas to images and here we can see some examples at the bottom we have an example URL so we're going to take that and use that inside Excel so now in cell D13 we have the text that we want to convert into a math formula in cell D15 we've used that same API code that we got from Excel api.org and we've now created the HTTP address that we need to use for our image function so now if we come across to cell B13 we can type equals image then enter cell D15 and when we commit that we now have eal mc^2 as a function image inside Excel for our final API example we're going to return back to quick chart. and we're going to create a sanki diagram here it is in the chart Gallery so let's click on that and look at the documentation on the left we can see an example of the code that's required to create this chart so let's head back to Excel and create our own sanki diagram the table on the right displays the flows that we want to display inside our sanki diagram we then have a formula that generates valid syntax that we can use inside our URL if we look in cell c49 you can see that we have used the text join function on our chart data column in cell B21 we now have those three elements required for our URL combined into a single string so as before we can type equals image and we want that from the URL in cell B21 when we close that and press return fantastic we now get a sanki diagram in inside Excel so that's three examples of using image generating apis inside the image function there are lots of apis out there some are paid some are free it's just a matter of finding the ones that you need for your scenarios are you just tired of working late and missing out on what's important to you well you don't need to work late anymore over at excelth grid.com our training program teaches you how to automate your work so you can get more done in a fraction of the time so head over to excelth grid.com and let's make working late a thing of the past the image function relies on a web URL which means if we don't have a website how can we share images well we've long been told that we can't use one drive or share point for this but we can and a few weeks ago win over at access analytic showed us a technique that he learned from baz from how to powerbi and that's what we're going to look at now how can we use one drive or SharePoint images in inside the image function here we have an image file that's been saved on my one drive and you can see that we have a green tick to indicate that this has been synced back to the server so now if I want to get the URL for this I can right click on the image and then select share next I'll click on the gear icon and you can see we have the option to share the link with anyone now this option might be gray out depending on your admin settings so you can only use this option if your admin allows you to share files with anyone but we can select that and then click apply next we want to copy the link and then come back to Excel and we'll paste that into cell E6 now the key thing about this URL is the section at the end we want to delete everything after the question mark replace that with download equal 1 now in cell B6 we can type equals image select the URL in cell E6 and fantastic we we are now connected to our file which is saved on one drive for our final example we're going to look at how we can select images dynamically and we'll also see a really important use for the alt text argument here in Excel we have a table called images which contains three columns now let's use the image function inside the image column equals image Open Bracket and then we want to use the URL column as the source next we want to use the description column as the alt text then we can close that bracket and press return that now copies down and gives us an image for each of those rows one of the easiest ways to create a data validation list that stays in sync with a table is to use a dynamic array so in cell F6 we're going to type equals then images is the name of the table then in square brackets image is the name of the column and when we press return on that we get the spill range showing all of those images and we can now use this spill range in our data validation list so I'll select cell B12 and then click data and then data validation in the allow dropdown we want to select list and then for the source we want to use our Dynamic array so we will select F6 as the first cell and then enter hash to get the spill range then we can click okay and now in cell B12 we have a data validation list where when we click on the drop down we can see the alt code that provides the description for the image and when we select one of those it will then display each of those images the alt text is also useful for slicers so if I select a cell inside the table and then click insert Slicer in the insert slicers download box I can select image and then click okay and you can see the text displayed for that slicer is the text that we provided as the alt text so yes please provide meaningful alt text for accessibility reasons but also provide meaningful alt text because we can use it inside a data validation or a slicer it would be nice if we could use the images themselves inside these objects but we can't let the minut it therefore the alt text is the best option we have and that's it in this video we've seen awesome ways that we can use the image function that you might not have thought of before we used image generating apis we used images from SharePoint and one drive and we also saw that we can use images dynamically using a data validation list if you like this video don't forget to subscribe and then click there for more Excel goodness thanks for watching I'll catch you next time

Channel: Excel Off The Grid

Views: 5,921

Keywords:

Id: f6y7xmPqfi4

Length: 10min 15sec (615 seconds)

Published: Thu Jul 04 2024