Excel 2007 Essentials

Power Point Slides

Corporate Training Materials
 Module One: Did you know
Getting Started that Excel
was first
released in
• Welcome to the Microsoft Excel 2007 1985? Excel
Essentials workshop. 2007 is the
• Excel is the world’s premier spreadsheet 12th version
of the
software. You can use Excel to analyze software.
numbers, keep track of data, and
graphically represent your information.
• With Excel 2007, you can manage more
data than ever, with increased worksheet
and workbook sizes.
• Excel also makes your job easier by
providing an easy to use interface, and an
array of powerful tools.
 Pre-Assignment Review
• The purpose of the Pre-Assignment is to get
you thinking about the Excel features you are
already using, and where you need to
improve.
• You were asked to review a list of topics and
place a letter indicating your interest in the
topic.
 Workshop Objectives (I)
• Open and close Excel
• Differentiate between worksheets, workbooks, rows,
columns, and cells
• Enter labels and values
• Edit data
• Check spelling
• Open, close, and save workbooks (including publishing to
PDF)
• Switch between Excel views
• Use Zoom
• Set up your page
• Preview and print your workbook
 Workshop Objectives (II)
• Build and edit formulas
• Copy formulas
• Use absolute referencing appropriately
• Use basic Excel functions, including SUM, AVERAGE, MAX,
and MIN
• Use Selection Statistics
• Use AutoFill and AutoComplete
• Sort and filter data
• Format text and numbers
• Apply borders
• Use cell styles
• Change the workbook theme
 Module Two: The
Opening and Closing Excel beginning is
the most
important
• In this module, you will learn to part of the
work.
open and close Excel.
• You will also explore the Excel Plato

interface, and be able to

differentiate between cells, rows,
columns, worksheets, and
workbooks.
 Opening Excel (I)
• To begin, click the Start button (or the
Windows logo) in the bottom left-hand corner
of your screen.
• Then click All Programs.
• Next, click the program group Microsoft
Office, and then from the submenu, choose
Microsoft Excel 2007.
Opening Excel (II)
Understanding the Interface (I)
 Understanding the Interface (II)
• To view the Office
menu, click the Office
button.
• To add or remove
commands from the
Quick Launch toolbar,
click Customize Quick
Access Toolbar, and
then select an item to
add or remove.
Understanding Worksheets
Understanding Workbooks
Closing
Excel
 Module Three:
He who
Your First Worksheet fails to
plan, plans
• Generally, you will create worksheets to fail.
with a purpose in mind.
• Spend some time thinking about Proverb
exactly what you want from your
spreadsheet.
• Then plan how you are going to lay
out the data and calculations.
• The structure of your worksheet
depends on what you are trying to
do, but most worksheets consist of
data organized into rows or columns.
 Entering Data (I)
• Data is entered into cells.
• Click the cell you want, and type the desired entry. You
can enter either numbers (values) or text (labels).
• Values automatically right-align within the cell, and
labels left-align. (Of course, you can change the
alignment if you want.)
• As you type your data, it appears in two places: in the
cell itself, and in the Formula Bar.
• Once you have finished typing, press Enter to complete
the entry. The active cell will move down one row,
ready for your next entry.
Entering Data (II)
Entering Data (III)
Using the Wrap Command
Editing Data
Adding Rows and Columns
 Checking Your Spelling
• To check your spelling, display the Review tab,
and click the Spelling button (in the Proofing
group).
 Module Four: Quick Tip:
Working with Excel Files Once you
have saved
• You should save your workbook as your file,
you can
soon as you start it, in order to quickly
update it by
protect your work. pressing
Ctrl + S –
• Excel 2007 introduces a new file making
format, and gives you more options sure you’ll
never lose
for saving than ever before. your hard
work!
 About the New File Format
• After many years of the same file format,
Office 2007 introduces new file formats.
• Instead of the .xls extension, the new file
format is an XML based format, with the
extension .xlsx.
• This allows Excel to save the new file size, use
other new features such as themes, and
improves the interoperability of your Excel
data with other applications.
Saving
Files
(I)
Saving Files (II)
 Publishing Files to PDF (I)
