Are you tired of manual data entry and boring spreadsheets? Do you want to hook the audience to your Excel report by adding images, comments, URLs, and graphs? Learn here the best automatic ways to add external content to Excel using Excel VBA scripting and Office Scripts.

Excel VBA and Office Scripts make Excel the playground for basic to advanced automation. Such automation extends from the premise of Excel to Power Automate so you can run a cascade of Microsoft apps to perform tasks in a single click.

This Excel tutorial explores the art of seamlessly incorporating images, insightful comments, dynamic URLs, and more, elevating your spreadsheets from monotonous to magnificent. Learn step-by-step how to wield the power of these tools not only to organize and visualize your data but also to add layers of context and interactivity.

Reasons to Add Images, Comments, URLs, Etc. to Excel

• Images provide a quick and intuitive way to convey information, making data interpretation easier.
• Graphs and charts visually explain the patterns and insights in data.
• Comments enable detailed explanations, clarifications, or instructions directly within cells.
• Comments can also serve as documentation, preserving insights and knowledge for future users.
• URLs facilitate access to external resources, supporting thorough research and referencing.
• Hyperlinks to websites or intranet pages create interactive reports with external data integration.
• Images, links, and comments combine to create comprehensive dashboards for data tracking.

How to Add Image in Excel

The Insert Picture From tool inside the Illustrations command section in the Insert tab is the standard way to add an image to your Excel worksheet.

However, this process is manual and requires you to perform many clicks to add more than one picture to the Excel sheet.

Suppose, you’d like to add multiple images in a flash and also automate the process, then you need to use any of the following methods:

Using Excel VBA

The VBA script mentioned below helps you to create an automated content management system in Excel to manage images of your blog, website, graphics designing business, etc.

Provided that the image names on your Excel worksheet match the image names in a local drive on your PC, the script will automatically import the relevant images to the right of the source data.

For example, if you’ve got image names like Services, Registry, and Group Policy in cell range A2:A4, the code will insert the respective images from B2:B4.

Find below the code and steps to follow to implement the code:

1. Call the Excel VBA Editor by pressing Alt + F11.
2. Click the Insert button and choose Module.
3. Inside the new module, copy and paste the following Excel VBA script:

Sub AddImagesBasedOnTextWithDynamicRange()
    Dim ws As Worksheet
    Dim imgPath As String
    Dim img As Picture
    Dim cell As Range
    Dim destRange As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    Set destRange = ws.Range("B2:B4") ' Change the destination range as needed
    
    For Each cell In ws.Range("A2:A4")
        imgPath = "D:\Images\" & cell.Value & ".jpg" ' Assuming images are in JPG format
        
        If Dir(imgPath) <> "" Then ' Check if image file exists
            Set img = ws.Pictures.Insert(imgPath)
            With img
                .Left = destRange.Cells(cell.Row - 1, 1).Left
                .Top = destRange.Cells(cell.Row - 1, 1).Top
                .Width = destRange.Cells(cell.Row - 1, 1).Width
                .Height = destRange.Cells(cell.Row - 1, 1).Height
            End With
        End If
    Next cell
End Sub

4. Click the Save button and choose Yes on the pop-up that follows.
5. Close the Excel VBA Editor.

Now, you need to run the code. Here’s how:

1. Press Alt + F8 to call the Macro dialog box.
2. Click the AddImagesBasedOnTextWithDynamicRange macro.
3. Hit Run to execute.
4. Excel will import and paste the images into their respective cells by matching the names on the input data cell range A2:A4 and the image file names in the local drive.

Here are some quick tips to modify the code to work for you. You’ll also find important comments within the VBA script.

• "Sheet1" must be changed if your Excel worksheet’s name is different.
• "A2:A4" represents the input dataset. The values in this cell range tell Excel which images to import. So, change according to your own worksheet.
• "B2:B4" represents the destination for the imported images. Hence, modify the cell range according to your choice.
• Images imported in this tutorial are all in the JPG format. So I used the ".jpg" code in the script. If your files are in PNG format, use ".png" instead.

Using Office Scripts

Office Scripts is a much more advanced automation tool than Excel VBA. You can link up Office Scripts codes with Power Automate to take your Excel sheet data to other apps.

Find below the code and steps to use Office Scripts in this situation:

1. Click the Automate tab.
2. Click New Script in the Scripting Tools block.
3. In the Code Editor, copy and paste the code:

async function main(workbook: ExcelScript.Workbook) {
    // Fetch the image from a URL.
    const link = "https://raw.githubusercontent.com/OfficeDev/office-scripts-docs/master/docs/images/git-octocat.png";
    const response = await fetch(link);

    // Store the response as an ArrayBuffer, since it is a raw image file.
    const data = await response.arrayBuffer();

    // Convert the image data into a base64-encoded string.
    const image = convertToBase64(data);

    // Add the image to a worksheet.
    workbook.getWorksheet("WebSheet").addImage(image);
}

