Office Tips and Methods

Introduced in some Office 2007 applications, the Fluent User Interface is Microsoft’s attempt to ‘expose’ (make more readily available) the commands used to work with an Office application. With Office 2010, all Office applications use this style of user interface. For the average user, the most visible aspect of the Fluent User Interface is the Ribbon, which replaced the menus and toolbar interface style of Office applications from ‘97 through to 2003.

On the positive side the Fluent User Interface does a creditable job of displaying, and making more easily available, the commands that serve the needs of the vast majority of users. In the menu/toolbar style that preceded the Fluent User Interface, some commands necessarily had to be buried in the menu structure. Only the most adventurous user stood any chance of accidentally discovering some of these commands.

The Ribbon, on the other hand makes it possible for the user to easily discover many more commands simply by exploring the contents of each tab. What users will encounter however, are differences in the appearance of the Ribbon depending on the current width of the Application window and the monitor’s resolution.

Notice the detail on the Home tab of the Excel 2010 Ribbon for example:

This is a screenshot of the Ribbon as it is displayed in a very wide window. For display purposes here, the image had to be somewhat resized. Compare that image with this one, using a narrower window:

In the first image, the Styles group has a rich assortment of buttons. In the second, the styles group has only three buttons. In to see the cell styles gallery, you have to click the Cell Styles button dropdown.

With progressively narrower windows more and more groups are collapsed to a few essential buttons which you must click in order to see all the possibilities a group offers:

Here the Styles group has been reduced to a single button:

Here, the Number and Cells groups are also reduced to single buttons:

When the window is extremely narrow, most groups are barely recognizable. Notice that none of the tabs can display their full name:

It is even possible to reach a point where there is no longer enough screen with to display the full Ribbon:

When that happens, ‘expander’ buttons appear, allowing you to navigate to hidden portions of the Ribbon.

Practically speaking, it is unlikely that you would ever use such an extremely narrow window as in the last graphic but keep in mind that screen resolution also affects how the Ribbon will display. The lower the resolution setting, the more likely it is that you will see a truncated view of the Ribbon.

Guest Blogger Jack Leach is a colleague of mine at UtterAccess where his pet project is the Access Wiki. Recently, in a mailing list that we both subscribe to, Jack posted his experience in building an Access application for his business. You can find his website at http://www.dymeng.com and his blog at http://dymeng.com/blog. Jack has graciously given his permission to OTM to publish his success story.

I’ve used Access to create a shop management software for my manufacturing business.  Besides the average FE type stuff: customers, orders, purchasing, quickbooks integration, the software also handles all the backend stuff: visual scheduling, what-if scenarios, integrated quality control, everything right down to tooling tracking (of which there’s thousands).  Min/Max for both customer pull-supply as well as shop supply, so on and so forth.  The whole nine yards.

Even more so, I was able to integrate basic CAM (Computer Aided Machining – like CAD that outputs machine tool code for producing the parts) into the routing structure for the components we make.  AutoCAD automation for reading customer drawings and auto-generating tolerance sets for quality reporting, etc.

The very first piece of the application – my first Access project – saved the company an estimated $20k/year by providing different machine code formats for different machine tool platforms.

We work with medical, military, aerospace and other demanding industries, which has all been fully supported by this for quite a few years.

What kind of computer training works best for you? If you have a few minutes, please take my survey.

Click here to take survey

I am among the first to point out that Excel is not a data manager. Yet, at the same time, Excel does have some useful features for working with data, especially small data collections. Such lists can be used directly for data analysis, for example, or indirectly as the data source for mail merges in Word.

Regardless of how the list will be used there are four fundamental rules for setting up an Excel data list:

• The list should have a header row of unique names for each column in the list
• There should be no blank columns in the list
• There should be no blank rows in the list
• Similar data should appear in one column

The last rule may be misunderstood because there is one other guideline for setting up any list. Data in any column should not be grouped. For example, in a name and address list, newcomers are tempted to list a person’s full name in a single cell. This can become problematic very soon.

For example, if you use a single cell for people’s full names, you can then sort the list only into ascending or descending order by the full name. Depending on how you actually enter the name information, a meaningful sort may prove to be impossible.

 

So, while first name, middle name and last name are similar types of information –they are all names, after all – they are really different and deserve their own cells and therefor their own columns. Separating names into Full Name, Middle Name and Last Name, allows you to then sort the list by Last Name; where several people have the same last name, those groups can be further sorted by First and Middle Names.

Separate columns for First, Last, and Middle names, makes your data far more flexible to work with in other ways. For example, having the first name separate from the last name makes it possible to address a letter to John Smith, but have a salutation in the letter like, “Dear John.”

