TechBits

Hey, can you stop what you're doing and proofread this for me?

About two months ago or so an ad popped up while I was watching a YouTube video about Grammarly. The ad indicated it can check your spelling and grammar for free! I feel like I’m always asking someone (way smarter than me)to check my work for mistakes, and when I saw this ad I thought I would give it a try to see how well it worked. This article is going to be all me and Grammarly, so be kind in the comments if you find any mistakes, please.

You can download Grammarly from there website for free. They have a version for Microsoft Office and for web browsers. I downloaded both options just to try out. If you happen to write a lot more than I do and would like a more robust version, they do offer a subscription version.

I wrote this in Word with Grammarly turned off until this point. I intentionally misspelled a couple words, which I fixed that both Microsoft Office Spell Checker and Grammarly caught. I also left out a comma (not intentional) between mistakes and please at the end of the first paragraph that Grammarly said should be there.

I’m also getting an alert from Grammarly that it see’s five more mistakes that the Premium version will fix. Not today it won’t! Those are for you to find!

Office 365 login change

You may have noticed recently when you log into Office 365 for email that a prompt with the message “We have a new sign-in experience! Try it now” appears. Sometime late last month Microsoft added this “update”. From what I can see nothing changes other than the login screen, rather than having your user name and password on the same screen you select your user name then another window appears to type in your password. If you don’t like the new look you can revert back…for now. At the login screen click “Go back to the old one” in the bottom right corner. I have a feeling that eventually everyone will be migrated over and you won’t have a choice to go back any longer.

I switched to the updated login on one of the accounts I check on a regular basis that is programed to auto log in a couple weeks ago and I didn’t have to do anything different. I switched this morning on my regular account and I don’t really notice any difference other than the picture that looks like sunny California is gone. I also feel like I’m a better person than I was yesterday too, but that could just be a coincidence.

   

 

Removing duplicates in Excel, again

We've covered a couple of ways to remove duplicates from Excel before, but those methods destroyed the original data.  If you wanted to keep the original data, you needed to save a copy of it somewhere first.  There's a way to filter out the duplicates which leaves the original data in place and has you save the de-duplicated information in another location instead. 

1. Open the file in Excel and elect the information you'd like de-duplicated.  Note: If you don't have a header on your column, Excel will complain about the missing column header.
   
   
   
2. Click on the Data tab (1) at the top of page and, in the Sort and Filter section, click on Advanced (2).
   
   
   
3. An Advanced Filter window will appear.  Since we're wanting to keep the original list, click on "Copy to another location" and click on the button at the right hand side of the "Copy to:" field.
   
   
   
4. This brings up a small "Advanced Filter - Copy..." window.  Chose the column you wish to have the new list copied to and then click on the icon at the end of the field. 
   
   
   
5. You'll be back at the "Advanced Filter" window.  Check the "Unique records only" box and click on OK.
   
6. You now have a new list with the duplicates removed but your original list is still intact.    
   

Sorting lists randomly in Excel

Recently, I needed to sort a list of names in random order. Rather than manually figure out a random sort, I decided to see if Excel could do the sort for me. Guess what - it can! It's a little clunky, but it works.

• Enter the list of names in an Excel column.
• Select the column, right-click and choose Insert to insert a new column next to the list of names.

• Enter the formula =RAND() into the first cell of the new column.
  • (The RAND function will generate a random number in the cell.)

• Copy the =RAND() formula from the first cell to the rest of the cells, until you get to the end of the list of names.
  • (Click on the lower right-hand corner of the cell, and then drag the mouse down to the end of the list.)
• You will now have a column with random numbers.

