I love learning new things. Today I was using a CSV export of data in Excel and was just using Excel to reassemble the data. At one point it resembled something like:
This is, of course, after some manipulation. Basically I removed the parts of a URL column expanded by delimiter (“/”) I didn’t want. This left awkward gaps and all I wanted was to quickly delete the gaps and shift contents of remaining cells to the left to sort of “reconstruct” each row without gaps. Since they didn’t line up in neat columns, I needed a different method.
If you’re using SharePoint online (O365) you won’t run into this issue. But for those of you, like me, who are still on SharePoint 2013 or 2016 you may have tried to use “Export to Excel” on a SharePoint list or library and received the following error message:
To export a list, you must have a Microsoft SharePoint Foundation-compatible application.
Clicking “OK” only redirects you to page that is most definitely not an Excel export:
Using Internet Explorer to open and export your SharePoint list might be the simplest way to export your list. However, some are unable to use IE.
If you can’t download or use Internet Explorer (IE) you can still get your lists exported to Excel by working in reverse. Rather than export from SharePoint we are going to open Excel and import from SharePoint.
1. Open a blank workbook in Excel
2. Navigate to the Data ribbon –> Get Data > From Other Sources > From SharePoint List
Note: There are two SharePoint list options under Get Data. Do not select From Online Services > From SharePoint Online list (this option would be for O365 only and O365 users will not need this post’s workaround).
3. Paste the URL to your SharePoint site. You can paste the URL to the list you’re wanting to import, but delete everything after the site’s name in the URL.
4. Click OK.
5. Select Windows and leave credentials as the default “Use my current credentials” unless you have reason to access the list as another account (perhaps a service account which may be able to retrieve all items even with restricted item-level permissions).
6. Click Connect.
7. Find the name of the list you want to import, select it, and click Load.
8. Depending on the size of your list, this may take a while. Once imported, you’ll find all of your data as well as some SharePoint metadata associated with your list items.
While not ideal, it doesn’t take long to do and you do get the result you’re looking for (with a bit of cleanup, deleting unnecessary columns and such).
Some things to keep in mind:
Exporting from SharePoint using IE allows you to export a view.
Importing from SharePoint using Excel imports every single list item and column, regardless of view.
You’re only able to import items to which you at least have view permissions.
Since Excel doesn’t have the same set of robust text transformation tools as Word, we need creative ways to get the same effects sometimes. When it comes to auto-capitalizing whole words or first letters of words, there are two functions you can use:
=UPPER() will capitalize the entire cell contents
=PROPER() will capitalize first letters of each word in a cell
In the GIFs below, you’ll notice I demonstrate both. The steps for both are exactly the same, so just pick the formula/function you need.
Since nobody wants to add another column to a sheet unnecessarily, we’ll just temporarily create a column for the calculation, copy the result, then delete it. You may, alternatively, wish to just hide it for later use or keep it alongside the original for a purpose.
Insert column and formula
Place formula of choice in top cell of column and drag down (lower right-hand box/handle of cell) or press enter if in table format as seen below
Copy new values over originals and delete temporary column
Next we’ll use the “Paste values” feature to remove the formula dependency of the resulting data and keep just the newly formatted text.
Paste (special) values only over original column values to replace them with the correct capitalization version
This morning I pasted a list of values into Excel in which the pattern consisted of a “good value” followed by that same value with a minor adjustment. The second, near-duplicate value wasn’t useful to me so I wanted to filter out all even rows so I’d just have the first of each near-duplicate.
A simple trick in Excel allows you to remove duplicate values from a column in Excel. Depending on your version of Excel, yours may look a bit differently but the process is nearly the same. Below, I’m using Excel 2016.
1. Select the column, or the values from which you’re removing duplicates. Note: You could also just start with step 2, and then select the relevant column(s) from the resulting dialog. 2. Go to Data > Remove duplicates (in the Data Tools panel)
3. Confirm if your column has a header (column title or not) and click OK.
4. Excel will confirm the number of duplicates found and how many remain. Click OK.
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.
Open SharePoint in Internet Explorer (IE)
Quick access: Hit Windows key, type IE, hit enter
Press F12 to open Developer Tools and select the Emulation Tab
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.
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).
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.
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:
With the group open in SharePoint, copy the URL and note the ID number at the end of the URL
Open a new excel workbook
From the “Data” tab, select “New Query” –> “From Other Sources” –> “From OData Feed“
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 /_api/Web/SiteGroups/GetByID(6)/Users
When prompted for login credentials you’ll use “Windows” for on-prem/server and “Organizational account” for O365.
Click “Edit” once the group loads so we can choose which columns to keep/delete
Ctrl+click the column headers you want to keep
Right-click a header of a column you’re keeping and select “Remove Other Columns”
Close and Load
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.
Updated 8/8/2019: Added step 5 to show the login credentials prompt.
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).
Select column or cells you’re splitting
Select the “Data tab” then “Text to Columns”
Choose to split by “delimiter.” Click next.
If your delimiter isn’t an option, enter it as I’ve done (max 1 character) in the “Other” space. Click next.
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.
Now you can sort and filter correctly on the values you separated.
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.