Generating QR code in Excel (easy guide with full methods)
In today’s digital age, QR codes have become an essential tool for sharing information quickly and efficiently. This guide explores different techniques to create QR codes directly within Excel, whether you are using the latest Excel 365 or earlier versions. By the end of this article, you will be well-equipped to integrate QR codes into your Excel projects seamlessly.
- Using Excel Built-in Add-ins to generate QR codes one by one
- Using Kutools for Excel to generate QR codes in bulk
Video
Generating QR codes in Excel 365
Excel 365 enhances its capabilities with functions like IMAGE and LAMBDA, allowing users to generate QR codes dynamically. This section will show you how to generate QR codes using the IMAGE or LAMBDA functions in Excel 365.
Using the IMAGE function
In this example, we are going to use the IMAGE function to generate QR codes containing links to the following websites.
- Select a cell where you want the QR code to appear. Here I select D3 to display a QR code containing a link to the Extendoffice website.
- Enter the following formula and press Enter to get the QR code.
=IMAGE("https://quickchart.io/qr?text=" & ENCODEURL(C3))
- Select this formula cell, double click the bottom right corner (the green square) of the cell to get the rest of QR codes.
- In the formula, please replace C3 with the cell reference that contains the text you want to encode in the QR code.
- The QR code will automatically update if the content in the referenced cell changes.
- This formula can also be used to generate QR codes in Google Sheets.
- QuickChart.io provides a simple API to generate QR codes. The base URL for generating QR codes is https://quickchart.io/qr?text=.
Using the LAMBDA function
For many Excel users, remembering complex formulas can be challenging. In this section, I'll demonstrate how to utilize the LAMBDA function to create a custom and reusable function based on the previously mentioned IMAGE formula. This will allow you to use a simpler, user-defined function to generate QR codes in Excel in the future.
Step 1: define a name using the LAMBDA function
- Go to the Formulas tab and click Define Name in the Defined Names group.
- In the popping up Edit Name dialog box, you need to configure as follows.
- In the Name box, give a name to the LAMBDA formula. Here I name it as fnGetQRCode.
- In the Refers to box, enter the following formula.
=LAMBDA(text, IMAGE("https://quickchart.io/qr?text=" & ENCODEURL(text) & "&size=150"))
- Click OK to save the defined name. See screenshot:
Notes:
- In the formula, 150 is the default pixel size for the QR code image. You can adjust this value as needed, but remember that the actual display size of the generated QR code in Excel will be determined by the size of the cell.
- To know more about the LAMBDA function, visit this page: Excel LAMBDA Function (365).
Step 2: Apply the user-defined function to generate QR codes
- Select a cell where you want the QR code to appear. Here I select D3 to display a QR code containing a link to the Extendoffice website.
- Enter the following formula and press Enter to get the QR code.
=fnGetQRCode(C3)
- Select this formula cell, double click the bottom right corner (the green square) of the cell to get the rest of QR codes.
Notes:
- In the formula:
- fnGetQRCode is the name you defined in step 1.
- Please replace C3 with the cell reference that contains the text you want to encode in the QR code.
- The QR code will automatically update if the content in the referenced cell changes.
- In the formula:
Generating QR codes in all Excel versions
For users without access to Excel 365, there are still reliable options to generate QR codes through add-ins and external tools, making this functionality accessible across all versions of Excel.
Using Excel Built-in Add-ins to generate QR codes one by one
Excel add-ins like QR4Office provide a robust alternative that is compatible across multiple versions of Excel. This method allows for detailed customization of QR codes and is ideal for users who need to generate codes sporadically within their documents.
Step 1: Install the QR4Office addin-in
- Go to Insert > Get Add-ins.
Note: If you don’t see the Get Add-ins command under the Insert tab in the ribbon, you will need to add it manually. Below are the steps to add this command to the Quick Access Toolbar:
- Click the Customize Quick Access Toolbar drop-down arrow and select More Commands from the menu.
- In the Word Options window:
- Select Commands Not in the Ribbon from the Choose command from drop-down list.
- Find and select Get Add-ins in the left commands list.
- Click the Add button (the Get Add-ins command will be added to the right pane).
- Click OK to save the changes. See screenshot:
- The Get Add-ins command is now added to the Quick Access Toolbar. You can click on it to open the Office Add-ins store.
- In the Office Add-ins store:
- Type "QR4Office" in the search box and press Enter.
- Once the QR4Office add-in appears, click the Add button to install it.
- In the following prompt box, click Continue.
Step 2: Use the QR4Office add-in to generate QR code
Once installed, the QR4Office pane will automatically open on the right side of your workbook, allowing you generate QR code as follows.
- Select the cell containing the text or link you want to encode in the QR code.
In this case, to enclose a link to the Extendoffice website, I select cell C3 containing the URL.
- Click the Copy current document selection button to transfer the cell content into the QR4Office fields.
Tip: Alternatively, you can select either URL or Text type from the drop-down list and manually enter the required information into the text box.
- In the Options section, you can optionally customize the QR code's appearance by selecting the color, background, and size, or retain the default settings.
- Select the destination cell for the QR code. Here I select D3.
- Click Insert to place the QR code into the selected cell. See screenshot:
- Repeat the above 5 steps to generate other QR codes.
Using Kutools for Excel to generate QR codes in bulk
Also an add-in, Kutools for Excel offers a distinct advantage over QR4Office with its ability to batch generate QR codes across a selected range. The Insert QR Code feature of Kutools for Excel is particularly valuable for users who need to create multiple QR codes efficiently. It automates the process across multiple cells, saving time and minimizing manual input errors.
After installing Kutools for Excel, go to select Kutools > Insert > Insert QR Code to open the Insert QR Code pane, then you can generate QR codes in bulk as follows:
- In the Data Range field, select the range of cells containing the text or link you want to encode in the QR code.
- In the Insert Range field, select a range of cells to place the corresponding QR codes.
- In the Options section, you can optionally customize the QR code's appearance by specifying the size, line color, etc., or retain the default settings.
- Click the Generate button to generate QR codes for all selected cells simultaneously.
Generating QR codes in Excel is straightforward, whether you use the advanced capabilities of Excel 365 or rely on add-ins available for all versions. By following the steps outlined in this guide, you can efficiently integrate QR codes into your Excel sheets, enhancing the interactivity and accessibility of your data. Explore these methods to find the one that best suits your needs and streamline your workflow in new and innovative ways. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.
Related Articles
Insert barcode into cells based on certain value
If you need to create barcodes in Excel, the methods in this tutorial can help you with this task.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Table of contents
- Video
- Generating QR codes in Excel 365
- Using the IMAGE function
- Using the LAMBDA function
- Generating QR codes in all Excel versions
- Using Excel Built-in Add-ins to generate QR codes one by one
- Using Kutools for Excel to generate QR codes in bulk
- Related Articles
- The Best Office Productivity Tools
- Comments