• Select the column of random numbers.
  • (Go back to the top of the column and click on the column's letter to select the column.)
• Go to Sort & Filter and choose Sort Smallest to Largest.

• You will get a pop-up. Choose Expand the Selection, and click the Sort button.

• The list of names will sort in a random order, based on the numbers that were generated by the RAND function.
  • (Note that you can repeat the Sort & Filter process to generate a new random sort.)

• You can delete the column of numbers after you are satisfied with the name sorting. 

Mystery of the disappearing data

I remember like it was yesterday. Snowflakes were falling out of a lead gray sky to the delight or annoyance of passersby when I got the call. A voice, you know the kind that had maybe seen one too many frustrations as of late, told me that, while they appreciated the spreadsheet, they really needed the details and not just the totals. I sat back in my chair, confused. My report into the murky depths of the thing men call Koha had all the details when I sent it. What had happened? Had something happened to the report between the time I sent it and when they received it and why? Was I going to have to redo the whole thing? Most importantly, when was this attempt to make the story like the start of an old detective story going to stop?

Ahem.

After some investigation, it turns out the problem wasn’t some two bit crook, hardened criminal or even someone in a rubber computer gremlin mask ala Scooby Doo. The details, in fact, had been there the whole time. The problem was, well, me. Or at least the fact that I’d forgotten to expand the subtotals on the spreadsheet.

In Excel, there are some visual cues to know if your spreadsheet is using the subtotal function. To the left of your spreadsheet data, instead of being just the normal row count, there will be a gray area with a number of + or - signs in boxes down the middle and numbers in small boxes across the top. Usually it’s 1-3 at the top but there can be more. Another hint can be the fact that the row numbers skip, though that can also just indicate rows hidden with the “hide” command.

• If you’re seeing + signs, the subtotals are collapsed so you’re only seeing the totals. If the – signs are displaying, the subtotals are expanded to show the details.
• You can expand or collapse individual subtotals by clicking on the individual + or – signs. Clicking on a + sign expands the subtotal to show the details as well while the - sign hides the details and only shows the subtotal.
• You can expand or collapse the all subtotals of that level by clicking on one of the numbers in the boxes at the top of the area. Clicking on the number 1 will show only the grand total, the right most number (in this case 3) will show all of the details. The number or numbers between will show the various levels of subtotals.

In this case, I’d left the spreadsheet on level 2 so the subtotals displayed but the full details were still hidden. Oops!

A few Microsoft Word shortcuts

I use Microsoft Word a lot in my everyday life and there are a few tips I've picked up recently. 

Nothing is more frustrating than when you’re typing away and you touch something that unintentionally moves your cursor to another part of the document. An easy shortcut to get back where you were is so simple.  Just hit the SHIFT+F5 key combination to return to where you were last in your document and continue where you left off. 

There are a lot of shortcuts that I find handy for editing.  The ones I use the most are related to selecting sections of text.  In order to select an entire paragraph, just make three rapid mouse clicks anywhere in the paragraph.  If you wish to select an entire sentence, click anywhere in the sentence while holding the Ctrl key down.

These little tricks have saved me a lot of time and there are many more out there.  Please feel free to share any Microsoft Word tips or shortcuts that you find useful in the comments section. 

Office 365 Contact Lists and Groups Explained

Within Office 365 there are two items found within the People section that I'd like to explain as they are a little confusing. The two items I'm talking about are "Contact lists" and "Groups".  The part that I've found confusing is knowing which one to use when you want to send an email out to a lot of people.  Both of these differ from a patron email list in the fact that an email from it shows that it came from the name of the patron email list and only the list administrators can send out emails to the list.  Whereas email from a "Contact list" or "Group" shows that it came from your personal email address and anyone can send out emails to the list by using Reply All.  So when you're emailing patrons it is best to use an email list.

So what is a "Contact list?"  
A "Contact list" (formerly called a distribution list) allows you to send an email message to all the email addresses in the list at once.  That way you don't need to enter every email address every time you need to send an email to the same group of people. The really important thing about a contact list is that it allows you to send emails to people that are not within your organization's Office 365.

So what is a "Group?"
A "Group" has the same features as a "Contact list" but the key difference is that it only allows you to send emails to people that are within your organization's Office 365.  The reason for this is that it was designed by Microsoft to be used for team collaboration within an organization.

I hope this clears up any confusion, but if not you can feel free to give me a call at the Help Desk.

Custom Sorts in Excel

In a report that I was working on, I ran into a problem with the way Excel was sorting the pivot table.  While Excel usually sorts the 3-letter library codes without a problem, this time it pulled the listings for two libraries out of order at the very top of the list.  I couldn’t figure out why Excel suddenly forgot the alphabet for those two locations until I realized that their 3-letter codes were the abbreviations for a day of the week and a month of the year.  Excel was trying to be “helpful” by putting those two codes at the top.  

There wasn’t a convenient “don’t do that” button to force it to go back to a straight alphabetical sort and I was wondering if I’d have to manually edit the spreadsheet to put the rows for those libraries back where they belonged when Greg told me about custom lists.  They’re a way you can set up a new sorting order for Excel.  You can then choose your custom list and Excel will use that as the sort criteria.  

To create a custom sort list:

1. Open Excel and type the values you want to sort by in the order you want them, from top to bottom, in a single column in a spreadsheet.
2. Select the cells in the column that contain your new sort criteria. Don’t select the entire column, just the cells that have entries.
3. In the Menu bar, click on File and then choose Options from the list. 
4. This will open the Excel Options window.  Click on Advanced from the list on the left of the window.
5. Scroll all the way down to the bottom of the window and, just above the colored bar for “Lotus compatibility” there should be an Edit Custom Lists button.  Click on it.
6. A Custom Lists window should now be showing.  The range you had selected earlier should be showing in the “Import list from cells” box towards the bottom of the window.  Click on Import.
7. The list should now show in the Custom lists with the full list of the contents in the List entries box.  Click on OK.
   
   
   
8. The next time you need to sort based off of this list, under Order where you’d normally choose ascending or descending, choose Custom List and then just click on the list you created.

Gail's Toolkit for technology training

What do you get when very motivated librarians teaching lots of technology courses wants to standardize their course templates to make life easier for everyone (and have a grant to do it)?
Gail's Toolkit, built by staff at the Gail Borden Public Library.

From their website:  "Gail's Toolkit is a project funded by an American Library Association Publishing Carnegie-Whitney Grant that runs from 4/1/15 through 3/30/17. This free, online portal—sponsored by the Reaching Across Illinois Library System—offers lesson plans, presentations, handouts, and surveys that librarians can use to teach classes ranging from Microsoft Word to LinkedIn. The portal also offers an online bibliography of training resources for those who want to learn more about instruction."

I heard about Gail's Toolkit at WiLSWorld this year (presentation, handout), and I thought it had a lot of potential to help libraries everywhere with technology training. The Gail Borden Public Library staff have already developed a collection of courses that are free for anyone to use. For those interested in developing their own courses, the course templates are designed to make the courses so standardized that in the case of staff illness or other unexpected changes, any staff person could step in and cover the class.

Tracks & Classes: http://www.gailstoolkit.com/tracks-classes (templates to design your own classes are also included on this page)

As of September, there are already-developed classes for computer and internet basics, Microsoft programs, Google tools, resumes, and LinkedIn. Not a bad start!

Screenshots with Microsoft Word

I like to use Microsoft Word for much of the documentation that I write.  I also like to include screenshots in my documents when they can be more effective than words.  I am embarrassed to admit that I did not know until recently that Word has built-in screenshot capability.  There's no need to bring third party screenshot software into the mix!

Next time you need to add a screenshot to a Word document, simply select the Insert tab then click Screenshot.  You'll have the option to make a screenshot of any window that you have open or to make a custom screen clipping on your own.  Once you have the screenshot inserted into your document, the Format menu will appear in the toolbar.  You can change the color and shape and add some artistic effects to enhance the screenshot.

Source:  http://www.groovypost.com/howto/take-screenshots-word-2013/