The video illustrates just how easy it is to create a simple data list. In coming articles, I will discuss how to enhance the list to provide additional columns (fields) for mail merging and how to set up the list as mail merge data source.

Whether you purchase applications on-line and download the installation files or install new software from installing disks, keeping a central record of Application Licences and Activation Codes can greatly simplify managing the applications you have and having the information you need (User Name, associated email, licence number and activation code).  OneNote provides a simple solution for individuals and small businesses who do not have ready access to Information Technology experts who specialize in such management.

 

Keeping the information organized simplifies the process of

• upgrading applications
• re-installing applications
• transferring applications to a new computer system.

There are, of course, a number of different ways you could organize this information in OneNote. My notebook has just two sections.

Contents

The contents section has only one page which serves as a table of contents for the notebook and as a quick list of all applications. Each entry on this page is a hyperlink to the main page for each application

Applications

I have organized the Applications Section by having a Page for each application. If I have received the main information by email then I simply send the email to OneNote using the link on my Outlook Ribbon. Once I have created this page, I copy the link to it and insert it as a hyperlink on the Contents page.

Occasionally, additional information about the application may come up. For example, I called support for one application. To keep a record of the discussion and its outcome I created a subpage for the application, titling the page Support followed by the date of the discussion. Having a separate subpage for each support call, if there is more than one, makes it easy to track down and review the applications support call history

An alternative approach to organizing application information might be to create a separate OneNote section for each application with as many detail pages as necessary.

However you organize the pages, a summary of key application information at the top of the main application page can be helpful. Include in this summary as much key information as you have used in obtaining the application in the first place. For example most of the applications I have installed have the following types of information:

• Developer/Provider Website
• User Name
• User Email
• Serial Number
• Activation Code
• Sales Contact
• Support Number/Address
• Version
• Installation Date
• Upgrade Versions and Installation Dates

 

If I have created an account with the Provider that I purchased the application from, the one piece of information that I do not store in OneNote, is my password. Similarly, if the application itself requires a log-in password, for security reasons I do not store the password in the OneNote workbook.

OneNote makes it quick and easy to store and update Application “Vital Statistics.” Investing a few minutes to record this information when you install a new application can save you hours of frustration in the future trying to remember where you put that scrap of paper that scribbled the details on.

Recently, a colleague distributed some rather important health and well-being statistics, illustrating the data with an exploded 3D Graph. Unfortunately, while 3D graphs are more pleasing to the eye than their flat cousins; the perspective necessary to create the 3D illusion, distorts the values being plotted.

Here’s an example using simple arbitrary data. First the exploded version:

 

 

 

 

 

 

Notice how, in the 3D version, the Cons wedge appears smaller than the Pros wedge, even though the two wedges represent exactly the same value (46%). In the flat version, on the other hand, the Pros and Cons wedges appear to have exactly the same size.

Is it the Exploded view that creates this illusion? Consider the following unexploded views. The illusion persists.

 

 

 

 

 

 

 

 

An unscrupulous presenter could easily use this illusion to distort the facts and unfairly influence his/her audience. (Remember the adage: “Figures don’t lie; liars figure”?) Think about a political race, for example. Depending on which position the presenter wants to improve the apparent advantage of, all he or she has to do is rotate the 3D chart accordingly to immediately improve the apparent advantage of the favoured position:

 

 

 

The Pros Have It!

The Cons Have It!

Of course, these examples include data labels to help the viewer’s interpretation. Omitting the labels can only make the dishonest presenter’s self-appointed task of deception easier.

So the next time you have to create a graph, think carefully about purpose of graphs and avoid the inevitable optical illusions inherent in 3D charts.

Here are some useful links that I have recently come across on the Web. The list is weighted in favour of Excel only because, at the moment, I am doing mostly Excel training.

OFFICE

Migrating from Excel 2003 to Excel 2010

A help guide to working with the Fluent User Interface (Ribbon) first introduced in Office 2007 and enhanced in Office 2010, replacing traditional menus and toolbars

BLOGS AND PEER SUPPORT SITES

Office Tips and Methods

A blog focusing on tips and how to techniques for getting the most out of Microsoft Office Applications

UtterAccess

User peer support forum primarily focused on Microsoft Access but with an active Excel forum as well

Mr. Excel

Bill Jelen’s (Mr. Excel) site includes peer support forum and many pages of Excel help and tips from Mr. Excel himself

Excel is Fun!

YouTube gateway to the ExcelisFun channel –hundreds of well documented videos illustrating how to do just about anything in Excel.