• You can access
the add-in by
clicking the
Office Button –
Save-As – Find
add-ins for other
file formats, and
following the
instructions.
 Publishing Files to PDF (II)
• Once you have installed
the add-in, click Office
Button – Save As – PDF or
XPS.
• Follow the same steps for
naming and storing the
file as you would for
saving in the default
format.
• Click Publish to save the
file.
 Closing Files
• To close a file, click the Office button and
choose Close.
• If you have made changes to the file, you will
be prompted to save your work.
Opening Files (I)
Opening Files (II)
 Module Five: Quick Tip:
You can
Viewing Excel Data change the
view and
zoom control
• Once you have data entered in on the Status
Bar at the
your workbook, you may find it bottom of the
Excel
helpful to change the way you view window.
each worksheet.
• You can make sure that it is going
to print nicely, control page breaks,
and change how much of your
worksheet appears on screen.
 An Overview of Excel’s Views (I)
• The View tab controls the on-screen
appearance of your worksheet.
• Changing the worksheet view does not affect
the way your worksheet prints, only the way
you see it on your monitor.
• The default view you have been working in is
called the Normal view.
 An Overview of Excel’s Views (II)

There are other useful views, including:

• PAGE LAYOUT
• PAGE BREAK PREVIEW
• FULL SCREEN
 Switching Views (I)
• To choose the view for your worksheet, click
the View tab.
• Then, click the button for the desired view.
Switching Views (II)
 Using Zoom
• To change the zoom level, use the slider on
Excel’s status bar.
• To increase the zoom level, click the + symbol.
• To decrease the zoom level, click the – symbol.
• You can also drag the slider to the desired
zoom level.
 Switching Between Open Files
• To switch between open files, click the desired
button on the Windows taskbar.
 Module Six: Quick Tip:
The Print
Printing Excel Data dialog is the
same across
all Office
• It’s easy to print worksheets in applications,
and even
Excel. appears in
other non-
• Before you print your worksheet, Microsoft
programs.
you might want to change some of
the page settings.
• Once you are satisfied with your
layout, you can print your
worksheet(s).
An Overview of the
Page Layout Tab
 Setting Up Your Page (I)
• You can use the Page Setup group to control
various aspects of your page.
Setting Up Your Page (II)
Using Print Preview (I)
Using Print Preview (II)
Printing Data (I)
Printing Data (II)
 Module Seven: The essence
Building Formulas of
mathematics
is not to
• There are two ways to set up make simple
calculations in Excel: using formulas things
complicated,
or using functions. but to make
• Formulas are mathematical complicated
things simple.
expressions that you build yourself.
• You need to follow proper math S. Gudder
principles in order to obtain the
expected answer.
• Building the formula is simply a
matter of combining the proper cell
addresses with the correct operators.
 The Math Basics of Excel (I)

OPERATION OPERATOR
Addition +
Subtraction -
Multiplication *
Division /
 The Math Basics of Excel (II)
• Excel formulas should generally use cell
references instead of numbers.
 Building a Formula (I)
• To build a formula in Excel, first determine what
you want to calculate. (We are going to write a
formula to calculate the total value of each stock
item. )
• Start by clicking in the cell that you want to
contain the answer, in this case D4.
• Press = to begin your formula.
• Click the first cell in the formula (B4) and then
type the first operator (*).
• Click C4, and press Enter to complete the
formula.
Building a Formula (II)
Editing a Formula
Copying a Formula (I)
Copying a Formula (II)
 Copying a Formula (III)
• Both formulas in the Inventory worksheet can
be copied this way.
Relative vs. Absolute Referencing
 Module Eight:
Excel 2007
Using Excel Functions offers 343
functions,
• So far, we have performed simple with 51
new
calculations in Excel, combining cell functions.
references and operators.
• Excel functions take calculations
one step further, making it easier
to do complex or tedious
calculations.
 Formulas vs. Functions
• Instead of combining cell references and
operators, functions use function names and
arguments. The syntax is always the same:

