Crash Course: Microsoft Excel

 Presenter notes:
 This Microsoft Excel presentation is a prepackaged solution
for basic Excel training.
 You may use the presentation as-is or customize it.
 Complete TechRepublic’s feedback form for a chance to win
a free TechRepublic title.
 Remove this slide before presenting the slideshow.
Tips for maximizing Microsoft Excel 2010
Say hello to the ribbon
• Revolutionary way to work.
• Simple click to access.
• Tabs on the ribbon replace menu structuring.
File format changes abound
 The file format used by Microsoft Excel 2010 is an
XML-based format.
 The new format provides less rigid standards for its
files, making documents more accessible and easier
to handle.
Creating formulas
 Excel formulas are powerful and versatile.
 Excel uses a specific order of operation.
 Use parentheses to override the order of
operation (i.e., =2+3*2 results in 8 while
=(2+3)*2 results in 10).
Using functions
 Use functions to calculate financial and
statistical expressions.
 Access functions by clicking the Formula tab
and selecting Insert Function in the Function
Library group.
Cell operations
 Refer to contents of cells instead of numbers.
 Make for easy manipulation and provide capability
for what if scenarios.
 Cells are named by a unique letter and number
 Formulas use this combination to evaluate the
contents of a cell.
 Key Performance Indicator reporting is available to
show graphical representation without a chart or
Absolute v. relative
 A relative reference changes when a formula is
copied, but not when a formula is moved.
 Use a dollar sign ($) to mark a cell reference as
 Highlight any cell reference in the formula bar
and press F4 to cycle through the
relative/absolute combos.
Formatting a cell and its data
 Excel offers numerous formatting options.
 Excel formatting includes boldface, italics,
underline, shading, lines, currency signs,
percentages, and more.
 Cell and data formatting options are on the
Home tab in the Font, Alignment, and
Number groups.
Formatting a page
Change page margins to fit more content
on a page.
Add a header or footer with the date and
time or any other static information.
See additional items in context.
Cell operations - KPI
Conditional formatting of
cells allows for better
representation of data.
Using filters
 Filters enable displaying specific data subsets
from a long table.
 Use multiple or custom filters to quickly drill down
to the exact data you require.
 Use filters to
compare data.
AutoFilter options
 AutoFilter includes several options:
 Sort Ascending / Sort Descending
 Color (where available)
 Custom
Sorting data
 Use Excel’s Sort feature and combine multiple Sort
levels to sort data exactly as you require.
 Advanced options further customize data sorting.
 Sorting features
are on the Data
tab in the
Sort & Filter
About subtotals and data levels
 Subtotals provide at-a-glance subset totals.
 Create subtotals by sorting tables according to
your needs.
 Display subtotals by clicking the Data tab and
then clicking Subtotals in the Outline group.
Using subtotals and data levels
 Related data
levels let you quickly
just a grand total,
subtotals, or all the
Enable quick reviews of what if scenarios.
Manipulate data without accidentally
deleting it or changing it.
Display data in a meaningful way.
Creating PivotTables
 Create PivotTables using
the PivotTable and
PivotChart Report
Creating charts
Excel supports
numerous chart types.
Use different chart
types to portray
different kinds of
Create charts from
pivot tables.
Create charts using the
Chart Wizard.
Using macros
 Simplify routine tasks.
 Automate common
 Easily record macros and
play them back to repeat
the action as needed.
Exporting Excel data/charts
 Excel data is easily exported to other Microsoft
Office applications.
 You can copy and paste information from
one Office application to another.
Exporting Excel data/charts
 Link data between
Office applications.
 Linking data enables
changes made
within one
application to be
reflected in another.
Retrieving database data
 Combine Access’ database power with Excel’s
 Databases and spreadsheets have many
important differences.
 Import Access data a table at a time into Excel.
Printing Excel data
 Excel supports numerous print configuration options:
 Page margins
 Column and row headings
 Date and time information
 Configure printing options from the Page Setup group in
the Page Layout tab of the ribbon.
Share Excel data on the Web
 Share a spreadsheet or worksheet on the Web.
 Change the file name (if needed) and specify
other options.
Collaboration features
 Microsoft SharePoint Services empower enhanced
collaboration through support for:
 Document Workspaces
 Meeting Workspaces
Final steps
 Are there any questions?
 Thank you for attending.
 Don’t forget your handout.