Wednesday, December 7, 2016

Excel Vlookup explained

I just posted a new video to my Youtube channel - Excel Vlookup explained. Check it out.

(all versions of Excel)

Wednesday, November 30, 2016

Excel Tip - Viewing a Worksheet

I have worked as an IT trainer for over 12 years. During those years I conducted a lot of classroom training, but I also did a lot of one-on-one. Here is one of my stories.

I often had what I would call “open project days.” My office had a conference table that could seat 4 people comfortably. Anyone could come in on those days and work on their Excel project and I was there to answer any questions. Sometimes people just used this to get away from their desks, but I never went through one of those days without being of some assistance. It usually did not happen in the form of a question, but rather an exclamation of frustration.
Jeanie mumbled “There are so many date columns, I can’t remember what goes where.”
“Do you not have headings?” I questioned, misunderstanding.
“Of course, I have headings, but when I scroll down to enter data, I can’t see my heading,” she responded in a frustrated tone.
“I have a cure for that,” I said as I walked over.

Here is what I showed Jeanie to make her data entry a little easier - Freeze Panes

Freeze panes allows you to lock specific rows and columns so that they will always be visible on screen no matter how far you scroll to the right or down.

Go to the View tab - Freeze Panes.

Wednesday, November 2, 2016

Excel tip: Wrap Text

Here is one I see a lot. When you want to shorten the column and there are two words in the header. I have seen 2 rows used for the header. 

No need to do this, use Wrap Text.
If you want text to appear on multiple lines in a cell, you can format the cell so that text wraps automatically, or you can enter a manual line break.
Wrap text automatically:  Select the cells you want to format.  On the Ribbon on the Home tab, click the wrap text icon.

Enter a line break manually: To start a new line of text at a specific point in a cell, click where you want to break the line, and then press ALT+ENTER.
Note:   If all wrapped text is not visible, it may be because the row is set to a specific height. Data in the cell will wrap to fit the column width and row height. You may need to adjust the row height to see all text. 

Monday, October 31, 2016

Word Tip - How to get back to your last location

To move the cursor to the last location you were working when you last saved the document press Shift + F5

Tuesday, August 2, 2016

Excel - Remove leading and trailing spaces from text

You may need to remove extra spaces before, after or sometimes in between text. Extra spaces can happen when you have copied and pasted from another application into Excel.

I have a list of books I have read that I was keeping in OneNote. I copied the information over to Excel and this is what I got:

I can use the TRIM function to get rid of those extra spaces. TRIM removes unwanted spaces in your text, except single space in between words.

=TRIM(this is the cell that you want all those extra spaces removed from).

Extra spaces aren’t always this easy to spot, especially those at the end. An unseen extra space can cause problems. TRIM is a great Text function that helps with data cleansing. 

Sunday, July 17, 2016

Excel - Auto Calculate

Need to see quickly what a range of cells containing numbers add up too? You can see the results of an Average, Count, Numerical Count, Min, Max, and Sum, etc. for a range of cells without writing the formula using a tool called Auto Calculate.
Select a range of cells with your mouse that you would like calculate – for example that you want to know the Sum (total) of.
Look down in the lower right hand corner of your screen on the Status Bar. Notice that there are some calculations listed there based on your selected data.

You can customize what calculations appear on the status bar. Right-click on the status bar and then check the desired AutoCalculate choices to make them visible on the status bar.

Tuesday, July 12, 2016

Microsoft Publisher or Word

Yesterday, I taught a small group some of the basics of Microsoft Publisher. I have always preferred Publisher over Word when it comes to designing a flyer or brochure.

Most people are more familiar with Word and I often get asked why even use Publisher.
I do acknowledge that Word has gotten a lot more friendly in regards to creating documents that contain images, however, Both Microsoft Word and Publisher can help you add images and text to a document - they share a lot of common tools that make that possible. However, if you need a desktop publishing program, choose Publisher. Word excels at helping people create other types of documents. Think of Word as a powerful text editor that handles general writing tasks and Publisher as a more specialized tool to help with your creative designs.

Here are some advantages of Publisher over Word:
  • Publisher has easy to use templates for brochures, newsletters, and greeting cards.
  • You can use ruler guides to section parts of a document. This helps with the placement of objects. 
  • Precise control over positioning text with images and other design elements.
  • Easier to position textboxes next to images and wrap text around images. 
  • Poster and banner designs with easy width and height choices to match your needs.
  • Create custom page sizes.
  • Identify and correct unintended desktop, commercial print, Web, and e-mail problems using the improved Design Checker. 
  • Support for large jobs that require the services of a commercial printer, including composite CMYK PostScript output and advanced print settings for better color separations.

