Exploring
Microsoft Office 2016
Series Editor Mary Anne Poatsy
Mulbery|Krebs|Hogan|Cameron |Davidson|Lau|Lawson|Williams
Series Created by Dr. Robert T. Grauer
Exploring Excel 2016
Tutorial 1
Introduction to Excel
Creating and Formatting a Worksheet
Copyright 2017 Pearson Education, Inc.
Objectives
Explore the Excel Window
Enter and Edit Cell Data
Create Formulas
Display Cell Formulas
Manage Columns and Rows
Select, Move, Copy, and Paste Data
Copyright 2017 Pearson Education, Inc.
Objectives
Apply Cell Styles, Alignment, and Font Options
Apply Number Formats
Manage Worksheets
Select Page Setup Options
Preview and Print a Worksheet
Copyright 2017 Pearson Education, Inc.
Objective 1:
Explore the Excel Window
Skills: Identify Excel Window Elements Identify Columns, Rows, and Cells
Navigate In and Among Worksheets
Copyright 2017 Pearson Education, Inc.
Explore the Excel Window
Spreadsheetelectronic file that:
Contains a grid of columns and rows
Displays results of calculations
Enables interpretation of quantitative data
Copyright 2017 Pearson Education, Inc.
Explore the Excel Window
Worksheetsingle spreadsheet that contains:
Descriptive labels
Numeric values
Formulas, functions
Graphical representations of data
Copyright 2017 Pearson Education, Inc.
Explore the Excel Window
Workbookcollection of one or more related worksheets
Cellintersection of a column and a row
Identification:
Columns use lettersA
Rows use numbers10
Cells use a combination of a letter and a numberA10
Copyright 2017 Pearson Education, Inc.
Explore the Excel Window
Cell A1
Workbook
Book1
Row 11 Column S
Single
worksheet
Copyright 2017 Pearson Education, Inc.
Objective 2:
Enter and Edit Cell Data
Skills: Enter Text Use Auto Fill to Complete a Sequence Enter Values
Enter a Date Clear Cell Contents
Copyright 2017 Pearson Education, Inc.
Enter and Edit Cell Data
Data types:
Textcombination of letters, numbers, symbols, and spaces
Valuesnumbers used as the basis of calculations
Dates and timesentered in a variety of formats
Auto Fillfeature that completes a sequence of words or
values
Copyright 2017 Pearson Education, Inc.
Enter and Edit Cell Data
Clear
options
Copyright 2017 Pearson Education, Inc.
Class Activity 1
1. Open the HouseSold worksheet of Tutorial1 Data. xlxs
2. Assign Property ID (Prop ID) to Cell A5 to A12. Example:
Assign 2018-001 to A5 cell, 2018-002 to A6 cell, 2018-
003 to A7 cell, 2018-004 to A8 cell etc.
Copyright 2017 Pearson Education, Inc.
Objective 3: Create Formulas
Skills: Use Cell References in Formulas Apply the Order of Operations
Use Semi-Selection to Create a Formula Copy Formulas
Copyright 2017 Pearson Education, Inc.
Create Formulas
Formulaused to perform calculation, such as =A1 + 3
Order of operationsrules in which arithmetic operations
are performed
Some unfamiliar operators:
* for multiplication
^ caret for exponentiation
Semi-selectionselects cell or range of cells as formula is
built
Copyright 2017 Pearson Education, Inc.
Create Formulas
Copyright 2017 Pearson Education, Inc.
Class Activity 2
Enter the following formulas into the worksheet:
1. Days on Market = Dates Sold- Dates listed.
2. % of List Price = Sold Price/ List Price.
Copyright 2017 Pearson Education, Inc.
Objective 4:
Display Cell Formulas
Skills: Display Cell Formulas
Copyright 2017 Pearson Education, Inc.
Display Cell Formulas
Cell
formula
Cell value
Cell
formula
Copyright 2017 Pearson Education, Inc.
Class Activity 3
Press Ctrl + to see all the formulas of the
HouseSold worksheet.
Copyright 2017 Pearson Education, Inc.
Objective 5:
Manage Columns and Rows
Skills: Insert Cells, Columns, and Rows Delete Cells, Columns, and Rows
Hide a Column or Row Adjust Column Width Adjust Row Height
Copyright 2017 Pearson Education, Inc.
Manage Columns and Rows
Copyright 2017 Pearson Education, Inc.
Manage Columns and Rows
Copyright 2017 Pearson Education, Inc.
Manage Columns and Rows
Hide &
Unhide
Copyright 2017 Pearson Education, Inc.
Manage Columns and Rows
Adjust
column
width
Copyright 2017 Pearson Education, Inc.
Manage Columns and Rows
Column
Width
Copyright 2017 Pearson Education, Inc.
Class Activity 4
1. Merge and Centered the cells that
contain the title and subtitles.
2. Adjust the column width of all cells to
22.
Copyright 2017 Pearson Education, Inc.
Objective 6:
Select, Move, Copy, and Paste Data
Skills: Select a Range Move a Range Copy and Paste a Range Use Paste
Options and Paste Special
Copyright 2017 Pearson Education, Inc.
Select, Move, Copy, and Paste Data
Copyright 2017 Pearson Education, Inc.
Select, Move, Copy, and Paste Data
To move, copy, or paste data:
1. Select range
2. Select Cut or Copy
3. Move to new location
4. Select Paste and the paste option
Copyright 2017 Pearson Education, Inc.
Select, Move, Copy, and Paste Data
Cut Copy
Paste
Paste
Special
Copyright 2017 Pearson Education, Inc.
Class Activity 5
1. Copy all cells from A1 to H12.
2. Paste all cells in a new worksheet
called HouseSold New.
3. Paste the cells using Paste Special
option.
Copyright 2017 Pearson Education, Inc.
Objective 7:
Apply Cell Styles, Alignment, and Font Options
Skills: Apply a Cell Style Merge and Center Data Change Cell Alignment
Wrap Text Increase Indent Apply a Border Apply Fill Color
Copyright 2017 Pearson Education, Inc.
Apply Cell Styles, Alignment, and Font Options
Cell stylecollection of format settings that provide a
consistent appearance
Cell styles control:
Font
Font color and size
Borders and fill colors
Alignment
Number formatting
Copyright 2017 Pearson Education, Inc.
Apply Cell Styles, Alignment, and Font Options
Copyright 2017 Pearson Education, Inc.
Apply Cell Styles, Alignment, and Font Options
Alignmenthow data are positioned within boundaries of
cell
Data type default alignments:
Textaligns at left cell margin
Dates and valuesalign at right cell margin
Horizontal alignmentposition of data between left and
right cell margins
Vertical alignmentposition of data between top and
bottom cell margins
Copyright 2017 Pearson Education, Inc.
Apply Cell Styles, Alignment, and Font Options
Merge and centermerges selected cells together into one
cell
Wrap textdata appears on multiple lines in cell
Indentoffset of data from current alignment
Borderline that surrounds cell or range of cells
Fill colorbackground color that displays behind data
Copyright 2017 Pearson Education, Inc.
Apply Cell Styles, Alignment, and Font Options
Merged
and Wrapped
centered text
Filled color
Border
Indented
text
Copyright 2017 Pearson Education, Inc.
Class Activity 6
Highlight A4 to H4 cell, fill with colour
Blue Accent 1 Lighter 40%.
Add border to C5-D12 cell.
Ident all text by 3.
Wrap text from B5 to B12.
Copyright 2017 Pearson Education, Inc.
Objective 8:
Apply Number Formats
Skills: Apply Number Formats Increase and Decrease Decimal Places
Copyright 2017 Pearson Education, Inc.
Apply Number Formats
Copyright 2017 Pearson Education, Inc.
Class Activity 7
Change the number format of F5-G12 to RM
Copyright 2017 Pearson Education, Inc.
Objective 9:
Manage Worksheets
Skills: Insert a Worksheet Delete a Worksheet Copy or Move a
Worksheet Rename a Worksheet Group Worksheets
Copyright 2017 Pearson Education, Inc.
Manage Worksheets
Delete
selected
sheet
Rename
selected
sheet
Move or
copy
selected
sheet
Selected
worksheet New sheet
tab Copyright 2017 Pearson Education, Inc.
Manage Worksheets
New
location
Move
within
workbook
Copy
Copyright 2017 Pearson Education, Inc.
Class Activity 8
Move or copy House Sold worksheet before House Sold New
worksheet. Name it as House Sold Latest.
Copyright 2017 Pearson Education, Inc.
Objective 10:
Select Page Setup Options
Skills: Set Page Orientation Select Scaling Options Set Margin Options
Create a Header or Footer Select Sheet Options
Copyright 2017 Pearson Education, Inc.
Select Page Setup Options
Page Setup Scale to Fit
group group
Copyright 2017 Pearson Education, Inc.
Select Page Setup Options
Copyright 2017 Pearson Education, Inc.
Select Page Setup Options
Copyright 2017 Pearson Education, Inc.
Class Activity 9
1. Set Page Margin as Top and Bottom 1.9 cm, Left and Right:
1.8cm.
2. Set Orientation as landscape.
3. Set Size as A4.
4. Insert Page Break.
Copyright 2017 Pearson Education, Inc.
Objective 11:
Preview and Print a Worksheet
Skills: View in Print Preview Set Print Options Print a Worksheet
Copyright 2017 Pearson Education, Inc.
Preview and Print a Worksheet
Print
preview
Number of
pages
Copyright 2017 Pearson Education, Inc.
Class Activity 10
Change the print setting to Print Entire
Workbook.
Copyright 2017 Pearson Education, Inc.
Summary
A workbook can consist of multiple worksheets, divided into
columns, rows, and cells
Cells contain text, values, or formulas, with contents entered
by typing, copying and pasting, and using Auto Fill
Columns, rows, and cells can be added or deleted, copied, or
moved
Styles, alignment, and formatting can be applied to cells
Formatting can be applied to numerical values
Copyright 2017 Pearson Education, Inc.
Summary
Worksheets can be inserted, deleted, renamed, copied, and
moved
A worksheet can have orientation and margins changed,
headers and footers added, and scaling adjusted
Before printing, a worksheet can be previewed and final
printing options selected
Copyright 2017 Pearson Education, Inc.
Questions
Copyright 2017 Pearson Education, Inc.
Copyright
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or
transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or
otherwise, without the prior written permission of the publisher. Printed in the United States of
America.
Copyright 2017 Pearson Education, Inc.
