Thursday, October 26, 2017

Excel Basics

I am a guest blogger on  They asked me to create some new-to-Excel posts for beginners.  Just click the links below to go right to those lessons:

Excel Spreadsheet Basics Everyone Should Know:  a tour of Excel, discussion of the cursors, and how to move around

Formatting Cells in Excel For a Better Understanding of Information:  basic formatting

Excel Dates | How to Format Dates in Excel:  use Excel's formatting shortcuts and more advanced formatting features to make your dates just the way you want them.

Excel Formulas and Dollar Format:  create a simple spreadsheet multiplying quantity and price to get the cost, plus adding up the sales

Excel Tables | How To Format Excel Tables with Total Sort and Filter:  format information as a table so you can sort and filter

How To Create Charts and Graphs in Excelmake your numbers visual with a chart or graph

Saturday, January 7, 2017

Voice Mail for the Small Business Owner

Do you have one phone for business and one phone for personal calls?  If not, your voice mail for your phone should be professional.

What are the parts of an excellent voice mail message?
Call a few similar businesses after hours and listen to their messages.  What do you like and what don’t you like?  What makes you feel like your call really is important?
Does it answer the basic questions:
  • Who?
  • What?
  • When?
  • Where?
  • How?

Before recording any message, practice in front of mirror and smile the entire time!  This practice will also help in your daily interactions with clients.
Who?  State your name and/or the name of your business.
What?  Think of your missions statement or elevator pitch.  State what you do in one sentence.
When?  State your hours.  If you do not have regular hours, such as “Monday through Friday, 8 to 5,” think of the simplest way to state your hours so someone can understand.  Practice this with a friend or ask for help.
Where?  State your address and cross-streets if applicable… or say “We come to you!”
How?  What is the best way for the customer to reach you for an appointment or questions?  Email, website, leave a message.  You may wish to state how quickly they may expect to hear from you:  24 hours, the next business day, etc.
Many cell phone companies allow a caller to press a key, like “*” to skip the message.  Please check with your provider, and ask if this is possible.  If so, add the instructions to the end of your message, such as “To skip this message in the future, please press ‘star’.”
Clear and concise is the key!

Tuesday, August 2, 2016

Automatically Playing an Embedded YouTube Video in PowerPoint

I'm getting ready to teach another PowerPoint class, so I got out my previous class notes.  I teach this class about once a year, and with every new update from Microsoft or Google's YouTube, I know my instructions will be out of date.  Sure enough...

First, I was pleasantly surprised to see that Microsoft has added a “Online Video” option for adding video.  It even has an option to search for a video on YouTube. Great, right?

It’s only great if you want that big red YouTube play icon right in the middle of your PowerPoint show.  You also have to click on the big red YouTube play icon to play your video.  This is not always the easiest thing to do if you are in a presentation hall and using a laser pointer.

My class is beginning PowerPoint, so of course I will show them how to do it with the big red YouTube play icon, but I was sure I could still use the old instructions.  Right?

Wrong again.  The old version used the “Old Embed Code” which is no longer available from YouTube.  I googled and googled for a workaround and found several, but none that were recognized by PowerPoint.

If finally decided to try my limited HTML skills and combine the old with the new, right?  IT WORKED!

These instructions have been updated and work as of August 2, 2016. Tomorrow, you’re on your own.
  1. Using the internet, find your YouTube video
  2. Pause the video (because it’s annoying when it’s running and you’re trying to think)
  3. Click on the word Share below the video
  4. Click on the word Embed below Share
  5. Select “SHOW MORE” below the Embed Code
  6. Select your Video size.
  7. I always deselect “Show suggested videos when the video finishes”
  8. Click the HTML code in the box so it is highlighted
  9. Right click and copy (or Ctrl C)
  10. In PowerPoint, click on the Insert tab>Online Video.
  11. Instead of using the built in YouTube search, click on the Video Embed Code box, and paste the embed code by right clicking and paste (or Ctrl P).  (You can also paste into MS Word or other text editing software to make the change below – then simply copy and paste the new code into PowerPoint.)
  12. Here is the tricky part.  After the video name which is a bunch of gobbledygook like “Oe6lBGO6OSk” you’ll see a question mark (?).  Place your cursor to the right of the question mark and type this:

