IG006S17A

From CourseWiki

(Redirected from IG006)
Jump to: navigation, search


Microsoft Office® Advanced Tips & Tricks for Word, Excel®, and Outlook®

Overview

Introduction

Microsoft Office® Advanced Tips and Tricks for Word, Excel® and Outlook®

This is a day to satisfy that nagging feeling; ''There must be a better way''.

Course Description

There are some great features shared by all three of these programs that you may have never found. Learn them once and use them in several ways.
Then there are features that are unique to the individual programs. We will explore these too.
When you fire up a new version of a program the first priority is to be able to do what you have always done. That is, to get back to work. Once you've done that, there's rarely an opportunity to find, learn, and use the new features in the new version. We will explore hidden or difficult to find features throughout Word, Excel, and Outlook so that you can be more productive as you save time and effort.
See the Objectives tab above for details

Duration: 6 hours
Instructor: Jim Hope

Download pdf version

Register for this course: Microsoft Office® Advanced Tips & Tricks for Word® Excel® and Outlook® Introduction
and
Excel Troubleshooting and Designing Workbooks the next course in this series.

Objectives

  • Recognize and use features common to all three programs
  • Ribbon (fluent user interface)
  • Backstage View
  • Quick Access Toolbar
  • Start Screens
  • Pinned Locations (a feature in Windows you can use everywhere)
  • Preparing Documents before sharing them
  • Encrypting Documents
  • Document Properties
  • Templates and Styles
  • Reusing blocks of Text
  • Status bar
  • Keyboard and Mouse Tricks
  • Keyboard shortcuts
  • Macros Introduction

Outlook

  • Navigation Pane
  • To Do Bar
  • Permanent Delete
  • Show All / Unread
  • View and Message Preview
  • Close original on reply or forward
  • Paste into a New Item
  • Categories
  • Rules
  • Templates and Styles
  • Search Folders
  • Quick Steps

Excel & Word

  • Create Templates and Style, use Themes
  • Prepare documents for before sharing
  • Encrypt documents
  • Create Outlines
  • Create Simple Macros

Excel

  • Use Names to document your formulas
  • Use Auditing Tools to understand your worksheets and solve problems
  • Backstage View
  • Quick Access Toolbar
  • Create Data Tables to sort and filter your data
  • Pinned Locations (a feature in Windows you can use everywhere)
  • Create simple PivotTables
  • Use the Status Bar to double check your work

Word

  • Tables of Contents
  • Use the Navigation Bar to traverse and reorganize the document
  • Use Bookmarks to link within a document
  • Create and work with Breaks and Sections

Prerequisites

Prior experience with Microsoft Office and specifically Word, Excel, and Outlook.

Downloads

Computer Equipment

The sessions are conducted in a computer lab, and you will have a computer provided for your use. You are welcome to bring your own computer to the lab sessions if you prefer to use that. Wireless access is available in the lab.

Please note that at times you will be doing a fair bit of typing and you will want to have a large enough screen and separate physical keyboard so that you can view content and type at the same time.

If it appears that you are having challenges with your own device, we will ask you to use the computer provided for you in the lab.

Storage

Please plan to use your own cloud based storage SkyDrive, DropBox, iCloud (or similar) or a USB storage key.

Instructional Methods

  • Hands-on labs
  • Discussion
  • Lecture

Course Materials

  • Web site
  • Course notes
  • Printed copies of on-screen lecture notes available on request during lectures

No Recording or Pictures in the Lab

Please note: Taking pictures or video during the course sessions is not permitted. All the course materials and lecture notes are available in physical form and on the web.

Coming Soon
Courses coming

Follow JimUBC on Twitter for latest course updates
Follow JimUBC on Twitter for latest course updates

Contents

This web page is a resource for participants in this course, a lab oriented workshop. This is not intended as a standalone tutorial. There is no narrative here.

To be a part of the discussion that accompanies these materials, please come to the course.

Register for this course



Register for this course: Microsoft Office® Advanced Tips & Tricks for Word® Excel® and Outlook® Introduction | Register for this course: Excel® Troubleshooting and Designing Workbooks follow up to this course.

  • Current Session IG006S17A
  • One Session: Tuesday June 17, 2017
  • 9:00am - 04:00pm
  • Lab Lab C460
  • CA: Mandy Lui
  • Students Registered: 11 *