/**
 * Converts an ArrayBuffer containing a .png image into a base64-encoded string.
 */
function convertToBase64(input: ArrayBuffer) {
    const uInt8Array = new Uint8Array(input);
    const count = uInt8Array.length;

    // Allocate the necessary space up front.
    const charCodeArray = new Array(count) as string[];

    // Convert every entry in the array to a character.
    for (let i = count; i >= 0; i--) {
        charCodeArray[i] = String.fromCharCode(uInt8Array[i]);
    }

    // Convert the characters to base64.
    const base64 = btoa(charCodeArray.join(''));
    return base64;
}

4. Click the Save script button.
5. Click Run to add an image in Excel from the website source.

In the above code, replace the code element "https://raw..." with the source URL if you need to insert another image.

How to Add Comment in Excel

To enter a comment in an Excel cell or cell range, you might be using the Insert Comment command in the Comments block on the Insert tab. However, there are more intuitive and less time-consuming ways that you must learn to level up your Excel game.

Find below the methods to automate the commenting process on an Excel worksheet:

Using Excel VBA

The following Excel VBA code will show a pop-up message box to enter a comment for each student and their score in the Pass/Fail column. You can repurpose the code for any other situation like sharing feedback to employees before appraisal, sharing feedback to contractors, and so on.

Find below the code you can use:

Sub AddCommentsWithPassFail()
    Dim ws As Worksheet
    Dim namesRange As Range
    Dim scoresRange As Range
    Dim passFailRange As Range
    Dim cell As Range
    Dim comment As String
    
    Set ws = ThisWorkbook.Sheets("Sheet3") ' Change to your sheet's name
    
    ' Set input cell ranges for names, scores, and pass/fail status
    Set namesRange = ws.Range("A2:A6")
    Set scoresRange = ws.Range("B2:B6")
    Set passFailRange = ws.Range("C2:C6")
    
    For Each cell In namesRange
        ' Display message box with student name, score, and pass/fail status
        comment = InputBox("Enter comments for " & cell.Value & " (Score: " & scoresRange.Cells(cell.Row - namesRange.Cells(1, 1).Row + 1, 1).Value & ", Pass/Fail: " & passFailRange.Cells(cell.Row - passFailRange.Cells(1, 1).Row + 1, 1).Value & "):")
        
        ' Write comment as Excel cell comment in the respective pass/fail cell
        With passFailRange.Cells(cell.Row - passFailRange.Cells(1, 1).Row + 1, 1)
            On Error Resume Next
            .comment.Delete ' Delete existing comment if any
            On Error GoTo 0
            If comment <> "" Then
                .AddComment comment
            End If
        End With
    Next cell
End Sub

In the above VBA script, "C2:C6" is the destination for the comments. So, customize this cell range in the script according to your own worksheet. Also, you must modify "Sheet3" to a suitable worksheet name as per your workbook.

Cell ranges "A2:A6" and "B2:B6" are there in the code as additional data for the current data analysis. You can repurpose these cell ranges according to your workbook.

To implement the script, follow the steps outlined previously in this article.

Basically, here’s what you’ll experience when running the above Excel VBA script:

Upon running it, you get the above pop-up to enter a comment in text format for the student’s name, their scores, and pass/fail status.

You enter a comment and press OK. Then, the next pop-up appears for the next student. This way, the pop-up keeps appearing as long as there are data in columns A and B.

Note: Don’t run the code repeatedly on the same dataset. The VBA script will rewrite or delete the old comments. Also, take a backup of your workbook before using this VBA script.

Using Office Scripts

Here’s the code that adds comments through C2:C6 cell addresses in Excel:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	workbook.addComment(selectedSheet.getRange("C2"), "OK");
	workbook.addComment(selectedSheet.getRange("C3"), "OK");
	workbook.addComment(selectedSheet.getRange("C4"), "OK");
	workbook.addComment(selectedSheet.getRange("C5"), "OK");
	workbook.addComment(selectedSheet.getRange("C6"), "Feedback given");
}

To customize the comment text, replace the "OK" with your own comment within double quotes.

If you’ve got data beyond C6, then simply create a new code line as the following in the above script:

workbook.addComment(selectedSheet.getRange("C7"), "Feedback given");

How to Add Hyperlink in Excel

The common way to add a hyperlink or URL to a value, string, or cell in Excel is through the Insert Link or Add a Hyperlink command in the Links block on the Insert tab.

If you need to add links to thousands of cells from another dataset, following the above method would take days. I’ve got an automatic and intuitive solution for you. Keep reading!

Using Excel VBA

The Excel VBA script presented below will automatically link URLs or hyperlinks from a source dataset to a target dataset automatically.

