Exporting SharePoint data to an Excel spreadsheet

The connection between SharePoint and Excel is a handy one to know about.

SharePoint has a feature that lets you export data to an Excel spreadsheet.

You can then save that Spreadsheet back into SharePoint.

Why is this handy?

It lets you create a read-only view of data that comes from a list you manage.

Why would you ever need this?

I use this feature periodically when I want to run analysis on data from a SharePoint list. For the categories in a community site, I use it to provide me a reference sheet with category IDs so I can create custom views.

OK, so how do I do it?

Exporting to Excel can be done from most lists, and the feature can be found under the LIST tab.

It will initially want to open the file in the desktop version of Excel.

Then when you go to save it, choose the Your Company – Sites option and paste in the address for the document library where this file should live. If you don’t have a document library on your site, you will need to add one.

Once you have saved the file back on to SharePoint, you can then use the file link to connect it to a page, or a discussion item.

Advertisements

Removing pictures from Excel file

There is a report that I run out of SharePoint which includes a lot of icons. When I copy the data into Excel, it also copies the icons. I don’t need the icons when I am manipulating the data.

The way to remove all the icons from the spreadsheet at once, I use the Go To command (Ctrl + G), then choose Special, then Objects.

That selects all the images, and the delete or backspace key will remove the images from my worksheet.

Delve gets a new look

A few months ago, Microsoft rolled out a new look for Delve. Overall, it’s a little more modern looking and it’s clear now that you can Skype Chat with your fellow coworkers. This feature was hidden in the previous look under the “message” button.

You now also have the ability to change your header image. There’s no way as of yet to upload your own header, but if you don’t like the yellow flowers, you at least have some options.

Microsoft has created a nice reference page for Delve and how to use it, so if you would like to know more about this tool, I suggest you look there for some basics.

One feature – which I liked – the folder link, seems to be hidden under the file name. Also, the board links appear to be missing.

However, these links are only missing when you’re viewing the summary dashboard view. When you click on the See All link, you are returned to the content card view, which shows all the pieces that we wanted to see.

Editing Email Messages and adding the feature to your Quick Access Toolbar in MS Outlook

Have you ever received an email with a subject line of “Help”, “Question”, or even one without a subject line at all? Do you wish that you could change the subject of those emails to something more useful?

Besides “Help” or “Question”, I get quite a few emails that just say “hi” in the subject line, or “could you….” or something along those lines. Sometimes they are one-off emails so it’s not too much of a problem, but other times the emails actually need to be kept, and “Question” just isn’t enough information for me to be able to refer back to or find the email at a later time.

Changing the Subject Line in an email is actually very easy:

  • Open the email in its own window by double-clicking the email in your inbox.
  • Place the cursor in the subject line and make your edits
  • Save the email.

There may be times however, that you also need to edit the actual email that you received. Maybe there’s an incorrect date that you need to fix, or maybe someone misspelled your (or someone else’s) name. You could always forward it to yourself and save the forwarded version, but Outlook has a better feature called Edit Message.

This Edit Message feature is hidden under the Actions dropdown in the Move section of the MESSAGE tab when the email is open in its own window.

Because I use this feature fairly frequently, I added it to my Quick Access bar in Outlook.

Here’s how:

  • In the main Outlook Window, choose the FILE tab
  • Then choose Options
  • Choose Quick Access Toolbar
  • From the Commands Not in the Ribbon dropdown, choose Edit Message and click Add
  • Click OK to save

Other ways I use the Edit Message feature:

Oftentimes I get SharePoint related emails after a string of emails has been sent between other people, and then when SharePoint comes up, I get included at that point. Oftentimes the email subject line is referring to something that only the committee people know about, and has nothing to do with my scope of knowledge.

Once I figure out what it is I’m supposed to help with, I take the pertinent content from the email string and place it at the top of the email that I received, so that I don’t have to search for it again. Alternatively, if there’s a lot of information, I bold or color portions of the message so that they stand out for me.

