Solution: “Feature requires a browser that supports ActiveX controls” when importing spreadsheet to SharePoint

If you’re trying to import a spreadsheet as a new list in SharePoint, you’ll need to use a browser that supports ActiveX controls or you’ll get the error:

“This feature requires a browser that supports ActiveX controls.”

Chances are you’re running a browser other than IE, or you’re running IE version 11. ActiveX controls are not supported in IE11, or most browsers. We can work around this error message by having IE pretend it’s version 10 momentarily.

  1. Open SharePoint in Internet Explorer (IE)
    • Quick access: Hit Windows key, type IE, hit enter
  2. Press F12 to open Developer Tools and select the Emulation Tab
  3. Change Document mode from 11 to 10 (supports ActiveX). Page will reload so you can try again in version 10.

Now try to import the spreadsheet again and it will work fine, opening your spreadsheet and prompting you to select the table or relevant data for import.

Preserve Excel hyperlinks when saving as PDF

If you have hyperlinks in your excel files and need to save your file as a PDF, you’ve probably run into the error in which your hyperlinks in the PDF output are inactive.

To be able to maintain hyperlinks you will need Adobe Acrobat. If you don’t have it, please skip down to the bottom of this post to the “Don’t have Adobe Acrobat?” section.

If you do have Adobe, it’s quite simple unless you’re using the HYPERLINK() formula (see below). Just use the Acrobat add-in to save as Adobe PDF.

File –> Save as Adobe PDF –> Convert to PDF. You can also use the Acrobat tab in the ribbon and click “Create PDF”.

Using HYPERLINK() formula

If you’re using the hyperlink formula, as seen below, we’ll need to do some manipulation to our sheet first. Printing to PDF will require that your hyperlinks are properly written before conversion (http… or https…).

To get just the hyperlinks from our formula, we can copy values from Excel and paste into Word, then copy from Word and paste back into Excel (keeping source formatting).

Paste into blank Word document, then copy all values (not the whole table, just the column)
Back in Excel, select the original column values, and paste the new values copied from Word in their place

Now when you save as Adobe PDF, your links will remain active:

Don’t have Adobe Acrobat?

Save the Excel Sheet as a web page and links will work. This isn’t ideal but in a pinch will save you some stress.

Export SharePoint group membership names and email addresses to Excel

Before we start, if you’re just wanting to email all members of the group you can “select all” then choose “E-Mail Users” from the action menu when viewing the group membership within SharePoint. This opens an Outlook window with all the addresses pre-populated.

But, if you still need an excel sheet of membership for another purpose (perhaps to format a sign-in sheet, generate documents with mail merge, share the list with others, etc.) follow these steps:

  1. With the group open in SharePoint, copy the URL and note the ID number at the end of the URL
  2. Open a new excel workbook
  3. From the “Data” tab, select “New Query” –> “From Other Sources” –> “From OData Feed
  4. Paste the group URL in the prompt but delete everything after the site address and replace with the following, replacing “6” with your group’s ID from step 1
  5. Click “Edit” once the group loads so we can choose which columns to keep/delete
  6. Ctrl+click the column headers you want to keep
  7. Right-click a header of a column you’re keeping and select “Remove Other Columns”
  8. Close and Load
  9. You should now see your group membership and email addresses (and any other fields you kept). Save this somewhere and, if updates are made in the future, just click “Refresh all” to bring in new members and remove old.

Splitting an Excel column by delimiter

You’re bound to run into an excel sheet where differing data types are joined together in a single column. For example in the following data dates are combined with event names making it impossible to filter or sort by either date or event. By using Excel’s “Text to Columns” feature, we can separate these two distinct data points.

As long as you have a consistent delimiter (in my case, the pipe character “|”) or a pre-determined fixed width, you can easily move the text following the delimiting character (or exceeding the specified width) to the adjacent column (which I’ve inserted in advance to prevent overwriting existing data in the original second column).

  1. Select column or cells you’re splitting
  2. Select the “Data tab” then “Text to Columns”
  3. Choose to split by “delimiter.” Click next.
  4. If your delimiter isn’t an option, enter it as I’ve done (max 1 character) in the “Other” space. Click next.
  5. You can choose to change data formats here, so I’ve made my new first column date format for example. You can always change column data types later as well.
  6. Finish

Now you can sort and filter correctly on the values you separated.

Quick tip: Prevent #DIV/0! and #VALUE! errors in Excel files

Recently I was helping a client visualize their excel data using Power BI and ran across an error in Power BI when it couldn’t deal with “#DIV/0!” as a cell value in Excel:

“DataFormat.Error: Invalid cell value ‘#DIV/0!’.” 

You get this error in excel whenever you have a calculated formula that is trying to divide by zero or a blank cell. Similarly, you get “DataFormat.Error: Invalid cell value ‘#VALUE!'” typically when your data formats don’t match – like there’s a word in a number-formatted column.

It’s a simple fix: just modify your Excel formula to be wrapped in =IFERROR((YourFormula),0) so that if your formula attempts to divide a value by zero, it returns a value of zero instead of an error code.

=IFERROR(Your_Formula, Value_If_Error)


Solution: Excel drag to “fill” not working – value is copied, formula ignored

A client of mine recently ran into an issue I hadn’t seen before. When she would click a formula cell and drag down to calculate it across multiple rows, it only copied the value. The formulas were correct, but the value being shown was from the original cell:

Continue reading “Solution: Excel drag to “fill” not working – value is copied, formula ignored”