(that is a semicolon, not a colon)  

That's it.  Now hit enter, and a black video box should show up on your slide.   If you get an error message, try again.  As always, preview the current slide in Slide Show tab and to see if it worked.

Thursday, September 10, 2015

Inserting and Formatting a Table with a Formula in MS Word

I was recently teaching a MS Word workshop and ran out of time to teach about inserting a table, so here is that lesson.  I was going to use it as an introduction to Excel, with a subtitle: why-tables-are-easier-in-Excel-than-Word.  Sometimes, though, you just need a tiny table in the middle of your document without inserting a spreadsheet, so Microsoft has included some basic table functions in Word.

First, let’s define a table using spreadsheet terms.  This will help when we transition to Excel. A table is a grid of cells laid out in rows and columns.  Rows go across horizontally and columns go up and down vertically. Memorize that while I wait.

Insert and Create your Table

Ready, now place your cursor where you want the table.  You’ll be able to move it later, so just give it a good guess for now.

On the Insert tab>Tables section>select the drop-down arrow below Tables. The Insert Table window menu will appear.  Drag your cursor through the squares to select your number of cells in rows and columns.  For this exercise, use two columns and five rows.  Word automatically puts in you “Drag” mode, so you don’t have to hold down the mouse button.  Drag through two horizontal squares (columns) and five vertical squares (rows) as shown.

The arrow in the illustration points to the text above the grid.  It will change from “Insert Table” to a description of your table, 2x5 Table, when you have our chosen layout selected. When you click on the grid square two columns and five rows down, Word inserts a two column, five row table in your document.

All columns are spaced evenly between the current margins and the rows are spaced evenly based on your current line spacing.

Add Text and/or Numbers

Let’s add data to your Table.  You may click, tab, or cursor to go from cell to cell.  (A not-so-subtle reminder that numbers have to be checked for typos.  Spell-check doesn’t help here.)

I recently moved to a new town, so I’m going to use US Census Bureau population estimates from the neighboring communities.  Just type the information, starting with a descriptor in the top cells; we’ll format it later.

2013 Est Population
Prescott Valley
Chino Valley

Formatting your Table with Table Styles

Now let’s make it easier on the eyes with some formatting.  When making changes to the format of your entire table, always select the table first.  When selected, a small “Move” arrow group will appear on the top left corner.   Select your table, then on the Home tab>Styles section> select No Spacing to get rid of any strange formatting, like double-spacing.  Do this as a matter of habit.

Hopefully, you noticed that when you select your table, the Table Tools tab appears with two tabs:  Design and Layout.  Let’s select a pre-designed style.  On the Tables/Design tab>Tables Styles section>click the more choices drop-down arrow.   Let’s pick a simple and bold style today.  Scroll down to the black header, horizontal borders, with no vertical lines inside.

Resize and Move your Table

To resize our table, select and drag the bottom right corner handle to the left until “Dewey-Humboldt” goes to two lines.  Stretch it back until it is only one line.

Now grab the “Move” arrow on the top left and drag your table to the right side of the page.  Your words will automatically wrap around it.

Align your Text or Numbers within your Table

In a table, you can set each cell, each row, or each column so that is aligned up, down or center, and right, left, or center.  Click on the “City” cell.   In the Table Tools>Layout tab>Alignment section>there are nine alignment choices.  For “City,” select Align Bottom Left icon.

Numbers and their headings should always be right-aligned.  So we want the entire right-hand column aligned to the right, right?  Hover your mouse over the right-hand column until a small black down arrow appears.  That is the Select Column arrow.  Click your mouse when the arrow appears. (You may also drag through the right-hand column from top to bottom.)

Your column will be selected.  In the Table Tools>Layout tab>Alignment section>select Align Top Right icon.  Your column heading and the numbers will move to the right of the table.

Add a Totals Row with a Formula

These four towns are called the Quad-Cities, and inquiring minds want to know the total population, but we don’t want to get out a calculator.  Word has a few built-in formulas for our table.

