Transcript Word and Excel Essentials

Goals
• Assume you know Word and Excel basics
• I hope to teach you some time saving tools and extend
your knowledge
• If you have things you want to know how to do, please tell
me.
Joe Perret - Pierce College
2
OCT 2014
Keyboard Shortcuts
CTRL + C = Copy
CTRL + V = Paste
CTRL + X = Cut
CTRL + Z = Undo
CTRL + B = Bold
CRTL + A = Select All
SHIFT + Delete = Deletes a file (forever)
Joe Perret - Pierce College
3
OCT 2014
Word Skills
• Header – Footer
• Save as old Word file
• Show/Hide and Paragraph
• Line Breaks, Page Breaks, Section Breaks
• Page Setup
• Footnotes
• Bibliography
• PDF
• Comment
Joe Perret - Pierce College
4
OCT 2014
Header and Footer
• Header/Footers appears on all pages
of your document
• You can set a different header/footer
for first page or Odd and even pages
Use them to
create your own
letterhead
Joe Perret - Pierce College
5
OCT 2014
Use the Header to create a letterhead
First Page Header
Second Page Header
Add Page Number
Joe Perret - Pierce College
6
OCT 2014
Use the Header to create a letterhead
After all that work…
Save it as a
template, so you can
use it over and over
again
Joe Perret - Pierce College
7
OCT 2014
Save Word File in old format
• Problem - If you send a Word 2007 or newer file to someone with the older
version of Word (2003 or 97) they will not be able to open it without an add-on
converter.
• Solution – Use Save as to save your file in the older format
• Solution – Send them the link to the converter
Joe Perret - Pierce College
8
OCT 2014
Save Word File in old format
• Solution – Use Save as to save your file in the
older format (*.doc)
• Trick is to click on Save As Type
• Solution – Send them the converter link (free)
http://www.microsoft.com/en-us/download/details.aspx?id=3
Joe Perret - Pierce College
9
OCT 2014
Show/Hide
•
Show/Hide
•
•
•
•
Tells you a lot about your document that can save you time.
Look on the Home Ribbon for this icon
Characters that appear – do NOT print
Easy to find extra spaces and extra paragraphs.
Joe Perret - Pierce College
10
OCT 2014
Paragraph Dialog Box
• A very useful dialog box is the Paragraph
• Look in the Home ribbon, in the Paragraph group for
the small arrow in the corner
• Click on it to open the dialog box
Joe Perret - Pierce College
11
OCT 2014
Line Breaks, Page Breaks, Section Breaks
• Line breaks – use this to create a
Line break = Shift + enter
new line inside of a paragraph
• Page break – use this to force the
Page break = Ctrl + enter
text to a new page
• Section break – use this to
change the orientation of a page
from portrait to landscape within
a document.
Joe Perret - Pierce College
12
OCT 2014
Page Set Up
• Another very useful dialog box
Joe Perret - Pierce College
13
OCT 2014
Footnotes and Endnotes
• Adding footnotes and endnotes
Joe Perret - Pierce College
14
OCT 2014
Citations and Bibliography
• Oh no not a term paper!
• Cites are now easy
• So is the bibliography
Joe Perret - Pierce College
15
OCT 2014
Convert Word to PDF (and back)
•
•
•
PDF’s have the advantage that any
browser can open them
They are harder to tamper with
Use the Save As  Save as Type to
convert your files to PDF
Joe Perret - Pierce College
16
OCT 2014
Use Comments when Collaborating
• You can use the comment
feature of word when you
are collaborating on a
document.
Joe Perret - Pierce College
17
OCT 2014
Track Changes when you are Collaborating
Joe Perret - Pierce College
18
OCT 2014
Excel
• Database Tools
• Sort
• Filter
• Format
• Conditional Format
• Charts
• Header – Footer
Joe Perret - Pierce College
19
OCT 2014
Let’s open a data file
3
1
2
• In a browser, go to : http://faculty.PierceCollege.edu/PerretJD
Joe Perret - Pierce College
20
OCT 2014
Problem
1. In the Cash Receipts worksheet, freeze the top row so the
headers remain on the screen as you scroll.
2. Make a copy of the Cash Receipts worksheet, and then rename
the copied worksheet as Feb Data. (Hint: To make a copy of the
worksheet, press and hold the Ctrl key as you drag the sheet tab
to the right of the Cash Receipts sheet tab.)
3. In the Feb Data worksheet, unfreeze the top row.
Joe Perret - Pierce College
21
OCT 2014
Problem Continued…
1.Create an Excel table for the cash receipts data.
2.Format the Excel table with Table Style Medium 25, and
then change the Amount field to the Accounting format
with two decimal places.
3.Rename the Excel table as February Data.
Joe Perret - Pierce College
22
OCT 2014
Problem continued….
Make the following changes to the February Data table:
1.Add a record for 2/29/2016, Monday, 10, Spec Drink,
353.11.
2.Edit the record for Coffee on 2/27/2016 by changing
the
Amount from 219.71 to 269.71.
3.Delete any duplicate records.
Joe Perret - Pierce College
23
OCT 2014
Problem Continued
1.Make a copy of the Feb Data worksheet,
2.Rename the copied worksheet as Sort Term Data
3.Sort the cash receipts by Trn Date, displaying the
newest receipts first, and then by Amount displaying the
largest amounts first..
Joe Perret - Pierce College
24
OCT 2014
Problem continued….
1.Make a copy of the Feb Data worksheet,
2.Rename the copied worksheet as Sort by Day.
3.In the worksheet, sort the cash receipts by Day (use the
custom list order of Sunday, Monday, …), then by
Segment (A to Z), and then by Amount (smallest to
largest).
Joe Perret - Pierce College
25
OCT 2014
Problem Continued….
1.Make a copy of the Feb Data worksheet,
2.Rename the copied worksheet as Filter Omit Gifts.
3.Filter the FebruaryData table to display the cash
receipts for all items except Gifts
Joe Perret - Pierce College
26
OCT 2014
Problem cont……
• In the Filter Omit Gifts worksheet, insert the Total row to calculate the average
amount of the cash receipts for the Filtered data. Change the label in the Total row to
Average. Sort the filtered data by descending order by Amount.
• Make a copy of the Feb Data worksheet, and then rename the copied worksheet as
Subtotals. In the Subtotals worksheet, convert the FebruaryData table to a range, and
then sort the range by the Segment column.
• In the Subtotals. worksheet, use the Subtotal command to calculate the total cash
receipts for each segment in the Amount column. Display only the subtotal results.
Joe Perret - Pierce College
27
OCT 2014
Cloud Storage
• Save your files on the cloud
• Share files
Joe Perret - Pierce College
28
OCT 2014
What is Google Docs
• Google Docs is a free Web-based application
• Documents (Word) and spreadsheets (Excel)
• Files can be accessed from any computer with browser
• You can
• Google Docs lends itself to collaborative projects
.
•
•
•
All participants can see who made specific document changes
Documents are stored -no risk of total data loss
•
•
•
•
Import
Create
Edit
Update
Some concern that work may not be private or secure.
Joe Perret - Pierce College
29
OCT 2014
Google Drive Video
Joe Perret - Pierce College
30
OCT 2014
Word – Excel- PowerPoint type files
• Google Docs - https://docs.google.com/document/
•
Google Docs is an online word processor that lets you create and format text documents
and collaborate with other people in real time. Here's what you can do with Google Docs:
• Google Sheets - https://docs.google.com/spreadsheets
•
Google Sheets is an online spreadsheet app that lets you create and format spreadsheets
and simultaneously work with other people. Here's what you can do with Google Sheet
• Google Slides - https://docs.google.com/presentation
•
Google Slides is an online presentations app that allows you to show off your work in a
visual way.
Joe Perret - Pierce College
31
OCT 2014
Getting Started
• Create a Google (Gmail) account –
• To create Google Account, go to the https://accounts.google.com/signup
page.
You can also simply click the Create an account link beneath the sign in box in the
middle of any Google sign-in page.
• Login to http://Docs.Google.com
• Click on the + in the upper right
• Drag and Drop a document here
Joe Perret - Pierce College
32
OCT 2014
Uploading and Syncing Files
Joe Perret - Pierce College
33
OCT 2014
Sharing Files
How to Share
• Add their email to share
• Click Share
• Add email address
• Send them a link
• Send file as email attachment
Joe Perret - Pierce College
What they can do
• Can edit: Users can edit the file or
folder and share it with others
• Can comment:
Users can view and add
comments to the file or folder, but can’t
edit it
• Can view:
Users can see the file or
folder but can’t edit or comment on it
34
OCT 2014