A couple of little Office tips

Add a comment

This week I've been updating some information with a lot of vendors, and I'm finding it a little difficult to keep track of where I am in the process. I created a spreadsheet and have been adding dates and color coding things to indicate what's done, but occasionally I need to add a note with some extra details specific to a vendor.

Office programs have an option to add comments, and this has been a perfect way for Present Kerri to leave Future Kerri some extra information. CommentExample

To add a comment in an Office doc...

  1. Select the content you want to comment on.

  2. Go to Review > New Comment, and make your comment. If you want to make changes to any of your comments, just go back and edit them.

Show or hide the ribbon in Office

Have you ever accidentally "lost" the ribbon (with all the menus and options) in Word or Excel? Or have you ever wished it would go away to give you more screen real estate? I always forget where this setting lives and have to look it up. I just looked it up again this week and want to share what I learned!

The settings for how the ribbon behaves are found in the upper right-hand corner. Here's what they look like...
RibbonSettingsand here's a link to Microsoft's "Show or hide the ribbon in Office" article. 

Office Compatibility Mode

You might have noticed “[Compatibility Mode]” started appearing in the title bar of some of your Word and Excel files after the recent Office 2016 upgrade.  This means that the document that you have opened was created with an older version of Microsoft Office.  Compatibility Mode ensures that people using older versions of Office will still be able to edit the document and the document will also maintain its intended format.

Word - Compatibility Mode

Documents created with Office 2007 or Office 2010 will display “[Compatibility Mode]” in the title bar when opened with Office 2013 or 2016.  Documents created with Office 2013 and opened with the 2016 version will not open in Compatibility Mode.  This is because 2013 and 2016 are compatible. 

There really is no reason to convert your older documents to the 2016 format unless there is some new feature or formatting option you are dying to use.  Remember, if you do convert them, people using older versions of Office might not be able to edit some components and the formatting may not appear as intended. 

Follow these instructions to convert Word documents to the 2016 file format:

  1. Open the Word document
  2. Click File
  3. Click the Info menu option at the left
  4. Next to Compatibility Mode, click Convert
  5. Click OK
  6. Save the document

For Excel:

  1. Open the Excel document
  2. Click File
  3. Click the Info menu option at the left
  4. Next to Compatibility Mode, click Convert
  5. Click OK
  6. Click Yes to close and reopen the workbook

Brief guide to buttons that clear formatting

Noticing unwanted formatting differences in the text on your website, email, Excel, or Word document (where one line looks good, but another is a hair bigger or smaller)? Many times there is a little button intended to fix it! Just highlight/select the text in edit mode, click the button—voila, wonky formatting gone. Here's a guide to what to look for in some common tools:

Microsoft Word & Office 365 (same icon!)

Microsoft Word & Office 365 use an icon with a pink eraser scrubbing out an uppercase A

Excel (specialized format clearing options in a drop-down)

Excel's icon shows a pink eraser next to the word Clear, with a drop down menu

Gmail

Gmail's button looks like an italicized uppercase T with a small subscript x

Drupal websites - CKEditor toolbar

The CKEditor toolbar button used on many Drupal websites has a button with an italicized uppercase T with a small subscript x

 

Office 2016 Reference Cards

We will be upgrading the version of Microsoft Office on all SCLS-supported staff computers to 2016 Professional Plus during the week of August 6th.  The interface for Office 2016 is fairly similar toDesk-glasses-laptop-3061 the 2013 version, but the upcoming upgrade is a good reason to re-post information from a previous TechBits articleCustomGuide's Quick Reference Cards are nice tools to learn the best way to accomplish common tasks in Microsoft Office.  The 2016 reference cards are linked from the SCLS Technology page.

Word's Disappearing Ribbon Trick

Have you ever been using Word and wished for more vertical space? Or maybe, like what happened here and at one other library where all that was showing up in the Word window was the menu bar and the user was wondering: Where's the ribbon?

This post will help you answer these questions. In Word there is an option to hide the ribbon or unhide the ribbon by using Ctrl F1. This keyboard command works for both Word 2010 (if you have Windows 8) and Word 2013 (if you have Windows 10).

If you prefer using the mouse then the location of the clickable caret (looks like an upside down V) varies depending on which version of Word you have.

For Word 2010

In the upper right-hand corner of a Word window, directly to the left of the question mark inside a blue circle is the caret you click on to either hide the ribbon or show the ribbon. If it is hidden then the caret points down and if the ribbon is visible then the caret points up.

Word_Ribbon_04

 

For Word 2013

In the upper right-hand corner of a Word window, directly to the right of the word "Editing" is the caret you click on to hide the ribbon.

Word_Ribbon_01

If it is hidden to show it again you need to click on the icon directly to the right of the question mark in the upper right-hand corner.

Word_Ribbon_02

It will show you three options: Auto-hide Ribbon, Show Tabs and Show Tabs and Commands. In order to show the ribbon again you will need to click on the Show Tabs and Commands option.

Word_Ribbon_03

 

Display formulas in Excel

Ever need to look through the formulas on a spreadsheet?  Maybe you need to update them, maybe you're trying to fix a problem or maybe you just want to know what's going on.  Sure you can go through and click on each cell to show the formula but did you know you can set Excel to show the formulas instead of the values in the regular display?

Select the Formulas tab and click on the Show Formulas option.  Now, instead of showing the results, Excel will display the formulas themselves for the entire spreadsheet.  Since the formulas are often longer than the results, the columns may also automatically resize to be able to display the formulas. 

Showformula

When you're done, just click on Show Formulas again and the spreadsheet will go back to showing the results.  It will also automatically resize the columns back to their original settings. 

Easily combine text or csv files

I am totally nerding out. I admit it. I had a folder full of text files, and I wanted to dump them all into a single Excel worksheet. It turns out there is a super-easy way to do this, provided you're willing to open up a command line and type in a few DOS commands!!

Here's where I found the instructions:  https://www.rondebruin.nl/win/s3/win021.htm

In my case...

  1. I put the .log files (which are all really text files with a fancy extension) in a folder named "logs" on my desktop
  2. I clicked on the Windows Start button and typed cmd to open a command prompt
  3. My path showed that I was already in my user account. 
  4. I typed cd desktop/logs to navigate to the "logs" folder on my desktop
  5. I typed copy *.log all.txt  to copy ALL the .log files into a single text file titled "all.txt"
    Copy
  6. Then I opened Excel, chose File->Open and navigated to the all.txt file

I am ridiculously excited about this trick!

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

GrammarlyAbout 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

MessageYou 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.

New login with option to go back    Password

 

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.
    Selectaddresses

  2. Click on the Data tab (1) at the top of page and, in the Sort and Filter section, click on Advanced (2).
    Datafilter

  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.
    Filterwithcopy

  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. 
    Filterlocation

  5. You'll be back at the "Advanced Filter" window.  Check the "Unique records only" box and click on OK.
    Filterwithcopy2
  6. You now have a new list with the duplicates removed but your original list is still intact.    
    Nodups