First we need to add a row for our total.  Click anywhere on the bottom row of our table.  Right-click and select Insert>Insert Rows Below. A blank row of similar size and shape will be added below.(Notice you can also delete and shift cells or rows from this right-click menu.)

In the City column of our new bottom row, type “Quad-Cities Total.”  Tab over (or click) to the bottom row of the Population column.  In the Table Tools>Layout tab>Data section, select Formula
and the  Formula window will appear.  The default formula is “equal” to the “sum” of the column “above” so the formula is filled in for you as “=SUM(ABOVE).”  Click OK and the total will appear.

We want to emphasize the total line, so let’s change a Border in our Table.  With the table selected, go to Table Tools>Design tab>Border section.  The default border is 1/2 point.  Change the border to 3 point for an emphasis line.  Now your mouse has changed to an Ink Pen icon.  With this ink pen mouse, draw through the line above your totals row.

Review, Proof, and Update your Table

We are almost finished.  Always review your table for readability.  Does it give the information we want at a glance?  Is there any confusion?  Remember the priority is to make your information stand out.  Colors and fonts can be distracting.  Use a critic’s eye and double-check your numbers for typos.

Now, go learn Excel, it’s much more fun and faster.  Why?  If you make a correction to your numbers, your total will not automatically update in MS Word.  You need to click on the total number, right-click and select Update Field to adjust the total.  (Excel does this automatically.)

Monday, August 31, 2015

Adding my Signature to a Letter for Emailing

What’s a Signature Block?

To create this, at the end of your letter, drop down two lines with one or two Enters depending on your current Style.  Type “Sincerely,” and press enter 4 times to create room for a signature or emphasis for your name.  Type your name as you sign it, for our example I will be “Joan Smith.”

Isn’t a typed name good enough?

A typed signature block is acceptable to recipients of most emailed letters.  Sometimes you might want to use a script font to retype your name in the middle of the blank lines above the name typed in the normal font.  Some script fonts are smaller, so use a bigger size for emphasis and legibility.  For example here is a Freehand font in 24 point size:

What about a Real Signature?

Let’s say you need a “real” (actual a digital) signature. Take out a piece of plain white paper, regular 8 1/2 by 11 size white letter paper – the paper you use for a copy machine.  Using a black Sharpie, create a large signature in the middle of the page.  The large size will make the file more clear and legible when you resize it.

Use your scanner to create a black and white .jpg file.  (Most office stores such as Staples or Office Depot have a print shop that can scan this for you for a small fee if you don’t have a scanner.)  Save in a central, easy to find location.  Save as Your Name Signature.jpg.  Open the signature with photo editing software and crop it so that the empty edges of the white paper are left out, but your signature is still there.  Resave the image.

To use your scanned signature, start with your cursor in the blank area of your signature block.  On the Insert tab>Illustrations section>select Pictures.  Browse for your scanned signature, then click Insert.  Select the image of your signature, and grab a corner handle to adjust the size of your signature.  When you click on your image, a small Layout Options box appears next to its top right corner.  Click the Layout Options and select the bottom right choice, In Front of Text.

Now you may drag your signature image all over the page without affecting the text spacing.  Drag and move your signature above your typed name.

If your signature has white space around it and blocks out the text, you need to make the white space transparent.  In the Picture Tools tab>Color section>click on the Color drop-down arrow.   From the menu, select Set Transparent Color.  Your cursor will turn into an ink pen.  Using the cursor, click on the white background of your signature.  The white background will turn transparent and your text will show through it.  Readjust size and location of your signature if necessary.

Now you can save your document as a pdf (File tab>Save as… File Type PDF).  Your signature is there and no one can edit your document.

How do I sign a PDF?

If someone emails you a pdf, you can sign the document with the free Adobe Reader software.  Open the document with Adobe Reader.  Simply click on the “Fill & Sign” tab on the upper right, and then “Place Signature.”  The first time you do this, you will need to browse for your signature image.  After using it once, Acrobat Reader will remember where your signature is until you change it.

Once you save this pdf, it will automatically add “-signed copy” to the end of the name.  If you have pdf editing software, such as Adobe Acrobat, editing will not work on the saved document.  You will need to edit the original, before the signature was added – so don’t delete that one.