Microsoft Answers

http://office.microsoft.com/en-us/excel-help/use-excel-spin-boxes-to-help-with-financial-modeling-HA001226521.aspx?pid=CL100570551033

EXCEL

http://www.scoop.it/t/excel-hero-linkedin-group/

An excellent collection of Excel related links

http://www.lynda.com/Excel-2007-tutorials/Data-Validation-in-Depth/77849-2.html

Clever data validation example

http://www.andypope.info/charts.htm

Charting Examples

http://dmcritchie.mvps.org/excel/colors.htm

Color Palette and the 56 Excel ColorIndex Colors:

http://www.contextures.com/xlUserForm01.html

Create an Excel UserForm

https://sites.google.com/site/exceladdinsdirectory/

Excel Add-ins Directory:

http://www.excel-blackbelt.com/

Analytics and Visualization

http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

Excel for Developers

http://excel.tips.net/C0200_Macros.html

ExcelTips:

http://www.excel-spreadsheet.com/vba/vba.htm

Excel VBA – Reference Guide

http://www.exinfm.com/free_spreadsheets.html

Free Spreadsheets

https://groups.google.com/forum/?hl=en&fromgroups#!forum/microsoft.public.excel.programming

microsoft.public.excel.programming:

http://www.gummy-stuff.org/Excel/

Index of /Excel:

http://spreadsheetpage.com/

The Spreadsheet Page:

https://sites.google.com/site/exceladdinsdirectory/home/main-directory

Excel Add-ins Directory

http://answers.microsoft.com/en-us/office/forum/excel

Use Excel spin boxes to help with financial modeling:

http://www.functionx.com/vbaexcel/

VBA for Microsoft Office Excel 2007

http://tech.groups.yahoo.com/group/ms_excel/

Yahoo Groups MS_Excel

http://tech.groups.yahoo.com/group/ExcelVBA/

Yahoo Groups Excel VBA:training.

I came across this video today which is an excellent tutorial of advanced Excel techniques for data validation. The example uses the Indirect() function as the source for the lists that are used as choices in a ‘drop-down’. I will let the video speak for itself.

 

One thing that the video doesn’t mention, is that because the lists validation works with are named ranges, they can actually be on a different sheet than the one that the data validation is used on. Ordinarily, data validation lists must be on the same sheet as the cells with the validation rules.

Jack Leach who has been very busy for some time tweaking the Access Wiki has just posted this news item at UtterAccess, the first of an announcement series for Wiki content. In this announcement, Jack feature these articles:

• Base Ten to Base Letter
• HttpRequest_Class and Access and the web HTML Object Library
• TypeOf

If you are interested in enhancing your Access knowledge check out Jack’s post for links to these article. You might even want to browse the entire Wiki Table of Contents.

Recently, a client asked me to facilitate a Project Management workshop. As I started to think about planning the workshop, I realized the OneNote would be an excellent tool to help with the planning.

I already had an outline of the content that this one day workshop would cover so that was my starting point for setting up the OneNote notebook. I set up the notebook with several sections:

• Major Topics
• Labs (Hands-on exercises)
• Handouts
• Background and Resource Materials

Thinking about these sections, it seemed to me that they naturally fell into two major categories, suggesting that the notebook should consist of two sections:

• Workshop
  • Major Topics
  • Labs (Hands-on exercises)
• Materials
  • Handouts
  • Background and Resource Materials.

Now I had the basic structure for my workshop planning notebook. There was, however, one additional requirement for the notebook. Although I wasn’t collaborating with anyone else, I did want to be able to view and edit the notebook remotely. While I do most of this sort of work on a desktop computer, from time to time I also work away from home base, so to speak. Accessing the notebook remotely would allow me to review and update the workshop plan even if I were away from home.

OneNote offers a solution tailor made to my need. Rather than creating the notebook locally, I created a OneNote Web App on my SkyDrive. Once I had the notebook set up, I was able to link to it from my Android tablet using Microsoft’s OneNote Android App. Now I had the best of both worlds, ability to work with the notebook on my desktop computer exactly as if it were a local file, and ability to access the notebook anywhere I had WiFi or 4G service.

Remote access to OneNote offers a huge advantage over making manual notes and then updating the notebook later when I am back at home base. The notebook is always current with my latest changes. Making changes directly in the notebook instead of making paper notes that have to be transcribed later, saves time and effort.

OneNote is arguably one of the most versatile personal productivity tools in the Windows world. Using it as a workshop planning tool as I have described here if but one of a seemingly endless parade of possibilities. How you use OneNote to best advantage in your world is limited only by your imagination.