Tuesday, July 5, 2016


Tabular Data

At my previous job as an IT Trainer, there were days when not a lot was going on. I would use this slow time to wander the halls, mainly to remind people that I was there. Software training is one of those things that most people feel they don’t have time to attend or don't need. However, if they saw me out and about, they felt comfortable asking about something they had been having problems with. That is what happened when Darlene saw me. 
Darlene had just started her new job as admin support in the department. She was still getting to know many of the documents that she had to work with.

“How weird to see you at this very moment,” said Darlene as I walked up to the service counter.
“What’s going on?” I responded.
“I need to update the motorpool spreadsheet and the formulas aren’t working,” she said as her eyes never left the monitor.

I came around the counter and she got up from her chair so I could take a seat. I clicked into various cells and then informed Darlene “the formulas are not working because there are no formulas.”
Darlene looked at me with a bit of confusion.
To clarify, I said “whoever created this spreadsheet did not setup formulas, they simply did the calculations manually and entered the result into the spreadsheet.”
“Wow,” was her response.

Wow, indeed. One of the main reasons for using Excel is to collect data. Many people keep lists in Excel. Even if you are not sure how you want to use this data or list in the future, setup your spreadsheet the right way. You will realize over time what data you want to extract. You can’t measure what you don’t track. 

Not a great way to track data

Take a look at this spreadsheet above. This is the motorpool spreadsheet that Darlene had inherited. It is for tracking what technician has checked out a car, what unit they are assigned to and how long the car is out.
What’s wrong with this?
Notice Column C (Usage), that the technician last name, hours checked out, and total hours are all in this column. Four pieces of data in one column. Notice also, that Column D and E are simply repeating the data in B and C, except it is the evening unit.

Starting in Row 15, is the Average Usage Per Unit. All of these totals are manually inputted. Not a single formula in this spreadsheet. The way this spreadsheet was structured,  Darlene could not pull any useful data out of it. 

Each piece of data now has its own column

We revamped the data. Each piece of information had its own column. We separated out the time the vehicle was checked out and checked back in. This allowed us to set up a formula in Column G to automatically calculate the total hours the vehicle was out.
With the spreadsheet reworked, Darlene could now easily filter the data. If she wanted to look at only the Day shift, then she could filter out the evening shift.
We were also able to use a SUMIF function to calculate total hours a vehicle was checked out per unit.

SUMIF function

We were also able to use a SUMIF function to calculate total hours a vehicle was checked out per unit.

No more manual calculations.

How you setup your spreadsheet really does matter. 

Friday, June 17, 2016

Speed up Your Typing with AutoCorrect

MS Word’s AutoCorrect feature corrects typos and misspellings as they occur. For example, if you 
accidentally type “teh,” Word changes it to “the.” The feature saves you time as you’re working on your document. It also helps you eliminate errors that you might otherwise miss.
AutoCorrect is also used to apply special formatting. For example (c) is changed to the copyright symbol.

Like many of Word’s features, you can customize AutoCorrect. You may want to assign abbreviations to frequently typed names, addresses, or terminology.

To customize AutoCorrect in Word, follow these steps:

1. In 2007, click the Office button, in later versions, click the File tab.
2. Select Options
3. Click Proofing in the left pane
4. Click the AutoCorrect Options button
5. Select the AutoCorrect tab
6. In the box labeled Replace type the abbreviation
7. In the box labeled with enter the full text
8. Click Add
9. Click OK

Tuesday, May 31, 2016

Rename an email subject line (in MS Outlook)

Do you get emails with a subject line that don’t make sense or don’t have anything to do with the actual message?
You can rename those email subject lines.
1. Double-click on the email to open and select the text in the subject line.
2. Type the new subject line. Click on the X to close out of the message window.
3. You will get a message dialog box asking if you want to save changes. Click Yes.

Friday, May 20, 2016

MS Outlook - Create reusable text blocks for e-mail messages

Create reusable text blocks for e-mail messages

Do you find yourself repeating certain text in your emails? Maybe you include directions to your office, a standard set of questions for potential clients, disclaimers, contract clauses, or your logo. Quick Parts enables you to save selected text or graphics for quick insertion into your Outlook e-mail message without having to retype the selection or cut and paste. Use Quick Parts to save text that you use time and again.

Don’t retype or copy and paste – use Quick Parts. Here’s how:

1.  From a new mail message, click in the text area of the message and type the text you would like to store as a reusable building block. Apply any formatting to your text.
2.  Select the text.

3.  On the Insert tab, in the Text group, click Quick Parts.