=NAME(Arguments)
 Understanding the Formulas Tab
• To see the functions available in any group,
click the arrow beside the name of the group.
 Using the SUM Function (I)
• Access the SUM function via the AutoSum
button on the Formulas or Home tab.
 Using the SUM Function (II)
• Click in the cell next to the numbers you want
to add up.
• Click the Sum button.
Using the SUM Function (III)
Using Other Basic Excel Functions
• In addition to the SUM function, there are
several other commonly used functions.
• You can access these by clicking the arrow
next to the Sum button.
Using Other Basic Excel Functions (II)
Using the Status Bar to Perform
Calculations (I)
 Using the
Status Bar to
Perform
Calculations
(II)
 Module Nine: Time is
Using Time Saving Tools money.

• You can use Excel to fill in days of Benjamin

the week or months of the year, or Franklin
use Excel’s AutoComplete feature
to quickly write functions.
• To get more out of your data, you
can sort and filter it.
• We will look at all of these tools in
this module.
Using AutoFill (I)
Using AutoFill (II)
 Using AutoComplete (I)
• Start by clicking in the cell where you want the
answer, and then type = and the first letter of
the desired function.
Using AutoComplete (II)
Sorting Data
Filtering Data (I)
Filtering Data (II)
Filtering Data (III)
 Module Ten: Quick Tip:
Use the Undo
Formatting your Data arrow on the
Quick Access
toolbar to
• In this module, we will look at how reverse
unwanted
to make your worksheet more changes.
appealing by changing the font
type and size, alignment,
formatting numbers, and by adding
color and borders.
Changing the Appearance
Of Your Text (I)
Changing the Appearance
Of Your Text (II)
Changing the Appearance
Of Your Text (III)
Changing the Appearance of
Numbers (I)
Changing the Appearance of
Numbers (II)
 Setting Alignment Options (I)
• You can align the contents horizontally and
vertically, or change the orientation of the cell
contents.
Setting Alignment Options (II)
 Using Merge
• Select the cells you want to merge.
• Click the Merge button on the Home tab.
 Removing Formatting
• To remove the formatting of a cell and return
it to the default formatting, select the cell(s)
and click the Clear Formats button.
 Module Eleven: Quick Tip: You
can access all
More Formatting available
formatting
commands in
the Format
• There are other ways to enhance dialog boxes.
your worksheet. Click the
• You can add borders and shading, Dialog Box
Launcher (the
small button to
apply styles and conditional the right of a
group name on
formatting to cells, and change the a tab) to
access these
theme of the workbook. full featured
dialog boxes.
Adding Borders
Adding Fill Color
Using Cell Styles
Using Conditional Formatting (I)
Using Conditional Formatting (II)
• In the dialog, set the criteria for the
formatting, choose the type of formatting you
want to apply, and click OK.
 Changing the Theme (I)
• A theme is a collection of formats that can be
applied to a worksheet.
• It includes settings for colors, fonts, and effects
(such as shadows and colors for graphics).
• Themes are an Office 2007 feature, and only
work on files saved in the new .xlsx format.
• If you change the theme for a worksheet after
you have formatted cells, the theme may not
override all of your customizations.
Changing the Theme (II)
 Module Twelve: Anyone who
stops
Wrapping Up learning is
old, whether
at twenty or
• Although this workshop is coming to eighty.
a close, we hope that your journey to
improve your Excel skills is just Henry Ford
beginning.
• Please take a moment to review and
update your action plan.
• This will be a key tool to guide your
progress in the days, weeks, months,
and years to come.
• We wish you the best of luck on the
rest of your travels!
 Words from the Wise
• Remember to spend some time planning your worksheet.
Be clear about why you are creating it.
• Remember that everything can be changed if needed.
• Save often, and back up your work regularly.
• Try to use cell references instead of numbers in your
formulas and functions.
• Try to write a formula or function once, and then copy it,
instead of repeatedly writing it.
• Practice as much as you can, and as soon as you can.
• Remember the Undo button!
• If you find you are getting frustrated, come back to this
manual, and try the guided exercises to refresh your skills.