About Me

My photo
Frisco, CO, United States
Hi, I'm Joel Levy owner of PC Applications. I have been providing quality Windows and MS Office Training and Consulting services to Central Colorado for over 16 Years. I have been specifically teaching computer classes since 1993 and bring incredible enthusiasm in a relaxed, laid back style that makes the learning fun and enjoyable. My personable teaching style makes it easy to understand concepts of how the software works, not just what buttons to click. My experience in working with Windows and ALL of the MS Office Applications at ALL levels allows me to explain things from a broad perspective comparing and contrasting MS Office features. Check out our website www.pcapplications.com

Saturday, October 22, 2011

Using Hyperlinks in MS Office Applications

Hello Again;

Yes, Tempus Fugit (Latin for "time flies")...And you thought you were going to learn about hyperlinks...

Is it Vidi Vici Veni or Veni Vidi Vici ??

Oh yeah, hyperlinks...

You might say "you mean hyperlinks like on websites" ( or probably say "you mean like hyperlinks on the Internet"?)  You do know the difference, right?

My mind drifts back...it was 1989 when the concepts of websites on the Internet was created by Dr. Tim Burners-Lee (not Al Gore) by implementing http (hypertext transfer protocol) on the Internet allowing hypertext (as it was originally called) to work.  Now referred to as hyperlink, the concept is to click on something (originally blue underlined text) and link to information in this "web" of connected information via the Internet.  Currently, several things might happen upon clicking a hyperlink:  go to another place on the current page, go to another place in the current website, go to another website, a small picture (thumbnail) gets bigger, an email is compiled and ready to be sent, a file (like pdf) opens...

Over the years Microsoft has integrated this concept into the Office applications such a Word, Excel, PowerPoint, and others.  Once one knows how to create a hyperlink in one application, it is pretty much the same for all other Office applications.

I will use MS Word 2007 as an example.

Why would I want to use a hyperlink anyway?  As with the Internet you can use the hyperlink to have someone click in the Word document and end up at your website.  You can have someone click and send you an email.  Another option would be able to "link" to any other file within you computer or network (that could be handy; don't even have to know where the file is but could click and wa la, it's open in my face).

Although there are a number of steps, there is not necessarily a "right" order to getting it done... here's just how I tend to do it.

I type some text as in the first case like "See Website".  I select the text and then on the Insert Ribbon, Click Hyperlink.  The Insert Hyperlink dialog box appears.  At the top, Text to Display shows the selected text which can be edited.  On the left, the default Link To button is Existing File or Web page.  At the bottom is a line for Address.  In the Address line type in the address of the website or web page.  You could click the Internet Browse button (the globe with magnifying glass icon) to find the site or page on the Internet first.

The button at the top of the dialog box "Screen Tip" allows one to create such (Screen Tip - a caption that appears upon moving the cursor over the hyperlink).  Click the button and type.

As far as linking to a file.  Just think of the concept first.  I could have someone reading a Word doc click to see a related PowerPoint presentation, click to see the supporting information in an Excel file, click to open a related Word doc, etc...(of course one must have the associated application installed on the computer they are using).

Note by default the Insert Hyperlink dialog box contains files in the current directory folder.  At the top, you can click the drop down button in Look In and change the directory location.  By choosing a file in the list, the Address will contain the information to hyperlink to the file.

So now let's look at the two types of hyperlinks discussed so far.  Upon clicking OK on the Insert Hyperlink dialog box your selected text should appear blue and underlined.  Upon moving your cursor over the hyperlink, you would see the Screen Tip if created.  The cursor will change to a hand with the pointing index finger and upon clicking, you will be looking a website in IE or a file such as Word, Excel, PowerPoint, PDF...