4.  Click Save Selection to Gallery Name Gallery.

5.  Fill out the information in the Create New Building Block dialog box. Click OK.

6.  Now the next time you need to insert certain text, go to Quick Parts and click on the text to insert.   

Tuesday, May 10, 2016

Excel - Indent Text in a Cell

What happens when you hit the Tab key on your keyboard in a Word document?
It will indent 5 spaces. 
What happens when you hit Tab in Excel? 
It moves to the next cell. 
So how do you indent text in a cell in Excel? 
By increasing or decreasing indent.

Pressing [Tab] in Excel doesn't indent text in a cell; it simply moves the cell pointer to the next cell. 

To indent text in a spreadsheet cell: On the Home tab, in the Alignment group, use the Increase Indent button.

Enter your text without any lead spaces, select the cells whose entries you want to indent, and click the Increase Indent button. Each time you click that button, Excel nudges the selected text to the right. If you go too far, you can click the Decrease Indent button to nudge the text back toward the left.

Tuesday, April 26, 2016

Microsoft Word - Using the Ruler to Indent Paragraphs

The easiest and most direct way to change the indents of paragraphs is by dragging the indent markers on the ruler.

First Line Indent
1. Select the paragraph or paragraphs to be formatted.
2. Drag the first line indent marker to the right to change the indent of only the first line.

Hanging Indent
With a hanging indent, the second and all the following lines of a paragraph are indented more than the first.
1. Select the text where you want to add a hanging indent.
2. Drag the hanging indent marker to the right. It’s the lower triangle on the ruler.

Note: If the ruler is not visible, click View and then select the Ruler check box to show rulers.

This works on all versions of Word. 

Tuesday, April 19, 2016

####### Excel Error?

What does it mean when you see the number symbol in a cell? 

The column is not wide enough to display the content.

How to fix it? 

Normally, just increase column width, but you can shrink contents to fit the column, or apply a different number format.

Friday, March 25, 2016

Microsoft PowerPoint - Crop an Image to a Shape

Select the picture you want to crop to a specific shape.
Under Picture Tools, on the Format tab, in the Size group, click the arrow under Crop.
Click Crop to Shape and then click the shape you want to crop to.
Click here for YouTube video

Thursday, March 24, 2016

Turn Your Mouse into a Laser Pointer in PowerPoint

While in Slide Show view, you can use your mouse as a laser pointer.

From Slide Show view or Reading view:

Press and hold CTRL, and then click and drag the left mouse button to point to the contents on the slide that you want to draw attention to.

PowerPoint 2010, 2013, 2016

Click here for YouTube video

Friday, March 18, 2016

Create a Professional Looking Flyer in Word - Video

Hey, I've added another video to my YouTube Channel - Create a Professional Looking Flyer in Microsoft Word. Check it out. Create a Flyer in Microsoft Word

Tuesday, March 15, 2016

Learn, Unlearn, and Relearn: Why Constant Learning is the Key to Success

Learn, Unlearn, and Relearn: Why Constant Learning is the Key to Success

As Future Shock author Alvin Toffler says, "The illiterate of the 21st Century will not be those who cannot read or write, but those who cannot learn, unlearn and relearn."

Friday, March 11, 2016

Excel Sparklines

Are you using Sparklines in Excel? Charts can bridge the gap between our spreadsheet data and the real world. Sparklines are miniature charts that fit into a single cell and make it easy to grasp the data.

Saturday, February 6, 2016

Create a Drop Down List in Excel

Create a drop down list in Excel - In this video, I will show you how to create a drop-down list in Microsoft Excel. 
To make data entry easier, or to limit entries to certain items that you define, you can create a 
drop-down list of valid entries that is compiled from cells elsewhere in your workbook.
When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then select the entry that you want.

Tuesday, January 12, 2016

Excel Tip for Navigating Worksheets

If your workbook contains many worksheets, and not all of the sheets' tabs fit on the screen, you may find it tedious to scroll through the tabs or use the keyboard to activate a distant sheet.
Here's a trick for navigating those many worksheets. Display a pop-up list of sheet names by right-clicking one of the arrows to the left of the sheet tabs that are located at the bottom of the worksheet window. Select a sheet from the list.

You can also move to different sheet tabs by  using Ctrl-Page Up or Ctrl-Page Down.

Monday, January 11, 2016

Using the Ruler in MS Word (It really is handy)

Click here to watch the MS Word Ruler Video

After watching this video, you will be able to:
· Improve alignment by using tab stops 
· Apply indents to control text alignment
· Adjust left, right, top and bottom margins

You will be a “ruler of Word"