I then change the subject line to help me find the email again later. I usually include the committee or department name in the subject as well as a better summary for what the question was.

Benefits of using SharePoint for communications over email

Our Intranet central communication hub contains a calendar, announcement list, and discussion list will become the “water cooler” for the division and that it will become a source of more open dialog between division employees.

We used to use a passive “listserv” way of communicating, which meant that everyone received all the emails. We want to create a more active approach where people retrieve their own information.

I for one am looking forward to having this information distilled to me in a more useful format, and here specifically is what I like about the different apps and the information they provide.

Announcements

Important information will now be front-and-center on the page to which I can refer back any time I want, without needing to hunt for that all-important email.

Calendar

Important division event dates will be prominently available to me so that I can see at a glance when things are happening division-wide. I can also connect that calendar to my outlook and compare it with my own.

Discussion List

This can be a double-edged sword as we hope that more content will be posted to the discussion list than is currently being sent to listserv, which will create two situations simultaneously:

  • GOOD – we have more collaboration and dialog and learn more about what’s happening in the division, and
  • NOT SO GOOD – we get more information served to us which may or may not be relevant to our work or interests.

Here are some ways you can customize your discussion list experience:

  • There is a specific view for “featured items” which will show only the most important information relevant to the division. This should give prominence to certain topics and help sort through some of the clutter that we receive on a daily basis.
  • All the content will now be sorted and categorized by department or committee so if I want to learn about upcoming training opportunities, I can choose the Human Resources category and see all the notes posted by that department. If I want to know about the latest developments in diversity, I can visit the Diversity and Inclusive initiatives page or choose the specific departments and committees involved with that, and I will be able to see everything tied to that topic. I won’t have to hunt or search emails anymore to find the information I want.
  • I can set alerts on the discussion list to receive content when I want to receive it. If I like emails as they happen, I can choose that. If I like emails once a day, I can choose that. If I want to only receive information once a week, I can do that too.
  • I can create my own Views to only show things that are important to me, and then set different alerts on those views according to my preferences. This way, I can choose to receive a digest of all discussion items once a day, and receive immediate emails on the departments or committees of interest to me.

If we all embrace the use of the central communication hub, then we can limit the listserv emails to only those topics that are truly important for the whole division.

OneNote and Your Phone

Typing up notes after a meeting can be pretty tedious. If I had a stylus with my device I might write notes on my laptop, but I find I write shorthand faster on paper than I do using an electronic device. Afterwards, typing things up afterwards is annoying and time consuming.

One way to make this process much faster, is to use the microphone on your mobile phone. My android phone voice recorder does a decent job of dictation. I had once used it to dictate a 7-page printed document into electronic format.

I have the OneNote app on my Android, and when I want to dictate meeting notes, I create a new page and hit the microphone button. Then I read the notes that I just took from the meeting and OneNote transcribes them for me. It’s not perfect, and you have to spend a bit of time formatting, but it does beat having to type every word.

Transparent Shapes in PowerPoint

When I make screen captures, there are times that I would like to add a highlight to a graphic. There may be some tools within OneNote Clipping Tool that I am not familiar with yet (be on the lookout for a new post in the future if that’s the case), so I have been using PhotoShop as a work around.

I realize not everyone has PhotoShop, and I also don’t always want to have to save out a screen shot, open in PhotoShop, add shape, use the “Darken” filter to create transparency, and then save out the image again.

Sometimes I just want to copy and paste the screen shot and then highlight it in PowerPoint using a transparent shape.

How to create a transparent shape

Create a shape from within the Home tab, set the Shape Outline to No Outline and set the Shape Fill to the color of your choosing.

The transparency feature is actually somewhat hidden:
In the Shape Fill menu, click More Fill Colors.

At the bottom of that dialog box, is a transparency slider. While you slide it, it won’t change the shading in the view, but once you click ok, you’ll see you have a transparent shape.