Sub hyperlinkcells()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim cell As Range
    Dim hyperlinkText As String
    
    Set ws = ThisWorkbook.Sheets("Sheet4") ' Change to your sheet's name
    Set rng = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
    
    For Each cell In rng
        lastRow = cell.Row
        hyperlinkText = cell.Value
        cell.Value = ""
        
        If ws.Hyperlinks.Count < lastRow - 1 Then Exit For ' Break if there are no more hyperlinks
        
        ws.Hyperlinks.Add Anchor:=cell, Address:=ws.Hyperlinks(lastRow - 1).Address, TextToDisplay:=hyperlinkText
    Next cell
    
    ' Delete column C
    ws.Columns("C").Delete
End Sub

The above script will hyperlink the values in column B, from B2, until the last row that contains values. The code will fetch URLs from column C. Also, the hyperlinking will be done between B2 and C2, B3 and C3, and so on.

The above is an image of the dataset I used for hyperlinking column B values with column C URLs.

Now, find above the image after applying the VBA script to the raw data.

How to Add Graph in Excel

You must have added thousands of graphs in your Excel worksheet as a graduate student and professional working as a data analyst.

The common route to add a graph is by selecting the given data on your Excel sheet and clicking Insert. There, you go to the Charts block to add a chart from a long list of graphs like 2-D Columns, 2-D Line, 2-D Pie, Treemap, and so on.

If you get an Excel workbook containing thousands of worksheets with tabulated data for chart preparation, the manual process won’t be a productive move.

Instead, use these methods to flaunt your Excel skills and become productive:

Using Excel VBA

The following VBA code will loop through all the worksheets, ask you to add a dataset for charts, and choose the chart type by typing its name. Then, press OK to populate the graph instantly.

Sub AddChartsToWorksheetsWithCancel()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim chartType As String
    Dim dataRange As Range
    Dim chartRange As Range
    
    Set wb = ThisWorkbook
    
    ' Loop through each worksheet
    For Each ws In wb.Worksheets
        ' Prompt user to select data range
        On Error Resume Next
        Set dataRange = Application.InputBox("Select data range for chart in " & ws.Name, Type:=8)
        On Error GoTo 0
        
        If Not dataRange Is Nothing Then
            ' Prompt user to select chart type
            chartType = Application.InputBox("Select chart type for chart in " & ws.Name & " (Column, Pie, Line, etc.):")
            
            ' Create chart
            Set chartRange = dataRange.Offset(1).Resize(dataRange.Rows.Count - 1)
            CreateChart ws, chartRange, chartType
        End If
    Next ws
End Sub

Sub CreateChart(ws As Worksheet, chartRange As Range, chartType As String)
    Dim cht As ChartObject
    Dim chtTop As Double
    Dim chtLeft As Double
    Dim chtWidth As Double
    Dim chtHeight As Double
    
    ' Determine chart position and size
    chtTop = chartRange.Cells(1, 1).Top
    chtLeft = chartRange.Cells(1, 1).Left
    chtWidth = chartRange.Width
    chtHeight = chartRange.Height
    
    ' Add chart to worksheet
    Set cht = ws.ChartObjects.Add(chtLeft, chtTop, chtWidth, chtHeight)
    
    ' Set chart type
    cht.Chart.chartType = GetChartType(chartType)
    
    ' Set chart data source
    cht.Chart.SetSourceData chartRange
End Sub

Function GetChartType(chartType As String) As XlChartType
    Select Case LCase(chartType)
        Case "column"
            GetChartType = xlColumnClustered
        Case "pie"
            GetChartType = xlPie
        Case "line"
            GetChartType = xlLine
        Case Else
            GetChartType = xlColumnClustered
    End Select
End Function

The code will start from Sheet1 and continue until there are worksheets left in your workbook. You can cancel one worksheet and move on to the next as well.

The above image shows how Excel will collect chart data from you.

Then, Excel will also ask you to select a chart type. I’ve typed my chart preference above in the image.

Finally, Excel creates a beautiful and professional-looking chart automatically.

Using Office Scripts

The following Office Scripts code will create a column chart from the data range A1:B9:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Insert chart on sheet selectedSheet
	let chart_1 = selectedSheet.addChart(ExcelScript.ChartType.columnClustered, selectedSheet.getRange("A5:B9"));
}

To customize the above code, you can do the following:

• Replace the data range for the chart by changing the cell reference "A5:B9" according to your own worksheet.
• If you need a different chart type, replace the code element columnClustered with that chart type name.

Conclusions

Now you know how to automate the process of adding additional content to your Excel workbook using Excel VBA or Office Scripts.

You learned the quick methods to add images, URLs, comments, and graphs by using scripting. Use the methods in your own worksheet and comment below about your experience using these Excel skills.