Surprising Excel: Tackling Three Tasks I Never Expected It Could Handle

While many view Excel merely as a tool for calculations and chart creation, there’s a surprising versatility to its capabilities. I’ve learned that it can be employed to streamline tedious Windows tasks, generate dynamic QR codes, and gather country-specific information.

Convert a Spreadsheet into a Folder Generator

Flash Fill Automates Command Generation

Creating numerous folders manually can be a labor-intensive task. However, if you already have the folder names in a spreadsheet, you can easily arrange them in Excel and let Windows create the folders in mere seconds.

This technique transforms Excel into a rapid command generator for Windows. By utilizing Flash Fill alongside a simple batch file, you can swiftly produce the necessary commands. The batch file harnesses the MD command, which stands for “make directory,” to create new directories in Windows.

To start, organize your folder names in Excel:

1. Open a new workbook, and input or paste the folder names into column A beneath an appropriate header.
2. In column B, label the header as Folder Name, then write the first folder command using the format MD [Name], replacing spaces in folder names with underscores (_). For example, for Matt Turner, you would write MD Matt_Turner.
3. Select your created folder command and press Ctrl+E to activate Flash Fill, which will automatically complete the remaining folder commands based on your pattern.

Next, create the folders in Windows:

1. Copy the generated commands from column B, making sure to exclude the header row.
2. Open a new Notepad document, and paste the copied commands into it.
3. Hit Ctrl+Shift+S to bring up the Save As dialog.
4. Select the location where you want the folders created, name the file with a .bat extension (e.g., US Soccer.bat), and click Save.
5. In Windows File Explorer, locate the saved batch file and double-click it to execute.

Within seconds, the folders you prepared in Excel will appear as a structured hierarchy on your computer.

Once the folders are created, you can close Excel and securely delete the temporary batch file from your system.

Generate Dynamic QR Codes

The IMAGE Function Keeps QR Codes Synced

Creating a single QR code is straightforward, but maintaining it with updates can be cumbersome—you typically need to regenerate the code each time the linked text or URL changes. Excel can help simplify this process by allowing dynamic QR codes to be displayed directly within the worksheet.

This approach utilizes a free online service called QR-Server (from GOQR) to generate the QR codes, while Excel constructs the request and showcases the resulting code. The QR service can create a code from any data provided after data= in this link:

https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=

This method effectively turns the URL into a request: any information after data= becomes embedded within the QR code. The IMAGE function in Excel enables the display of the retrieved image, while ENCODEURL ensures that spaces and special characters are converted into a web-friendly format.

The complete formula, assuming your data is in cell A2, looks like this:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data="&ENCODEURL(A2))

Follow these steps to execute:

1. Input the text or URL you wish to encode into cell A2.
2. In an adjacent cell, paste the formula, adjusting the reference if necessary.
3. Review the security notice that pops up, stating that Excel will send the cell contents to QR-Server for code generation. Proceed with Paste everything only if you’re comfortable with sharing that information, considering the service’s privacy policy.
4. Hit Enter to generate the QR code within the cell.
5. Adjust the row height and column width of the QR code cell for better visibility, or modify the 150x150 size in the URL for a larger image.

Now, if you edit the original text or URL, such as changing a website or adding keywords, you’ll see the QR code update instantly.

Retrieve Country Facts Without Leaving Excel

The Geography Data Type Simplifies Research

Switching between a web browser and a spreadsheet while building a reference table can be inefficient. If you’re compiling data for a trip or international project, Excel can automatically retrieve relevant information using linked data types, significantly reducing your reliance on search engines by transforming country names into detailed data entries.

Rather than treating country names as static text, Excel can pull related data from Microsoft’s resources. Once Excel identifies the names as linked data types, it can fill in fields like capital cities, populations, currencies, and land areas without the need for manual lookups.

To implement this, follow these steps:

1. Enter a list of country names in a column.
2. Highlight the cells with these names and navigate to the Data tab on the ribbon.
3. Expand the Data Types dropdown.
4. Select Geography.
5. After Excel identifies the countries, click the Insert Data button next to the selected cells, and choose a field—like Population, Capital, or Currency—to insert it into your worksheet.

You can repeat the last step to add as many fields as necessary. Each new data selection generates an additional column, enabling you to create a comprehensive reference table rapidly.

Discovering the Hidden Features of Excel

Excel is replete with functionalities that can address challenges you might not expect a spreadsheet to overcome. These instances are just the tip of the iceberg—beneath the familiar layout lies a treasure trove of overlooked features awaiting exploration.