PC Applications Blog

Working with Styles in Word

2012-01-19T08:04:00.000-08:00

Hello Again:

I know, it's already next year relative to the last time I blogged. Tempus fugit muy rapido! (Latinol??)

Having been teaching several Word Level 1 classes recently, I have been reminded of some of the great features (in my opinion) that have to do with working with Styles. Hopefully you saw my blog in March 2011 which explains concepts of formatting including a Style. Check out the blog to get the big picture..(http://pcapplicationsblog.blogspot.com/2011_03_01_archive.html).

What I teach in a Level 1 Word class (and much more!) is how to work with Styles using the Style "Task Pane". In Word 2007/2010, there is a Styles Command Group in the Home Ribbon. As with many Command Groups, there is what is called a "Dialog Box Launcher", the small square button in the lower right hand corner of the Command Group with an arrow pointing to the lower right. You may notice that many Command Groups have Dialog Box Launchers but not all. Upon clicking the Dialog Box Launcher, a Dialog Box relative to the Command Group options will appear on the screen. These Dialog Boxes are the same dialog boxes that have been used in many prior versions and what many people have seen before if they have worked with Word particularly 97-2003.

In the case of the Styles Dialog Box Launcher, it opens the Styles Task Pane (a window pane that "docks" on the right side of the Word window), not a dialog box per se. It's this Styles Task Pane that can be very useful when working with Styles.

The list of Styles you see in the Styles Task Pane by is controlled by the Style Pane Options accessible through the link in the lower right of the Styles Task Pane, "Options..."; the default list is "Recommend" but consider the other options such as "In Use" or "All Styles". The order of the list can be controlled as well, the default "Recommended" is set by Microsoft but notice you can choose "Alphabetical" as well as others.

Another significant option is the "Show Preview" check box near the bottom of the Styles Task Pane. With it "on" the Styles in the list are shown with the actual formatting inherent with the Style name.

Now for some of the functionality. I you simply click in text of your document or select text (of the same style) there will be an outline around the Style in the Styles Task Pane so you immediately know what the specific style for the select text is. Furthermore, if you move over the selected style in the Styles Task Pane and click the down arrow button that appears, you have a list of options. Notice you can see how many times the Style occurs in the document as well as select all of them if desired. I use this a lot to change one format to another. For example, let's say I have typed in several paragraphs in a document that are all "Normal" style (the defult style for all documents). I decided to change all "Normal" styles to "Heading 1" style. Using the concepts above, I could click on text that is "Normal" and see the "Normal" style selected in the Styles Task Pane. I click the down arrow and choose "Select All # Instances" where # is the number in my document. Then I click "Heading 1" Style in the Styles Task Pane to apply the new Style to my selected text. There are actually other ways to find and replace text formatting but this is a very straight forward method. Of course there are several other options as what can be done with the Styles Task Pane such as select and clear specific Styles, modify Styles and even create your own custom style and I encourge you to have a look at this.

So what's your Style??

Have fun in the meantime until next time, Joel

Using Hyperlinks in MS Office Applications

2011-10-22T10:23:00.000-07:00

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

Autocalculate in Excel, You just have to look!

2011-08-16T07:21:00.000-07:00

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

Printing in Excel

2011-06-03T06:00:00.000-07:00

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

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

2011-03-18T06:26:00.000-07:00

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.

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

2011-01-24T07:33:00.000-08:00

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.

Take Control of AutoCorrect Options

2010-12-14T07:32:00.000-08:00

Hello Again:

If you have used MS Word, then you have experienced "AutoCorrect". As one may be aware, there are many things that become Automatically "corrected" when typing in a Word document (and other Office Applications). This can be good news or bad news, there are the pros and cons, the yen/yang, the double edged sword...

Microsoft (as always) has made some important decisions for you about the AutoCorrect feature. And, as most of the time, you have options about these decisions.

To get to the option settings in Word, in version 2003 and earlier, Tools>AutoCorrect Options (AutoCorrect Tab) and in 2007 Click the Office button and choose Word Options in the lower right-hand corner of the menu then click Proofing Options (on the left), you will then see a button to get to the AutoCorrect options dialog box. In the AutoCorrect dialog box, there is a tab with the AutoCorrect options: Here is where you can see the specific options that are ON by default. Clearly, here is where you have the option to turn things OFF if you don't want the AutoCorrect feature. Be aware that the AutoCorrect options can be set through any Office Application and the current settings effect all of the Office Applications.

Specifically you have access to the Replace Text as You Type feature which can be used to replace common typos or what ever you want. For example you could setup replace Breck with Breckenridge to save some typing time. You can also delete entries in the list (select and entry in the list and click Delete) or turn the feature OFF if you don't want to use the replace as you type feature at all.

Another handy option is the Exceptions which applies to Capitalize first word of sentence, Correct Two Initial Capitals, and Other Corrections. After a period (.) Word is set to automatically capitalize the next word (assumes a new sentence). This is fine except for after abbreviations where the next word is not Capitalized and thus enter these Exceptions in the list. You can also list words with two capital letters that are NOT to be "fixed" such as "ID's" as well as Other Corrections as Exceptions to AutoCorrect options that are ON.

So, by "tweaking" these options, you can get Word (and the other Office Applications) to do what you want them to do as far as AutoCorrect options. Getting the settings to work for you can create tremendous efficiency.

Take care in the meantime until next time, Joel

Highlights about highlighting in Word

2010-11-07T05:44:00.000-08:00

Hello Again:

In my last blog, I discussed the concept of making selections in Word or just about anywhere else you are working with text in a computer. I also differentiated the words "selection" and "highlight" and pointed out that there is actually a "highlight" feature in Word. Well sure enough, one of my great fans (and it's mutual), my brother-in-law Jan V. Nystrom commented on my post and questioned this highlighting feature. So for you Jan, and everyone else out there in blogland, here's some information about the Word "highlight" feature.

But first:

My mind drifts back...and for those of you old enough, you remember when one would emphasize text in a book by underlining it with a pencil or even a pen, or perhaps drawing brackets around the text in the margins of the page, etc. This was probably the case until 1963 when the Avery Corporation created the first "Hi-Liter", a felt tip pen with translucent-ink, which is what a highlighter pen is by definition.

The first felt tip pen was invented by Sidney Rosenthal in 1952. It was made with a wool felt wick and tip and a glass bottle filled with ink and was called a "magic marker" so named for it's ability to mark on any surface. Yes, the good ole "magic markers" and the living room carpet.

The modern fiber-tipped pen was invented by Yukio Horie in 1962. He worked for the Tokyo Stationary Company in Japan. His original pens used bamboo fiber for the pen tips although synthetic fibers later replaced the bamboo. His pens also were the first to use dyes rather than inks to get their pigment.

In 1978 the Avery Corporation introduced their first highlighters with fluorescent colors having various pastel shades. The first fluorescent color was yellow which is why most people stereotypically think of highlighters being yellow. So now in this modern world one "highlights" text with a highlighter to emphasize text in a book.

Sorry for the diversion of historical perspective.

Back to Word and highlighting. So YES, there is a highlight feature in Word with the same concept of "highlighting" text but in a document rather than book.

To "highlight" text in Word, click on the highlight button. In Word 2003 and earlier, it's the second button from the right in the formatting toolbar (looks like a highlighter pen on the button). In Word 2007, it's a button in the Font Command Group in the Home Ribbon. When over text in the document, the cursor will have a highlight pen icon; highlighting is "on". Then with the highlighter "on", select the text to be highlighted (see last blog about making selections). The default color is yellow (what-da-ya-know). The down-arrow for the highlighting button has various color options. To "remove" any highlighting that is already there, choose "no color" as the highlight and then select the text that has been highlighted to apply no color. Yes, we have no bananas...

Well, there are the highlights about highlighting...

Take care in the meantime until next time, Joel

Making selections in Word (and many other places)

2010-10-15T06:24:00.000-07:00

Hello Again:

Alright, so I'm going to be a little picky here...

One of the things that comes up all the time is when I am working with someone in a Word document and they click and drag the cursor over some text and say "here, I highlighted the text ..." I usually at that point go into a discussion about concepts and semantics. I say "you mean you selected the text?" They look at me like I'm crazy and say "OK, but you see the text is highlighted, right?" I say "yes, but that is the result of you having 'selected' the text."

So what's the point here? Clicking and dragging over text "selects" the text. The software "highlights" the text to let you know it is selected. Just to be clear, there is a "highlighting" feature in Word. So knowing about this and in light of the discussion, when someone tells me they "highlighted" some text in Word, I have to question their meaning of "highlight"; do you mean highlight or perhaps select? See what I mean...semantics.

Now that I have cleared the air about the concept of selecting text, how might I make selections in a Word doc??

Below are ways to select anything in a document from a single character to the entire document:

Any amount of text: Click where you want to begin the selection, hold down the left mouse button, and then drag the pointer over the text that you want to select. Or Click at the start of the selection, scroll to the end of the selection, and then hold down SHIFT while you click where you want the selection to end.

A word: Double-click anywhere in the word.

A line of text: Move the pointer to the left of the line until it changes to a right-pointing arrow, and then click.

A sentence: Hold down CTRL, and then click anywhere in the sentence.

A paragraph: Triple-click anywhere in the paragraph.

Multiple paragraphs: Move the pointer to the left of the first paragraph until it changes to a right-pointing arrow, and then press and hold down the left mouse button while you drag the pointer up or down.

An entire document: Move the pointer to the left of any text until it changes to a right-pointing arrow, and then triple-click or CTRL+A.

Try 'em out. These methods usually work in any text editor, not just Word. Talking about improving efficiency!

Take care in the meantime until next time. Joel

More Words about Word: What's "Normal" in Normal Style?

2010-09-25T05:56:00.000-07:00

Hello Again:

Well, some more words about Word. But first a question...What's a Style (as applied to software like Word)? How about a predefined compilation of formats (my definition)? Such as {Times New Roman, 12, Green, Left Aligned, 2 Line Spacing}. Each Style has a name and a definition of the formats that make up that Style. Then there are of course default settings such as the default Style for a given application. Microsoft seems to like the word 'Normal', many of the default settings in MS applications are set to Normal, although Normal may mean different settings for the specific format in this case. Excel has Normal Style by default but has different settings than Word.

As far as the Word Normal Style, low and behold, MS decided to change what Normal is for Word after Word 2003 (Word 2007). So, for those of you who have worked with both versions (or even 2010), you probably have "seen" this and wondered what happened.

In Word 2003 and earlier versions, basically the Normal Style is {Times New Roman, 12, Left Aligned, single space, no before or after spacing}, there are certainly other setting to fully define Normal, but good enough for comparison.

In Word 2007 and 2010, Normal is {Calibi, 11, Left Aligned, 1.15 Line Spacing, 0 pt Before, 10 pt After} and other settings.

The HUGE difference as I would see it, not so much the font change but the spacing changes and the implications. Word 2003 has single spacing with no before or after paragraph spacing, thus one would press Enter twice to create the end of a paragraph, a "blank line", and then start a new paragraph. In Word 2007, 2010, pressing Enter once, starts a new paragraph but 'appears' to have a blank line between the paragraphs. This is of course is due to the After Paragraph setting of 10 pts and the line spacing of 1.15. MS decided this for you. They decided the font and spacing setting are the most visually appealling and the easiest way to make it work.

So what if you want it to be the way it was? In Word 2007 notice there is a style "No Spacing", it is the old style spacing but still uses Calibri 11. To change the font, right click on "No Spacing" and choose Modify. In the lower left click Format, Font and then change it to what you want. Do this for any of the format options. After you are done changing any formats, click "New documents based on this template" to make it the default style for new docs. You could do the same thing to the Normal Style, just modify IT and make it the default. You could even create a New Style and make it the default...

So you don't have to be Normal I guess is the bottom line...

Take care in the meantime until next time. Joel

Word: Basic but Important

2010-08-12T06:18:00.000-07:00

Hello again. I guess it's time to put in a word about Word. So I'll start at the beginning...

I taught a Word Level 1 class last week and and once again was reminded as to how some of the most basic concepts about how an application works are missed sometimes when people just "jump into" working with an application and "fumble around" (these are the terms I am told by users, I didn't make it up).

When I teach a Word Level 1 class, one of the first things that is taught in the class is about the "Show/Hide" button. It's the paragraph symbol button. It's in the Standard Toolbar in 2003 and earlier versions and a button in the Paragraph Command Group of versions 2007 and later.

What does the button do (it is NOT on by default)? Well, it shows or hides non-printing characters (dot's for spaces, paragraph marks for paragraphs, right arrow for tab spacing, etc.) Non-printing means the characters don't print but show you the "data" in the document such as spaces.

How many of you after typing something have wondered "is that one or two spaces there???" If Show/Hide were on, just count the number of dots as one dot is one space (zoom in if you need to see it better, like 250%). You all know that one creates paragaphs in Word and the document starts with one blank paragraph (with Show/Hide on you see one paragraph mark in the upper left of the blank document). Honestly, I don't know how someone can confidently work on a document without Show/Hide on or at least turn it on every now and then to see what's there or not there. White space in a document is hard to interpret without this feature; several things can cause white space such as spaces, tabs, indents, alignments, etc. Show/Hide makes it obvious as to what is creating the white space. Do you "see the white", har har.

I hope this gives you some insight and opened your eyes as to what the Show/Hide button in Word is about, no pun intended.

Stay tuned, I'll have more to tell you about in the near future.

Take care in the mean time until next time. Joel

Smart Tags

2010-07-18T06:02:00.000-07:00

Hello again, I know it has been awhile but here's something that I'll bet you have seen but may not really understand the significance thereof...Smart Tags

Starting with Office 2002 (also called XP) Microsoft added a new feature to the Office Applications called Smart Tags. Upon completing an action (such as Paste or Insert) and Icon related to the action (such as a Paste Options Smart Tag (Clipboard icon on a button) in the event of a Paste action) appears on the screen. You can ignore the button, keep on working and it will go away. If you move your cursor over the button you will see a down arrow. Click on the down arrow and you will see a list of options. The idea is when you perform an action, Microsoft has decided for you what happens by default but the Smart Tag is to offer you other options. You ought to at least look at the options to see what your choices are, you may be surprised as to the number of options. For example, in Excel, the Paste Smart Tag has 9 (yep, 9)different options as to what may happen when you Paste!

For those of you who have "been around awhile" you may have known about the Edit>Paste Special command. After a cut or copy, you could choose Edit>Paste Special from the menu and you would be presented with many options as what to do upon Paste. I suspect MS's intent was to provide a way to have options related to an action more "up front" or obvious by using the Smart Tag concept (no personal communication with Bill Gates and his team).

Check it out...I believe it's always good to know what your options are!!

Now just make a decision!

Have fun in the meantime until next time, Joel

Statistics Linear Regression Excel 2007

2010-06-20T17:13:00.000-07:00

OK, so the weather has been so nice lately...

Let's see, how about for you statisticians or wannabe stat kinda guys or gals...you all remember r (linear correlation coefficient) or r2 (coefficient of determination) from statistics? Anyway, something about how well points fit a line. If r or r2 = 1, there is a perfect fit of the data to a line. In plain english, r is equal to the cross product of the sum of difference of each data point from it mean for x and y values divided by the number of data point minus 1 times the product of the standard deviation of the x and y values. Of course the line equation is y = mx + b (m is slope of the line and b is the y intercept).

In Excel after you have created a line chart (really could be several other types but makes most sense with a line chart (remember linear regression...). In Excel 2007 select the chart and the Chart Tools Layout Tab, the Analysis Command Group has Trendline, More Trendline Options (at the bottom of the list). By default, what we want in this case anyway, is Linear. This will add the best fit linear trendline to the chart. But now, maybe the best part...At the bottom of the trendline options dialog box are the options to force a Y intercept value (many times 0) and show the line equation and r2 values on the chart..pretty cool. I just think of all of the calculations to do this...

Otherwise, not into stats...never mind...

Have fun in the meantime until nextime, Joel

Databases: Excel vs Access

2010-06-14T11:05:00.000-07:00

This discussion comes up frequently about databases...

What software should I use to manage my data? In my opion, right up there with knowing about word processing (Word) and spreadsheets (Excel) I would want to understand database concepts and database software (Access).

Many people tell me Excel "does everything...", even database...yes, to some degree...Actually we could set up a "database" using a Word table and it could be used as a data source file for mail merge for example (but probably wouldn't).

Flatfile vs. Relational Database:

What most people don't realize is that it is not very efficient to store all of the information that you may be dealing with in a single table (flatfile). As Dr. Codd (developed relational databases in computers) would suggest, one would want to setup a relational database where one would have many related (linked) tables. This design cuts down on duplication and inconsistencey of information (as well as many other advantages such as referential integrity, had to throw that in).

Access is relation database software and allows one to design and develop a relational databases where Excel is basically a flatfile database. Access has several parts in the database file including Tables and Forms (for entering, editing, and viewing data), Queries (questions about the data in the database), Reports (compiling, viewing, printing data), and Macros and Modules (automation with Macro commands or VBA code contained in Modules). So it has features that go "way" beyond what Excel can do in term of managing a database.

So, if you are "serioius" about having a database, you might consider Access. BTW, Access can be easily interfaced with Excel to take advantage of any of the unique Excel features that could be applied to your Access data even though Access has it own analytical features such as Charts, Pivot Tables, Crosstab Queries, and many others.

PC Applications specializes in Access training and database design and development and can help you at any level of setting up your database(s) using Access.

So, not right or wrong but a matter of being efficient...how do you manage your data? Excel, Access, Index Cards?

Have fun in the meantime until next time...Joel

Email Accounts, Hosting, Email Clients

2010-06-01T08:50:00.001-07:00

Well Hello:

Sorry it has been some time since my last blog post...I did take some vacation. Anyway recently I was working with a client in regards to Email access/accounts/setup and several things came up that I thought might be of interest.

Email account: Where is it? Do you know where your account is?

You may have an Email account in association with your hosting service (how you get connected with the Internet, DialUp, DSL, cable, etc.). john@vail.net

You may have an email account associated with an Internet Site such as Hotmail (Microsoft), GMail (Google), Yahoo Mail (Yahoo), etc. john@hotmail.com

You may have an Email account associated with a web hosting service for your website (if you have one). john@website.com

You may have several of these setup as many people do!

In any case, your Email account will have a login and password established when you setup your account.

How do I get my Email?

If you have an Internet site account or web hosting based account, once you have Internet access, you go to the website where you Email account is setup, such as www.google.com or www.godaddy.com (web hosting site) and then to the Email part of the website and log onto your Email account. All of your Email managment is done on your hosting website.

If you have an account with a service provider, you can log onto the Internet and access your account at your service providers website (check with your service provider to see what the web address is), or you can access you Email though an Email "client" such as Outlook or Outlook Express. Outlook Express comes with Windows and Outlook is part of MS Office. To use Outlook Express or Outlook you need to setup an account in Outlook Express or Outlook to access your Email at you service provider. Your service provider usually has information about setting up Outlook Express or Outlook to access Email. Once setup, Email management is done using Outlook or Outlook Express on your computer as Emails are download from your Email site to your computer when you "check mail".

Many free web based email services do not let you access your account using Outlook Express or Outlook and you must access you account directly through the internet.

Of course PC Applications can help you with any of these issues about setting up and accessing your Email account through the internet or using Outlook or Outlook Express.

Happy Emailing.

Stay tuned...

Excel: Dates, Serial Date Value, and Leap Year

2010-05-06T12:05:00.000-07:00

OK, now for something a little different. For you Excel users...

I was teaching an Excel Level 1 class at Keystone for Vail Resorts yesterday. As always in my Excel Level 1 class, I go over the concepts of how Excel deals with dates. So I thought this might be some good information for the blog and thus here it is.

As I explain in my Excel 1 class, if you type in a date in a cell of Excel such as 5/6/2010, you are really doing two things: assigning a value to the cell as well as a numerical format (Date). I continue to explain in the class that Excel uses what's called the 1900 calendar relationship which defines January 1, 1900 = 1.

Thus Excel really uses what is called the Date Serial Value or just Date Value in Date/Time calculations/functions. I also expain that Excel is aware of leap years...but then the plot thickened as just for grins I looked into this issue and low and behold here's the real story:

Pope Gregory XIII back in 1582 introduced a calendar to correct errors inherent in the prior Julian calendar.

The Gregorian calendar is based on a year of 365 days. But because the actual length of a year is 365.242190402 days, a "leap year" occurs every four years and consists of 366 days with February 29 being the extra day for the leap year. As you can see this is fine if the fraction were exactly .25 but it's .242190402. If you do the math (use Excel) you would discover that in a 400 year interval there should only be 97 extra days and not 100 as per above. To compensate for this error, the Gregorian calendar defines a "leap year" as: Any year that is evenly divisible by 4 but for years that are evenly divisible by 100, they must also be evenly divisible by 400 to be a "leap year" and consequently 3 of the 4 years in the 400 year peiod that would otherwise be a leap year aren't. Thus the years 1700, 1800, 1900, 2100, 2200, 2300, 2500, 2600 are not "leap years". Did you know that 1900 was not a leap year even though it is evenly divisible by 4? So what does this have to do with Excel you ask?? Ever heard of Lotus 1-2-3, Lotus Corp.?

Well, Lotus Corp. (makers of Lotus 1-2-3) assumed the year 1900 to be a leap year when it created the Lotus 1-2-3 program (to make things easier in date calculations, which works for the most part). Lotus 1-2-3 was a spreadsheet program that was around before Micosoft had Excel. Lotus's Date calculations were based on the 1900 calendar but incorrectly had 1900 as a leap year. To be compatible with Lotus, Microsoft decided to use the same date value relationships as Lotus and thus the 1900 calendar was adopted in Excel. SO, Jan 1, 1900 = 1 but also has a February 29 with a Date Value of 60. There shouldn't be a 2/29/1900 but THERE IS!...3/1/1900 should be 60 but it's 61...Microsoft documents this situation and notes that there are "workarounds" if you deal with dates prior to 3/1/1900. There is no problem with any calculations involving dates after 3/1/1900.

Holy smoke screens batman! Think about it..

Stay tuned...

And that's not's all, a "final" comment on shortcuts

2010-05-02T14:22:00.000-07:00

What else might I know about shortcuts you ask...well "Page 4" as has been said before. Thanks Paul.

Have you ever noticed (particularly in Office applications version 2003 and earlier) there is an underlined letter in the Menu items eg File, Edit, etc.? Ever just wonder WHY?? Just because??

If you like using the keyboard, pressing the Alt key "activates" the keyboard menu commands, ie, the underlined letters. In other words, Alt>F (press Alt then press F) is the same as clicking "File" in the menu. Notice menus and submenus also have items with an underlined letter. Pressing the letter (if you have already pressed Alt) is like clicking on the item using your mouse. Thus in an earlier blog post I mentioned Ctrl+P (Ctrl and P keys pressed together), to print, could also be accomplished by Alt>F>P. Now you see that all Menu items have some keyboard shortcut!!

In Office 2007, pressing Alt causes a shortcut key to "highlight" over the Tabs, eg an H appears on the Home Tab after pressing Alt; pressing H causes the shortcut key or keys for the ribbon items to highlight eg an AR apprears on the align right button in Word 2007. Thus Alt>H>AR would align right a paragrah in Word 2007.

Thus, there are many options of getting things done without grabbing the mouse and having to click somewhere. It might behoove you to get to know a few of the keyboard shortcuts for things you tend to do repetitively! It's all about being efficient in gett'n er done! Do the best you can...

Stay tuned...

Even more about keyboard shortcuts

2010-04-27T18:28:00.000-07:00

So far in the first 2 blogs about keyboard shortcuts, I provided information about specific shortcuts such as cut, copy and paste. Now some generic information about shortcuts.

Have you ever noticed that in many dropdown list in a menu, to the right of the command such as Edit>Cut there is Ctrl+X in this case? Yep, that's the keyboard shortcut for that command. I was teaching a class yesterday and mentioned this to the 2 attendees and they both were surprised that they had "never seen that before".

If you tend to do the same thing over and over again like Edit>Find you may just remember Ctrl+F. You may also find the command you learn in one application is really more generic and works in many applications such as Ctrl+F for Find...

Happy Shortcutting

Other useful shortcuts

2010-04-25T09:00:00.000-07:00

So if you like cut, copy, and paste shortcuts, here are some others that can be used every day while working with MS Office applications:

Open - Ctrl + O
Save- Ctrl + S
Print- Ctrl + P
New - Ctrl + N

Give these a try!

2010-04-24T12:23:00.000-07:00

Welcome to PC Applications Blog! I will post tid bits of information from time to time relating to working with Windows or MS Office applications. Having worked with Microsoft applications for over 20 years I have a few things to say. Otherwise check out our website www.pcappplications.com for all of our services. Well with out further adou...here's the first tip...

Sometimes learning keystrokes to get something done can be more efficient than grabbing your mouse and clicking or even right clicking to use the shortcut menu as it as called. My favorites are: cut (ctlr+x), copy (ctrl+c) and paste (ctrl+v). These can be used even if you do not have access to a menu or shortcut menu (like in a dialog box, eg). Give it a try!