I didn’t think Excel could handle these 3 tasks—but it did

Excel is still full of surprises

You probably think you know Microsoft Excel pretty well. You’ve used it for budgets, charts, maybe even some basic formulas. But I recently discovered three things Excel can do that genuinely blew my mind. I didn’t think it was possible—yet here we are.

Let me walk you through each one. You might end up using Excel in ways you never imagined.

1. Excel can generate unique random values without duplicates

Need a list of random numbers, codes, or IDs? The old way was to use RAND() or RANDBETWEEN(), but those often produce duplicates. That’s not acceptable for things like raffle tickets, sample IDs, or password resets.

I assumed you’d need a macro or a third-party add-in. But Excel has a hidden function: RANDARRAY. Combined with UNIQUE, it creates a list of random values with zero repeats. Here’s a quick example:

=UNIQUE(RANDARRAY(10, 1, 1, 100, TRUE))

That formula gives you 10 unique random integers between 1 and 100. No duplicates. No VBA. Just clean, dynamic data. It’s perfect for Excel random number generation in real-world scenarios.

How it works

• RANDARRAY generates an array of random numbers (rows, columns, min, max, integer/ decimal).
• UNIQUE filters out any duplicates automatically.

This combo is incredibly useful for simulations, lottery draws, or assigning random IDs to participants. I use it all the time now.

2. Excel can pull live data from the web automatically

I always thought web scraping required Python or a dedicated tool. But Excel’s Power Query (Get & Transform Data) can fetch live data from websites, APIs, and even PDFs.

Here’s what I did: I set up a query to pull the latest exchange rates from a public API. Once configured, Excel refreshes the data with one click—or automatically on file open. No coding. No manual copy-paste.

Steps to try it yourself

1. Go to Data > Get Data > From Other Sources > From Web.
2. Paste the URL of a table or API endpoint.
3. Use the Power Query Editor to clean and shape the data.
4. Load it into your worksheet.

This is a game-changer for anyone tracking stock prices, weather data, or sports stats. Excel becomes a live dashboard, not just a static spreadsheet.

3. Excel can send emails directly from a worksheet

This one really surprised me. I needed to send personalized emails to a list of clients—each with a different subject line and body. I assumed I’d need Outlook mail merge or a third-party service.

But Excel has a built-in feature: Mail Envelope (part of the legacy Send to Mail Recipient tool). It’s not new, but most people don’t know it exists. You can also use VBA to automate the process with a simple macro.

A quick VBA example

Sub SendEmail()
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.CreateItem(0).Display
End Sub

With a bit of tweaking, you can pull recipient names, subjects, and body text directly from cells. No extra software needed. It’s a huge time-saver for Excel email automation tasks.

I now use this to send weekly status reports to my team—all from one spreadsheet.

Why these tasks matter for everyday users

These three examples prove Excel is far more than a number cruncher. It can generate unique data, pull live information from the web, and even act as a basic email client. For small businesses, freelancers, or anyone managing data, these features eliminate tedious manual work.

You don’t need to be a programmer to use them. Just a willingness to explore Excel’s hidden menus and functions. Trust me—it’s worth the effort.

What surprising things have you discovered in Excel? Share your finds in the comments below.