Course Evaluation Form

Downloads



Resources

Office


Excel


Older

Outlook

Word



Office 2013 User Interfaces

Excel

See more

Ribbon

Image:Excel 2013 User Interrface 01.png


Image:Excel 2013 Home Tab.png


Image:Excel 2013 Insert Tab.png


Image:Excel 2013 Page Layout Tab.png


Image:Excel 2013 Formulas Tab.png


Image:Excel 2013 Data Tab.png


Image:Excel 2013 Review Tab.png


Image:Excel 2013 View Tab.png


Image:Excel 2013 Developer Tab.png


Image:Excel 2013 Load Test Tab.png


Image:Excel 2013 Team Tab.png


Backstage View

Image:Excel 2013 Backstage View.png

Options

Image:Excel 2013 Options.png


Quick Access Toolbar

You can add a command from any Ribbon by right clicking the command and choosing Add to Quick Access Toolbar

Image:Excel 2013 Customize Quick Access Toolbar 01.png


Customize the Quick Access Toolbar by adding a command that is not available on any of the Ribbons.

Image:Excel 2013 Customize Quick Access Toolbar 02.png

Contextual Tabs

Image:Excel 2013 Contextual Tabs CHART TOOLS.png


Gallery

Image:Excel 2013 Gallery of Styles.png


Excel 2010 Ribbon Image:Excel 2010 Ribbon.png

Ribbon

Word

See more

Ribbon Overview

Image:Word2010Ribbon.png

Home Tab

Image:Word2010HomeTab.png

Insert Tab

Image:Word2010InsertTab.png


Page Layout

Image:Word2010PageLayoutTab.png

References

Image:Word2010ReferencesTab.png

Mailings

Image:Word2010MailingsTab.png

Review

Image:Word2010ReviewTab.png

View Tab

Image:Word2010ViewTab.png



Word 2013 User Interface See IG004

Outlook

See more

Home

Image:Outlook2013Ribbon01.png

Tabs, Groups, Commands

The Ribbon is broken down into Tabs, Groups, and Commands.

Image:Outlook2013Ribbon01a.png

More

At the bottom of a Group you may see a symbol (the Dialog Box Launcher) to indicate that there is more detail available.

Image:Outlook2013Ribbon01b.png

Outlook Ribbons

Each function in Outlook has its own ribbon. These are for the Inbox, Reading a Message, Creating a Message, Appointment, Contact, Task. They also have their own Quick Access Toolbar.

Outlook Ribbons

Send / Receive

Image:Outlook2013Ribbon02.png

Folder

Image:Outlook2013Ribbon03.png

View

Image:Outlook2013Ribbon04.png

Quick Access Toolbar

Image:Outlook2013Ribbon06.png

File (Backstage View)

Image:Outlook2013Ribbon07.png


Outlook 2013 Options

Outlook Standard View

Image:Outlook 2013 Default View.png


See IG007

Image:Outlook2013Ribbon01.png

More detailed information: Outlook 2013 Ribbon

User Interface

Backstage View

Access this from the File Tab

Image:Excel 2010 Backstage View.png

Options

Image:Excel 2010 Options.png


Clipboard

Image:Excel 2010 Clipboard.png

Formatting

Image:Excel 2010 Formatting.png

Functions

Image:Excel 2010 Functions.png

Image:Excel 2010 Functions2.png

Quick Access Toolbar

Add AutoFormat to the Quick Access Toolbar

Image:AutoFormat.png

Galleries

Styles

Image:Excel 2007 Galleries Styles.png




New Document

  1. Click the Office Button
  2. New
  3. Featured - this will give you access to new featured items online at Microsoft.

Special Requests

IG006S17A Notes


Excel Quick Hits

  • Open a Copy: Right click a recent Document and this will open a copy.
  • Surveys in OneDrive
  • Chart Live Preview (Mouseover chart types)
  • Recommended Charts
  • Recommended PivotTables


Word Quick Hits

  • Open a Copy: Right click a recent Document and this will open a copy.
  • PDF Reflow (you can edit PDFs with Word)
  • Outlines
  • Navigation Pane (reorganize your document by dragging)
  • Word Macro to List All Keyboard Shortcuts (Add Developer Tab to Ribbon first)
    Image:Word Macro ListCommands.png

