We provide on-site training in Microsoft Office Excel, Word, PowerPoint, and Outlook. We train anywhere in the US. Chris Menard is the first Microsoft Office Master Instructor in the world and has been featured on Microsoft's training website.
to Microsoft Office. You can install MS Office on 5 computers and also on the Ipad, Android phone, etc...Each user also gets 1 TB of storage. The annual fee is $99 or you can pay monthly. So you aren't buying Microsoft Office you are renting it or subscribing to it.
The advantage of MS Office 365 is when a new version of MS Office comes out, like Office 2016 for Windows will be out around October 2015, you get the MS Office version for free. If you went and purchased Office 2013 at Office Depot or Staples, you will pay $400. You own that copy but when the new version comes out, you have to go spend another $400. To me the subscription service, Office 365, is the way to go for home users.
You also get 1TB (1,000 GB) of storage on Microsoft OneDrive for each user with Office 365. So all five users get 1TB each. That is a bargain. You also get MS Office online for Excel, Word, PowerPoint, and OneNote.
Try Office 365 out for free
Microsoft Word - Create a Directory with Excel as your Data Source
Using Microsoft Word's Mail Merge feature you can create a directory with Microsoft Excel as your data source. This short two minute YouTube video shows you how to do it. An example of a directory is a phone list for employees in MS Word.
Microsoft Excel - Consolidate Data
Another excel feature that is very useful but rarely used is Consolidate. Consolidate allows you to add multiple worksheets or ranges that aren't set up the same on every worksheet.
Example: Product A is located in A4 on one worksheet and in A7 on another worksheet. To total the orders for Product A use Consolidate. Click on Data | Consolidate feature. Consolidate is also an easy way to summarize duplicate data on the same worksheet.
Excel Video on how to Transpose Data
Need to switch your data from columns to rows or vice versa? Transpose data in Microsoft Excel allows you to switch your rows and columns. To transpose, select your data, copy it, click in a blank cell, click the arrow below paste and select Transpose. Delete the rows you no longer need.
Microsoft Excel - 3D referenceQuestion
: How to I create a 3D reference Excel?
Another excel feature that most people do incorrectly. If you are summing up multiple worksheets that are set up the same except for the numbers, the incorrect way is to do = click the worksheet, click the cell, plus symbol, and click the next worksheet and keep repeating. This method may give you the correct answer but it is very time consuming and if you add worksheets it will have to be edited.
The correct way to add multiple worksheets is to:
- Click in cell B5 of Creating 3-D Formula. Click AutoSum, click the first worksheet which is North in our example.
- Hold down the Shift key, click the last worksheet which is West in our example.
- Let go of the Shift key.
- Click cell B5 and press ENTER.
Microsoft Excel - filters and subtotal function
When using filters in Excel, the functions average, max, min, and sum will give you the wrong answer since they include the hidden rows. Use the Subtotal function to get the correct answer. =subtotal(1, range) will give you the Average. =subtotal(4, range) will give the Max or highest value. Use 5 for the Min or lowest and 9 to sum or total.
Microsoft Excel - How do I fill in empty cells on a worksheet quickly?
- Select the first cell that has information to the last empty cell.
- Press F5 on the keyboard
- Click Special. Select Blanks. Click OK twice.
- In the active cell, reference the cell above or to the right. Example =C2
- Hold down CTRL and press ENTER.
- Select all, copy and paste values.
Microsoft Excel - How do I create a data validation list?
This works in all version of MS Excel. Also shown is how to quickly remove duplicates in Excel.
Microsoft Word - add the filename and path to the footer of a document
To add the filename and path to a Word document do the following:
- After you document is saved, in the footer of the document, click the Insert Tab
- Click Quick Parts
- Select Field
- Change categories to Document information
- Select filename
- Check add path
- Click OK
- Go to print preview and view the filename and path
- Close print preview
Microsoft Word - Odd and Even headers and footers for duplex printing
Question: how do I print double sided with the page numbers on the outside of the pages?
If you want to print two sided, here is how you create odd and even headers or footers in MS Word.
Creating odd and even headers and footers are a must in Microsoft Word if you are going to do two-sided printing. Think about opening any book. The page numbers are always on the outside of the pages. Here is a short video I created on how to do 2 sided printing.
Microsoft Word - Add Decimal Tab Stops with leaders to an existing Word DocumentSteps to add decimal tab stops
- Select the lines you want to add a tab stop.
- Turn on the ruler if necessary (View tab - check Ruler)
- Click the Home Tab and click paragraph setting
- Click Tabs in the lower left
- Type in the number you want so set your tab stop (Example: type in 4.5 or 5 or 5.5
- Select decimal and select leader number 2 (that is a dot leader).
- Click on set and click OK.
- Click in front of the number and press Tab on the keyboard
AutoCorrect in Microsoft Word
AutoCorrect is the built in feature that correct typos and misspelled words, as well as to insert symbols and other pieces of text as you type. You can add your own text entries to AutoCorrect. Example: if you are constantly typing Gwinnett Medical Center you can add an Autocorrect entry so when you type gmc1 Gwinnett Medical Center appears. The reason I added a 1 at the end of gmc is because gmc is an abbreviation I still need to use.
Steps to make an AutoCorrect entry:
- Click the File tab.
- Click Options.
- Click Proofing.
- Click AutoCorrect Options.
- On the AutoCorrect tab, make sure the Replace text as you type check box is selected.
- In the Replace box, type the abbrevaition of a word or phrase that you often type and add the number 1 after it. Example cdc1
- In the With box, type the Phrase — for example, Centers for Disease Control
- Click Add.
- Click OK.
Now when you type cdc1
and press Enter or the space bar, Word will insert Center for Disease Control
Google Calendar - how do I import appointments and events into my Google Calendar?
A lot of people use Gmail and the Google calendar. Businesses even use Google Apps for their email client and calendar. If you have to add a couple appointments, just add them manually, but if you have to add a lot of appointments to your calendar, you can import a CSV file into your calendar.
In the video below I show specifically how to import appointments into your Google Calendar. If you are like me and use multiple calendars, you can even decide what calendar to import into.
Here is a CSV file to download if you don't want to set up a CSV file from scratch.
Row 1 in the file is the header information. Don't change it. Row 2 is a sample appointment to get you started. Just type over the sample appointment in row 2.
Microsoft Excel - Data Validation based on another data validation list
This short video shows how to create a data validation list in Excel based on another data validation list. You will need to name ranges and use the Indirect function in Excel.
Microsoft Excel - Mixed Reference
Use Mixed Reference in Excel when you need to freeze just a column or row when doing a formula. In the video shown, we are trying to determine gross revenue but we don't know the number of attendees yet or the price to charge for our conference.
The file used in this example can be downloaded here.
Microsoft Excel - combination chart (2 axis chart)
When charting data in Excel with a large variance, you need to use a combination chart with 2 axis.
The file used in this example can be downloaded here.
Microsoft Excel - Conditional Formatting with Functions
Use functions with Conditional Formatting in Excel to highlight entire rows above the average, every other even row, every other odd row, or every fifth row.
The video demonstrates how to show the entire row above the average.
The function used is =$F2>=Average($F$2:$F$18).
At the two minute mark, the video show how to make every other row a different background color and how to make a fifth row a different background color. =mod(row(),2)=0
The file used in this example can be downloaded here.
IRFANVIEW - batch resize images or picturesQuestion:
I have a lot of pictures I need to email to friends and family to share. Most of the images are over 4 MB. Is there an easy way to quickly resize them all so I can email them out. My email account has a file attachment maximum of 25MB. I have over 50 pictures.
Answer: Yes! IRFANVIEW is a free program for home use. You can use it to batch resize your pictures. This comes in handy for PowerPoint so the presentation does not become to large, for emailing images, and for uploading pictures to Facebook. In the video below, the original ten pictures are 36 MB. After resizing, they are 1 MB!
- Right click and open an image in IRFANVIEW
- Click File | Batch Conversion Rename
- Click Add All
- Click Advanced and make sure the width is between 600 and 800
- Click OK
- Click Use Current (look in) directory
- At the end of the last backslash type in "smaller" without the quotes. Example: C:\Users\Triality 1\Desktop\Pictures\smaller
- Click Start Batch
- Close IRFANVIEW when done
Microsoft PowerPoint - insert a Word Doc into PowerPointQuestion
: I have a Word document that I need to insert into PowerPoint. Is Copy / Paste the only way to get it in PowerPoint?
Answer: Use PowerPoint's Slide from Outline feature to get a Word Document into PowerPoint.
- Home Tab
- New Slide drop down arrow
- Slides from Outline
- Find the Word Document
Make sure you use Heading 1 and Heading 2 styles in MS Word. Heading 1 becomes the slide title and Heading 2 becomes the first level bullet. Normal style will not go into PowerPoint.
Microsoft Excel - Add number quicklyQuestion:
is AutoSum and Autofill are fastest excel tools to add numbers and copy formulas?
Answer: usually, but often you have groups of data you want to add that are not part of one data range. In that case this tip is the fastest way to add numbers. Here is a short two minute video I created for SCB TV on the fastest way to add numbers in Excel.
- Select your first range and stop where you want your totals for the months or quarters.
- Hold down CTRL and select the other ranges. Still stopping where you want your totals. The CTRL key allows you to select non adjacent ranges.
- Let go of the CTRL key after selecting the other ranges.
- Click AutoSum.
- Now select all the ranges including the Grand Totals, Column F and Row 17 in the video below. Click AutoSum.
Windows 8 Keyboard Shortcuts
Word and Excel keyboard shortcuts and quick guideWord video: How do I create a Table of Contents in Word with two columns?
- Here is one page pdf of my favorite windows 8 keyboard shortcuts
This short video shows you how to use section break next page and section break continuous to make a 2 column table of contents in Microsoft Word
About Chris Menard
Chris Menard is a Microsoft Office Master Instructor. Microsoft in Alpharetta calls on Menard to train some of their largest clients including Coca-Cola and Georgia-Pacific Corp. Menard recently presented to over 220 Administrative Professionals in Atlanta at the IAAP annual meeting.
In 2010, Menard created an Excel 2007 video for the Coca-Cola Company that was distributed to over 150 countries. Chris Menard has a BBA in Accounting and is a frequent speaker for the Georgia Society of CPAs.
Have a technology question for Chris Menard?