TechBits

Rediscovering Excel Macros

What is a macro?  Macros are Visual Basic for Applications (VBA) codes that are saved inside a document.  They can be applied in several Microsoft Office Applications.  An analogy is to think of a document as HTML and the macro as Javascript.  A macro can manipulate a document similarly to the way Javascript can manipulate a web page.  Macros are powerful and can do many things including; apply style and formatting, manipulate data and text, communicate with data sources, create entirely new documents, and any combination of these. 

You must be asking, how can a macro help me?  They allow you to save time on predictable, repetitive tasks as well as standardizing document formats.  Now without having to write a single line of code!  I found myself having to run the same data sets for different libraries in our system regularly and exporting them into Excel spreadsheets.  I grew tired of all the customization and formatting within Excel I had to do each time.  Excel Macros were the answer for me!  Not only have they saved me time, but they have also helped improve consistency and accuracy. 

To create an Excel macro simply import your spreadsheet into Microsoft Excel.

1. Select the View tab
2. From there, select the Macros button and highlight the down arrow and select Record Macro.

1. From this point, go ahead and make your desired changes to the spreadsheet as you would normally do.
2. When you are finished with your changes, go back to the Macro button down arrow and you will now select the option to Stop Recording.
3. Close out of Excel and save your Macro.
4. Once completed, the macro will be available anytime you open Excel under the Macro menu.
5. Simply select View Macro and select which macro to use (if you have more than one saved).
6. Then hit the Run button.

 

1. The macro I created for the Library Weeding Reports completes the following tasks in Excel with just one click;
   1. Bolds and freezes the top row
   2. Converts barcodes to a number without decimal places
   3. Changes the print orientation to landscape
   4. Sets the correct margins
   5. Wraps the text
      (See my spreadsheet changes below.)

I have gone on to create many Excel macros for the different data sets I run.  It’s amazing how intuitive macros have become in the last several versions of Microsoft Excel.  Please share with us how you use macros in the comments.

PowerPoint Pointer

Last year while I was working on a PowerPoint presentation I came across a nifty little feature. In slide show mode you can make your regular mouse curser look like a laser pointer. Even though I didn’t use it then it came up as a topic during a presentation I sat in on last week, so I thought I’d share this with you now.

This only works if you’re in presentation mode in front of your computer--just hold the CTRL key down and left click your mouse button. Your mouse pointer should turn into a red dot. You can release the CTRL key when the red dot appears. You can move the dot around your presentation like a laser pointer now. When you release the mouse button your regular mouse pointer will re-appear.

A more permanent solution is to hold the control button down and press the letter P--this will turn your mouse pointer into a small red dot. Hit Esc on the keyboard to bring your mouse pointer back.

Deleting old or bad email addresses from Outlook's Auto-Complete list

Even though Andrew covered this topic back in Dec. 2014 I thought it might be good to cover it again.

You've all encountered Outlook's Auto-Complete feature when you start typing in the To, Cc, or Bcc fields in Outlook and you get a list of suggested email accounts based on the first few letters you've already entered. These suggestions are coming from that feature and is trying to save you time in entering someone's full email address.

This time saving list is sometimes your friend and other times it is not. If you have ever mistyped an email address and sent it, then that incorrect email address is now stored in your Auto-Complete list. This also goes for an employee that you emailed frequently and now that employee has moved on to other ventures. Their email address will still come up if you type the first few letters of their email account.

In order to delete theses bad and old email addresses from your Auto-Complete list you must do the following steps:

1. Open a new email message.
2. Type the first few characters of the email address that you want to delete.
3. Use your mouse and click the 'X' next to that email address or you can use the down arrow key to highlight that email address and then press the Delete key.

Now you know how to keep your Auto-Complete list current and up-to-date.

 

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.

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

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!)

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

Gmail

Drupal websites - CKEditor toolbar

 

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 to the 2013 version, but the upcoming upgrade is a good reason to re-post information from a previous TechBits article.  CustomGuide'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.

 

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.

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.

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.

 

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. 

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"
   
6. Then I opened Excel, chose File->Open and navigated to the all.txt file

I am ridiculously excited about this trick!