Outlook Quick Hits

  • Show All / Unread
  • Ignore Conversation
    • ctrl-Delete while viewing a message and all future emails with this Subject line will go straight to the Delete Folder
  • Message Preview (multiple lines)
  • Categories
  • Rules

Macro Examples

Excel

Developer Ribbon

Turning on the Developer Tab in the Ribbon


Created by using the Macro recorder.

This splits names like Cooper, Sheldon Lee into three columns, rearranges the column order and converts the data to a table.

Sub FixNames()
'
' FixNames Macro
' Split First and Last names from FullNames, then rearrange the columns and convert to a table
'

'
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "LastName"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "FirstName"
    Columns("B:B").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$153"), , xlYes).Name = _
        "Table1"
    Range("Table1[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Middle"
    Range("Table1[[#Headers],[FirstName]]").Select
End Sub

Word

Developer Ribbon

Turning on the Developer Tab in the Ribbon


This macro was recorded using the Macro Recorder. Use this to add ® to Microsoft product names.

Sub RegisteredTrademarks()
'
' RegisteredTrademarks Macro
' add Registered mark to Microsoft Product names
'
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "Microsoft Office"
        .Replacement.Text = "Microsoft Office®"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    With Selection.Find
        .Text = "Excel"
        .Replacement.Text = "Excel®"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    With Selection.Find
        .Text = "Outlook"
        .Replacement.Text = "Outlook®"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "Word"
        .Replacement.Text = "Word®"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    Call RegSuper
End Sub
Sub RegSuper()
'
' RegSuper Macro
'
'
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find.Replacement.Font
        .Bold = False
        .Italic = False
        .Superscript = True
        .Subscript = False
    End With
    With Selection.Find
        .Text = "®"
        .Replacement.Text = "®"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub

Outlook

Developer Ribbon

Turning on the Developer Tab in the Ribbon

Note - there is no Macro Recorder in Outlook. You have to write your own code.
Public Sub saveAttachtoDisk()
	Dim itm As Outlook.MailItem
	Dim currentExplorer As Explorer
	Dim Selection As Selection
 
	Dim objAtt As Outlook.Attachment
	Dim saveFolder As String
	Dim fso As Object
	Dim oldName
 
	Dim file As String
	Dim DateFormat As String
	Dim newName As String
 
	Dim enviro As String
	enviro = CStr(Environ("USERPROFILE"))
	saveFolder = enviro & "\Documents\Attachments\"
 
	Set currentExplorer = Application.ActiveExplorer
	Set Selection = currentExplorer.Selection
 
	Set fso = CreateObject("Scripting.FileSystemObject")
	On Error Resume Next
	For Each itm In Selection
 
		For Each objAtt In itm.Attachments
 
			file = saveFolder & objAtt.DisplayName
			objAtt.SaveAsFile file
 
'Get the file name
			Set oldName = fso.GetFile(file)
			x = 1
			Saved = False
 
			DateFormat = Format(oldName.DateLastModified, "yyyy-mm-dd ")
			newName = DateFormat & objAtt.DisplayName
 
'See if file name  exists
			If FileExist(saveFolder & newName) = False Then
				oldName.Name = newName
				GoTo NextAttach
			End If
 
'Need a new filename
			Count = InStrRev(newName, ".")
			FnName = Left(newName, Count - 1)
			fileext = Right(newName, Len(newName) - Count + 1)
			Do While Saved = False
				If FileExist(saveFolder & FnName & x & fileext) = False Then
					oldName.Name = FnName & x & fileext
					Saved = True
				Else
					x = x + 1
				End If
			Loop
 
			NextAttach:
			Set objAtt = Nothing
 
		Next
 
	Next
 
	Set fso = Nothing
 
' message box
	Dim msg As String
	msg = "Attachments saved to " & saveFolder
	Dim title As String
	title = "Message from Macro"
 
	MsgBox msg, vbOKOnly, title
 
End Sub
 
Function FileExist(FilePath As String) As Boolean
 
	Dim TestStr As String
	Debug.Print FilePath
	On Error Resume Next
	TestStr = Dir(FilePath)
	On Error GoTo 0
'Determine if File exists
	If TestStr = "" Then
		FileExist = False
	Else
		FileExist = True
	End If
 
End Function




Personal tools