Another hyperlink option I mentioned is to send an email via the hyperlink.  In the Word doc for example I might type "For More Info", select the text and Insert Hyperlink as before.  In this case, click on the "Email Address" button in the Link To bar as it is called.  The dialog box changes to enter information about the email address to send to and the subject line.  Click OK and now the hyperlink in the document upon clicking with generate a new email with the email address and subject line already filled in.  The user could then type in the body of the email and send.  The user must already have email software (like Outlook) loaded on their computer for this to work.

So you might agree this hyperlink thing can be VERY useful in the MS Office applications.

Where do you want to go with a click??

Have fun in the meantime until next time, Joel

Tuesday, August 16, 2011

Autocalculate in Excel, You just have to look!

Hello Again and OOOH My, it's August, I have been enjoying summer in the Colorado Rockies and hope your summer has been good as well.  So following the laid back attitude, I thought I would discuss a feature that is so "laid back" you don't really have to do anything except look.

I am always suprised as to what people don't see in the application window, usually due to their focus being on a specific area of the screen/window and not really "looking around" to just see what's happening in the window as they are working with the application.  When I point out specific things that are happening or things that are just there to see (if you look at it) to someone during a training/consulting session, commonly they remark "has that always been there?!"  And of course they are suprised to hear YES and as a matter of fact in some cases "this feature has been in many prior versions."

A classic example of this is the Autocalculate feature of Excel; this is not new and has been a feature of Excel for quite a while.

In a workbook for example select a range of cells containing values.  Look at the right section of the Status Bar (the bar at the bottom of the Excel window), you should see Sum= and the sum of the values in the Range.  In Excel 2007/2010 (as well as earlier vesions) right click the Status Bar and you will be presented a shortcut menu with many options as what to show in the Status Bar.  Notice the second segment from the bottom is a list of 6 functions which can be turned on/off.  Which ever of the 6 functions you turn on, the results will be in the Status Bar upon selecting a range of data.  Notice there is a Count function that will apply to Text data.  As per above, I find many people even after working for years in Excel never knew about this feature and never saw it because they just never looked to see.

So now I guess we can call this a 'quick look' feature now that we know to look.  "I see said the blind man..."

Have fun in the meantime until next time!  Joel

Friday, June 3, 2011

Printing in Excel

Hello To All, Already June! It's been awhile since I've blogged, sorry for the hiatus...

A common issue in Excel is knowing how to control a multi-paged print job. How many times upon printing a worksheet you may have one or more pages "filled up" with part of the print range, but end up with one page that has one or two columns of data on it or one page that has one or two rows of data on it? Wouldn't it be nice to get the one or two columns or rows onto a previous page and save a page or more?

Of course there are several ways to control what data prints, and the number of pages for the print job in Excel. One of the easies ways I have found to control the print job is to work with Page Break Preview and Scale to Fit options.

First of all, in regards to the issue about having a page(s) with just a few columns or rows of data: In Excel there have 2 views (2007 and later added another view called Page Layout) Normal and Page Break Preview. In 2007, there are view option buttons in the status bar on the lower right of the window. Click the Page Break Preview button (read the message that comes up). Now you can click and drag the page break lines and define where the page breaks occur in you print range. Dragging a page break to the edge of the print area will eliminate the page break and of course a page in the print job.

Otherwise one can define the print job using Scale to Fit options in the Page Layout Ribbon in 2007. The print job in Excel is defined as so many pages Wide (width) by so many pages Tall (height) and an associated Scale, the default settings, are "Automatic" for Width and Height and 100% for Scale. You can force the print job to a given number of pages Wide and Tall by setting these options, the scaling with be automatically adjusted. With a combination of Page Break Preview and changing the Scale to Fit options, you can easily see how the print job will occur. To get the print job back to default, Width and Height should be "Automatic" and 100% for scale.

Of course you can view the print job in Print Preview (Ctrl+F2) shortcut.

If nothing else, your goal would be use the least amount of paper to have a readable print job.

Happy printing!

Take care in the meantime until next time, Joel

Friday, March 18, 2011

MS Office Formats, Styles, and Themes: The Big Picture

Hello Again:

Sorry, I know it's been a while and I'm sure you all have been anxiously waiting for the next blog...

I find many people using MS Office applications understand "font" as something to do with formatting but may not understand the concept of formatting including Styles and Themes. Thus below is my attempt to 'put things in conceptual perspective'.

In several prior blogs I have discussed certain concepts related to formatting; for me 'appearance' is my synonym, how the data or information looks.

As I see it, MS Office has 3 "levels" or hierarchy of formatting. There are the individual formats such as font, font size, font color, alignment, etc. Then there are Styles, which are several aspects of formatting predefined and used by default. In MS Office applications, the Style is called Normal (see blog Sept 2010 about Word Styles). The definition of Normal Style depends on the application. For example, in Word 2007, Normal Style uses Calibri 11 (and other formats) while Excel uses Arial 10 (and other formats) as the Style Normal.

In addition (particularly in MS Office 2007 and 2010) there is another aspect of formatting called a Theme. Themes are aspect of formatting that no only effect text but also effect the appearance of objects, such as pictures, charts, drawings, smart art, etc.

There are a set of Themes (Page Layout Ribbon) that are available in the MS Office 2007 and 2010 applications that can be applied to the document, spreadsheet, or presentation that define formatting for Colors schemes, Font schemes, and Effects schemes that are applied to the text and objects.

MS Office applications use the same set of Themes such that one can create consistency of the "look and feel" of your documents, spreadsheets, and presentations.

In the Themes command group there are options to choose a Theme out of the gallery such as Apex or Flow, "Office" is the default Theme. You can also customize the Theme by choosing which Colors scheme, Font scheme, and Effects scheme you want to use. Of course each of these aspects of a Theme can be customized and saved your custom Theme.

There you have it, looking into changing how things look using MS Office applications. If you can't impress them with brilliance, dazzel them with formatting. Good luck otherwise...

Take care in the meantime until next time, Joel.

Monday, January 24, 2011

A Whole Other Dimension to Formatting, Conditional Fomatting in Excel 2007

Hello Again:

Hope all survived the Holiday Season and Happy New Year.

Although Conditional Formatting is not new to Excel 2007, the possibilities of conditional formatting have been greatly expanded in 2007 relative to prior versions.

Conditional formatting (hence the name) allows formats to be applied based on defined conditions. A conditional format can be defined based on the following:

Range of Cell Values - Format a range containing values from lowest to highest. Formats can be 2 or 3 color variations (color shade varies from low to high values), data bars (bars of color in cell based on cell value within range, like a bar graph), or icon sets (icons related to value of cell within range). An example would be a range of values form 1 to 10 vary from blue to red.

Cell Contents - Format a cell based on contents (containing a specific value, text, date). An example would be cells within a range containing 10 would be blue.

Cell Range Rankings - Format a range containing values based on ranking in range (top 10%, eg). An example would be the cells that are in the top 10% of a range of values from 1 to 100 would be green.

Cell Range Average - Format a range containing values based on average of range (above or below average). An example would be the cells that are above average within a range of values from 1 to 100 would be green.

Cell Range Unique or Duplicate Values - Format a range containing unique or duplicates values. An example would be cells containing duplicates within a range of values would be red.

Cell Formulas - Format a range based on a formula being true or false. An example would be a formatting odd rows on a worksheet by using =MOD(ROW(),2)=1 as the formula and then defining the rows and format like gray fill. 10 Bonus Points to those whose see the formula logic!

So where is the Conditional Format option anyway??

In the Home ribbon, Styles command group, there is a button for Conditional Formatting. There are 5 predefined options (Highlight cells, Top/bottom, Data Bars, Color Scales, and Icon Sets). At the bottom of the list are options for New Rules, Clear Rules, or Manage Rules. New Rules allow one to create and define custom rules as per above.

As you can imagine, there are now many options as to formatting other than just bold. HA!

Take care in the meantime until next time, Joel.